Running

You can run macros that are either in the current workbook or in any other open workbook.
There are a number of different ways you can run a macro:
Developer Tab, Macros dialog box
Select the appropriate macro name and press Run
Alt + F8
This dialog box lists all the macros in the current project. Any recorded macros will appear in this list.
Any procedures that are declared as "Private" (instead of "Public") will not appear in this list.
If any names are not unique then the macro can be qualified by the name of the module in which the macro appears.



 


Run - This will run the macro that is currently selected.
Cancel - This will close the dialog box
Step Into - This will step into the macro that is currently selected and will take you into Debug mode. The first line of the macro will be highlighted in yellow (or whatever indicator has been set for the Current Execution Line indicator).
Edit - This opens the Code Window and displays the macro currently selected.
Create - This button lets you to create the first and last line of a new subroutine. This button is enabled when you start to type into the Macro name box. This will open the Visual Basic Editor.
Delete - This will permanently remove the macro from the Code Window. This will remove the macro from its corresponding code module. This will not remove any empty code modules from your project.
Options - This allows you to assign shortcut keys to the macro currently selected.
Note that if the Visual Basic project is password protected then most of these buttons are disabled.


You could run your macro from the Macro dialog box. Select (Tools > Macro > Macros).
Select the name of your macro from the list and press the "Run" button.
Alternatively you can use the shortcut key (Alt + F8) to display the Macro dialog box.


(Tools > Macro > Macros) dialog box

The default is to display all the macros that are currently available from "All Open Workbooks".
It is possible to change the "Macros in" drop-down box to only display macros from a specific workbook.
The macros that appear in this dialog box will be any that have been recorded as well as any that have been written.
Any macros that have the same name as those in the active workbook are prefixed by the name of the workbook.


(Tools > Macro > Macros) dialog box

You can prevent a macro from appearing in this list by declaring it as "Private" as opposed to Public. Public is the default for all recorded macros.

Private Sub Macro1() 
'
' Macro recorded #date# by Russell Proctor
'
End Sub



Quick Access Toolbar

Excel > Ribbon > Customize - Quick Access Toolbar page.



Ribbon

Using commands added to the Ribbon tabs.
Excel > Ribbon > Customize - Ribbon page.
You could run your macro by using an additional button on either a toolbar or a drop-down menu.
This is slightly more complicated and there are specific pages giving step by step instructions on how to do this following on from this page.
Any macros that are added to existing toolbars and menus will be accessible from any workbook.
These should be stored in your Personal Macro Workbook and not a specific workbook.


Assigning to a Graphic or AutoShape

You could run your macro by using a graphic or AutoShape.
When the AutoShape is clicked (with the left mouse button) the corresponding macro will run.
Add the shape or AutoShape you would like to use to the worksheet.
To insert an AutoShape you have to display the Drawing toolbar. This can be done by selecting (View > Toolbars > Drawing).
Select the graphic or AutoShape with the Right mouse button and select "Assign Macro".


Shortcut menu when you right mouse click on a graphic or AutoShape

This will display the Assign Macro dialog box which looks identical to the Macro dialog box.
Select the macro you want to assign and press OK.


Using a Hyperlink to run a Macro

Enter the following subroutine into a standard code module.

Public Sub FromHyperlink 
   Call Msgbox("Fired from a hyperlink")
End Sub


Select (Insert > Hyperlink) to display the Insert Hyperlink dialog box
On the left hand side there is a choice of the type of hyperlink to insert, Select "Place in this document".
SS
Enter the text to display the top in the "text to display" box
Click on the ScreenTip button and enter the name of the subroutine into this box. In our example our macro is called "FromHyperlink".
Enter the cell address of the cell address that contains the hyperlink in the "cell address" box. In our example we are using cell "B3".
Use the Worksheet_FollowHyperlink event


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 
Dim objRange As Range
Dim objHyperlink As Hyperlink

   If Target.Range.Address = "$B$3" Then
      Set objRange = Target.Range
      For Each objHyperlink In objRange.Hyperlinks

         Application.Run (objHyperlink.ScreenTip)
         Exit Sub
      Next objHyperlink
   End If
End Sub


Right mouse click and select "Edit Hyperlink"
SS - Edit Hyperlink dialog box


Visual Basic Editor

1) From Another Subroutine or Function

Call ModuleName.SubroutineName() 

2) Using Application.Run

You can use the Application.Run method to specify the filename and subroutine name of the macro you want to run

Application.Run "MyProject.MyModule.MyProcedure" 

It is also possible to call macros in other files by adding an explicit reference to the file.
For more details, please refer to the Application.Run page.

3) Adding a Reference to the File

You can add a reference to another file from within your VBA Project and you can then easily call procedures and functions from this file.
After you have established a reference you can call macros in that file as if they were in the same file as the calling procedure.
The referenced file does not have to be open since it is treated as a separate object library.
For more details about adding referencing, please refer to the References section.

4) Pressing F5 from the Visual Basic Editor

You can quickly run your procedures by pressing F5 from within the Visual Basic Editor

5) Using (Run > Run Sub) from the Visual Basic Editor

Select (Run > Run Sub) or (Run > Run UserForm).
The cursor must be located inside the procedure you want to run.

6) Using the Immediate Window

Just type the name of the procedure (including any arguments) and press Enter.
If this window is not visible you can use the shortcut key (Ctrl + G) to display it.
Immediate Window


You can use the Application.Run method to specify the filename and the subroutine name

Call Application.Run(Macro:="MyAddin.xlam'!MyModule.MyProcedure")  
Application.Run Macro:="MyAddin.xlam'!MyModule.MyProcedure"
Application.Run "MyAddin.xlam'!MyModule.MyProcedure"

It is also possible to call macros in other files by adding an explicit reference to the file.
For more information refer to VBA > Macros > Application.Run.


Important

Any macros that are available from any additional Excel add-ins are not displayed in the (Tools > Macro > Macros) dialog box.
You can interrupt a running macro by pressing the ESC key. This will display a dialog box allowing you to either Continue, End or Debug your macro. You can also use the key combination (Ctrl + Break) to interrupt a macro.
Macros cannot be undone. This is not technically right and is discussed in the advanced sections.
Selecting "All Open Workbooks" in the Macros in drop-down list will display all macros in any open workbooks including those in the Personal Macro Workbook.
The shortcut key and the description can only be displayed from the Options button on the (Tools > Macro > Macros) dialog box and not from within the Visual Basic Editor.
All shortcut keys are case sensitive.
It is possible to also use worksheet controls to run your macros. For more information on running macros using worksheet controls, please see the Worksheet Controls section.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext