Calculation Percentage Value

[radddogg]

Hi,

When running a large calculation action the status bar states how far it has processed in percentage. Is there a way I can use this value? I have a progress indicator form and I'd like to update the form with this value as screenupdating is set to false so the status bar doesn't always show.

Cheers

[Keebellah]

All you have to do is take the current value of a loop being processed and devide it by the total number of rows to process

Lets say you have a loop that will process 10000 rows

Please Login or Register  to view this content.

You can place that calculation in the form or wherever you want

[radddogg]

Hi,

Maybe I wasn't clear. I already use something similar for the loop functions. In this case the workbook is calculating approx. 300,000 formulas which takes approx. 45 seconds. If I step through the code I see the formula calculation percentage displayed in the statusbar, however when running the code this is not shown due to display alerts being set to false. In this case the user may think the application is hanging. I want to pass the formula calculation percentage value to the userform.

Does this make sense?

Thanks

[Keebellah]

It makes perfect sense and that is exactly what I told you, pass the value to this progress bar user form you have. I do this very often for the same reason.

BTW if application display alerts is set to false the Status bar will remain visible and show the value you pass it unless of course if you set this to not visible.
Another thing, Excel (as fo version 2010) in many case shoes "Not responding" even though the calculations continue, this something I think the Microsoft developers found funny

[radddogg]

It makes perfect sense and that is exactly what I told you, pass the value to this progress bar user form you have. I do this very often for the same reason.

Pass which value? This is what I'm looking for. I use manual calculation and Application.Calculate to start the calculation process. Are you suggesting I define the calculation range and perform a calculation loop in order to define the calculation percentage? If so, I thought I could use the value which the application already produces.

BTW if application display alerts is set to false the Status bar will remain visible and show the value you pass it unless of course if you set this to not visible.
Another thing, Excel (as fo version 2010) in many case shoes "Not responding" even though the calculations continue, this something I think the Microsoft developers found funny

This is the problem. The status bar is set to false yet the calculation percentage doesn't show. The title bar states not responding.

[Keebellah]

If you are not using macros then there is nothing to pass.

[radddogg]

In my first post I stated I was using a userform to display status, in my second post I stated I was using a loop to populate it. Is it not clear that I am using VBA? During the macro I use a formula also.

calc.JPG

I want to use this value in a userform.

[Keebellah]

If you have the userform with a progress bar, that progress bar works, right? So if it works it has data to show the progress, right? Well then you have the value to calculate the percentage too, so use this value to fill a textbox with that value.
What you see in the thumbnail you attached is Excel's internal calculation but you can replicate ith with the value for your loop, just format the text accordingly, This I explained with the small loop I posted earlier.

Please Login or Register  to view this content.

The part about the processors you will have to read from the system values there are codes for that, it;s not really VBA it's some API tools that retrieve your system's characteristics like processors, memory etc. It;s the same that you see when you open the Task Manager.

So again the section that updates that userform will need some extra text to show it.

Without the code I cannot tell you more.

[Keebellah]

BTW way, you're so good at explaing.
Have you tried Google?

Try this

Please Login or Register  to view this content.

The link: http://codevba.com/office/environ.htm#.WJjXkxsrK9I
The Google search criteria:

excel vba get number of processors and other system characteristics

Happy coding

[Keebellah]

And instead of populating the statusbar you can place the text in the userform to be displayed in a textbox or whatever

Please Login or Register  to view this content.

[Keebellah]

This is how the progressbar looks that I sue and the % value is shown too.

[Jayson Mora]

I've been searching for the solution on this, too! I want to display in my own progress bar the exact value of the xx% in the "Calculating: (x Processor(s)): xx%" in the lower right corner of the status bar. I hope someone could provide a solution to this. Thanks.

[radddogg]

I've been searching for the solution on this, too! I want to display in my own progress bar the exact value of the xx% in the "Calculating: (x Processor(s)): xx%" in the lower right corner of the status bar. I hope someone could provide a solution to this. Thanks.

Hi,

In the end I was never able to find a solution to this. In the end I created a userform progress bar and looped through the range, calculating row by row, then for each loop dividing the row number (i) by the total number of rows to get the percentage. For some reason I found iterating through the range much faster than calculating the entire range in one event. 7 seconds versus 5 minutes.

You can print this percentage value in the status bar.

I'm away from my computer for the weekend but if you search my recent threads I posted an answer to it there.