Explain App.Index(Rng, Rws(), Clms()) (multicolumn Combobox with Index.application)

**Amelynn** · 02-28-2022, 01:28 AM

Who can help me, I have the following code that I found somewhere that I don't remember:

Code:

Private Sub UserForm_Initialize()

   Dim rng As Range

   Set rng = Range("B4:D8")

   With ComboBox1
      .ColumnCount = 2
      .ColumnWidths = "50;50"
      ' load 1st, 2nd columns of range into combobox
      .List = Application.Index(rng, Evaluate("ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
   End With
End Sub

In "sheet1" I have three columns of data in the range from B4 to D8 (the headers in row 3). In a combobox the data of columns B and D appears.This is a multicolumn combobox with non-continuous columns (that are not next to each other). It works perfectly and is adapted to what I wanted. Unfortunately I don't understand how the line
".List = Application.Index(rng, Evaluate("ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))"
works and I can't stay So.

I know that we are making an output list from an input list and I understand that evaluate with row is returning the index of the rows that have data in the range, from the first row to the fifth, and that with array we determine the columns which will take the combobox (or so I thought I understood).
But why can't we use an array to determine the rows as well?
I tried to do it and it didn't work for me.

If someone is kind enough to tell me what data is returning evaluate (which is supposed to be used to occupy excel formulas in vba) with row, I would greatly appreciate it.

I hope everything is understood. I'm using google translator.

https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**DocAElstein** · 02-28-2022, 06:49 PM

Hello Amelynn
Welcome to ExcelFox , the thinking man’s Excel forum

Originally Posted by Amelynn

Who can help me, ....

I think, therefore I am , and able to help you.

Originally Posted by Amelynn

...... I don't understand how the line ".List
= Application.Index(rng, Evaluate("ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
" works and I can't stay So....

To Explain

Question: _(i) What is Array(1, 3) ?
Answer_ It is like –
___1 _3
( Sometime we write in excel spreadsheet convention {1, 3} , but this usually means the same thing )

Question: _(ii)a) Evaluate("ROW(1:" & rng.Rows.Count & ")")
Answer:

Originally Posted by Amelynn

..... evaluate (…….. used to occupy excel formulas in vba) with row,

Correct! Good! - Because you understand this, it makes the explanation for me much easier.
We are really only interested in understanding what is , =ROW(1:" & rng.Rows.Count & ")" , in excel spreadsheet formula

Question: _(ii)b) What is =ROW(1:" & rng.Rows.Count & ") ? ( in excel spreadsheet )
For your range, rng = B4:D8 , rng.Rows.Count = 5
=ROW(1: " & rng.Rows.Count & " )
=ROW(1: " & 5 & " )
=ROW(1:5)

( Green is Excel Spreadsheet, Blue is VBA in string “ “ in Evaluate( “ “ ) )

The excel spreadsheet Row( ) function is usually like for
Row( A1) = 1
but can also return a “vertical” array of values like
Row( A1:A2) = 1
Row( A1:A2) = 2
Inside VBA Evaluate “ _ “ we find that we are decoupled from spreadsheet absolute values, and so in inside VBA Evaluate “ _ “
Row(A1:A2) = Row( 1:2) = 1
Row(A1:A2) = Row( 1:2) = 2

ROW(1:5)
It is like
1
2
3
4
5
( Sometimes we may write
{1
2
3
4
5}
or sometimes we may write in excel spreadsheet convention , ={1;2;3;4;5} , but usually this means the same thing)
Note: For “vertical” array some excel use ; but some Excel use \ – So sometime you may need ={1\2\3\4\5}

Sometimes if we are lucky, Excel will try to do array calculations and return you an array.
Like

Code:

	{	1	2		X	{	6	8		  =	{	1, 6	2, 8	
		3	4	}			7	9	}			3, 7	4, 9	}

But if you ask it to do = Index ___ , ROW(1:5)_ ,_ Array{1 , 3} it tries to look at columns and rows not specified.
This should not work

