CODEWITHSUNDEEP
mathexcelprogress-barvba
Our Man in Bananas
Our Man in Bananas

Reputation: 5981

Calculate percentage fill of progress bar

I am having trouble setting the worrect width of my progress bar image on a userform in Excel VBA.

I have a userform, and on that form is a label, and an image.

The maximum width of the image is 330.

In my loop from 1 to intNumberOfGetRows (in this case 64) I want to update the width property of the image to show progress as each 1000 record chunk of a large recordset is put in an array and written to a csv file.

Here is my code:

  For intRecord = 1 To intNumberOfGetRows + 1 ' outer loop

    ' put the data in an array and print to file
    arrContacts = adoRsMailshotAccConData.GetRows(intRows)

    With fsOutputFile

        For iDx = 0 To UBound(arrContacts, 2)
            .WriteLine arrContacts(0, iDx)
        Next

        '.Close
    End With

    sMsg = "Number " & intRecord & " of " & intNumberOfGetRows
    Application.StatusBar = sMsg

    With frmProgress
        .lblProgress.Caption = sMsg
        .imgProgress.Width = (intRecord / intNumberOfGetRows) * 330
        DoEvents
        .Repaint
    End With

    DoEvents
Next

when intRecord is 13, then that should be around 20% of the image filled, which means the width of the image should be 66, so I came up with this:

330 * (intRecord / intNumberOfGetRows * 100) / 100

It looks as though I have forgotten my basic maths theory, so is that the best way?

thanks for any suggestions on how to improve this

Philip

Upvotes: 1

Views: 2375

Answers (1)

Vicky
Vicky

Reputation: 13244

You don't need those 100s in there. You just want your progress to show you have done (in your example) 13/64ths of the total, and the total is 330, so all you need to calculate is 330 * (13/64):

330 * (intRecord / intNumberOfGetRows)

Upvotes: 3

Related Questions