Trouble using a UDF in an array formula.

wrterry · Aug 28, 2009

I have a custom UDF (user defined function) that I have been using for about 2 years with success. I am now trying to use it in an array formula and getting a #Value! error.

The UDF looks like this...

=MyUDF(A1,B1)

...where A1 is a string to be evaluated and B1 is a number. Basically, the function returns another number depending on the relationship of the two inputs. I have not had any issues in the past using it as shown above.

Now, I would like to use this function on a range of cells and sum the results. Here is what I have now...

{=SUM(MyUDF(A1,B1:B100))}

Does anyone know why this returns a #Value! error?

Scott Huish · Aug 28, 2009

Probably because your UDF is not returning an array.

What is your UDF supposed to do? Can you post your code?

Phox · Aug 28, 2009

In order to be able to use a UDF in an array formula, the UDF must be specifically designed to be capable of handling the input array and return an output array.

James_B · Aug 28, 2009

The above posters are right. Personally, I've gone off UDFs. It may be that I just don't understand them. But I find that they recalculate when I don't want them to and don't recalculate when I do want them to. Anyway, one way of getting a UDF to handle a range of inputs, for illustration purposes, would be

Code:

option explicit
public function fSum(r as range) as double
   dim rCell as range, dRes as double
   for each rCell in r
      if isnumeric(rCell.value) then dRes = dRes + rCell.value
   next rCell
   fSum = dRes
end function

wrterry · Aug 28, 2009

Here is my code for the UDF. Here are some sample inputs...

=Tierfactor("1-4",3) returns 1.00
=Tierfactor("2-4, 6, 7-9", 6) returns 1.00
=Tierfactor("2-4, 6, 7-9", 5) returns 0.00
=Tierfactor("2-4.5, 6, 7-9", 5) returns 0.50

Anyway... I'm not sure how much this helps the issue...but enjoy the code.

Code:

'This function interprets a string to see if the value is contained in the expression
'Returns a factor depending if partial or full value
'Inputs: Description = string being searched, Tier = number to test
'Code written by Rob Terry
Public Function TierFactor(Description As String, Tier As Integer) As Variant
Dim d() As String 'd = description substring array
Dim i As Integer 'i = substring #
Dim l As Variant, h As Variant 'l,h = low and high substring values

'First verify that both required fields are there
    If Description = "" Or IsNull(Tier) = True Then
        TierFactor = 0
        Exit Function
    End If

'Parse description into substrings and evaluate (assume comma delimiter)
    d = Split(Description, ",")
    For i = LBound(d) To UBound(d)
    'Check if string is one tier or multiple tiers
        If IsNumeric(d(i)) Then 'Numeric - Single tier
            l = Val(d(i))
        'Check if partial tier or full tier
            If l <> Int(l) Then 'Partial
                If Tier = Ceiling(l) Then
                    TierFactor = l - Int(l)
                    Exit For
                End If
            End If
            If Tier = l Then 'Full
                TierFactor = 1
                Exit For
            End If
        Else 'Non-numeric - Tier Range
            l = Val(Left(d(i), InStr(d(i), "-") - 1)) 'low
            h = Val(Mid(d(i), InStr(d(i), "-") + 1)) 'high
            If l <> Int(l) Then 'Partial
                If Tier = Ceiling(l) Then
                    TierFactor = l - Int(l)
                    Exit For
                End If
            End If
            If h <> Int(h) Then 'Partial
                If Tier = Ceiling(h) Then
                    TierFactor = h - Int(h)
                    Exit For
                End If
            End If
            If Tier >= l And Tier <= h Then 'Full
                TierFactor = 1
            End If
        End If
    Next i
End Function

'Recreates the ceiling function in excel - not a standard VBA function
Public Function Ceiling(ByVal x As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(x / Factor) - (x / Factor - Int(x / Factor) > 0)) * Factor
End Function

pgc01 · Aug 28, 2009

Hi

Your problem is that your function does not return an array, it returns a single value.

You can modify the code to get an efficient solution. I'll leave that to you.

I'll use the black box approach to return an array, will just loop through the values of the input and build and return the array with the values returned from Tierfactor.

I'll call this new function TierfactorA. It will have a first parameter just like Tierfactor but the second parameter will be a range, rTier, a vertical vector like A1:A100.

TierfactorA invokes Tierfactor for each value in rTier and stores the result in an array. At the end it returns an array that can be stored in the worksheet in a vertical array the same size of rTier or used in an array formula.

Try your formula:

{=SUM(TierfactorA(A1,B1:B100))}

adding this code to your module:

Code:

' Returns an array with answers from TierFactor for the values in rTier
' rTier is a vertical vector, like A1:A10
Function TierFactorA(Description As String, rTier As Range) As Variant
Dim arrOut As Variant
Dim l As Long, j As Long
 
