August 17, 2021

Using Application.Run in Excel VBA

How to use Application.Run in Excel VBA to run a macro from another workbook automatically. Watch the video:

How to use Application.Run in Excel

How can we use Application.Run in Excel?
If we wish to run a macro from an event or from another macro in the same workbook or Excel file we call the macro like this in your code :

Call MyMacroName

We do not use Call but it makes it clearer when we read the code. It is evident that another macro is being called.

But what if wish to run a macro that is in another workbook. We can use the same strategy to run an Add-in.
We can use Application.Run code as shown below if both the files, that is, the macro requesting and macro containing files are open:

Application.Run “‘data-for-printing.xlsm’!PrintHeaderOnFirstPage”

data-for-printing.xlsm is the file that contains the macro and it is important to enclose the file name in single quotes. We also place an exclamation mark after the file name. Doesn’t this remind you of accessing a cell from another worksheet?

PrintHeaderOnFirstPage is the name of the macro in the file data-for-printing.xlsm

Given below is the complete macro code to run a macro from another Excel workbook if the file containing the macro VBA code is closed:

Option Explicit

Function IsWorkBookOpen(FileName As String)

Dim FF As Integer, ErrNum As Integer

On Error Resume Next ‘ We turn off error checking
FF = FreeFile() ‘ The inbuilt function gets a free file number.
Open FileName For Input Lock Read As #FF ‘we try to open the file and lock it
Close FF ‘ Close the file
ErrNum = Error ‘ capture the error number
On Error GoTo 0 ‘ Turn on normal error checking
‘Find which error happened
Select Case ErrNum
‘ No error
‘ File is not open
Case 0: IsWorkBookOpen = False

‘ File already open
Case 70: IsWorkBookOpen = True
‘ Some other error occurred. Capture the error number for further action
Case Else: Error ErrNum
End Select
End Function

Sub TestMacroFromAnotherOpenWorkbook()
Dim info
‘we check whether the workbook is open using a function that is shown below
info = IsWorkBookOpen(“C:\excel-training-videos-15\data-for-printing.xlsm”)

If info = False Then
Workbooks.Open FileName:=”C:\excel-training-videos-15\data-for-printing.xlsm”

Windows(“sales-data-by-region-and-rep.xlsm”).Activate

Application.Run “‘data-for-printing.xlsm’!PrintHeaderOnFirstPage”

End If
Windows(“data-for-printing.xlsm”).Close

End Sub

Run Macro from Another Workbook Using Application.Run

One thought on “Using Application.Run in Excel VBA

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.