Click here to Skip to main content
15,354,954 members
Articles / Desktop Programming / ATL
Posted 3 Jun 2011


21 bookmarked

Functors in VBA

Rate me:
Please Sign up or sign in to vote.
4.40/5 (10 votes)
7 Mar 2012CPOL3 min read
This articles describes the implementation of functors (of sorts) for VBA using C++ ATL.
Image 1


This article suggests an implementation of functors (i.e. function objects) in ATL for VBA/VB6 consumption.

VBA comes with very little support for pointers and no support for function pointers apart from the AddressOf operator. I have been working with Excel VBA for the past few years and I often feel that access to function pointers would make my life much easier when it comes to generic programming.

This implementation allows VBA code like the below to be written:

'-- Initialize a functor that hooks on a **Sub** that takes two args.
Dim opfn As Functor: Set opfn = New_Functor(AddressOf MyFunction, retvoid_2_args)
'-- Invoke the function through the functor
Call opfn.call_retvoid_2("This works", " fine!")

Background/ C++ Implementation

The project (written in C++ ATL - VS2010) compiles to a COM DLL. The exported Functor objects can be used in VBA code to:

  • Store the address of a function (the function type is an input at this stage) and
  • Later call it by using the method of the Functor object that matches the function type

The IDL declarations of the HookFunction() method and a sample function calling method are:

interface IFunctor : IDispatch{
 [id(1), helpstring("Hooks on a function")] 
  HRESULT HookFunction([in] LONG fnAddress, [in] enum FuncType functionType);

