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!

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

On Jan 8, 6:07*pm, David <skulkrinb...@googlemail.com> wrote:
> 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!

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)



"David" <(E-Mail Removed)> wrote in message
news:d5719cd4-5779-43a7-964b-(E-Mail Removed)...
> 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!

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
>
>
> "David" <(E-Mail Removed)> wrote in message
> news:d5719cd4-5779-43a7-964b-(E-Mail Removed)...
>> 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!
>
>

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)



"Mark Ivey" <(E-Mail Removed)> wrote in message
news:A081D9C8-E6EA-411F-B118-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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)
>>
>>
>>
>> "David" <(E-Mail Removed)> wrote in message
>> news:d5719cd4-5779-43a7-964b-(E-Mail Removed)...
>>> 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!
>>
>>

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)

"David" <(E-Mail Removed)> wrote in message
news:d5719cd4-5779-43a7-964b-(E-Mail Removed)...
> 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!

Thanks Bob,

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

Mark Ivey

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
>
>
> "Mark Ivey" <(E-Mail Removed)> wrote in message
> news:A081D9C8-E6EA-411F-B118-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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)
>>>
>>>
>>>
>>> "David" <(E-Mail Removed)> wrote in message
>>> news:d5719cd4-5779-43a7-964b-(E-Mail Removed)...
>>>> 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!
>>>
>>>
>
>

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)



"Mark Ivey" <(E-Mail Removed)> wrote in message
news:8A632317-DD75-45F8-B290-(E-Mail Removed)...
> Thanks Bob,
>
> I will give it a try the next time I need to debug my code.
>
> Mark Ivey
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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)
>>
>>
>>
>> "Mark Ivey" <(E-Mail Removed)> wrote in message
>> news:A081D9C8-E6EA-411F-B118-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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)
>>>>
>>>>
>>>>
>>>> "David" <(E-Mail Removed)> wrote in message
>>>> news:d5719cd4-5779-43a7-964b-(E-Mail Removed)...
>>>>> 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!
>>>>
>>>>
>>
>>