MrExcel Publishing
Your One Stop for Excel Tips & Solutions

can i access the call stack from VBA?

Posted by ben on November 22, 2000 4:54 AM

Is it possible to get the call stack during VBA code execution? ie use it for an error routine to let me know where it's at when it all goes wrong. Or is there another way?

I know that you can only view the call stack in break mode in the VBE, but i want it to print to a log file during programme execution, and not go into the debugger.

Posted by Ivan Moala on November 25, 2000 4:33 AM

This may help you ???
Routine to log an error
Drawbacks: You will have to explcitly name your
sub routine that the call comes from.

Sub Test()
Dim ATest

On Error GoTo Log

ATest = 1 / 0
MsgBox ATest

Exit Sub
LogErr Err.Number, Err.Description, "Test()"

End Sub

Note the last string is the name of your sub
The error routine is as follows;

Option Explicit

Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function NTDomainUserName() As String
Dim strBuffer As String * 255
Dim lngBufferLength As Long
Dim lngRet As Long
Dim strTemp As String

lngBufferLength = 255
strBuffer = String(255, 0)
lngRet = GetUserName(strBuffer, lngBufferLength)
strTemp = (Left(strBuffer, lngBufferLength - 1))
NTDomainUserName = strTemp
End Function

Public Sub LogErr(strInput1 As String, strInput2 As String, strInput3 As String)

'Routine based on Micheal Waddells 04/98

Dim strMsg As String 'The msg in a msgbox explaining the error to the user.
Dim strTitle As String 'The title of that msgbox
Dim OldErrDesc As String
Dim OldErrNum As Long
'The old info is here in case there is an error
'while logging the error, so that the old error info is not erased.
Dim intFile As Integer 'This is the file number, a handle for VBA.
Dim SubR As String

OldErrNum = strInput1 'Err.Number
OldErrDesc = strInput2 'Err.Description
SubR = strInput3 'Sub routine where error occured

strMsg = "Error: " & Err.Description & Chr(13) & _
Chr(13) & "Please contact the programmer to " & _
"inform them of this error."
strTitle = Application.Name
strTitle = strTitle & " Error #" & Err.Number
MsgBox strMsg, vbExclamation + vbOKOnly, strTitle

On Error GoTo ErrWhileLogging:
'That's in case logging the error generates an error.

'Log the error in Error.log
intFile = FreeFile
Open Application.Path & "\Errors.log" For Append As #intFile
Print #intFile, ""
Print #intFile, "-----------------------------------------------------------------------------"

Print #intFile, "Error in " & ThisWorkbook.FullName & Chr(13)
Print #intFile, "Sub Routine:= " & SubR & Chr(13)
Print #intFile, "Sheet Name:= ";
Print #intFile, ThisWorkbook.ActiveSheet.Name & Chr(13)
Print #intFile, "UserName:= " & NTDomainUserName

Print #intFile, "Date&Time:= " & Now() & Chr(13)
Print #intFile, "Error #:= " & OldErrNum & Chr(13)
Print #intFile, "Discription:= " & OldErrDesc
Close #intFile

Exit Sub
strMsg = "Fatal Error: Could not log error." & Chr(13) & _
"Please contact the program vendor with the following " & _
"error information:" & Chr(13) & Chr(13) & _
"Err #" & OldErrNum & Chr(13) & OldErrDesc

strMsg = strMsg & Chr(13) & strInput1
strMsg = strMsg & Chr(13) & strInput2
strMsg = strMsg & Chr(13) & strInput3

MsgBox strMsg
End Sub



Posted by ben on November 27, 2000 2:30 AM

That looks like it will do the job nicely! Cheers
for that Ivan. I'll give it a go.

Ben Sub

HTH Ivan