Capture active macro name as a variable 1

[remeng]

Hi Folks,

Is there a way to capture the active macro's name as a variable? I am trying to create an error log which declares what macro has an error.

sub test_code()

some code

on error

[indent]goto error_capture[/indent]


error_capture:

'capture macro name

variable = test_code


end sub

Thanks for the help,

Mike

 

[Andrzejek]

The only way I know is to hard-code the name:

sub [red]test_code[/red]()
...
error_capture:
[green]
'capture macro name[/green]
string_variable = [red]"test_code"[/red]
end sub

:-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Hopefully someone can think of a better work around. I did see this article, but I'm not sure it even works because it errors when I try and run it. Link

I wanted to avoid something like.

sub test

error_var="test"

call macro_rec(error_var)

end sub

There are just a lot of macros in the project and the less I need to program, the better.

 

[mintjulep]

The solution you linked to is for VB6. It may or may not be adaptable to VBA.

Some Googling suggests that there might be a way.

But the complexity seems to outweigh the potential benefit.

Suggest that you take a step back and reevaluate what you are trying to accomplish.

 

[SkipVought]

The BASIC issue (double entendre here) is coding discipline. In every procedure you ALWAYS do the same things like:

1) minimum documentation annotations
2) complete all blocks (With…End With, If…Else…Endif, For…Next ect) immediately
3) declare all variables (Use Option Explicit)
4) assign error_val with procedure name
5) error trapping

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mintjulep]

Possible solutions here

https://stackoverflow.com/questions/23945321/how-to-get-the-procedure-or-function-name-at-runtime

As I noted earlier, complexity:benefit does not appear favorable.

 

[remeng]

I think I need to just hard code the macro name and pass that as a variable to the macro that writes the error logs. It sucks but it might be easier than messing with more complex code.

At least I can setup 1 variable and 1 call to initiate a common error logging method. 2 to 3 lines of added code per macro and 1 new universal error logging macro.

 

[Andrzejek]

Hopefully NOT this;

sub test
error_var="test"
call macro_rec(error_var)
end sub

but something simple like this:

sub test
[s]error_var="test"[/s]
call macro_rec("test")
end sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

To what end?

How will knowing the name of the function that errored help you debug?

You'll need more information to do anything useful.

At the very least, the values of the parameters passed to the function when the error occurred.

 

[combo]

I'm with mintjulep. Watch window, Locals window, Call stack, breakpoints, 'break on all errors' setting, right-click popup menu in code module are your friends in debug-time. IMHO only expected errors should be handled, otherwise control of code is a nightmare.

combo

 

[remeng]

So the reason I was hoping to get code to pull the macro name was so that I could copy and paste it directly into all of the macros without having to write more macro specific code.

I am planning to write the error description in the error handling section of the macro and pass that as a variable to the error recording macro "macro_rec".

It's somewhat save time here, but customize there. It might not be worth the extra time saved if I am already writing the description.

It was somewhat of a research question. Great if there is a solution, ok if there wasn't since I had an alternate method.

Thanks everyone!

 

[Andrzejek]

>I am planning to write the [blue]error description[/blue] in the error handling section of the macro and pass that as a variable to the error recording macro "macro_rec".
>if I am already writing the description.

You don't have to:

Sub Test()

On Error GoTo error_capture

Debug.Print 2 / 0

error_capture:

Call macro_rec("test", [blue]Err.Description[/blue])

End Sub

Sub macro_rec(ByRef strErrLocation As String, ByRef [blue]strErrDescr[/blue] As String)


End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

> hat I could copy and paste it directly into all of the macros without having to write more macro specific

Well - you can automate that "having to write more macro specific"

