Excel XLL Add-ins with twinBASIC (backwards compatible with VBA language)

[fafalone]

So I was told that this community would be interested in a way to make XLL addins using your VBA language skills rather than need to learn C/C++ or other entirely different languages.

If you haven't heard of twinBASIC before, its a backwards compatible successor to VB6, with VBA7 syntax for 64bit support, currently under development in late beta. FAQ

XLL addins are just renamed standard dlls, and tB supports creating these natively (Note: it can also make standard activex/com addins for Office apps, and ocx controls). So I went ahead and ported the Excel SDK definitions from xlcall.h to tB, then ported a simple Hello World addin as a proof of concept it's possible to make these without too much difficulty:

    [DllExport]
    Public Function xlAutoOpen() As Integer
 
        Dim text As String = StrConv("Hello world from a twinBASIC XLL Addin!", vbFromUnicode)
        Dim text_len As Long = Len("Hello world from a twinBASIC XLL Addin!")
        Dim message As XLOPER
        message.xltype = xltypeStr
       
        Dim pStr As LongPtr = GlobalAlloc(GPTR, text_len + 2) 'Excel frees it, that's why this trouble
        CopyMemory ByVal VarPtr(message), pStr, LenB(pStr)
        CopyMemory ByVal pStr, CByte(text_len), 1
        CopyMemory ByVal pStr + 1, ByVal StrPtr(text), text_len + 1
 
        Dim dialog_type As XLOPER
        dialog_type.xltype = xltypeInt
        Dim n As Integer = 2
        CopyMemory ByVal VarPtr(dialog_type), n, 2
 
        Excel4(xlcAlert, vbNullPtr, 2, ByVal VarPtr(message), ByVal VarPtr(dialog_type))
        Return 1
    End Function

Pretty much all the difficulty is dealing with that nightmarish XLOPER type. It's full of unions and internal structs neither VBx nor tB (yet) supports. So I substituted LongLong members to get the right size and alignment, then fortunately the main union is the first member so all data is copied to VarPtr(XLOPER). Assigning it without CopyMemory would be at the wrong spot in memory most of the time because of how unions are laid out internally.

So a little complicated, and I did use some of tB's new syntax/features, but still way more accessible than C/C++ imo!

For complete details on how and full source code, check out the project repository:

GitHub - fafalone/HelloWorldXllTB: Making Excel XLL Addins in twinBASIC

Making Excel XLL Addins in twinBASIC. Contribute to fafalone/HelloWorldXllTB development by creating an account on GitHub.

github.com

 

[Dan_W]

TwinBasic is already opening up new possibilities for both Excel and VBA users. I had no idea that this was also a possibility. Great job.

 

[Jaafar Tribak]

I've just seen this. Thanks fafalone ... This will be very useful.

 

[fafalone]

So my initial proof of concept wasn't very useful, just showing a messagebox on load. I've now made a followup which shows how to create User-Defined Functions in XLLs. Additionally, I've added helper functions to the SDK to wrap many of the gory details of handling XLOPER12 types, especially for Strings. XLL UDFs directly execute native compiled code, making them substantially faster than the P-Code interpreter that runs regular Office VBA. Once twinBASIC supports LLVM optimization in the near future, it will go from 'substantially faster' to 'completely blows it out of the water'.


There's a much more detailed writeup in the GitHub repo.


GitHub - fafalone/TBXLLUDF: twinBASIC XLL UDF Addin Example

 

[VB6 Programming]

Good to see the twinBASIC programming language being used to replace VBA as well as VB6