VBA - 'Macro Cannot Be Found' Error

Posted on 2005-04-19
Last Modified: 2008-01-09
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?
Question by:jmg48
    LVL 12

    Expert Comment

    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
    LVL 35

    Expert Comment

    by:[ 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.


    LVL 2

    Accepted Solution

    >> 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.
    LVL 35

    Expert Comment

    by:[ fanpages ]
    Well done, mjwills,

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



    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now