twinBASIC makes it easy to develop a standard DLL with functions that you can reference from VBA using
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.
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
- Click the "Standard DLL (non-ActiveX)" button on the dialog that appears at startup
- 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
- Click on MainModule.twin in the Sources folder of the PROJECT EXPLORER pane
- Notice the
[ DllExport ]attribute above the MyCoolFunction subroutine
- Also, note the comment below the routine that shows how to reference the function from VBA/VB6
4. Save and Build the Project
- Click the disk icon to "Save all changes" and choose a location to save your project (I recommend saving it in an empty subfolder)
- Choose the "win32" or "win64" build configuration to match your VBA bitness (this is very important!)
- Click the build icon to "Build" the DLL
- Check the DEBUG CONSOLE pane for any error messages
5. Declare the Function in a VBA Module
- Open Microsoft Access (or Excel or Word or etc.)
- Switch to the VBA Editor (Alt + F11)
- Create a new standard module*
- Add code to the module as shown below
TestMyCoolFunctionfrom 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
- Go back to twinBASIC
- Add the code below
- 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
- Switch back to VBA
- Add the code below
- 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
- Copy the routine's signature from twinBASIC (the signature is the line of code that begins with
- In VBA, go to the header section of a code module
- On a new line, type
- Add a space then paste the contents of the clipboard (the function signature from earlier)
- Place the cursor between the name of the Function/Sub and the left parenthesis ("
Libthen the full path to the DLL
- Add underscores as line continuation characters as needed for readability
UPDATE [22-08-12]: Update instructions for installing twinBASIC.