Results 1 to 8 of 8

Thread: Compiling VBA

  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    54
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Compiling VBA

    I have been doing stuff in VBA for a while now and have reached the point where I feel quite proficient.

    A colleague of mine has developed an Access Db which also uses some VBA behind the forms and he has asked me to add in some extra functionality which I am now doing. When I made the first change I automatically compiled it and it came up with an error:

    Method or Data Member not found

    I looked at the code that triggered this error and it refers to a control that is not present in the form. I removed the offending code and tried again and got the same error elsewhere. I repeated this a few further times with the same result.

    After I spoke to the guy who developed it he said that when he started the app he imported a few forms from a similar app to get him going and that the code triggering the apps may be redundant. He then asked how and why I compile - he never has. It was one of those questions that I had never even thought about. I always have compiled the code before executing without really questioning why. The only answer I could give was "Because you do." I assumed that if I did not compile and then started to run it then a compile would be done anyway in the background before execution. But the fact that when I start using the app it just runs and does not generate any errors tells me this is not the case.

    So I've reached the point where I ask myself: Should you compile? What are the advantages of compiling?

    The 2 reasons I can come up with straight away are:

    1 - It will flush out any syntax errors prior to execution
    2 - It will identify any undeclared variables prior to run time (I always use Option Explicit but that is not used in this app).

    Are there any other, perhaps more important reasons why you should compile VBA code before executing? Will a compiled version run more quickly?

    Any thoughts or input would be welcome.

    TIA.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,053
    Thanks
    196
    Thanked 759 Times in 694 Posts
    NJL,

    The VBA will be compiled by the application. AFAICT the only benefits to manual compilation are those that you state above. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    njl100 (2013-04-23)

  5. #3
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    54
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thanks RetiredGeek.

    What I don't understand though is why the application will not trigger an error when it runs as the code still refers to non existent controls.

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,053
    Thanks
    196
    Thanked 759 Times in 694 Posts
    njl,

    Hard to tell w/o seeing the code. My first guess would be that the code in question does not get executed because the code logic jumps around it. But as I said can't tell with out the code to look at.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #5
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    54
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thanks RetiredGeek,

    I've had a play with it by adding new code that I know will be executed, introducing a deliberate error and running. It looks like it compiles each function prior to executing that function as it stops when calling the function with the error. How it does it & why it does it that way I don't know.

    Thanks for your help.

  8. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    856
    Thanks
    0
    Thanked 9 Times in 8 Posts
    My understanding has always been that Debug>Compile checks syntax and does an "early" compile of the code, meant to save time when the code is compiled at run time. At run time, each module is compiled as needed. You've probably noticed that when you compile, the file size might change significantly, increasing or decreasing in size. VBA apparently does a "housecleaning" every x number of compiles, and sometimes the file size might drop by 100k, even if you've added a few lines of code. I don't think Microsoft has ever been very specific about how the compile process words.

  9. #7
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Quote Originally Posted by njl100 View Post
    Are there any other, perhaps more important reasons why you should compile VBA code before executing? Will a compiled version run more quickly?

    Any thoughts or input would be welcome.

    TIA.
    For the the most important reason to manually compile is make sure there are not errors when you can create a mde/.accde. I would never deploy an Access app without compiling it into an mde/accde.

    An mde/accde has many benefits. One is that it keeps the VBA code in a compiled state. I have also found that Access app that is compiled as an MDE/ACCDE is more stable.

    I have seen database had never been compiled manually or as an MDE/ACCDE which had many issue. The VBA code was some messed up that I had to /decomile and recompile the VBA code. Once all the VBA code issues were fixed and it would compile it started working properly.
    Last edited by RetiredGeek; 2013-04-26 at 09:38. Reason: Spelling
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  10. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Luling, LA
    Posts
    6
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Look at Tools then References. A reference may have been added that is no longer used but could trigger an error in a compile if it is missing. You can uncheck it to make the error go away if the reference is not used in the VB Project.

Posting Permissions

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