98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Calling a sub name into the current sub
Calling a sub name into the current sub
Resolved · Urgent Priority · Version 2003
Wed 23 Sep 2009, 13:15Delegate Rob said...
Rob has attended:
Excel VBA Advanced course
Calling a sub name into the current sub
To automate my error logging, I would like to know which procedure I am running and what was parent procedure that called it. The sub could look like this
Sub mySub()
Dim strSubName As String
Dim strCallerSubName As String
strSubName = ThisSub.name 'Name of the current sub ie mySub
strCallerSubName = ThisSub.Parent.Name 'Name of caller sub
End Sub
Is there any excel object available which will tell me what is "ThisSub" within the sub you are running in the above example?
Thanks
Rob
Thu 24 Sep 2009, 11:25Trainer Stephen said...
RE: Calling a sub name into the current sub
Hi Rob
Thank you for your question
A procedure is not an object, so you cannot reference its name property as you did above, because it doesn't have one.
Can you elaborate a bit for me. How would you use the variables above. Where you planning to add watches to the variables? or where you going to use it in your error handler?
Thanks
Stephen
Thu 24 Sep 2009, 16:06Delegate Rob said...
RE: Calling a sub name into the current sub
Hi Stephen,
I would use this in conjunction with an error handler...
I have around 40 or so subs scattered over various modules, and many of these subs are are pulled together under one event so for example.
Sub EventHandler()
Call Sub1
Call Sub2
Call Sub3
Call Sub4
End Sub
where say...
Sub Sub1()
Call Sub5
Call Sub6
Call Sub7
End Sub
...etc.
I have created a separate sheet which lists the 'Date & Time' of an error and the 'Name' of the sub in which the error occured.
As part of the code which I attach here I wanted to add the name of the sub in a more intuitive way so that if I changed the name of the sub for any reason the 'Name' carried across to the ErrorLog sheet would be correct without me having to manually change the name in " " commas...
Sub ForExample()
Application.ScreenUpdating = False
' Main body of sub starts here...
ThisWorkbook.Sheets("Sheet1").Range("A1").Copy
ThisWorkbook.Sheets("Sheet1").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
' Main body of sub ends here
' Error Handler starts here
GoTo TheEnd:
AnError:
Application.ScreenUpdating = True
MsgBox "The following error occurred in
Sub 'ForExample':" & vbCrLf & Err.Description, _
vbCritical, "Error Occurred - Process Aborted"
Application.ScreenUpdating = False
strUserErrorMessage = InputBox("Enter a brief description of the error, including the button you clicked prior to the error occurring.", "Error Log Detail")
ThisWorkbook.Sheets("ErrorLog").Visible = True
ThisWorkbook.Sheets("ErrorLog").Select
Range("A1").Select
Set rngErrorLog = ThisWorkbook.Sheets("ErrorLog").Range("A1").CurrentRegion
ActiveCell.Offset(rngErrorLog.Rows.Count, 0).Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Copy
Selection.PasteSpecial xlValues
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Select
ActiveCell.value = "ForExample"
ActiveCell.Offset(0, 1).Select
ActiveCell.value = strUserErrorMessage
ThisWorkbook.Sheets("ErrorLog").Visible = False
TheEnd:
Range("A1").Select
Application.CutCopyMode = False
...
I would prefer to say for instance...
MsgBox "The following error occurred in
Sub " & ThisSub.Name & ":" & vbCrLf & Err.Description, _
vbCritical, "Error Occurred - Process Aborted"
and...
ActiveCell.value = ThisSub.Name
instead of having to type out the sub name in inverted commas.
Hope this makes sense.
Cheers
Rob
Fri 25 Sep 2009, 11:46Trainer Stephen said...
RE: Calling a sub name into the current sub
Hi Rob
This is an interesting problem. I can find no way to reference the name of a procedure by way of a method or VB function. This does not mean that it can't be done. I will have to consult some additional references over the weekend. If I find a solution I will follow up on this post.
However, I have worked out a way to do what you want that is less than elegant, but it seems to work. (The technical term for this is a "cludge")
Dim ErrorLocator() As String
Dim LineNumber As Long
Dim IntVal As Integer
Sub Test()
LineNumber = LineNumber + 1
ReDim Preserve ErrorLocator(LineNumber)
ErrorLocator(LineNumber) = "test"
End Sub
Sub Test1()
LineNumber = LineNumber + 1
ReDim Preserve ErrorLocator(LineNumber)
ErrorLocator(LineNumber) = "test1"
IntVal = LineNumber / 0
End Sub
Sub Test2()
LineNumber = LineNumber + 1
ReDim Preserve ErrorLocator(LineNumber)
ErrorLocator(LineNumber) = "test2"
End Sub
Sub main()
On Error GoTo errorhandler
Call Test
Call Test1
Call Test2
errorhandler:
Dim LastLine As Long
LastLine = UBound(ErrorLocator)
MsgBox Err.Description & vbCrLf & _
" Has occured in " & vbCrLf & ErrorLocator(LastLine)
End SubWe have declared a dynamic array in the declerations section. At the start of each procedure the following
LineNumber = LineNumber + 1
ReDim Preserve ErrorLocator(LineNumber)
ErrorLocator(LineNumber) = "test2"Increases the line number by one and then redims the array, using the preserve option to keep existing records.
essentially, we are adding a new row to the array. We then write the name of the procedure as a literal to the array.
In the error handler we simply retrieve the last procedure as this willbe where the error happened
Hop this helps
Regards
Stephen
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Adding Rows or Columns in an Excel 2010 WorksheetIf you want to add a row to an Excel spreadsheet, these are the simple steps you should take: |
