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

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

Code:
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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
Top