• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Show macro progression (no loop on the code)

C

cacos

Member
Hi everyone! Here's a question I've had for some time.


It's mostly about the visuals of excel reports, but I'd love to have a way for excel to show something- a clock, a pop up, anything- that indicates the progression of a macro as it's running.


I've looked online and found pretty cool stuff but it's all for codes that have a loop on them.


Is there a way to do this with a simple vba code?


Thanks!!
 
I don't believe so. So the loop adds overhead and increases macro execution time. But having a progress bar makes the execution time seem significantly shorter than no progress bar.
 
Since as a developer, one would know what percentage of work is complete during each stages of the processing, you could just use the Application.StatusBar and display the completion in percentages. It doesn't need a VBA loop, but it needs at least 1 line of code each whenever you want to display the status.
 
Cacos


You have two choices


1. use existing facilities like the status bar (bottom bar in windows)

2. develop a custom control


Both add a very small overhead to your project


I will post details of option 1 later


I have never done an option 2 implementation so dont ask me

Look it up on the net
 
Craig hatmaker had a great one at his Beyond Excel blog. Overhead depends on what you are doing. For instance if you are iterating through a collection and checking each items status (and then possibly changing that status) - for instance inverting the .visible status in a pivotitems collection for a pivotfield that contains 10000 pivotitems- and you want to show progress then you have no option but to add a counter and check its value for every one of those iterations. Then you need to change your progress bar after every x counts. All of which takes time. In cases like this then maybe its better to simply warn the user that the operation might take a while instead of showing a "% complete" update.
 
Hi, Cacos!


Once I had (I should have again as a new Excel version has been released) a process that lasted 20 minutes, a web query for this project:

http://chandoo.org/forums/topic/excel-multilanguage-formula-translator-and-function-reference

in fact a preparatory step for building the final workbook.


As I had to run it several and several times and didn't liked the status bar approach I just build a user form with additional info per step (items, repeated within a loop) despite of the status bar.


I tried to cut off everything useless for general purposes and arrived at this:

https://dl.dropboxusercontent.com/u/60558749/VBA%20-%20Show%20macro%20progression%20%28no%20loop%20on%20the%20code%29%20%28for%20cacos%20at%20chandoo.org%29.xlsm


Test it and check it's somehow related to what you were looking after.


Good luck!


Regards!
 
Hi everyone !


A friend of mine complain for a slow procedure within 48s to operate an external file.

Just by retiring the scrollbar, the procedure takes 30s less, just 18s ‼

After recoding all its process, my last procedure needs no more than 2s …

(In fact it depends on the processor)
 
Marc - I'm not clear what you mean by 'retiring the scrollbar'. But certainly your comment points out that before implementing additional overhead like progress indicators, it's a good idea to see if restructuring the code will make any such progress indicator redundant.


General best practice VBA stuff in this regards includes:

1. Turning off calculation, screenupdating, pivot layout update, and event handling options where relevent.

2.Avoiding loops whereever possible (for instance by using the SpecialCells method to select cells with constants or formulas, instead of looping through them and checking if they have constants or formulas)

3. Using Select Case statements instead of heaps of independant IF statements (or using ELSEIF)

4. Thinking really really hard about how you can polish your code so it goes as directly from A to B as possible

5. Understanding when to leverage off things like the Dictionary object, SQL etc.

6. The list goes on and on....
 
Sorry for my mistranslating, by retiring I mean deleting the scrollbar process …


Using of array variables is faster than working with cells.
 
Marc: Re Using of array variables is faster than working with cells. .


What's slow is transferring data to and from the worksheet. Working with cells is actually pretty fast, provided you're not transferring data into VBA and back. For instance, it will often be faster to use VBA to write some array functions to the spreadsheet as opposed to pulling the data into a variant array and then doing it in VBA, then writing it back. OR as Charles Williams puts it The only thing faster than bringing all the data across to VBA in one lump is to use Excel functions to bring across only the minimum data your function needs. See http://fastexcel.wordpress.com/2011/06/06/writing-efficient-vba-udfs-part-2/


There's also a limit to how many rows you can transfer back to the worksheet from VBA in one go. Basically when they released the bigger Excel grid (Excel 2007) they forgot to remove the 65536 row limit of transferring data back from VBA to Excel. So there's another bottleneck...you have to dump data back in blocks of 65536 rows.
 
Sorry but I'm not specially talking of UDFs …

But for sure even in VBA I use a maximum of internal Excel's functions.
 
OK.   Could you explain the 65536 row limit of transferring data back from VBA to Excel since Excel 2007 ?

Just 'cause I never had any problem to transfer an array above this limit, whatever from sheet to VBA nor from VBA to sheet ‼


I've just done a little test in Excel 2007 with an array of 100 000 (10^5) elements : that works without any problem !

With an i3 processor, the test takes less than 0.9s (transfer to VBA, operate the array, transfer back),

with working within the cells, more than 13.5s ‼


In fact I met that limit for the Excel's inner functions but not for transfer from or to range of cells …
 
Sorry, I was off on that one...that limitation only occurs for UDFs that are array entered on the worksheet.
 
To the original question:


You can do something like this: http://support.microsoft.com/kb/211736


However rather than updating the progress bar in a loop 'PctDone = Counter / (RowMax * ColMax)', just write:


PctDone =0

PctDone =0.25

PctDone =0.5

PctDone =0.75

PctDone =1


etc at various stages to state the percentage completed
 
Thanks everyone for responding!


Thanks DaveTurton and SirJB I'll try your approaches, thanks also to Hui, jeffrey and everyone who jumped in.
 
You must log in or register to reply here.
Back
Top