How to add loop counter value as array name suffix

Question

I have 3 variant arrays: Array1 Array2 Array3

I am wanting to add the counter value of my for loop to the end of the array name to save repeating the same code line over e.g. :

For i = 1 To oXlWkBk.Sheets.Count
FillArray ArraySource & i, "B1:" & oCurrentWs.Cells(lNumRows, lNumCols).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Next i

instead of:

For i = 1 To oXlWkBk.Sheets.Count
FillArray ArraySource1, "B1:" & oCurrentWs.Cells(lNumRows, lNumCols).Address(RowAbsolute:=False, ColumnAbsolute:=False)

FillArray ArraySource2, "B1:" & oCurrentWs.Cells(lNumRows, lNumCols).Address(RowAbsolute:=False, ColumnAbsolute:=False)

FillArray ArraySource3, "B1:" & oCurrentWs.Cells(lNumRows, lNumCols).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Next i

I've tried using:

Array & i
Array & Cstr(i)
Array(i)

Is it possible to append the counter value as a suffix to complete the array name?

Nope. Put them in a Scripting.Dictionary instead. Then you can use string keys. — Comintern
– Comintern, Commented Jan 27, 2017 at 19:33

A.S.H · Accepted Answer · 2017-01-27 20:50:17Z

1

This should work:

Dim Arrays As Variant
Arrays = Array(Array1, Array2, Array3)

For i = 1 To oXlWkBk.Sheets.Count
    FillArray Arrays(i-1), "B1:" & oCurrentWs.Cells(lNumRows, lNumCols).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Next i

Since Array() returns a 0-based array you need to be careful with the indices.

edited Jan 27, 2017 at 20:50

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

answered Jan 27, 2017 at 19:48

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

2 Comments

SmartSolid Over a year ago

Thanks for the suggestion. When I try this I get a compile error: Type Mismatch: Array or user-defined type expected. Not sure why this as I already have the Arrays array declared via Public Arrays() as Variant

John Coleman Over a year ago

@BLothian Drop the () and declare it as Arrays As Variant rather than Arrays() As Variant. You don't need to declare Arrays as an array -- just declare it as a simple variant variable. There is a difference between a variant which happens to contain an array and an array of variants. Along the same lines, if there is still a problem you could change the declaration of FillArray so that its first argument is a simple variant and not anything more specific, although this is less likely to be the issue.

Matt Cremeens · Accepted Answer · 2017-01-27 19:53:52Z

0

Can you try to store all three arrays inside an array and then reference the containing array with the i? For example,

Sub test()
largerArray = Array(Array(1, 2, 3), Array(4, 5, 6), Array(7, 8, 9))
For i = 0 To 2
    Debug.Print (sumArray(largerArray(i)))
Next i
End Sub
Function sumArray(thisArray As Variant) As Integer
sumArray = 0
For i = LBound(thisArray) To UBound(thisArray)
    sumArray = sumArray + thisArray(i)
Next i
End Function

So in your case, you would have

largerArray=Array(ArraySource1, ArraySource2, ArraySource3)
for i=0 to oXlWkBk.Sheets.Count
    FillArray largerArray(i), "B1:" & oCurrentWs.Cells(lNumRows, lNumCols).Address(RowAbsolute:=False, ColumnAbsolute:=False)

etc.

answered Jan 27, 2017 at 19:53

Matt Cremeens

5,2118 gold badges42 silver badges71 bronze badges

Collectives™ on Stack Overflow

How to add loop counter value as array name suffix

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related