My new adage of

"You only know what you know how to google"
- Martin Childs, 2016

stood true for this little puzzler, the answer wasn't apparent from simply googling it! All I wanted was to do something to a range input in a custom Excel function, easy you'd think - not in this case. I toiled for a bit of time before conceding that I'd just have to work it out from first principles.

First up it is properly referred to as an array rather than a range - range refers just to the cell reference rather than the contents of the range. To be able to use inputted range you need to determine a variable as an array of some type and then read all the possible cells in the range into it:

Dim MyArray() as Variant
MyArray = InputRange

Now we are rocking, next up is referencing it - LBound & UBound are your friends here, used quite universally in a lot of programming languages - they find the outer extremities of your array and allow you to loop through all the values. The next bit also foxed my for a short time - I could only read in one row with

for i = LBound(MyAr1, 2) To UBound(MyAr1, 2)

But why was it it two - well to get a two dimensional array working you have to loop both the 1 and the 2. Now we just have to hook it all together and loop through all the references and read the values, in this example I just complete a simple sum with the function:

Function mySum(input1 As Range) As Variant
'
' www.childs.be, 2017-02-04
'
    Dim MyAr1() As Variant
    MyAr1 = input1
   
    For j = LBound(MyAr1, 1) To UBound(MyAr1, 1)
    For i = LBound(MyAr1, 2) To UBound(MyAr1, 2)
        If IsNumeric(input1(j,i)) = True Then
                N = N + input1(j, i)
        End If
    Next i
    Next j
   
    mySum = N

End Function