HOW TO: Creating a Standard (non-ActiveX) DLL with twinBASIC

twinBASIC makes it easy to develop a standard DLL with functions that you can reference from VBA using Declare statements.

In the past, if you wanted to develop a standard DLL, you had two realistic programming language options: C and C++.  In other words, if you were a VBA developer, you had no realistic programming language options.  twinBASIC is changing all of that.

If you can write VBA, then you can build a standard DLL with twinBASIC.

By the way, if you're not sure whether you should develop a standard DLL or an ActiveX COM DLL, check out my earlier article on the topic:

Creating a Standard (non-ActiveX) DLL

If you have a spare 15 minutes, you can follow these steps to create a simple DLL.

Until now, standard DLLs were generally built with C++ or C.  Let's just say that's not the best programming language fit for a VBA developer (present company included).  In that sense, twinBASIC makes creating a standard, non-ActiveX DLL a realistic possibility for VBA developers for the first time ever.

A Few Caveats
twinBASIC is very much a work in progress.  The custom IDE is constantly changing, currently averaging several new releases each week as it is still in its early beta stages.  The sample project I reference is also likely to change and evolve over time.  Depending on when you read this, some of the specifics may differ.  However, the overall approach should be largely the same.

Without further ado, let's jump right into the steps.

Step-by-Step

1. Install twinBASIC

Get the latest step-by-step instructions for installing twinBASIC at HOW TO: Install twinBASIC.

2. Create a new Standard DLL Project

1. Click the "Standard DLL (non-ActiveX)" button on the dialog that appears at startup
2. Click "Open"

Note: If you missed it at startup, simply close and re-open the twinBASIC IDE to show the above dialog box.

3. View/Modify the MainModule.twin Code

1. Click on MainModule.twin in the Sources folder of the PROJECT EXPLORER pane
2. Notice the [ DllExport ] attribute above the MyCoolFunction subroutine
3. Also, note the comment below the routine that shows how to reference the function from VBA/VB6

4. Save and Build the Project

1. Click the disk icon to "Save all changes" and choose a location to save your project (I recommend saving it in an empty subfolder)
2. Choose the "win32" or "win64" build configuration to match your VBA bitness (this is very important!)
3. Click the build icon to "Build" the DLL
4. Check the DEBUG CONSOLE pane for any error messages

5. Declare the Function in a VBA Module

1. Open Microsoft Access (or Excel or Word or etc.)
2. Switch to the VBA Editor (Alt + F11)
3. Create a new standard module*
4. Add code to the module as shown below
5. Execute TestMyCoolFunction from the immediate window

^{* This works just as well from class modules, too; I'm using a standard module here because it requires fewer steps to demonstrate.}

' Be sure to update the path below to match the path in your Debug Console
Declare PtrSafe Sub MyCoolFunction Lib _
   "C:\Users\Mike\Documents\Sample DLL\Build\NewProject_win64.dll" ()

Sub TestMyCoolFunction()
    MyCoolFunction
End Sub

6. Handling Parameters and Function Return Types

1. Go back to twinBASIC
2. Add the code below
3. Save and build the DLL as before

    [ DllExport ]
    Public Function AddNums(NumX As Integer, _
                            NumY As Integer, _
                            Msg As String) As String
    	Return Msg & ": " & (NumX + NumY)
    End Function

7. Add an Associated Declare Line in VBA

1. Switch back to VBA
2. Add the code below
3. In the immediate window, run ?AddNums(1, 2, "Sum")

' Be sure to update the path below to match the path in your Debug Console
Declare PtrSafe Function AddNums Lib _
   "C:\Users\Mike\Documents\Sample DLL\Build\NewProject_win64.dll" _
   (NumX As Integer, NumY As Integer, Msg As String) As String

Building the Declare Lines

Probably the biggest downside to using a standard DLL in VBA is that you need to Declare every routine that you want to use from VBA.

Luckily, this is a straightforward process with twinBASIC.  One of the really nice things about building a standard DLL in twinBASIC rather than C++ is that you don't need to worry about translating data types between C++ and VBA.  

You can generally use the following algorithm to create a Declare statement in VBA for your DLLExported routines:

1. Copy the routine's signature from twinBASIC (the signature is the line of code that begins with Function or Sub)
2. In VBA, go to the header section of a code module
3. On a new line, type Declare PtrSafe
4. Add a space then paste the contents of the clipboard (the function signature from earlier)
5. Place the cursor between the name of the Function/Sub and the left parenthesis ("(")
6. Type Lib then the full path to the DLL
7. Add underscores as line continuation characters as needed for readability

Image by Erkut2 from Pixabay

UPDATE [22-08-12]: Update instructions for installing twinBASIC.