Intercepting resetting of vba editor as well as unhandled errors for safe subclassing

[Jaafar Tribak]

Hi all,

As you know, excel doesn't expose many useful events such as when activating excel or when moving or resizing its main window etc...

The way to catch these missing events is through subclassing or hooking excel. Both techniques work in compiled programs but they freeze office applications or make them unstable at best.

Probably the main issue with subclassing is when the vb editor is inadvertently reset or when an unhadled error occurs while excel is subclassed .When that happens the whole application crashes.

In an attempt to deal with the freezing and crashings issues, I have written this C++ dll (about 14 kb) which gracefully removes the subclassing right before the vbe is reset due to clicking on the vbe stopt button or due to an unhaded error.

The dll is not an activeX dll so it doesn't need registration before using it.

Also, in order to maintain the dll code self-contained with the workbook, I have extracted the dll file bytes and place them in seperate vba module so it can be rebuilt on the fly at runtime (kind of a resource).

Drawbacks I am aware of:
*When first loading the dll the vbe is reset behind the scenes. This will reset any already initialised variables and may cause the loss of data. So the recommendation is to install the subclassing at the start of the program.

*The only error that the dll is unable to catch is inside the window procedure so careful error handling is required there.


C++ dll code:

#include <windows.h>#define DLL_EXPORTS

BOOL bSub = NULL;
BOOL bUnsub = NULL;
BOOL bVBEreset = NULL;
BOOL bClosing = NULL;
BOOL bVBEvisible = NULL;
HWND lwkbHwnd = NULL;
HWND lVBEhwnd = NULL;
HWND lXLhwnd = NULL;
LONG lVBACallback = NULL;
LONG_PTR lOldProc = NULL;
LONG_PTR lOldProcWkb = NULL;
LONG_PTR lOldProcVBE = NULL;
HHOOK hookHandle = NULL;

extern "C" __declspec(dllexport) void SubClass(HWND, HWND, LONG);
extern "C" __declspec(dllexport) void UnSubClass(HWND);
void CALLBACK TimerProc(HWND, UINT, UINT, DWORD);
void CALLBACK TimerProc2(HWND, UINT, UINT, DWORD);
LRESULT CALLBACK WinProc(HWND, UINT, WPARAM, LPARAM);
LRESULT CALLBACK WinProcWkb(HWND, UINT, WPARAM, LPARAM);
LRESULT CALLBACK WinProcVBE(HWND, UINT, WPARAM, LPARAM);
LRESULT CALLBACK CBTProc(int, WPARAM, LPARAM);
void SetCBTHook();
void RemoveCBTHook();

extern "C" __declspec(dllexport) void SubClass(HWND hwnd, HWND wkbHwnd, LONG CallBackFunc)
    {
        if (bSub == FALSE) {
            bSub = TRUE;
            bUnsub = FALSE;
            bClosing = FALSE;
            bVBEreset = FALSE;
            lVBACallback = CallBackFunc;
            lXLhwnd = hwnd;
            lwkbHwnd = wkbHwnd;
            lVBEhwnd = FindWindow(L"wndclass_desked_gsk", 0);
            SetCBTHook();
            if (lVBEhwnd == 0) {
                SetForegroundWindow(lXLhwnd);
                keybd_event(VK_MENU, 0, 0, 0);
                keybd_event(VK_F11, 1, 0, 0);
                keybd_event(VK_F11, 0, KEYEVENTF_KEYUP, 0);
                keybd_event(VK_MENU, 0, KEYEVENTF_KEYUP, 0);
            }else {

                if (GetProp(lVBEhwnd,L"MsgPosted")==0){
                    bVBEvisible=IsWindowVisible(lVBEhwnd);
                    PostMessage(lVBEhwnd, WM_USER + 0xC44, 0x30, 0);
                    PostMessage(lVBEhwnd, WM_USER + 0xC44, 0x33, 0);
                    PostMessage(lVBEhwnd, WM_USER + 0xC44, 0x83, 0);
                    SetProp(lVBEhwnd, L"MsgPosted", HWND(1));
                    if (!bVBEvisible){
                        SetTimer(HWND(lVBEhwnd), 0, 0, (TIMERPROC)&TimerProc2);
                    }
                }
            }
            SetTimer(hwnd, 0, 0, (TIMERPROC)&TimerProc);
        }
    }

