Saving and Emailing Macros (Probably easy)

[Leila-Marie]

Hi,

I have a "begginers question" regarding how/ where to save macros.

If I save a macro "normally" ("in this workbook") then it only applies to that one file, right? And I can't use it in any new files.

So what do I do if I want a macro I can use in "all" my Excel files?

And can I email a file containing a macro to a colleague, so he can work on the file, using the macro too?

I ask, because I've had trouble with this in the past (tho in MS Word) where I couldn't get the macro to be emailed along with the file...

Also - how can I get an .xls file with a macro to work in newer versions of Excel? And can I use Excel 2010 to save a file with a macro in an .xls format, so it can be opened in older Excel versions too?

Thanks for your pointers!

From a very confused Leila-Marie...

 

[kpark91]

Hi Leila, I can't answer all the questions but I'll answer the ones I can.

If I save a macro "normally" ("in this workbook") then it only applies to that one file, right? And I can't use it in any new files.

Yes, it only applies to that one file.

So what do I do if I want a macro I can use in "all" my Excel files?

You might want to take a look at XL add-in. You would have to create it then move it to a XLSTART location for excel to contain the macro at any given time. However, it's best if you google it or search the forum regarding the topic.

And can I email a file containing a macro to a colleague, so he can work on the file, using the macro too?
I ask, because I've had trouble with this in the past (tho in MS Word) where I couldn't get the macro to be emailed along with the file...

Yes, he can use the macro too as long as the macro is enabled.
If there is a macro you want to perform for Workbook_Open() event, it's best to only make the workbook work when macro is enabled.

Also - how can I get an .xls file with a macro to work in newer versions of Excel? And can I use Excel 2010 to save a file with a macro in an .xls format, so it can be opened in older Excel versions too?

As new versions come out, VBA (macro) changes too!
Depending on what functions you have used in the macro, it may or may not work on newer versions of Excel.
Lastly, you can "Save As" a .xls format in all XL versions. However, again there are some compatibility issues so it's best to check after you have converted to .xls format.

 

[Leila-Marie]

Hi kpark!

Thank you so much for those clarifications.

I have been reading Excel Help, a tutorial on Excel macros and your comments but I'm only mildly less confused, so far.

If I save an Excel workbook, which contains a macro, as a normal .xlsx file, Excel tells me that that's not a format that supports macros. Then I choose a ".xlsx file with macros". I assume that that's right, but it suprises me that I need to save it in a "different" format... With the older versions of Excel, I don't remember needing to do that.

Also, to be able to use some of the macros, Excel has required me to "enable all macros" despite advising me not to do this, at the same time, because it's a security risk.

Hmm. Well, I will try to work on this tomorrow morning, as I am supposed to first demonstrate and then email the files to a colleague at noon tomorrow. Once it's on his computer I guess I'll know whether the macro has been emailed successfully and whether it runs in his copy of the file...

I like learning about this stuff - it's interesting. I really like macros, but have only ever used them in Word so far.

Oh, and Excel's Help feature explains this stuff really badly, I think. Whoever wrote that Help Manual didn't do a very good job. The sections which explain all the Excel formulas are written attrociously too, I think. Reading through them it sound like total gibberish, most of it.

Hmm. Yeah. Well, I will be all the more thrilled if and when this macro works on my colleagues computer and does all the swanky stuff to the data that it's meant to...

Thanks for your reply!!

XXX

Leila-Marie

 

[Michael M]

Hi there
Instead of saving the file as an ".xlsx" you need to save it as "xlsm", which means macro enabled.
No, you didn't do this in earlier version of Excel.
If you are comfortable with the macros within the file, then of course, it's OK to enable ALL macros.

Also, if you want to "share" the macro, put it in your personal. xls file......to do this, simply record a macro and when the Popup appears....in the box that says
"Store macro in" and select "Personal Macro Workbook"