VBA Application.Run error (The macro may not be available in this workbook or all macros may be disabled)

Question

New question:

My original question is answered and the solution is perfectly fine. However, this resulted in another question. Here is the process flow:

macroFile1 Sub1 (calls macroFile2 Sub3 and passes arguments) -> macroFile2 Sub3 (does some process and is expected to return multiple values to macroFile1 Sub1)

macroFile1 Sub1 is able to call macroFile2 Sub3, but macroFile2 Sub3 is not able to return values. I have tried the following methods:

method1:

'(macroFile1)
returnCodes = Application.Run("'" & macroFileName & "'!pasteCapture", _
thisWorkbookName, selectedFile, cmntText, workingSheet, cmntRowNum, cmntColNum, imgRowNum, imgColNum, size, clrTyp)

Error upon execution: Compile error: Can't assign to array

method2:

'(macroFile1)          
Sub1 
'(This sub is going to call macroFile2 Sub3, and macroFile2 Sub3 is going to do some process, and then return values to the following Sub2.)

Application.Run "'" & macroFileName & "'!pasteCapture", _
thisWorkbookName, selectedFile, cmntText, workingSheet, cmntRowNum, cmntColNum, imgRowNum, imgColNum, size, clrTyp
'thisWorkbookName is the absolute path of the file containing macroFile1. I am passing this to macroFile2 Sub3 so that I can call macroFile1 Sub2 from macroFile2 Sub3.

End Sub

Sub2 
'(I wrote this sub so that I can receive return codes from macroFile2 Sub3. This sub is right below macroFile1 Sub1.)

Sub getReturnCodes(extError, fileNotFound, opnError, worksheetNotFound, rowNotNumeric, rowOutOfScope, colNotNumeric, colOutOfScope, sizeNotNumeric, sizeOutOfScope, incorrectClrTyp, success)

If success = 0 Then
    MsgBox "Successful", vbOKOnly
Else
    MsgBox "Error", vbOKOnly
End If

End Sub

'(macroFile2)
Sub3 
'(This sub will receive multiple arguments from macroFile1 Sub1, process them, and call macroFile1 Sub2 to pass multiple return values.)

'Some process...

Application.Run "'" & calledBy & "'!getReturnCodes", _
extError, fileNotFound, opnError, worksheetNotFound, rowNotNumeric, rowOutOfScope, colNotNumeric, colOutOfScope, sizeNotNumeric, sizeOutOfScope, incorrectClrTyp, success

End Sub

Error upon execution: The macro may not be available in this workbook or all macros may be disabled

Please help. I want to pass multiple values as return values to macroFile1. I am allowed to pass them to either Sub1 or Sub2.

Original question:

I am getting an error while calling a macro from another macro file. I have seen many similar posts and tried the suggested solutions; however, I am still getting the error.

Here is the code:

'The following program is supposed to call another program

Sub screenCapture_Click()

Dim selectedFile As String 'Full path of the selected file

Const rowNum As Integer = 1
Const colNum As Integer = 1
Const workingSheet As Integer = 1
Const size As Variant = 100
Const clrTyp As Variant = 1

With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False

        If .Show = False Then
            Exit Sub
        Else
            selectedFile = .SelectedItems(1)
            Application.Run ("'ScreenCapture子プログラム.xlsm'!pasteCapture (" & selectedFile & "," & rowNum & "," & colNum & "," & workingSheet & "," & size & "," & clrTyp & ")") **
            'This is where the error occors. Application.Run is able to open 'ScreenCapture子プログラム.xlsm', but it is not able to call the macro...**
        End If
End With

End Sub

'The above program calls the following program

Sub pasteCapture(selectedFile, rowNum, colNum, workingSheet, size, clrTyp)

Dim capture As Image 'Captured image
Dim capturesFile As Workbook

EnterCommentUserForm.Show
.
.
.

End Sub

Can you please help me?

rondebruin.nl/win/s9/win001.htm That's not how you pass arguments. Also, Run doesn't open the file with the macro - it needs to already be open. — Tim Williams
Hi Tim, Thanks for sharing the link. I tried passing the arguments as shown on rondebruin.nl/win/s9/win001.htm this page (Application.Run "'" & TestWkbk.Name & "'!MacroNameHere", "parm1", "parm2"). But it just does not work... — RebornMe
What does it do instead of work? Please update your post to add the modified call. — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2020-01-28T06:06:19

Should look more like this:

Application.Run "'ScreenCapture子プログラム.xlsm'!pasteCapture", _
                  selectedFile, rowNum, colNum, workingSheet, size, clrTyp

This is a useful reference: https://www.rondebruin.nl/win/s9/win001.htm

Note also the workbook with the macro being called must already be open.

VBA Application.Run error (The macro may not be available in this workbook or all macros may be disabled)

1 Answers