UDF with two ranges as arguments

[Chad M]

Thanks in advance or any help, VBA gurus.

The following function accepts two dynamic ranges as arguments, finds the percentage difference between each element in the "original" range and it's corresponding element in the "correct" range, and returns the maximum difference.

Function MaxRate(original As Range, correct As Range) As Double
Dim N As Long, i As Long
Dim h() As Double

N = Application.WorksheetFunction.Count(original)
ReDim h(1 To N)
For i = 1 To N
h(i) = (correct(i) - original(i)) / original(i)
Next i

MaxRate = Application.WorksheetFunction.Max(h)

End Function

When either range contains an NA value I'm getting a type-mismatch error. I'd like the function to include an error-checking statement such as ISNUMBER that allows it to simply skip elements that contain "NA", but the function either doesn't work at all when I try to include one or doesn't do what I want it to. I'm a noob at this and any help at all would be greatly appreciated.

 

[mikerickson]

I think the VBA you are looking for is If IsError(Correct(i)) or IsError(original(i)) Then

But wouldn't a CSE formula like =MAX((A1:A10-B1:B10)/B1:B10) work?

Also, do you want to use ABS so that a large, but negative, change isn't missed?

 

[RoryA]

Something like:

Function MaxRate(original As Range, correct As Range) As Double
Dim N As Long, i As Long
Dim h() As Double

N = Application.WorksheetFunction.Count(original)
ReDim h(1 To N)
For i = 1 To N
If Not IsError(correct(i)) and Not IsError(original(i)) Then
h(i) = (correct(i) - original(i)) / original(i)
End If
Next i

MaxRate = Application.WorksheetFunction.Max(h)

End Function

 

[Chad M]

Mike - a cse works great but I'm looking for something I can distribute to some coworkers who aren't too excel savvy. My end goal is to get this UDF to do exactly what {=MAX(IF(ISNUMBER((A5:A25-B5:B25)/B5:B25),((A5:A25-B5:B25)/B5:B25),""))} does.

Rory - that's what I thought too but yours ends up getting the same results I've gotten...it will not skip the NA values. It's in the way I've written the function I think. If I sort the "original" range so that the NA values are moved to the bottom, the error doesn't occur and the function does what I want it to. But there must be a way to to write this so as not to have to sort the range...?

 

[mikerickson]

Try original.Item(i) rather than original(i). Similarly correct

 

[RoryA]

Count will ignore errors, so you won't process all the items. Is the error definitely an error value, not text? I'd change the worksheetfunction.Count to Original.Count and add a test for isnumeric on both values.

 

[Chad M]

Ahha! The following code works brilliantly:

Function MaxRate(original As Range, correct As Range) As Double
Dim N As Long, i As Long
Dim h() As Double

N = Application.WorksheetFunction.Count(original)
ReDim h(1 To N)
For i = 1 To N
If IsNumeric(original.Item(i)) And IsNumeric(correct.Item(i)) Then
h(i) = (correct.Item(i) - original.Item(i)) / original.Item(i)
End If
Next i

MaxRate = Application.WorksheetFunction.Max(h)

End Function

Thanks for your help guys!