Excel Status bar will not update

[sxotty]

I have a relatively simple problem. I have some VBA code that goes through a worksheet 600,000 rows long. It copies specific rows and creates new rows to a new worksheet based on many criteria. It is extremely slow (takes hours to run) so I created a little statement to update the status bar.
next_percent = round(read_row/max_row * 100,0)
if next_percent > current_percent then
Application.StatusBar = "Completed " & next_percent & "%or " & read_row & "/" & max_row " rows"
current_percent = next_percent
endif Simple enough. It is in an if statement and updates only when it calculates that it has done max_row/100 more rows. It does work when I use ctrl + f8 to step thru the program until it hits it. It works if you do it with a small number of rows to experiment as well. But when I do the entire data set it just freezes at the first update that I have before the loop starts. Any ideas of why this is or how to fix it? I really like to have it so I know if there is a weird goof up in the code I can track down around where it is occurring. Without it I have to wait hours and hours hoping things are working well. I have been searching and searching to no avail.. The best I found was some threads where someone asked something similar and never got an answer.

edit: sorry code format was all fubar

[Trebor76]

Hi sxotty,

Welcome to the forum!!

You'll have to include the code along with your other loop code so it refreshes itself on each line.

If you could post a workbook with a snippet of your data and the results you want to see after a procedure is run, I'll have a look at putting something together for you (no promises though).

Regards,

Robert

[sxotty]

The code is inside the loop. It runs it the status bar just doesn't update.

Like I said if I put the cursor there and press ctrl + f8 the status bar updates. It works all the way through the loop, but as it takes ages to complete I only manually ran it up to 7% complete.

When I just run the program though it doesn't work. I used the same code in another program that was shorter and it functioned as expected once, but the other time it quit after changing the focus away from excel and back. It is a screen refresh/ updating type issue. I just did not know if there was a way to force excel to actually display the update. I left the program running over night and it finished so at least I know it works now.

edit:
BTW does the Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual screw up the status bar? I have them to speed up the code.

Also I checked and when I run it with 1000 rows instead of 600,000 rows then everything works peachy.

[mbarron]

If Application.ScreenUpdating is set to false the status bar will not update.

[mbarron]

sxotty,
Can you post the code you are using? Someone here may be able to help make the code more efficient.

[sxotty]

If Application.ScreenUpdating is set to false the status bar will not update.
It is strange that it does work with screenupdating set to false on shorter files though. I will have to change it and see, though the decrease in speed may not be worth it.

Thanks

edit:

I can post the code in a bit, it is running now so I cannot access it. It is pretty messy and sloppy though. I did change a copy row command to copy range command and I hope that will speed it up a bit at least.

[mbarron]

You are correct.... I apologize.

[sxotty]

Ok here goes the code dump basically the idea is to find skipped records and insert the known values as well as copy the included records to another sheet. It does work. What it doesn't do is update the status bar when run for 600,000 records. It does update the status bar when run on short sections. Any help speeding it up would of course be greatly appreciated. I am a novice coder for sure.

Now the psuedo code is it goes through reading IDs to find a groups start and end, then it finds the number of sub IDs (called VIDS) that belong to each ID group. Then it copies the used and unused VIDS to a new sheet dropping and ID group with no VIDS.


Sub insert_unused_VIDS()
Application.StatusBar = "Starting Procedure"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim InputData_worksheet As Worksheet
Set InputData_worksheet = ActiveSheet
Dim OutputData_worksheet As Worksheet
Set OutputData_worksheet = ActiveWorkbook.Worksheets(2)

Dim max_row As Long, read_row As Long
Dim ID_current As String
Dim VID As String
max_row = InputData_worksheet.Cells(Rows.Count, 1).End(xlUp).Row 'this is the maximum row in the load work book
Application.StatusBar = "Number of rows " & max_row
Dim current_percent As Integer, next_percent As Integer
current_percent = 0
read_row = 2
Dim VID_used_col As Integer
VID_used_col = 77

Dim copy_row As Long
copy_row = 2
read_row = 2
Dim VID_used() As Integer
Dim temp_read_row As Long

While read_row < max_row

next_percent = Round(read_row / max_row * 100, 0)
If next_percent > current_percent Then
Application.StatusBar = "Completed " & next_percent & "% or " & read_row & "/" & max_row & " rows, step 1 of 2"
current_percent = next_percent
End If

