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…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Spreadsheets are really easy !