VBA Layout,Tips & Tricks RRS feed

  • Question

  • Hello Everyone!

    I Havent been writing VBA for very long and Im really struggling Debugging and looking through my code.

    Has anyone got any Tips or Tricks on laying out VBA? Expecially keeping tract of If statements and what ones are not closed.

    Many Thanks in advance.


    Monday, February 29, 2016 1:52 PM

All replies

  • Hi Soliddrew,

    According to your description, this issue is related to VBA, the VBA forum is the better forum for this issue and we will move it there for you.



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 1, 2016 6:13 AM
  • Indenting is your friend.  

    The following text is a bit hard to read.

    If x=100 then
    If y=300 then
    If z=300 then
    msgbox "This is nice"
    End if
    End if
    End if

    Much easier to read it like this

    If x=100 then
    	If y=300 then
    		If z=300 then
    			msgbox "This is nice"
    		End if
    	End if
    End if

    • Edited by Exiled_In_CA Wednesday, March 2, 2016 2:27 AM Formatting
    Wednesday, March 2, 2016 2:26 AM
  • When writing new code I write the comments first to describe what the code needs to do, then I write the code to achieve each bit. I always try to use meaningful, self documenting variable names so that too helps.

    For example if I need an Excel range variable for processing a list of sales and am copying some data to a report worksheet I would use:

    Dim rngSales as Range
    Dim rngReport as Range

    never rng1 and rng2.

    As above, indenting is good, keep code for one Sub to less than one screenful and use meaningful sub names such as:

    Sub CopySalesDataToSummaryReport()

    not just Sub CopyData

    Rod Gill
    Author of the one and only Project VBA Book

    Wednesday, March 2, 2016 8:53 AM
  • Thankyou Rod, I wasnt doing this before. I will start to use this in my macro writing =) I appreciate the time you have spent to reply to my post.
    Wednesday, March 2, 2016 9:16 AM
  • Hello Exiled, Thankyou for your reply. I did start to adapt this method but found the code was going off the right had side of the screen. I will try to pick it back up as I did find it useful for finding what if statements done what.

    Wednesday, March 2, 2016 9:18 AM
  • If there any options to turn on lines that connect "do while loops" and "If then else endif" so you can easily locate what section of code sits in your if statement ect?
    Wednesday, March 2, 2016 9:21 AM
  • No there isn't a way to do that.  Most of my programs are 1000-2500 lines.  Here are some tricks I use:

    1. I create separate modules global variables, constants, common functions/subs.

    2. I have an initialization module that contains most initialization code.

    3. I create a module for each major function.  I don't refer to any form fields directly.  I pass form information through variables.  This way I can setup testing procedures that don't interact with the form.  I often setup testing procedures that I call from the command line.  This way I can call a batch program to test everything.

    4. I keep form module code at a minimum.  I collect all the form fields in variables or user defined types and pass that to the function/sub.

    5. Think about how something will fail and code accordingly. 

    Wednesday, March 2, 2016 3:48 PM
  • Soliddrew,

    My gut instinct tells me you've got too many if/then blocks if your code is moving off to the left.

    You might want to try Case statements.

    Also you can end a line with a _ and then pick it up on the next line.

    I cannot recommend indenting enough.


    Thursday, March 10, 2016 12:06 AM