Using Application.Run to call macro from Personal Macro Workbook

gravanoc

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
346
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I want to run a macro in my Personal Macro Workbook from another workbook. I have not been successful.

I believe that this is the required syntax:
VBA Code: 
Sub Caller()
    
    Dim rngA As Range
    Set rngA = Selection

    ' The next line works when calling a macro in a workbook other than Personal.xlsb.
    Application.Run "'Exporter v2.xlsm'!PrintAddress", rngA

    ' The next line fails.  Why?
    Application.Run "'PERSONAL.XLSB'!PrintAddress", rngA


End Sub

And the sub being called...
VBA Code: 
Public Sub PrintAddress(ByVal Target As Range)
    
    Debug.Print(Target.Address)

End Sub

Additionally, I've tried using the exact path to the file, but no luck.
Thanks!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Here is code that runs successfully here.

VBA Code: 
Option Explicit

Sub RunMacro_NoArgs()
'Macro purpose:  To demonstrate using the run method to execute
'a macro without arguments from another workbook

    Dim wbTarget As Workbook, _
        CloseIt As Boolean
    
    'Attempt to set the target workbook to a variable.  If an error is
    'generated, then the workbook is not open, so open it
    On Error Resume Next
        Set wbTarget = Workbooks(Sheets(1).Range("B9"))
        
        If Err.Number <> 0 Then
            'Open the workbook
            Err.Clear
            Set wbTarget = Workbooks.Open(Sheets(1).Range("B8") _
                & "\" & Sheets(1).Range("B9"))
            CloseIt = True
        End If
        
        'Check and make sure workbook was opened
        If Err.Number = 1004 Then
            MsgBox "Sorry, but the file you specified does not exist!" _
                & vbNewLine & Sheets(1).Range("B8") & "\" & Sheets(1).Range("B9")
            Exit Sub
        End If
    On Error GoTo 0
    
    'Run the macro!  (You will need to update "SimpleMacro" to the
    'name of the macro you wish to run)
    Application.Run (wbTarget.Name & "!SimpleMacro")
    
    If CloseIt = True Then
        'If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
        'If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If

End Sub

Sub RunMacro_WithArgs()
'Macro purpose:  To demonstrate using the run method to execute
'a function or macro (with arguments) from another workbook

    Dim wbTarget As Workbook, _
        Number1 As Long, Number2 As Long, _
        Mynum As Variant, _
        CloseIt As Boolean
    
    'Get values from user
    Number1 = InputBox("Please enter a number")
    Number2 = InputBox("Please enter a second number")
    
    'Attempt to set the target workbook to a variable.  If an error is
    'generated, then the workbook is not open, so open it
    On Error Resume Next
        Set wbTarget = Workbooks.Open(Sheets(1).Range("B9"))
        
        If Err.Number <> 0 Then
            'Open the workbook
            Err.Clear
            Set wbTarget = Workbooks.Open(Sheets(1).Range("B8") _
                & "\" & Sheets(1).Range("B9"))
            CloseIt = True
        End If
        
        'Check and make sure workbook was opened
        If Err.Number = 1004 Then
            MsgBox "Sorry, but the file you specified does not exist!" _
                & vbNewLine & Sheets(1).Range("B8") & "\" & Sheets(1).Range("B9")
            Exit Sub
        End If
    On Error GoTo 0
    
    'Run the function, and give user the results
    Mynum = Application.Run(wbTarget.Name & "!EasyMath", Number1, Number2)
    MsgBox Number1 & "+" & Number2 & "=" & Mynum

    If CloseIt = True Then
        'If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
        'If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If

End Sub

Download sample workbooks : RunMacroOtherWorkbook.zip
 
Upvote 0
I placed PrintAddress in my personal macro work, and then I ran Caller from a new workbook. The code ran successfully.

Which error did you get, and on which line did it occor?
 
Upvote 0
Logit said:
Here is code that runs successfully here.

VBA Code: 
Option Explicit

Sub RunMacro_NoArgs()
'Macro purpose:  To demonstrate using the run method to execute
'a macro without arguments from another workbook

    Dim wbTarget As Workbook, _
        CloseIt As Boolean
 
    'Attempt to set the target workbook to a variable.  If an error is
    'generated, then the workbook is not open, so open it
    On Error Resume Next
        Set wbTarget = Workbooks(Sheets(1).Range("B9"))
     
        If Err.Number <> 0 Then
            'Open the workbook
            Err.Clear
            Set wbTarget = Workbooks.Open(Sheets(1).Range("B8") _
                & "\" & Sheets(1).Range("B9"))
            CloseIt = True
        End If
     
        'Check and make sure workbook was opened
        If Err.Number = 1004 Then
            MsgBox "Sorry, but the file you specified does not exist!" _
                & vbNewLine & Sheets(1).Range("B8") & "\" & Sheets(1).Range("B9")
            Exit Sub
        End If
    On Error GoTo 0
 
    'Run the macro!  (You will need to update "SimpleMacro" to the
    'name of the macro you wish to run)
    Application.Run (wbTarget.Name & "!SimpleMacro")
 
    If CloseIt = True Then
        'If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
        'If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If

End Sub

Sub RunMacro_WithArgs()
'Macro purpose:  To demonstrate using the run method to execute
'a function or macro (with arguments) from another workbook

    Dim wbTarget As Workbook, _
        Number1 As Long, Number2 As Long, _
        Mynum As Variant, _
        CloseIt As Boolean
 
    'Get values from user
    Number1 = InputBox("Please enter a number")
    Number2 = InputBox("Please enter a second number")
 
    'Attempt to set the target workbook to a variable.  If an error is
    'generated, then the workbook is not open, so open it
    On Error Resume Next
        Set wbTarget = Workbooks.Open(Sheets(1).Range("B9"))
     
        If Err.Number <> 0 Then
            'Open the workbook
            Err.Clear
            Set wbTarget = Workbooks.Open(Sheets(1).Range("B8") _
                & "\" & Sheets(1).Range("B9"))
            CloseIt = True
        End If
     
        'Check and make sure workbook was opened
        If Err.Number = 1004 Then
            MsgBox "Sorry, but the file you specified does not exist!" _
                & vbNewLine & Sheets(1).Range("B8") & "\" & Sheets(1).Range("B9")
            Exit Sub
        End If
    On Error GoTo 0
 
    'Run the function, and give user the results
    Mynum = Application.Run(wbTarget.Name & "!EasyMath", Number1, Number2)
    MsgBox Number1 & "+" & Number2 & "=" & Mynum

    If CloseIt = True Then
        'If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
        'If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If

End Sub

Download sample workbooks : RunMacroOtherWorkbook.zip
Click to expand...

Thanks for your reply. I determined that the error is somehow related to the info below.
Domenic said:
I placed PrintAddress in my personal macro work, and then I ran Caller from a new workbook. The code ran successfully.

Which error did you get, and on which line did it occor?
Click to expand...

This is the error dialog I get on the highlighted line:
1662443689545.png


1662443656240.png


I have further determined that it has something to do with it being a range object, instead of a literal type value, such as a string. If I change rngA to String, and then put rngA = Selection.Address, it will run the macro if I also change the receiving parameter to String. That will allow me to use this function, but I'm still interested in knowing why it's rejecting rngA as a Range object. This only seems to occur when the target workbook is Personal.xlsb.
 
Upvote 0
Does the code in your personal macro workbook compile (Visual Basic Editor >> Debug >> Compile VBAProject) when you're trying to pass a Range object?
 
Upvote 0
Which module in personal.xlsb is the code in?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
289
glynn1969
G
JeffGrant
  • Question
Running a macro in ThisWorkBook on an external workbook
Replies
1
Views
209
Alex Blakenburg
A
R
  • Question
Advanced Filter Macro works until I run a different Advanced Filter Macro in same workbook
Replies
1
Views
70
rdarrylhudson
R
starl
  • Solved
Calling Private Worksheet level module from standard module doesn't always work after changing sheet's CodeName
Replies
9
Views
684
starl
starl
P
  • Solved
VBA to open file using Cell for the path
Replies
4
Views
531
Peter Davison
P

Forum statistics

Threads
1,214,376
Messages
6,119,174
Members
448,870
Latest member
max_pedreira

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top