Excel - Add Variables to Fix Recorded Excel Macro - Episode 1778

1,845 views · Published 21 August 2013 · 9:39 · Indexed 31 October 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Add Variables to a Recorded Excel Macro | MrExcel Podcast.

Welcome to another episode of the MrExcel podcast. In today's episode, we will be discussing how to add variables to a recorded Excel macro. This question was sent in by Matt, who has become proficient in using the macro recorder but now needs to do some tasks that the recorder cannot handle. Specifically, he needs to add a summary table below the data, and to make this process easier, he wants to add some variables to the macro recorder.

To begin, we will open the DEVELOPER tab and click on "Record Macro". We will name the macro "MATTTAKE1" and store it in the current workbook. It is important to have relative reference turned on before recording the macro, as this will make it easier to add variables later on. We will then proceed to build the summary table by selecting all the regions in column A, copying them to the clipboard, and pasting them below the data. We will then remove duplicates to ensure we have a unique list of regions. Next, we will use the SUMIF function to calculate the revenue for each region.

Now, we need to add variables to the recorded code. The first variable we will create is called "FINALROW" and it will determine the last row with data in the worksheet. We will also create variables for the total row, summary row, and row count. These variables will make it easier to adjust the macro for different data sets. We will then go through the recorded code and replace any hard-coded values with these variables. This will ensure that the macro works for different data sets and not just the one that was recorded.

After making these adjustments, we can run the macro and see that it is now more flexible and can handle different data sets. However, we may notice that the summary table is always three regions high. This can be fixed by going back to the code and making some adjustments, but for the sake of time, we will leave it as is. It is important to remember that the mrexcel.com/forum is a great resource for learning VBA and getting help with any issues you may encounter. Thank you for watching this episode of the MrExcel podcast, and we will see you next time for another informative netcast.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Adding Variables to Recorded Macro
(00:11) Need for Variables in Macro Recorder
(00:21) Building a Summary Table with a Macro
(00:38) Adding Variables to the Recorded Code
(03:06) Fixing Hard-Coded Parts of the Macro
(06:26) Putting the Formula in the Right Spot
(07:44) Running the Macro and Making Adjustments
(09:00) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Add Variables To Record A Macro
Learn Excel From MrExcel
Macro recorder
Podcast Episode 1778
Relative reference in macros
Remove duplicates in Excel
SUMIF formula
Summary table
Variables in macros
VBA programming


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153599/


Matt asks about making VBA code created with the macro recorder more reliable. In today's long episode, a discussion about adding variables to your recorded code and then using those variables to replace the hard-coded bits in the recorded code.

More from this channel