Saving Macros

[Guest]

I have had this problem for about five weeks now and either I do not
understand something or a Microsoft problem exists.

When saving a Macro (and for some reason they have been disappeaing) I am
assuming that the following applies.

All Open Workbooks means for every WorkBook in Excel regardless of what
Folder(Directory) the WorkBooks are in. These have been my major problem!

WorkBookName.xls I feel is the one I want. I am under the impression it
saves the Macro in this WorkBook Only! However when I close Excel and
Re-Open it, it shows that the Macros are available to All Open Workbooks. If
I forget to change it to WorkBookName.xls, it destroys all of the Macros in
all WorkBooks.

This WorkBook I think means just temporary changes until you are done with
the current WorkBook.

This dilema has cost me a fair amount of money from Microsoft Excel Support
and they have not been able to assist me at this time. They do have my code
and the same thing happens to them in India as it does here.

Naturally I have saved the code in NotePad and re-applied it when this
occurs, but it is very frustrating when someone else has the problem.

Thanks for any assistance Group!

J. Q.

 

[Chip Pearson]

John,

I think you are confusing things on the Macros dialog box. A procedure (sub
or function) exists within the VBProject component of a workbook. Within
that project, your code should be stored in a standard module (insert from
the Insert menu in the VBA editor). Do not save your code in the Sheet
modules or the ThisWorkbook code module. A single procedure is stored only
in a single workbook.

"All Open Workbooks" is relevant only when you want to run a macro. If you
select this option, all callable macros in all open workbooks are displayed
in the list of macros. "This Workbook" lists only macros that are stored in
the currently active workbook. "Personal.xls", if it exists, is usually
stored in the XLStart folder so that it will be opened automatically when
Excel starts. All of these options apply only to running an existing macro.
They have nothing to do with creating a new macro. ("All Open Workbooks"
does NOT mean that the macro will be stored in all open workbooks.)

If you are recording macros, you have the choice of saving to the currently
active workbook, the "Personal.xls" workbook, or a new workbook. If you save
to the active workbook or a new workbook, you must save that workbook.
Otherwise, the code will not be preserved.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[gimme_this_gimme_that]

One thing that might be confusing you is that you can add VBA to VB
modules (by clicking on Insert->Module in the VBE) *OR* you can add
VBA to worksheets (expand Excel Objects in the VBE and double click
the Sheet).


You might imagine that you're losing macros when you're actually been
adding VBA to a Worksheet and you just don't see when you exit and
return to the VBE.