# Blog

### Create custom Excel function using an array/range input

"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```