In order to implement effective error handling, it would be useful if from code you could read the name of the sub/function inside which the code is running
A sort of Me.name for forms
Then the question is: how to read, via vba, the name of the function/sub inside which the code is running?
I don't think it's available.
If you use MZ tools to insert a generic error handler it puts the name of the sub in, but I don't know where it gets it from?
best guess is it can read back from the location of the call to the error handle for find the word 'sub' or 'function' and determine the name from there - perhaps using saveastext to interrogate
Collecting Function Names for Generic Error Handling in VBA
Introduction
You asked whether it’s possible to collect a function or subroutine’s name programmatically in VBA, so you can make your error-checking routine more generic. The short answer is yes, with some creativity. However, it depends on the tools and techniques available in the environment, such as using VBA.CallStack (which is not standard in VBA) or leveraging other alternatives like line-number mapping.
Approach 1: Using Line Numbers for Mapping
If your environment does not support accessing the call stack directly (such as in MS Access), you can assign unique line numbers to each subroutine and use those numbers for error tracking. Here’s how:
Step 1: Define a Mapping Function
Create a function to map line numbers to subroutine names.
Function GetSubroutineName(ByVal lineNumber As Long) As String
Select Case lineNumber
Case 1000: GetSubroutineName = "SomeSubroutine"
' Add more mappings as needed
Case Else: GetSubroutineName = "UnknownSubroutine"
End Select
End Function
Step 2: Add Line Numbers to Subroutines
Each subroutine gets a unique identifier. Example:
Private Sub SomeSubroutine()
Const lineNumber As Long = 1000 ' Unique line number for this subroutine
On Error GoTo ErrorHandler
' Your code here
Exit Sub
ErrorHandler:
Dim strSubName As String
strSubName = GetSubroutineName(lineNumber)
MsgBox "Error in " & strSubName & ": " & Err.Description
Resume Next
End Sub
Pros and Cons
Pro: Works without advanced tools or external libraries.
Con: Requires careful maintenance of unique line numbers.
---
Approach 2: Using the VBE Object (Not Supported in MS Access)
Some VBA environments, like standalone Excel, allow access to the call stack through the VBE object. This enables dynamic retrieval of the current procedure name.
Example Implementation
Here’s how it works:
Function GetCurrentProcedureName() As String
On Error Resume Next
Dim procName As String
procName = "UnknownProcedure"
' Example pseudocode for accessing the call stack
' This is a placeholder and may not work in MS Access
Dim CallStack As Variant
CallStack = VBA.CallStack
If Not IsEmpty(CallStack) Then
procName = CallStack(UBound(CallStack)).Procedure
End If
GetCurrentProcedureName = procName
On Error GoTo 0
End Function
Using the Function in a Subroutine
Call the helper function when an error occurs:
Private Sub SomeSubroutine()
On Error GoTo ErrorHandler
' Your code here
Exit Sub
ErrorHandler:
Dim strSubName As String
strSubName = GetCurrentProcedureName()
MsgBox "Error in " & strSubName & ": " & Err.Description
Resume Next
End Sub
---
The MS Access Limitation
In MS Access, the CallStack object is not natively supported, which means this direct approach won’t work. Instead, you may need to use a combination of line numbers and structured error logging.
Alternative Reference
I recommend reviewing the article “Error Handling and Debugging Tips and Techniques for Microsoft Access, VBA, and Visual Basic 6 (VB6)” by Luke Chung, President of FMS, Inc. It provides insights into error handling specifically for MS Access and suggests alternatives for environments lacking advanced debugging tools.
---
A Hybrid Approach for MS Access
If neither CallStack nor the VBE object works in your environment, you can consider a hybrid approach:
1. Use a helper variable to store the subroutine name explicitly.
2. Pass the subroutine name to your logging function during an error.
Example:
Private Sub SomeSubroutine()
Const SubroutineName As String = "SomeSubroutine"
On Error GoTo ErrorHandler
' Your code here
Exit Sub
ErrorHandler:
MsgBox "Error in " & SubroutineName & ": " & Err.Description
Resume Next
End Sub
---
Conclusion
If your environment doesn’t support the call stack, assigning unique identifiers (like line numbers) or hardcoding the subroutine name in a constant are practical alternatives. For MS Access, I recommend relying on Luke Chung’s debugging techniques to build robust error handling and logging systems.
Unless I'm completely missing something, the second part of the summary, re VBA.CallStack, is a complete nonsense hallucination of the AI.
I would prefer if you, as a knowledgeable VBA developer yourself, would filter such hallucination from your AI digests.
In order to implement effective error handling, it would be useful if from code you could read the name of the sub/function inside which the code is running
A sort of Me.name for forms
Then the question is: how to read, via vba, the name of the function/sub inside which the code is running?
Coincidentally, I spent several hours recently trying to figure out a way to do exactly this. I found one elaborate workaround on Stack Overflow IIRC, which I don't think is viable for realistic use.
The alternative that does work is MZ Tools, though. Here's a screenshot of my error handler template in MZ Tools and a sample of the resulting error handler.
MZ Tools provides Predefined variables, highlighted in yellow. The one you are looking for is the one I outlined in red. Of course, it works only in an MZ Tools environment, so you'd have to buy a license.
As an aside, originally I was referring to "forms" and "controls" on forms so I used sFrm and sCtl as arguments. Over time, I realized that is not particularly accurate, but now, with hundreds of procedures all using the same error handler in multiple accdbs, correcting that is a task for my heirs.
Code:
Public Sub testit()
100 On Error GoTo errHandler
Dim x As Integer
110 x = 1 / 0
Cleanup:
120 On Error Resume Next
exitProc:
130 Exit Sub
errHandler:
140 Call GlblErrMsg( _
sFrm:=Application.VBE.ActiveCodePane.CodeModule, _
sCtl:="testit" _
)
150 Resume Cleanup
160 Resume
End Sub
Although line numbers serve no other useful purpose that I know of, they are handy in reporting the erroring line.
Here's the error message produced by testit()
I do not log errors because it's my personal database and that is superfluous to its purpose, but in a production environment, you'd want to do that.
Some online research shows that VBA doesn't currently have such an ability directly.
This is an example of an old principle in programming. Most of the time, what you want isn't there. If you look at the documented functions of Access & VBA, there is no way to get this information. So you have to learn to program it yourself.
The practical solution is that you have to include the name of the entry point in every routine you create, by having every routine do its own error handling to include a name. Which means that you have to customize EACH and EVERY error handler routine to include a way to log or print some message containing that name. You ALSO could include line numbers for every line of code because there is a part of the ERR object that tells you the line number containing an error. Otherwise, you have nothing on which to "hang your hat." It would also mean that if you call a subroutine that doesn't have an error handler, it becomes invisible to this approach.
Part of the underlying problem is that to do what you ask, you have to be able to actually read and manipulate arbitrary addresses taken from the hardware stack pointer. But VBA doesn't inherently have that ability. If you were a good programmer in languages such as C++ or C#, you MIGHT be able to code something that could explore the program stack for you and return something like the name of the entry point. However, the peril at THAT point is that we don't know (because Access is NOT OpenSource) what actual subroutines get called "behind the scenes" for each subroutine/function call occurring inside VBA. Nor do we know exactly how the traceback data structure is built that enables Access to know where it is at a given time.
Yes I know MzTools and the possibility of using its functions to create the start and end routines
I wanted to understand if it was possible to do it only from Vba code
And it seems not to be possible
Yes I know MzTools and the possibility of using its functions to create the start and end routines
I wanted to understand if it was possible to do it only from Vba code
And it seems not to be possible
First, you aren't the first to ask that question across the various web sites (not limited to AWF).
Second, there doesn't seem to be an easy way if you limit yourself to VBA because it doesn't have the language construct you need (a "pure" address data type plus an operator to "follow the link" of an address datatype in the abstract sense of "follow.") Which is why I suggested writing something in any C or related variant language.
amorosik,
Further to what others have said, with VBA I don't think it is possible to get function/sub names during program execution. Best you can probably do is get the names at design time. Tom van Stiphout discusses this in this video on Northwind 2.2.
You could write code to modify code-- that is read your source and replace any/all error handling with your desired error handler. (The NW 2.2 shows a static class module that might be useful to you for this intended purpose.)
Andrej Karpathy @karpathy
People have too inflated sense of what it means to "ask an AI" about something. The AI are language models trained basically by imitation on data from human labelers. Instead of the mysticism of "asking an AI", think of it more as "asking the average data labeler" on the internet.
Few caveats apply because e.g. in many domains (e.g. code, math, creative writing) the companies hire skilled data labelers (so think of it as asking them instead), and this is not 100% true when reinforcement learning is involved, though I have an earlier rant on how RLHF (Reinforcement Learning from Human Feedback) is just barely RL, and "actual RL" is still too early and/or constrained to domains that offer easy reward functions (math etc.).
But roughly speaking (and today), you're not asking some magical AI. You're asking a human data labeler. Whose average essence was lossily distilled into statistical token tumblers that are LLMs. This can still be super useful of course.
Post triggered by someone suggesting we ask an AI how to run the government etc. TLDR you're not asking an AI, you're asking some mashup spirit of its average data labeler.
UG Said:-
The trouble is the AI, particularly ChatGPT, has also absorbed massive amounts of information from the internet on how to con people into buying products they don't want... So you find, when you are talking to it, you are easily led down the garden path because that's what a lot of the internet blurb does, and is designed to do! "Leads you down the garden Path"
In principle, it is possible to read out the function name, as it can be read out by vbWatchdog. However, you have to be a "VBA magician" to do this
I only know that it is not possible with VBA.
As I mentioned in my earlier post I don't think it's possible... However I've said that before and been brought down in flames more than once!
Me and chatty both, think the best way is to do it in the function itself, and to make that an easy, methodical, repeatable process I suggest you adopt a strategy I use.
I have a function template which I use when I start out writing a function...
It has its critics! One criticism is that much of your VBA code does not need sophisticated error handling....
I used to believe that myself until I made a runtime application out of one of my databases!!!!
Since that, shall we say interesting experiment, I have always included error checking, well maybe the odd simple function slips through!!
On this webpage you can find my function template:-
Private Function fXXXXX() As String
Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "
Dim strSubName As String
Dim strModuleName As String
strSubName = "fXXXXX"
strModuleName = "Form - " & Me.Name
'strModuleName = "Module - basModuleName"
On Error GoTo Error_Handler
'Place Code HERE !!!
'Place Code HERE !!!
'Place Code HERE !!!
Exit_ErrorHandler:
'adoCon.Close
'Set adoCon = Nothing
'Set adoCmd = Nothing
Exit Function
Error_Handler: 'Version - 1a
Dim strErrFrom As String
Dim strErrInfo As String
strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
Select Case Err.Number
Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
Case Else
MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
End Select
Resume Exit_ErrorHandler
End Function 'fXXXXX
And if you cringe at the necessity to copy and paste this code in every time you want to start a new function then below I demonstrate a very easy way of adding this template anywhere within your form module or class module:-
There's a little Known feature of Microsoft Access and indeed many of the Microsoft programs where you can easily insert a text file!
There's a better description, and also a video demonstration on my website here:-
Insert File
There’s a fantastic, useful, clever feature of MS Access (actually I think it’s in all of the VBA products) where you can store code snippets in the normal file structure of Windows.
Here are the Videos:-
Demo inserting a property statement (but could be any text) Starts at Time Index:- 19 seconds
3) - OOP's - Creating A Class Step 1 - Nifty Access
How to update your files ready for the next time you need to use the code in your access database... Starts at Time Index:- 119 seconds
3) - OOP's - Creating A Class Step 1 - Nifty Access
In principle, it is possible to read out the function name, as it can be read out by vbWatchdog. However, you have to be a "VBA magician" to do this
I only know that it is not possible with VBA.
Just to add my agreement to this point
I spent many hours trying to do this almost 10 years ago using VB Extensibility Code. In the end I admitted defeat.
Whilst I know it can be done (as both MZ-Tools and vbWatchdog have proved), I suspect both are using non-VBA methods.
Mz-Tools only requires the name at design time, which can be handled using vbComponent and CodeModule.
The magic starts with vbWatchdog to provide the name at runtime.
Mz-Tools only requires the name at design time, which can be handled using vbComponent and CodeModule.
The magic starts with vbWatchdog to provide the name at runtime.
FWIW, this is the code I came up with about 10 years ago to try & get the current procedure name at runtime.
I placed it in the error handler as below:
Code:
Exit_Handler:
Exit Sub
Err_Handler:
strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
MsgBox "Error " & Err.number & " in " & strProc & " procedure : " & Err.description
Resume Exit_Handler
End Sub
This always returned the name of a procedure in that code module and often it was the currently running procedure
However, it wasn't reliable.
For example, sometimes it was the previous procedure or more often, the first procedure in that code module
