Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Optional" output of a Public Type record
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 -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Optional" output of a Public Type record
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" wrote in message ... 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 -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Optional" output of a Public Type record
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 Z "Peter T" wrote: 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" wrote in message ... 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 -- Neal Z . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Optional" output of a Public Type record
Sorry Peter, I keep forgetting to answer your question 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 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 -- Neal Z "Peter T" wrote: 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" wrote in message ... 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 -- Neal Z . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Optional" output of a Public Type record
u
"Neal Zimm" wrote in message ... 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 Z "Peter T" wrote: 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" wrote in message ... 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 -- Neal Z . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Optional" output of a Public Type record
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Optional" output of a Public Type record
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 "Neal Zimm" wrote in message ... Sorry Peter, I keep forgetting to answer your question 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 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 -- Neal Z "Peter T" wrote: 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" wrote in message ... 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 -- Neal Z . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bunch of "yes" or "no" entered in row, output 1 if a single yes | Excel Worksheet Functions | |||
Where is the toolbar with the "bold type", "font type", options | New Users to Excel | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |