Add-ins
Where can I get add-ins?
- You can get Excel add-ins from a number of places:
- Excel includes several add-ins that you can use if you need them.
- Third-party developers sell add-ins for special purposes.
- Shareware developers distribute add-ins via on-line services and Internet sites.
- You can create your own add-ins using the Tools Add-Ins command from a VBA module.
When I add an add-in to the Add-Ins dialog box, it shows up without a name or description. How can I give my add-ins a description?
Before creating the add-in, use the File Properties command to bring up the Properties dialog box. Click the Summary tab. In the Title box, enter the text that you want to appear in the Add-Ins dialog box. In the Comments field enter the description for the add-in. Then create the add-in as usual.
NOTE: The File Properties command is new to Excel 95. In Excel 5, use the File Summary Info command.
How can I write a VBA macro to create an add-in? I've searched everywhere for a VBA command and can't find it.
There is no VBA command to create an add-in. You can, however, use an XLM macro to do so. The following VBA statement creates an add-in called MYADDIN.
ExecuteExcel4Macro "VBA.MAKE.ADDIN(""MYADDIN.XLA"")"
I have several add-ins that I no longer use, yet I can't figure out how to remove them from the Add-Ins Available list in the Add-Ins dialog box. What's the story?
There is no way to remove unwanted add-ins from the list directly from Excel. You must edit the Windows registry and remove the references to the add-in files you don't want listed. Another way to do this is to move or delete the add-in files. Then, when you attempt to open the add-in from the Add Ins dialog box, Excel will ask you if you want to remove the add-in from the list.
What's the difference between an XLS file and an XLA file created from it? Is the XLA version compiled? Does it run faster?
There isn't a great deal of difference between the files, and you generally won't notice any speed differences. VBA code is always "compiled" before it is executed. This is true if it's in an XLS file or an XLA file. The compilation creates p-code, which is faster than simply interpreting each statement. However, XLA files contain the actual VBA code, not compiled code. The main difference is that the casual user can't view XLA files.
I used a file viewer to examine an XLA add-in that I created, and I can still see the comments I inserted into my VBA code. Doesn't Excel remove these comments before creating an XLA file?
No. The comments and spaces remain in the XLA file, making it larger than it need be. To make your XLA file as small as possible, you should remove all comments, extra spaces, and blank lines from your VBA code before creating the add-in. And to compact your file even further, you should copy the code in your modules, delete the modules, insert new modules, and then paste the copied code to the new modules.
Is there any way to automatically remove all comments, excess spaces, and blank lines from a VBA module?
No. However, you can use the Edit Replace command from a VBA module to help you. For example, to remove excess spaces, replace all occurrences of two spaces with one space. And if you've used apostrophes only for comments, you can replace '* (that's an apostrophe followed by an asterisk) with nothing - but make sure you check the Use Pattern Matching option. I know of no way to automatically remove blank lines from a VBA module. However, you can save the module as a text file, read the text file into a worksheet, manipulate it, and then copy it back to the module.
Are my XLA add-ins safe? In other words, if I distribute an XLA file, can I be assured that no one else will be able to view my code?
No. A few simple VBA commands is all it takes to convert an XLA back to its original XLS. To make it more difficult, you should protect the sheets on your workbook with a password before creating an add-in. Even then, it's possible that the password can be broken by someone who knows the secret. Bottom line? Don't think of an XLA as being an encrypted file.
Doesn't protecting the add-in with a password help keep it secure?
You can password protect add-in files, but whoever uses the add-in must know the password in order to open the file. And if the XLA add-in file can be opened, someone who knows the secrets can probably convert it back to an XLS file.
What is the secret of recovering the XLS version of an XLA file?
If I told you it wouldn't be a secret.