Short Guide to Better VBA

[sijpie]

Following are some guidelines to improve your VBA skills and macro writing.


Writing macros in Excel uses Visual Basic for Applications (VBA) and so in a way you are programming. As with nearly all programming languages this means you have to use a logical approach and be accurate. VBA has a few tools to help you. Let's start of with some:


Correct variable usage
Option Explicit
Start every module with

Option Explicit

It has to be the first line in the module. You can do this automatically by going to the Tools menu in the VBA editor, and checking the box in front of 'Require Variable Declaration'.


Why is this important? Because now you know that a variable you are using is correctly written. So what good does that do? Say that you (not using this option) have some code as follows:

    rowcount = Range("A4").Value
    columncnt = Range("A5").Value
    Sheet(1).Cells(1,columncnt) = "Totals"
    Sheet(1).Cells(rowcnt,columncnt) = myTotal

So did you spot the error? you are using two variables for the row count, one of which (rowcnt) is not set to any value. If you had used Option Explicit, then when running the code, the rogue one would be highlighted. Bug squashed.


Proper declaration
You declare a variable by using the Dim keyword.

   Dim iCnt as Integer, iIndex as Integer
   Dim vRet 
   Dim rTbl as Range, rOutp as Range
   Dim wsClients as WorkSheet

As you type each declaration the VBA editor (VBAE) will show you a list of types you can choose from, quite handy, type in the first few letters, then a space (or comma or whatever) and VBAE will fill out the full name.

And what does it mean?
You have declared a few variables with a certain type, like two variables that can be used to store integers, two that can be used to store ranges, one that can be used to store a worksheet object, and one (vRet) that can be used to store anything (it is called a variant type). We could aslo have declared it as:

Dim vRet as Variant

which I prefer because it is clear then.

Why not declare all your variable as variants, then? It is the least typing. But it invites errors. Say that you would store a sheetname in such a variable and then later on you think it contains a sheet object, all kind of problems could occur. (the same happens when you do not declare variables)

Also you may have noted that I put a letter in front of each variable to give me a reminder of the type. So I could never make the mistake of thinking sName would contain a worksheet object, because I know it is a string. Bug squashed.


Last two tips:
1) Use some capitals in your names. But when you type your code, don't type the capital(s). As soon as you go to the next line, VBAE should capitalise your name correctly, if not you made a typo.
2)make your variable names understandable. But also don't make the names too long, as it it siring to type it and difficult to read through. Yes you will understand it when you are writing the code, but what about in a year's time? or if someone else needs to look at your code? That leads me to the next subject:


Comments
Use comments in your code. What are you doing here, what are you storing there. You write comments by adding a ' in front of the line.

Function CheckValid(rRng as Range) as Boolean
'### This sub checks to see if the range passed ### 
'### contains valid entries according to the    ###
'### table in the Users sheet. It returns TRUE  ###
'### when all cells in rRng are OK. Else FALSE  ###
...

or

    ' Check to see that the file is open, if not open first
    on Error Resume Next
    set vFile = Workbooks("2013 Costs.xlsx")
    on Error  Goto 0
    if vFile is Nothing then ' 2013 Costs is not open yet
        ' Open file
        ...

Here all the comments make it clear what you are doing, and when your code gets complicated, believe me, you will need it! How often have I come back to some older code and thought: What in heaven's name am I doing here???


Simplicity and Speed
Workflow and testingBefore you dive in and write reams of code only to find that it doesn't run do the following:

• write down on a piece of paper what the flow of the macro is going to be

