visual basic for applications

Do you really need Visual Basic for Applications macro for that?

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

Google your problem!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!

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

A
2
5
11
12

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:

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?

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
Words of advice when posting questions: most forums have a policy to ignore (at best) questions which don’t show that you made any effort to resolve the problem yourself. Therefore when posting a question remember to follow the above steps or at least:

  • Google your question
  • Search the Forum database for similar questions.. you would be surprised how many people had the same problem as you did
  • Include a summary of your research in the question
  • Include your sourcecode (line that throws error even better) or be sure to explain the issue in detail
See also  VBA Read file in Excel (txt, xml, csv, binary)

On the other hand don’t:

  • Post questions showing negligence to details and quality – lots of typos, misuse of formatting etc.
  • Write general statements that nothing works or post questions with general titles like – Excel Problem or VBA Issue
  • Demand that someone write the code for you (really, it happens)

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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