Creating variables within a loop

Question

I'm working in VBA and I wanted to know if it was possible to create and name variables within a while loop. My goal is to look through a particular column in the worksheet and create variables whose values are the text within the cells of that column.

Ideally, the variable names and their values would be something along the lines of:

party1 = "Owner" 
party2 = "Contractor" 
party3 = "Subcontractor"

And so on a so forth based on an number of parties.

So far I have:

Dim i As Integer, j As Integer
i = 2
j = 1
Do While Cells(i, 7).Value <> ""
    Dim partyName As String
    ' Pulls the text from the worksheet cell
    partyName = Cells(i, 7).Text
    Dim curNum As String
    curNum = CStr(j)
    Dim tempParty As String
    tempParty = "party" & curNum
    i = i + 1
    j = j + 1
Loop

I think this comes down to the question: How do I then get partyX to be the variable name and the variable value to be the string stored in partyName?

This seems like it's along the lines of what I'm looking for, but it doesn't quite answer my question. Also, I recognize there may be issues with the scope of these variables, but I can't think of any other way to do this.

An Array is what you want, they allow party(i) = ".." where i is a number, see patorjk.com/programming/tutorials/vbarrays.htm — Alex K.
– Alex K., Commented Jan 5, 2015 at 17:13
You can't create variables and name them during runtime. You can assign to a variant array, collection, or dictionary object. A dictionary would technically allow you to create "names", but probably not necessary, you could easily use an array instead. — David Zemens
– David Zemens, Commented Jan 5, 2015 at 17:16
I think it would be simpler if he just used a 'Select Case' once he had the value of the party from what I assume is 'partyName' — Chrismas007
– Chrismas007, Commented Jan 5, 2015 at 17:17
@Chrismas007 partyName stores a string (i.e. "Owner", "Contractor", etc.). I think the array will work. I'll also figure out what a 'Select Case.' Thank you, all! — arlederman
– arlederman, Commented Jan 5, 2015 at 19:32

Chrismas007 · Accepted Answer · 2015-01-05 19:40:17Z

2

IF you need to turn a NAME into a NUMBER using Select Case:

Dim CurRow As Long, LastRow As Long
LastRow = Cells(Rows.Count, 7).End(xlUp).Row

For CurRow = 1 to LastRow
    partyName = Cells(i, 7).Value
    Select Case partyName
        Case "Owner"
            partyNum = 1
        Case "Contractor"
            partyNum = 2
        Case "Subcontractor"
            partyNum = 3
        Case Else
    End Select
    'Do something with partyNum here
Next CurRow

If you need to turn a NUMBER into a NAME using Select Case:

Dim CurRow As Long, LastRow As Long
LastRow = Cells(Rows.Count, 7).End(xlUp).Row

For CurRow = 1 to LastRow
    partyNum = Cells(i, 7).Value
    Select Case partyNum
        Case 1
            partyName = "Owner"
        Case 2
            partyName = "Contractor"
        Case 3
            partyName = "Subcontractor"
        Case Else
    End Select
    'Do something with partyName here
Next CurRow

answered Jan 5, 2015 at 19:40

Chrismas007

6,1154 gold badges27 silver badges47 bronze badges

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

1 Comment

arlederman Over a year ago

This is helpful, thank you! It's similar to what I'm doing, and I'll be able to use the first case later in the script.

Collectives™ on Stack Overflow

Creating variables within a loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related