Trouble using a UDF in an array formula.

[wrterry]

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?

 

[pgc01]

I suggested changing one statement in my last post. Did you see it?

 

[wrterry]

I added the transpose, but that did not seem to make any difference.

 

[pgc01]

I just did this test, just took some seconds.

On a new worksheet loaded A1 and B1:C25 with the values you posted.

In a cell used:

=SUMPRODUCT(rTierFactor(A1,B1:B25),C1:C25)

Got the result 1432611.5 as you expected.

I had previously added the transpose as I suggested.

Can you repeat this test, maybe it will help debug?

 

[wrterry]

Ahh!! I am at a loss. I have the exact same thing as you and I am still getting the error.

Any suggestions? FYI.. I am running Excel 2007

My formula:

=SUMPRODUCT(rTierFactor(A1,B1:B25),C1:C25)

My 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 = Application.Transpose(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

 

[wrterry]

Do I need a specific reference turned on to use Application.Transpose?

 

[pgc01]

I'm sorry, I changed 1 thing automatically and forgot to post it, maybe that's it.

I saw the statement

ReDim arrOut(rTier.Count)

and since you were referring in arrOut to rTier with the same index I assumed you had posted at the beginning of the module

"Option Base 1"

to force arrays to start at 1 by default, which I added in my module.

Now I'm thinking that maybe it is an error, can you check it.

You must either have the Option satement at the beginning of the module or use

ReDim arrOut(1 to rTier.Count)

I'm posting the code I used although it's almost the same as yours:

'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
Dim j As Long
 
ReDim arrOut(1 To 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 = Application.Transpose(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

Remark: You should always add Option Explicit at the beginning of the module, the code didn't compile at first because you forgot to Dim j as Long.

 

[wrterry]

That did it! Thank you so much for all your time and quick replies. I will make sure to pay it forward.... (and next time I will try the code that is given to me first before making my own)

 

[pgc01]

I'm glad it's working ok.

Cheers