Option Explicit
Function CompareArrays(r1 As Range, r2 As Range) As Variant
Dim v1 As Variant, v2 As Variant, v3() As Variant
Dim r As Long, c As Long
'set default return value in case of exit
CompareArrays = CVErr(xlErrNA)
'put each range into VBA array for speed
v1 = r1.Value
v2 = r2.Value
'make sure both arrays are the same size, exit if not (xlErrNA)
If LBound(v1, 1) <> LBound(v2, 1) Then Exit Function
If LBound(v1, 2) <> LBound(v2, 2) Then Exit Function
If UBound(v1, 1) <> UBound(v2, 1) Then Exit Function
If UBound(v1, 2) <> UBound(v2, 2) Then Exit Function
'create array for output same size as the inputs
ReDim v3(LBound(v1, 1) To UBound(v1, 1), LBound(v1, 2) To UBound(v1, 2))
'across all cols in row 1 to add years to output array
For c = LBound(v1, 2) To UBound(v1, 2)
v3(1, c) = v1(1, c)
Next c
'down all rows in col 1 to add names to output array
For r = LBound(v1, 1) To UBound(v1, 1)
v3(r, 1) = v1(r, 1)
Next r
'across non-name columns and down non-year rows to calculate abs(difference) and put in putput array
For r = LBound(v1, 1) + 1 To UBound(v1, 1)
For c = LBound(v1, 2) + 1 To UBound(v1, 2)
v3(r, c) = Abs(v1(r, c) - v2(r, c))
Next c
Next r
'return output array (with names, years, and differences) to worksheet as normal Excel worksheet functions
'!!!! must be array-entered and should be same size as input arrays
CompareArrays = v3
End Function