vba:: sample code - 작업진행창 progressBar004

 

엑셀 VBA 실행상태 확인, progressBar004

작업 진행상태를 알려주는, 상태바 ProgressBar004 의 예시 엑셀파일입니다.

간결한 코드로 간편히 사용할 수 있습니다. 

wVBA002_progessBar004.xlsm

Option Explicit
'https://www.excel-easy.com/vba/examples/progress-indicator.html
 
 
Private min_Value As Long, current_Value As Long, max_Value As Long
 
 
Private Sub UserForm_Initialize()
    ' UserForm Init code from
    ' http://www.program1472.com/bbs/board.php?bo_table=TB_03&wr_id=111&page=2
    
    min_Value = 0: current_Value = 0: max_Value = 100
    Me.Height = 70      '// 폼의 넓이와 높이를 지정
    Me.Width = 300
    Label1.Caption = ""
    Label2.Caption = ""
    
    
    '// 배경이 될 라벨의 사이즈와 위치 설정
    With Label1: .Left = 15: .Top = 5: .Width = InsideWidth - 30: .Height = InsideHeight - 20: End With
    '// 진행이 표시될 라벨의 사이즈와 위치 설정
    With Label2: .Left = 15: .Top = 5: .Width = 0: .Height = InsideHeight - 20: End With
    '// 진행값이 표시될 라벨의 사이즈와 위치, 백스타일 투명하게 설정
    With Label3: .Left = 15: .Top = InsideHeight / 3.6: .Width = InsideWidth - 30: .Height = InsideHeight / 3: .BackStyle = 0: End With
    Label2.Width = 0                        '// 초기 진행상태 0
    Label3 = "0 / " & max_Value             '// 표시할 테스트 초기값
End Sub
 
 
' Simple drawing current status
Sub progressUpdate(current_Value As Single)
    'Update Current value
    Label2.Width = (Me.InsideWidth - (Label2.Left * 2)) * (current_Value / max_Value)
    Label3 = current_Value & " / " & max_Value
    Repaint
End Sub
 
 
 
'' // for example, copy & paste in Module, NOT here
'Sub progressBar004example()
'    'For example
'
'    Dim i As Integer, j As Integer, lastRow As Integer
'
'    lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
'    j = lastRow
'
'    Range(Cells(1, 3), Range("C" & j)).Clear
'
'    For i = 1 To j
'        Range("C" & i) = Range("A" & i) + Range("B" & i)
'
'        'Show hide window and set as zero, 0 percent
'        If Not progressBar004.Visible Then progressBar004.Show 0
'
'
'        'ProgressBar Status Update
'        'total work j, current work i >> calculate % percentage value and show it
'        'Update progressBar.Value
'        If j > 100 Then
'            'Option1) For long line
'            If (i Mod 10) = 0 Then progressBar004.Caption = i & " of " & j & " on progress."
''           progressBar004.progressUpdate (i / j * 100)
'        Else
'            'Option2) Not used commonly, in short line
'            progressBar004.Caption = i & " of " & j & " on progress."
'            progressBar004.progressUpdate (i / j * 100)
'        End If
'
'
'        '실제 사용시 아래 SLEEP 문구는 삭제하세요
'        'In actual work, Delete below
'        Sleep 300
'    Next
'
'    'Unloading progressBar
'    Unload progressBar004
'
'End Sub