Getting Procedure Name

[Neil]

Can I get the name of a procedure from within the procedure? In my error
handler, I write the error to an error table. I'd like to write the name of
the procedure that's writing the error. But, rather than customizing each
error handler with the procedure name, it would be nice to be able to call a
system variable or function that gives me the procedure name and module
name. Is that possible?

 

[Allen Browne]

Unfortunately, VBA does not expose the name of the executing procedure, nor
the name of that module.

In case you are not aware, there is a great little utility you can download
from:
www.mztools.com
Choose the one for VBA.
Install.
It adds a toolbar to the VBA window.
You can now drop your error handler into the current procedure just by
clicking the toolbar button. And it's configurable.

For the module name, what I personally do is to declare a private constant
in the General Declarations section of every module, and assign it the name
of that module, e.g.:
Private Const conModName = "Form_frmInvoice"
Use the constant name in your error handler where you want it to pass the
module name. This arrangement requires no change to the code at all when you
copy'n'paste procedures between modules.

(You can use Module.Name, but that fails in an MDE.)

 

[John Spencer]

You do realize that you just posted copyrighted material.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Neil]

Thanks, Allen. I remember looking at Mztools some time ago and deciding not
to get it. I'll have to have a second look. Thanks!

Neil

 

[Neil]

Thanks. I'll have a look at that.

Hi, there is this module I use - originaly from Access97 Developers
handbook. I have modified it a bit but it still works and I use it in
all my XP apps today.
bobh.

Option Compare Database
Option Explicit

' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.

Private Declare Function adh_apiIsClipboardFormatAvailable Lib
"user32" Alias "IsClipboardFormatAvailable" (ByVal uFormat As Integer)
As Integer
Private Declare Function adh_apiOpenClipboard Lib "user32" Alias
"OpenClipboard" (ByVal hWnd As Long) As Integer
Private Declare Function adh_apiGetClipboardData Lib "user32" Alias
"GetClipboardData" (ByVal uFormat As Integer) As Long
Private Declare Function adh_apiGlobalSize Lib "kernel32" Alias
"GlobalSize" (ByVal hMem As Long) As Integer
Private Declare Function adh_apiGlobalLock Lib "kernel32" Alias
"GlobalLock" (ByVal hMem As Long) As Long
Private Declare Sub adh_apiMoveMemory Lib "kernel32" Alias
"RtlMoveMemory" (ByVal strDest As Any, ByVal lpSource As Any, ByVal
Length As Long)
Private Declare Function adh_apiGlobalUnlock Lib "kernel32" Alias
"GlobalUnlock" (ByVal hMem As Long) As Integer
Private Declare Function adh_apiCloseClipboard Lib "user32" Alias
"CloseClipboard" () As Integer
Private Declare Function adh_apiGlobalAlloc Lib "kernel32" Alias
"GlobalAlloc" (ByVal uFlags As Integer, ByVal dwBytes As Long) As Long
Private Declare Function adh_apiEmptyClipboard Lib "user32" Alias
"EmptyClipboard" () As Integer
Private Declare Function adh_apiSetClipboardData Lib "user32" Alias
"SetClipboardData" (ByVal uFormat As Integer, ByVal hData As Long) As
Long
Private Declare Function adh_apiGlobalFree Lib "kernel32" Alias
"GlobalFree" (ByVal hMem As Long) As Long

Private Const GMEM_MOVABLE = &H2&
Private Const GMEM_DDESHARE = &H2000&
Private Const CF_TEXT = 1

'Error return codes from Clipboard2Text
Public Const adhCLIPBOARDFORMATNOTAVAILABLE = 1
Public Const adhCANNOTOPENCLIPBOARD = 2
Public Const adhCANNOTGETCLIPBOARDDATA = 3
Public Const adhCANNOTGLOBALLOCK = 4
Public Const adhCANNOTCLOSECLIPBOARD = 5
Public Const adhCANNOTGLOBALALLOC = 6
Public Const adhCANNOTEMPTYCLIPBOARD = 7
Public Const adhCANNOTSETCLIPBOARDDATA = 8
Public Const adhCANNOTGLOBALFREE = 9

