• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

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?
0
jmg48
Asked:
jmg48
  • 2
1 Solution
 
gbzhhuCommented:
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
0
 
[ fanpages ]IT Services ConsultantCommented:
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.
0
 
mjwillsCommented:
>> I have an Excel application, built in VBA, which needs to be deployed to a group of users.

Make sure you compile it *before* distributing it. To do this, Alt-F11 -> Debug -> Compile then close and save the worksheet.
0
 
[ fanpages ]IT Services ConsultantCommented:
Well done, mjwills,

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

BFN,

fp.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now