[vba] 작업진행 상태바 만들기(Progress Bar)

엑셀 매크로로 몇만 행을 돌리다 보면 화면이 하얗게 굳은 채로 한참 있습니다. 만든 사람은 돌아가는 줄 알지만, 쓰는 사람은 엑셀이 죽은 줄 알고 강제 종료를 누르죠. 그래서 오래 걸리는 매크로에는 진행 상태를 보여 주는 창을 하나 붙여 두는 편입니다. VBA 에는 쓸 만한 기본 진행 막대 컨트롤이 없어서(추가 컨트롤을 등록하면 있긴 하지만 PC 마다 등록 여부가 달라 배포할 때 골치 아픕니다) 라벨 두 장을 겹쳐서 흉내 내는 방식을 많이 씁니다.

원리는 간단합니다. 바닥에 깔 라벨 하나, 그 위에 폭이 늘어나는 라벨 하나, 숫자를 보여 줄 투명 라벨 하나. 진행값이 바뀔 때마다 두 번째 라벨의 Width 만 비율에 맞춰 바꿔 주면 막대가 차오르는 것처럼 보입니다.

폼 준비

VBA 편집기에서 UserForm 하나와 표준 Module 하나를 추가합니다.

폼에 Label 을 세 개(Label1, Label2, Label3) 올립니다. 아래 화면에는 하나가 빠져 보이는데 세 개 모두 있어야 합니다. 위치나 크기는 대충 놓아도 됩니다. 코드에서 다시 잡아 줍니다.

Label1 은 바탕이 될 막대라서 BackColor 를 연한 색으로 정합니다.

Label2 는 차오르는 막대입니다. 바탕과 확실히 구분되는 진한 색을 고릅니다. Label3 은 손대지 않아도 됩니다.

마지막으로 폼 이름을 UserForm1 에서 pBar 로 바꿉니다. 이후 코드에서 전부 이 이름으로 부릅니다.

pBar 폼 코드

pBar 를 더블클릭해 코드 창을 열고 아래를 붙여 넣습니다. 폼이 뜰 때 크기와 라벨 배치를 잡고, 바깥에서는 Value, maxValue, Text 세 속성만 건드리게 만들어 두었습니다.

Private min_Value As Long, run_Value As Long, max_Value As Long

'// 폼이 실행될 때
Private Sub UserForm_Initialize()
    min_Value = 0: run_Value = 0: max_Value = 100    '// 기본값 설정
    Me.Height = 55: Me.Width = 300    '// 폼의 넓이와 높이를 지정
    Label1.Caption = "": Label2.Caption = ""
    '// 배경이 될 라벨의 사이즈와 위치 설정
    With Label1: .Left = 5: .Top = 5: .Width = InsideWidth - 10: .Height = InsideHeight - 10: End With
    '// 진행이 표시될 라벨의 사이즈와 위치 설정
    With Label2: .Left = 5: .Top = 5: .Width = 0: .Height = InsideHeight - 10: End With
    '// 진행값이 표시될 라벨의 사이즈와 위치, 백스타일 투명하게 설정
    With Label3: .Left = 5: .Top = InsideHeight / 3: .Width = InsideWidth - 10: .Height = InsideHeight / 3: .BackStyle = 0: End With
    Label2.Width = 0    '// 초기 진행상태 0
    Label3 = "0 / " & max_Value '// 표시할 텍스트 초기값
End Sub

'// 현재 진행값을 얻음
Public Property Get Value() As String
    Value = run_Value
End Property

'// 현재 진행값을 설정하고 화면 갱신 프로시저 호출
Public Property Let Value(runVale As String)
    run_Value = runVale '// 현재 진행값을 변경
    workRun '// 실제 라벨의 사이즈를 재설정하기 위해 프로시저 호출
End Property

'// 진행상태 바 업데이트 및 표시값 출력
Sub workRun()
    Label2.Width = (Me.InsideWidth - (Label2.Left * 2)) * (run_Value / max_Value)
    Label3 = run_Value & " / " & max_Value
    Repaint
End Sub

'// max값을 얻음
Public Property Get maxValue() As String
    maxValue = max_Value
End Property

'// max값 설정
Public Property Let maxValue(maxVale_ As String)
    max_Value = maxVale_
    workRun
End Property

'// 폼 제목 변경
Public Property Let Text(text_ As String)
    Me.Caption = text_
    Repaint
End Property

핵심은 workRun 의 한 줄입니다. 폼 안쪽 폭에서 좌우 여백(5씩)을 뺀 길이에 현재값 / 최대값 을 곱해 Label2 폭으로 넣습니다. 그다음 Repaint 로 폼을 다시 그리게 하는데, 이게 없으면 루프가 도는 동안 화면이 갱신되지 않아 막대가 끝에 한 번에 꽉 차 버립니다.

호출하는 쪽: 건수로 보여 줄 때와 %로 보여 줄 때

Module1 에서 테스트해 봅니다. pBar.Show 0 의 0 은 모덜리스(vbModeless)로 띄우라는 뜻입니다. 이걸 빼면 폼이 뜬 채로 코드가 멈춰서 아래 루프가 돌지 않으니 꼭 붙여야 합니다.

처리 건수를 그대로 보여 주고 싶으면 maxValue 를 전체 건수로 둡니다.

Sub program1472_com()
    If Not pBar.Visible Then pBar.Show 0
    pBar.maxValue = 50000
    Dim i As Long
    For i = 1 To 50000
        If (i Mod 100) = 0 Then pBar.Text = i & "번째 작업을 진행중입니다...."
        pBar.Value = i
    Next
    Unload pBar
    MsgBox "완료!!"
End Sub

퍼센트로 보여 주고 싶으면 maxValue 를 100 으로 두고 현재값을 비율로 바꿔 넣습니다.

Sub program1472_com()
    If Not pBar.Visible Then pBar.Show 0
    pBar.maxValue = 100
    Dim i As Long
    For i = 1 To 50000
        If (i Mod 100) = 0 Then pBar.Text = i & "번째 작업을 진행중입니다...."
        pBar.Value = i / 50000 * 100
    Next
    Unload pBar
    MsgBox "완료!!"
End Sub

두 예제 모두 제목 글자는 100 건마다 한 번씩만 바꿉니다. 이것도 이유가 있습니다. 매 반복마다 Caption 을 바꾸고 다시 그리면 실제 작업보다 화면 그리는 데 시간을 더 쓰게 됩니다.

실제 매크로에 붙일 때

같은 이유로 pBar.Value = i 도 매번 부르기보다 If i Mod 100 = 0 안으로 넣어 두면 체감 속도가 꽤 달라집니다. 막대는 어차피 몇백 픽셀이라 매 건 갱신해도 눈으로 구분이 안 됩니다.

Application.ScreenUpdating = False 로 시트 갱신을 꺼 둔 매크로에서도 이 폼은 그대로 보입니다. 시트 화면은 멈춰 있고 진행 창만 움직이니 오히려 그 조합이 보기 좋습니다. 그리고 중간에 오류로 빠져나가면 폼이 남아 있을 수 있으니, 에러 처리 구간에도 Unload pBar 를 한 줄 넣어 두세요. maxValue 에 0 을 넣으면 workRun 에서 0 으로 나누기 오류가 나는 것도 기억해 둘 부분입니다.