You just need some code like this (this just inserts a comment with the relevant proc name into each proc, but obviously this can actually be any code you like

[COLOR=blue][COLOR=green]' Requires reference to Microsoft Visual Basic for Applications Extensibility library[/color]
Public Sub MacroPatch()
    Dim vbProj As VBProject
    Dim vbComp As VBComponent
    Dim lp As Long
    Dim lastproc As String
    
    Set vbProj = Workbooks("examplemacrosheet.xlsm").VBProject [COLOR=green]' hard coded for this example[/color]

    For Each vbComp In vbProj.VBComponents
        With vbComp.CodeModule
            For lp = 1 To .CountOfLines
                If .ProcOfLine(lp, vbext_pk_Proc) <> lastproc And .ProcOfLine(lp, vbext_pk_Proc) <> "MacroPatch" And .ProcOfLine(lp, vbext_pk_Proc) <> "" Then
                    lastproc = .ProcOfLine(lp, vbext_pk_Proc)
                    .InsertLines .ProcBodyLine(lastproc, vbext_pk_Proc) + 1, "' This could be anything, and we know the proc name: " & lastproc
                End If
            Next
        End With
    Next
End Sub
[/color]

 

[remeng]

Here was my final code. It works so I can't complain.

Sub error_rec(error_msg, error_macro_name) '<---- macro pass variables

On Error GoTo error_rec_fail

If error_msg = Null Or error_msg = "" Or error_msg = 0 Or error_msg = Empty Or error_macro_name = Null Or error_macro_name = "" Or error_macro_name = 0 Or error_macro_name = Empty Then

    GoTo error_rec_fail

Else: End If


todays_date_time = Format(Now, "MM/DD/YYYY HH:MM Am/Pm")

user_name = VBA.Interaction.Environ$("UserName")    'Currently Logged In User Name

'Index Columns

err_date_time_index = ThisWorkbook.Sheets("Macro Error Log").ListObjects("macro_errors").ListColumns("Error Date and Time").Index
err_macro_name_index = ThisWorkbook.Sheets("Macro Error Log").ListObjects("macro_errors").ListColumns("Error Macro ID Name").Index
err_user_name_index = ThisWorkbook.Sheets("Macro Error Log").ListObjects("macro_errors").ListColumns("User Name").Index
err_details_index = ThisWorkbook.Sheets("Macro Error Log").ListObjects("macro_errors").ListColumns("Error Details").Index


'Count active rows

err_record_count = ThisWorkbook.Sheets("Macro Error Log").Range("macro_errors").ListObject.ListRows.Count



If err_record_count = 0 Then

    err_new_record_row = 2

    Else: err_new_record_row = err_record_count + 2

End If

ThisWorkbook.Sheets("Macro Error Log").Cells(err_new_record_row, err_date_time_index).Value = todays_date_time
ThisWorkbook.Sheets("Macro Error Log").Cells(err_new_record_row, err_user_name_index).Value = user_name
ThisWorkbook.Sheets("Macro Error Log").Cells(err_new_record_row, err_macro_name_index).Value = error_macro_name
ThisWorkbook.Sheets("Macro Error Log").Cells(err_new_record_row, err_details_index).Value = error_msg

GoTo end_macro

error_rec_fail:

MsgBox "An unknown error has occured and cannot be recorded to the Macro Error Log." & Chr(13) & Chr(13) & "Please contact the macro Administrator if this problem occurs again.", vbCritical + vbOKOnly, "A CRITICAL ERROR HAS OCCURRED"

end_macro:


End Sub

Here is the code that I am adding to the host macro that can experience the error.

On Error GoTo Record_error

Some error occurs here


GoTo end_macro

Record_error:

error_msg = "hello error"
error_macro_name = "bob"


Call error_rec(error_msg, error_macro_name)

end_macro:

End Sub

 

[Andrzejek]

If the error_msg (Err.Description?) will always be a String, and error_macro_name will always be a String, why declare them as Variants in: [tt]
Sub error_rec([red]error_msg[/red], [red]error_macro_name[/red]) [green]'<---- macro pass variables AS VARIANTS[/green][/tt]

and you have to check all of this:
[pre]
If error_msg = Null Or _
error_msg = "" Or _
error_msg = 0 Or _
error_msg = Empty Or _
error_macro_name = Null Or _
error_macro_name = "" Or _
error_macro_name = 0 Or _
error_macro_name = Empty Then
[/pre]
With Strings, you would simply do:
[tt]
If Len(Trim(error_msg)) = 0 Or Len(Trim(error_macro_name)) = 0 Then
[/tt]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

So, the user does something that doesn't work.

And this logs the user name, time, erroring function and the error.

But doesn't notify the user that what they were trying to do didn't work.

How is this helpful? What is the business case for this process?

 

[Andrzejek]

..."doesn't notify the user" or notify anybody that something went wrong.
Somebody has to open a workbook (daily? weekly?) and look at the "Macro Error Log" sheet to know if there is anything that needs attention.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson