We have a useful (and complicated) add-in written in Excel 4 macro language,
that has been in use for many years, but which needs updating. We have
edited, updated and tested the macro and it now runs as required. All we
have to do now is save the updated version as an add-in, to replace the
previous version. But Excel won't let us.
Previously, using Excel 5, we used the VBA.MAKE.ADDIN command to do this,
but now we have Excel 2000, and this command just causes a "macro error".
We can't run the VBA.MAKE.ADDIN command at all, neither from a macro nor from
a visual basic module as suggested by Microsoft at
http://support.microsoft.com/kb/q114436. Please help.

Sorry, there's something wrong with that link and I can't work out how to
edit my post to correct it. It should have been
http://support.microsoft.com/kb/q114436

Also, when I wrote "Excel 2000" I meant "Excel 2002". Is there no way of
editing my previous post to correct it?

No, I believe there is no way.

In the vbe, did you go to the properies for the ThisWorkbook element of the
workbook and change the IsAddin property to True, then save the file as an
XLA.

This should make it an addin.

--
Regards,
Tom Ogilvy


"Graham" wrote:

Also, when I wrote "Excel 2000" I meant "Excel 2002". Is there no way of
editing my previous post to correct it?

Hello. Thank you for replying.
To clarify - the macro that we want to save as an add-in is purely an Excel
4 macro. There is no VBA involved in it at all. The only VBA we have tried
is a single line of code adapted from http://support.microsoft.com/kb/q114436
which we thought might enable us to use the Excel 4 macro function
"=VBA.MAKE.ADDIN(filename_text)". (VBA.MAKE.ADDIN appears to have been
disabled in our version of Excel - I suspect this may have something to do
with virus protection measures). But this line of VBA code did not work.
Your suggestion, if I have understood it correctly, is to save the VBA module
continuing this code as an add-in. I can't see how saving a bit of code that
doesn't work as an add-in is going to help anything. Maybe I have
misunderstood you, or maybe there is some important principle here I haven't
grasped? Please explain. Thank you very much for your time.
Best wishes,
Graham

You said you had a workbook that you wanted to make an addin.

I said go to the vbe in that workbook and change the isaddin property of the
thisworkbook object properties to true. Then save the workbook so it has an
xla property and is of type addin.

I don't see anywhere that those instructions involve VBA and certainly
nothing that involves executing the macro command you have been bantying
about. ( although doing the save may be easiest to do in the immediate window
using a VBA command. )

Assume the file you want to save as an addin in named Myaddin.xls, then in
the immediate window:

Workbooks("MyAddin.xls").SaveAs "C:\MyFolder\MyAddin.xla", Fileformat:=xlAddin
Workbooks("MyAddin.xla").Close SaveChanges:=False

here is a reference which might have some additional useful information on
addins in general

http://www.jkp-ads.com/Articles/DistributeMacro00.htm

--
Regards,
Tom Ogilvy


"Graham" wrote:

Hello. Thank you for replying.
To clarify - the macro that we want to save as an add-in is purely an Excel
4 macro. There is no VBA involved in it at all. The only VBA we have tried
is a single line of code adapted from http://support.microsoft.com/kb/q114436
which we thought might enable us to use the Excel 4 macro function
"=VBA.MAKE.ADDIN(filename_text)". (VBA.MAKE.ADDIN appears to have been
disabled in our version of Excel - I suspect this may have something to do
with virus protection measures). But this line of VBA code did not work.
Your suggestion, if I have understood it correctly, is to save the VBA module
continuing this code as an add-in. I can't see how saving a bit of code that
doesn't work as an add-in is going to help anything. Maybe I have
misunderstood you, or maybe there is some important principle here I haven't
grasped? Please explain. Thank you very much for your time.
Best wishes,
Graham

