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

 

[Eric W]

You don't show your entire VBA function. In particular, it would be useful to show how you define the incoming parameters. My suspicion is that you define them as a range, something like:

Function dummy(one As Range, two As Range)

End Function

The difference between the 2 ways that you're calling the function is that the first way, you're passing ranges, and the second way, you're passing arrays. They are not interchangeable. Even if you define the parameters as variant, once you pass them, they are defined by what you pass, a range or an array. The way of accessing them might be different. You could define your variables as variant (or leave them to default to variant), then check the type and handle them accordingly. Something like this:

Function test1(var1)
Dim x As Variant

    test1 = 0
    If TypeOf var1 Is Range Then
        For Each x In var1              ' Range
            test1 = test1 + x.Value
        Next x
    ElseIf IsArray(var1) Then           ' Array
        For Each x In var1
            test1 = test1 + x
        Next x
    Else
        test1 = test1 + var1            ' Constant
    End If
    
End Function

This works for arrays or ranges:

Book2
	A	B	C
1	1		15
2	2		15
3	3
4	4
5	5
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=SEQUENCE(5)
C1	C1	=test1(A1#)
C2	C2	=LET(one,A1#+0,test1(one))
Dynamic array formulas.

Some years ago I wrote a UDF version of TEXTJOIN that shows how to use the PARAMARRAY parameter and tests for the variable type as well. The UDF isn't really needed anymore, but it shows how to code the PARAMARRAY. See here:

ARRAY FORMULA (TEXTJOIN) periodically not updating or returning correct values

Hi, I am using a TEXTJOIN function in an ARRAY FORMULA to return multiple matches to one cell. However periodically the formula doesnt update and/or doesnt update correctly. See formula in the attached - cell reference - D63 If you play with the inputs in cells L37:L39 - you will see that...

www.mrexcel.com

Hopefully, you'll find something useful in this.

 

[BEDE]

You don't show your entire VBA function. In particular, it would be useful to show how you define the incoming parameters. My suspicion is that you define them as a range, something like:

Function dummy(one As Range, two As Range)

End Function

The difference between the 2 ways that you're calling the function is that the first way, you're passing ranges, and the second way, you're passing arrays. They are not interchangeable. Even if you define the parameters as variant, once you pass them, they are defined by what you pass, a range or an array. The way of accessing them might be different. You could define your variables as variant (or leave them to default to variant), then check the type and handle them accordingly. Something like this:

Function test1(var1)
Dim x As Variant

    test1 = 0
    If TypeOf var1 Is Range Then
        For Each x In var1              ' Range
            test1 = test1 + x.Value
        Next x
    ElseIf IsArray(var1) Then           ' Array
        For Each x In var1
            test1 = test1 + x
        Next x
    Else
        test1 = test1 + var1            ' Constant
    End If
   
End Function

This works for arrays or ranges:

Book2
	A	B	C
1	1		15
2	2		15
3	3
4	4
5	5
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=SEQUENCE(5)
C1	C1	=test1(A1#)
C2	C2	=LET(one,A1#+0,test1(one))
Dynamic array formulas.

Some years ago I wrote a UDF version of TEXTJOIN that shows how to use the PARAMARRAY parameter and tests for the variable type as well. The UDF isn't really needed anymore, but it shows how to code the PARAMARRAY. See here:

ARRAY FORMULA (TEXTJOIN) periodically not updating or returning correct values

Hi, I am using a TEXTJOIN function in an ARRAY FORMULA to return multiple matches to one cell. However periodically the formula doesnt update and/or doesnt update correctly. See formula in the attached - cell reference - D63 If you play with the inputs in cells L37:L39 - you will see that...

www.mrexcel.com

Hopefully, you'll find something useful in this.

Good Morning,

Thank you for shining the light but can you clarify a few questions?

So using your template and other forum post I have put this together.

        DIM ONE As Variant
        testone = ""
        For i = LBound(S) To UBound(S)
        If TypeOf S(i) Is Range Then
        For Each ONE In S(i).Cells              ' Range
            testone = testone + S.Value
        Next ONE
        ElseIf IsArray(S(i)) Then           ' Array
        For Each ONE In S(i)
            testone = testone + ONE
        Next ONE
        Else
        testone = testone + S(i)            ' Constant
        End If

Is this correct?

Also is there a way to incorporate multiple variables into that loop? for example:

Function dummy(S, var2, var3, var4, var5)

End Function

OR would I need to create a loop for each variable?

 

[BEDE]

Can't edit but one other question.

Can I create a stand alone function of the above that just loops through all these variables before I pass it off to the actual function? If yes what does that look like?

 

[BEDE]

AI saying it's not possible. IDK, I was playing with the code below and I can pass constants, ranges, and arrays {10,20,30} but not the above.

Public Function test1(S As Variant) As Variant

    Dim results() As Variant

    Dim i As Long

    Dim count As Long

    Dim poo As Variant

    Dim y As Variant

    
    ' Determine if the input is a range or an array

    If TypeOf S Is Range Then

        ' Handle dynamic range input (could be a single row/column range)

        count = S.Cells.count

    ElseIf IsArray(S) Then

        ' Handle array input

        count = UBound(S) - LBound(S) + 1

    Else

        count = 1 ' Single constant input

    End If

    

    ' Resize the results array

    ReDim results(1 To count)

    

    ' Process range input

    If TypeOf S Is Range Then

        i = 1

        For Each y In S.Cells

            poo = y.Value

            results(i) = poo ' Store the value in results

            i = i + 1

        Next y

    ' Process array input

    ElseIf IsArray(S) Then

        For i = LBound(S) To UBound(S)

            poo = S(i) ' Directly access the array value

            results(i - LBound(S) + 1) = poo ' Store the value in results (1-based index)

        Next i

    Else

        results(1) = S ' Handle constant input

    End If


    test1 = results ' Return the results array

End Function

 

[Eric W]

I'm not sure where to even start.

First, the code you came up with in post #3 has several issues with it. I don't know how to fix them, because you didn't show the entire function, and because I really don't know what you're trying to do.

Second, yes you can incorporate multiple variables in the macro. The TEXTJOINX macro shows how to do that, first you have a loop to look at the passed parameters and see what type they are, next each parameter is handled according to its type, which may be another loop. The TEXTJOINX macro assumes the parameters are strings, arrays of strings, or ranges of strings. But in your case, you seem to be looking at values. This is fine, it just depends on what your ultimate goal is.

Third, you may want to look into using Excel functions in your VBA code. The line

MySum = WorksheetFunction.Sum(Parm1)

will work regardless of whether the parameter is a range, array or constant. Or even:

MySum = WorksheetFunction.Sum(Parm1, Parm2, Parm3, Parm4, Parm5)

Depending on your requirements, and if there is an existing built-in function, you might be able to simplify things a lot.

Fourth, I'm not exactly sure what that AI statement is saying, but I have a suspicion. Using the same macro I provided in post #2, consider this:

Book2
	A	B	C	D	E	F											Q	R
1	1		15	25
2	2		15	25
3	3		15
4	4
5	5
6
7
8
9
10
11
12	A			1													A	1
13	B			2													C	3
14	C			3													E	5
15	D			4													G	7
16	E			5													I	9
17	F			6
18	G			7
19	H			8
20	I			9
21	J			10
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=SEQUENCE(5)
D1	D1	=test1(R12#)
D2	D2	=test1(INDEX(D12#,MATCH(Q12#,A12#,0)))
C1	C1	=test1(A1#)
C2	C2	=LET(one,A1#+0,test1(one))
C3	C3	=test1(A1#+0)
A12:A21	A12	=CHAR(SEQUENCE(10,,65))
D12:D21	D12	=SEQUENCE(10)
Q12:Q16	Q12	=CHAR(SEQUENCE(5,,65,2))
R12:R16	R12	=INDEX(D12#,MATCH(Q12#,A12#,0))
Dynamic array formulas.

This shows that a construction like INDEX(D12#,MATCH(Q12#,A12#,0)) CAN be passed as a parameter to a function. It will be passed as an array. The exception is when the Q12# range returns only a single value. In this case (for whatever reason the Excel developers came up with), it will be passed as a 1-cell range. You can see this if you change the Q12 formula to

=CHAR(SEQUENCE(1,,65,2))

and step through the macro. With 1 cell, it will take the range branch, with more, the array branch. There is actually a pretty easy workaround, if you're working with numerical values. Just add 0 to the range (if the range only contains numbers), and Excel will convert it to an array, even if it's only 1 cell. This should simplify your function enormously, since you'll only have to handle arrays, not ranges or constants. So like this:

=test1(R12#+0)

=test1(INDEX(D12#,MATCH(Q12#,A12#,0))+0)

Fifth and finally, I tried to make a useful version of =INDEX(D12#,MATCH(Q12#,A12#,0)), but got confused quickly, so I just came up with a generic version that uses that formula, but doesn't really do much. Every time I tried to do a useful version, I came up with a shorter version that didn't require that construction. So instead of posting some code that you admit doesn't work, it would be more useful if you were to show some of your data, and explain what you want to do with it.

Sorry about the long post, but I'm trying to be helpful with limited data, which means there are lots of options.

 

[BEDE]

I'm not sure where to even start.

First, the code you came up with in post #3 has several issues with it. I don't know how to fix them, because you didn't show the entire function, and because I really don't know what you're trying to do.

Second, yes you can incorporate multiple variables in the macro. The TEXTJOINX macro shows how to do that, first you have a loop to look at the passed parameters and see what type they are, next each parameter is handled according to its type, which may be another loop. The TEXTJOINX macro assumes the parameters are strings, arrays of strings, or ranges of strings. But in your case, you seem to be looking at values. This is fine, it just depends on what your ultimate goal is.

Third, you may want to look into using Excel functions in your VBA code. The line

MySum = WorksheetFunction.Sum(Parm1)

will work regardless of whether the parameter is a range, array or constant. Or even:

MySum = WorksheetFunction.Sum(Parm1, Parm2, Parm3, Parm4, Parm5)

Depending on your requirements, and if there is an existing built-in function, you might be able to simplify things a lot.

Fourth, I'm not exactly sure what that AI statement is saying, but I have a suspicion. Using the same macro I provided in post #2, consider this:

Book2
	A	B	C	D	E	F											Q	R
1	1		15	25
2	2		15	25
3	3		15
4	4
5	5
6
7
8
9
10
11
12	A			1													A	1
13	B			2													C	3
14	C			3													E	5
15	D			4													G	7
16	E			5													I	9
17	F			6
18	G			7
19	H			8
20	I			9
21	J			10
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=SEQUENCE(5)
D1	D1	=test1(R12#)
D2	D2	=test1(INDEX(D12#,MATCH(Q12#,A12#,0)))
C1	C1	=test1(A1#)
C2	C2	=LET(one,A1#+0,test1(one))
C3	C3	=test1(A1#+0)
A12:A21	A12	=CHAR(SEQUENCE(10,,65))
D12:D21	D12	=SEQUENCE(10)
Q12:Q16	Q12	=CHAR(SEQUENCE(5,,65,2))
R12:R16	R12	=INDEX(D12#,MATCH(Q12#,A12#,0))
Dynamic array formulas.

This shows that a construction like INDEX(D12#,MATCH(Q12#,A12#,0)) CAN be passed as a parameter to a function. It will be passed as an array. The exception is when the Q12# range returns only a single value. In this case (for whatever reason the Excel developers came up with), it will be passed as a 1-cell range. You can see this if you change the Q12 formula to

=CHAR(SEQUENCE(1,,65,2))

and step through the macro. With 1 cell, it will take the range branch, with more, the array branch. There is actually a pretty easy workaround, if you're working with numerical values. Just add 0 to the range (if the range only contains numbers), and Excel will convert it to an array, even if it's only 1 cell. This should simplify your function enormously, since you'll only have to handle arrays, not ranges or constants. So like this:

=test1(R12#+0)

=test1(INDEX(D12#,MATCH(Q12#,A12#,0))+0)

Fifth and finally, I tried to make a useful version of =INDEX(D12#,MATCH(Q12#,A12#,0)), but got confused quickly, so I just came up with a generic version that uses that formula, but doesn't really do much. Every time I tried to do a useful version, I came up with a shorter version that didn't require that construction. So instead of posting some code that you admit doesn't work, it would be more useful if you were to show some of your data, and explain what you want to do with it.

Sorry about the long post, but I'm trying to be helpful with limited data, which means there are lots of options.

I apologise for the confusion. I will try to get to the meat of the problem.

In a new workbook, drop the code below into a new module, goto sheet1 and in

A1 type 888,
b1 sequence(5)
c1 TRANSPOSE({10,20,30,40,50})

then in d1 =test1(a1) it will work. (constant)
e1 = test1(b1#) will work (range)
f1 = test1(c1#) will work too (array)

but then if you type into g1
=let(aa,index(C1#,match(b3,b1#)),
test1(aa)) This will fail, AI screenshot was in regards to this, it can definitely be wrong.

I suppose my goal is to be able to pass every type into test one get the result.

I will look further into your post, and thanks again.

Public Function test1(S As Variant) As Variant

    Dim results() As Variant

    Dim i As Long

    Dim count As Long

    Dim poo As Variant

    Dim y As Variant

  
    ' Determine if the input is a range or an array

    If TypeOf S Is Range Then

        ' Handle dynamic range input (could be a single row/column range)

        count = S.Cells.count

    ElseIf IsArray(S) Then

        ' Handle array input

        count = UBound(S) - LBound(S) + 1

    Else

        count = 1 ' Single constant input

    End If

  

    ' Resize the results array

    ReDim results(1 To count)

  

    ' Process range input

    If TypeOf S Is Range Then

        i = 1

        For Each y In S.Cells

            poo = y.Value

            results(i) = poo ' Store the value in results

            i = i + 1

        Next y

    ' Process array input

    ElseIf IsArray(S) Then

        For i = LBound(S) To UBound(S)

            poo = S(i) ' Directly access the array value

            results(i - LBound(S) + 1) = poo ' Store the value in results (1-based index)

        Next i

    Else

        results(1) = S ' Handle constant input

    End If


    test1 = Application.Transpose(results) ' Return the results array

End Function

 

[Eric W]

Here's what I get when I try that:

Book2
	A	B	C	D	E	F	G	H	I	J
1	888	1	10	888	1	10	30	#VALUE!	50	50
2		2	20		2	20			40	40
3		3	30		3	30			30	30
4		4	40		4	40			20	20
5		5	50		5	50			10	10
6
Sheet3

Cell Formulas
Range	Formula
B1:B5	B1	=SEQUENCE(5)
C1:C5	C1	=TRANSPOSE({10,20,30,40,50})
D1	D1	=test1(A1)
E1:F5	E1	=test1(B1#)
G1	G1	=LET(aa,INDEX(C1#,MATCH(B3,B1#)),test1(aa))
H1	H1	=LET(aa,INDEX(C1#,MATCH(6-B1#,B1#,0)),test1(aa))
I1:I5	I1	=LET(aa,INDEX(C1#,MATCH(6-B1#,B1#,0)),test1(TRANSPOSE(aa)))
J1:J5	J1	=LET(aa,INDEX(C1#,MATCH(6-B1#,B1#,0)),test2(aa))
Dynamic array formulas.

Every example works, including G1. When you say it failed, how? Error? If so, what? Wrong answer?

In H1, I tried using a range in the match instead of just B3. I used 6-B1# to put the results in reverse order. And I got the #VALUE error. Is this what you got? In this case, yes you are passing an array, and yes the function can handle an array. But the problem is that you are passing a vertical array, and the function is expecting a horizontal array. In I1, I used the same formula, just transposing the array before passing it to test1. In J1, I tweaked the test1 function to create test2, which will work on either type of array. Here are the changed lines:

    ' Process array input
    ElseIf IsArray(S) Then
        i = 1
        For Each poo In S
            results(i - LBound(S) + 1) = poo ' Store the value in results (1-based index)
            i = i + 1
        Next poo
    Else

Depending on what you want to do, you can use UBOUND(S, 2), LBOUND(S, 2), UBOUND(S, 3), etc. to further determine the dimensions of the array before processing it. But at some point you have to ask yourself, do I really need to do all this work? Can't I require that anyone calling this function has to pass the parameters in a specific format?

But still, this is just a technique. Are you just trying to learn more about VBA, or do you have a specific application in mind?

 

[BEDE]

Here's what I get when I try that:

Book2
	A	B	C	D	E	F	G	H	I	J
1	888	1	10	888	1	10	30	#VALUE!	50	50
2		2	20		2	20			40	40
3		3	30		3	30			30	30
4		4	40		4	40			20	20
5		5	50		5	50			10	10
6
Sheet3

Cell Formulas
Range	Formula
B1:B5	B1	=SEQUENCE(5)
C1:C5	C1	=TRANSPOSE({10,20,30,40,50})
D1	D1	=test1(A1)
E1:F5	E1	=test1(B1#)
G1	G1	=LET(aa,INDEX(C1#,MATCH(B3,B1#)),test1(aa))
H1	H1	=LET(aa,INDEX(C1#,MATCH(6-B1#,B1#,0)),test1(aa))
I1:I5	I1	=LET(aa,INDEX(C1#,MATCH(6-B1#,B1#,0)),test1(TRANSPOSE(aa)))
J1:J5	J1	=LET(aa,INDEX(C1#,MATCH(6-B1#,B1#,0)),test2(aa))
Dynamic array formulas.

Every example works, including G1. When you say it failed, how? Error? If so, what? Wrong answer?

In H1, I tried using a range in the match instead of just B3. I used 6-B1# to put the results in reverse order. And I got the #VALUE error. Is this what you got? In this case, yes you are passing an array, and yes the function can handle an array. But the problem is that you are passing a vertical array, and the function is expecting a horizontal array. In I1, I used the same formula, just transposing the array before passing it to test1. In J1, I tweaked the test1 function to create test2, which will work on either type of array. Here are the changed lines:

    ' Process array input
    ElseIf IsArray(S) Then
        i = 1
        For Each poo In S
            results(i - LBound(S) + 1) = poo ' Store the value in results (1-based index)
            i = i + 1
        Next poo
    Else

Depending on what you want to do, you can use UBOUND(S, 2), LBOUND(S, 2), UBOUND(S, 3), etc. to further determine the dimensions of the array before processing it. But at some point you have to ask yourself, do I really need to do all this work? Can't I require that anyone calling this function has to pass the parameters in a specific format?

But still, this is just a technique. Are you just trying to learn more about VBA, or do you have a specific application in mind?

What the I updated to your section above, tried a let and now it works.

This is definitively for educational purposes. I mainly use math/market functions but kept running into issues with the above. My goal is to be able to put this function in front of an actual function, I'm not sure I'm saying this right but I've seen many different example of an other function calling another.

I'm so excited. I'm going to do some more testing with your update.

One other question good sir. Does adding more variables mean adding more loops? Or is there a more elegant way of doing this?

 

[Eric W]

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.