Learn Excel - Step by 0.01: Podcast #1350

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Feb 28, 2011.
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.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top