在 Excel VBA 中创建进度条

Creating a progress bar in Excel VBA

提问by Matt Ridge

I found an Excel VBA script and "tutorial", except it doesn't really break the code, and the bar code into separate sections.

我找到了一个 Excel VBA 脚本和“教程”，但它并没有真正破坏代码，并将条形码分成不同的部分。

http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/

http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/

The script attached to the "demo" adds random numbers into an excel sheet, as the progress bar goes across.

随着进度条的移动，附在“演示”上的脚本将随机数添加到 Excel 表中。

What this code on the sheet doesn't do is break the sections up, so saying ' this is the code for the random numbers, and ' this is the code for the actual progression bar.

工作表上的这段代码没有做的是将部分分解，所以说'这是随机数的代码，'这是实际进度条的代码。

Could someone disect this code and make it more "user friendly" for those who can't speak VBAeese as well as those who seemed to of written it?

有人可以剖析这段代码，让它对那些不会说 VBAeese 的人和那些似乎写过它的人更“用户友好”吗？

Thanks in advance.

提前致谢。

回答by danielpiestrak

Here is a heavily commented version of the code for you:

这是为您提供的代码的大量注释版本：

Sub Main()
'   Inserts random numbers on the active worksheet
    Dim Counter As Integer
    Dim RowMax As Integer, ColMax As Integer
    Dim r As Integer, c As Integer
    Dim PctDone As Single


    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    '- if this subroutine is ran on a sheet that is not called "Worksheet" then exit
    '-- change 'Worksheet' to whatever sheet you want the progress bar on

    Cells.Clear
    '- clear all cells in active worksheet

    Application.ScreenUpdating = False
    '-disables updating the screen in the for loop that follows
    '- that way if we are editing 1000 cells int he workbook it only needs to update them at the end when
    '- this is set back to true

    Counter = 1 '- counter counts what cell we are on
    RowMax = 100 '- this is how many rows will be filled with data
    ColMax = 25 '- this is how many columns will be filled with data

    '- note that Rowmax * ColMax = 2,500 co counter will go from 1 to 2,500

    For r = 1 To RowMax
        '-for each row 1 to 100 we will loop through the 25 columns to add the random number
        For c = 1 To ColMax
            '- enter a random number into the cell we are on (Cells(r,c))
            Cells(r, c) = Int(Rnd * 1000)
            '- +1 to the coutner so we can count which cell we ar eon out of 2,500
            Counter = Counter + 1
        Next c
        '- after finishing each column but before starting the next row
        '- check what percent done we are (to update the userform)
        PctDone = Counter / (RowMax * ColMax)

        '- Edit the progressbar called "UserForm1" (already exists in workbook)
        With UserForm1
            'Userform has 2 items in it a Label called 'FrameProgress' and a onject called 'LabelProgress'
            'Change the text in the Label called 'FrameProgress' to display the percent done we calculated earlier
            .FrameProgress.Caption = Format(PctDone, "0%")
            ' Resize the object called 'LabelProgress' to be X perxent of the width of the previous label (minus 10 to leave room on the edge)
            ' - where X is the percent we are done
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
'       The DoEvents statement is responsible for the form updating
        DoEvents
    Next r
    '- exit form when it is at 100%
    Unload UserForm1
End Sub

the only parts of the code that are useful to you are to note that while it is looping it is figuring out what percent it is done, then using that to update the form.

代码中唯一对您有用的部分是注意，当它循环时，它会计算完成的百分比，然后使用它来更新表单。

If you had a lot of code you could simply put the follwoing throughout it (assuming you build the form)

如果您有很多代码，您可以简单地将以下内容贯穿其中（假设您构建了表单）

Sub Example()
    'wait a few seconds
    Application.Wait (100000)
    'your code goes here instead of .wait

    PctDone = 0.3
    With UserForm1
        'Userform has 2 items in it a Label called 'FrameProgress' and a onject called 'LabelProgress'
        'Change the text in the Label called 'FrameProgress' to display the percent done we calculated earlier
        .FrameProgress.Caption = Format(PctDone, "0%")
        ' Resize the object called 'LabelProgress' to be X perxent of the width of the previous label (minus 10 to leave room on the edge)
        ' - where X is the percent we are done
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
    End With
    'The DoEvents statement is responsible for the form updating
    DoEvents

    Application.Wait (100000)
    'your code goes here instead of .wait

    PctDone = 0.6
    With UserForm1
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
    End With
    DoEvents

    Application.Wait (100000)
    'your code goes here instead of .wait

    PctDone = 0.9
    With UserForm1
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
    End With
    DoEvents

    Application.Wait (100000)
    'your code goes here instead of .wait

End Sub