temp_read_row = read_row 'the temp value keeps track of the start of ID
ID_start = read_row
loop_over = 0

'check for begining and end of hh
ID_current = InputData_worksheet.Cells(read_row, 1)
While loop_over = 0 And read_row < max_row
read_row = read_row + 1
ID_next = InputData_worksheet.Cells(read_row, 1)
If StrComp(ID_current, ID_next) <> 0 Then
'The ID changed
loop_over = 1
ID_end = read_row - 1
End If
If read_row = max_row Then
If loop_over = 1 Then 'it already went into upper loop so don't mess with ID_end

Else
ID_end = read_row
End If
loop_over = 1
End If
Wend

NUM_VIDS = InputData_worksheet.Cells(ID_start, "M")
If NUM_VIDS > 0 Then
'they have VIDS

ReDim VID_used(1 To NUM_VIDS)
'mark VIDS that were used
For x = ID_start To ID_end
HHVC = InputData_worksheet.Cells(x, "BY")
If HHVC <= NUM_VIDS And HHVC > 0 Then
'Checks to see if a valid VID was used
'deletes invalid
VID_used(HHVC) = 1
End If
Next x

For x = 1 To NUM_VIDS
'check to see if a VID was used
If VID_used(x) = 0 Then
'This VID was not used so must skip a copy row and paste a new VID in there
'We know almost everything in the row so just copy the entire thing then erase the spots where nothing goes
If VID_end = ID_end Then 'when the last VID is the one not used then it would copy the next row
InputData_worksheet.Cells(temp_read_row - 1, 1).EntireRow.Copy _
Destination:=OutputData_worksheet.Cells(copy_row, 1)
Else 'when the first VID is unused it would copy prior row otherwise
InputData_worksheet.Cells(temp_read_row, 1).EntireRow.Copy _
Destination:=OutputData_worksheet.Cells(copy_row, 1)
End If

'Data is unknown put in -1
OutputData_worksheet.Cells(copy_row, 2) = -1
OutputData_worksheet.Cells(copy_row, 3) = -1
OutputData_worksheet.Cells(copy_row, "F") = -1
OutputData_worksheet.Cells(copy_row, "G") = -1
OutputData_worksheet.Cells(copy_row, "H") = -1
OutputData_worksheet.Cells(copy_row, "S") = -1
OutputData_worksheet.Cells(copy_row, "T") = -1
OutputData_worksheet.Cells(copy_row, "U") = -1
OutputData_worksheet.Cells(copy_row, "Y") = -1
OutputData_worksheet.Cells(copy_row, "AA") = -1
OutputData_worksheet.Cells(copy_row, "AI") = -1
OutputData_worksheet.Cells(copy_row, "AJ") = -1
OutputData_worksheet.Cells(copy_row, "AK") = -1
OutputData_worksheet.Cells(copy_row, "AL") = -1
OutputData_worksheet.Cells(copy_row, "AO") = -1
OutputData_worksheet.Cells(copy_row, "AP") = -1
OutputData_worksheet.Cells(copy_row, "AQ") = -1
OutputData_worksheet.Cells(copy_row, "AR") = -1
OutputData_worksheet.Cells(copy_row, "AT") = -1
OutputData_worksheet.Cells(copy_row, "AW") = -1
OutputData_worksheet.Cells(copy_row, "BL") = -1
OutputData_worksheet.Cells(copy_row, "BP") = -1
OutputData_worksheet.Cells(copy_row, "BR") = -1
OutputData_worksheet.Cells(copy_row, "BS") = -1
OutputData_worksheet.Cells(copy_row, "BT") = -1
OutputData_worksheet.Cells(copy_row, "BZ") = -1
OutputData_worksheet.Cells(copy_row, "CA") = -1
OutputData_worksheet.Cells(copy_row, "CB") = -1
OutputData_worksheet.Cells(copy_row, "CC") = -1
OutputData_worksheet.Cells(copy_row, "CD") = -1
OutputData_worksheet.Cells(copy_row, "CE") = -1
OutputData_worksheet.Cells(copy_row, "CF") = -1
OutputData_worksheet.Cells(copy_row, "CI") = -1
'BO=1 always I guess this could be inserted after program is done saving a bit of time
OutputData_worksheet.Cells(copy_row, "BO") = 1
'BY is the VID used
OutputData_worksheet.Cells(copy_row, VID_used_col) = x
OutputData_worksheet.Cells(copy_row, "CK") = 0

