Visual Basic for Applications (abbrv. VBA) is often misused causing issues with maintaining code, backward compatibility with older Excel version and other issues. Reason being is often that some of us don’t know, or better still, don’t make the minimal effort to learn ready available tools in Excel or other Office applications. From time to time I see some Excel macro that does the job of a Excel PivotTable or more often that of a simple MS Query (SQL). So I am dedicating today’s post to those struggling with their doubts on whether to open up the VBE (Visual Basic Editor).

More often than not, Excel users tend to create macros in situations where they could easily leverage an Excel formula, PivotTable, AddIn or other ready solution instead of writing VBA Code. So let us ask ourselves today:

Should I use a Visual Basic for Applications macro for that?

Do your research

What is the reason you consider resorting to VBA? Did you really do your research and you are sure there are no native Excel functions that will do the job? Too often I see people using VBA Macros because they didn’t do a 10 min Google search to see if there are Excel formulas that will do the job.

Does Excel have a built in tool for that?

Excel is the best entry level Data Analysis Tool out there. Are you sure you know all the tools natively available in Excel? Check the Data ribbon tab first!

• Sort and Filter tables
• PivotTables – doing reports in Excel
• What if Analysis – do a what if analysis for multiple scenarios with different variables
• Data Analysis Toolpak – statistical analysis tools and more
• Solver – solve linear problems
• MS Query – run SQL queries like SELECT, DELETE or UPDATE

None of them fit the issue?

Can you use an Array Formulas (CTRL+SHIFT+ENTER)?

Often what we need is to create a complex formula that will run on an entire array of cells or rows, not just a single cell.
Are you aware of Array Formulas? They allow you to do operations on whole arrays (rows/columns) of Excel data. A simple example below to demonstrate the power of Array Formulas:

The Table

The Array Formula (type and hit CTRL+SHIFT+ENTER):

=COUNT(IF(A1:A10 > 10;A1:A10))

The result (number of cells with value above 10):

2

As you can see the formula above can easily count all cells between A1:A10 of value higher that 1. Nice right? That is just the peak of the iceberg of what Array Formulas can do. Check out this Array Formula tutorial to learn more.

Is there an App or AddIn for that?

I know some of us like to be creative and look to code stuff their selves. However, I do encourage that you check for existing solutions:

• Office Store Apps – since Office 2013 Microsoft has launched its Office Store where you can easily download ready apps for many use cases
• Microsoft Power Suite AddIns:
  • Power Pivot
  • Power Query
  • Power Map
• Third Party Office AddIns – I myself have crafted a couple Excel AddIns myself, but there are more sites which list openly available and Free AddIns worth mentioning:
  • Name Manager – an absolutely awesome tool for managing Named Ranges in Excel
  • 50+ Free AddIns

No alternatives – need to use VBA

Ok so there doesn’t seem to be any alternative to writing a VBA macro. Where to start before you begin?

• Learn Excel VBA with my VBA Tutorial
• Check out the VBA Cheatsheet for ready code examples
• Get the VBA Timer Saver Kit – ready with multiple commonly used code snippets

Need help / support

If you stumble across issues when developing your VBA code, feel free to post your questions (do some research first!) on one of these forums:

• StackOverflow – in my opinion the best technical forum out there
• AnalystCave.com forums – got a question to one of my posts or tools? Feel free to reach out!
• MSDN Excel forum – the official Excel forum by Microsoft
• MSDN VBA forum – the official VBA forum by Microsoft
• MrExcel forum – no longer the power house it used to be, but still hosts a bunch of Excel users that share their knowledge and can help

Why am I discouraging the use of Visual Basic for Applications?

Don’t get me wrong – my blog is ALL about VBA. It is not that I discourage the use of VBA, in many cases you won’t have an alternative. It is just that in so many cases we are incorrectly lazy enough to avoid doing proper research that we actually spend more time writing unnecessary VBA scripts that copy often built in Excel capabilities. So many enterprises rely on VBA scripts that often do nothing more than a simple Pivot Table, Array Formula or PowerQuery/MS Query could do in seconds.

So seriously Help Yourself and do your research first. You will appreciate VBA when you will really need it.