Passing Userform as an argument to a procedure

[Chip Pearson]

Howard,

Declare the parameter in the called function As Object or As FormName. E.g.,

Sub AAA()
BBB UserForm1
UserForm1.Show
End Sub

Sub BBB(UF As UserForm1)
UF.Caption = "this is new caption"
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Howard Kaikow]

In order to access the Caption of a Userform, I am passing the Userform as
an argument to a procedure.

If I pass the Userform as an Object, then the code works as intended. If I
pass the Userform as an MSForms.Userform, the code does not work properly.
I've seen the same behavior in both Excel and Word (Office 97, 2000, and
2002, not yet tested in Office 2003).

In one case, the caption is treated as an empty string.

In the other case, the caption is displayed in the "body" of the Userform
rather than in the title bar.


See Passing Userform as an argument to a procedure (Excel and Word) at
http://www.standards.com/OhMyWord/VBABugsFeatures.html

 

[keepitcool]

You yell BUG, but's it's your lack of understanding


Public Sub ResetCaption(Optional frmCurrent As _
frmSetUserformCaption = Nothing)

'OK Public Sub ResetCaption(Optional frmCurrent As _
Object = Nothing)
'ERR Public Sub ResetCaption(Optional frmCurrent As _
MSForms.UserForm = Nothing)

If frmCurrent Is Nothing Then
frmSetUserformCaption.Caption = "Feel a fool?"
Else
frmCurrent.Caption = "keepITcool!!"
End If
End Sub

cheerz!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepitcool]

Checked your AreasBug.xls too...

the problem you describe is caused by passing an
unqualified reference to the udf you created

would you declare it a range => no problem.
now that you decalre it a variant the activesheet is used
rather then the caller's sheet

You can solve THAT by either changing the calling function To:
k3 to =processvariant((Arrays!$A$1:$D$2, Arrays!$A$3:$D$3))

Or change your function to work byVal rather then byref
Function ProcessVariant(Optional ByVal vntArray as Variant = Nothing)

Bugs? not!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Howard Kaikow]

Object has the disadvantage of late binding.

Using the actual userform name ties the code to a single Userform.
In this case, that may work, but is rather inflexible.

So I guess that I'll stick with As Object

VB does not appear to have this problem, so maybe VB .NET will also be OK in
this area.
--
http://www.standards.com/; See Howard Kaikow's web site.

Howard,

Declare the parameter in the called function As Object or As FormName. E.g.,

Sub AAA()
BBB UserForm1
UserForm1.Show
End Sub

Sub BBB(UF As UserForm1)
UF.Caption = "this is new caption"
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[keepitcool]

Make your life a bit easier...
using object variables for your forms


'---------
'ModSet
'unused


'--------
'ModStart code
Option Explicit

Public form(0 To 2) As frmSetUserformCaption

Public Sub StartHere()
Set form(1) = New frmSetUserformCaption
Set form(2) = New frmSetUserformCaption
With form(1)
.StartUpPosition = 0: .Top = 200: .Left = 0
.Caption = "keepITsimple"
End With
With form(2)
.StartUpPosition = 0: .Top = 200: .Left = 200
.Caption = "keepITcool"
End With
Set form(0) = form(1)
form(1).Show vbModeless
End Sub



'-----------------
'FrmSet
Option Explicit

Private Sub btnFinish_Click()
Dim i%
Set form(0) = Nothing
For i = 1 To 2
Unload form(i)
Set form(i) = Nothing
Next
End Sub

Private Sub btnPushMe_Click()
'Çomparing objects => use IS not =
If form(0) Is form(1) Then
Set form(0) = form(2)
form(1).Hide
form(2).Show vbModeless
Else
Set form(0) = form(1)
form(2).Hide
form(1).Show vbModeless
End If
End Sub







keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Howard Kaikow]

Need to use AS Object for the example that's in the class.

Note that As MSForms.Userform seems to fail only for the Caption property.
Works for controls on the Userform.

--
http://www.standards.com/; See Howard Kaikow's web site.

Howard,

Declare the parameter in the called function As Object or As FormName. E.g.,

Sub AAA()
BBB UserForm1
UserForm1.Show
End Sub

Sub BBB(UF As UserForm1)
UF.Caption = "this is new caption"
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[keepitcool]

ouch...
you're right. i'm wrong.

big mouthed impulsive me..
other suggestion re Areas s*cks too.
the forms stuff is better (I hope)

deeply humbled

keepITcool

 

[Howard Kaikow]

Changing the arg to use ByVal does not change the result.
I have not looked at your other suggestions.

Take a look at http://www.decisionmodels.com/downloads.htm#AreasBugBypass.

 

[Howard Kaikow]

We ALL make mistakes.

Perhaps, MSFT includes such bugs just so we can have the fun of finding
them.

I expect to receive Office 2003 tomorrow.
Tonight, I'll dream that the Areas bug has been fixed.
But I'll likely wake up to reality.

 

[keepitcool]

Howard,


Humbled.. not not defeated

The BugByPass is one UGLY bastard. AND not needed when you dont pass named
ranges. There'a much simpler way around it.


Note the extra () around the argument in K2 and K3
If you try that with SUM function...
This will work:=SUM( Arrays!A1,RefSheet!A1 )
This will fail:=SUM((Arrays!A1,RefSheet!A1))

it'll warn you of illegal use, cause you use union before passing.
it to the sum formula

You should make your UDF use ParamArray (as Sum does internally)
Then all examples compute correctly with the exception of ROW 7
where a MultiArea NamedRange is passed.

Private Function ProcessParam(ParamArray vntArray())
Dim dblSumFunction As Double
Dim vntItem As Variant
Dim vntArea As Range

For Each vntItem In vntArray
dblSumFunction = dblSumFunction + Application.Sum(vntItem)
Next

ProcessParam = dblSumFunction
End Function




< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepitcool]

the bug which i refused to call a bug
is still there in xl2003(b2)

but! have you seen my solution ?..
(this thread diff post)

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Howard Kaikow]

ParamArray, etc.cannot be used for the particular app

There was a long thread on this topic way back when.

Charles Williams and I took the discussion off line.

The proposed solution at
http://www.decisionmodels.com/downloads.htm#AreasBugBypass works, but is not
appropriate for the general case, due to the requirement for Application
volatile.

I did find some MSFT KB articles that described other instances of this bug,
but dag nab it, I don't recall where I saved those articles.