18 Mar 09 Secret of the giant excel array formula
I’ve already mentioned this on Twitter:
# The secret of my giant excel array formula is soon to be revealed. 928 characters, 78 left braces, 76 function in a cell.
# My Excel monster-formula makes 240 cells needless. Half as many lines are enough
9:13 AM Mar 16th from web
# What do you think, how can such a big formula be maintained? Tomorrow I’ll give you the answer.10:39 AM Mar 17th from web
# Here’s the great day, and the secret of the giant excel array formula.3 minutes ago from web
One of my customers needed an auxiliary line to perform a calculation reoccurring in every line.
This broke the unity of the whole system and made it quite hard to use.
My formula was much simpler at the beginning, but I improved it so many times that it became gigantic.
It is not easy to review a formula of such size, and to maintain it the traditional way is quite impossible.
I turned to VBA for a solution. I pieced the formula together in a macro.
I created variables for the recurring parts of the formula, which could even follow from each other:
strDate = "R" & intDateRow & "C"
strIndex = "IF(RC" & intIndexColumn & "=0, R24C15, RC" & intIndexColumn & ")"
strMonthStartDate = "DATE(YEAR(" & strDate & "),MONTH(" & strDate & ")+1,1)"
strMonthEndDate = "DATE(YEAR(" & strDate & "),MONTH(" & strDate & ")+1,0)"
Than from these variables I aggregated the whole function.
By breaking apart the formula, it remained maintainable, and comprehensible.
This was one of the great secrets, I gathered together in 9 years of Excel programming, but there are a lot more.
12 Mar 09 Error communicating with the OLE server or ActiveX Control
Several MS Access databases working on one computer and they don’t work on another.
The error message belongs to the forms of the database.
When you click on a button, MS Access gives this error message:
Error communicating with the OLE server or ActiveX Control
The error is generally caused by the use of special characters in object names.
When you create a form in Hungarian or other localized Access you will see, that the objects on the form get problematic names (Labels – Címke, Header – Űrlapfej, Footer – Űrlapláb, Combobox – KombináltLista etc.)
The solution:
1. You have to rename the special named objects on the problematic form.
2. If the error remained then create a new mdb file and import all objects from your old mdb.
Subscribe!
