4 Mistakes You Can Avoid when Programming Excel Macros with VBA

Microsoft Excel is a very capable data analysis tool already, but with the ability to automate repetitive tasks with macros by writing simple code in  (VBA), it’s that much more powerful. However, used incorrectly, VBA can cause a lot of problems. computer science computer science computer science computer science computer science computer science

Even if you’re not a programmer, VBA offers simple functions that allow you to add some really impressive functionality to your spreadsheets, so don’t leave just yet!

Whether you’re a VBA guru, who creates dashboards in Excel, or a newbie, who only knows how to write simple scripts that do basic cell calculations, you can follow easy programming techniques, that will help you improve the odds of writing clean and bug-free code. computer science computer science computer science computer science computer science computer science

Getting Started with VBA

If you haven’t programmed in VBA in Excel before, enabling the Developer tools to do so is actually pretty easy. Just go to File > Options and then Customize Ribbon. Just move the Developer command group from the left pane over to the right.

computer science computer science computer science computer science computer science computer science

excel-vba1

computer science computer science computer science computer science computer science computer science

Make sure the checkbox is enabled, and now the Developer tab will appear in your Excel menu.

computer science computer science computer science computer science computer science computer science

excel-vba2

computer science computer science computer science computer science computer science computer science

The easiest way to get into the code editor window at this point is just to click on the View Code button under Controls in the Developer menu.

1. Horrible Variable Names

Now that you’re in the code window, it’s time to start writing VBA code. The first important step in most programs, whether it’s in VBA or any other language, is defining your variables.

Throughout my couple of decades of code writing, I came across many schools of thought when it comes to variable naming conventions and learned a few things the hard way. Here are the fast tips for creating variable names:

  • Make them as short as possible.
  • Make them as descriptive as possible.
  • Preface them with variable type (boolean, integer, etc…).
  • Remember to use the right scope (see below).

Here’s a sample screenshot from a program that I use often to make WMIC Windows calls from Excel to gather PC information. computer science computer science computer science computer science computer science computer science

excel-vba3

computer science computer science computer science computer science computer science computer science

When you want to use the variables inside of any function inside of the module or object (I will explain this below), then you need to declare it as a “public” variable by prefacing the declaration with Public. Otherwise, variables get declared by prefacing them with the word Dim.

As you can see, if the variable is an integer, it’s prefaced with int. If it’s a string, then str. This helps later on while you’re programming because you’ll always know what type of data the variable holds, just by glancing at the name. You’ll also notice that if there’s something like a string that holds a computer name, then the variable is called strComputerName.

Avoid making very convoluted or confusing variable names that only you understand. Make it easier for another programmer to come behind you and understand what it all means!

Another mistake people make is leaving sheet names as the default “Sheet1”, “Sheet2”, etc…  This adds further confusion to a program. Instead, name the sheets so that they make sense.

computer science computer science computer science computer science computer science computer science

excel-vba4

computer science computer science computer science computer science computer science computer science

This way, when you refer to the sheet name in your Excel VBA code, you’re referring to a name that makes sense. In the example above, I have a sheet where I pull in Network information, so I call the sheet “Network”. Now in the code, any time I want to reference the Network sheet, I can do it quickly without looking up what sheet number it is.

Prev1 of 5Next

Leave a Reply

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