copy_row = copy_row + 1
ElseIf VID_used(x) = 1 Then
'temp_read_row needs to increment if the VID is -1
ID_current = InputData_worksheet.Cells(temp_read_row, 1)
veh_used = InputData_worksheet.Cells(temp_read_row, VID_used_col)
If veh_used = -1 Then
loop_over = 0
While loop_over = 0 And temp_read_row < max_row
temp_read_row = temp_read_row + 1
veh_used = InputData_worksheet.Cells(temp_read_row, VID_used_col)
If veh_used > 0 Then
loop_over = 1
End If
Wend
End If
VID_start = temp_read_row
loop_over = 0
While loop_over = 0 And temp_read_row - 1 < max_row
temp_read_row = temp_read_row + 1
ID_next = InputData_worksheet.Cells(temp_read_row, 1)
next_veh_used = InputData_worksheet.Cells(temp_read_row, VID_used_col)
If StrComp(ID_current, ID_next) <> 0 Or veh_used <> next_veh_used Or temp_read_row > max_row Then
'The VID changed (noted b/c of VID or ID)
loop_over = 1
VID_end = temp_read_row - 1
End If
Wend

copy_end = VID_end - VID_start + copy_row
InputData_worksheet.Range _
(InputData_worksheet.Cells(VID_start, 1), InputData_worksheet.Cells(VID_end, 100)) _
.Copy _
Destination:=OutputData_worksheet.Range _
(OutputData_worksheet.Cells(copy_row, 1), OutputData_worksheet.Cells(copy_end, 1))
OutputData_worksheet.Range _
(OutputData_worksheet.Cells(copy_row, "CK"), OutputData_worksheet.Cells(copy_end, "CK")) = 1

copy_row = copy_row + (VID_end - VID_start) + 1


End If
Next x
Else
'ID has no VIDS

End If
Wend
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = False
End Sub

[mbarron]

Can you post a sample workbook too?

[sxotty]

I will try I have to delete most of the data as you can imagine, but I left in the parts necessary for the program to work. I included only a small number of rows.

[mbarron]

I created a sheet using your data that has 612,000 rows and the status bar was updating.

What are the rules as far as what gets copied and when do the extra lines get inserted?

[sxotty]

The extra lines get inserted when the ID has VIDs associated with it that do not have a data entry yet. So any unused VID gets inserted. The used VIDs are just copied.

So for example the last two IDs have 2 and 3 VIDs associated respectively. Yet there are only entries for 1 and 2 VIDs for those IDs. Thus a new VID entry is put in for ID 47101570 and one for 47101580. If you ran the program you can see where it put new rows in b/c it puts -1 into spots where data will be unavailable to indicate the lack of information. It is likely that it has something to do with the lack of actual data in the sheet I sent you so that the writing is way less than in reality. Normally data is in all the cells from the column A to CJ and CK is the only one that I actually add to mark whether the VID was included in original file or not.

The program works as intended, it is just horribly slow and the status bar doesn't update consistently, but it could be a bug in Vista display or something outside of excel's control.

To be clear the status bar sometimes does update at the beginning, or if I use step thru mode it always does. It just quits at some point it might have something to do with the frost mode /non-responsive program. If I switch focus to another program it is very likely to happen, or after some apparently random amount of time it stops (sometimes it doesn't even get to 1% other times to 40%).

I was just hoping there was an easy fix that someone knew, like "force update" or some command that would ensure it actually showed the silly status bar. Maybe a wait command or something would do it. Since it takes like 5 hours to run though I don't want to try it again just for fun :)

edit:

I appreciate your help, but don't worry about it too much. I just thought there would be an easy fix as often there is. If there is any insight into how to make the program not dog slow that is good, but otherwise I would just give up since it works at least.

[ReneL]

Hi sxotyy,

In my program, I had the same problem about the update of the statusbar as you (program running for 5 minutes). Seems to only happen in Excel 2010 and not in previous version.
I managed to overcome the problem by adding the command DoEvents after changing the statusbar.

Regards,

Rene

[sxotty]

Hey thanks I will try to remember that next time I need it. I read about that command and it makes perfect sense as to why that would work.

[snb]

you better use a variable in which you read column A: sn=columns(1).specialcells(2)
You can perform the checking in this variable.
The copying of cells can best be done afterwards in 1 go by copying whole rows.
Before blinking your eyes the macro will be terminated and you won't need a statusbar at all.