Creating a twinBASIC ActiveX DLL

To learn more about twinBASIC, join me at this year's virtual Access DevCon where I will be presenting this exciting new project from vbWatchdog creator, Wayne Phillips.

Enough blather, let's make something useful

I've written several articles about writing and debugging code in twinBASIC recently.  But if you're reading this blog, it's probably because you're an Access (or VBA) developer.  In which case, you've probably been reading these articles and feeling a bit like the Bobs in Office Space asking Tom Smykowski, "What would you say...ya do here?"

Creating an ActiveX DLL

Now that twinBASIC's public preview has gone live, let's make something useful with it.  We'll start by creating a simple ActiveX DLL that we can call from an Access application.

Getting started

Begin by following the instructions in the above link to get twinBASIC up and running.  Here's a brief checklist of what you'll need to do:

  • Install VS Code
  • Install the twinBASIC extension in VS Code
  • Download the project files under Sample 3 - A Simple ActiveX DLL
  • Extract the two files
  • In VS Code: File > Open Workspace > myLibrary.code-workspace > [Open]

Build the library

Click the Build button in the bottom right corner of the EXPLORER pane under the TWINBASIC section:

VS Code: Explorer (Ctrl + Shift + E) > twinBASIC > Build

The Debug Console will show the following output:

[LINKER] SUCCESS created output file 'C:\Path\To\twinBASIC_myCodeLibrary\Build\MyTestLibraryProject_win32.dll'
[COMPILER] SUCCESS registered COM classes for file 'C:\Path\To\twinBASIC_myCodeLibrary\Build\MyTestLibraryProject_win32.dll' [this user only]
Build complete!

Open a 32-bit VBA host application

This can be Access or Excel or Word or...you get the idea.  The important thing is it must be 32-bit.  The current twinBASIC preview does not yet support 64-bit VBA.

Add a reference to the COM library

  • Switch to the VBA IDE ([Alt] + [F11])
  • Tools > References...
  • Check the box next to MyTestLibrary project
  • Click [OK]

Confirm early binding works

Create a new standard module and add the following code:

Sub TestEarlyBinding()    
    Dim Obj As New MyTestLibrary
    Debug.Print Obj.MultiplyByTen(42)    
End Sub

Now, let's make sure it works:

Looks good. I can independently confirm that 42 multiplied by ten equals 420.

Now let's test late binding

Next, let's see if we can use late binding instead of early binding to call the MultiplyByTen function.

Sub TestLateBinding()
    
    Dim Obj As Object
    Set Obj = CreateObject("MyTestLibraryProject.MyTestLibrary")
    Debug.Print Obj.MultiplyByTen(55)
    
End Sub

Unfortunately, running the TestLateBinding function blew up in my face with the following error on the Debug.Print line:

That's a super helpful error message, VBA.

After a bit of troubleshooting, I thought maybe I should re-read the current limitations of the twinBASIC preview.  Sure enough, I found my answer there:

Current Preview State

  • Late-bound calls to twinBASIC class objects are disabled
  • DllRegisterServer is not currently implemented in built ActiveX DLLs (the IDE registers it appropriately for now)

That second bullet explains why regsvr32 isn't working and–more critically–why I can't unregister libraries using regsvr32 /u.  

The IDE registers twinBASIC ActiveX DLLs, but I don't see any way to use the IDE to unregister previously registered DLLs (which is too bad, because I've managed to register about four or five similarly-named twinBASIC DLLs in the course of testing out the new functionality).

Image by Pexels from Pixabay