• write the first bit of the code and step through it to see if it runs (for stepping through see 'Debugging' below

• keep it simple. Should part of the code be in a separate sub or function? It often helps to do that.

The end of Selecting, Copying and Pasting
Now there are a few things that can really speed up your code, and make it easier to see what you are doing as well. Let's start at how you use Excel: Say that cell A1 contains a material code, which can be changed by the user. You want to show the same code in Cell G12. What would you do: copy & paste the cell A1 to G12. or put a formula in G12 that says: =A1
Right, you would use the formula. So stop copying and pasting in your VBA! Yes, very occasionally it is required, but hardly ever. You can do rafts of moving data across form sheets and workbooks without ever having to select cells, sheets, workbooks, let alone use copy/paste. And it is immensly faster if you don't do all this. So how?


If on Sheet1 A1:L1 contain the months of the year (or headings of your choice) then the following simple line will transfer the headings to Sheet2:

 Sheets(2).Range("B3:M3").value = Sheets(1).Range("A1:L1").value

Meanwhile the active sheet could be Sheet5. See what I mean?


Oh but you want to copy the formulas. there are two options: exactly the same (referring to exactly the same cells) or relative, referring to the same cells, say the ones above it:

    ' copy exact formula across: if G4 contains =G3+1, then E10 will show =G3+1
    Range("E10:J10").Formula = Range("G4:L4").Formula
    
    'copy relative formula across: if G4 has =G3+1, then E10 will show =E9+1
    Range("E11:J11").FormulaR1C1 = Range("G4:L4").FormulaR1C1

The only thing is you need to know how big your receiving range is.


Smart Objects
We usually want to make our lives easy, so this is no exception, how can we do it?
Assume that you are working with 2 workbooks and a few sheets in each workbook. And you have to transfer data across and check values of cells to see what needs to go where.

workbooks("ClientList").Sheets{"Builders").Range("A3") = WorkBooks("Invoice").Sheets("2013").range("G72")

is quite some typing. And of course it is never one line! So how about:

    ' decalre our objects
    Dim wbCl as workbook, wbInv as Workbook
    Dim wsT as WorkSheet, wsInv as WorkSheet
    
    ' Initilise the objects 
    Set wbCl = Workbooks("ClientList")
    Set wbInv = WorkBooks("Invoice")
    Set shT = wbCl.Sheets{"Builders")
    Set shInv = wbInv.Sheets("2013")
    
    'use the objects
    shT.Range("A3") = shInv.Range("G72")

Looks neat, doesn't it?

and particularly with Ranges you can do things very easy. Dynamic ranges are ones where the address changes depending on values of variables etc.


Ever seen something like:

    ' unclear coding
    Range("A" & lRow + 2 & ":G" & lRow + 6).value = ....

Compare that with

    Cells(lRow + 2,1).Resize(5,8).value = ...

and if you had used a Range object:

    Dim rRng as Range
    
    ' set rRng to relevant cellin Column A
    Set rRng = Cells(lRow +2, 1)
    'Expand rRng by 5 rows and 8 columns and fill
    rRng.Resize(5,8).Value = ....

Looping - Think about it
Looping until some value has been achieved is quite common and very useful.
But looping on large datasets in your spreadsheet can be very slow, particularly if data is written to the sheet in each turn through the loop.


There are a number of things you can do to improve the situation:

• Ask: is there an alternative?

• Do as little as possible in the loop

• Can I read loads of data into memory and perform the operations there, before writing anything back?

Alternatives: If you are just looping to search for a different value, then forget it: use Find. See this webpage for an excellent way of avoiding such loops: Excel VBA Loops: Correct/Efficient Uses of Excel Loops. Do, For Each and While Loops


Can you read loads of data to memory (and write back) in one go?
Yes, you can!
With the help of arrays. Now if you are just starting with VBA programming the next bit may be confusing, but you can always come back to read it again. An Array is not very different from a range or a sheet in excel. it is a collection of data that we can access through indexes just like Column B Row 10 in a spreadsheet. I don't know why they are faster to manipulate then sheets, as these really are kept in memory as well. But the difference is hugh.


So how to work with Arrays (There is a lot of info on the web, I am just dealing with the arrays for reading data, manipulating and writing back).
We can read a range into an array simply by setting the array to the range:

    Dim aData as Variant
    DIM lC as long, lR as long
    
    ' load data range into array
    aData = Range("A2:H2000").value
    ' Check each element in "Column B" . _
      Ranges are loaded as a 2 dimensional array
    ' We check the lower and upper limits of the array _
      with LBound(array, dimension) and Ubound(array, Dimension) _
      here we want the limits of the first dimension which are the rows
    for lR = LBound(aData,1) to UBound(aData,1)  ' in this example the same as 'For lR = 1 to 1999'
        if instr(adata(lR,2) = "New York" then 
            ' the term New York is found in the second column at row lR
            for lC=3 to Ubound(aData,2)
                ' Fill the cells to the left with stars
                adata(lR,lC)="*****"
            next lC
        end if
    next lR
    
    ' Now write the results back to the sheet
    Range("A2:H2000").value = aData

Debugging


Debugging will happen. So how do you do it? You run the code, an error pops up. End or Debug? Debug of course, because VBAE will show you in which line it is having problems. Unfortunately there is little other information. Well there was, but you just clicked on the button and now you can't remember what the message said...


Look in the toolbar of the Editor. You see thre icons like on a DVD player: Play, Pause and Stop. Hit the Play button, and VBA will show the message once more. A message about an Object not Set. This could happen as follows:

sub ErrObj()
    Dim rRng as Range
    Dim vV as Variant
    
    vV = rRng.Value
end sub

The range object rRng has not been set to anything before you tried to use it.


Anyway that was just warming up, what handy tools does the debugger have? The best tool is the Step through.
Open a macro, and click once in the macro. Now press the F8 key. You will see the macro name highlighted in yellow. Press the F8 key again. The highltight moves down to the next (executable) line. Keep pressing and you will see the macro do its work! Very, very handy.


But what is the macro doing and why is it skipping the IF statement?
Most of the time you can see the values of the variables and ranges by hovering the mouse over them. Try it. Now you can see it is skipping the If because the value is too small. Not what you expected perhaps. So how did it get the smaller value. Be a Sherlock Holmes and find out.


If it is a long macro(s) and it is too tedious to step through every part and particular that loop that repeats 1000 times then you can do two things:
1) you can click in the border, left of the line you are interested in. A red blob appears and the line turns red as well. You have just set a bookmark. Every time the code gets to this point it will stop and you can check values, step through further, etc. Then press the Play button again to get the macro on its way again. If it pops by your bookmark once more it will stop. and you can repeat. To clear the bookmar, click on the red blob.


2) you can put your mouse cursor on a line where you are interested in and press Ctrl-F8. the code runs until your pointer. The as above you can check things and continue


