Create a DLL for Both VBA and Python Users

Wayne Jin

5.00/5 (2 votes)

Dec 5, 2022

CPOL

3 min read

11538

209

Extend the function of Microsoft Office and Python

Download

Download demo - 148.6 KB

Introduction

Sometimes, we need to develop DLL Libraries for VBA (Visual Basic for Application) users to extend the functionalities of Microsoft Office Suite (Excel, Word, PPT and Outlook).

What if we need to develop a library with similar features for Python also?

I just wonder whether it is possible to have one library for both VBA and Python.

The advantage is obvious, it's easy to maintain the codes in one project.

After some exploration, I find that Robert Giesecke's Unmanaged Exports Nuget Package is right tool to achieve this purpose.

I have packaged some useful features in this DLL, it may save some precious time for others also.

This library is still under development, if you have any suggestion, please leave your comment below this article. For example, if there is any feature which is in need but missing from Excel or Python, etc.

Using the Code

Please download the demo and extract the whole folder "PythonVbaDemo".

To test it in Excel: Open the "VbaCallDLL.xlsm"
To test it in Python: Open the folder "PythonVbaDemo" with Visual Studio Code

VBA: Just run the VbaTestDllDemo Sub.

Python: run the "PythonCallDLL.py"

Points of Interest

1. Used in VBA

There are 2 version of DLL (32bits and 64bits), how to make the VBA codes auto handle both version, please refer to below code snippet.

To determine whether the exisiting Office is 32bits or 64bits: (Thanks Daniel Pineault)

Function IsOffice32Bit() As Boolean
'https://docs.microsoft.com/en-us/office/troubleshoot/office-suite-issues/numbering-scheme-for-product-guid
'   p => 0=32-bit, 1=64-bit
    IsOffice32Bit = (Mid(Application.ProductCode, 21, 1) = 0)
End Function

Now we can use "change current directory" to let VBA know which DLL it shall refer to

Sub ChangeCurDirToDllFolder()
    Dim sSubfolderName As String
    If IsOffice32Bit() Then
        sSubfolderName = "x86"
    Else
        sSubfolderName = "x64"
    End If
    ChDir ThisWorkbook.Path & "\" & sSubfolderName
End Sub

Below are a list of functions that can be used by VBA:

Vba_PlotFreeSpaceChart
ReloadMediaFile
StartPlayingMediaFile
StopPlayingMediaFile
Sum
InstantiateExtLib
Vba_Echo
EncryptStringTripleDES
DecryptStringTripleDES
GetLibName
GetLibVersion
GetEmpIDByUserID
GetUserIDByEmpID

There are three types of interfaces:

Subroutines which don't return any data
Functions which return primitive data type such as Integer, Double, etc.
Functions which return non-primitive data type such as String

For type 1 and 2, we can use the below declaration to call them in VBA directly.

'
' Interface 1 & 2 declarations:
'
#If VBA7 Then
    Private Declare PtrSafe Sub Vba_PlotFreeSpaceChart _
            Lib "ExtLib4VbaPython.dll" (ByVal sSpaceCheckPathsInCsv As String, _
            ByVal nMinFreeSpacePercent As Integer)
    Private Declare PtrSafe Function Vba_Echo Lib "ExtLib4VbaPython.dll" _
            (ByVal sName As String) As String
    Private Declare PtrSafe Function Sum Lib "ExtLib4VbaPython.dll" _
            (ByVal nNum1 As Integer, ByVal nNum2 As Integer) As Integer
    
    Private Declare PtrSafe Function BytesToBase64 Lib "ExtLib4VbaPython.dll" _
            (bytes() As Byte) As String
    Private Declare PtrSafe Function Base64toBytes Lib "ExtLib4VbaPython.dll" _
            (ByVal sBase64String As String) As Byte()
    
    Private Declare PtrSafe Function EncryptStringTripleDES Lib _
            "ExtLib4VbaPython.dll" (ByVal sPlainText As String) As String
    Private Declare PtrSafe Function DecryptStringTripleDES Lib _
            "ExtLib4VbaPython.dll" (ByVal sEncryptedText As String) As String    
    
    Private Declare PtrSafe Sub Vba_ReloadMediaFile Lib _
            "ExtLib4VbaPython.dll" (ByVal sMediaFileName As String)
    Private Declare PtrSafe Sub StartPlayingMediaFile Lib "ExtLib4VbaPython.dll" ()
    Private Declare PtrSafe Sub StopPlayingMediaFile Lib "ExtLib4VbaPython.dll" ()

    Private Declare PtrSafe Function InstantiateExtLib Lib _
            "ExtLib4VbaPython.dll" (ByVal text As String) As Object
