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.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1778: Add Variables To Record A Macro.
Alright. Hey. Today's question is sent in by Matt.
Matt is coming up to speed getting really good with a macro recorder but now he needs to do a few things that the macro recorder can't do, and so we have this report, we get this report every day, and there's a different number of records, and Matt can deal with that.
He's gotten really good at dealing with the different number of records but we want to be able to do other things, like add a summary table below the data, and to make that easier, it'd be good to have a couple of variables that we're going to add to the macro recorder.
So, here on the DEVELOPER tab, I'm going to say that I want to RECORD A MACRO.
I'll just call it MATTTAKE1 and we'll store it in THIS WORKBOOK.
Click OK.
Alright.
Now, relative reference should always be on.
I turned that on before I started recording.
99% of your macros should have relative reference on.
I'm going to build that summary table.
The summary table wants to take all of the REGIONs in column A. So, from here, I'm going to do CONTROL+SHIFT+DOWNARROW, I’m going to CONTROL+C to copy that to the clipboard, CONTROL+DOWNARROW, and then I'll go down 1, 2, 3, yeah, 3 rows, right 2 rows, and CONTROL+V to paste the whole region down there, and on the DATA tab, I will do REMOVE DUPLICATES.
Click OK.
That way I know I have just the unique list of REGIONs down there, alright, and then we want to build a report showing REVENUE.
Alright.
So, here, =SUMIF.
I know that I’m going to have to fix this in the macro but let's see if we can get close, at least.
So, we start here in A2, CONTROL+SHIFT+DOWNARROW, F4, is = to, press the LEFT ARROW key, , and then up here, D2, CONTROL+SHIFT+DOWNARROW, press F4, and I want to CONTROL+ENTER to stay in the same cell.
Double-click the fill handle to shoot that down.
[ =SUMIF($A$2:$A$17,C21,$D$2:$D$17) ] Now, here, there's only 1 region but tomorrow there might be 2 regions or something like that.
Alright.
So, the first step is done.
I have that summary table built with the macro recorder.
I now need to go up and add totals, and to do that, I always want to go to cell A1.
I don't want to go up 21 cells from where I am now because, tomorrow, I might be at a different spot.
So, I'm going to use the F5 key.
This is a great way in a macro to say I always want to go to a specific spot, click OK, and we’ll do CONTROL+DOWNARROW.
That'll get me to the last row with data.
One more time will get me to the total row, TOTAL, and come over here 2 cells.
=SUM.
Never use the AUTOSUM here, by the way.
The macro recorder will not record what you're trying to do.
We're trying to go from C $ sign 2.
Would you ever type that $ sign in in real life?
No, but we're going to do it with a macro recorder because that will convince the macro recorder to do the right thing.
Down to C17, the cell right above me, and that is a relative reference, and CONTROL+C and CONTROL+V to the right.
Okay.
Good.
Let's stop recording, and now we're going to go take a look at the code.
[ =SUM(C$2:C17) ] Okay.
So, we press ALT+F11.
Here is our workbook.
There's our module.
Double-click and there is the recorded code.
Alright.
So, now, we're going to add in some variables to the recorded code, and one variable that I create in every single macro is something called FINALROW.
FINALROW is = to CELLS ROWS.COUNT, so in other words go down to the very last row, , 1.
That's column A. Press the END key and the XLUP key and figure out what row that is.
That tells us the last row with data today.
My TOTALROW is going to be = to FINALROW + 1.
The SUMMARYROW is going to be = to FINALROW + 3, and then the ROWCOUNT, because we have headings in row 1, is going to be = to FINALROW – 1, alright?
So, now I have those 4 variables that I can use in the macro, and as I look through the macro, the first few lines here worked great.
It selected everything that we have in column A, it went down 3 rows and 2 columns over, it did a paste, but then it hard-coded that we had data in rows C20 to C36, and that will not work.
We have to fix that piece.
So, here's what we're going to do.
We're going to say that we are in CELLS SUMMARYROW , column 3 .RESIZE.
RESIZE will take that starting cell and it's actually going to be FINALROW is the height , 1.
So, rather than doing ACTIVESHEET.RANGE and hard-coding that, we're going to use this that has 2 different variables, and we will use the rest of the recorded code for the second half of that line.
Now, this went up and put the revenue in.
That should work.
Next, we want to put the word REVENUE in so let's change that.
We’ll say that that is in CELLS SUMMARYROW-1 , 4.
We can get rid of this line.
Alright.
Then the next thing that's happening is we're putting in the SUMIF formula, and as I look at this, it's going from row 2 column 1 to row 17 column 1.
They hard-coded that piece of that and that's not good at all.
So, I'm going to create MYFORMULA is = to I just put “” there so that way I'm allowed to go to the next line and scoop this out here.
I'm going to take everything between the “, CONTROL+C, and bring this back, alright, and then I'm going to build this myself.
So, up to where it says row 17, I'm going to concatenate FINALROW & “, go back to what they had, column 1, RC -1, column 4, that's good, but then, here, where we have 17 & FINALROW & “, get rid of the 17, column 4, and so now that builds an R1 C1 formula that will be flexible to always go down to however many rows we happen to have that day.
R1 C 1 is difficult to understand.
I'm not going to cover that in this macro, but even without understanding it, just knowing that they had hard-coded row 17 before, now we have concatenated together a formula that’ll work.
The trick now is to get that formula into the right spot.
So, we already know that we put the heading.
Let's see where.
We put the heading here.
SUMMARYROW -1 in column 4.
So, I know it's going to be SUMMARYROW, not – 1 but starting in SUMMARYROW , 4, and we're going to do .RESIZE.
If I had more time, I would actually put in a variable to figure out how many rows we had today.
I'm just going to make it be 3 rows tall, 1 column wide, .FORMULAR1C1 is = to MYFORMULA.
It's too bad we don't have picture in picture anymore because you can't see where my eyes are looking.
As I was typing that, I was looking on here, the FORMULAR1C1, so I was copying that property but then using the formula that I built above.
So, we can now actually get rid of these parts here and also this section goes away, alright?
So, we put the revenue heading in, we build the formula, and then put the formula in the right spot.
From there on out, everything that we recorded should be fine.
Here's where we go to A1, which is R1C1 in the language of the macro, do SELECTION END DOWN, put the TOTAL in, and I think, at this point, we're good to go, except for the fact that we're going to get a summary table that's always 3 regions high, and we should come back later and fix that, but I'm running short on time.
So, let's go back all.
ALT+F11.
Okay.
So, we'll come back here to a different day, start up in cell A1, and we will run the macro.
ALT+F8, MATTTAKE1, click RUN, and, hey, it's remarkably close.
We're putting this column, column 4, everything is going to the wrong spot.
So, let me erase what we've done here, come back and take a look at our code.
ALT+F11.
So, down here in SUMMARYROW, , 4, SUMMARYROW, the column 4, everything there needs to be adjusted down one, and here +1, those 2 changes.
Alright.
Let's go back and try it again.
ALT+F8, MATTTAKE1, Click RUN.
Totals appear on the right spot.
Always make sure they're going all the way up to D2, and we have the REVENUE table appearing with a TOTAL that matches.
Everything looks good, alright?
So, it's tough to go from the macro recorder all of a sudden having to create these variables, but these variables will help you find the spots that when the macro recorder did something stupid, like hard-code where that formula is even though we had relative turned on, you can go in and patch those bits of the macro using these variables here, and it turns out that we never actually use total row in the macro but that's okay.
I'd rather just have it there, and then if I needed it, we know that we're good to go.
Well, hey.
I want to thank everyone for stopping by.
I want to thank Matt for asking that question.
Matt, good luck with the…as you begin going from the macro recorder over to VBA.
Hey.
Of course this is a great time to remind you about the mrexcel.com/forum.
Boy, what an amazing community of people there.
They answer 30,000 questions a year.
10,000 of those questions are VBA questions.
So, pop out there.
Start a new topic.
Say, hey, I'm trying to learn VBA, here’s my code, can you help me out, and they will be happy to give you some pointers.
I want to thank for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1778: Add Variables To Record A Macro.
Alright. Hey. Today's question is sent in by Matt.
Matt is coming up to speed getting really good with a macro recorder but now he needs to do a few things that the macro recorder can't do, and so we have this report, we get this report every day, and there's a different number of records, and Matt can deal with that.
He's gotten really good at dealing with the different number of records but we want to be able to do other things, like add a summary table below the data, and to make that easier, it'd be good to have a couple of variables that we're going to add to the macro recorder.
So, here on the DEVELOPER tab, I'm going to say that I want to RECORD A MACRO.
I'll just call it MATTTAKE1 and we'll store it in THIS WORKBOOK.
Click OK.
Alright.
Now, relative reference should always be on.
I turned that on before I started recording.
99% of your macros should have relative reference on.
I'm going to build that summary table.
The summary table wants to take all of the REGIONs in column A. So, from here, I'm going to do CONTROL+SHIFT+DOWNARROW, I’m going to CONTROL+C to copy that to the clipboard, CONTROL+DOWNARROW, and then I'll go down 1, 2, 3, yeah, 3 rows, right 2 rows, and CONTROL+V to paste the whole region down there, and on the DATA tab, I will do REMOVE DUPLICATES.
Click OK.
That way I know I have just the unique list of REGIONs down there, alright, and then we want to build a report showing REVENUE.
Alright.
So, here, =SUMIF.
I know that I’m going to have to fix this in the macro but let's see if we can get close, at least.
So, we start here in A2, CONTROL+SHIFT+DOWNARROW, F4, is = to, press the LEFT ARROW key, , and then up here, D2, CONTROL+SHIFT+DOWNARROW, press F4, and I want to CONTROL+ENTER to stay in the same cell.
Double-click the fill handle to shoot that down.
[ =SUMIF($A$2:$A$17,C21,$D$2:$D$17) ] Now, here, there's only 1 region but tomorrow there might be 2 regions or something like that.
Alright.
So, the first step is done.
I have that summary table built with the macro recorder.
I now need to go up and add totals, and to do that, I always want to go to cell A1.
I don't want to go up 21 cells from where I am now because, tomorrow, I might be at a different spot.
So, I'm going to use the F5 key.
This is a great way in a macro to say I always want to go to a specific spot, click OK, and we’ll do CONTROL+DOWNARROW.
That'll get me to the last row with data.
One more time will get me to the total row, TOTAL, and come over here 2 cells.
=SUM.
Never use the AUTOSUM here, by the way.
The macro recorder will not record what you're trying to do.
We're trying to go from C $ sign 2.
Would you ever type that $ sign in in real life?
No, but we're going to do it with a macro recorder because that will convince the macro recorder to do the right thing.
Down to C17, the cell right above me, and that is a relative reference, and CONTROL+C and CONTROL+V to the right.
Okay.
Good.
Let's stop recording, and now we're going to go take a look at the code.
[ =SUM(C$2:C17) ] Okay.
So, we press ALT+F11.
Here is our workbook.
There's our module.
Double-click and there is the recorded code.
Alright.
So, now, we're going to add in some variables to the recorded code, and one variable that I create in every single macro is something called FINALROW.
FINALROW is = to CELLS ROWS.COUNT, so in other words go down to the very last row, , 1.
That's column A. Press the END key and the XLUP key and figure out what row that is.
That tells us the last row with data today.
My TOTALROW is going to be = to FINALROW + 1.
The SUMMARYROW is going to be = to FINALROW + 3, and then the ROWCOUNT, because we have headings in row 1, is going to be = to FINALROW – 1, alright?
So, now I have those 4 variables that I can use in the macro, and as I look through the macro, the first few lines here worked great.
It selected everything that we have in column A, it went down 3 rows and 2 columns over, it did a paste, but then it hard-coded that we had data in rows C20 to C36, and that will not work.
We have to fix that piece.
So, here's what we're going to do.
We're going to say that we are in CELLS SUMMARYROW , column 3 .RESIZE.
RESIZE will take that starting cell and it's actually going to be FINALROW is the height , 1.
So, rather than doing ACTIVESHEET.RANGE and hard-coding that, we're going to use this that has 2 different variables, and we will use the rest of the recorded code for the second half of that line.
Now, this went up and put the revenue in.
That should work.
Next, we want to put the word REVENUE in so let's change that.
We’ll say that that is in CELLS SUMMARYROW-1 , 4.
We can get rid of this line.
Alright.
Then the next thing that's happening is we're putting in the SUMIF formula, and as I look at this, it's going from row 2 column 1 to row 17 column 1.
They hard-coded that piece of that and that's not good at all.
So, I'm going to create MYFORMULA is = to I just put “” there so that way I'm allowed to go to the next line and scoop this out here.
I'm going to take everything between the “, CONTROL+C, and bring this back, alright, and then I'm going to build this myself.
So, up to where it says row 17, I'm going to concatenate FINALROW & “, go back to what they had, column 1, RC -1, column 4, that's good, but then, here, where we have 17 & FINALROW & “, get rid of the 17, column 4, and so now that builds an R1 C1 formula that will be flexible to always go down to however many rows we happen to have that day.
R1 C 1 is difficult to understand.
I'm not going to cover that in this macro, but even without understanding it, just knowing that they had hard-coded row 17 before, now we have concatenated together a formula that’ll work.
The trick now is to get that formula into the right spot.
So, we already know that we put the heading.
Let's see where.
We put the heading here.
SUMMARYROW -1 in column 4.
So, I know it's going to be SUMMARYROW, not – 1 but starting in SUMMARYROW , 4, and we're going to do .RESIZE.
If I had more time, I would actually put in a variable to figure out how many rows we had today.
I'm just going to make it be 3 rows tall, 1 column wide, .FORMULAR1C1 is = to MYFORMULA.
It's too bad we don't have picture in picture anymore because you can't see where my eyes are looking.
As I was typing that, I was looking on here, the FORMULAR1C1, so I was copying that property but then using the formula that I built above.
So, we can now actually get rid of these parts here and also this section goes away, alright?
So, we put the revenue heading in, we build the formula, and then put the formula in the right spot.
From there on out, everything that we recorded should be fine.
Here's where we go to A1, which is R1C1 in the language of the macro, do SELECTION END DOWN, put the TOTAL in, and I think, at this point, we're good to go, except for the fact that we're going to get a summary table that's always 3 regions high, and we should come back later and fix that, but I'm running short on time.
So, let's go back all.
ALT+F11.
Okay.
So, we'll come back here to a different day, start up in cell A1, and we will run the macro.
ALT+F8, MATTTAKE1, click RUN, and, hey, it's remarkably close.
We're putting this column, column 4, everything is going to the wrong spot.
So, let me erase what we've done here, come back and take a look at our code.
ALT+F11.
So, down here in SUMMARYROW, , 4, SUMMARYROW, the column 4, everything there needs to be adjusted down one, and here +1, those 2 changes.
Alright.
Let's go back and try it again.
ALT+F8, MATTTAKE1, Click RUN.
Totals appear on the right spot.
Always make sure they're going all the way up to D2, and we have the REVENUE table appearing with a TOTAL that matches.
Everything looks good, alright?
So, it's tough to go from the macro recorder all of a sudden having to create these variables, but these variables will help you find the spots that when the macro recorder did something stupid, like hard-code where that formula is even though we had relative turned on, you can go in and patch those bits of the macro using these variables here, and it turns out that we never actually use total row in the macro but that's okay.
I'd rather just have it there, and then if I needed it, we know that we're good to go.
Well, hey.
I want to thank everyone for stopping by.
I want to thank Matt for asking that question.
Matt, good luck with the…as you begin going from the macro recorder over to VBA.
Hey.
Of course this is a great time to remind you about the mrexcel.com/forum.
Boy, what an amazing community of people there.
They answer 30,000 questions a year.
10,000 of those questions are VBA questions.
So, pop out there.
Start a new topic.
Say, hey, I'm trying to learn VBA, here’s my code, can you help me out, and they will be happy to give you some pointers.
I want to thank for stopping by.
We'll see you next time for another netcast from MrExcel.
