Debugging Advice Wanted

[David]

Hello

I'm debugging a fairly complex piece of VBA and using the "MsgBox"
function at various points of the program to check what variables are
etc.

This is quite time consuming due to the nature of my code, is there a
better way to debug? Is there a "trace" or similar function in VBA?

Any advice gratefully received thanks!

 

[carlo]

You can use the
Debug.print YourVar
which will result in printing the value of YourVar in the immediate
window (if not visible go to the view-menu)
Otherwise you can open the locals window which shows you the current
status of your variables.
Then there's also the Watch window where you can check the variables
according to the status.

I think all of those are better than to use the msgbox, because you
don't need to change to excel and you don't have to click them to go
away.

Hope that helps

Carlo

 

[Bob Phillips]

There is no automatic trace facility, you would need to build it yourself.

You can avoid MsgBox using Debug.Print, which will write those results to
the Immediate window. A better way IMO is to build yourself a trace facility
that you can turn on/off with a simple Boolean switch. This trace facility
would write the results to a text log file, that you can examine at leisure
afterwards.

There are also various debugging facilities, such as inserting break points,
using the Watch window to watch particular variables, etc.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mark Ivey]

Bob,

That is an interesting alternative. Do you have any example snippets to do
something like this. I would be interested in learning more...

Mark Ivey

 

[Bob Phillips]

Off the top, you will need to iron out any quirks. It writes to a logfile in
the workbooks directory, where the logfile name is defined in a variable
called mmErrorLogName.

Dim mpFullName As String

Const mpProcedure As String = "this_procedure_name"

mpFullname = "[" & Filename & "]" & ModuleName & "." & mpProcedure

... some normal code

If TraceOn Then