extern "C" __declspec(dllexport) void UnSubClass(HWND hwnd)
    {
        if (bUnsub == FALSE) {
            bSub = FALSE;
            bUnsub = TRUE;
            RemoveCBTHook();
            SetWindowLongPtr(lwkbHwnd, -4, lOldProcWkb);
            SetWindowLongPtr(lVBEhwnd, -4, lOldProcVBE);
            SetWindowLongPtr(lXLhwnd, -4, lOldProc);
            if ((!bClosing)&&(bVBEreset)){
                bVBEreset = FALSE;
                MessageBox(lXLhwnd, TEXT("The VBE was reset.") TEXT("\n") TEXT("Excel has been safely Un-Subclassed."), \
                    TEXT("Oops !!"), 0x00000040 + 0x00001000);
            }
        }
    }

void CALLBACK TimerProc(HWND hwnd, UINT uMsg, UINT timerId, DWORD dwTime)
    {
        KillTimer(lXLhwnd, 0);
        bSub = TRUE;
        bUnsub = FALSE;
        lOldProcWkb = (LONG_PTR)SetWindowLongPtr(lwkbHwnd, -4, (LONG_PTR)&WinProcWkb);
        lOldProc = (LONG_PTR)SetWindowLongPtr(lXLhwnd, -4, (LONG_PTR)&WinProc);
        lOldProcVBE = (LONG_PTR)SetWindowLongPtr(lVBEhwnd, -4, (LONG_PTR)&WinProcVBE);
    }

LRESULT CALLBACK WinProc(HWND hwnd, UINT uMsg, WPARAM wParam, LPARAM lParam)
    {
        if (uMsg == WM_SYSCOMMAND) {
            if (wParam == SC_CLOSE) {
                bUnsub = FALSE;
                bClosing = TRUE;
                UnSubClass(lXLhwnd);
            }
        }
        LRESULT Ret = CallWindowProc((WNDPROC)lVBACallback, hwnd, uMsg, wParam, lParam);
        if (Ret == -1) {
            return Ret; /*abort message*/
        }
        return  CallWindowProc((WNDPROC)lOldProc, hwnd, uMsg, wParam, lParam);
    }

LRESULT CALLBACK WinProcWkb(HWND hwnd, UINT uMsg, WPARAM wParam, LPARAM lParam)
    {
        if (uMsg == WM_SYSCOMMAND) {
            if (wParam == SC_CLOSE) {
                bUnsub = FALSE;
                bClosing = TRUE;
                RemoveProp(lVBEhwnd, L"MsgPosted");
                UnSubClass(lXLhwnd);
            }
        }
    return  CallWindowProc((WNDPROC)lOldProcWkb, hwnd, uMsg, wParam, lParam);
    }

LRESULT CALLBACK WinProcVBE(HWND hwnd, UINT uMsg, WPARAM wParam, LPARAM lParam)
    {

        if (uMsg == WM_SETTEXT) {  /*The VBE was reset other by the user or than by an error !!*/
            bUnsub = FALSE;
            bVBEreset = TRUE;
            UnSubClass(lXLhwnd);
            SetActiveWindow(lVBEhwnd);
        }
        return  (LRESULT)CallWindowProc((WNDPROC)lOldProcVBE, hwnd, uMsg, wParam, lParam);
    }

