By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

How to get the address of a VBA function

Seth Schrock
Expert 2.5K+
P: 2,951
I have found online a way to add the ability to drag and drop files onto a listbox using the method described here. For this method to work, it needs the ability to get the address of a specific function. In VB, the function I need is called AddressOf, but this function isn't available in VBA. I found a copy of a function that does the same thing, but it references some APIs located in a vba332.dll file that isn't on my computer. Evidently this was created to fix a problem back in Access 97.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. '-------------------------------------------------------------------------------------------------------------------
  4. '   Declarations
  5. '
  6. '   These function names were puzzled out by using DUMPBIN /exports
  7. '   with VBA332.DLL and then puzzling out parameter names and types
  8. '   through a lot of trial and error and over 100 IPFs in MSACCESS.EXE
  9. '   and VBA332.DLL.
  10. '
  11. '   These parameters may not be named properly but seem to be correct in
  12. '   light of the function names and what each parameter does.
  13. '
  14. '   EbGetExecutingProj: Gives you a handle to the current VBA project
  15. '   TipGetFunctionId: Gives you a function ID given a function name
  16. '   TipGetLpfnOfFunctionId: Gives you a pointer a function given its function ID
  17. '
  18. '-------------------------------------------------------------------------------------------------------------------
  19. Private Declare Function GetCurrentVbaProject _
  20.  Lib "vba332.dll" Alias "EbGetExecutingProj" _
  21.  (hProject As Long) As Long
  22. Private Declare Function GetFuncID _
  23.  Lib "vba332.dll" Alias "TipGetFunctionId" _
  24.  (ByVal hProject As Long, ByVal strFunctionName As String, _
  25.  ByRef strFunctionId As String) As Long
  26. Private Declare Function GetAddr _
  27.  Lib "vba332.dll" Alias "TipGetLpfnOfFunctionId" _
  28.  (ByVal hProject As Long, ByVal strFunctionId As String, _
  29.  ByRef lpfn As Long) As Long
  30.  
  31. '-------------------------------------------------------------------------------------------------------------------
  32. '   AddrOf
  33. '
  34. '   Returns a function pointer of a VBA public function given its name. This function
  35. '   gives similar functionality to VBA as VB5 has with the AddressOf param type.
  36. '
  37. '   NOTE: This function only seems to work if the proc you are trying to get a pointer
  38. '       to is in the current project. This makes sense, since we are using a function
  39. '       named EbGetExecutingProj.
  40. '-------------------------------------------------------------------------------------------------------------------
  41. Public Function AddrOf(strFuncName As String) As Long
  42.     Dim hProject As Long
  43.     Dim lngResult As Long
  44.     Dim strID As String
  45.     Dim lpfn As Long
  46.     Dim strFuncNameUnicode As String
  47.  
  48.     Const NO_ERROR = 0
  49.  
  50.     ' The function name must be in Unicode, so convert it.
  51.     strFuncNameUnicode = StrConv(strFuncName, vbUnicode)
  52.  
  53.     ' Get the current VBA project
  54.     ' The results of GetCurrentVBAProject seemed inconsistent, in our tests,
  55.     ' so now we just check the project handle when the function returns.
  56.     Call GetCurrentVbaProject(hProject)
  57.  
  58.     ' Make sure we got a project handle... we always should, but you never know!
  59.     If hProject <> 0 Then
  60.         ' Get the VBA function ID (whatever that is!)
  61.         lngResult = GetFuncID( _
  62.          hProject, strFuncNameUnicode, strID)
  63.  
  64.         ' We have to check this because we GPF if we try to get a function pointer
  65.         ' of a non-existent function.
  66.         If lngResult = NO_ERROR Then
  67.             ' Get the function pointer.
  68.             lngResult = GetAddr(hProject, strID, lpfn)
  69.  
  70.             If lngResult = NO_ERROR Then
  71.                 AddrOf = lpfn
  72.             End If
  73.         End If
  74.     End If
  75. End Function
  76.  
  77. Function Foo()
  78.  
  79. End Function
  80.  
Is there a current way of achieving the same result?
Jan 13 '15 #1
Share this Question
Share on Google+
1 Reply


Seth Schrock
Expert 2.5K+
P: 2,951
I managed to find a website that had the vba332.dll file that I needed. Back to testing the original idea.
Jan 13 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.