Today, in Episode #1350, Bill examines an Excel VBA [Visual Basic for Applications] loop is supposed to step by 1% increments (0.01) - but it isn't working. Bill shows us the solution to get the result intended. Learn Excel 97-2007 from MrExcel.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1350: Step 0.01.
Well, hey welcome back to the MrExcel netcast.
I've got a great question today sent in by me.
That's right, I'm working on a brand new scenario manager better than the one that Excel has because I hate the Excel scenario manager and as part of that there was an input screen where someone could say hey I want to run all the scenarios from-- in this case 4% to 6% in 1% increments, something like that and there was the ability to put in 20 different variables if you wanted to change.
So you can create a lot of scenarios very, very quickly and I was stung by something in VBA; I'm going to switch over to VBA here.
Not to bore those of you who don't do macros but let's just talk about a simple “For Next” loop in VBA.
If you're going from 4 to 8 with a step of 2 what's going to happen is that loop is going to run once for 4, once for 6, once for 8 and then after the loop it's going to be at 10.
Here let me just show you; simple little loop here.
So we've got once for 4, once at 6, once at 8 then after the loop we are at 10, that's the way it works.
All right, so I was testing out the scenario manager and I said all right I want to run this one variable from 5% to 6% in 1% increments.
In other words, I wanted two scenarios, one at 5 and one at 6 and when I ran this macro it only ran the loop once.
I only got the 5% scenario not the 6% scenario and I thought that was really strange so I kind of stepped through the code here with F8.
First time through, i = 0.05, gets here to the next i and instead of going back up for the 6 it goes right down after the loop and it's now equal to 0.06 and what we're being stung by here is the floating-point problem.
Excel-- while computers store numbers as floating point which is binary and that doesn't convert well to tenths-- turns out that you know the 0.5 or 0.6 is a repeating number in binary and so it never comes out exactly equal; and I started to come up with some ideas where I would go not to 6 but to 0.06 then add a little bit, some tiny amount all the time.
Just very strange but I got a great tip from the MrExcel message board; there is actually a variable type that is called currency.
So if I added this one line; Dim i As Currency Currency is a very special type in Excel that has four decimal places so up to 0.0001 is the accuracy of it.
Watch when I run this now and that solves my problem.
I get the 5% and 6%.
So there you have it.
If you're doing loops in Excel and you're doing small steps, like 1% steps this “Dim i As Currency,” a very strange type will allow that loop to work.
Well, hey I want to thank you for stopping by.
We'll see you next time for another that netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1350: Step 0.01.
Well, hey welcome back to the MrExcel netcast.
I've got a great question today sent in by me.
That's right, I'm working on a brand new scenario manager better than the one that Excel has because I hate the Excel scenario manager and as part of that there was an input screen where someone could say hey I want to run all the scenarios from-- in this case 4% to 6% in 1% increments, something like that and there was the ability to put in 20 different variables if you wanted to change.
So you can create a lot of scenarios very, very quickly and I was stung by something in VBA; I'm going to switch over to VBA here.
Not to bore those of you who don't do macros but let's just talk about a simple “For Next” loop in VBA.
If you're going from 4 to 8 with a step of 2 what's going to happen is that loop is going to run once for 4, once for 6, once for 8 and then after the loop it's going to be at 10.
Here let me just show you; simple little loop here.
So we've got once for 4, once at 6, once at 8 then after the loop we are at 10, that's the way it works.
All right, so I was testing out the scenario manager and I said all right I want to run this one variable from 5% to 6% in 1% increments.
In other words, I wanted two scenarios, one at 5 and one at 6 and when I ran this macro it only ran the loop once.
I only got the 5% scenario not the 6% scenario and I thought that was really strange so I kind of stepped through the code here with F8.
First time through, i = 0.05, gets here to the next i and instead of going back up for the 6 it goes right down after the loop and it's now equal to 0.06 and what we're being stung by here is the floating-point problem.
Excel-- while computers store numbers as floating point which is binary and that doesn't convert well to tenths-- turns out that you know the 0.5 or 0.6 is a repeating number in binary and so it never comes out exactly equal; and I started to come up with some ideas where I would go not to 6 but to 0.06 then add a little bit, some tiny amount all the time.
Just very strange but I got a great tip from the MrExcel message board; there is actually a variable type that is called currency.
So if I added this one line; Dim i As Currency Currency is a very special type in Excel that has four decimal places so up to 0.0001 is the accuracy of it.
Watch when I run this now and that solves my problem.
I get the 5% and 6%.
So there you have it.
If you're doing loops in Excel and you're doing small steps, like 1% steps this “Dim i As Currency,” a very strange type will allow that loop to work.
Well, hey I want to thank you for stopping by.
We'll see you next time for another that netcast from MrExcel.
