Excel macro programming tips

I know there is a gazillion of web pages and bulleting board conversations full of Excel hints, tips and best practices. Here are my top tips for writing better code.

Option Explicit

Always, always, always and – did I mention always – place this in the top of every module. It forces you to declare constants and variables. So you hate regulation but the good thing is that it will help you by making your declarations act as part of the syntax. This way you will easily find typos and other annoying little mishaps in your code.

Naming convention

It is highly recommended that you use some consistent naming standard when you give names to your macros as well as declare constants and variables. This helps you to remember the type and use of said item when revisiting your code after a few months. It also helps you to avoid accidentally using reserved keywords or commands as names.

My personal convention uses (usually) one lower case letter indicating type of item followed by the item name beginning with an upper case letter. This way when I type the name in the code later in all lower case Excel will change the name to proper case indicating that I got it right.

Strings begin with “s”, integers with “n” (and always go Long), decimals with “d” (and always Double up), Booleans with “b”, Variants with “v” and object types with an “o”.  Array variable names begin with an “a” regardless for what type of array it is declared as.

The exceptions to this “rule” are frequently changing variables such as loop pointers declared at macro level. Numeric ones are single upper case letters (R for row, C for column, A for array pointer, I for index, etc). Objects are two lower case letters (ws for Worksheet, nm for Name, etc).

Also sometimes you may want to be more specific especially in a large project where public constants are used extensively. As an example you might declare that the number of the “Price” column on your “Products” sheet would be named “npcPrice” (see below) or alternately “ncProductsPrice”.

Sample declarations on the top of a module:

Macro names are preceeded by “p” (procedure) regardless if they are subs or functions. If your macro accepts parameters then make those names descriptive as well so you remember what they are used for when the syntax is shown.

Sample macro declaration:

Sheet codename

It is highly recommended to use sheet codename rather than sheet name when referring to a sheet in VBA. Sheet name is the one that you see on the tab of the sheet and it can potentially be changed by the end user thus rendering your macro references invalid.

Sheet codename is what you see in the Visual Basic for Applications Project Explorer window. The sheet codename is shown first and the name follows in brackets. You can easily change the codename to something more descriptive than “Sheet1” from the properties. Personally I prefer to use the convention shown below. Also take note of the function shown for the next item of Arrays.


Arrays

Every time a macro interacts with sheet content it takes a little time to read or write. As short as the time may be it is hugely slower than reading or writing to memory. For hundreds or thousands of sheet interactions the time difference is very significant – to put it mildly.

If you iterate through a lot of rows or columns then it is better to read the entire sheet to memory and just poke around in there. Reading the entire sheet is only one sheet interaction so the performance differerence is definitely worth it. Simplest way to do it is by declaring an array variant and pointing the sheet used range to it. (See image above.)

Once in an array you can iterate through with incredible speed benefit just referencing to the array similarly as you would to Cells. The first one below if a reference to a sheet directly and the latter is to an array previously read from the sheet.

If you make changes to the memory array then of course you need to write it back to the sheet. With a huge array it can be slower than doing a few selective direct writes to cell values that should be changed. If you decide to write the entire array back then you need to point the top left corner and resize to the dimentions of the array:

Regions and ranges

Excel is very handy with lists and tables when you keep the region intact. Typically a list has a top row with headers and each row is one record with data items in different columns. It is easy to filter and sort but people using Excel do not often make it easy. They want some visual “space” between rows so instead of adjusting row height they leave empty rows between items and the same goes with columns.

Unless you totally control the sheet content it is good to be ready for anything. So for an array you might be tempted to read the CurrentRegion starting usually from the top left corner (Sheet1.Cells(1,1).CurrentRegion). However that might be like Forrest Gumps box of chocolates “You never know what you’re gonna get”. CurrentRegion ends when it finds the first completely empty row intersecting with the first column that is completely empty up to that row.

Reading the UsedRange guarantees you get everything from the sheet. You may also get a lot of empty cells if the sheet has contained more at any given time than it does now. However you can reset the used range of a sheet by the following sequence:

 

To be continued …

Leave a Reply