Sam wants to Stop Calculating some cells in a worksheet but keep the formula there. Other cells need to keep calculating. Paste Values would cause the formula to be lost. Although that functionality is not built in, a VBA macro simulates what Sam is looking for.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1970 - Stop Calculating Some Cells!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question sent in by Sam, and this is one that I've never seen before, Sam has a web query that's pulling in some data in B through G, and he has formulas out here to the left of the web query, that are grabbing values.
Sam wants this formula and A2 to stop calculating, but the others to keep calculating.
Alright, I said: "Well Sam, that's easy, you're going to copy, right click, and paste as Values." He's like: "No no no, I don't want to get rid of the formula, I need to keep the formula there, I just want that cell to stop calculating.
Can I just put =STOPCHANGE(A2) somewhere?" Well no, that's not built-in, and, you know, if we went to Calculation Options and change to Manual, well that's going to stop everything from calculating, not just one or two or three cells.
So there's nothing built into Excel that's going to do this, and so, we're going to have to turn to a macro to simulate this.
I'm assuming that you've never used macros before, so, we're going to do some really important steps first, don't skip these steps or it's not going to work!
Alt+T, as in Tom, M as in Mary, S as in Sam, to get to the macro settings, by default they've disabled all macros, they don't tell you they've disabled macros, you have to go down to the second one, so that way your file will open and you're able to enable the macros.
Click OK.
Secondly, I'm guessing that Sam is using the XLSX file format, because that's the default, the IT managers of the world, forced Microsoft to make this broken file format be the default, this is the only file format does not allow macros, right?
So, your macros will work the first day, but you save the file, and macros will be deleted.
That is completely pointless, you have to do File, Save As, and convert this file to anything else, XLSM, XLSB, heck even XLS!
I'll just go to XLSM, and click Save.
If you don't do that, all the work that we do for the rest of the video is going to be lost.
Alright, we need to switch over to VBA, so that's Alt+F11, get a nice blank screen here, we'll insert a module, and then type this code, alright, here's what I've done: I've created two macros, the first macro is called "Stop Calculating", and the way that I envision this, is Sam is going to choose the cells that he wants to stop calculating, where to loop for all the cells in the selection.
See IF the left of that cell.Formula starts with a = , in other words, do we have a formula here?
If we have a formula, then we're going to build a new formula that's equal, whatever the cell value is, and then the N function, it's likely that you've never used the N function, so let's just talk about the N function briefly, I'm going to just put some numbers in, 17, a word, a date, and FALSE.
Alright, here's how N works: This is leftover from Lotus, if you take the N of a number, you get the number back.
Cool right?
So N of 1 is 1, N of 17 - 17, and N a date, it's the serial number, N a TRUE or FALSE, it's a 0 or 1, but N of a word is always 0.
Alright?
Any text.
So if you have a formula that says: =193+N("Any Text Here") , the N of any text here is going to be 0, we're adding 0 into this number, we're going to get the original number.
Alright, but what I'm proposing, is instead of "Any Text Here", we store the original formula inside the N function.
Alright, so let's go look at the VBA again, ALT+F11, so I take the cell.Value+N( , those three quotes put a single quote in, & cell.Formula & again, that's starting the quote, and then two quotes puts the quote in, ) , and then close parentheses ("). Write that new formula back to the cell and then I change the interior color to pink, so I know which cells are stopped.
Sam may not want to do that, you might just leave that whole line out.
To resume calculating that'll be a second macro, For Each cell in Selection, save the OldFormula, use the InStr(ing) function to start at position 1, looking through the OldFormula for that "+N(" , wherever that's found.
Alright, so if it's if it's there, we're going to start at that position +4, we're going to figure out how long the old formula was, grab everything from the EndAt - StartAt +1 , and then, the new formula is MID(OldFormula, StartAt, LengthToGet) . Then write it back, cell.Formula = NewFormula, cell.Interior.Color = ' no fill, which is that number there.
Finally close the End If, Next cell.
Alright, so now we have these two functions, we'll do File, Close and Return to Microsoft Excel, we want a way to run those functions, I'll come up here to the Quick Access Toolbar, right click, Customize, from the left drop-down we're going to choose macros, from the top-right drop-down we'll say "Only for this workbook", and then we'll put StopCalculating, and then ResumeCalculating.
Let's choose a nice little icon.
For StopCalculating maybe we'll use the red X, and for ResumeCalculating, so it's going back, let's use the back arrow.
Click OK. you can choose anything you want there, I wish we still have the 4,000 items we had back in Excel 2003, but we're stuck with those few little items, click OK.
Alright so here's how it's gonna work, let me take these two cells and stop them from calculating, so I choose those cells, see right now the, formula is =E6, choose the first macro, StopCalculating.
Alright, and it remembered it was 187 + , there is the formula.
If I Calculate Now Calculate Now Calculate Now, see, these numbers are changing, but these are not, this number though, because it is not stopped calcuating, it continues to update, and when I'm done I just click ResumeCalculating, and it brings back the original formula.
So kind of an unusual question.
I've never had anyone in 30 years of using spreadsheets, where they wanted to stop calculating just some cells.
But for whatever reason, that's what Sam needs to do, a little bit of VBA will solve that problem.
Hey, by the way, if you are completely new to VBA, and you want to learn how to do this script, and many more scripts like this, check out my book.
Tracy and I wrote "Excel 2016 VBA and MACROS"!
There's a card in the top-right hand corner of this video.
Click that to jump to my website.
We'll take you up the learning curve, you know Excel well, but you need to learn how to do things in VBA, this will walk you through that journey.
Hey, I want to thank Sam for setting that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question sent in by Sam, and this is one that I've never seen before, Sam has a web query that's pulling in some data in B through G, and he has formulas out here to the left of the web query, that are grabbing values.
Sam wants this formula and A2 to stop calculating, but the others to keep calculating.
Alright, I said: "Well Sam, that's easy, you're going to copy, right click, and paste as Values." He's like: "No no no, I don't want to get rid of the formula, I need to keep the formula there, I just want that cell to stop calculating.
Can I just put =STOPCHANGE(A2) somewhere?" Well no, that's not built-in, and, you know, if we went to Calculation Options and change to Manual, well that's going to stop everything from calculating, not just one or two or three cells.
So there's nothing built into Excel that's going to do this, and so, we're going to have to turn to a macro to simulate this.
I'm assuming that you've never used macros before, so, we're going to do some really important steps first, don't skip these steps or it's not going to work!
Alt+T, as in Tom, M as in Mary, S as in Sam, to get to the macro settings, by default they've disabled all macros, they don't tell you they've disabled macros, you have to go down to the second one, so that way your file will open and you're able to enable the macros.
Click OK.
Secondly, I'm guessing that Sam is using the XLSX file format, because that's the default, the IT managers of the world, forced Microsoft to make this broken file format be the default, this is the only file format does not allow macros, right?
So, your macros will work the first day, but you save the file, and macros will be deleted.
That is completely pointless, you have to do File, Save As, and convert this file to anything else, XLSM, XLSB, heck even XLS!
I'll just go to XLSM, and click Save.
If you don't do that, all the work that we do for the rest of the video is going to be lost.
Alright, we need to switch over to VBA, so that's Alt+F11, get a nice blank screen here, we'll insert a module, and then type this code, alright, here's what I've done: I've created two macros, the first macro is called "Stop Calculating", and the way that I envision this, is Sam is going to choose the cells that he wants to stop calculating, where to loop for all the cells in the selection.
See IF the left of that cell.Formula starts with a = , in other words, do we have a formula here?
If we have a formula, then we're going to build a new formula that's equal, whatever the cell value is, and then the N function, it's likely that you've never used the N function, so let's just talk about the N function briefly, I'm going to just put some numbers in, 17, a word, a date, and FALSE.
Alright, here's how N works: This is leftover from Lotus, if you take the N of a number, you get the number back.
Cool right?
So N of 1 is 1, N of 17 - 17, and N a date, it's the serial number, N a TRUE or FALSE, it's a 0 or 1, but N of a word is always 0.
Alright?
Any text.
So if you have a formula that says: =193+N("Any Text Here") , the N of any text here is going to be 0, we're adding 0 into this number, we're going to get the original number.
Alright, but what I'm proposing, is instead of "Any Text Here", we store the original formula inside the N function.
Alright, so let's go look at the VBA again, ALT+F11, so I take the cell.Value+N( , those three quotes put a single quote in, & cell.Formula & again, that's starting the quote, and then two quotes puts the quote in, ) , and then close parentheses ("). Write that new formula back to the cell and then I change the interior color to pink, so I know which cells are stopped.
Sam may not want to do that, you might just leave that whole line out.
To resume calculating that'll be a second macro, For Each cell in Selection, save the OldFormula, use the InStr(ing) function to start at position 1, looking through the OldFormula for that "+N(" , wherever that's found.
Alright, so if it's if it's there, we're going to start at that position +4, we're going to figure out how long the old formula was, grab everything from the EndAt - StartAt +1 , and then, the new formula is MID(OldFormula, StartAt, LengthToGet) . Then write it back, cell.Formula = NewFormula, cell.Interior.Color = ' no fill, which is that number there.
Finally close the End If, Next cell.
Alright, so now we have these two functions, we'll do File, Close and Return to Microsoft Excel, we want a way to run those functions, I'll come up here to the Quick Access Toolbar, right click, Customize, from the left drop-down we're going to choose macros, from the top-right drop-down we'll say "Only for this workbook", and then we'll put StopCalculating, and then ResumeCalculating.
Let's choose a nice little icon.
For StopCalculating maybe we'll use the red X, and for ResumeCalculating, so it's going back, let's use the back arrow.
Click OK. you can choose anything you want there, I wish we still have the 4,000 items we had back in Excel 2003, but we're stuck with those few little items, click OK.
Alright so here's how it's gonna work, let me take these two cells and stop them from calculating, so I choose those cells, see right now the, formula is =E6, choose the first macro, StopCalculating.
Alright, and it remembered it was 187 + , there is the formula.
If I Calculate Now Calculate Now Calculate Now, see, these numbers are changing, but these are not, this number though, because it is not stopped calcuating, it continues to update, and when I'm done I just click ResumeCalculating, and it brings back the original formula.
So kind of an unusual question.
I've never had anyone in 30 years of using spreadsheets, where they wanted to stop calculating just some cells.
But for whatever reason, that's what Sam needs to do, a little bit of VBA will solve that problem.
Hey, by the way, if you are completely new to VBA, and you want to learn how to do this script, and many more scripts like this, check out my book.
Tracy and I wrote "Excel 2016 VBA and MACROS"!
There's a card in the top-right hand corner of this video.
Click that to jump to my website.
We'll take you up the learning curve, you know Excel well, but you need to learn how to do things in VBA, this will walk you through that journey.
Hey, I want to thank Sam for setting that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
