Is it possible to use a UDF in an array formula?

[PA HS Teacher]

I am making an NCAA tournament spreadsheet. I wrote a user defined function that returns a certain number of points based the round number, and the seed number it is fed. This function appears to work under normal circumstances,

For example:=GamePoints($B$73,1)
returns 12, as it should based on my rules and the value in B73

However, when I enter the following Array entered formula:
=SUM(GamePoints($B$71:$B$73,1))

I get the
#VALUE! error

Is it possible to use a UDF within an Array formula? I can change the UDF to loop through a range itself, but it would be kind of nice to use this UDF in an array entered formula. If it is possible, does anyone know what the problem may be?

If it helps the following is the code for my GamePoints UDF

Function GamePoints(iRound As Integer, iSeed As Integer) As Integer
Dim iRegularPoints As Integer, iMaxSeed As Integer, iSeedFactor As Integer
iRegularPoints = Application.WorksheetFunction.VLookup(iRound, Worksheets("Seeds").Range("F2:I8"), 2, 0)
iMaxSeed = Application.WorksheetFunction.VLookup(iRound, Worksheets("Seeds").Range("F2:I8"), 4, 0)
iSeedFactor = Application.WorksheetFunction.VLookup(iRound, Worksheets("Seeds").Range("F2:I8"), 3, 0)
If iMaxSeed <= iSeed Then
  GamePoints = iRegularPoints * iSeedFactor
Else
  GamePoints = iRegularPoints
End If
End Function

 

[Juan Pablo González]

You have to modify the UDF to handle the array, it doesn't happen "automatically". Look here for an example:

http://www.dicks-blog.com/archives/2005/02/03/parse-names-with-outlook-array-formula/

 

[Dutchy]

I asked a similar question a month or so back. I believe you need the UDF to return a range of results suitable for use in an array formula. If you don't explicitly set it up that way then only a single result will be returned and this won't work in an array.


http://www.mrexcel.com/board2/viewtopic.php?t=127819&highlight=

 

[PA HS Teacher]

Thank you for all of your help. This was a really good excercise for me. Took me a little while to realize I couldn't simply index a range like an array. So I had to loop through each cell in the range and put it in an array. I'm still so new at this, thank you for your help. Should anyone be interested here is the UDF I modeled after yours.

Function Points(Round As Variant, Seed As Variant) As Variant
Dim iRegularPoints As Integer, iMaxSeed As Integer, iSeedFactor As Integer
Dim GCount As Integer, Game As Variant, PointsArr() As Variant, RoundsArr() As Variant, SeedsArr() As Variant
Dim s As Variant, GCount2 As Integer
'On Error GoTo MyError
' Make sure the Two input arguments Round and Seed are Ranges
If TypeName(Round) = "Range" Then
      If Round.Count = 1 Then
         Round = Array(Round.Value)
      Else
         Round = Round
         Round = Application.Transpose(Round)
      End If
ElseIf Not IsArray(Round) Then
      Round = Array(Round)
End If

If TypeName(Seed) = "Range" Then
      If Seed.Count = 1 Then
         Seed = Array(Seed.Value)
      Else
         Seed = Seed
         Seed = Application.Transpose(Seed)
      End If
ElseIf Not IsArray(Seed) Then
      Seed = Array(Seed)
End If
' ************  Put the values from the Round and Seed ranges into Arrays, RoundsArr and SeedsArr **********
ReDim Preserve PointsArr(1 To UBound(Round) - LBound(Round) + 1)
ReDim Preserve RoundsArr(1 To UBound(Round) - LBound(Round) + 1)
ReDim Preserve SeedsArr(1 To UBound(Round) - LBound(Round) + 1)

For Each Game In Round
  GCount = GCount + 1
  RoundsArr(GCount) = Game
Next Game

For Each s In Seed
  GCount2 = GCount2 + 1
  SeedsArr(GCount2) = s
 ' MsgBox (SeedsArr(GCount))
Next s

For i = 1 To GCount     ' Calculate the points earned for that particular matchup and put it in an Array PointsArr
  iRegularPoints = Application.WorksheetFunction.VLookup(RoundsArr(i), Worksheets("Seeds").Range("F2:I8"), 2, 0)
  iMaxSeed = Application.WorksheetFunction.VLookup(RoundsArr(i), Worksheets("Seeds").Range("F2:I8"), 4, 0)
  iSeedFactor = Application.WorksheetFunction.VLookup(RoundsArr(i), Worksheets("Seeds").Range("F2:I8"), 3, 0)
  If iMaxSeed <= SeedsArr(i) Then
    PointsArr(i) = iRegularPoints * iSeedFactor
  Else
    PointsArr(i) = iRegularPoints
  End If
Next i
'Return the Array PointsArr, each entry in this array is the number of points earned for that particular game.
Points = PointsArr

End Function