Excel 5 Makes its Splash

by Dermot Balson - Independent Developer

Roll your own Excel status bar
The minute Excel 5 hit the decks, programmers were asking how to control the neat little LED bar used when files load. No go. Although that didn't stop David Wiseman here in Australia. He developed a look-alike that works beautifully (in Excel 5 and 7), with extra features, and he gave it to the world.

Working with Excel Add-Ins
You have built your workbook. Now it is time to turn it into an add-in for distribution. That's when you can run into some problems.

The first is discovering that when you use the add-in to manage another workbook, ActiveWorkbook refers to the other workbook, not the add-in. You need to use ThisWorkbook to refer to the add-in.

Debugging add-ins can be a pain, when you have to keep "recompiling" them all the time. Here's some code to automate the process. Put it in the addin workbook somewhere and run it to create a new version of the add-in, fast. You can keep the XLS version open all the time, while you are testing the add-in, so as you find mistakes, you can edit the XLS and recompile the XLA. 

  ThisWorkbook.Save
  Workbooks("MyXLA.xla").Close                    'if the XLA was open
  ExecuteExcel4Macro "VBA.MAKE.ADDIN(""MYXLA.XLA"")"
  Workbooks.Open "MyXLA.xla"                      're-open the XLA
One warning about this approach, however. If you call the XLA and suddenly get the inexplicable message that Excel can't find the sub you want, even though you know it is there, and you have been using it all day, it is likely there's a syntax error in your XLA, which prevents the XLA from operating properly. The automatic compile approach above creates the add-in, even if there are mistakes.

You can't change an XLA once it is created, and you can't display its sheets onscreen, but you can keep sheet templates in it and copy them to other workbooks, or use sheets to hold constants.

To protect your XLA from prying eyes, password protect the workbook structure, because XLA code can be turned back into code if you know how. 

  ActiveWorkbook.Protect Password:="Fred", structure:=True
Well, thanks David. Here is the zipped Excel demo (30kb) of how to do this yourself. We will watch for more API calls in the futute.
Written by: Dermot Balson
Feb 1996


[HOME] [TABLE OF CONTENTS] [SEARCH]