Not sure how to state this one. RRS feed

  • Question

  • H all.

    I've brought my old xlsb file into the office, and have mixed the existing office xlsb with the old xlsb. 

    In doing so, I've found that the existing office (New.xlsb) macros are throwing errors which were not a problem before, now that I've imported them into the old (Old.xlsb) file.

    As I'm working through this, I've found myself remembering back several years, when there were option explicit statements in one function or macro, that would result in what I can only call--- cross contamination.

    I.e., for reasons I'm unclear on, the option explicit in macro A- module 1, would affect some other macros in entirely different modules. 

    I have done a project-wide search (using ctrl+f), for other uses of the variables I am using in the newer, New.xlsb file-- in the Old.xlsb. There are no duplicate variable names appearing anywhere else. Which is why this is so frustrating.  

    If any of this is unclear, please ask. I've tried stating it as clearly as I can. 

    Is there something that I should be looking for that'd cause this mess? 


    Monday, November 23, 2015 11:29 PM


  • IMHO you should always use Option Explicit on every module and then before test running the code, in the VBA editor select menu item Debug -> Compile. Objects that cannot be found (sometimes misspelt) and any variables (also sometimes misspelt) will be immediately identified.

    Compile does not detect every error but it certainly helps. An example of an error it does not detect is a misspelt worksheet name that is enclosed in double quotes.

    In the VBA editor select menu item Tools -> Options and check the box against "Require variable declaration" and the Option Explicit will be added to every new module. (It does not add it to existing modules but all new workbooks will have it in all modules).

    What sort of errors are you getting? The code should stop on a error and give you the option to select Debug and then the errant line should be highlighted.

    If you are getting an error "ambiguous name" then you have duplicate sub names and need to change them so you have unique names.

    Regards, OssieMac

    Tuesday, November 24, 2015 2:54 AM