how to "synchronize" VBProjects & VBA.Userforms?

[Lars Uffmann]

Hey everyone,

Excel is driving me wild again with stupid programming. I had the
following VBA code working just fine to create a UserForm on the fly:

'''''''''''''''''''''''''''''''''''
Sub createForm()

Dim hRootFrm As Object
Dim hOuterFrm As Object

Set hRootFrm = _
Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm)

hRootFrm.Properties("ShowModal") = False

Set hOuterFrm = VBA.UserForms.Add(hRootFrm.Name)

Set frmProgress = hOuterFrmProgress
End Sub
'''''''''''''''''''''''''''''''''''

This worked well until I discovered my error. I created some other
workbooks on the fly, and it complained when adding the
hRootFrmProgress.Name to the UserForms collection:
"object required"

The crucial point is the line before that, where I create the MSForm as
part of the "ActiveVBProject". If a new workbook is created, it gets
created with an empty VBProject - and that is the "ActiveVBProject" then
- creating an object in that scope will make it impossible for that
object to be added to the UserForms collection in the scope of the VBA
object.

If the code is - for example - part of myWorkbook, and I use
Set hRootFrm = _
myWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

then everything works fine. So apparently, the VBA object in the term
VBA.UserForms is a SCOPED expression - and it is scoped to a workbook.

Is it defined *anywhere* that the scope for the VBA object is always(!)
the same scope in which the code resides that is accessing the VBA
object? Because I have not found *any* possibility to specify which
scope's VBA object I want to access; yet there seem to be as many as
there are open workbooks (i.e. VBProjects).

Thanks & Best Regards,

Lars

 

[Peter T]

I don't follow the overall objective at all, so just one or two comments in
line

Excel is driving me wild again with stupid programming. I had the
following VBA code working just fine to create a UserForm on the fly:

'''''''''''''''''''''''''''''''''''
Sub createForm()

Dim hRootFrm As Object
Dim hOuterFrm As Object

Set hRootFrm = _
Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm)

hRootFrm.Properties("ShowModal") = False

Set hOuterFrm = VBA.UserForms.Add(hRootFrm.Name)

Set frmProgress = hOuterFrmProgress
End Sub
'''''''''''''''''''''''''''''''''''

This worked well until I discovered my error. I created some other
workbooks on the fly, and it complained when adding the
hRootFrmProgress.Name to the UserForms collection:
"object required"

The "UserForms collection" referrs to loaded forms in the current project
(that's calling the code). Even if you have 10 forms but none currently
loaded, Userforms.Count will be zero. The only way to add to this collection
is to "load" a form, eg Userform1.Show

No idea what you are trying to do with "hRootFrmProgress.Name", what's
hRootFrmProgress

The crucial point is the line before that, where I create the MSForm as
part of the "ActiveVBProject". If a new workbook is created, it gets
created with an empty VBProject - and that is the "ActiveVBProject" then -
creating an object in that scope will make it impossible for that object
to be added to the UserForms collection in the scope of the VBA object.

Are you getting confused with the ActiveWorkbook's project and the project
that's active in VBE. They are not necessarily the same. It's very rare you
would ever want to refer to "ActiveVBProject", at least probably not in the
context with what you are doing. Set your myBook as required, =
ActiveWorkbook, = ThisWorkbook, = Workbooks("Book1.xls) and
Set vbp = myBook.VBProject

If the code is - for example - part of myWorkbook, and I use
Set hRootFrm = _
myWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

then everything works fine. So apparently, the VBA object in the term
VBA.UserForms is a SCOPED expression - and it is scoped to a workbook.

It's an unusual way to put it, as I mentioned VBA.UserForms refers to the
loaded Userforms within the project it's called from (not necessarily all
userforms even if loaded but not all belonging to same project)

Is it defined *anywhere* that the scope for the VBA object is always(!)
the same scope in which the code resides that is accessing the VBA object?
Because I have not found *any* possibility to specify which scope's VBA
object I want to access; yet there seem to be as many as there are open
workbooks (i.e. VBProjects).

Trust that's already answered above

Generally it's best not to add code modules to the same project that's
running the code.

Regards,
Peter T

 

[Lars Uffmann]

The "UserForms collection" referrs to loaded forms in the current project
(that's calling the code). Even if you have 10 forms but none currently
loaded, Userforms.Count will be zero. The only way to add to this collection
is to "load" a form, eg Userform1.Show

That's what my code works around

No idea what you are trying to do with "hRootFrmProgress.Name", what's
hRootFrmProgress

Sorry - I made a mistake on editing the code for a minimal example. I
meant the object hRootFrm as returned by
VBComponents.Add (vbtext_ct_MSForm)

Anyways - the UserForms.Add (hRootFrm) *is* a way of loading a form -
try it If you dynamically create a form like I did, you can load it
that way. I spent quite some time finding that solution

Are you getting confused with the ActiveWorkbook's project and the project
that's active in VBE. They are not necessarily the same. It's very rare you
would ever want to refer to "ActiveVBProject", at least probably not in the
context with what you are doing.

Yes - I had those confused before I discovered my mistake. And basically
did what you suggested then.

It's an unusual way to put it, as I mentioned VBA.UserForms refers to the
loaded Userforms within the project it's called from

It refers to that, and a little more - by referring to a collection of
those UserForms, but also providing the Add method to load new
(dynamically created) forms.

Trust that's already answered above

Yes - thank you - do you happen to know where I can find a source
(documentation) on that? Some Microsoft site maybe?

Generally it's best not to add code modules to the same project that's
running the code.

Well - I don't see how I could add the form dynamically to a different
project. I mean - I could add it, but then I could not dynamically load
it because I cannot access the VBA project for another project, as
VBProject does not have a VBA property, sadly.

Thanks for your comments & Best Regards,

Lars

 

[Peter T]

It refers to that, and a little more - by referring to a collection of
those UserForms, but also providing the Add method to load new
(dynamically created) forms.

That's two separate things. First you dynamically created the form and
return is name, say sName

Second, you add it to the Userforms collection of "Loaded" forms using the
Userforms.Add(sName) method. But that's not directly relevant to the fact
you have just dynamically created it. If the form was created during design
time you could have loaded and show'n it the same way.

You could simply do
Userforms.Add(sName).Show

though it may well be better for later reasons to do it as you are and set a
reference to the Add'ed form

Well - I don't see how I could add the form dynamically to a different
project. I mean - I could add it, but then I could not dynamically load it
because I cannot access the VBA project for another project, as VBProject
does not have a VBA property, sadly.

I'm not saying it won't work, but look out for unexplained things happening
while adding controls and related event code to the form (like the code
re-compiling while running and causing a crash). Though adding code to a
form is probably somewhat safer than doing similar behind a Worksheet.

Regards,
Peter T