MrExcel.com - Your One Stop for Excel Tips & Solutions

Initializing Add-Ins as Excel is Opening

Posted by Rob G on March 30, 2001 9:05 AM

I am having a problem getting rid of an error message. My macro starts using the Auto_Open procedure, which has in it some lines that determine if the 'Analysis Toolpack' and 'Analysis Toolpack - VBA' Add-Ins are installed and if not, then to install them. I use the worksheetfunction command in order to use the Isodd() and Mround() functions in my code. What is happening is that VBA compiles all the code before hitting the Auto_Open procedure, and if the Add-Ins are not installed then an error message appears, before installing my Add-Ins. Is there any way of getting around this? How does Excel initilalize the code? Can I install the Add-ins during the initialization portion of opening the workbook?

Any comments will be greatly appreciated!!
Thanks,

Rob G

Posted by Dave Hawley on March 31, 2001 3:48 AM

Hi Rob G

The prefered method for executing code upon opening is the Workbook_Open method:

Private Sub Workbook_Open()
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
End Sub

This must be placed within the "ThisWorkbook" module.

To prevent the early compiling, while in the VBE go to Tools>Options|General and de-select the "Compile on demand" checkBox.


Compile:

Compile On Demand — Determines whether a project is fully compiled before it starts, or whether code is compiled as needed, allowing the application to start sooner.


Background Compile — Determines whether idle time is used during run time to finish compiling the project in the background. Background Compile can improve run time execution speed. This feature is not available unless Compile on Demand is also selected

Dave
OzGrid Business Applications

Posted by G. Ramos on March 31, 2001 6:55 PM

Posted by Dave Hawley on April 01, 2001 7:35 PM

Hi Gabriel

To easily access the "ThisWorkbook" module, right click on the sheet picture, top left next to "File" and select "View Code". This will take you straight there. This is where all Event codes for the workbook must go. You can see the full list by selecting "Workbook" from the Top left drop down box ("Oject") and then selecting the Event from the Top right drop down box ("Procedure").

Dave
OzGrid Business Applications