Excel does not have a built-in function to find the maximum absolute value of a range, perhaps because the Max() and Abs() functions can be combined in an array function:
This solution has a number of drawbacks however:
- The function must be entered as an array function, by pressing Ctrl-Shift-Enter, rather than just enter.
- If it is entered with just pressing the Enter key it displays the wrong value, rather than an error message.
- Even if it is entered correctly, if anyone presses F2 then enter it will revert to a normal function, and display the wrong result.
- It is not available from VBA.
For all these reasons I decided to write a MaxAbs function in VBA, that can be called either from the worksheet, or from another VBA routine. Here is the code, Version 1:
Function MaxAbs(Dataa As Variant) As Double Dim MaxVal As Double, Val As Variant If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2 For Each Val In Dataa If Abs(Val) > MaxVal Then MaxVal = Abs(Val) Next Val MaxAbs = MaxVal End Function
Having done that, I wondered if calling the built-in Max function might work better, particularly for big data ranges. My first effort was:
Function MaxAbs2(Dataa As Variant) As Double Dim MaxVal1 As Double, MaxVal2 As Double If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2 MaxVal1 = WorksheetFunction.Max(Dataa) MaxVal2 = -WorksheetFunction.Min(Dataa) If MaxVal1 > MaxVal2 Then MaxAbs2 = MaxVal1 Else MaxAbs2 = MaxVal2 End Function
This proved to be slower than the first version, even for very big data ranges, but working with ranges rather than variant arrays:
Function MaxAbsR(Dataa As Range) As Double Dim MaxVal1 As Double, MaxVal2 As Double MaxVal1 = WorksheetFunction.Max(Dataa) MaxVal2 = -WorksheetFunction.Min(Dataa) If MaxVal1 > MaxVal2 Then MaxAbsR = MaxVal1 Else MaxAbsR = MaxVal2 End Function
made the code faster than the original version for anything more than about 15 rows. The drawback with this version is that if you are working with double or variant arrays in VBA (which I usually am), these would need to be converted to range objects first, so I ended up with the two versions:
- MaxAbs() for use with arrays in VBA
- MaxAbsR() for use as a UDF on the spreadsheet, or on range objects in VBA
The first function can also be easily adapted to provide the minimum absolute value:
Function MinAbs(Dataa As Variant) As Double Dim MinVal As Double, Val As Variant If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2 MinVal = 1E+308 For Each Val In Dataa If Abs(Val) < MinVal Then MinVal = Abs(Val) Next Val MinAbs = MinVal End Function
Other than changing max to min, and > to <, the only difference is that MinVal is set to a very large number before starting the loop; otherwise it would always return zero.