在Excel VBA中创建进度条 [英] Creating a progress bar in Excel VBA

问题描述

我发现了一个Excel VBA脚本和教程，除了它不会真正地将代码和条形码分成不同的部分。

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

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

附加到演示的脚本会将随机数添加到Excel表中，因为进度条已经过去。

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

工作表中的这些代码不会打破这些部分，所以说'这是随机数的代码，'这是代码实际的进度条。

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.

有人可能会破坏这段代码，使其对于不能说VBAeese的人以及似乎写的人来说更加用户友好它？

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?

提前感谢。

推荐答案

这是一个大量注释版本的代码：

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

这篇关于在Excel VBA中创建进度条的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！