VbExcel line counter during macro execution
Solved
necro27
Posted messages
125
Registration date
Status
Member
Last intervention
-
necro27 Posted messages 125 Registration date Status Member Last intervention -
necro27 Posted messages 125 Registration date Status Member Last intervention -
Hello,
I am refocusing my previous question under a more meaningful title:
How can I display real-time processing on the 'greyed-out' bar located just below the names of the Excel sheets while running a macro in Excel, which processes a varying number of rows depending on the files?
Basically, I would like a text like the following:
'For processing row 1:
Processing in progress: row 1 / 5,550.
'For processing row 3,000:
Processing in progress: row 3,000 / 5,550.
Clearly, the text needs to be updated for each new line.
This allows, for example, to follow the state of processing and especially to know if the program has crashed or not. Currently, processing 5,000 lines takes about 10 minutes, so how can I know before 20 minutes if the program has crashed or if it is still processing?
I hope I have been clear because I can’t find anything related to what I’m looking for on the internet, and even less in the VB help.
If you need clarity, I am all ears
Thank you in advance
Necro27
I am refocusing my previous question under a more meaningful title:
How can I display real-time processing on the 'greyed-out' bar located just below the names of the Excel sheets while running a macro in Excel, which processes a varying number of rows depending on the files?
Basically, I would like a text like the following:
'For processing row 1:
Processing in progress: row 1 / 5,550.
'For processing row 3,000:
Processing in progress: row 3,000 / 5,550.
Clearly, the text needs to be updated for each new line.
This allows, for example, to follow the state of processing and especially to know if the program has crashed or not. Currently, processing 5,000 lines takes about 10 minutes, so how can I know before 20 minutes if the program has crashed or if it is still processing?
I hope I have been clear because I can’t find anything related to what I’m looking for on the internet, and even less in the VB help.
If you need clarity, I am all ears
Thank you in advance
Necro27
Configuration: Windows 2003 Internet Explorer 6.0
1 answer
I found it!!
For those who have the same problem, you just need to use right before the end of the loop:
Application.statusbar="Processing line" & ligne & " out of " &nb_lig & " lines"
Example:
dim nb_lig as integer, ligne as integer
nb_lig = 1
ligne = 2
'Calculating the total number of lines
while range ("A1") <> Empty
[..code..]
nb_lig = nb_lig +1
activecell.offset(0,1).activate
wend
'Processing
while range ("A1")
[..code..]
ligne = ligne + 1
Application.statusbar="Processing line" & ligne & " out of " &nb_lig & " lines"
wend
And at the end of the macro:
Application.statusbar=false
End sub
There you go, the essentials are there, if you have any questions, feel free to ask, I will elaborate...
Have a good end of the day everyone
Necro27
For those who have the same problem, you just need to use right before the end of the loop:
Application.statusbar="Processing line" & ligne & " out of " &nb_lig & " lines"
Example:
dim nb_lig as integer, ligne as integer
nb_lig = 1
ligne = 2
'Calculating the total number of lines
while range ("A1") <> Empty
[..code..]
nb_lig = nb_lig +1
activecell.offset(0,1).activate
wend
'Processing
while range ("A1")
[..code..]
ligne = ligne + 1
Application.statusbar="Processing line" & ligne & " out of " &nb_lig & " lines"
wend
And at the end of the macro:
Application.statusbar=false
End sub
There you go, the essentials are there, if you have any questions, feel free to ask, I will elaborate...
Have a good end of the day everyone
Necro27