step through VBA for debugging -- how! 3

[smandoli9]

I am used to using [blue]F8[/blue] and [blue]shift-F8[/blue] to step through code. I'm used to breakpoints and the [highlight]Stop[/highlight] command, and I ignore the Watch feature but I know it's there.

I want a toggle command somewhere in the VBA editor that breaks the the running code as soon as a line of VBA is called. Like having a [highlight]Stop[/highlight] command at the start of every sub and function. So when I'm troubleshooting forms automation, I can use the form in form view, and as soon as an event fires that has code, there my VBA debug cursor will be.

My current method is to find the spot in the code where code is first invoked (or I hope it is, at least), and stick a break point there. Unsatisfactory. What's the way to do this?

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[smandoli9]

With no solution forthcoming, I propose a sub routine that runs through all [highlight]Application.Modules[/highlight] and amends every sub and function. The title line,

Sub (or Function) DoesSomething()

changes to

Sub DoesSomething():   Stop

After debugging, another routine would be called to "pull out the Stops", so to speak.

The Step-Over command would be useless as the next [highlight]Stop[/highlight] would immediately break the code again. I would use the Step-Out command.

Also, my routine would have to account for sub/function title lines that spill onto multiple lines.

Any thoughts on that?

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[PWise]

when we Migrated from jet to sql sever and were still running on the old be we ran this sub to add "dbseechanges" to all mydb.execute
Sub AddSeeChanges()
On Error GoTo AddSeeChanges_err
Dim db As Database
Dim mdl As Module
Dim cnt As Integer
Dim rst As Recordset
Dim LineContents$, LineContentsNew$, RightLineContents$
Dim startline&, startcolumn&, endline&, endcolumn&
Dim NeedsSaving&
'Things to check
'1. Sometimes a line end isn't the end of the code line if ti has a '_'.
'2. Execute isn't allways currentdb.execute
'3. Before Appending 'dbSeeChanges' check if one option is used we'll have to use ' + dbSeeChanges'
'4. if it has another option like dbfailonerror

Const ModuleType = -32761
Const FormType = -32768
Const ReportType = -32764
NeedsSaving = acSaveNo
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Name, Type From msysObjects WHERE Type IN ( " & FormType & ", " & ModuleType & ", " & ReportType & " )")
rst.MoveFirst

Do While Not rst.EOF

If rst("type") = FormType Then

DoCmd.OpenForm rst("Name"), acDesign
DoCmd.OpenModule "Form_" & rst("Name")
Set mdl = Modules("Form_" & rst("Name"))

ElseIf rst("type") = ReportType Then

DoCmd.OpenReport rst("Name"), acDesign
DoCmd.OpenModule "Report_" & rst("Name")
Set mdl = Modules("Report_" & rst("Name"))

Else
If rst("Name") = "modSQLTables" Then GoTo NoModule
DoCmd.OpenModule rst("Name")
Set mdl = Modules(rst("Name"))

End If

'Debug.Print "St: " & mdl.Name & ": " & mdl.CountOfLines

Do While mdl.Find("Execute", startline, startcolumn, endline, endcolumn, True)

LineContents = Trim(mdl.Lines(startline, Abs(endline - startline) + 1))

'Let's check if it's not a currentdb.execute or it has dbseechanges
'Nothing needs to be done
If Mid(LineContents, startcolumn - 1, 1) <> "." _
Or InStr(LineContents, "dbseechanges") > 0 _
Or Right(LineContents, 7) = "Execute" Then
'Nothing needs to be done

'Let's check if line ends with a '_' then just mark
ElseIf Right(LineContents, 1) = "_" Then
Debug.Print mdl.Name & ", " & startline & ", " & LineContents
Else
RightLineContents = IIf(Left(Trim(Mid(LineContents, startcolumn + 7)), 1) = "(", ")", "")
LineContentsNew = Left(LineContents, Len(LineContents) - Len(RightLineContents))
LineContentsNew = LineContentsNew + IIf(InStr(LineContents, "dbFailOnError") > 0, " + ", " , ")
LineContentsNew = LineContentsNew + " dbseechanges"
NeedsSaving = acSaveYes
mdl.ReplaceLine startline, LineContentsNew

