Variables in VBA loops

Question

I'm trying to run a VBA application using a loop and using variables whose names depends on where in the loop I am. Specifically something like

Dim i As Integer
i = 1
Dim varname() As String
while i < 50
varname(i) = asdasd
i = i + 1
Wend

Somehow it can't read varname(i) or whatever. It reports subscript out of range.
I have no idea what the problem is, can someone helt me perhaps?

mango · Accepted Answer · 2013-06-22 12:53:10Z

2

You need to give your array a capacity first.

Sub max()

Dim i As Integer
i = 1

Dim varname() As String
ReDim varname(49) '<---- There

While i < 50
varname(i) = asdasd
i = i + 1
Wend


End Sub

This is a good resource for VBA arrays: http://msdn.microsoft.com/en-us/library/office/aa164778(v=office.10).aspx

answered Jun 22, 2013 at 12:53

mango

5,6364 gold badges32 silver badges42 bronze badges

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

3 Comments

ThomasMS Over a year ago

Ah okay. Can I update the ReDim later on, without destroying existing variables? Since the dimension in my actual code will depend on some if outcomes.. So at the start I don't know the final number of variables I'll need.

mango Over a year ago

yes, the command to use there instead would be ReDim Preserve

Alex P Over a year ago

I think you can just use Dim varname(49) as String without needing ReDim.

Collectives™ on Stack Overflow

Variables in VBA loops

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related