 [id(8), helpstring("Calls Function that a)Retruns VARIANT b)Takes 2 arguments")] 
  HRESULT call_retvar_2([in,out,optional]VARIANT* 
	Arg1,[in,out,optional]VARIANT* Arg2,[out,retval]VARIANT*);

The hooked functions can be of one of the types that are typedef'd in Functor.h:

// Functions returning void
typedef HRESULT (__stdcall *pfn_retvoid_0)(void);
typedef HRESULT (__stdcall *pfn_retvoid_1)(VARIANT*);
typedef HRESULT (__stdcall *pfn_retvoid_2)(VARIANT*, VARIANT*);
typedef HRESULT (__stdcall *pfn_retvoid_3)(VARIANT*, VARIANT*, VARIANT*);
// Functions returning VARIANT
typedef HRESULT (__stdcall *pfn_retvar_0)(VARIANT*);
typedef HRESULT (__stdcall *pfn_retvar_1)(VARIANT*, VARIANT*);
typedef HRESULT (__stdcall *pfn_retvar_2)(VARIANT*, VARIANT*, VARIANT*);
typedef HRESULT (__stdcall *pfn_retvar_3)(VARIANT*, VARIANT*, VARIANT*, VARIANT*);
// Functions returning VBA Boolean
typedef VARIANT_BOOL (__stdcall *pfn_retbool_0)(void);
typedef VARIANT_BOOL (__stdcall *pfn_retbool_1)(VARIANT*);
typedef VARIANT_BOOL (__stdcall *pfn_retbool_2)(VARIANT*, VARIANT*);
typedef VARIANT_BOOL (__stdcall *pfn_retbool_3)(VARIANT*, VARIANT*, VARIANT*); 

These function types correspond to members of the enum FuncType (defined in VBA_Functors.idl) that is also exported from the DLL:

enum FuncType

The DLL also exports an initializer class (used to simulate a constructor with arguments) that exposes functions that return newly initialized COM objects; here New_Functor()returns a newly initialized Functor (IFunctor) object. From ClassInitializer.cpp:

// Returns a newly initialized Functor object
STDMETHODIMP CClassInitializer::New_Functor(/*[in]*/LONG fnAddress, 
	/*[in]*/FuncType functionType, /*[out,retval]*/IFunctor** ret)
 HRESULT hr = CFunctor::CreateInstance(ret);
 if (FAILED(hr)){ 
  return hr;
 return (*ret)->HookFunction(fnAddress, functionType);

The IDL declarations follow. Notice the appobject attribute of coclass ClassInitializer. This makes the object global (i.e. its methods can be invoked by VBA code that references the DLL without dimensioning a variable of the type).

interface IClassInitializer : IDispatch{
 [id(1), helpstring("Returns a newly initialized Functor object")] 
  HRESULT New_Functor([in] LONG fnAddress, [in] enum FuncType functionType, 
	[out,retval] IFunctor**);
library VBA_FunctorsLib
{ ...
 coclass ClassInitializer
  [default] interface IClassInitializer;

Using the Code

The file [VBA_Functors_Test.xls] that is included with the source code contains a few (contrived) examples of using Functor objects from VBA.

  • The VBA project contains a reference to the DLL (VBA_Functors.dll)
  • The DLL is a COM DLL that needs to be registered (typically using regsvr32.exe, for some instructions, see this link)

In order to correctly initialize a Functor object from VBA, you need to:

  • Dimension and initialize a Functor variable
  • Hook to an existing VBA function. The function has to be of one of the pre-specified types and the correct function type needs to be passed-in through the second argument of HookFunction() (as mentioned above, function types are encapsulated in the FuncType enum so VBA auto-complete conveniently kicks in)
Public Function SimpleFunction(ByRef vDisplay As Variant) As Variant
    SimpleFunction = MsgBox(vDisplay, vbYesNo, "Did that display correctly?")
End Function
Public Sub UseFunctors()
    Dim ofn As Functor: Set ofn = New Functor
    Call ofn.HookFunction(AddressOf SimpleFunction, retvar_1_args)
End Sub

Using the function New_Functor() allows for more compact syntax:

Public Sub UseFunctors()
    Dim ofn As Functor: Set ofn = New_Functor(AddressOf SimpleFunction, retvar_1_args)
End Sub

You can then use the Functor object to invoke the hooked function. Here is the UseFunctors() sub extended to include the function call:

Public Sub UseFunctors()
    Dim ofn As Functor: Set ofn = New_Functor(AddressOf SimpleFunction, retvar_1_args)
    Dim vbmRes As VbMsgBoxResult
    vbmRes = ofn.call_retvar_1("Display this!")
End Sub

Using New_Functor() also enables inline initialization of functors when calling functions that take Functor arguments. Consider the following snippet taken from the MORE REALISTIC EXAMPLE section of the VBA code in the sample .xls file:

'-- Predicate functions
Public Function IsMultipleOfTwo(ByRef vNumber As Variant) As Boolean
    IsMultipleOfTwo = (0 = vNumber Mod 2)
End Function
Public Function IsMultipleOfThree(ByRef vNumber As Variant) As Boolean
    IsMultipleOfThree = (0 = vNumber Mod 3)
End Function
'-- The generic function
Public Function CountMultiplesOfNumber(ByRef lNumber() As Long, _
	ByRef pfn As Functor) As Long
    Dim vIter As Variant
    For Each vIter In lNumber
        If pfn.call_retbool_1(vIter) Then CountMultiplesOfNumber = _
						CountMultiplesOfNumber + 1
    Next vIter
End Function
'-- The client code
Public Sub TestAbove()
    Dim alNUms(0 To 100) As Long ' The array is somehow initialized...
    '-- CountMultiplesOfNumber is Customized using a Functor constructed in line
    MsgBox CountMultiplesOfNumber(alNUms, New_Functor_
		(AddressOf IsMultipleOfThree, retbool_1_args))
End Sub

In my opinion, things become even more interesting when one considers exporting "stock" functors from a DLL (exported methods of a global object come to mind) that can be used selectively on the VBA side to drive the behaviour of algorithms exported by the DLL (mutating/non-mutating for_each on VBA SafeArrays for example?)

Points of Interest

Some points worth noting:

  1. Having worked in a corporate environment, I've come to realize that security policies can clash with the registration of COM components. The latter were traditionally registered under HKEY_LOCAL_MACHINE and the typical user doesn't have write access to the hive.

    Registering components under HKEY_CURRENT_USER addresses this. In ATL AtlSetPerUserRegistration(true) can be used to that end. Here is the DllRegisterServer definition in VBA_Functors.cpp:

    // DllRegisterServer - Adds entries to the system registry.
    STDAPI DllRegisterServer(void)
     // Register/Unregister under HKCU
     // registers object, typelib and all interfaces in typelib
     HRESULT hr = _AtlModule.DllRegisterServer();
     return hr;
  2. The file com_definitions.h included in this project structures HRESULTs commonly used as return values of COM methods in enums under COMErrorCodes namespace. This allows code like below to be written (without having to look up the codes in WinError.h):
    STDMETHODIMP CFunctor::call_retvoid_0(void){
     if (retvoid_0_args == m_ft){
    	__uuidof(IFunctor), COMErrorCodes::E__INVALIDARG);
  3. Keen observers might have noticed in the typedefs for functions that return Boolean, that the return values are not implemented as an extra [out,retval] parameter. True/False is instead returned directly through the function's return value (presumably for efficiency reasons):
    typedef VARIANT_BOOL (__stdcall *pfn_retbool_0)(void);


  • 03 June 2011: First revision


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Yiannis Spyridakis
Software Developer LogismiX
Greece Greece
Yiannis has been working as a developer for over 10 years.

He enjoys working with various technologies including C++, C#, Javascript/Typescript, Java, Kotlin and still works on the odd Excel/VBA project.

He is pationate about web technologies. ASP.NET Core, Node.js and Spring on the server side and SPAs (mainly Angular) on the client side.

Comments and Discussions

SuggestionAnother way Pin
Dusan Paulovic5-Feb-15 19:27
MemberDusan Paulovic5-Feb-15 19:27 
GeneralCalling functions by name in VBScript / VBA - alternative method using no DLLs etc Pin
jsc427-Jun-11 1:32
professionaljsc427-Jun-11 1:32 
GeneralRe: Calling functions by name in VBScript / VBA - alternative method using no DLLs etc Pin
Yiannis Spyridakis8-Jun-11 21:49
MemberYiannis Spyridakis8-Jun-11 21:49 
GeneralRe: Calling functions by name in VBScript / VBA - alternative method using no DLLs etc Pin
jsc429-Jun-11 0:10
professionaljsc429-Jun-11 0:10 
GeneralRe: Calling functions by name in VBScript / VBA - alternative method using no DLLs etc Pin
theonm13-Jan-12 15:24
Membertheonm13-Jan-12 15:24 
GeneralRe: Calling functions by name in VBScript / VBA - alternative method using no DLLs etc Pin
Yiannis Spyridakis15-Jan-12 0:35
MemberYiannis Spyridakis15-Jan-12 0:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.