LRESULT CALLBACK CBTProc(int nCode, WPARAM wParam, LPARAM lParam)
    {
        WCHAR className[MAX_PATH];
        if (nCode == HCBT_CREATEWND) {
            GetClassName(HWND(wParam), className, MAX_PATH);
            if (0 == lstrcmp(className, TEXT("wndclass_desked_gsk"))) {
                if (GetProp(lVBEhwnd, L"MsgPosted") == 0) {
                            SendMessage(HWND(wParam), WM_SETREDRAW, 0 , 0);
                            PostMessage(HWND(wParam), WM_USER + 0xC44, 0x30, 0);
                            PostMessage(HWND(wParam), WM_USER + 0xC44, 0x33, 0);
                            PostMessage(HWND(wParam), WM_USER + 0xC44, 0x83, 0);
                            SendMessage(HWND(wParam), WM_SETREDRAW, 1, 0);
                            lVBEhwnd = HWND(wParam);
                            SetProp(lVBEhwnd, L"MsgPosted", HWND(1));
                            SetTimer(HWND(wParam), 0, 0, (TIMERPROC)&TimerProc2);
                }
            }

        }

        if (nCode == HCBT_ACTIVATE) { /*Check if a VBA runtime or compile error occurred.*/
            GetClassName(HWND(wParam), className, MAX_PATH);
            if (0 == lstrcmp(className, TEXT("#32770"))) {
                CHAR buffer[MAX_PATH] = { 0 };
                GetWindowTextA(HWND(wParam), buffer, MAX_PATH);
                if (strncmp(buffer, "Microsoft Visual Basic", 22) == 0) {
                     HWND staticHwnd = 0;
                     staticHwnd  = GetDlgItem(HWND(wParam), 0x00000000000012C3); /*Error Static Control*/
                     GetWindowTextA(staticHwnd, buffer, MAX_PATH);\
                        /* English language office*/
                     if ((strncmp(buffer, "Run-time error", 14) == 0) || (strncmp(buffer, "Compile Error:", 14) == 0) ||\
                        /* French language office*/
                         (strncmp(buffer, "Erreur d'exécution", 18) == 0) || (strncmp(buffer, "Erreur de compilation:", 22) == 0) ||\
                        /* Spanish office*/
                         (strncmp(buffer, "Se ha producido el error", 24) == 0) || (strncmp(buffer, "Error de compilación:", 21) == 0)){
                         bUnsub = FALSE;
                         bVBEreset = TRUE;
                         UnSubClass(lXLhwnd);
                         SetActiveWindow(lVBEhwnd);
                     }
                }
            }
    }

    return CallNextHookEx(hookHandle, nCode, wParam, lParam);
}

void SetCBTHook()
    {
        HMODULE hInstance = NULL;
        hInstance = GetModuleHandle(L"XlSubClass.dll");
        hookHandle = SetWindowsHookEx(WH_CBT, (HOOKPROC)CBTProc, hInstance, 0);
    }

void RemoveCBTHook()
    {
        { UnhookWindowsHookEx(hookHandle); }
    }

void CALLBACK TimerProc2(HWND hwnd, UINT uMsg, UINT timerId, DWORD dwTime)
    {
        KillTimer(lVBEhwnd, 0);
        ShowWindow(lVBEhwnd, 0);
    }

</windows.h>

 

[Kurt369]

Hi Jaafar,

I know this thread is old, but ... I've been following it with great interest and I find the subclass approach to be excellent, especially the integration of C++ DLLs.

For some time now I've been busy creating DLLs and calling them from VBA. For 32-bit it works really fine, but 64-bit doesn't work at all.
Run-time error '453' is always displayed and depends.exe indicates that the my 64-bit DLL does not export any functions.

I use VS2010, 2017 and 2019 and since this is not a C++ thread, I don't think it would be appropriate to go into further details here ...

Hence a big request: if you managed to create working 64-bit DLLs, may I kindly ask you to upload the VS project file or alternatively a sample framework with empty functions?

I would really appreciate your help.

Regards
Kurt

 

[Jaafar Tribak]

@Kurt369

I tested the x64bit code on my new 64bit Excel installation and x64 OS and indeed, just like you said, it doesn't work. It frezes the entire application.

I am afraid, I can't promise anything as I don't have VS installed at the moment. If anything comes up, I will post back.

 

[6diegodiego9]

@Kurt369

I tested the x64bit code on my new 64bit Excel installation and x64 OS and indeed, just like you said, it doesn't work. It frezes the entire application.

I am afraid, I can't promise anything as I don't have VS installed at the moment. If anything comes up, I will post back.

Same on my new 64bit Excel: it crashes