Macro Avaliable to Any Excel Workbook that is Opened

[jo15765]

I have a macro that I want to use in numerous excel workbooks. What I have been doing is just copying/pasting in a new module etc etc. My ? is, is there a way in excel 2000 to make a macro available to ANY workbook that is opened? In a sense I guess that would be embedding the macro to the "bones" of excel2000, or maybe it's better called a global macro?

 

[shg]

You can create an add-in. http://msdn.microsoft.com/en-us/library/aa140936(v=office.10).aspx

 

[HalfAce]

Not sure about xl2000 but I think it'll work the same. (Using 03 & 07 here.)

In the vb editor, open the VBA Project window and locate the workbook called "VBAProject PERSONAL.XLS"
If you insert a standard module and store your macro in there, it will be available to all open workbooks. You just need to refer to the Personal workbook when you call the macro from another.

Does that help at all?

 

[jo15765]

I don't see a personal workbook. When I go into the VB editor I see: Sheet1 and This workbook, and those are my only two options.

 

[HalfAce]

OK, for a single sheet workbook, that is all you'd see there.
If you scroll down to the bottom of the project window, is there no PERSONAL.XLS there? (Like I said, I don't have xl2000 handy here but I'd have to think it's in there as well as in later versions.)

I might be wrong (that happened once before... - and I'll stand corrected if I am), but I'm pretty sure it should be in there.

 

[jo15765]

Those are the only two that I see. I can't scroll down as there is no scroll bar. Is this something I have to set to be visible somehow?

 

[HalfAce]

No, if you're looking at the vba project window it should be visible (assuming it's actually there...)

It's possible I'm wrong I guess.
(you better print this because I'll never admit that again. )

Perhaps someone using xl2K can check it out and let us know...

 

[Weaver]

If you need (or don't mind) having the names of the subroutines appear in the macro menu (ctrl+F8) then by all means use personal.xls. If you don't have it, record a new macro and in the form that appears, change the 'store macro in:" dropdown to 'personal macro workbook' which will then create one for you. (I'm using '07 right now and it's still there when you start recording)

Personally, I prefer the add-in approach as pointed to in the first post, as this allows the macros to be less visible to the average user. You can add your own menu controls to access the routines.

 

[jo15765]

Maybe a macro is the wrong terminology for me to use. What I have been doing is right clicking my sheet, and then going to View code then selecting "This Workbook" then clicking on Insert---->Procedure--->Sub and then I would enter the code. The only way I could run that would then be selecting Tools---->Macro---->Macros and then choosing it form the list and selecting run.

So maybe I am using the wrong terminology, because I don't have the option to save in my personal, I can only save it in what sheets I have available when I choose to view the code.

 

[HalfAce]

Weaver:
Thanks for that. I didn't realize the Personal.xls workbook was created when a macro was recorded. (It's just been there as long as I can remember.)


jo15765:
No, I think you're using the right terminology. If you're going to go the personal.xls route (as opposed to an add-in) then just record a simple macro doing something and then you should be able to find the personal.xls in the vba project window after that.