Getting Procedure Name

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?

Re: Getting Procedure Name

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Neil" wrote in message
news:gtZ_i.22861$JD.7872@newssvr21.news.prodigy.net...
> 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?
>

Re: Getting Procedure Name

On Nov 15, 10:20 am, "Neil" wrote:
> 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?

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

Re: Getting Procedure Name

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
'====================================================


bobh wrote:
> On Nov 15, 10:20 am, "Neil" wrote:
>> 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?
>
> 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.
>
> 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.
>
> 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.
>
> 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.
>
> 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:

Re: Getting Procedure Name

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

"Allen Browne" wrote in message
news:473c6561$0$19697$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> 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.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Neil" wrote in message
> news:gtZ_i.22861$JD.7872@newssvr21.news.prodigy.net...
>> 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?
>>
>

Re: Getting Procedure Name

Thanks. I'll have a look at that.

"bobh" wrote in message
news:0b087292-9188-4294-9ca2-ee141220fa45@n20g2000hsh.google groups.com...
> On Nov 15, 10:20 am, "Neil" wrote:
>> 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?
>
> 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

Re: Getting Procedure Name

John Spencer wrote:
> 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
MPAPoster@FortuneJames.com

Re: Getting Procedure Name

On Fri, 16 Nov 2007 16:31:55 -0500, "James A. Fortune"
wrote:

>John Spencer wrote:
>> 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
>MPAPoster@FortuneJames.com

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

Re: Getting Procedure Name

On Nov 15, 10:20 am, "Neil" wrote:
> 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?

It would be a couple of hours work I suppose to write code that would
put into each procedure as line two and three:
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"
for Procedure "Temp" in Module 13.
or whatever. I suppose one would want the Module13 bit to
differentiate among various Temp private subs in various Modules.

But to what purpose?
I write my own error handling code only when I feel I need to be sure
that nothing non-VBAish is left dangling. A low level file handle is
one of these. Somewhere in the mists of time when Access was only a
gleam in the Wicked Witch of the West's eye I learned that these
should be closed. So when I open one, I do it within error handling
that will close it on error.
But almost none of my modules require that kind of fail-safe garbage
deletion. And almost none has its own error-handling.
Yes, I know, thousands of developers who write code superior to mine
are going to protest here. Such is life.

Having run the code once, then what? If we run it again we'll get
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"
Dim VBProcedureName$
VBProcedureName = "Module13:Temp"

which will raise a compile error. So now we have to be sure the lines
are not already there. And if we change the name of a procedure ... I
suspect that ifs just keep rolling on here.

Re: Getting Procedure Name

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



"lyle" wrote in message
news:213a25f4-e6d6-4673-a8dc-e4991219f8cd@i37g2000hsd.google groups.com...
> On Nov 15, 10:20 am, "Neil" wrote:
>> 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?
>
> It would be a couple of hours work I suppose to write code that would
> put into each procedure as line two and three:
> Dim VBProcedureName$
> VBProcedureName = "Module13:Temp"
> for Procedure "Temp" in Module 13.
> or whatever. I suppose one would want the Module13 bit to
> differentiate among various Temp private subs in various Modules.
>
> But to what purpose?
> I write my own error handling code only when I feel I need to be sure
> that nothing non-VBAish is left dangling. A low level file handle is
> one of these. Somewhere in the mists of time when Access was only a
> gleam in the Wicked Witch of the West's eye I learned that these
> should be closed. So when I open one, I do it within error handling
> that will close it on error.
> But almost none of my modules require that kind of fail-safe garbage
> deletion. And almost none has its own error-handling.
> Yes, I know, thousands of developers who write code superior to mine
> are going to protest here. Such is life.
>
> Having run the code once, then what? If we run it again we'll get
> Dim VBProcedureName$
> VBProcedureName = "Module13:Temp"
> Dim VBProcedureName$
> VBProcedureName = "Module13:Temp"
>
> which will raise a compile error. So now we have to be sure the lines
> are not already there. And if we change the name of a procedure ... I
> suspect that ifs just keep rolling on here.
>
>

Re: Getting Procedure Name

Arch wrote:

> 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
MPAPoster@FortuneJames.com

Re: Getting Procedure Name

"Allen Browne" wrote in message
news:473c6561$0$19697$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> 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 :)

Re: Getting Procedure Name

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" wrote in message
news:5qd9bkFvf38vU1@mid.individual.net...
>
> "Allen Browne" wrote in message
> news:473c6561$0$19697$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
>> 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 :)
>
>

Re: Getting Procedure Name

"Neil" wrote in message
news:uwf0j.309$Vq.235@nlpi061.nbdc.sbc.com...
> 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.

Re: Getting Procedure Name

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

"Deano" wrote in message
news:5qdhldFv43sqU1@mid.individual.net...
>
> "Neil" wrote in message
> news:uwf0j.309$Vq.235@nlpi061.nbdc.sbc.com...
>> 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.
>
>

Re: Getting Procedure Name

"Neil" wrote in message
news:gqg0j.225$AR7.197@nlpi070.nbdc.sbc.com...
> 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.

Re: Getting Procedure Name

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.

"Deano" wrote in message
news:5qdjabFv93b8U1@mid.individual.net...
>
> "Neil" wrote in message
> news:gqg0j.225$AR7.197@nlpi070.nbdc.sbc.com...
>> 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.
>
>