There is a lot more, but these are some quick & handy tools

Happy Coding

 

[daverunt]

Interesting read. But I have to disagree with you here.

' Exceptionally clear coding

  Range("A" & lRow + 2 & ":G" & lRow + 6).value =

..and you look back at this and think WTF?

 Cells(lRow + 2,1).Resize(5,8).value =

 

[shg]

Nice job, sijpie, thanks for posting.

 

[shg]

Interesting read. But I have to disagree with you here.

' Exceptionally clear coding

  Range("A" & lRow + 2 & ":G" & lRow + 6).value =

I'd prefer

  Rows(lRow).Range("A3:G7").Value = ...

 

[Jon von der Heyden]

I'd prefer

  Rows(lRow).Range("A3:G7").Value = ...

I love that construct! But I always forget to use it!

 

[skorpionkz]

One question about looping.

In your example you assign range to the array and then loop inside of array.

Is it any faster than just loop through the range?

 

[Jon von der Heyden]

As far as I know it is generally faster to use a For Next loop through an array than it is to use a For Each Next loop...

 

[RoryA]

It is almost always faster - more so, the larger the range.

 

[skorpionkz]

I am sorry for those question but I am a bit confused.

so code:

Sub Test1()

Dim lrow As Long

For lrow = 1 To 1000
    If Cells(lrow, 1) = "Test" Then Cells(lrow, 2) = "Pass"
Next lrow

End Sub

will work slower than:

Sub Test2()

    Dim aData As Variant
    Dim lC As Long, lR As Long
    
    aData = Range(Cells(1, 1), Cells(1000, 2)).Value
  
    For lR = LBound(aData, 1) To UBound(aData, 1)
        If InStr(aData(lR, 1), "Test") Then aData(lR, 2) = "Pass"
    Next lR
    
    Range(Cells(1, 1), Cells(1000, 2)) = aData

End Sub

is this correct?

 

[RoryA]

Yes - try it and see.