Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2009
    Location
    New York, New York
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I encountering a weird problem in an Excel 2007 VBA project I'm doing now.

    Each time before I close the file, I compile and save. The next time I open up the .xlsm file again, I expect to see the "Compile VBAProject" command grayed out, but it's not! Why does Excel still think the VBA code is un-compiled?

    Has anyone here experienced this problem before?

    Thanks.
    [pre]Stephan Ip
    www.CustomOfficeDev.com
    [/pre]

  2. #2
    New Lounger
    Join Date
    Feb 2009
    Location
    New York, New York
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, I discovered the cause of the problem -- ActiveX controls (option buttons, command buttons, etc.) which I put in the Excel sheets. I looked at an earlier version of my project without the ActiveX controls and it didn't have this problem. And if I delete the ActiveX controls in my current version, re-compile and save, the project does remain compiled after re-opening.

    Still, I would like to know how I can keep my project in a compiled state even with the ActiveX controls. This has to be an Excel 2007 bug. I definitely don't recall this happening with the earlier Excel versions.
    [pre]Stephan Ip
    www.CustomOfficeDev.com
    [/pre]

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have NEVER worried about the compiled state of my workbooks. I think you can safely do the same. In fact, I even prefer an uncompiled version of my workbooks because these are smaller in file size.

    Try this on a file with some significant amount of VBA:
    - Open file, enable macros, compile VBA project, save. Note filesize. (my example: 9852 kb)
    - Open same file, DISABLE macros and save. Note filesize has decreased (My example: 8023 kb).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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