Forum Discussion
Procedure Name
Hi,
In VBA, can you programmatically know the name of the procedure that is executing the code?
Rafael .:McPegasus.:
Valencia, Spain
- DanMoorehead_PowerWeb5AIIron Contributor
Unfortunately, it is not possible to obtain the name of the currently executing procedure or function in VBA.
There are many limitations with VBA compared to other more modern languages like JavaScript, TypeScript, C#, and .NET, with runtime reflection, async / multi-threaded coding, function pointers/objects/passing, callbacks, multiple classes per file, try-catch (vs On Error Go To), shared try-catch/logging (by passing inline arrow function definitions, like try(() => console.log)), easier debugging, more concise code, large class libraries, regex, more modern (tabbed) IDEs/editors, and the like.
However, you may find it helpful to enable "Break in Class Module" under VBE (VBA Editor) > Options, and/or use Ctrl+L to show the Call Stack window when debugging, and add Watches there as well as use the Immediate Window. You can handle errors via On Error GoTo OnError statement for example and write error handling code like OnError: LogError(funcName) function which calls Debug.Print, but that requires writing out LogError("MyFuncName") for each function.
However, if you're scripting macros and events with JavaScript or C#-like TypeScript macros in Microsoft Access - as is now possible with the PowerAccess.js Framework for Microsoft Access, as I'd just presented on and demoed at Access Day 2019 in Redmond last week -- which makes it possible to run JavaScript without any WebBrowser Control and use any JavaScript API and library from within VBA and use the full Access VBA/Automation API from JavaScript, as well as use IE11 HTML5 UIs embedded into forms -- then you could use arguments.callee or getCallerFuncName() (defined via: function getCallerFuncName() {return getFuncName.caller.name;}) (when don't have Strict Mode enabled). You can also pass functions around as objects/variables, for event handlers and the like, and use var func = (arg) => { console.log(func.name); } for example.
In that case, you can write JavaScript macros (and C#-like TypeScript macros and event handlers) using PowerAccess' included Script Editor (an embedded version of Visual Studio Code provided install-free with PowerAccess.js or with the PowerAccess All-in-One Toolset) or else using full Visual Studio Code or Visual Studio instead of older limited VBE.
With PowerAccess.js, you also then have access to 800,000 libraries, web APIs and NPM packages available, to use from VBA and JavaScript, and C#-like TypeScript macros alike, whether for Microsoft Graph, Azure Active Directory, QuickBooks, FTP, web APIs, logging or anything else you could imagine, without needing to rewrite it all from scratch in VBA, and all without needing to compile and COM register or install (like an add-in) as needed with .NET. This is why macro & Add-in development for Excel and the rest of the Office applications has moved to JavaScript / TypeScript with Office.js, where all the new Automation (Macro) functions / APIs are being developed.
JavaScript / TypeScript is also what we had used to build PowerExcel.ai and PowerAnalytics.ai to provide install-free, workbook embedded Excel Add-ins for AI generated Power Query and enhanced Pivot Tables and other AI powered tools and formula functions. And it's how we brought rich Pivot Table reports to Microsoft Access (also provided as part of PowerAccess.js, and available for Excel as Power Reports for PowerAnalytics.ai).
Once we began porting PowerAccess VBA Framework and PowerSQL Functions to TypeScript / JavaScript with PowerAccess.js, we've been able to simplify and speedup development greatly, and enable code reuse across our Access databases, Excel, SQL Server, mobile, Azure cloud, web and other solutions, and avoid reinventing the wheel through use of dozens on NPM packages / existing libraries and samples instead.
If you're interested in PowerAccess.js for JavaScript and C#-like TypeScript macros or rich Pivot Tables with end-user sort/filter/grouping, charts and inline data editing, then you can register as PowerAccess.net to get notified once available for early adopters. We are planning to include some of our dozens of new power tools for MS Access, like Global Find & Replace and/or Power SQL Editor (modern SQL editor with Intellisense code completion, syntax highlighting, Find & Replace, etc.), in a free PowerAccess Express edition as well, in addition to inexpensive subscription options for all the tools and features.
Also, if you're interested in PowerExcel.ai AI power tools (like Power Query and Pivot Table auto-generation) and new functions or PowerAnalytics.ai AI automated Data Prep for Excel, Access and Cloud, then you can register at PowerAnalytics.ai or begin using the cloud based version available online now.
I hope that helps.
Best regards,Dan
--Dan Moorehead
Founder & CTO, PowerAccess.js | PowerExcel.ai | PowerAnalytics.ai
- McPegasusCopper Contributor