Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm seriously dumbfound by this issue, I have a nested for-next loop that gives over a thousand loops I know this from seeing this code:

'find row ranges for department 1-10
    For i = 1 To 10 Step 1
        Dim tempRange As Range
        Set tempRange = GetRowRange(importsheet, DepColumn, i)
        'and iterate through the columns to insert them
        ' find row ranges for section
        If Not (importsheet.UsedRange.Find("afdeling_" & i) Is Nothing) Then
            Dim SecColumn
            Dim secRange As Range
            SecColumn = importsheet.UsedRange.Find("afdeling_" & i).column
            Set bCell = tempRange.Columns(SecColumn)
            tempRange.Sort Key1:=bCell, Order1:=xlAscending, Header:=xlYes
            For ix = 1 To 10 Step 1
                'check for a valid section column
                Set secRange = GetRowRange(tempRange, SecColumn, ix)
                totalposts = totalposts + IterateColumns(secRange, spgsheet, importsheet, debugsheet, year, month, week, Hospital, i, ix, varType, False)
            Next ix
        totalposts = totalposts + IterateColumns(tempRange, spgsheet, importsheet, debugsheet, year, month, week, Hospital, i, 0, varType, False)
        End If
    Next i

My progress function looks like this:

Function Progress()
iProgress = iProgress + 1
Application.StatusBar = Format(iProgress, "0%") & " Completed"
End Function

but the progressbar will often show up to 3300%.

How's that even possible?

share|improve this question
1 = 100% , so 33 = 3300% – Larry Feb 25 '13 at 10:33
You would be better served asking this on stackoverflow – brettdj Feb 25 '13 at 11:07
There is no possible way this code is ran 1,000 times. You either didn't post all the code or your debugging methods are flawed. – Ramhound Feb 25 '13 at 11:52
up vote 1 down vote accepted

You are not showing all of the code. For example, I assume thst iProgress is globally defined but we can't see that here.

Also, why are you doing DIM statements inside a loop? You should only do them once and also you should be setting object variables to nothing once you've finished with them. Failure to do that is likely to result in some nasty memory issues along the way.

To answer the specific question. You've set iProgress to be a counter not a %.

To get the %, you need to know how many items you will be iterating over before starting the progress bar. You then need the current item counter and the total. CurrentCount/TotalItemCount gives you the progress.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.