Seeing VBA commands without using the Macro Recorder

[alon91]

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

 

[Tom Urtis]

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.

 

[stanleydgromjr]

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]

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]

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]

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.