Call LogError(19999, mpFullName, "some details"
Call LogError(19999, mpFullName, "some more details"
Call LogError(19999, mpFullName, "even more details"
End If

... some more ordinary code

If TraceOn Then

Call LogError(19999, mpFullName, "yet more details"
End If

I pass 1999 as the error number here as I also use this logging routine to
log real errors, 19999 is a non-error here.

TraceOn would be a global bollean variable that you can set at the app start
(you could even use conditional compilation).

'---------------------------------------------------------------------------
Public Function LogError(ByVal ErrorNum As Long, _
ByVal Fullname As String, _
ByVal ErrorMsg As String)
'---------------------------------------------------------------------------
Dim mpText As String
Dim mpPath As String
Dim mpFilenum As Long
Dim j As Long

mpPath = ThisWorkbook.Path
If Right$(mpPath, 1) <> "\" Then mpPath = mpPath & "\"
mpText = " " & Fullname & ", Error " & CStr(ErrorNum) & ": " &
ErrorMsg

mpFilenum = FreeFile()
On Error GoTo 0
Open mpPath & mmErrorLogName For Append As #mpFilenum
Print #mpFilenum, Format$(Now, "dd mmm yyyy hh:mm:ss"); mpText
Print #mpFilenum,
Close #mpFilenum
End Function


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

I have some notes about debugging at http://www.cpearson.com/Excel/Debug.htm
..


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Mark Ivey]

Thanks Bob,

I will give it a try the next time I need to debug my code.

Mark Ivey

Off the top, you will need to iron out any quirks. It writes to a logfile
in the workbooks directory, where the logfile name is defined in a
variable called mmErrorLogName.

Dim mpFullName As String

Const mpProcedure As String = "this_procedure_name"

mpFullname = "[" & Filename & "]" & ModuleName & "." & mpProcedure

... some normal code

If TraceOn Then

Call LogError(19999, mpFullName, "some details"
Call LogError(19999, mpFullName, "some more details"
Call LogError(19999, mpFullName, "even more details"
End If

... some more ordinary code

If TraceOn Then

Call LogError(19999, mpFullName, "yet more details"
End If

I pass 1999 as the error number here as I also use this logging routine to
log real errors, 19999 is a non-error here.

TraceOn would be a global bollean variable that you can set at the app
start (you could even use conditional compilation).

'---------------------------------------------------------------------------
Public Function LogError(ByVal ErrorNum As Long, _
ByVal Fullname As String, _
ByVal ErrorMsg As String)
'---------------------------------------------------------------------------
Dim mpText As String
Dim mpPath As String
Dim mpFilenum As Long
Dim j As Long

mpPath = ThisWorkbook.Path
If Right$(mpPath, 1) <> "\" Then mpPath = mpPath & "\"
mpText = " " & Fullname & ", Error " & CStr(ErrorNum) & ": " &
ErrorMsg

mpFilenum = FreeFile()
On Error GoTo 0
Open mpPath & mmErrorLogName For Append As #mpFilenum
Print #mpFilenum, Format$(Now, "dd mmm yyyy hh:mm:ss"); mpText
Print #mpFilenum,
Close #mpFilenum
End Function


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Bob,

That is an interesting alternative. Do you have any example snippets to
do something like this. I would be interested in learning more...

Mark Ivey

 

[Bob Phillips]

I would try it out before then if I were you, iron it out, and adjust it to
your needs. Then we you really need such a tool, it is primed and ready to
go. When you need it, you want have time to tune it, life is like that.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thanks Bob,

I will give it a try the next time I need to debug my code.

Mark Ivey

Off the top, you will need to iron out any quirks. It writes to a logfile
in the workbooks directory, where the logfile name is defined in a
variable called mmErrorLogName.

Dim mpFullName As String

Const mpProcedure As String = "this_procedure_name"

mpFullname = "[" & Filename & "]" & ModuleName & "." & mpProcedure

... some normal code

If TraceOn Then

Call LogError(19999, mpFullName, "some details"
Call LogError(19999, mpFullName, "some more details"
Call LogError(19999, mpFullName, "even more details"
End If

... some more ordinary code

If TraceOn Then

Call LogError(19999, mpFullName, "yet more details"
End If

I pass 1999 as the error number here as I also use this logging routine
to log real errors, 19999 is a non-error here.

TraceOn would be a global bollean variable that you can set at the app
start (you could even use conditional compilation).

'---------------------------------------------------------------------------
Public Function LogError(ByVal ErrorNum As Long, _
ByVal Fullname As String, _
ByVal ErrorMsg As String)
'---------------------------------------------------------------------------
Dim mpText As String
Dim mpPath As String
Dim mpFilenum As Long
Dim j As Long

mpPath = ThisWorkbook.Path
If Right$(mpPath, 1) <> "\" Then mpPath = mpPath & "\"
mpText = " " & Fullname & ", Error " & CStr(ErrorNum) & ": " &
ErrorMsg

mpFilenum = FreeFile()
On Error GoTo 0
Open mpPath & mmErrorLogName For Append As #mpFilenum
Print #mpFilenum, Format$(Now, "dd mmm yyyy hh:mm:ss"); mpText
Print #mpFilenum,
Close #mpFilenum
End Function


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Bob,

That is an interesting alternative. Do you have any example snippets to
do something like this. I would be interested in learning more...

Mark Ivey




There is no automatic trace facility, you would need to build it
yourself.

You can avoid MsgBox using Debug.Print, which will write those results
to the Immediate window. A better way IMO is to build yourself a trace
facility that you can turn on/off with a simple Boolean switch. This
trace facility would write the results to a text log file, that you can
examine at leisure afterwards.

There are also various debugging facilities, such as inserting break
points, using the Watch window to watch particular variables, etc.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hello

I'm debugging a fairly complex piece of VBA and using the "MsgBox"
function at various points of the program to check what variables are
etc.

This is quite time consuming due to the nature of my code, is there a
better way to debug? Is there a "trace" or similar function in VBA?

Any advice gratefully received thanks!