CODEWITHSUNDEEP
excelvba
bvsnkiran
bvsnkiran

Reputation: 35

Functions and Methods available in third party .xll Add in

we have a 3rd party excel Add-in which we registered in excel. Now, how can we see what are all the list of functions and methods available in that .xll. Also, can we see the vba code inside .xll file? I am using 64bit 2016 Excel. Below is the code I tried but its not showing any functions and subs available in .xll file.

Public Sub ListRegisteredXLLFunctions()
    Dim RegisteredFunctions As Variant
    Dim i As Integer

    RegisteredFunctions = Application.RegisteredFunctions


    If IsNull(RegisteredFunctions) Then
        Exit Sub
    Else
        Dim rng As Range
        Set rng = SignalLogs.Range("A1")
        Set rng = rng.Resize(UBound(RegisteredFunctions, 1), UBound(RegisteredFunctions, 2))
        rng.Value = RegisteredFunctions
    End If
End Sub

Upvotes: 3

Views: 735

Answers (1)

stevecu
stevecu

Reputation: 506

Offical Method

Use Application.RegisteredFunctions in VBA but for some reason this doesn't work for the OP.

Quick method

You can run Dependencies and drop the XLL into its main window. The right hand pane will show the entry points to the XLL, which is really just a renamed DLL. Each function registered by the XLL add-in should correspond to an entry point (unless it works dynamically like xlOil or ExcelDNA). There's no guarantee that the name of the registered function in Excel will match the entry point name, but you may be able to figure out a link.

Involved method

We can pretend to be Excel and capture calls to the xlfRegister method. Below I do this in C++ for add-ins using the newer version of the C-API. If it uses the older API we'd instead need to spoof xlcall32.dll in a similar way, defining an Excel4 entry point. We'd know we were in this case if the GetProcAddress for SetExcel12EntryPt fails.

#include <iostream>
#include <windows.h>

// You need this header from the Excel C-API SDK
#include <Excel2013SDK/include/XLCALL.H>
#include <string>  
using namespace std;

// Full path to the target XLL
static wstring TARGET = L"example.xll";

wstring to_wstring(LPXLOPER12 xloper)
{
    if (!xloper) return L"(null)";
    switch (xloper->xltype)
    {
    case xltypeNum:     return to_wstring(xloper->val.num);
    case xltypeBool:    return to_wstring(xloper->val.xbool == 0);
    case xltypeErr:     return L"ERR:#" + to_wstring(xloper->val.err);
    case xltypeMissing: return L"#MISSING";
    case xltypeNil:     return L"#NIL";
    case xltypeInt:     return to_wstring(xloper->val.w);
    case xltypeStr:     return wstring(xloper->val.str + 1, xloper->val.str[0]);
    default:
        return L"#UNKNOWN?";
    }
}

int __stdcall MdCallBack12(int xlfn, int coper, LPXLOPER12* rgpxloper12, LPXLOPER12 result)
{
    // It's possible an XLL may call other functions and except a valid return,
    // so this switch may need expansion to other cases.
    switch (xlfn)
    {
    case xlfRegister:
    {
        wcout << "xlfRegister: " << to_wstring(rgpxloper12[3]) 
              << L", argtypes=" << to_wstring(rgpxloper12[2]) << L" : ";
        // We simply dump the parameters to the register call:
        // Refer to the Excel SDK documentation for their meanings
        for (auto i = 0; i < coper; ++i)
            wcout << to_wstring(rgpxloper12[i]) << L", ";
        cout << endl;
        static int iFunc = 0;
        if (result)
        {
            result->xltype = xltypeNum;
            result->val.num = ++iFunc;
        }
        return xlretSuccess;
    }
    case xlGetInstPtr:
    {
        result->val.bigdata.h.hdata = GetModuleHandle(NULL);
        result->xltype = xltypeBigData;
        return xlretSuccess;
    }
    case xlGetInst:
    {
        result->val.w = (int)GetModuleHandle(NULL);
        result->xltype = xltypeInt;
        return xlretSuccess;
    }
    case xlGetHwnd:
    {
        result->val.w = (int)HWND_DESKTOP;
        result->xltype = xltypeInt;
        return xlretSuccess;
    }
    case xlEventRegister:
    {
        if (result)
        {
            result->val.xbool = 1;
            result->xltype = xltypeBool;
        }
        return xlretSuccess;
    }
    case xlGetName:
    {
        auto len = TARGET.size() + 1;
        auto s = new wchar_t[len];
        s[0] = len;
        wcsncpy_s(s + 1, len, TARGET.c_str(), len);
        result->val.str = s;
        result->xltype = xltypeStr;
        return xlretSuccess;
    }
    default:
        cout << "Called: " << xlfn << endl;
    }
    return xlretSuccess;
}

int main()
{
    auto handle = LoadLibrary(TARGET.c_str());
    if (handle == 0)
    {
        // If this load fails, ensure that xlcall32.dll is on the path. 
        // You can find this DLL in any Excel installation. Also ensure 
        // that any other dependencies the XLL needs can be found on the path.
        cout << "Load failed" << endl;
        return -1;
    }

    auto autoOpen = (int(*__stdcall)())GetProcAddress(handle, "xlAutoOpen");
    if (autoOpen == 0)
    {
        cout << "Cannot find xlAutoOpen. Not a valid XLL." << endl;
        return -1;
    }

    auto setExcel12EntryPt = (void(*__stdcall)(void*))GetProcAddress(handle, "SetExcel12EntryPt");
    if (setExcel12EntryPt == 0)
    {
        cout << "Cannot find SetExcel12EntryPt. Probably uses older C-API." << endl;
        return -1;
    }

    setExcel12EntryPt(&MdCallBack12);
    autoOpen();

    return 0;
}

Make sure you build with the right bitness to match the add-in! Youll need the Excel XLL SDK.

Upvotes: 2

Related Questions