l = rTier.Count
ReDim arrOut(1 To l)
For j = 1 To l
    arrOut(j) = TierFactor(Description, rTier(j))
Next j
TierFactorA = Application.Transpose(arrOut)
End Function

HTH

wrterry · Aug 31, 2009

OK. So I updated my code to read in/out arrays. Thank you for your advice. I have also verified that the new code returns the correct result. The code is posted below.
The problem is that my formula is still returning a #Value! error. Here is my formula:

=SUMPRODUCT(rTierFactor(A1,B1:B100),C1:C100)

...where column C are numbers. Basically, I need to multiply the rTierFactor result by column C and then sum.
FYI... the following formulas DO work:

=SUM(rTierFactor(A1,B1:B100))
{=SUM(rTierFactor(A1,B1:B100))}

Code:

'This function interprets a string to see if the value is contained in the expression
'Returns a factor depending if partial or full value
'Inputs: Description = string being searched, Tier = number to test
'Code written by Rob Terry
Public Function rTierFactor(Description As String, rTier As Range) As Variant
Dim arrOut() As Variant 'arrOut = temp array to hold results
Dim d() As String 'd = description substring array
Dim i As Integer 'i = substring #
Dim l As Variant, h As Variant 'l,h = low and high substring values


ReDim arrOut(rTier.Count)
For j = LBound(arrOut) To UBound(arrOut)
    'First verify that both required fields are there
        If Description = "" Or IsNull(rTier(j)) = True Then
            arrOut(j) = 0
            Exit Function
        End If
    
    'Parse description into substrings and evaluate (assume comma delimiter)
        d = Split(Description, ",")
        For i = LBound(d) To UBound(d)
        'Check if string is one tier or multiple tiers
            If IsNumeric(d(i)) Then 'Numeric - Single tier
                l = Val(d(i))
            'Check if partial tier or full tier
                If l <> Int(l) Then 'Partial
                    If rTier(j) = Ceiling(l) Then
                        arrOut(j) = l - Int(l)
                        Exit For
                    End If
                End If
                If rTier(j) = l Then 'Full
                    arrOut(j) = 1
                    Exit For
                End If
            Else 'Non-numeric - Tier Range
                l = Val(Left(d(i), InStr(d(i), "-") - 1)) 'low
                h = Val(Mid(d(i), InStr(d(i), "-") + 1)) 'high
                If l <> Int(l) Then 'Partial
                    If rTier(j) = Ceiling(l) Then
                        arrOut(j) = l - Int(l)
                        Exit For
                    End If
                End If
                If h <> Int(h) Then 'Partial
                    If rTier(j) = Ceiling(h) Then
                        arrOut(j) = h - Int(h)
                        Exit For
                    End If
                End If
                If rTier(j) >= l And rTier(j) <= h Then 'Full
                    arrOut(j) = 1
                End If
            End If
        Next i
    Next j
    rTierFactor = arrOut
End Function

'Recreates the ceiling function in excel - not a standard VBA function
Public Function Ceiling(ByVal x As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(x / Factor) - (x / Factor - Int(x / Factor) > 0)) * Factor
End Function

pgc01 · Aug 31, 2009

Hi

Please post some values so that we can do the same test. Not the 100 rows, for ex. A1:C10.

pgc01 · Aug 31, 2009

No need.

I just looked into your code and saw that you changed one thing in my code, I was returning a vertical vector and you are returning a horizontal vector. In the SumProduct() all arrays must have the same dimensions.

Since C1:C100 is a vertical vector, so must the array returned by rTierFactor() be one.

Replace

Code:

    rTierFactor = arrOut

with

Code:

    rTierFactor = Application.Transpose(arrOut)

wrterry · Aug 31, 2009

So here is some sample data. The answer to the sumproduct formula should be 1432611.5

Here is the value for cell A1

2, 5-6.5

Here is the data for B1:C25

1 18033
1 35286
1 56670
2 85565
2 123742
3 177213
3 242479
3 324991
3 432519
3 639501
4 140676
4 151363
4 163606
5 178635
5 185236
6 186074
6 172095
6 153081
6 122450
6 61427
7 72414
7 109492
7 146707
8 191566
8 237308

By the way, you can use the code I posted earlier in this post (the non-array UDF) to see how the function works row by row to help you understand the end result. For example: =TierFactor(A1,B1)

Trouble using a UDF in an array formula.

wrterry

New Member

Excel Facts

Scott Huish

MrExcel MVP

Phox

Well-known Member

James_B

Active Member

wrterry

New Member

pgc01

MrExcel MVP

wrterry

New Member

pgc01

MrExcel MVP

pgc01

MrExcel MVP

wrterry

New Member

Similar threads

Forum statistics

Share this page

Trouble using a UDF in an array formula.

New Member

Excel Facts

MrExcel MVP

Well-known Member

Active Member

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock