Progress bar using VBA

Question

I'm working on excel sheet using VBA: I'm trying to test a progress bar. I already completed the design, as shown below:

Below the userForm code:

'PLACE IN YOUR USERFORM CODE
Private Sub UserForm_Initialize()
#If IsMac = False Then
    'hide the title bar if you're working on a windows machine. Otherwise, just display it as you normally would
    Me.Height = Me.Height - 10
    HideTitleBar.HideTitleBar Me
#End If
End Sub

Below the Module code:

'PLACE IN A STANDARD MODULE
Sub LoopThroughRows()
Dim i As Long, lastrow As Long
Dim pctdone As Single
lastrow = Range("A" & Rows.Count).End(xlUp).Row

'(Step 1) Display your Progress Bar
ufProgress.LabelProgress.Width = 0
ufProgress.Show
For i = 1 To lastrow
'(Step 2) Periodically update progress bar
    pctdone = i / lastrow
    With ufProgress
        .LabelCaption.Caption = "Processing Row " & i & " of " & lastrow
        .LabelProgress.Width = pctdone * (.FrameProgress.Width)
    End With
    DoEvents
        '--------------------------------------
        'the rest of your macro goes below here
        '
        '
        '--------------------------------------
'(Step 3) Close the progress bar when you're done
    If i = lastrow Then Unload ufProgress
Next i
End Sub

When I run the code, I get this error:

And when I press on Debug, it highlights on this:

ufProgress.LabelProgress.Width = 0

More information

UserForm name is (ufProgress) , The label that in the upper left of UserForm that will be used to display the text indicating the status name (LabelCaption) ... And the frame on UserForm name (FrameProgress ) .. Finlay , the another label, that going to grow progresses indicator name (LabelProgress) ..

Any advice ...

Kind Regards

Answer 1

A userform in VBA is an object, and you need to create an instance of an object before you can use it. But this fact is well hidden by the behavior of VBA to create a default instance of an userform if you access it via it's class name (form name).

Consider you have a form named Form1 and you write Form1.Show: VBA would create an instance of the form and display it.

The alternative is to declare a variable of type of the form, create it manually and then work with that variable:

Dim ufProgress as Form1
set ufProgress = new Form1
ufProgress.Show

It is usually better to do the last, but in you special case, you could go ahead with the default instance - as the form is just used for displaying (if you enter data into a user form and want to do something with it, using the default instance can lead to some unexpected problems).

---

Still considering that you form is named "Form1", you could solve the problem by

a) rename the form to ufProgress
b) replace ufProgress with Form1 in your code
(in both cases, you would access the automatically generated instance of the form)
c) Use the code snippet above.

---

Update - after having more information:
The line that is diplayed in the debugger is misleading, the guilty statement is (most likely) within the UserForm_Initialize-code of the form, but the debugger doesn't stop there until you tell it do so: In VBA editor, open the Option Window (Tools->Options), choose the General Tab and select "Break in Class modules". I would guess that the command HideTitleBar.HideTitleBar Me throws that error. Either remove that line (and live with the fact that the form shows its title) or find the definition for HideTitleBar.