Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA macros in all workbooks
I've created a VBA macro for Excel which does some formatting of a
spreadsheet I receive weekly at work, and saves a lot of time. However, I want to be able to just use the macro of the spreadsheet when it is mailed to me. I though of having the macro in a blank spreadsheet in the background while opening the new spreasheet (this is my current solution) but, to be honest, this seems like a bit of a botched solution! Surely there is some way to just load a macro and run it - I want this to be as simple as possible so I can instruction other non-macro-savvy workmates to use the macro in my abscense. Any ideas? Cheers, Ian Griffiths. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA macros in all workbooks
Ian,
Seems best to put the macros in an addin. You could also have the addin create a commandbar top launch the macros. -- HTH Bob Phillips "Ian Griffiths" wrote in message om... I've created a VBA macro for Excel which does some formatting of a spreadsheet I receive weekly at work, and saves a lot of time. However, I want to be able to just use the macro of the spreadsheet when it is mailed to me. I though of having the macro in a blank spreadsheet in the background while opening the new spreasheet (this is my current solution) but, to be honest, this seems like a bit of a botched solution! Surely there is some way to just load a macro and run it - I want this to be as simple as possible so I can instruction other non-macro-savvy workmates to use the macro in my abscense. Any ideas? Cheers, Ian Griffiths. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA macros in all workbooks
Excel opens a blank workbook every time you start it, but it is hidden. The
name of the workbook is personal.xls and it can contain all of the "global" macros that you write and want to use in any workbook that you open. I have a few formatting macros in my personal macro folder. Open the file that contains the macro, select ToolsMacroMacros and select Edit. You should see your macro in the right hand window (Code Window). Highlight the macro from "Sub Macro1()" to "End Sub" and copy. (if you called it something other than Macro1, highlight that) In the left window, there should be a Project Explorer window with a caption "Project-VBAProject" and a tree of filenames with branches of worksheets and modules. If you don't see that, press ctrl-R to open the Project Explorer Window. One project should be named "Personal". If it is not expanded, click the + to expand the tree and click on Module1 to open it in the Code Window to the right. If there is no module, right click the Project explorer window and select InsertModule. Paste your Macro into the code window with Personal.Module1 open. You can attach some bells and whistles, like a menu button or hot key to run the macro. Give a holler if you need help. When you close Excel, a warning will advise you that there have been changes made to the personal macro folder, do you want to save the changes? Click Yes. The macro will be available whenever you open Excel. Making it an addin would work, but, like the blank worksheet or template, it is a bit of overkill. Any coworkers that need to use the macro will have to repeat the above steps with their own personal macro folder. "Ian Griffiths" wrote in message om... I've created a VBA macro for Excel which does some formatting of a spreadsheet I receive weekly at work, and saves a lot of time. However, I want to be able to just use the macro of the spreadsheet when it is mailed to me. I though of having the macro in a blank spreadsheet in the background while opening the new spreasheet (this is my current solution) but, to be honest, this seems like a bit of a botched solution! Surely there is some way to just load a macro and run it - I want this to be as simple as possible so I can instruction other non-macro-savvy workmates to use the macro in my abscense. Any ideas? Cheers, Ian Griffiths. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA macros in all workbooks
Excel doesn't create the personal.xls until the user records a macro and
says to store it there. So it doesn't automatically exist. Also, any workbook stored in the xlstart (startup) directory will be opened by excel when it is opened manually with no suppression options. Any workbook could be stored there as hidden and would provide similar functionality in terms of the availability of macros. -- Regards, Tom Ogilvy Bradley Dawson wrote in message ... Excel opens a blank workbook every time you start it, but it is hidden. The name of the workbook is personal.xls and it can contain all of the "global" macros that you write and want to use in any workbook that you open. I have a few formatting macros in my personal macro folder. Open the file that contains the macro, select ToolsMacroMacros and select Edit. You should see your macro in the right hand window (Code Window). Highlight the macro from "Sub Macro1()" to "End Sub" and copy. (if you called it something other than Macro1, highlight that) In the left window, there should be a Project Explorer window with a caption "Project-VBAProject" and a tree of filenames with branches of worksheets and modules. If you don't see that, press ctrl-R to open the Project Explorer Window. One project should be named "Personal". If it is not expanded, click the + to expand the tree and click on Module1 to open it in the Code Window to the right. If there is no module, right click the Project explorer window and select InsertModule. Paste your Macro into the code window with Personal.Module1 open. You can attach some bells and whistles, like a menu button or hot key to run the macro. Give a holler if you need help. When you close Excel, a warning will advise you that there have been changes made to the personal macro folder, do you want to save the changes? Click Yes. The macro will be available whenever you open Excel. Making it an addin would work, but, like the blank worksheet or template, it is a bit of overkill. Any coworkers that need to use the macro will have to repeat the above steps with their own personal macro folder. "Ian Griffiths" wrote in message om... I've created a VBA macro for Excel which does some formatting of a spreadsheet I receive weekly at work, and saves a lot of time. However, I want to be able to just use the macro of the spreadsheet when it is mailed to me. I though of having the macro in a blank spreadsheet in the background while opening the new spreasheet (this is my current solution) but, to be honest, this seems like a bit of a botched solution! Surely there is some way to just load a macro and run it - I want this to be as simple as possible so I can instruction other non-macro-savvy workmates to use the macro in my abscense. Any ideas? Cheers, Ian Griffiths. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA macros in all workbooks
"Tom Ogilvy" wrote in message ... Excel doesn't create the personal.xls until the user records a macro and says to store it there. So it doesn't automatically exist. Also, any workbook stored in the xlstart (startup) directory will be opened by excel when it is opened manually with no suppression options. Any workbook could be stored there as hidden and would provide similar functionality in terms of the availability of macros. -- Regards, Tom Ogilvy Do you think it would be better to place the workbook with the macro in the xlstart directory, record a dummy macro like copy a1 paste a2 to create personal.xls or do the addin as the others suggested? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA macros in all workbooks
for a single Utility type macro, being distributed to others, I would
probably use a workbook stored in the xlstart directory other than personal.xls or if the users are VBA literate, send them the source code in an email and let them paste it where they want. I see personal.xls as being just that. Sharing personal.xls doesn't really appeal to me. For more stringent requirements, an addin is probably advisable - but not if you want to do Tools=Macros=Macro to run it. It is really situationally dependent. -- Regards, Tom Ogilvy Bradley Dawson wrote in message ... "Tom Ogilvy" wrote in message ... Excel doesn't create the personal.xls until the user records a macro and says to store it there. So it doesn't automatically exist. Also, any workbook stored in the xlstart (startup) directory will be opened by excel when it is opened manually with no suppression options. Any workbook could be stored there as hidden and would provide similar functionality in terms of the availability of macros. -- Regards, Tom Ogilvy Do you think it would be better to place the workbook with the macro in the xlstart directory, record a dummy macro like copy a1 paste a2 to create personal.xls or do the addin as the others suggested? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA macros in all workbooks
This is all very helpful guys, I'm going to try and create the
personal.xls tomorrow if I can, but keep the suggestions coming! Cheers, Ian. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros in shared workbooks | Excel Discussion (Misc queries) | |||
Macros common to several workbooks | Excel Worksheet Functions | |||
Cascading macros to new workbooks | Excel Worksheet Functions | |||
Making Macros Available in All Workbooks | Excel Worksheet Functions | |||
linking of macros with other workbooks. need to stop it! | Excel Discussion (Misc queries) |