Calling a sub using a variable name or range value

srejja
srejja used Ask the Experts™
on
Is it possible to call a subprocedure  within VBA (Excel 2007) using a variable or value from a cell in a worksheet?  If so, please provide code to do so.

Here is the pseudocode of what I'm trying to accomplish:

Cell D3 value is "Y"
Cell E3 value is "TestMacro"

sub test1()
If Range("D3") = "Y" Then
Call Range("E3").Value
Else: End
end sub

sub TestMacro()
MsgBox "Test Successful"
end sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
Yes, it is:

Application.Run "'Name of an open workbook.xls'!" & Range("e3")

Author

Commented:
Received an error.  Note that outside of this snippet of code everything else works properly.

The error was on:
Application.Run ThisWorkbook.Sheets("Template").Range("E" & i)

The error verbatim:
Run-time error '1004':
Method 'Run' of object '_Application' failed

Here is the actual code i ran:
Dim Counter as Integer
Range("q1").Formula = "=COUNTA(A2:A65536)"
Counter = Range("q1").Value + 1
Dim i As Integer
For i = 5 To Counter
Workbooks.Open Filename:=Range("A" & i)  'Opens the workbook all code within this sub is applied to
If ThisWorkbook.Sheets("Template").Range("D" & i) = "Y" Then
Application.Run ThisWorkbook.Sheets("Template").Range("E" & i)
'Application.Run "'QC Audits Report GeneratorRange.xlsm'!" & Range("E" & i)
End If
Next
Ardhendu SarangiSr. Project Manager
Commented:
Hi,

This code worked for me... can you give it a try?

- Ardhendu
'Cell D3 value is "Y"
'Cell E3 value is "TestMacro"

Sub test1()
    If Range("D3") = "Y" Then
        Application.Run Range("E3") & "()"

    Else:
    End If
End Sub

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Top Expert 2010

Commented:
srejja said:
>>The error was on:
>>Application.Run ThisWorkbook.Sheets("Template").Range("E" & i)

And what were the contents of that cell at the time of failure?

Author

Commented:
The cell value was TestMacro"


Ardhendu SarangiSr. Project Manager

Commented:
Did u try the code I pasted?

- Ardhendu

Author

Commented:
Could the problem be related to the fact that the macro being called from "ThisWorkBook" is contained within the "ActiveWorkBook"?



Author

Commented:
Pari123 - I did try your code, but how would you write it if your trying to call a sub contained in a different workbook?  Basically call in a sub in the ActiveWorkBook from ThisWorkBook.
Ardhendu SarangiSr. Project Manager

Commented:
Hi,
Assuming the macro to be called resides in Book1, I tried running this code from Book2.xls...

Try this pls.

Ardhendu


Sub test1()
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    If Range("D3") = "Y" Then
        Application.Run "Book1.xls!" & Range("E3")
    Else:
    End If
    Application.EnableEvents = True
End Sub

Open in new window

Top Expert 2008
Commented:
There are two basic techniques for calling code in other workbooks: referenced and unreferenced.

Referencing an external workbook means adding a reference to that workbook to the local workbook's VBA project. When a workbook is referenced, all public variables and routines in all general code modules are accessible as if they were in the same workbook. Even classes are usable although they cannot be instantiated from outside the other workbook (the workaround is to create a public function in the external workbook that instantiates the class object and passes it back to the caller.) While accessing the routines and classes of a referenced workbook is easier and more intuitive then when the workbook is not referenced, the one drawback is that most of the local code will cease to function if the referenced workbook is not available, whether or not that code depends on the external workbook.

To add a reference to an external workbook, swicth to the VBE and choose the menu command Tools->References. If the external workbook is a pre-loaded Add-In then it will appear in the list - find it and check the corresponding box. If the external workbook is not an Add-In, click "Browse", change the extension to "Microsoft Office Excle files (*.xls;*.xls)", and locate and open the external workbook.

Without referencing the external workbook it is still possible to call a routine located in a general module, a worksheet module, and the ThisWorkbook module in another workbook. The sample code below illustrates how to do this with each possible location and how to call a sub and a function. One feature of the Run method is that it can call private routines.

   Dim OtherWorkbook As Object
   Set OtherWorkbook = Application.Workbooks("Other Workbook Name.xls")

Call a sub:

   Application.Run "'" & Replace(OtherWorkbook.Name, "'", "''") & "'!MacroName", Parm1, Parm2
   Application.Run "'" & Replace(OtherWorkbook.Name, "'", "''") & "'!ThisWorkbook.MacroName", Parm1, Parm2
   OtherWorkbook.Sheets("Sheet1").MacroName Parm1, Parm2

Call a function:

   Result = Application.Run("'" & Replace(OtherWorkbook.Name, "'", "''") & "'!MacroName", Parm1, Parm2)
   Result = Application.Run("'" & OtherWorkbook.NameReplace(OtherWorkbook.Name, "'", "''") & "'!ThisWorkbook.MacroName", Parm1, Parm2)
   Result = OtherWorkbook.Sheets("Sheet1").MacroName(Parm1, Parm2)

If calling from another application such as Word or Access then replace "Application" with the object variable containing a reference to the Excel application that has the target workbook open.

Note that the workbook name must have all single quotes changed to two single quotes because single quotes are used to delineate the workbook name from the rest of the reference.

Kevin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial