PDA

View Full Version : ByRef doesn't pass values in dialog initialization



Sebastian H
10-22-2010, 07:06 PM
Using Excel 2003, create a new (empty) dialog, and enter the following:
Private Sub UserForm_Initialize()
Debug.Print "before:", Left, Top
SetDlgPos Left, Top
Debug.Print "after: ", Left, Top
End Sub
Add a new module with the following:
Sub SetDlgPos(ByRef x As Integer, ByRef y As Integer)
x = 30
y = 50
Debug.Print "SetDlgPos:", x, y
End Sub

Sub TryIt()
UserForm1.Show
End Sub
Run TryIt. Result:

before: 0 0
SetDlgPos: 30 50
after: 0 0
In other words, SetDlgPos doesn't pass the values to UserForm_Initialize. Why not?

Sebastian H
10-22-2010, 07:41 PM
Never mind, I understand it now and found a solution.

Sebastian H
10-23-2010, 01:57 AM
I was premature. I still haven't solved it.

I had thought that I could just pass the whole UserForm to the sub, which would change to the following:
Sub SetDlgPosByObject(ByRef uf As UserForm)
uf.Left = 31
uf.Top = 51
End Sub
However, for some weird reason, Left and Top are not properties of the UserForm object! I get a Run-time error '383', "Object doesn't support property or method." How can that be?! :bug:

Also, strangely, there seems to be no method to directly define the position. There is Move, but that only moves it. Since there is no way to access Left and Top, there's no way to position the dialog absolutely.

BTW, I am aware that it is possible to get a workaround by introducing two dummy variables, and then assigning Left and Top indirectly: Dim x_dummy As Integer
Dim y_dummy As Integer
SetDlgPos x_dummy, y_dummy
Left = x_dummy
Top = y_dummy However, it is really ugly and not satisfying when I have to do that in each of the UserForms.

Is there a way to set this thread back to "not solved"?

p45cal
10-23-2010, 03:30 AM
try:Private Sub UserForm_Initialize()
Dim left As Single, top As Single
Debug.Print "before:", left, top
SetDlgPos left, top
Debug.Print "after: ", left, top
Me.StartUpPosition = 0
Me.top = top
Me.left = left
End Sub
Sub SetDlgPos(ByRef x As Single, ByRef y As Single)
x = 30
y = 50
Debug.Print "SetDlgPos:", x, y
End Sub

It seems .StartUpPosition overrides .top and .left, so your other solution passing the userform might work with .StartUpPosition set to 0
but I'm using Excel 2007 and xl2003

p45cal
10-23-2010, 04:24 AM
so your other solution passing the userform might work with .StartUpPosition set to 0 You may have to change the top line of:

Sub SetDlgPosByObject(ByRef uf As UserForm)
uf.Left = 31
uf.Top = 51
End Sub

to:

Sub SetDlgPosByObject(ByRef uf)
or just:
Sub SetDlgPosByObject(uf)
perhaps because (and I'm not sure of my ground here) it should strictly be

Sub SetDlgPosByObject(ByRef uf As UserForm1)
uf.Left = 31
uf.Top = 51
End Sub(which worked here) A stab in the dark: is this because Userform1 is a class itself?
To make it work for more than one userform just miss out the As xxxx.
You'll still have to set .startupposition to Manual (that's 0) somewhere.

Sebastian H
10-23-2010, 11:21 AM
Thank you for providing not just one, but two solutions!
:bow::bow:

And thank you also for mentioning StartUpPosition; that has bitten me before! I wish they had implemented it the other way round, that changing a position overrides StartUpPosition, but we have to work with what we got.

More to the point, the way VBA implements the object model is counterintuitive to me. It just doesn't seem to make sense that Left and Top are not recognized until you override them. Normally, I would expect just the opposite. Similarly, why does UserForm refuse to recognize Left and Top, but the generic Object (which is what you get when you leave out the As .. part) accepts them? :dunno. Ah, well, I'm glad it's working somehow!

BTW, in the meantime, I implemented another workaround which has at least the advantage that I understand how it works. I changed the sub into two functions (one for Left and one for Top), which allows me to just assign the values like this;Left=GetDlgPosLeft()
Top= GetDlgPosTop()
The drawback is that there is some code my actual implementation of SetDlgPos that now gets called twice, but that is not too big a cost.

Jan Karel Pieterse
10-24-2010, 07:44 AM
(which worked here) A stab in the dark: is this because Userform1 is a class itself?

Correct.
So you can do things like (and I very much prefer using object variables to hold userform instances anyway):


Sub MoreThanOne()
Dim uf1 As UserForm1
Dim uf2 As UserForm1
Set uf1 = New UserForm1
Set uf2 = New UserForm1
uf1.Show vbModeLess
uf2.Show vbModeLess
End Sub

