How do you pass a virtual range from a LET into vba?

[BEDE]

I don't know how to explain this well so please bare with me.

Say you have a function(a, b, c, d, e)

if I do dummy(a1#,b1#,c1#,d1#,e1#) Everything works.

If I do Let(
one,index(xx,match(xx,xx)),
two,index(xx,match(xx,xx)),
three,index(xx,match(xx,xx)),
four,index(xx,match(xx,xx)),
five,index(xx,match(xx,xx)),
dummy(one,two,three,four,five))

It doesn't work.

On the VBA side:

Dim results() As Variant

maxrows= a.rows.Count
ReDim results(1 To maxRows)

For i = 1 To maxRows

say:
doo(a.cells(i),b.cells(i),c.cells(i),d.cells(i),e.cells(i))

 

[BEDE]

If you have a bunch of parameters that will all be treated the same way, or an indeterminate number of parameters, you can use PARAMARRAY. So you can loop through each parameter in PARAMARRAY, and process each parameter in sequence. The TEXTJOINX UDF, which you can find through the link above, will show you how to do that. Or you can Google PARAMARRAY and get several other explanations and examples. I only used PARAMARRAY in the TEXTJOINX macro because I was trying to duplicate TEXTJOIN for earlier versions of Excel that didn't have it.

However, using PARAMARRAY is really only when you have an indeterminate number of parameters, all treated the same way. Like concatenating lots of strings into a single string, or summing up or averaging several numbers. Most of the functions like this already exist as Excel functions. Usually when you write your own function, you know exactly what parameters you need, you won't need to allow for variable numbers of parameters. And in this case, you can specifically type those parameters, like

Public Function MyNeatTool(rng1 as Range, rng2 as Range, MyCode as Long, MyTitle as String)

This way Excel can validate the number and types of parameters for you, and you don't need to code for it. It also makes the code easier to follow. Unless you have a very specific need to accept untyped parameters, I suggest requiring the parameters to be passed in a specific, predefined fashion.

What are your thoughts on:

ReDim SValue(1 To maxRows)
Dim SValue() As Variant,
Call PopulateArray(S, SValue)

Sub PopulateArray(inputData As Variant, outputArray() As Variant)
    Dim i As Long
    Dim A As Variant
    If TypeOf inputData Is Range Then
        i = 1
        For Each A In inputData.Cells
            outputArray(i) = A.Value
            i = i + 1
        Next A
    ElseIf IsArray(inputData) Then
        For i = LBound(inputData) To UBound(inputData)
            outputArray(i - LBound(inputData) + 1) = inputData(i)
        Next i
    Else
        outputArray(1) = inputData
    End If
End Sub

Though I can't get it to work in the actual function. I'm still trying to figure this out.

 

[BEDE]

Updated

Sub PopulateArray(inputData As Variant, outputArray() As Variant)
    Dim i As Long
    Dim A As Variant ' Declare A here
    If TypeOf inputData Is Range Then
        i = 1
        For Each A In inputData.Cells
            outputArray(i) = A.Value
            i = i + 1
        Next A
    ElseIf IsArray(inputData) Then
        i = 1
        For Each A In inputData
            outputArray(i - LBound(inputData) + 1) = inputData
            i = i + 1
        Next A
    Else
        outputArray(1) = inputData
    End If
End Sub

 

[BEDE]

This one works but when I pass the output variables to an actual function it fails for some reason and I can't figure out why.

Sub PopulateArray(inputData As Variant, outputArray() As Variant)
    Dim i As Long
    Dim A As Variant ' Declare A here
    If TypeOf inputData Is Range Then
        i = 1
        For Each A In inputData.Cells
            outputArray(i) = A.Value
            i = i + 1
        Next A
    ElseIf IsArray(inputData) Then
        i = 1
        For Each A In inputData
            outputArray(i - LBound(inputData) + 1) = A
            i = i + 1
        Next A
    Else
        outputArray(1) = inputData
    End If
End Sub

 

[BEDE]

This one works but when I pass the output variables to an actual function it fails for some reason and I can't figure out why.

Sub PopulateArray(inputData As Variant, outputArray() As Variant)
    Dim i As Long
    Dim A As Variant ' Declare A here
    If TypeOf inputData Is Range Then
        i = 1
        For Each A In inputData.Cells
            outputArray(i) = A.Value
            i = i + 1
        Next A
    ElseIf IsArray(inputData) Then
        i = 1
        For Each A In inputData
            outputArray(i - LBound(inputData) + 1) = A
            i = i + 1
        Next A
    Else
        outputArray(1) = inputData
    End If
End Sub

Okay so I figured out the issue. In order to pass the output variables to an actual function I needed to wrap them in parenthesis.

example:
Call PopulateArray(psi, psiValue)

in my case:
(psiValue(i))