How on earth do I generate an arglist with Excel 97 / VBA ? The code below for an array formula is an example only, the wanted method #2 seems to be necessary to pass calculated x datapoints to LINEST, I am trying to automate a multiple regression _w/o_ pasting the auxiliary data to a worksheet range.
Function Arghh_list(myRange As Range, iWhich As Integer) as Variant
Dim arrDefault As Variant, arrHowTo As Variant, arrNotThis As Variant
Dim arglist As Variant
Select Case iWhich
Case 1
‘Easy, hardcoded
arrDefault = Array(1, 4, 9, 16)
Arghh_list = arrDefault
Case 2
‘How to generate arglist of form ‘arg1, arg2, arg3,…, argN’ ?
‘arglist = ????
arrHowTo = Array(arglist)
Arghh_list = arrHowTo
Case 3
‘Ordinary array, filled with the values of the passed range
arrNotThis = myRange.Value
Arghh_list = arrNotThis
Case Else
MsgBox “check syntax”
End Select
End Function
