Not sure if its possible, and I can't find a specific question that shows how to do this. I am basically looking to write a function that would work like the SUM (or any number of functions in Excel). The user can call the function from the spreadsheet and select a variably sized, one dimensional set of numbers from the SS as an argument. For illustrative purposes I've tried writing the following test function to learn how to do this:
Function Test(tester() As Double) As Double
MsgBox "array position 1 = " + tester(1) Test = tester(1) End Function
This is just one manifestation of the things I've tried to get this to work, but anytime I choose data from the SS as an argument to my function, it makes the cell go "#Value". I'm thinking I need to do something clever with ranges but am very new to this. It is worth noting that I can pass arrays around function behind the scenes in code, but can't master allowing the user to select the data from the SS. Any help is appreciated!
Try something like this
Public Function TestFunc(TestRange As Range) As Variant
Dim vArray As Variant
vArray = TestRange.Value2
If IsArray(vArray) Then
TestFunc = vArray(1, 1)
Else
TestFunc = vArray
End If
End Function