If there is a spreadsheet with many macros within a spreadsheet, irrespective of whether they are recorded macros or written in the VBA editor there may be a need to combine the macros together. Is this possible?
Combining two macros is a relatively easy process. It can be done in 3 different ways depending upon the nature of the macros that you are creating and your level of experience.
Combining Recorded Macros (No VBA Required)
If you’re somebody that is not confident doing anything in VBA macros can still be combined using your recorded method even if the macros have been completed at completely separate times.
The easiest way to do this is to record a new macro where the other macros are triggered that have previously been recorded. This will result in there being a single macro which contains the original macros.
This method will work irrespective of how the macros are triggered for example if you have hotkeys the triggering on macros they work or if you have buttons they will also work.
Combining Macros Using ‘Call’ Method
The second method requires the VBA editor to be opened in order for there to be code written. If you are unfamiliar with the VBA editor it can be accessed by selecting ALT + F8 and a pop-up of all the macros contained within the file will appear.
To go into the VBA itself you need to select the name of the macro and click the “step into” button.
To create a macro with code that calls multiple macros is very straightforward. To code to do this is provided below.
Sub Combine_Two_Macros()
Call Name_Of_Macro_1
Call Name_Of_Macro_2
End Sub
What Each Part Of The Code Does
Sub Combine_Two_Macros()
Any macro in VBA start with a line containing the word sub which is short for subroutine and the name of the macro which needs to be one continuous line of characters. There can be no spaces. The name selected, in this example is Combine_Two_Macros, will appear in the list of macros when you hit ALT + F8.
Call Name_Of_Macro_1
Call is the command that is required by VBA to trigger a macro to run. To successfully trigger a macro the name used must be exact. For example to trigger the combined two macros subroutine the line of code would be;
Call Combine_Two_Macros
End Sub
Is just the last line of the code which closes out subroutine and disappears on every single macro in the VBA editor whether it is recorded or not
Where To Place The Code
If this is the first time you are using the VBA editor the code can be placed anywhere in one of the modules. You can just paste the code above at the bottom of one of the modules or alternatively create a brand new module. To do this click insert and select module.
Combining The Macros By Transferring The Code
The other alternative that you can do if you want to reduce the number of macros in your file is to copy and paste the macros under the same subroutine which is fairly easy to do.
The main consideration and doing this is ensuring that the macro’s code is pasted just above the final line of the macro which will be End Sub. The only other thing you need to be aware of is you need to make sure that you do not copy the first line of your macro and the last layer of the macro which contain the name of the macro at the start (ie Sub ………) and the End Sub, because that will cause an error.
Relevant Articles
Is VBA Worth Learning? Does It Take Long?