Link to home
Start Free TrialLog in
Avatar of jmg48
jmg48

asked on

VBA - 'Macro Cannot Be Found' Error

I have an Excel application, built in VBA, which needs to be deployed to a group of users.

It turns out that some of them may have the 'Compile On Demand' option within VBA switched off (I have it switched on) and I've discovered that turning it off causes an error when Excel starts.

The application consists of two add-ins, and the error occurs when the first add-in uses the Application.Run method to call a function in a module which is in the second add-in. The error message is: " ''The macro PrB.xla!'!modApplication.HideControls' cannot be found "

The file, module and macro all exist, and I get no error when running with Compile On Demand switched on.

Also, having triggered the error, if I then turn Compile On Demand on and rerun the Workbook_Open event handler which causes the error then it works fine. Turn Compile On Demand off again and rerun, and again it works fine. Then restart Excel and I get the error again.

Can anyone shed any light on why this happens, and whether there is a way to get round it without having to turn Compile On Demand on for all of the users?
Avatar of gbzhhu
gbzhhu
Flag of United Kingdom of Great Britain and Northern Ireland image

This maybe something to do with the addin 2 not loaded yet when addin 1 calls the macro in addin 2.  Try to sleep for a few seconds after loading addin 2 or do a few DoEvents

Failing that try to turn compile on demand on via code while loading your app then turn it off afterwards.  I am using Office 97 so i can't see any compile on demand option, you must be using newer version
Avatar of [ fanpages ]
[ fanpages ]

Try reversing the order of the loading of the Add-Ins, i.e. load the Add-In currently loaded second, first.

I also suggest developing your code in an environment that matches your resultant user environment.

That is, if your users do not have "Compile On Demand" set, then do not set this in your environment either.

You then should not have any nasty surprises when you roll-out your code.

BFN,

fp.
ASKER CERTIFIED SOLUTION
Avatar of mjwills
mjwills

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well done, mjwills,

Sometimes the most obvious thing to check is overlooked; always make sure your compile before deployment :)

BFN,

fp.