Public Sub ErrorHandler()
'This routine will automatically add error handling to the routine
selected if executed.
'To get it to work properly (as currently set up) have the name of the
routine selected
'when you right-mouse click and run this from the shortcut menu.
'
'This routine has been modified by Ron Allard and Bob Hynes to improve
the error message display.
'
'Adding a command to the toolbar shortcut popup menu while in vba page
design.
'Click on the View menu bar and choose Toolbars>Customize. In the
Toolbars tab of
'Customize dialog, display the Shortcut Menus by checking the box next
to it (it's the last in the list).
'After you see the "Shortcut Menus" displayed, move to the Commands
tab in the Customize dialog.
'In the File category, click on "Custom" and drag it over the
"Shortcut Menus" bar.
'Hover over the Module menu and when it drops down, go into "Module
Uncompiled"
'Now you can drop the Custom item wherever you want it to be located.
'Right click the Custom item you just dropped and name it (right click
to pull up its properties).
'Then click on the "properties" option at the bottom of the dialog
box. On the next dialog all you need
'to do is type in the "On Action" data. The name of the function you
want it to run " =ErrorHandler() "
'
Dim strRoutineName As String, strRoutineType As String
Dim str3Letters As String, strTitle As String, str As String

SendKeys "(^c)" ' capture name of
routine
DoEvents ' allow clipboard
to note the new text
'MsgBox Clipboard.GetText()
strRoutineName = ClipboardGetText("")
SendKeys "(^{LEFT})(^{LEFT})"
SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}"
SendKeys "(^c)" ' capture 3
letters of word
DoEvents ' allow
clipboard to note the new text
str3Letters = ClipboardGetText("")
Select Case str3Letters
Case "Sub"
strRoutineType = "Sub"
Case "Fun"
strRoutineType = "Function"
Case "Get", "Let", "Set"
strRoutineType = "Property"
Case Else
strRoutineType = "What is this?"
End Select

strTitle = strRoutineType & " - " & strRoutineName & " in "
str = "{END}~" & "On Error GoTo Err_" & strRoutineName _
& "~Exit_" & strRoutineName & ":~{TAB} Exit " & strRoutineType
_
& "~Err_" & strRoutineName & ":~MsgBox Err.Number " & "& "" -
"" &" & " Err.Description" & ",," & """" & strTitle & """" & " & " &
"Me.Name" _
& "~Resume Exit_" & strRoutineName & "~"
SendKeys str
str = "{RIGHT}+{DOWN}(^x){up 4}(^v){UP 3}(^v){UP}~{TAB}"
SendKeys str

End Sub

Public Function ClipboardGetText(ByVal strFailureString As String) As
String
' Purpose: Gets some text from the Windows clipboard
' Params:
' strFailureString: the string to return if the function fails to
get a string from the Clipboard
' Returns: either the string in the Clipboard or the default failure
string (strFailureString)
' Note: This routine calls the Access 97 Developer Handbook routine
adhClipboardGetText().
' adhClipboardGetText returns a variant, and what is needed is a
string. This routine ensures
' the return of a string.
On Error Resume Next
Dim varReturnValue As Variant
varReturnValue = adhClipboardGetText()
ClipboardGetText = CStr(varReturnValue)
If err.Number <> 0 Then ClipboardGetText = strFailureString

End Function

Function adhClipboardSetText(strText As String) As Variant
' Puts some text on the Windows clipboard
' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.
' In:
' The text to place on the clipboard
' Out:
' If IsError returns true, then the value
' is an error number. If IsError is false
' the value is meaningless.

Dim varRet As Variant
Dim fSetClipboardData As Boolean
Dim hMemory As Long
Dim lpMemory As Long
Dim lngSize As Long

varRet = False
fSetClipboardData = False

