Recommended Excel VBA Coding Practices

A successful VBA project usually fulfils five criteria 1) meets user requirements, 2) is flexible, 3) user friendly, 4) optimized for speed and 5) easy to maintain. Good programming practices can improve your productivity as you create effective VBA applications with minimal effort on coding and debugging.

Many beginning programmers fall into the trap of not critically examining user requirements and focusing too much on user friendliness.

Real Life Example

I once advised an executive who wanted to re-make her company’s HR spreadsheet. It was a small company of 20 people and there was no need for database management software such as Access. The first thing I got her to do was to remove the elaborate and beautifully designed input form she had spent two days to create. There was absolutely no need to input information into one spreadsheet and copy it into another. It slowed the spreadsheet and created more hassle for users who had to navigate through the input form.

Some Habits I Practise

As a general rule, I do not do more than what the user requires, but instead keep the entire workbook as flexible as possible. Here are some habits I keep,

1) Use a spreadsheet for users to input model parameters, keeping it hidden until the macro is run. Avoid fanciful pop-ups, known as userforms, with multiple radio buttons, checkboxes, tabs, Ok and Cancel buttons UNLESS you really need it. Each element on the form has to be coded individually which is a real hassle. Any changes you make later will also require further coding.

2) Define a name for each cell requiring an input and refer to each input via that name in your code so that the input spreadsheet can be easily modified.

3) If you only have one variable for the user to input, use the InputBox Method. It launches a small window with a field for the user to enter his input with a single line of code. This is by the way, an excellent way for users to specify ranges.

I attach a complete workable set of code below for the InputBox method.

Sub popup()

Dim datarange as Range

On Error GoTo Handler 'if you click cancel on the pop up, exit macro

Set datarange = Application.InputBox _

("Select data range of at least 3 columns.", _

"Select your data range", Selection.Address(0, 0), Type:=8)

On Error GoTo 0 'Disable the error handler that we turned on above

Handler:

End Sub

Your input box will have the title "Select your data range", with the message "Select data range of at least 3 columns" and your current selected cell as the default input. The option "Type" refers to the type of data that the input box will accept. 8 refers to a range.

4) Break your project into parts and let each part be a macro. My code typically looks like this

Sub MainMacro()

Application.ScreenUpdating=False

Call processinputs

Call calculateinputs

Call showoutputs

Application.ScreenUpdating=True

End Sub

Sub processinputs()

….

End Sub

The Call functions in the MainMacro runs three different macros after each other. This way it will be much easier for me to locate my code and make changes when needed. Typically, codes with more than 30 lines should be split into multiple macros.

5) The Application.ScreenUpdating=False is a command to tell Excel not to visually update cell values while running your macro. This makes a lot of difference when you are dealing with large datasets. Basically, Excel spends more CPU power repainting your screen rather than performing the actual calculations most of the time.

6) Insert appropriate comments. You can do it in Visual Basic Editor with a single quote '. They help you to keept track of your code too.

7) Declare module level variables at least. It makes it much easier for you to keep track the use and importance of each variable. I sometimes go the extra mile of prefixing every module level variable with a g_. E.g.

Dim g_prices As Range

Dim g_Vol as Range

Sub MainMacro()

....

End Sub

8) Refer to spreadsheets by their name inside the Visual Basic Editor. For example, use Sheet1.Range(“A1”), rather than Sheets(“Sheet1”).Range(“A1”) to refer to cell A1 in your spreadsheet. This way, the user can edit the spreadsheet name in the main window without affecting your code.

9) Avoid hard coding calculations unless you want to hide a proprietary formula. Instead of coding something like

Range(“E1”).value=WorksheetFunction.Sum(Range(“A1:D20”)),

USE

Range(“E1”).value=”=SUM(A1:D20)”

If you need to, you can declare variable to store the address of the input range. E.g.

Dim address1 As String

address1=Selection.Address(False,False)

Range(“E1”).value=”=SUM(“ & address1 & “)”

address is the property to obtain the cell range as a string. A string is a list of characters. The two “False”s remove the $ signs from the address. & combines two strings together. This way, the user can vet the formula and make minor changes after your macro is done running.

10) Avoid writing your own functions, e.g.

Function crazy(Var1 as Range, Var2 as Range)

crazy=(Var1*Var2)^2

End Function

and type into cell A1 "=crazy(B2:C2)", when you can actually just type "=(B2*C2)^2"

These functions, known as user defined functions, slow your computer down a lot. Excel's built-in functions are already highly optimized. Sometimes, I would rather type a lengthy formula into a cell rather than to make my own function. It is much faster.

11) Dump the output onto a new sheet, so that you do not need to worry about accidentally pasting over the user's existing data.

Conclusion

At the end of the day, Excel is used by many people daily without VBA and rightfully it should be designed for such use. Hence, VBA is meant to complement existing Excel functionality. I often use VBA to automate repetitive tasks and to store formulas into the cells, so that much of the calculation work is done using Excel's ready made functions.

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)