UDF that returns arrays from if, then, else...

[wordjam]

Hello,

I am new to this forum and I am new to Excel VBA. I apologize if this is redundant with another thread or post, but I have looked high and low via google and on this forum and have yet to found what I am looking for.

I started with a simple if, then, else function as follows:

Function Sample(A, B, C)

If A <= 2 And B = "down" And C = 3 Then
Sample = 0.025
ElseIf A = 3 And B = "down" And C = 3 Then
Sample = 0.1
.................
.................
ElseIf A = 5 And B = "none" And C = 5 Then
Sample = 0.6
End If
End Function

Now I would like to be able to return mutliple variables from these conditions in a 1 dimensional array that will always be bounded by say 5 or six columns. How can I get it to return multiple values such as Sample = 0.1, 0.2, 0.4, 0.2, 0.1? The sum of the array would always equal 1. I'm not sure if I should use Dim or ReDim and I don't know what the context should be.

Any help you can provided would be very much appreciated.

Thanks!

Jim

 

[mikerickson]

>100 cases. That's a lot to handle explicitly. Is there a logic between the values of A,B,C and the values returned?

Have you considered using a look up table on a worksheet to store the function data.
100 rows X 8 columns; a , b, c, sample(0), sample(1), ..., sample(4) ; might be easier to edit and maintain than 100 hard-coded VBA values.