#Else
    Private Declare Function InstantiateExtLib Lib _
            "ExtLib4VbaPython.dll" (ByVal text As String) As Object    
    
    Private Declare Sub Vba_PlotFreeSpaceChart Lib "ExtLib4VbaPython.dll" _
      (ByVal sSpaceCheckPathsInCsv As String, ByVal nMinFreeSpacePercent As Integer)
    Private Declare Function Vba_Echo Lib "ExtLib4VbaPython.dll" _
      (ByVal sName As String) As String
    Private Declare Function Sum Lib "ExtLib4VbaPython.dll" _
      (ByVal nNum1 As Integer, ByVal nNum2 As Integer) As Integer
    
    Private Declare Function BytesToBase64 Lib "ExtLib4VbaPython.dll" _
     (bytes() As Byte) As String
    Private Declare Function Base64toBytes Lib "ExtLib4VbaPython.dll" _
     (ByVal sBase64String As String) As Byte()
    
    Private Declare Function EncryptStringTripleDES Lib "ExtLib4VbaPython.dll" _
     (ByVal sPlainText As String) As String
    Private Declare Function DecryptStringTripleDES Lib "ExtLib4VbaPython.dll" _
     (ByVal sEncryptedText As String) As String
    
    Private Declare Sub Vba_ReloadMediaFile Lib "ExtLib4VbaPython.dll" _
      (ByVal sMediaFileName As String)
    Private Declare Sub StartPlayingMediaFile Lib "ExtLib4VbaPython.dll" ()
    Private Declare Sub StopPlayingMediaFile Lib "ExtLib4VbaPython.dll" ()
#End If

For type 3 interface, we have to use the below method to call them:

'
' Interface 3 calling:
'
    Dim oExtLib As Object
    Set oExtLib = InstantiateExtLib("Alarm01.wav")
    Debug.Print oExtLib.Vba_Hello(Application.UserName)
    Debug.Print oExtLib.MediaFileName
    
    Debug.Print "Lib Name: " & oExtLib.Vba_GetLibName()
    Debug.Print "Lib Version: " & oExtLib.Vba_GetLibVersion()
    Debug.Print "Lib EmpID: " & oExtLib.Vba_GetEmpIDByUserID("Your User ID")
    Debug.Print "Lib UserID: " & oExtLib.Vba_GetUserIDByEmpID("Your Emp ID")

2. Used in Python

Below are a list of functions that can be used by Python:

Py_PlotFreeSpaceChart
ReloadMediaFile
StartPlayingMediaFile
StopPlayingMediaFile
Sum
InstantiateExtLib
Py_Echo
Py_EncryptStringTripleDES
Py_DecryptStringTripleDES
Py_GetLibName
Py_GetLibVersion
Py_GetEmpIDByUserID
Py_GetUserIDByEmpID

There are three type of interfaces:

Subroutines which don't return any data
Functions which return primitive data type such as Integer, Double, etc.
Functions which return non-primitive data type such as String

For type 1 and 2, we can use the below declaration to call them in Python directly.

#
# Interface 1 & 2 declarations:
#
import ctypes
import os
from ctypes import *
from comtypes.automation import VARIANT

full_path = os.path.realpath(__file__)
sPath, sFilename = os.path.split(full_path)
sDllPath = sPath + "\\x64\\ExtLib4VbaPython.dll"

oExtLib = ctypes.cdll.LoadLibrary(sDllPath)

print ("3 + 5 = " + str(oExtLib.Sum(3, 5)))

oExtLib.Py_PlotFreeSpaceChart ('C:', 10)

For type 3 interface, we have to use the below method to call them:

#
# Interface 3 calling:
#
import ctypes
import os
from ctypes import *
from comtypes.automation import VARIANT

def Hello(name, oExtLib):
    oExtLib.Py_Hello.argtypes = [VARIANT, POINTER(VARIANT)]
    v = VARIANT()
    oExtLib.Py_Hello(name, v)
    return v.value

full_path = os.path.realpath(__file__)
sPath, sFilename = os.path.split(full_path)
sDllPath = sPath + "\\x64\\ExtLib4VbaPython.dll"

oExtLib = ctypes.cdll.LoadLibrary(sDllPath)

print (Hello('Wayne', oExtLib))

Known Issue

Play media works in VBA but not in Python, if anyone knows the reason, please point out, many thanks.

#
# Known issues: Same function in VBA call, it will play the music, but not in Python
#
import ctypes
import os
from ctypes import *
from comtypes.automation import VARIANT

full_path = os.path.realpath(__file__)
sPath, sFilename = os.path.split(full_path)
sDllPath = sPath + "\\x64\\ExtLib4VbaPython.dll"

oExtLib = ctypes.cdll.LoadLibrary(sDllPath)

oExtLib.Py_ReloadMediaFile('')
oExtLib.StartPlayingMediaFile()

Credits

History

5^th December, 2022: Initial version