ExcelBanter - What determines location of VBA code from recorded macro?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - What determines location of VBA code from recorded macro? (https://www.excelbanter.com/excel-discussion-misc-queries/453162-what-determines-location-vba-code-recorded-macro.html)

What determines location of VBA code from recorded macro?

I recorded a macro (as a basis for editing, maybe with help here).
But how are you supposed to know where to find the VBA code please?

I assumed it would be in PERSONAL.xlsb as a new module, But as you
see it's replaced whatever was in Module 8, rather than creating
Module 16.
The Record Macro dialog lets you choose what to name it and which open
file to store it in. ThisWorkbook (the active workbook) is the default.

https://dl.dropboxusercontent.com/u/...e-Location.jpg

What logic or inadvertent setting on my part gets it into Module 8?
None! -It increments the next number.

Possibly because you gave it the same name as a procedure already
existing there in Module8 of the file you chose to store it in via the
Record Macro dialog??? Excel will not create another module to store a
duplicate procedure in the same file. (Excel prompts to warn you asame
name macro already exists in the file and asks if you want to replace
it!)

Also, could I ask a few associated very basic queries please:

1. Is there any way to change 'Module 1' etc to meaningful names?
Open the Properties window in the VBE and edit its 'Name' there.

2. Or search for a macro name?
Alt+F8 opens the 'Macros' dialog where you can manage (limited) them
from its list.

3. Is there a KB shortcut to stop a macro recording, rather than the
four clicks, in my case: Classical Menu Tools Macro Stop
Recording
When the Record Macro dialog closes the Macro Recorder toolbar floats
over the active window so you can click its 'Stop' button when your
done the actions you're recording. If you don't dismiss it then you
won't have to step through the menus to stop recording!

4. With many modules open, as I had here, can I close them all at
once instead of individually?
You can just maximize any module's window so you only see the one
you've selected (dbl-click) in the Project Explorer pane!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

What determines location of VBA code from recorded macro?

GS wrote:

I recorded a macro (as a basis for editing, maybe with help here).
But how are you supposed to know where to find the VBA code please?

I assumed it would be in PERSONAL.xlsb as a new module, But as you
see it's replaced whatever was in Module 8, rather than creating
Module 16.
The Record Macro dialog lets you choose what to name it and which open
file to store it in. ThisWorkbook (the active workbook) is the default.

https://dl.dropboxusercontent.com/u/...e-Location.jpg

What logic or inadvertent setting on my part gets it into Module 8?
None! -It increments the next number.

Possibly because you gave it the same name as a procedure already
existing there in Module8 of the file you chose to store it in via the
Record Macro dialog??? Excel will not create another module to store a
duplicate procedure in the same file. (Excel prompts to warn you asame
name macro already exists in the file and asks if you want to replace
it!)

Also, could I ask a few associated very basic queries please:

1. Is there any way to change 'Module 1' etc to meaningful names?
Open the Properties window in the VBE and edit its 'Name' there.

2. Or search for a macro name?
Alt+F8 opens the 'Macros' dialog where you can manage (limited) them
from its list.

3. Is there a KB shortcut to stop a macro recording, rather than the
four clicks, in my case: Classical Menu Tools Macro Stop
Recording
When the Record Macro dialog closes the Macro Recorder toolbar floats
over the active window so you can click its 'Stop' button when your
done the actions you're recording. If you don't dismiss it then you
won't have to step through the menus to stop recording!

4. With many modules open, as I had here, can I close them all at
once instead of individually?
You can just maximize any module's window so you only see the one
you've selected (dbl-click) in the Project Explorer pane!

Thanks Garry, understood.

Terry, East Grinstead, UK

When you press RecordMacro, a dialog box opens on which there is a drop down that lets you select what workbook the code is to be put in. It also allows you to specify the name of the about to be recorded macro.

Once you are done recording the macro, if you invoke the RunMacro dialog box, you can select the macro, press Edit and you will be taken to that macro in the VBEditor, which will let you see what module (in the VBProject you selected from the Record Macro dialog) your macro was put in.

Quote:

Originally Posted by Terry Pinnell[_4_] (Post 1628375)

What determines location of VBA code from recorded macro?

When you press RecordMacro, a dialog box opens on which there is a
drop
down that lets you select what workbook the code is to be put in. It
also allows you to specify the name of the about to be recorded
macro.

Once you are done recording the macro, if you invoke the RunMacro
dialog
box, you can select the macro, press Edit and you will be taken to
that
macro in the VBEditor, which will let you see what module (in the
VBProject you selected from the Record Macro dialog) your macro was
put
in.

Well stated!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

What determines location of VBA code from recorded macro?

Thanks Mike, appreciate the follow-up.

Terry, East Grinstead, UK