A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

What determines location of VBA code from recorded macro?



 
 
Thread Tools Display Modes
  #1  
Old February 25th 17, 10:33 AM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default 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.

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

What logic or inadvertent setting on my part gets it into Module 8?

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

1. Is there any way to change 'Module 1' etc to meaningful names?
2. Or search for a macro name?
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

4. With many modules open, as I had here, can I close them all at once
instead of individually?

--
Terry, East Grinstead, UK
Ads
  #2  
Old February 26th 17, 03:19 AM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 695
Default 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
  #3  
Old March 1st 17, 01:32 PM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default 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
  #4  
Old March 4th 17, 08:05 PM
mikerickson mikerickson is offline
Junior Member
 
First recorded activity by ExcelBanter: May 2007
Location: Winters CA
Posts: 23
Default

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_] View Post
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.

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

What logic or inadvertent setting on my part gets it into Module 8?

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

1. Is there any way to change 'Module 1' etc to meaningful names?
2. Or search for a macro name?
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

4. With many modules open, as I had here, can I close them all at once
instead of individually?

--
Terry, East Grinstead, UK
  #5  
Old March 4th 17, 08:54 PM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 695
Default 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
  #6  
Old March 4th 17, 10:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default What determines location of VBA code from recorded macro?

Thanks Mike, appreciate the follow-up.

Terry, East Grinstead, UK
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Recorded Macro Works, Code Doesn't Jay Excel Programming 4 August 11th 08 02:20 PM
Improving the code from a recorded macro Montse Excel Programming 2 September 17th 07 12:23 AM
Tidying Recorded Macro Code Big H Excel Programming 11 September 16th 07 06:29 AM
Call recorded macro from VBA code [email protected] Excel Programming 1 September 2nd 05 09:22 PM
looking to simplify a recorded macro with code HM Excel Programming 2 February 10th 04 06:35 PM


All times are GMT +1. The time now is 12:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.