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