This article shows a solution for providing one DLL library for both VBA and Python users.
Download
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
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.
#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:
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.
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:
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.
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
- 5th December, 2022: Initial version
<object data-extension-version="1.2.0.158" data-supports-flavor-configuration="true" id="__symantecMPKIClientMessenger" style="display: none;">__PRESENT
<object data-extension-version="1.2.0.158" data-supports-flavor-configuration="true" id="__symantecMPKIClientMessenger" style="display: none;">__PRESENT