' Get the length, including one extra for a CHR$(0) at the end.
lngSize = Len(strText) + 1
hMemory = adh_apiGlobalAlloc(GMEM_MOVABLE, lngSize)
If Not CBool(hMemory) Then
varRet = CVErr(adhCANNOTGLOBALALLOC)
GoTo adhClipboardSetTextDone
End If
' Lock the object into memory
lpMemory = adh_apiGlobalLock(hMemory)
If Not CBool(lpMemory) Then
varRet = CVErr(adhCANNOTGLOBALLOCK)
GoTo adhClipboardSetTextGlobalFree
End If
' Move the string into the memory we locked
Call adh_apiMoveMemory(lpMemory, strText, lngSize)
' Don't send clipboard locked memory.
Call adh_apiGlobalUnlock(hMemory)
' Open the clipboard
If Not CBool(adh_apiOpenClipboard(0&)) Then
varRet = CVErr(adhCANNOTOPENCLIPBOARD)
GoTo adhClipboardSetTextGlobalFree
End If
' Remove the current contents of the clipboard
If Not CBool(adh_apiEmptyClipboard()) Then
varRet = CVErr(adhCANNOTEMPTYCLIPBOARD)
GoTo adhClipboardSetTextCloseClipboard
End If
' Add our string to the clipboard as text
If Not CBool(adh_apiSetClipboardData(CF_TEXT, hMemory)) Then
varRet = CVErr(adhCANNOTSETCLIPBOARDDATA)
GoTo adhClipboardSetTextCloseClipboard
Else
fSetClipboardData = True
End If

adhClipboardSetTextCloseClipboard:
' Close the clipboard
If Not CBool(adh_apiCloseClipboard()) Then
varRet = CVErr(adhCANNOTCLOSECLIPBOARD)
End If

adhClipboardSetTextGlobalFree:
If Not fSetClipboardData Then
'If we have set the clipboard data, we no longer own
' the object--Windows does, so don't free it.
If CBool(adh_apiGlobalFree(hMemory)) Then
varRet = CVErr(adhCANNOTGLOBALFREE)
End If
End If

adhClipboardSetTextDone:
adhClipboardSetText = varRet

End Function

Public Sub adhTestClipboard()
' Tests putting some text on the clipboard then reading it off again
' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.
' Example:
' Call adhTestClipboard

Dim varRet As Variant

varRet = adhClipboardSetText("This is a test")
If IsError(varRet) Then
Call adhReportClipboardError(CInt(varRet))
Else
varRet = adhClipboardGetText()
If IsError(varRet) Then
Call adhReportClipboardError(CInt(varRet))
Else
MsgBox varRet
End If
End If

End Sub

Public Sub adhReportClipboardError(ByVal intError As Integer)
' Reports an error received from the clipboard
' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.
' Example:
' Call adhReportClipboardError(CInt(varRet))

Select Case CInt(intError)
Case adhCLIPBOARDFORMATNOTAVAILABLE
MsgBox "Clipboard format not available"
Case adhCANNOTOPENCLIPBOARD
MsgBox "Cannot open clipboard"
Case adhCANNOTGETCLIPBOARDDATA
MsgBox "Cannot get clipboard data"
Case adhCANNOTGLOBALLOCK
MsgBox "Cannot global lock data"
Case adhCANNOTCLOSECLIPBOARD
MsgBox "Cannot close clipboard"
Case adhCANNOTGLOBALALLOC
MsgBox "Cannot global alloc"
Case adhCANNOTEMPTYCLIPBOARD
MsgBox "Cannot empty clipboard"
Case adhCANNOTSETCLIPBOARDDATA
MsgBox "Cannot set clipboard data"
Case adhCANNOTGLOBALFREE
MsgBox "Cannot global free"
Case Else
MsgBox "From module function - adhReportClipboardError",
vbExclamation, "I'm stuck!"
End Select

End Sub

Public Function adhClipboardGetText() As Variant
' Gets some text on the Windows clipboard
' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
(Sybex)
' Copyright 1997. All Rights Reserved.
' Out:
' The text on the clipboard.
' If IsError returns true, then the value
' is an error number

Dim hMemory As Long
Dim lpMemory As Long
Dim strText As String
Dim lngSize As Long
Dim varRet As Variant

