Determining which values are returned from user defined function

[finturn]

Hello,

I have a function that returns an array, however I don't want/need all of the outputs all of the time. Is there a way for my inputs to determine which values are outputted?

Example:

Function car(person as String)

Dim info(3) as variant

state = 0: county = 1: make = 2: model = 3

Select Case person

Case Sara[INDENT]info(state) = "KS"
info(county) = "MI"
info(make) = "Chevy"
info(model) = "Tahoe"[/INDENT]
Case Steve[INDENT]info(state) = "KS"
info(county) = "DG"
info(make) = "Ford"
info(model) = "Excursion"[/INDENT]
End Select

car = info

End Function

But let's say I only need Sara's state and county, is there a way to limit the outputs to just those two?

i.e.

Function car(A1, 1, 1, 0, 0)

where

Function car(person as String, state as integer, county as integer, make as integer, model as integer)

but only the parameters inputted with a 1 are returned (A1 is the cell reference that hold the persons name)?

Thank you!

 

[Andrew Poulsom]

Let's have an example that actually works please.

 

[finturn]

Function BaseWind(angle As Integer)
 
    Dim PB(5, 1) As Variant  'PB(row, column)
    SubLat = 0:  SubLong = 1:  SuperLat = 2:  SuperLong = 3:  LiveNormal = 4:  LiveParallel = 5
    
    Select Case angle
        Case 0
            PB(SubLat, 0) = 0.075
            PB(SubLong, 0) = 0
            PB(SuperLat, 0) = 0.05
            PB(SuperLong, 0) = 0
            PB(LiveNormal, 0) = 0.1
            PB(LiveParallel, 0) = 0
        Case 15
            PB(SubLat, 0) = 0.07
            PB(SubLong, 0) = 0.012
            PB(SuperLat, 0) = 0.044
            PB(SuperLong, 0) = 0.006
            PB(LiveNormal, 0) = 0.088
            PB(LiveParallel, 0) = 0.012
        Case 30
            PB(SubLat, 0) = 0.065
            PB(SubLong, 0) = 0.028
            PB(SuperLat, 0) = 0.041
            PB(SuperLong, 0) = 0.012
            PB(LiveNormal, 0) = 0.082
            PB(LiveParallel, 0) = 0.024
        Case 45
            PB(SubLat, 0) = 0.047
            PB(SubLong, 0) = 0.041
            PB(SuperLat, 0) = 0.033
            PB(SuperLong, 0) = 0.016
            PB(LiveNormal, 0) = 0.066
            PB(LiveParallel, 0) = 0.032
        Case 60
            PB(SubLat, 0) = 0.024
            PB(SubLong, 0) = 0.05
            PB(SuperLat, 0) = 0.017
            PB(SuperLong, 0) = 0.019
            PB(LiveNormal, 0) = 0.034
            PB(LiveParallel, 0) = 0.038
        End Select
        
        BaseWind = PB
            
End Function

 

[finturn]

I want to be able to specify which of the variables are returned for each angle, i.e. I may only want SubLat and SubLong returned or SubLong and LiveNormal

 

[Andrew Poulsom]

Why can't you use a lookup table? With your existing function you could use this to return LiveNormal for an angle of 30:

=INDEX(BaseWind(30),5)

 

[finturn]

These are Table values from a design code manual and I'm trying to store them in a function so that they can be easily updated as the code changes. I have over 100 files referencing these numbers that need to be updated with changes as well. I was thinking this would be a way to store them all in one place where I can pull what I need. I've tried storing info in other external files and referencing that but have ran into many problems.

 

[Andrew Poulsom]

So did my formula work for you?

 

[Rick Rothstein]

These are Table values from a design code manual and I'm trying to store them in a function so that they can be easily updated as the code changes. I have over 100 files referencing these numbers that need to be updated with changes as well. I was thinking this would be a way to store them all in one place where I can pull what I need. I've tried storing info in other external files and referencing that but have ran into many problems.

EDIT NOTE 1: The below response was based on my assumption you were using your function in VBA code only, but in rereading Andrew's responses, I see where maybe you are using this as a UDF (user defined function). If so, then my response would not be applicable.

EDIT NOTE 2: And, of course, if I had simply reread your thread's title, then I would have know exactly what you wanted. Bottom line... use Andrew's INDEX formula on the worksheet.


Since your BaseWind function returns an array (not sure why you have it returning a 2-dimensional array when the second element appears to always be 0 though), you can retrieve whatever element you want directly from that returned array. For example, if your angle is 45 degrees and you wanted the LiveNormal (whose array index is 4), the you can retrieve its value like this...

MsgBox BaseWind(45)(4, 0)

That comma-zero is there because you you have BaseWind() returning a two-dimension array where the second element appears to always be zero. The first set of parentheses hold the function's argument, the second set of parentheses holds the returned arrays index value(s).

 

[finturn]

Andrew: The INDIRECT function worked! Thank you very much!