excelvbavariablesdynamic

Name and create variables dynamically in for loop (VBA)


Can variables be named and assigned dynamically in VBA any other way?

Sub variables()
    
    Dim lastCol As Long
    lastCol = Sheet1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookAt:=xlWhole).Column
    
    Dim i As Long
    For i = 1 To lastCol
        Dim colIndex(Cells(1, i).Value) As Long
        colIndex(Cells(1, i)) = Cells(1, i).Column
    Next i
    
End Sub

But when I try to run it I see this error:

Compile Error:

Constant expression required


Solution

  • No, you cannot do this. What you can do is create an array of the appropriate size, with a spot for each of the variables you will need.