VBA or Formulas?

In the process of development complex spreadsheets in Excel there is always the question of the balance of using VBA macros vs. implementation of with formulas. There is no one answer and the choice depends on the target users, complexity of operations and logic, size of the sheet and others. I would like to share some thoughts on this matter.



Formulas in general allow fast development, easy navigation between ranges and relatively easy building and debugging process. The ranges of tasks that could be performed is much larger that one might expect - check sites like  Chandoo, ExcelUser or Pearson Software to see some examples. Formulas are not good at handling large ranges of data as it may significantly slow down the performance and increase the workbook size. Complex tasks can produce many calculation steps with negative impact on performance and size as well.


Macros can perform tasks with great speed and no limitation in complexity. I am not including the tasks only a macro could do - like copy/paste, integration with other applications or the OS environment. However, the Excel is not a software development platform and some related tasks are difficult to impossible. Producing good quality is one of them. Also, the full blown use of macros is available to limited analysts with some programming background and skills. Another drawback comes from the IT policies some companies on running macros.

The decisions on VBA macros vs. formulas should depend on the following factors:

Who and how uses the workbook
If the end user needs to have control and understanding of the mechanics of the workbook then the the formulas, macros and design have to match their capabilities. If someone does not understand VBA but needs to understand the logic and read the flow then extensive use of macros is not a good choice if performance of formulas is OK. On the other pole if all the user needs is something to do the job then the developer is free to use whatever she wants.

Performance and file size
Formulas that operate over a large data ranges or perform some complex task could dramatically reduce the performance and inflate the file. Array formulas are such example. They are very powerful and could perform some difficult tasks but are an application killer if applied to some large range. Same tasks could be performed much faster and pain-free with a VBA macro of function.

Complexity of logical flow
My humble opinion are there is no evident winner as it all depends on the design and implementation. Following the good practices in workbook design or VBA macros should produce logic that is easy to follow and alter. macros may have some advantage due to debugging feature whereas formulas do not always offer easily such facility.

How clear and stable are the requirements
For various reasons, one of the downsides of developing with Excel is the requirements are very dynamic and could come fast. Development of macros requires careful planning and putting many things together - almost same process as in the software application development. A new requirement could result in re-writing big pieces of code while formulas could solve the problem with some minor adjustments.

My approach
The approach that proved to be most suitable for my typical customers, type of projects, development cycles and calculations is to first to try the formulas with some help of relatively simple macros. Then I move to macro and custom function if the performance suffers or some particular tasks is way too complex to maintain with formulas. I could sacrifice some performance in sake of the spreadsheet readability and flexibility - after all wait time of 1.5s is not way more that waiting time of 1s.