Excel Macro Names

Hello Experts,

I probably need a little basic education on macro names.

I have a ‘Macro Menu’ macro that I wrote to help my ‘macro challenged’ users.  This allows me to keep only one macro in our workbooks.  This program will go out to the network where the latest version of all our macros reside, and get the names of all the macros and populate a menu for them to choose from.  They can then select the macro to run and it will import the latest version and start it for them.

Sounds easy right ?

Well, I don’t know what causes the macro name to change in the ‘Tools, Macro’ window.  Sometimes it is just the name of the Public sub, and sometimes it is: MyWorkbook!MyModule!MyProcedure.

The selected macro will upload OK, but will not always run.  The error I get is the macro can’t be found.

Please reply,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I think the best way to distribute macros is using of .xla files (Add-ins). In this case users won't need any macro on their PC/workbook.

Just save your workbook with macros as .xla (see Excel menu File / SaveAs / .xla) then install it on users' PC with add-in manager (Tools / Add-Ins / Browse). xla can be placed on network drive (or UNC), in this case all the users will use one copy of xla. Don't forget to answer "No" when add-in manager asks you to save .xla on the local PC.

Hope it can help,
<MIXA />
Hi believer
if I understand you correctly what you would need to do is have the menu bar deleted each time the file is closed so that the path name is not retained. Ie:

sub Auto_close()
end sub

Sub Auto_open()
application.CommandBars("yourCustomNameHere").visible = true
end sub

Or/and in the workbook where the latest versions are do the same, or do not attach the toolbar.

believerAuthor Commented:

I like your suggestion, and am pursuing it first.  I want to see if it meets the needs of my users.  They are low-end when it comes to the VBA environment.

I guess you  could say that I am “Add-In Challenged” because I haven’t used them much.  I was able to create an *.xla file with all my macros in it and save it to the network, and install it as an add-in.

My remaining question is how to call it. When I installed the other excel add-ins the menu automatically expanded to offer them.  Do I need to create some custom menu item ?

Please Reply,

Antrat, I haven’t forgotten about your suggestion.  I am just pursing the add-in approach first.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

You can create your own toolbar with button(s) to macros in your xla, and include it into xla workbook. So this toolbar will appear every time xla is loaded. The bad side of this is that toolbar will exist even after deinstalling xla (Excel makes a copy of such toolbars). In Auto_Open and Auto_Close macros of xla you can handle all additional toolbars (for example temporary ones existing only in current XL session) and extend XL menus, hot-keys, etc.

You can call macros of xla just in VBA code in VBA project of xls file with adding xla in References list (see VBE menu Tools / References) or with Run VBA statement if you don't want to have the reference to xla.

I suggest you place the macro with form that lists xla macros also into xla, then create new toolbar in XL, attach its button to that xla macro, then include toolbar into xla (see Custom Toolbar menu). After this users will only have to install xla in add-in manager (Tools / Add-Ins ... )

<MIXA />

believerAuthor Commented:

Don’t mean to be difficult, but I am still having some problems.  I have my  *.xla file created.
It contains all my macros.

I created a custom toolbar with a button for each macro. How do I assign them to the macros in the *.xla ?
If I goto: View, Toolbars, customize and select the button. Then I choose assign macro. No macros are available unless they are in the particular workbook I have open ?

If I open the *.xla there is no worksheet available, and many menu items are grey ? Is this normal ?

Please reply,

P.S. I increased the points because this is a little harder then I first expected, at least for me anyway…
The standard way of preparing xla:

1. Open source workbook with macros that will be saved as xla.

2. Create custom toolbar, for example "Custom1". Add buttons here. Assign macros to buttons, use macros from the current workbook (xls). If they don't appear in list of macros then it means they are declared as 'private' or with 'option private module'

3. Go to Toolbars tab of Customize Toolbars dialog. Press Attach button. Select your custom1 toolbar and copy it into current xls (right panel)

3. Go to VBE (Alt-F11). Recompile VBA project (menu Debug / Compile). It is not necessary, but after it Excel won't compile xla each time when it's loaded

4. Save xls file.

5. Save it as xla. xls is the source file, you will need it to produce next versions of xla. xla has some problems for modification. For example you have saved it as MyFile.xla

How it work on the user's PC:

1. Delete current toolbar "Custom1"

2. go to Excel menu Tools / Add-ins

3. If MyFile.xla doesn't appear in Add-Ins manager list, press Browse and locate the xla.

4. Exit to Excel, toolbar "Custom1" must appear, and pressing buttons on it will call xla macros.

That's all

There is next problem - "Custom1" toolbar will exist on user's PC even after deinstalling xla. You may delete it in Auto_Close xla routine (or Workbook_Close event). The other way is to create new toolbars / menus in Auto_Open xla routine with Temporary flag, so they will be destroyed automatically at the end of Excel session.

Hope it's that you want,
<MIXA />
believerAuthor Commented:

Thanks for the step by step instructions.  I am awarding you the points because your have earned them.  I must admit I still have a problem that I hope you will continue to help me with.

I was successful with steps 1 through 5.

In step 5, I saved the xls as an xla in a different location. Then I deleted the Custom toolbar. I added my new add-in, and everything seemed to go well.  The problem I still have is the custom toolbar macros still point to the xls file. They have the entire path to it when you look at them (i.e. File.xls!MyMacro)

Any ideas…

do toolbar buttons load the File.xls ?

Seems like custom toolbar is not from xla but from xls file.

I tested it before making the answer, and all worked well. Try this:

1. delete all custom toolbar if they are in Custom Toolbar dialog.

2. set only your xla enabled in the Add-In manager list.

3. exit Excel, then enter again.

4. The custom toolbar must appear. Will the buttons point to the xls file instead of xla ?

If so, please try to remake xla:

0. Turn off xla file in Add-in manager

1. Open source xls file

2. In Custom Toolbar remove the attached toolbar from the xls.

3. Delete custom toolbar from XL

4. remake toolbar as it was described before

5. Please make sure you assign macros to the current xls file that also stores macros you're assigning to the buttons. no xla/xls must be opened at that moment (for simplify the situation).

6. Only after this include toolbar into workbook as described above

7. save xls

8. save xla

9. close file

10. delete custom toolbar

11. create new empty workbook if need for opening Add-in manager dialog

12. turn on xla (I recomend you delete the old xla file on the drive for sure you use new its version)

13. quit add-in manager

14. check how macros work

If it doesn't help I resign :)

<MIXA />


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I wrote a code to list the macros from any xls file.
The features include selecting a file and once the file is selected all the macros in that file  would be listed on to this sheet.

My email Id is exuser21@hotmail.com

I did not see any of the above comments. I am all excited to put this comment, because it took me 3 days to write this. I tested this and it works excellent. You dont have to do anything except opening the xls that I send you.

I would send you the file you can check it for your self the code. The code is pretty complex.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.