Executing Macro from External File Using Excel VBA

Executing Macro from External File Using Excel VBA

Macro

Script or code which is embedded within Office Application to automate the tasks like Copy/Paste contents, Data Insertion, Formula Manipulation etc. Sometimes we need to execute bulk operations and may require to execute some code which is written as part of another Excel file. Developer left with one choice by bringing that code in current working environment which is just a copy/paste job, But the real problem is to sync the environment. May be the code which is kept in another file, integrates some additional interfaces which are imported from external libraries, or it has code which is written to be execute in a specific Operating System.

Application

Excel VBA has a very powerful and root level object called Application. This object offers very rich properties and methods which directly interacts with the application. Application object offers RUN method.

Syntax

Application.Run(Macro,Arg1,Arg2,Arg3………So On)

Lets have an example and break it down in steps to understand. In this example I have created a File named as SayHello.xlsm which has one method named as HelloTest. This method contains a simple Message Box saying Hello World. Now I saved the file as SayHello.xlsm and close it. Now created another file to run HelloTest method which is kept in SayHello.xlsm file and put following code in VBE module:

Code example

Public Sub RunMacroFromFile()
    Application.Run "'C:\Users\Nawazish\Downloads\SayHello.xlsm'!Sheet1.HelloTest"
End Sub
  1. File name and path must follow single quote starting and ending
  2. Sheet name must be started with ! sign
  3. Actual method would be associated with sheet name hence . will make the call

Video example

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Next>>Export CSV File to XML using Excel VBA

Leave a Reply

Your email address will not be published.