Is it Faster/Better/Smarter to write it this way? RRS feed

  • Question

  • I have a dozen or so procedures in this workbook, all run from a Sub Main() procedure. Each procedure uses the same database to get the data, and several of the same worksheet objects. In addition to running the whole thing from the Main procedure, I also like to run each proc individually. Usually, what I will do is start off each procedure by declaring some object variables to hold my adodb connection and recordset, workbook and worksheets. and then setting them all. That way whether I run the whole thing from Main or from any individual procedure by itself, the variables get declared and set for the procedure to run. 

    For this new workbook I decided to try something different. Now, the first procedure call in the Main proc is to a procedure that sets object variables that are declared at the module level. THEN, in each procedure I start with an If..Then..Else statement to find out if the object variables are set. If so, then just keep going. If not it calls out to that proc that sets all the object variables then comes back and finishes. 

    OKAY. So here's the question. It works fine, but did I really accomplish anything by doing it this way? 


    Thursday, April 10, 2014 8:08 PM

All replies

  • Re:  "but did I really accomplish anything by doing it this way"

    Does the code run faster?  Is the file size smaller?
    Those are the two things one would normally look for.
    Is it easier to maintain?

    Also, if all your code is all in one module then the module level variables can be declared as Private instead of Public.
    That could save an ounce of memory.

    In addition, you might be able to minimize/eliminate the code in your initial procedure by declaring module level variables as constants...
    Private Const Sludge As Long = 100
    Private Const Muchtalk As String = "blah blah"

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, October 25, 2016 1:41 AM
    Friday, April 11, 2014 3:21 AM