Base on Matt Hall's answer but altered to show how you can, from Access:
- Invoke an Excel module apart from
ThisWorkbook;
- Invoke Excel Subs or retrieve a value from an Excel Function; and
- Fetch the atlered values of parameters passed by reference.
In a custom module, named basTextModule, in Excel:
Public Sub ShowCoolMessage()
MsgBox "cool"
End Sub
' Add02 is explictly ByRef (the default in VBA) to show that
' the parameter will be altered and have its value changed even for
' prodedures higher up the call stack.
Public Function GetCoolAmount(Add01 As Variant, _
Optional ByRef Add02 As Integer) As Integer
Add02 = Add02 + 1
GetCoolAmount = 10 + Add01 + Add02
End Function
In Access:
- Set a reference to Excel (VBA IDE > Tools > Reference ... Microsoft Excel 16.0 Object Library).
- Then create a (somewhat) generic RunExcelCode ...
For parameters passed by reference to work:
Note from Microsoft Docs, Application.Run method (Excel) that when you pass parameters to the Excel Sub or Function "You cannot use named arguments with this method. Arguments must be passed by position".
When declaring excelApp use Object rather than Excel.Application in order to ensure that the value of any parameters passed by reference to excelApp.Run can be retrieved. Source: Jaafar Tribak "Application.Run .. (Argument Passed ByRef)" at https://www.mrexcel.com/board/threads/application-run-argument-passed-byref.998132/post-4790961
In the called sub or Function the parameters (apart from the first ModuleAndSubOrFunctionName) must have a data type that match the datatype of the parmaters for the calling module or function. They can be variants or a specific datatype. E.g, and for illustrative purposes, Arg02 is an Integer and so must the second argument of GetCoolAmount when RunExcelCode(WorkbookPathAndFileName, "basTestModule.GetCoolAmount" ...) is used.
However to make your RunExcelCode more generic it may be wise to ensure Arg01, Arg02, ... Arg30 paramters are all variants; and therefore the parameters of your ultimately called sub or function are also variants, for example ...
Public Function GetCoolAmount(Add01 As Variant, _
Optional ByRef Add02 As Variant) As Integer
...
Public Function RunExcelCode(WorkbookPathAndFileName As String, _
ModuleAndSubOrFunctionName As String, _
Optional ByRef Arg01 As Variant, _
Optional ByRef Arg02 As Integer) As Variant
' Must be Object, not Excel.Application, to allow for parameters pass by reference
Dim excelApp As Object
Dim workbook As Excel.workbook
Dim Result As Variant
On Error GoTo HandleErr
' Can be Excel.Application if excelApp previously declared as Object
Set excelApp = New Excel.Application
' excelApp.Visible = True ' For debugging
Set workbook = excelApp.Workbooks.Open(WorkbookPathAndFileName)
' Get a value from a function or,
' if it is a sub a zero length string "" will be returned
Result = excelApp.Run(ModuleAndSubOrFunctionName, Arg01, Arg02)
RunExcelCode = Result
ExitHere:
workbook.Close
excelApp.Quit
Set workbook = Nothing
Set excelApp = Nothing
Exit Function
HandleErr:
Select Case Err.number
Case Else
MsgBox "Error " & Err.number & ": " & Err.Description, _
vbCritical, "RunExcelCode"
End Select
Resume ExitHere
End Function
Testing (from Access), calling a Sub and a Function:
Private Sub TestRunExcelCode()
Dim WorkbookPathAndFileName As String
Dim Result As Variant
WorkbookPathAndFileName = "C:\Users\YourName\Documents\MyWorkbook.xlsm"
' Run a sub
Result = RunExcelCode(WorkbookPathAndFileName, "basTestModule.ShowCoolMessage")
If IsNull(Result) Then
Debug.Print "{Null}"
ElseIf Result = "" Then
Debug.Print "{Zero length string}"
Else
Debug.Print Result
End If
' Will output "{Zero length string}"
' Get a value from a function
Dim Arg02 As Integer
Arg02 = 1
Debug.Print "Arg02 Before: " & Arg02
Result = RunExcelCode(WorkbookPathAndFileName, _
"basTestModule.GetCoolAmount", 1, Arg02)
Debug.Print "Arg02 After : " & Arg02 ' Value will have changed, as desired.
Debug.Print "Result : " & Result
End Sub
Edit 01: Major change to make code more generic.
Edit 02: Major change to handle paramaters passed by reference.
Edit 03: Added details in the case "to make your RunExcelCode more generic".
