Can you uses a string variable as the name of a new variable?

[DropTables]

Hello, long time lurker first time poster. Still fairly new to VBA. I am wondering if it is possible to put a string variable in a dimension statement.

I am trying to write a for loop that will store the column letter for each column in a sheet based on the text in the header row ("C_" & [header text]. For example, the ID column will be a string viable named "C_ID" which will store "A". I am currently doing this manually for each column, so I don't have to rely on the columns positions when I want to reference a column. The purpose is so the code doesn't break when I add or remove columns. I am mostly using these variable in in range statements (eg WS.range(C_ID & [row]). Open to alternative ways to accomplish the same.

My first go at try this is below. It thinks I am trying to declare ColumnVarName a second time instead of using the string value.


Dim WS As Worksheet
Dim CurrentColumn As Long ' Current Column Number
Dim ColumnLetter As String ' Current Column Letter
Dim ColumnTitle As String
Dim NumberofColumns As Long
NumberofColumns = WS.Cells(1, Columns.Count).End(xlToLeft).Column
Dim VariableName As String


For CurrentColumn = 1 To NumberofColumns

ColumnLetter = Split(WS.Cells(1, CurrentColumn).Address, "$")(1) 'Convert current iteration to column letter
ColumnTitle = WS.Range(ColumnLetter & 1).Value 'Sets Column title as header text
VariableName = "C_" & ColumnTitle 'Sets VariableName to what I want my variable to be named
Dim VariableName As String ' 'Declare new variable named after sting value ; Duplicate declaration error
VariableName = COLLetter(WS_BuySell, ColumnTitle, BS_LCOL, 1)
Next

 

[DropTables]

whoops just realized I could post it this way.

Dim CurrentColumn As Long ' Current Column Number
Dim ColumnLetter As String ' Current Column Letter
Dim ColumnTitle As String
Dim NumberofColumns As Long
    NumberofColumns = WS.Cells(1, Columns.Count).End(xlToLeft).Column
Dim VariableName As String


For CurrentColumn = 1 To NumberofColumns
    'Convert current iteration to column letter
    ColumnLetter = Split(Cells(1, CurrentColumn).Address, "$")(1)
    'Sets Column title as header text
    ColumnTitle = WS.Range(ColumnLetter & 1).Value
    'Sets VariableName to what I want my variable to be named
    VariableName = "C_" & ColumnTitle
    'Declare new variable from sting variable
    Dim VariableName As String
        VariableName = COLLetter(WS_BuySell, ColumnTitle, BS_LCOL, 1)
Next