VBA use string as variable name

Question

I have workbooks with datasets (of varying lengths) from measurements in different rooms. I need to sort each datasets. My code finds the start and end rows of each dataset and stores them as StartRowRoom1, EndRowRoom1, StartRowRoom2, EndRowRoom2 etc.

I want to go through each dataset in a while loop like this.

Dim StartRowRoom1 As Integer
Dim StartRowRoom2 As Integer
Dim EndRowRoom1 As Integer
Dim EndRowRoom2 As Integer

n = 1

While n < NumberOfRooms
    startRow = "StartRowRoom" & n
    endRow = "EndRowRoom" & n

    With Range(Cells(startRow, 4), Cells(endRow, 4))
        .FormulaR1C1 = "=RC[-2]+RC[-1]"
        #sorting and graph creation
    End With
    n = n + 1
Wend

My problem is that the startRow and endRow variables are strings ("StartRowRoom1" and "EndRowRoom1", for n=1). so they cannot be used in Cells(). I want them to refer to the variables defined as integers. Does anyone have a solution?

Sam · Accepted Answer · 2013-11-18 13:52:54Z

1

This is what arrays are for.

You should declare your variables as

Dim StartRowRoom(1 to 2) As Integer
Dim EndRowRoom(1 to 2) As Integer

StartRowRoom(1) = [your value here]
StartRowRoom(2) = [your value here]

EndRowRoom(1) = [your value here]
EndRowRoom(2) = [your value here]

Then you can access them as

startRow = StartRowRoom(n)
endRow = EndRowRoom(n)

using n as the index

answered Nov 18, 2013 at 13:52

Sam

7,3333 gold badges27 silver badges37 bronze badges

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

1 Comment

Michaël Benjamin Saerens Over a year ago

Arrays, Collections or Dictionaries

Antonio E. · Accepted Answer · 2013-11-18 13:58:41Z

0

I think you should try to store the StartRowRoom1 variables like Range type and use .offset() and .end() in getting that references.

Then in your loop you don't need the Cells(,):

With Range(startRow, endRow) ...

answered Nov 18, 2013 at 13:58

Antonio E.

3772 gold badges12 silver badges27 bronze badges

Collectives™ on Stack Overflow

VBA use string as variable name

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related