Seeing VBA commands without using the Macro Recorder

alon91

New Member
Joined
Jun 9, 2006
Messages
7
We've all done this before -- we need to figure out how to do something in VBA, so we use the Macro Recorder (Tools -> Macro -> Record New Macro), do the action, then go to the VBA editor and see what was recorded. To clean up we delete the module.


Is there a way to do this without going through this process? Is there a plug in that will show every action a user takes as the VBA code in a separate window? Or is the Macro Recorder the only way to get this sort of insight?


Thanks!
Alon
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,255
Not sure exactly what you are asking, but if you want to see sausage code being made in the VBE as you perform manual actions on a worksheet, follow these steps:


Close out all of Excel if you have it open.

Open Excel, which will also open a new workbook.

From the application title bar, which is the very top bar, to the left of the close "X" button is a Restore Down button. Click on that to reduce and resize the window of your application so it covers the left vertical half of your monitor's viewing area.

Turn on the macro recorder and click OK to record a new macro.

Press Alt+F11 to access the VBE.

Press Ctrl+R to expose the VBA Project Window.

Expand the yellow Module1 folder that was just created when you turned on the macro recorder. Double-click the Module1 object module to make the module and its contents visible in the VBE's right vertical pane.

Reduce the VBE's window size the same way you did for the Excel application window, but now, position that VBE window so it covers the right vertical half of your monitor.

Go back to the workbook's worksheet window (the macro recorder is still turned on at this time) and perform a few actions. In so doing, you will see the code produced by those actions in real time in the VBE's macro module window.

Turn off the macro recorder when you have seen enough.
 
Joined
Jul 30, 2006
Messages
3,656
Good question.

Here is a way to watch the macro being created.

Size the spreadsheet horizontally on the screen to, say 2/3, of the total vertical view.

Open the VBA Editor.
Create a Module.
Size the VBA Editor screen horizontally on the screen to, say 1/3, of the total vertical view.

Go back to the spreadsheet and click on Tools -> Macro -> Record New Macro, and watch the macro being recorded - the VBA Editor screen will scroll.


Have a great day,
Stan
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
Is there a reason you want to do this w/o the Macro Recorder? Maybe, if you explain the reasoning behind the request someone may be able to offer an alternative...
We've all done this before -- we need to figure out how to do something in VBA, so we use the Macro Recorder (Tools -> Macro -> Record New Macro), do the action, then go to the VBA editor and see what was recorded. To clean up we delete the module.


Is there a way to do this without going through this process? Is there a plug in that will show every action a user takes as the VBA code in a separate window? Or is the Macro Recorder the only way to get this sort of insight?


Thanks!
Alon
 

alon91

New Member
Joined
Jun 9, 2006
Messages
7
Is there a reason you want to do this w/o the Macro Recorder? Maybe, if you explain the reasoning behind the request someone may be able to offer an alternative...

Sure -- I just feel like there's a non-trivial amount of overhead associated with the Macro Recorder process: you have to go through multiple mouse clicks to initiate the recorder, several more to locate the newly created macro, and then more to delete the macro or module that was created.

Going through this process a dozen or more times a day is tedious.

I also feel like seeing a continuous stream of VBA could be an interesting way to learn more about how Excel works. I bet I'd see things that I can't discover now because I don't even think about recording/reviewing actions.

I was hoping for a method that would allow me to just see these happen in real-time, ideally in something like a pop-up window so I wouldn't need to rely on resizing/multiple windows (although that's not a bad suggestion -- thanks to the guys who wrote in with that), and without actually saving the code so I wouldn't need to delete afterwards.

I know it's not super-critical functionality, but I was hoping someone knew of an Excel plug-in that does this. I know this type of thing would help me be more efficient when programing VBA.

Thanks for all your answers, and please do post if you have any other ideas!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
If you are concerned about the number of clicks to start the macro recorder why not add the macro recorder button to a toolbar via Customize...

You could also add a button to open the VBA editor.

And you could probably even take it further and add a button that opens a particular module.

For that last part you would need some code.:)
 

Forum statistics

Threads
1,136,347
Messages
5,675,235
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top