Can Excel VBA call Macro in Different Macro Workbook

Question

Can VBA code in an excel spreadsheet call a different XLSM Workbook file's Macro?

I have a local XLSM that needs to call a method in a different XLSM file, how can that be done?

Solution 1

Open file workbook, close if already open

codeWorkBookPath = "Directory\"
codeWorkBookName = "file.xlsm"
codeWorkBookFullPathName = UCase(Trim(codeWorkBookPath)) &         
UCase(Trim(codeWorkBookName))
codeWorkbookAlreadyOpen = CommonWorkbook.isWbOpen(codeWorkBookName)
If Not codeWorkbookAlreadyOpen Then
 Workbooks.Open Filename:=codeWorkBookFullPathName, UpdateLinks:=False, 
 ReadOnly:=True
End If

activate the starting spreadsheet

Workbooks(startWorkBook).Activate
Sheets(startWorkSheet).Activate

Run the macroName

argString = "'" & Trim(codeWorkBookName) & "'!" & Trim(macroName)
APPLICATION.Run argString

Solution 2

You have to use the sheet's CODENAME, not the name.

In the image I share, you should used "Sheet1", not "testSheet"