Short Guide to Better VBA


Well-known Member
Nov 1, 2008
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:

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  ###
    ' 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
            next lC
        end if
    next lR
    ' Now write the results back to the sheet
    Range("A2:H2000").value = aData


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
Last edited by a moderator:


Well-known Member
Nov 1, 2008
Below a complete setup to test this.

Result for 1,000 rows :
using cell by cell: 28.4 sec (1,000 reads, 1,000 writes)
using array: 82 millisec (1 read, 1 write)
and that is even with putting screenupdating off for the cell by cell and on for the array.

The slow part lies in the sheet read and writes, the calculations in memory are as quick as anything

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Type</SPAN> SYSTEMTIME<br>        wYear <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        wMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        wDayOfWeek <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        wDay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        wHour <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        wMinute <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        wSecond <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>        wMilliseconds <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Sub</SPAN> GetLocalTime <SPAN style="color:#00007F">Lib</SPAN> "kernel32" (lpSystemTime <SPAN style="color:#00007F">As</SPAN> SYSTEMTIME)<br><br><SPAN style="color:#00007F">Function</SPAN> MyTimer() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#007F00">'In the Function where you need find diff</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sSysTime <SPAN style="color:#00007F">As</SPAN> SYSTEMTIME<br>    <SPAN style="color:#00007F">Dim</SPAN> lStartSec <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iCurrentSec <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    GetLocalTime sSysTime<br>    lStartSec = <SPAN style="color:#00007F">CLng</SPAN>(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds<br>    MyTimer = lStartSec<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> testt()<br>    <SPAN style="color:#00007F">Dim</SPAN> l1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, l2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, li <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lk <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    l1 = MyTimer<br>    <SPAN style="color:#00007F">For</SPAN> li = 1 <SPAN style="color:#00007F">To</SPAN> 1000000<br>        lk = lk + 1<br>    <SPAN style="color:#00007F">Next</SPAN> li<br>    l2 = MyTimer<br>    MsgBox "PG: " & l2 - l1<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Test1()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> lrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> l1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, l2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, l3 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> ITER <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 1000<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    l1 = MyTimer<br><br><SPAN style="color:#00007F">For</SPAN> lrow = 1 <SPAN style="color:#00007F">To</SPAN> ITER<br>    <SPAN style="color:#00007F">If</SPAN> Cells(lrow, 1) = "Test" <SPAN style="color:#00007F">Then</SPAN> Cells(lrow, 2) = "Pass" <SPAN style="color:#00007F">Else</SPAN> Cells(lrow, 2) = "fail"<br><SPAN style="color:#00007F">Next</SPAN> lrow<br>    l2 = MyTimer<br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br>    <SPAN style="color:#00007F">Dim</SPAN> aData <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    aData = Range(Cells(1, 1), Cells(ITER, 2)).Value<br>  <br>    <SPAN style="color:#00007F">For</SPAN> lR = <SPAN style="color:#00007F">LBound</SPAN>(aData, 1) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aData, 1)<br>        <SPAN style="color:#00007F">If</SPAN> InStr(aData(lR, 1), "Test") <SPAN style="color:#00007F">Then</SPAN> aData(lR, 2) = "Pass" <SPAN style="color:#00007F">Else</SPAN> aData(lR, 2) = "fail"<br>    <SPAN style="color:#00007F">Next</SPAN> lR<br>    <br>    Range(Cells(1, 4), Cells(ITER, 5)) = aData<br>    l3 = MyTimer<br><br>    MsgBox "Without array: " & l2 - l1 & vbCrLf & _<br>            "Using array: " & l3 - l2<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)


Well-known Member
Oct 1, 2013
Grate test:)

result for me: 70ms vs 21ms

I know this is of topic and I am apologise for this.
If some of you guys can write some basic Array Guide?

How to create Array
How to add new "element" to Array (exp. Array{1,2,3} my new number is 4 how can i change array to be Array{1,2,3,4}
How to search array
How to split array to half or to component

and so on

if there is the Guide like that can you guys give a link please?


Well-known Member
Nov 1, 2008
Bill Jelen (MrExcel) has writen a book that covers arrays: VBA and Macros for Microsoft Excel, you can get it as eBook. Chip Pearson has lots on arrays. But if you start a new thread, and send me a private message with the link, then I can help you there as well. rather than on this thread.

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back