How to get element number when looping through a VBA Array

Question

I have this VBA code that loops through an array (in this case, supplier_reports()) and it works fine. I was wondering if anyone know if there is a way to get the element number when working with the value in the array:

For Each x In supplier_reports

Workbooks.Open (x)

element_number = x

next x

In this case element number just becomes the value of x, not it's element number in the array.

Thanks

You need to have another variable as a counter that increments at the end of each loop. — Nischaal Cooray
– Nischaal Cooray, Commented Sep 24, 2015 at 14:56

Shalvin Abraham · Accepted Answer · 2015-09-24 15:06:15Z

3

You can use a For Loop instead of For Each.

Eg.

For I = LBound(supplier_reports) To UBound(supplier_reports)
 Workbooks.Open (supplier_reports(I))
 element_number = I
Next

answered Sep 24, 2015 at 15:06

Shalvin Abraham

4583 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tc_NYC Over a year ago

Good idea, I wasn't crazy about using a different structure, but this isn't a bad idea.

Shalvin Abraham Over a year ago

Thomas, In case of For Each as well, index is maintained internally. So no performance / memory loss also will be there.

Collectives™ on Stack Overflow

How to get element number when looping through a VBA Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related