The Best VBA is No VBA at all...

I am aware the above line will catch many off guard however allow me to explain. As a model builder you should see yourself as the custodian of a particular file. Now the whole world does not possess the herculean visual basic skills that you do so simple spreadsheet design that follows a sound set of rules will be easier for others to pick up and enhance long after you have taken your seat as CFO of some Fortune 500 company. VBA is not everyone’s cup of tea and it is possible to create wonderful financial models without its aid.

Saying this I am about to dump an inordinate amount of code on the following web pages, because if you have to use VBA it's best to be forearmed with the tools to use it effectively. I have made a lot of mistakes over the years and these errors have led to a crystallising of my coding which cuts to the heart of a procedure. I hope to impart that knowledge in the following pages in a way that is easy to follow, understand and most importantly replicate when necessary.

Visual Basic for Applications

Lessons for the Use of VBA

These are not hard and fast rules but a solid set of principles to follow whenever you are using Visual Basic for Applications (VBA).

Think before you use VBA. Only use it when it is completely necessary.
Use Option Explicit – it will force you to declare all your Variables.
Break projects into small chunks and test, test, TEST.
Use the recorder – but after you have used it make a copy and try to take all the noise out.
Use the Locals window and break points to test your code.
Use Filters and Special cells liberally – reduce your reliance on Loops.
Challenge yourself. Try problems online and compare your solution to the vb masters.

These tips are based on what I have learned writing VBA (and VB code) over the years. They are just a guide. The following pages will go through how to set up your vba environment and will demonstrate some of the techniques outlined above.

VBA VARIABLE TABLE

The table shown below does not contain all of the Variables available in VBA, just the most commonly used ones.

COMMON VARIABLE EXAMPLES

Variables are in essence used to provide a short cut when referring to Excel Objects (eg a Range) or to store data for later use (eg the name of a worksheet). When using Variables other than a few protected names, you can name your Variable anything you like. However, the general rule is to keep the Variable as succinct as possible, while maintaining relevance. The following are Variables which are quite common in the everyday use of VBA.

Dim wb as Workbook
Dim ws as Worksheet
Dim rng as Range
Dim i as Integer
Dim lr as Long
Dim str as String
Dim ar as Variant

What the above means to me;

wb is short for Workbook Object
ws is short for a Worksheet Object
rng is short for a Range Object
i is short for an Integer
lr is short for Last Row and it is a Long Integer
str is short for a String Variable
ar is short for Array and is a Variant

Below are 2 typical practical examples of the above;

Set ws=sheet1
‘Sheet1 is the sheet code name
Set rng=ws.Range(“A1”)

‘use of sheet variable above
i=10
‘static integer
Ws.rng=i
‘assign integer value to range

If you don’t specify the type of Variable, VBA declares the Variable as a Variant type. A Variant can accept any type of Variable. As such, if you don’t declare your Variables it will take longer for your code to run. One common mistake on forums all over the internet is to decare variables but don't specify a type. Here is an example

Dim ws1, ws2, wsR As Worksheet, rCel1, rCel2, rRep As Range

Each variable needs to be declared as type. This is the reason I put each variable on a separate line. Firstly for readability and secondly to declare as type. In VBA the above will be declared as follows.

ws1 as variant
ws2 as Variant
wsR as Worksheet
rCel1 as Variant
rCel2 as Variant
rRep as Range

Where as the intention would have been for the first three items to be declared as a worksheet object and the final three as a rang object.

SETTING UP OPTION EXPLICIT

Option Explicit is a statement which appears at the top of the worksheet, Thisworkbook and regular modules. Its presence ensures that all Variables aredeclared. Without Option Explicit, you can accidentally declare a new Variable just by misspelling another Variable name. This is one of those things that cause a lot of grief as you're trying to debug VB programs and figure out why your program isn't working properly. In my opinion, this shouldn't even be an option - it should always be on.

To set up a workbook so Option Explicit always appears at the top of each module in visual basic press Alt F11. Now choose Tools;

In the visual basic editor choose Tools – Options.

Ensure the above icon is ticked and click on the OK button above. To test that the procedure while still in the visual basic editor choose;
Insert – Module on the menu.

The new module will have the Option Explicit statement at the top of the screen.

Happy VBA writing!!!

TheSmallman.com