VBA breakpoints advanced question

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
I would like a quick way to put a breakpoint on every routine in every code object. The desire is to have something better than

F9
Ctrl-Down arrow
up-arrow
F9
etc.

Obviously I'm one of those that uses the keyboard heavily to get things done fast. This is pretty good, though doing it rapidly by rote is fouled by some comment lines. But even this still suffers from having to locate other modules, worksheet code. form code, etc. (footnote **)

Can this be automated by some personal macro code routine, keystroke macros, or external process? Keep in mind that all of this must happen in a VBA window, severely limiting what you can do.

For perspective, sometimes we inherit massive workbooks with tons and tons of sheets and code. My desire is to learn/debug an app. by putting breakpoints on all possible code so I can see exactly what happens (and EVERYTHING that happens) when I do some action in the workbook. (And yes, I realize that no matter what, I'll still miss the File_Open unless I start the thing with shift-open, or insert a STOP statement and reopen.)

TIA

------------------------------------------------------------------------

** I do have a trick for finding every module(et al) that has code, which is close to if not foolproof. It is essentially this:
Ctrl-F for the letter u with alt-c ("search entire project")
Ctrl-End
F3
repeat until all modules found

Anyone see a flaw here? This catches every "Sub" and "Function" but I wonder if there's any other CODE it might miss, such as in a .CLS --- or in a "completely" external routine.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I don't see anything in the VBIDE library in the object browser that looks like a way to programatically set breakpoints. And I've never had debug.assert work properly for me.

The only thing I could think to do would be to use Chip's tips on programming to the VBE and scan each module for each subroutine/function and add a conditional compile directive like
Code:
#IF yourswitch
    Stop
#ENDIF
at the top of each subroutine and then a
Code:
#Const yourswitch=TRUE
at the top of each module.

Help at all?
 
Upvote 0
Greg Truby said:
Help at all?
Hell yes it does! Way to go. Now at least I have to scour through the code only once - not every time I crash --- errr, I mean reopen the workbook.

Note - I know you were reciting approximate code (so "no foul") but I'll indicate that "then" needs to be here:
#If yourswitch then

(So, as you can see, I've been trying it out, in case there's any doubt as to whether your post was all for naught, hee hee)
 
Upvote 0
GIA - yes, a "then" would probably help :oops: And my thinking is that then all you need to is switch the constant at the top of the module if you want to enable-disable the STOPS.<hr />

Hi Tushar,

Have you tried MZtools yourself and if so, is it pretty good?

Thanks,
 
Upvote 0
Those references are definitely in the proper spirit, though after looking through all their features I didn't see this particular one. I'm glad you pointed them out because they deserve the look - and at least Stephen has a fine history and credentials.
 
Upvote 0
Greg Truby said:
{snip}Hi Tushar,

Have you tried MZtools yourself and if so, is it pretty good?

Thanks,

I did on the recommendation of a couple of fellow MS MVPs. Turned out its value to me was marginal. My guess is that my development style (highly modularized with few if any globals) does not mesh well with MZTools. Maybe, that should not be as much of a surprise since, after all, one would expect MZTools to be designed for the broader, more conventional, audience.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top