MaxNErr is a function within ddGlobal and forms part of the Func_Numeric suite of macros. This macro was originally created as I tend to use controlled errors in my formulas. As such, since I want to be able to ignore an error and still do some basic mathematical instructions I needed to write a whole set of “NErr” (No Error”) macros.
If you want to calculate the maximum number out of 5 numbers, and one of the cells have an error in it, then the standard Excel function (Large, or Max) will get seriously confused and just return the first error it encounters.
See the below example for how MaxNErr works:
| Scenario 1 | Scenario 2 | Scenario 3 | |
| Value: A | 10 | 10 | 10 |
| Value: B | 20 | 20 | # N/A |
| Value: C | 30 | # N/A | 30 |
| Value: D | 40 | 40 | # NAME? |
| Value: E | 50 | 50 | 50 |
| Max(A:E) | 50 | # N/A | # N/A |
| Large(A:E,1) | 50 | # N/A | # N/A |
| MaxNErr(A:E) | 50 | 50 | 50 |
The formula for calculating the maximum value in the five rows is:
MaxNErr(B2:B6)
Below is the source code for the macro
Function MaxNErr(TheRange As Range) As Double 'The function return the maximum number in a range. 'Last updated by Denis Dell Dim i, j As Integer With TheRange For i = 1 To .Rows.Count For j = 1 To .Columns.Count If IsNumeric(.Cells(i, j).Value) Then If .Cells(i, j).Value > MaxNErr Then MaxNErr = .Cells(i, j).Value End If Next j Next i End With End Function
Macro is short, efficient and it works. I am sure it could be optimised more, but right now it does what it says on the tin.