End If
LineContentsNew = ""
'Debug.Print startline
startline = startline + 1: startcolumn = 0: endline = 0: endcolumn = 0

Loop

startline = startline = 0: startcolumn = 0: endline = 0: endcolumn = 0
Do While mdl.Find("OpenRecordset", startline&, startcolumn&, endline&, endcolumn&, True)
LineContents = Trim(mdl.Lines(startline, Abs(endline - startline) + 1))

'Let's check if it's not a currentdb.execute or it has dbseechanges
'Nothing needs to be done
If Mid(LineContents, startcolumn - 1, 1) <> "." _
Or InStr(LineContents, "dbseechanges") > 0 Then
'Nothing needs to be done

'Let's check if line ends with a '_' then just mark
ElseIf Right(LineContents, 1) = "_" Then
Debug.Print mdl.Name & ", " & startline & ", " & LineContents
Else
LineContentsNew = Left(LineContents, Len(LineContents) - 1) + ", dbopendynaset, dbseechanges)"
NeedsSaving = acSaveYes
mdl.ReplaceLine startline, LineContentsNew
End If
LineContentsNew = ""
startline = startline + 1: startcolumn = 0: endline = 0: endcolumn = 0
Loop
startline = startline = 0: startcolumn = 0: endline = 0: endcolumn = 0
LineContentsNew = ""

If NeedsSaving = acSaveYes Then Debug.Print mdl.Name & "--> Saved"

DoCmd.Close acModule, mdl.Name, NeedsSaving

NoModule:

If rst("type") <> ModuleType Then DoCmd.Close IIf(rst("type") = FormType, 2, 3), rst("Name"), NeedsSaving
NeedsSaving = acSaveNo
rst.MoveNext
Loop
Exit Sub

AddSeeChanges_err:
If err = 2517 Then
'Debug.Print "St: " & rst("name") & " No module"
Resume NoModule
Else
Debug.Print rst("Name") & ": " & err.Description

Stop
End If
End Sub

 

[willir]

As an after thought, instead of inserting "STOP" throughout your modules, what about calling a public routine. And the routine contains STOP statement. This way, you can comment the STOP statement when you have finished debugging, or remove the comment so the STOP statement is excuted to debug.

Something like

Private Sub AModule()
StopOrGo

...
End Sub

And then in public module

Public StopOrGo()
   'Comment or uncomment STOP statement
   STOP
End Sub

Your problem will be subroutines that have error trapping. The way error traps work is to fail other to parent module if an error is encountered which may cause problems.

By-the-way pwise, stars to you for great code.

Richard

 

[smandoli9]

Thank you team. I think I'll try this. Pwise' code is daunting but Willir gives it approval, so why not make a copy of the DB and run the thing.

(I admit I was hoping for a single menu command in the VBA editor, but this does look entertaining. And I'm glad I didn't have to repost the question under a less imbecilic subject heading; I'm sure I frightened away some other good advice.)

If this works, do you guys think it's worth a Tip posting? I'll return to this thread if I get it running.

WILLIR:
About subroutines: this is for hardcore debugging, and "Break on all errors" will be switched on. So error trapping in nested procedures will not come into play ... am I missing something?

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[smandoli9]

FOLLOW-UP

I never tried to implement this. Lost confidence it was worth the effort for me.

My fantasy feature for VBA is a step-through that pauses on every VBA events as it runs.

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[grande]

That would be ideal, eh smandoli9? I'm also looking for something to do this for me. I think I might go willir's method.

 

[JeremyNYC]

It wouldn't work for retrofitting code, but I've got code on my old website (

http://www.abcdataworks.com)

that builds the shell of every sub or function I write. It could be easily updated to include a call in every routine to willr's proposed StopMeNow function.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

http://metrix.fcny.org

 

[willir]

Great to hear from you Jeremy. Congrats on the new job. A lot of hard work behind your new title I am sure.

 

[JeremyNYC]

Willr,

Thanks much! It's great that this is such a community that people notice things like that.

The new job is really pretty amazing--great people, great product, great learning opportunity. It has been a tremendous amount of work, but only in short bursts, so it's really quite alright.

In any case, thanks again, and cheers to you for all the work you're putting in here. I do hope to get to hang out here a bit more in the reasonably near future.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

http://metrix.fcny.org