"Optional" output of a Public Type record

[Neal Zimm]

Hi,
I have subs and functions that take in and update public type records.

No problem when: Sub Name(InRec As uTypaA, more args.....

Is there a way to optionally write a public type ?

VBA won't allow: Sub Name(Arg1, Arg2, optional uRec As uType = ???
I guess since there's no constant value to assign to the fields.

I get the late bound error msg when I tried this:

Sub Name(prior args....., optional uRec as variant = "")

Dim uWantRec As KnownType

uRec = uWantRec


I can guess VBA must know the Type to initialize the fields. Is it the same
thing by making the public type I want one element of a ParamArray ?

It seems a waste to put the "As KnownType" in the argument list, not
optionally, and then sometimes update the fields, and sometimes not.

Is that the best there is to optionally update and output a public type
record from a sub or function ?

Thanks,
Neal

 

[Peter T]

Short answer is no. But that said I don't understand what you are trying to
do, you suggest -

optional uRec As uType = ???

You can't assign a single value to an entire Type in any situation, maybe
try and explain the objective.

Regards,
Peter T

 

[Neal Zimm]

Thanks for the quick response Peter,

the phrase in my post: optional uRec As uType = ??? was ONLY

my attempt at illustrating what cannot be done since my understanding of

optional arguments is that they must be assigned a constant value, e.g.

Optional NumVar As Long = 0

The ??? was my way of trying to say this can't be done with a record.
Thanks again,

 

[Neal Zimm]

Sorry Peter, I keep forgetting to answer your question re: my objective.

Many of my sub's and function's of a "utility" type are written to do
more than "one" thing.

I was trying to communicate to the proc only the parms for the arguments
that are required for the proc to do its 'thing' and to have only those
arguments appear in the calling macro. (When debugging it helps me to quickly
see what's being asked of the proc being called.)

The original post was based on a boolean function that checks to see if a
row
is devoid of data. One flavor of a result is just a simple true false,

If bNullRowF(WrkSht, Row) then .... else ...... 'Note, all possible
arguments
'are not shown

In my App, if the worksheet is of a certain type (logic in the function
figures that out) and the row is NOT null, I want selected data coming back
from the function re: the not null cells. The selected data in this case is
master file type data contained in hidden columns and it's always in the same
columns, so in this case the call would look like:

If Not bNullRowF(WrkSht, Row, uRec) then
'process the uRec fields
else
'logic for a null row
end if

I was hoping there might be a way to not show, and not have to Dim
the uRec in procs calling bNullRowF that needed only a simple T/F result.

Best,
Neal

 

[Peter T]

u

Thanks for the quick response Peter,

the phrase in my post: optional uRec As uType = ??? was ONLY

my attempt at illustrating what cannot be done since my understanding of

optional arguments is that they must be assigned a constant value, e.g.

Optional NumVar As Long = 0

The ??? was my way of trying to say this can't be done with a record.
Thanks again,

 

[Peter T]

Re your follow-up question, which I seem unable to reply directly to:

If I follow, maybe workaround with something like this

Private Type uType
a As Long
b As String
End Type

Private muRec As uType
Private mbUseRec As Boolean

Sub Test1()
Dim n As Long, s As String
n = 123
s = "ABC"
MsgBox foo(n, s)
End Sub

Sub Test2()
Dim n As Long, s As String
Dim uRec As uType

With uRec
.a = 456
.b = "XYZ"
End With

muRec = uRec
mbUseRec = True

MsgBox foo(n, s)
mbUseRec = False ' < don't forget

End Sub

Function foo(n As Long, s As String) As String
If mbUseRec Then
With muRec
foo = .a & .b
End With
Else
foo = n & s
End If

End Function

FWIW I wouldn't do it like that myself!

Regards,
Peter T

 

[Peter T]

Just a test reply. Reason, I've tried to send a full reply several times
which has failed, guessing, something in my full reply is triggering a
filter

Peter T