varRet = ""

' Is there text on the clipboard? If not, error out.
If Not CBool(adh_apiIsClipboardFormatAvailable(CF_TEXT)) Then
varRet = CVErr(adhCLIPBOARDFORMATNOTAVAILABLE)
GoTo adhClipboardGetTextDone
End If

' Open the clipboard
If Not CBool(adh_apiOpenClipboard(0&)) Then
varRet = CVErr(adhCANNOTOPENCLIPBOARD)
GoTo adhClipboardGetTextDone
End If

' Get the handle to the clipboard data
hMemory = adh_apiGetClipboardData(CF_TEXT)
If Not CBool(hMemory) Then
varRet = CVErr(adhCANNOTGETCLIPBOARDDATA)
GoTo adhClipboardGetTextCloseClipboard
End If

' Find out how big it is and allocate enough space
' in a string
lngSize = adh_apiGlobalSize(hMemory)
strText = Space$(lngSize)

' Lock the handle so we can use it
lpMemory = adh_apiGlobalLock(hMemory)
If Not CBool(lpMemory) Then
varRet = CVErr(adhCANNOTGLOBALLOCK)
GoTo adhClipboardGetTextCloseClipboard
End If

' Move the information from the clipboard memory
' into our string
Call adh_apiMoveMemory(strText, lpMemory, lngSize)

' Truncate it at the first Null character because
' the value reported by lngSize is erroneously large
strText = left$(strText, InStr(1, strText, Chr$(0)) - 1)

' Free the lock
Call adh_apiGlobalUnlock(hMemory)

adhClipboardGetTextCloseClipboard:
' Close the clipboard
If Not CBool(adh_apiCloseClipboard()) Then
varRet = CVErr(adhCANNOTCLOSECLIPBOARD)
End If

adhClipboardGetTextDone:
If Not IsError(varRet) Then
adhClipboardGetText = strText
Else
adhClipboardGetText = varRet
End If

End Function

 

[James A. Fortune]

You do realize that you just posted copyrighted material.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

See:

U.S. Copyright Office - Fair Use:

http://www.copyright.gov/fls/fl102.html

I would argue that the effect of the use here would be to increase the
potential market for the book rather than decrease it. Then again,
after seeing the amount of code posted, purportedly to get the name of
the procedure, it might have a negative affect .

James A. Fortune
(e-mail address removed)

 

[Arch]

See:

U.S. Copyright Office - Fair Use:

http://www.copyright.gov/fls/fl102.html

I would argue that the effect of the use here would be to increase the
potential market for the book rather than decrease it. Then again,
after seeing the amount of code posted, purportedly to get the name of
the procedure, it might have a negative affect .

James A. Fortune
(e-mail address removed)

I suspect that if any of those guys (Litwin, Getz, or Gilbert) were
approached, they would readily grant permission to post that function.
I can't agree, however, that this falls under "Fair Use" guidelines. I
think it is rather blatant copyright violation.

My 2 cents worth,
Arch

 

[Neil]

I don't know -- I just put an error handler in every proc, and it's worked
out well for me. For things like closing files or other things that I always
want to do, I put those under the exit label, and have the error handler
transfer to there.

In any case, you gave me an idea with the below: I could use a proc name
variable in the error handler, and set the variable at the top of the proc.
That would allow me to use a standard template for error handlers (with a
default proc name of ""), and then fill in the names as needed (in the more
problematic routines). Probably, if I had the VBProcName = "" in the
template, I'd just automatically fill it in when I start a new proc, anyway.

But, you know, it would be simple to do something like this with a Word
macro -- just plop the whole module into Word, record/write a macro that
does what's needed, and then plop the module text back into Access. Only
problem is: losing the link to event procedures. If I could be sure that the
links to control's event procedures would remain in place after I paste in
the new procs from Word, then this would be fine. But I've had bad
experiences with that in the past. Any notes/tips/etc. on trying to get the
procs to remain linked or to relink them after an event such as this?

Thanks!

Neil

 