Code:

=  {	1	?			   {	1	3	
	2	?				?	?	
	3	?		X		?	?	
	4	?				?	?	
	5	?	}			?	?	}

???? So we have a problem ?,
But we can be lucky again, because then Excel will guess to see the following instead, ( actually its more complicated then that, more precisely it is due to Excel VBA Interception and Implicit Intersection , but we often say that Excel guesses things, as its often written to get things correct when you miss things out. ( In this particular case it is not clear if the phenomenum occurs by accident or design ) )

Code:

=  {	1	1			   {	1	3	
	2	2				1	3	
	3	3		X		1	3	
	4	4				1	3	
	5	5	}			1	3	}

( What is actually happening there above in those last two sketches is: -
If Excel is given a single row or a single column, but is being required to look at values of further adjoining rows and columns where no values are given, then the effect of the phenomena of Excel VBA Interception and Implicit Intersection is that in certain situation the missing values will effectively be taken as a duplication of the values in the given row or column )

So, Index will try to give us

Code:

_______    Index(    (    ,	1, 1	1, 3	
				2, 1	2, 3	
				3, 1	3, 3	
				4, 1	4, 3	
				5, 1	5, 3	  )

So if your range is, rng = B4:D8
-__=

Code:

	_B4	_C4	_D4
	_B5	_C5	_D5
	_B6	_C6	_D6
	_B7	_C7	_D7
	_B8	_C8	_D8

then Application.Index(rng, Evaluate("=ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("=ROW(1:5)"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;2;3;4;5}"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;2;3;4;5}"), Evaluate("={1, 3}"))
-__ =

Code:

    rng  ,            1,1  1,3
                      2,1  2,3
                      3,1  3,3
                      4,1  4,3
                      5,1  5,3

-__=

Code:

    _B4 _C4 _D4       1,1  1,3
    _B5 _C5 _D5       2,1  2,3
    _B6 _C6 _D6       3,1  3,3
    _B7 _C7 _D7       4,1  4,3
    _B8 _C8 _D8       5,1  5,3

Code:

	  =     _B4	_D4
		_B5	_D5
		_B6	_D6
		_B7	_D7
		_B8	_D8

Originally Posted by Amelynn

........use an array to determine the rows as well......

Sure, this is no problem:
One way, for example, for just 1st 3rd and 5th row
Change
Evaluate("={1;2;3;4;5}")
to
Evaluate("={1;3;5}")

-__ =Application.Index(rng, Evaluate("={1;3;5}"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;3;5}"), Evaluate("={1, 3}"))

Code:

    _B4 _C4 _D4       1,1  1,3
    _B5 _C5 _D5       3,1  3,3
    _B6 _C6 _D6       5,1  5,3
    _B7 _C7 _D7
    _B8 _C8 _D8

Code:

	  =     _B4	_D4
		_B6	_D6
		_B8	_D8

Here a demo macro for you
Put some arbitrary values in your range "B4:D8" , then run this macro:

Code:

Sub Test()
Dim Rng As Range
 Set Rng = Worksheets("Sheet1").Range("B4:D8")
Dim RwsCnt As Long
 Let RwsCnt = Rng.Rows.Count '  is = 5

Dim arr_List() As Variant
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:" & Rng.Rows.Count & ")"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:" & RwsCnt & ")"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:5)"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("={1;2;3;4;5}"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("={1;2;3;4;5}"), Evaluate("={1,3}"))

 Let Worksheets("Sheet1").Range("A40").Resize(UBound(arr_List(), 1), UBound(arr_List(), 2)).Value = arr_List()

' To only select 1st 3rd and 5th row
 Let arr_List() = Application.Index(Rng, Evaluate("={1;3;5}"), Evaluate("={1,3}"))
 Let Worksheets("Sheet1").Range("A47").Resize(UBound(arr_List(), 1), UBound(arr_List(), 2)).Value = arr_List()

End Sub