The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Excel Macros: Porting a macro easily
ElJeffe
Registered User, ClubPA regular
Registered User, ClubPA regular
So I have a VBA macro I wrote for Excel. This macro will need to be run from a number of different excel files. As in, each month the user will need to open a new file and be able to run this macro. The macro acts on whatever worksheet is currently active. I'm trying to find the easiest way for the user to do this.
I was hoping it'd be possible to create a button in the toolbar which would access the macro and run it on the currently active workbook, but that doesn't appear to be an option because Excel is dumb and requires that macros be tied to a single Excel file. Stupid, stupid Excel. So I need to figure out the simplest means to apply a macro to a new workbook. Possibilities I've thought of:
- Save out a template with the macro in it, and then apply this template to an existing workbook. I don't know if this is possible, though. I suspect it isn't because that would be too easy.
- Save the macro in a starter-workbook of sorts, and code the macro to prompt the user to open the file, and then select the worksheet, he wishes to perform the macro upon. Would this be viable? I'm not afraid of a little code, but I don't know if it's possible to do this in a way that's logical for the user.
- Can a macro be exported by itself, and then imported into an existing file? If so, is the process fairly simple?
If these or any other idea could be made to work, I'd appreciate any advice.
I was hoping it'd be possible to create a button in the toolbar which would access the macro and run it on the currently active workbook, but that doesn't appear to be an option because Excel is dumb and requires that macros be tied to a single Excel file. Stupid, stupid Excel. So I need to figure out the simplest means to apply a macro to a new workbook. Possibilities I've thought of:
- Save out a template with the macro in it, and then apply this template to an existing workbook. I don't know if this is possible, though. I suspect it isn't because that would be too easy.
- Save the macro in a starter-workbook of sorts, and code the macro to prompt the user to open the file, and then select the worksheet, he wishes to perform the macro upon. Would this be viable? I'm not afraid of a little code, but I don't know if it's possible to do this in a way that's logical for the user.
- Can a macro be exported by itself, and then imported into an existing file? If so, is the process fairly simple?
If these or any other idea could be made to work, I'd appreciate any advice.
I submitted an entry to Lego Ideas, and if 10,000 people support me, it'll be turned into an actual Lego set!If you'd like to see and support my submission, follow this link.
ElJeffe on
0
Posts
Take a look on the VBA side of things and specifically where you're saving it, I think there's an option somewhere to save it outside the actual workbook you're currently using.
Edit: That's really not terribly helpful, is it?
That might do what you need it to do.
the "no true scotch man" fallacy.
Except I'm on a Mac so the personal.xls thing didn't directly apply. A little poking around revealed the Personal Macro Workbook which is something that MS seemingly doesn't want to tell you about since they only subtly allude to it even if you directly search the help files for it. And the process of setting it up is... somewhat arcane. But it does exactly what I want and is simple, if unintuitive, to get working.
I found the easiest way (for me) to access the PERSONAL workbook would be to record a simple stupid macro... like, enter "a" in a cell, and saving it to the PERSONAL workbook from the drop list. Then I could go to the Window menu and Unhide the PERSONAL.XLS and voila!
Glad to hear you were able to figure it out.