[Guest]

Look into using the free MZ Tools. It has a customizable error handler that
you can set (it has a default which gives you info on the procedure it is in)
and you just have to put your cursor in the procedure and click the button to
add the error handler. It also has a procedure, and module, header button
which allows you to auto insert information about the procedure and header.
I use it all of the time and it is wonderful.

Plus there are other tools in that same tool set that are very useful.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[James A. Fortune]

I suspect that if any of those guys (Litwin, Getz, or Gilbert) were
approached, they would readily grant permission to post that function.
I can't agree, however, that this falls under "Fair Use" guidelines. I
think it is rather blatant copyright violation.

My 2 cents worth,
Arch

I think it's a tough call in this case for the following reasons:

1. The post contained way more code than needed to make the point.

2. The post contained clipboard code, the revealing of which could have
been the reason for the post.

3. The poster didn't show which improvements were made, making it hard
to determine how much of the post contains technical innovation.

4. It's difficult to determine the effect of the post on sales of the
book, if any.

5. The poster doesn't appear to be getting any financial gain from the post.

6. The original book is long, making the portion of the copyrighted work
small.

The stringency of the copyright notice suggests that even enhancements
to the code aren't permissible without permission and that the poster
should have honored that. But, it's possible that even a blatant
copyright violation can fall under "Fair Use," since any Fair Use is
blatant to some extent. In this case, even if revealing the clipboard
code can be proven to be intentional, it's not likely that the minimal
legal damages would be worth pursuing. A lawyer would be a better
person to ask about whether a particular citation falls under Fair Use
or not. I agree that getting permission would have been the preferred
way to go. Personally, I didn't think the code was even worth saving
anyway.

That brings up the topic of the code that Microsoft will be revealing as
part of Visual Studio 2008. The move is a welcome one that will benefit
both developers and Microsoft. It also affords some peace of mind about
the particular sections of code where that is allowed. The problem is
that the license is restrictive to the point that you can't expand on
any ideas you get from seeing their code. Thus, I won't be poking
around in any of that code unless I have no other choice due to some
unfathomable bug. I believe that, in the absence of a contractual
agreement such as an EULA, it's not possible to protect particular
software techniques. Even a contract cannot utterly protect particular
software techniques since particular portions of a contract can be
overturned if they are too outré. BTW, as seen on television, some
software companies have found some very effective legal techniques aimed
at getting around legal software obstacles, even sometimes going to the
extreme of hiring programmers who can be proven not to know about a
competitor's software and giving them a list of objectives that must be met.

James A. Fortune
(e-mail address removed)

 

[Deano]

Unfortunately, VBA does not expose the name of the executing procedure, nor
the name of that module.

In case you are not aware, there is a great little utility you can download
from:
www.mztools.com

Ta for the tip on that utility. Just being able to clear the immediate
window with a single click is useful

 

[Neil]

Does it have the ability to *close* the Immediate window with a single
click? I've always hated how you can press Ctrl+G to open the Immediate
window; but pressing Ctrl+G a second time doesn't close it. I hate having to
close it with the mouse all the time. That would be a great feature if it
had it!

 

[Deano]

Does it have the ability to *close* the Immediate window with a single
click? I've always hated how you can press Ctrl+G to open the Immediate
window; but pressing Ctrl+G a second time doesn't close it. I hate having to
close it with the mouse all the time. That would be a great feature if it
had it!

No it doesn't do that. Given that Access doesn't seem to support the
feature it seems unlikely that this toolkit would either.

 

[Neil]

Well, I was hoping for a miracle. You can't blame a fellow for dreaming, can
you?

 

[Deano]

Well, I was hoping for a miracle. You can't blame a fellow for dreaming, can
you?

Nah, guess not. Useful download though, haven't tried out all the stuff but
being able to drop in pre-written error handlers etc is useful. I tested to
make sure it wouldn't screw anything up and so far it looks like it works
just fine.

 

[Neil]

I just have error handlers stored in files, and I drop them in using Insert
| File. But being able to customize the error handler according to the proc
name would be useful.