CallBack function with VBA

This post shows you how to implement a CallBack function with VBA.

There is no way to implement a callback function in VBA wihout creating an API  in C (Dynamic Library Link).

Create Win32 Dynamic Library Link Project with VC++

1. Start VC++ IDE ,
2. Select New from File menu.  Then select Win32 Dynamic - link library from Projects tab.
3. Enter project name as "CallBackFuncApi"
4. then click OK button, Now you can see a dialog box with caption Win32 Dynamic - Link library - step 1 of 1
5. Select An Empty DLL project , and click Finish.
6. Create the follwing files cb.h , cb.cpp and cb.def

/***  cb.h file ***/
#define EXTERNC extern "C"
#define DLLAPI __declspec(dllexport)
#define WINAPI __stdcall


typedef bool (CALLBACK *CallbackFunc) (VARIANT userData);


EXTERNC DLLAPI DWORD WINAPI CallBackFuncVariantHndl(CallbackFunc pCallback);


/*** cb.cpp ***/
#include <windows.h>
#include "cb.h"


EXTERNC DLLAPI void WINAPI CallBackFuncVariantHndl(CallbackFunc pCallback, VARIANT userData)
{
 (*pCallback)(userData);
}
}
/*** cb.def ***/
EXPORTS
CallBackFuncVariantHndl

Using CallBack function api with VBA
You have to copy CallBackFuncApi.Dll into system32 folder

Public Declare Function CallBackFuncVariantHndl Lib "CallBackFuncApi" (ByVal pCallback As Long) As Long

'Call back function with variant parameter (should be in Module)
Public Sub MyCallBackFunction(ByVal vnt As Variant)
  ' Enter your code
End Sub
'
Public Sub MyCallBackFunction(ByVal vnt As Variant)
Dim  vnt As Variant
vnt = split("Item 1 - Item 2 - Item 3 ","-") 
Call CallBackFuncVariantHndl (AddressOf  MyCallBackFunction, vnt)
End Sub