Saving a Macro

[SteveK]

I have a Macro that I want to save and have available as a general tool I
can use on any file that I open.

I am using Record New Macro to generate the macro and it works fine in the
file that I had open when I recorded the file. But when I open a new blank
file (Workbook), where I intend to import a .csv file into it, I don't see
the macro available in the Macro window - not on This Workbook, nor All Open
Workbooks, nor Book1.

How do you make a macro that works in all excel docs?

Thanks,
STeveK

 

[Guest]

Have you tried to save it in your personal.xls file?

 

[SteveK]

I read a little about that:

My understanding is that I would make a personal.xls file and then save it
to some excel startup directory and then hide the personal.xls file when a
blank doc opens. I got all messed up trying to do it as I didn't seem to be
able to edit it unless it was unhidden and then couldn't save it hidden or
some sort of catch-22 like that.... I can probably figure out hwo to do it
with some more time and thought.

But is that the recommended way to make macros for general use? It seems
like such a work around. Other apps don't work that way. I can make Word
macros that show in all Word docs. Why would Excel do it differently?

Thanks,
SteveK

 

[Dave Peterson]

Microsoft is a big company. It's not too surprising that there are differences
between programs. (Although, I think that lots of the differences are
disappearing.)

But your personal.xls file doesn't have to be visible for you to edit the macros
contained in that personal.xls project.

Just open up the VBE (alt-f11 is a quick way).
hit ctrl-r (to see the project explorer)
doubleclick on the project for personal.xls

find your General module (or rightclick and insert a new module)
and edit your code there.

If you go back to excel and have personal.xls visible, you can use windows|hide
to hide it.

Then when you close excel, you'll be prompted to save personal.xls. If you save
it while it's hidden, then it'll open up hidden the next time.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=====
The only warning I'd give is that if you plan to share your macro workbook with
others, then I wouldn't use the name personal.xls.

Excel can only have one file open with any name--if you tell others to use your
personal.xls file, then they can't use their own at the same time.

You could call it SteveKUtil.xls and still store it in your XLStart folder.
Then share that workbook.

 

[SteveK]

Okay. I will try that.

One thing that always confuses me maybe because I haven't really learned
Excel in an organized step1, step2 way, is that I don't understand what a
Workbook is. Is it simply another name for an .xls file? and where does
that fit in with the concept of a Project?

The issue about sharing the macro, that you recommend not be in a file
called personal.xls, is relevant because if I can get this macro working I
would like to share it with some other people in my department.

Thanks,
SteveK

 

[Dave Peterson]

A "normal" workbook is a *.xls file.

You can have other files that are also used in excel--templates (*.xlt files),
addins (*.xla).

For me, it's kind of a grey area. When I say/write workbook, I usually mean
*.xls. But I guess others could mean *.wk*, *.html, *.xlw files, too.

And those excel workbooks consist of worksheets, chart sheets, macro sheets,
dialog sheets, and their project. Where the VBA code lives and where you can
change lots of properties for that workbook.

Maybe over simplified...When you're looking at excel (worksheet, chart sheet,
....), you're in excel proper. If you're in the VBE, you're looking at one of
the open workbook's projects.

But if you want to keep it an excel workbook, you can't separate the two. If
you have a workbook, then that workbook has a project.

Did this meandering shed any light?