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

 

[NdNoviceHlp]

I don't think a function can return an array? Maybe something like this. HTH. Dave

Option Base 1
Sub CallFunction()
Dim Ar() As Variant
ReDim Ar(3)
Ar(1) = Sample(1, "down", 3)
Ar(2) = Sample(3, "down", 3)
Ar(3) = Sample(5, "none", 5)
MsgBox "Ar(1)= " & Ar(1) & " Ar(2)= " & Ar(2) & " Ar(3)= " & Ar(3)
End Sub
Function Sample(A As Integer, B As String, C As Integer) As Double
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

edit: That's my 1K post!

 

[wordjam]

Thanks, Dave!

So would I still use the Sample function to return the arrays that you mentioned? I am a little confused as to return the array without the function. I am trying to understand the function portion similar to the built in LINEST function, only at the 1 dimension or single vector level. Would I put in all of the possible arrays as Ar (1)..... Ar ?

Thanks again and for the quick reply!

Jim

 

[NdNoviceHlp]

The sample function doesn't retun an array. You pass it the arguments (A As Integer, B As String, C As Integer) and it returns a value "As Double". The sub CallFunction's purpose is to call the sample function and load the results into an array. Hope that is the same outcome that you were looking for. Dave

 

[wordjam]

Thanks, Dave!

Is there a way to have the function return values such as?:

Sample = 0.2, 0.1, 0.4, 0.2, 0.1

Where Ar(1) = Sample(1, "down", 3)

and

Sample = 0.1, 0.2, 0.3, 0.3, 0.1

Where Ar(2) = Sample(3, "down", 3)

and

Sample = 0.4,0.1,0.1,0,0.4

Where Ar(3) = Sample(5,"none", 5)

I may be looking at it completely wrong from a logic standpoint, but this is what I'm trying to accomplish.

Thanks,

Jim

 

[wordjam]

I think I figured this one out with a little help from an old VBA book by Walkenbach. The code is pretty simple, but it does take a long, long time to run, even with just a few variables:

Function Sample2(a, b, c)
Dim X As Variant
X = Array(0.2, 0.2, 0.2, 0.2, 0.2)
Dim Y As Variant
Y = Array(0.4, 0.3, 0.2, 0.1, 0)
Dim Z As Variant
Z = Array(0, 0.1, 0.2, 0.3, 0.4)
If a = 1 And b = 2 And c = 3 Then
Sample2 = X
ElseIf a = 3 And b = 2 And c = 1 Then
Sample2 = Y
ElseIf a = 2 And b = 2 And c = 2 Then
Sample2 = Z
End If
End Function

I plan on adding quite a few more variables and I'm wondering if this will take a lot longer. Any ideas or suggestions to make this more efficient would be appreciated.

Thanks!

Jim

 

[mikerickson]

One way to make it easer to write would be to use Select Case

Function Sample (a, b, c) As Variant

Select Case CStr(a) & "-" & CStr(b) & "-" & CStr(c)
    Case "1-2-3"
        sample = Array(0.2, 0.2, 0.2, 0.2, 0.2)
    Case "3-2-1"
        Sample = Array(0.4, 0.3, 0.2, 0.1, 0)
    Case "2-2-2"
        Sample = Array(0, 0.1, 0.2, 0.3, 0.4)
    Case Else
        Sample = CVErr(xlErrNA)
End Select

End Function

 

[wordjam]

Thanks, Mike! I'll try that.

Will this also make it run a little quicker?

Jim

 

[mikerickson]

I can't see a significant difference in speed.
How many cases would you need?

In the
If..Then
If...The
If...Then

construct all the cases are tested

In
Select Case
Case one
Case two
Case three
End Select

only those cases before the first true are tested.

 

[wordjam]

Thanks, Mike!

I'm still figuring out how many cases I'll need. The array possibilities are not that many, but the cases are (looks like more than 100).

Jim