UDF with two ranges as arguments

Chad M

New Member
Joined
Oct 21, 2011
Messages
3
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,748
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Something like:
Code:
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

New Member
Joined
Oct 21, 2011
Messages
3
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,748
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Oct 21, 2011
Messages
3
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

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