Function MySAdd(v As Range)
'пример функции массива. Добавляет 1 к каждому элементу
 Dim retArr
If v.Cells.Count >  1  Then
    Dim i As Long, j As Long
    'допустимый вызов с указанием диапазона только в качестве функции массива.
    'иначе возвратим N/A
    If CalledByArray Then
      ReDim retArr(v.Rows.Count -  1 , v.Columns.Count -  1 )   
    
      For i =  1  To v.Rows.Count
        For j =  1  To v.Columns.Count
            retArr(i -  1 , j -  1 ) = v(i, j).Value +  1 
        Next
      Next
      MySAdd = retArr
    Else
    'недопустимый вызов
      MySAdd = CVErr(xlErrNA)
    End If
Else
    MySAdd = v.Value +  1 
End If
End Function

Private Function CalledByArray() As Boolean
'первоисточник: http://www.tek-tips.com/viewthread.cfm?qid=1301663&page=6
'This function indicates whether the cell from which it was called was a single cell or
'part of an array (as in the use of array formulae).

If TypeName(Application.Caller) = "Range" Then
    CalledByArray = Application.Caller.Cells.Count >  1 
Else
    CalledByArray = False
End If
End Function