Round to nearest 10%

[Dr. Demento]

Thanks to shg, I'm now able to estimate how much time a sub has left before it's complete. However, when I put this function within a loop, I'd like to test if the % complete (dPctComp) is a multiple of 10% (so that the function doesn't produce an output with every loop, only at each 10% complete. From what I've read, the nuances of matching a double are mind-boggling (clearly out of my ability). However, I'm hoping someone can tell me what is wrong with this formula or if I need to use a different approach.

I've tried using rounding factor (rFactor) as a double (0.10) or long (10 (%)), but neither one is ever true. I realize that with large loops, the equation may be true over a confined range (10% of 10,000 is 1,000 and the test may be true from 990 - 1010 (or something like that)), which is acceptable. In fact, if you can put in a variable that will allow the degree of precision, that would be awesome!

Dim dPctComp As Double
   dPctComp = 0#
Dim rFactor As Long
  rFactor = 10 ' ~~ Rounding factor (%)

If CDbl(Round(dPctComp * rFactor, 1)) - CInt(Round(dPctComp * rFactor, 1)) <> 0# then DoStuff ' [URL]https://stackoverflow.com/a/1795444\[/URL]

I feel like I'm flailing around in the dark with this one.

Thanks y'all.

 

[Andrew Fergus]

I'm not sure I can help with that sorry. I will however give you an example of how I handle the same situation. The following code is from a real life macro:

For LC = 3 To LL
Application.StatusBar = "Copying new data..." & Format((LC - 2) / (LL - 2), "0.0%")


LC is a loop counter, LL is the loop limit. Each time the loop counter is incremented, the % completed is updated in the status bar. It's very simple, one line of code and it works without slowing down the macro (noticeably). If you want the % to complete then it would be something like:
Application.StatusBar = "% to go : " & Format(1-(LC/LL),"0%")

 

[Dr. Demento]

Andrew,

I like that; thank you.

One issue with mine is that I don't know if it's the count down function itself or the sub it's measuring, but sufficient memory gets used that it's unable to update until after it's complete, thereby negating the whole experience

Out of curiosity, why the minus 2 for LC & LL? Is that specific to your usage or am I missing something?

Thanks again.

 

[Andrew Fergus]

You're welcome. In answer to your questions..

One issue with mine is that I don't know if it's the count down function itself or the sub it's measuring, but sufficient memory gets used that it's unable to update until after it's complete, thereby negating the whole experience

It's hard to tell if this is a coding issue or a resource issue.
Try inserting a single line:
DoEvents
into one of your loops. This will force the computer to not hog all of the resources and the screen *may* get updated assuming there are no other issues with the code. I find this works even if I have screen updating turned off within the macro. Unless this is another issue...?

Out of curiosity, why the minus 2 for LC & LL? Is that specific to your usage or am I missing something?

This was from a real life example where the data started in row 3 and so the loop counter started at 3 - hence the deduction of 2 from both the LC and LL values. For instance if I am processing data from row 3 to 10 then there are only 8 lines of data, the first record will be row 3 which is record number 1 of 8 or 12.5% complete. The macro was working with data directly in the sheet. However often I take all the spreadsheet data into an array and manipulate it there before outputting the results - not so with the example I copied/pasted. Using an array for data manipulation is much much faster than working directly in the sheet with macros.