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

wordjam

New Member
Joined
Dec 2, 2010
Messages
6
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I don't think a function can return an array? Maybe something like this. HTH. Dave
Code:
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!
 
Last edited:
Upvote 0
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 (n)?

Thanks again and for the quick reply!

Jim
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
One way to make it easer to write would be to use Select Case

Code:
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top