Thank you for your reply.
Some further clarification and explanation seems to be needed.
Recent versions of Excel have allowed users to save most workbooks as
add-ins by selecting "Microsoft Excel Add-In" from the "Save as type"
drop-down list in the "Save as" dialogue box. But you can't do this with
Excel 4 macros. When the thing you are trying to save is an Excel 4 macro,
the "Microsoft Excel Add-In" option doesn't appear on the list. (Try it
yourself if you don't believe me). If I remember correctly, this annoying
obstruction first appeared in Excel Version 5, at the same time that
Microsoft removed the facility to record actions in Excel macro language.
The idea seems to have been to discourage users from using the intuitive,
powerful and easy-to-use macro language that was (and still is) integral to
the way Excel works, and to force us instead into using VBA, a grossly
over-complex sprawling mess of a language which has been tacked-on to Excel
in an absurdly inelegant way, and which was clearly designed by and for
professional programmers with little understanding or sympathy for the needs
of Excel users. I'm sure I'm not the only person who experimented with VBA
when it was first introduced but gave up in disgust because of its endless
unnecessary complications, the impenetrable computer-programmer's jargon in
which it was couched and, most aggravating of all, the fact that it didn't
provide any way of doing very many important things that Excel's native macro
language did naturally and without fuss.
Anyway, I digress. The point is that for many years Excel users have been
prevented from directly saving an Excel 4 macro as an add-in. There used to
be a work-around, however, in the form of the macro-sheet-only command
"=VBA.MAKE.ADDIN(filename_text)".
But this command no longer works - it causes a macro error when it is run.
If you type VBA.MAKE.ADDIN into Google, then several of the sites that come
up are related to viruses, so, as I mentioned before, I suspect Microsoft
have deliberately disabled it as an anti-virus measure.
I hope you now understand why I have turned (in desperation) to this
Microsoft Excel forum and why I have mentioned the VBA.MAKE.ADDIN command
several times. I don't know whether or not I have been "bantying" about it,
since I've never heard that word before.
You wrote "go to the vbe in that workbook and change the isaddin property of
the
thisworkbook object properties to true". You then wrote "I don't see
anywhere that those instructions involve VBA".
Since "VBE" means "Visual Basic Editor" and "VBA" means "Visual Basic for
Applications", I had assumed that the purpose of one was to edit the other.
My mistake, evidently!
I tried adapting your suggested code and running it in the Visual Basic
Editor's "immediate window", but I'm afraid it didn't work. A dialogue box
came up headed "macros" containing the name Auto_Open (even though there was
no Auto_Open macro on the macro sheet I was trying to save), and buttons
labelled "run", "cancel", "step into", "edit", "create" (button disabled) and
"delete". I ran your code five times and pressed each of the enabled buttons
in turn, but no .xla file was created.
Manually changing the "isaddin" property did seem to have an effect, though
- it made the macro sheet disappear, and when I closed Excel and reopened the
file it seemed to behave like an add-in even though its extension was still
".xls". I then changed the file's extension to ".xla" in Windows Explorer
and it seemed to work.
Is it likely to cause any problems doing it this way, do you think?
Many thanks for your help and for your patience.
Best wishes,
Graham

Graham -

... Recent versions of Excel have allowed users to save most workbooks as
add-ins by selecting "Microsoft Excel Add-In" from the "Save as type"
drop-down list in the "Save as" dialogue box. But you can't do this with
Excel 4 macros. When the thing you are trying to save is an Excel 4
macro, the "Microsoft Excel Add-In" option doesn't appear on the list. ...
<

Here's what I do. Open the Excel 4 XLM file. Choose Insert | Worksheet. The
workbook now has an empty standard worksheet and the XLM sheets from the
original Excel 4 XLM file. With the empty worksheet active, choose File |
Save As. In the Save As dialog box, (a) edit the Fle Name by omitting quotes
and removing the .xlm extension, and (b) in the Save As Type list box scroll
all the way down to the bottom to select Microsoft Excel Add-In (*.xla), and
click Save.

- Mike
www.mikemiddleton.com

Mike,
Thank you. That works a treat. So simple! I feel a bit of a fool for not
thinking of it myself.
Incidentally, when you wrote "What I do..." does that imply that you still
use Excel 4 macros yourself? If so, I wonder if you know any way of
preventing them from disabling the multiple undo facility? When an Excel 4
macro is running which responds to entries by the user by writing data to a
spreadsheet, Excel only seems to permit a single action to be undone.
Pressing Ctrl+Z undoes the last thing the macro did, not the last thing the
user did, and pressing Ctrl+Z a second time has no effect. So in practice,
the undo facility is disabled completely.
I had hoped that saving the macro as an add-in would solve this problem, but
I just tried out a little test macro saved as an add-in using your method,
and I find it doesn't. I'd be grateful for any suggestions.
Many thanks to you and to Tom Ogilvy for your time and expertise.
Best wishes,
Graham

Graham -

My TreePlan decision tree add-in for Excel, available at www.treeplan.com,
is still 99% XLM code. However, I do plan to rewrite it completely in VBA
Real Soon Now.

Regarding Undo, see John Walkenbach's page "Undoing a VBA Subroutine,"

http://www.j-walk.com/ss/excel/tips/tip23.htm

Also, see a current thread in this programming newsgroup (which you can
search using Google Groups): "Why Undo commandbutton is Cleared ?"

- Mike
www.mikemiddleton.com

"Graham" wrote in message
...
Mike,
Thank you. That works a treat. So simple! I feel a bit of a fool for
not
thinking of it myself.
Incidentally, when you wrote "What I do..." does that imply that you still
use Excel 4 macros yourself? If so, I wonder if you know any way of
preventing them from disabling the multiple undo facility? When an Excel
4
macro is running which responds to entries by the user by writing data to
a
spreadsheet, Excel only seems to permit a single action to be undone.
Pressing Ctrl+Z undoes the last thing the macro did, not the last thing
the
user did, and pressing Ctrl+Z a second time has no effect. So in
practice,
the undo facility is disabled completely.
I had hoped that saving the macro as an add-in would solve this problem,
but
I just tried out a little test macro saved as an add-in using your method,
and I find it doesn't. I'd be grateful for any suggestions.
Many thanks to you and to Tom Ogilvy for your time and expertise.
Best wishes,
Graham