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

Help calling macros from custom add-in

Posted by Paul E. on May 03, 2001 12:43 PM

Hello,
I have a workbook in which I have written many macros. When saved this file is over 600KB. As a result, I have saved this file as an add-in, in hopes of having smaller files that utilize the functionality of these macros. Once I open this add-in, I don't know how to call the macros within it. I'd like to either assign these macros to buttons on a template worksheet. Or even better, I would like to have a toolbar appear (or be created), every time I launch this add-in, which contains links to the macros contained within the add-in.

Thanks for any help
Paul E.

Posted by Dave Hawley on May 03, 2001 2:20 PM


Hi Paul

The build a toolbar is by far the best option. I have a link to a MS site on my Website under the "Great Links" page that descibes how to do this in a lot of detail.

Regarding the file size, have a read of a list of my recommendations of how to reduce file size.

1. Save the workbook as one version only. Don't save as multiple versions unless needed.

2. Export ALL modules and Userforms to your Hard drive.

3. Open a new Workbook. Window back to the your Workbook and right click on a sheet name tab and select "Move or Copy" then Copy the sheet to the new Workbook. Then save your new Workbook.

4. Do the same for all Worsheets, but each time you copy a sheet to the new Workbook and save, go to File>Properties and make sure there is not an unusual increase in file size. If there is, then you have probaly got a corrupt Worksheet. If so delete the sheet and go back to the Workbook it came from. Select it and push Ctrl+A and copy it's content to a new sheet. Then try again.

5. After you have all Sheets moved open the VBE and Import all your Modules and UserForms.

6. Consider replacing any array formulas with either Pivot Tables or Database functions. Array formulas are notorious for slllloooowwwwing down Excels saving and recalculation.

Here are some intresting links. Some may apply ?

http://support.microsoft.com/support/kb/articles/Q186/3/69.ASP

http://archive.baarns.com/excel/develop/vbaperfm.asp

By following these steps I have succeeded in reducing a Workbook by 75%I hope this helps. Good Luck


Dave


OzGrid Business Applications

Posted by Paul E. on May 04, 2001 5:18 AM

Dave- Thanks!! You have a great web site!