This code crashes excel (unless the VBA editor is open)

[RawlinsCross]

Good day,

I have the following code that was crashing my excel when it executed. Oddly enough (and by accident), I discovered that when the VBA editor is open, the code runs fine. So I know how it's happening but not why it's happening.
Any thoughts to why this is happening and perhaps suggestions to replace the code with something more robust?

Dim UForm As Object

For Each UForm In VBA.UserForms
    If UForm.Name = "UserGUI" Then
        Exit Sub
    End If
Next UForm

 

[RawlinsCross]

RawlinsCross is an intersection famous in St. John's - where I'm from. John is my given name. But thanks for your help. I'm sure I'll figure it out. For now, I'll comment out the offending code. Just wondered if I wasn't catching something obvious.

 

[RawlinsCross]

Okay, so I'd like to keep this thread going as the problem is continuing even with code, I thought was offending, is commented out. So there's a command button on a tab that simply loads a userform. On this userform, there is A LOT of controls, like 390 checkboxes (I know, I know, I'm not the king of UX design perhaps). But anyhow, it's how it's designed for now. The funny thing is, is that this userform will load no problem if the VBA editor is open but crashes excel if not.

I'm only guessing, but is the editor loading the userform into some sort of memory so that the actually call to load it works?

 

[NdNoviceHlp]

That's a lot of controls. U were close. It probably wouldn't hurt to compile it, save it, close it and re-open it. HTH. Dave

Dim UForm As Object
For Each UForm In ThisWorkbook.VBProject.VBComponents 'VBA.UserForms
If vbc.Type = 3 Then 'vbext_ct_MSForm constant
    If UForm.Name = "UserGUI" Then
        Exit Sub
    End If
End If
Next UForm

 

[RawlinsCross]

Hey Nd,

Compiled it through the Debug menu, saved it, and closed it. Opened it again, and didn't open the VBEditor. Same crashing behavior.
I'm looking at options of opening the VB editor via code in the Workbook_Open event by using:

Private Sub Workbook_Open()

Application.VBE.MainWindow.Visible = True

End Sub

When the VB editor opens, the userform in question in not selected. How might I select this userform ("UserGUI") using VB code?

 

[mikerickson]

would be changing:

Dim UForm As Object

to this:

Dim UForm As userform

....

Userform is not a valid data type in VBA.
One could dimension a variable to match a particular userform's name

' Eg.

Dim aVar as UserForm1
Dim bVar as UserForm2

But if one is going to dimension a variable to take the value of many different user forms, it would have to be either dimensioned as Object or as Variant;.

 

[mikerickson]

The Userforms collection is a collection of all the current instances of userforms. (i.e. those that are loaded).
It is not a collection of the VB modules that could become userforms.

 

[NdNoviceHlp]

Did U trial the code I posted? It works without need for a library reference. Does the userform in question have any initialize or activate code? Dave

 

[RawlinsCross]

ND, I did but it did not like vbc - do I have to Dim this?

 

[RoryA]

I suspect vbc should actually read UForm in that code.

 

[NdNoviceHlp]

Well that's embarrassing. Yes it should be...

Dim UForm As Object
For Each UForm In ThisWorkbook.VBProject.VBComponents 'VBA.UserForms
If UForm.Type = 3 Then 'vbext_ct_MSForm constant
    If UForm.Name = "UserGUI" Then
        Exit Sub
    End If
End If
Next UForm

My apologies and thanks for the correction Rory. Dave