Short Guide to Better VBA
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Short Guide to Better VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Short Guide to Better VBA

    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
    Code:
    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:
    Code:
        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.
    Code:
       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:
    Code:
    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.
    Code:
    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
    Code:
        ' 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:
    Code:
     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:
    Code:
        ' 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.
    Code:
    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:
    Code:
        ' 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:
    Code:
        ' unclear coding
        Range("A" & lRow + 2 & ":G" & lRow + 6).value = ....
    Compare that with
    Code:
        Cells(lRow + 2,1).Resize(5,8).value = ...
    and if you had used a Range object:
    Code:
        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:
    Code:
        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:
    Code:
    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 RoryA; Feb 19th, 2014 at 07:47 AM. Reason: Fixing tag

  2. #2
    Board Regular
    Join Date
    Jul 2009
    Posts
    1,672
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Short Guide to Better VBA

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

    ' Exceptionally clear coding
    Code:
      Range("A" & lRow + 2 & ":G" & lRow + 6).value =
    ..and you look back at this and think WTF?
    Code:
     Cells(lRow + 2,1).Resize(5,8).value =

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Short Guide to Better VBA

    Nice job, sijpie, thanks for posting.

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Short Guide to Better VBA

    Quote Originally Posted by daverunt View Post
    Interesting read. But I have to disagree with you here.

    ' Exceptionally clear coding
    Code:
      Range("A" & lRow + 2 & ":G" & lRow + 6).value =
    I'd prefer
    Code:
      Rows(lRow).Range("A3:G7").Value = ...

  5. #5
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,679
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Short Guide to Better VBA

    Quote Originally Posted by shg View Post
    I'd prefer
    Code:
      Rows(lRow).Range("A3:G7").Value = ...
    I love that construct! But I always forget to use it!
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  6. #6
    Board Regular skorpionkz's Avatar
    Join Date
    Oct 2013
    Location
    Dublin
    Posts
    1,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Short Guide to Better VBA

    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?
    Regards,
    Andrzej (Andrew) Bejmart

    ------------------------------------------------------------------------
    Any fool can know. The point is to understand. - Albert Einstein

    Windows 10, Office 2013, Home PC
    Windows Server Datacenter, Office 2010, Work - Citrix Server

  7. #7
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,679
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Short Guide to Better VBA

    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...
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,737
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Short Guide to Better VBA

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

  9. #9
    Board Regular skorpionkz's Avatar
    Join Date
    Oct 2013
    Location
    Dublin
    Posts
    1,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Short Guide to Better VBA

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

    so code:
    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:
    Code:
    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?
    Regards,
    Andrzej (Andrew) Bejmart

    ------------------------------------------------------------------------
    Any fool can know. The point is to understand. - Albert Einstein

    Windows 10, Office 2013, Home PC
    Windows Server Datacenter, Office 2010, Work - Citrix Server

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,737
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Short Guide to Better VBA

    Yes - try it and see.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •