Calling a sub name into the current sub

Resolved · Urgent Priority · Version 2003

Wed 23 Sep 2009, 13:15 Delegate 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:25 Trainer 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:06 Delegate 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:46 Trainer 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 Sub

We 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

Fri 25 Sep 2009, 14:38 Delegate Rob said...

RE: Calling a sub name into the current sub

Thanks Stephen,

I'll give it a whirl...

My fingers are crossed for Monday...

Cheers

Rob

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Adding Rows or Columns in an Excel 2010 Worksheet

If you want to add a row to an Excel spreadsheet, these are the simple steps you should take:

With your mouse, right click on the row header below where you want the new row to be added. Then, click Insert.

Follow exactly the same steps if you want to add a column to an Excel worksheet, right click on the column header, choose Insert and the new column will be inserted to the left of the selected column.

View all Excel hints and tips