Sebastian H
11-03-2010, 11:06 PM
Well, yes. But part of the beauty of object oriented programming is inheritance. That doesn't just mean that the child (UserForm1) inherits such properties as Left and Top from the parent (UserForm), but also that you can access these properties when you pass an instance of the child to a parameter declared as the parent.

That's precisely what I expected, and what didn't happen. Instead, VBA simply forgets the inherited properties, which is a cheap way to cut corners, IMHO.

That said, I'm not overly disappointed; I still think VBA is amazing for a macro language. I just feel we should be honest about VBA's limitations here.

Jan Karel Pieterse
11-03-2010, 11:22 PM
If you look at Userform1 as being an instance of Userform, then it makes sense that changing a property of Userform does not affect Userform1, does it not?

Sebastian H
11-04-2010, 11:54 AM
To be honest, that doesn't make sense to me. I was under the impression that UserForm1 was not an instance, but a child class of UserForm. Or can it be both at the same time?

Maybe my confusion comes from learning OOP with TurboPascal, which doesn't allow such things as classes being the same as instances. That strictness has its advantage: There, you don't have to worry whether a property is defined in the parent or the child class; either way, when you change it in the instance, it stays changed.

Jan Karel Pieterse
11-05-2010, 03:51 AM
It probably is better to see it as a child class. But VBA does not have inheritance on forms, this was introduced with VB.NET.

Userforms in VBA do not really have classes and instances at the same time; if you call Userform1, VB creates an instance for you behind the scenes. But they behave unexpected from an OOP point of view. Even if you do an unload userform1 in your code, the control values seem to retained their values. Calling UserForm1.TextBox1.Value after the unload command returns a value previously entered in that textbox, whereas you'd expect an empty value returned from a new (auto-instantiated) instance of Userform1.
Because of this unexpected behaviour, I always advise to use object variables for forms and do the instantiation yourself.

mikerickson
11-05-2010, 07:50 AM
Userforms do have instances. I've heard them described as a ClassModule with a user interface attached.

Dim oneForm as Object
Dim twoForm as Object

MsgBox Userforms.Count

Set oneForm = UserForms.Add("Userform1")
Set twoForm = UserForms.Add("Userform1")

oneForm.Caption = "ONE"
twoForm.Caption = "TWO"

MsgBox Userforms.Count
oneForm.Show

MsgBox Userforms.Count
twoForm.Show

MsgBox Userforms.Count

mikerickson
11-05-2010, 07:55 AM
"Calling UserForm1.TextBox1.Value after the unload command returns a value previously entered in that textbox,"

The UnLoad command doesn't completely exicute until an End Sub line is reached. Try this with a uf with a ListBox and a TextBox. and a command button that hides (but does not unload) the UF.

Sub Test()

With UserForm1
.ListBox1 AddItem "a"
.ListBox1 AddItem "b"
.Show
End With

Unload Userform1

MsgBox Userform1.TextBox1.Text
MstBox Userform1.ListBox1.Value

End Sub

The textbox line will show what you entered in the textbox, but the ListBox line will error, due to the difference between how the (partially unloaded) userform handles TextBoxes and Listboxes before its completely unloaded after the End Sub.

Jan Karel Pieterse
11-05-2010, 08:06 AM
The UnLoad command doesn't completely exicute until an End Sub line is reached. Try this with a uf with a ListBox and a TextBox. and a command button that hides (but does not unload) the UF.

My thoughts exactly. And yet another good reason to use object variables to work with the forms.

mikerickson
11-05-2010, 10:21 AM
Perhaps its my use of VBA v.5 (on Excel 2004, to be upgraded to v.6 / Excel 2011 this weekend), but if I want a variable to be able to refer to more than one userform, Object is the only way that variable can be dimensioned. Similarly, if a function has an argument that expect a Userform, that argument has to be declared as Object. Perhaps its a VBA v.5 issue, but Object is the only option for those cases.

The discussion of whether VBA is "really" an OOL reminds me a bit of discussions of whether GoTo should ever be used outside of error code (IMO, there are appropriate uses of GoTo) The VBA/OOL discussion is more appropriate to the question "What is an OOL" than it is to the question "how should I use VBA to get results".

Sebastian H
11-05-2010, 12:13 PM
The discussion of whether VBA is "really" an OOL [does not help with] the question "how should I use VBA to get results". People are different. For me, the most helpful contribution in this thread was this:


But VBA does not have inheritance on forms [...]
Userforms in VBA do not really have classes and instances at the same time; if you call Userform1, VB creates an instance for you behind the scenes. But they behave unexpected from an OOP point of view. [...].

That clarified for me at least why VBA just doesn't behave as I expect. Concretely, it seems that explains why, as I wrote above, "Left and Top are not properties of the UserForm object". Does it also explain my initial question in this thread, why "ByRef doesn't pass values in dialog initialization"?