Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

Where can I get Excel add-ins?

You can get Excel add-ins from a number of places:

Excel includes several add-ins that you can use whenever you need them. Use the Add-Ins dialog box to install them.

You can download more add-ins from the Microsoft Office Update Web site.

Third-party developers distribute and sell add-ins for special purposes.

Many developers create free add-ins and distribute them via their Internet sites.

You can create your own add-ins.

How do I install an add-in?

The most common way to install an add-in is by using the Add-Ins dialog box. Choose Office Excel Options. In the Excel Options dialog box, select the Add-Ins tab. Then, select Excel Add-ins from the Manage drop-down control and click Go. A quicker method to display the dialog box is to press Alt+TI.

You can also install an add-in by using the Office Open command, but using the Add-Ins dialog box is the preferred method. An add-in opened with Office Open cannot be closed without using VBA.

When I install my add-in from Excel's Add-Ins dialog box, it shows up without a name or description. How can I give my add-in a description?

Before creating the add-in, use the Office Prepare Properties command to display the Properties panel. In the Title field, 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.

I have several add-ins that I no longer use, but 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?

Oddly, there is no way to remove unwanted add-ins from the list directly from Excel. One way to remove an add-in from the list is to move or delete the add-in file. Then, when you attempt to open the add-in from the Add-Ins dialog box, Excel will ask whether you want to remove the add-in from the list. Answer yes.

How do I create an add-in?

Activate any worksheet and then choose Office Save As. Then select Excel Add-in (*.xlam) from the Save as Type drop-down list. The add-in is created, and the original workbook remains open.

I try to create an add-in, but the Save as Type dropdown box doesn't provide Add-in as an option.

The most likely reason is that the active sheet is not a worksheet.

Should I convert all my essential workbooks to add-ins?

No! Although you can create an add-in from any workbook, not all workbooks are suitable. When a workbook is converted to an add-in, it is essentially invisible. For most workbooks, being invisible isn't a good thing.

Do I need to keep two copies of my workbook: the XLSM version and the XLAM version?

No, you can edit an add-in and even convert an add-in back to a normal workbook.

How do I modify an add-in after it has been created?

If you need to modify only the VBA code, no special action is required; you can access the code from the VBE and then save your changes in the VBE. If you need to modify information on a worksheet, activate the VBE (press Alt+F11) and then set the IsAddIn property of the ThisWorkbook object to False . Make your changes to the worksheet, set the IsAddIn property to True , and resave the file.

What's the difference between an XLSM file and an XLAM file created from an XLSM file? Is the XLAM 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 whether it's in an XLSM file or an XLAM file. However, XLAM files still contain the actual VBA code - not some special compiled code. Another difference is that the workbook is never visible in an XLAM file.

How do I protect the code in my add-in from being viewed by others?

Activate the VBE and choose Tools xxxx Properties (where xxxx is the name of your project). Click the Protection tab, select Lock Project for Viewing, and enter a password. Then save the file.

Are my add-ins safe? In other words, if I distribute an XLAM file, can I be assured that no one else will be able to view my code?

Protect your add-in by locking it with a password. This prevents most users from being able to access your code. Recent versions of Excel have improved the security features, but the password still might be broken by using any of a number of utilities. Bottom line? Don't think of an XLAM as being a secure file.