Tag Archives: efficient spreadsheets

I know about spreadsheets

I don’t have to tell you anymore about what you could do with spreadsheets.

At this level, you probably have difficulties to handle longer formulas (it is always a good idea to test shorter part of the formula separately and than build the mega-formula).

People which tried some programming before, might even rush to VBA and rather quickly build a function of their own. Beware ! Don’t make the errors that I made ! (* pls. see below, if this is the case)

A common problem I had before needing to move to more elegant solutions was that I built (I still do… 🙂 ) large ranges with intermediary data which checked all kinds of conditions (-sometimes, I couldn’t use ‘new functions’ as IFERROR or SUMIFS as I needed to keep backwards compatibility to Excel 2003). That’s not the problem yet ! When I built additional worksheets to keep such temporary calculations, it was really hard to control.

My experience is not the most relevant. Please, let’s meet in the Advanced users forum !

Go to start of page…
Why to avoid VBA in Excel files(as much as possible)
While some solutions cannot do without VBA, there are a few things to consider:

  • if you design solutions for others, this will make the solution harder to access and develop by a non-programmer. If you think that this will give you an advantage, being indispensable, think twice! Most of cases, it is easier to change the solution !
  • the application will be less portable ! If your employer/customer decides to switch to Macs with Excel 2008 installed (no VBA !), you might have a problem. Or maybe he decides to cut software costs and use OpenSource solutions. There is only one solution I know that is giving limited support to VBA (it is named Go-oo )
  • some environments will have a strict policy regarding Macros and VBA. Learn to better use the basic part and to the full potential before rushing to the ‘shortcuts’

Now, don’t get me wrong ! VBA is great and lets you build powerful solutions. Just, that before using it, you should be sure that there is not an alternative solution.

Go to start of page…