Help with Excel Add-in

[FJ]

Hi, I'm very new to VBA and I'm trying to create an add-in. The problem is,
after I've created it, I can't seem to load it correctly so that the macros
will be available to other workbooks. I have read several pages of step by
step instructions online that are very good and very detailed, but in the
end, I still can't seem to get it to work. I’ve done the following:

- Created the add-in.
- Renamed the VBAProject in the properties window.
- Saved the add-in in c:\documents and settings\John Doe\Application
Data\Microsoft\AddIns.
- Set the reference to the add-in from the Tools, References box in the VB
editor.
- Made sure the box is checked in Tools, Add-Ins on the Excel worksheet.

I'm probably missing something very obvious, but since VBA is all very new
to me, I'm not sure what.

Thanks in advance for any help or advice.

P.S., sorry if this is a duplicate post. I tried to post a while ago and
got an error message. Not sure if it went through twice.

 

[Bob Phillips]

Howdy you know that it is not added? I ask this because adding are not
visible workbooks. Check in the VBIDE to see if it is there.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Howdy was meant to be 'How do' BTW.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[FJ]

Hi, Bob, thanks for your response. Actually, the thing is that when I open a
new workbook and go into the VBE, I can see the macro code, but I can't see
the macros listed when I go to dialog box under Tools, Macro, Macros on the
worksheet itself. Shouldn't they be listed in that dialog box if the add-in
is loaded correctly?

 

[Bob Phillips]

They won't be listed if
- the procedures are private
- the procedures are Functions not subs
- the procedures have arguments
- the module has Option Private Module at the head

Do any of these application

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peter T]

"FJ" wrote in message

Hi, Bob, thanks for your response. Actually, the thing is that when I open a
new workbook and go into the VBE, I can see the macro code, but I can't see
the macros listed when I go to dialog box under Tools, Macro, Macros on the
worksheet itself. Shouldn't they be listed in that dialog box if the add-in
is loaded correctly?

Macros in addins are not listed in the macros dialog, they are typically
activated from a custom menu button that you have added.

To get you started you can add a button manually with Customize menus, try
the smiley face listed under 'macros'. Place it where you want and assign
your macro. Later though you will probably want to add and remove your
custom menu buttons programmatically.

Regards,
Peter T

 

[FJ]

Hi, Bob, no, none of those things apply. I think I'm very foggy on how this
whole add-in thing is supposed to work. At some points when I try to run a
macro I've saved in an xla file, I get a message about a macro book that
cannot be found, even though the code itself is sitting open in the VB
Editor. It will say something like, "The macro 'Book1.xls!Name of Macro'
cannot be found".

I'm very confused. I must be doing something very wrong, but I don't know
what.

 

[FJ]

Hi, Peter. The problem is, I can't get it to work from a custom button,
either. I click on the button and it gives me a message like, "The macro
'Book1.xls!Name of Macro' cannot be found".

As I said to Bob, I'm just very confused about all this and I'm probably
doing something very wrong on a very basic level. I also don't really
understand why it keeps telling me that a .xls file can't be found. I
thought the point of an add-in was that once you had it loaded, the macros in
it would be available to other workbooks. Or does it always have to be
associated with a regular .xls workbook on each computer where you want to
use the macros in that add-in?

Sorry if this is a really basic question. All this is very new to me.

 

[Dave Peterson]

Are you building a toolbar or adding something to the menubar?

If you are...

I think your life will be lots easier if you create that toolbar when the
workbook opens and destroy it when the workbook closes.

Some references if you want to look at that option:

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

 

[Peter T]

Things to check -
Below, twice you refer to file with an xls extension, presumably if it's an
addin (subject line) the extension would be '.xla'. Perhaps it was 'xls'
until you converted it to an addin.

Does your filename include any spaces or punctuation, if so you'll need to
embrace it with apostrophes, try doing that anyway, won't do any harm.

'my addin.xla'!myMacro

Do you have any duplicate named macros, try renaming your macro as something
certain to be unique.

Check out Dave Peterson's post.

Regards,
Peter T

 

[Gord Dibben]

Before the workbook was saved as an add-in did you have buttons with macros
assigned to them?

These assignments would have included the workbook name as you
show....Book1.xls!macroname

When you saved as an add-in then changed the nameas you stated you did, the
assignments would not have changed and are still looking for Book1.xls

You will have to change button assignments to just the macro name.

Macros in add-ins don't need the workbook reference.

Right-click on the button or menu item when in Tools>Customize and hit "assign
macro"

Delete the Book1,xls reference so you are left with macroname only.


Gord Dibben MS Excel MVP

 

[FJ]

Hi, Dave, I'm trying to add a button to run a macro. This isn't for anything
important right now. I'm just trying to learn how to do it.

Thank you very much for the links. They seem to have a lot of good
information and I will definitely read through them all later.

 

[FJ]

Hi, Peter, no, I'm not using any spacs or punctuation. Actually, I've tried
it so many times that I finally just started using one word names to make it
easier.

I've tried to create the add-in both by starting with an xls file and then
saving it as an xla as well as just trying to just save it right from the
start as an xla, but I can't get it to work either way. It always seems to
be looking for an xls file in order to run. Do I have to save an xls file in
the add-in folder also?

 

[FJ]

Hi, Gord, I've tried it both ways. I've tried starting with a regular xls
workbook, creating a macro, and assigning it to a button, and I've also tried
to just create the macro and assign it to the button and then save it as an
xla without saving it as an xls first.

I tried changing the button assignment to just be the name of the macro
without any of the "Book1.xls!macroname" type of thing, but it just always
seems to revert back to looking for an xls no matter what I do. I have been
assigning the macro to the button by going to Tools, Options, Customize,
Assign Macro, but do I have to assign or change anything anywhere else for
this to work?

Sorry if this is all basic stuff. I'm just a beginner.

 

[Gord Dibben]

Create a new workbook.

Add your macros to a general module........no buttons or assignments yet.

Save As an Add-in to your Office Library.

Close and re-open Excel

Open a workbook and browse Tools>Add-ins for your new add-in.

Load it by checkmark.

Now add a button or menu item and "assign macro"

Type in just the macroname because you will not see any macros in the "Assign
Macro" dialog box and you won't need the add-in name prepended.

But keep Dave's advice about creating "on the fly" menus and toolbars. in mind
for the future.


Gord

 

[FJ]

Hi, Gord, thank you so much. I followed your instructions and they worked.
And yes, I will definitely keep Dave's advice in mind for the future.

Thanks again!

 

[FJ]

Hi, Dave, I just wanted to let you know that I finally had a chance to read
through some of the material on the links you posted and it's really great.
Thanks for posting those. I followed Debra Dalgleish's instructions and was
able to get a toolbar.

I obviously have a lot to learn about macros, toolbars, buttons, etc. but
after all the valuable information everyone here has given me, things are
becoming much clearer.

Thanks again!