msgbartop
Just another WordPress site
msgbarbottom

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 icon smile Secret of the giant excel array formula 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.

28 Jan 09 VBA Error Handling with line numbers

Sometimes it is easier to find an error if we know the code line where the error occurred. I have a method to show this in an error message.This is a light version of my error handler and focuses only to the line number of the error.There is a variable called “erl” which contains the line number of the error, so we can use it in the error message:

MsgBox Err.Number & " - " & Erl & " - " Err.Description

But this will show 0 instead of the line number if we didn’t put numbers at the beginning of the rows.Dick Kusleika wrote about the MZ Tools, and MZTools is a great tool for this too.There is an “add line number” icon on the toolbar which made the line numbering in my stead.

addlinenr VBA Error Handling with line numbers

Add Line Number Icon

Voila, it is done, and we can use it.