How to use a string as a variable to set ColumnWidth of individual columns?

Estimator73 0
Viewed 36

I'm trying to refine the below code /commented out/ to something like the shorter script at the bottom.

Using VBA6 as this is legacy code.

Private Sub XLSetCol(xc1, xc2, xc3, xc4, xc5, xc6, xc7, xc8, xc9, xc10, xc11, xc12, xc13, xc14, xc15)
    ' Column Width
    'excel_app.Columns("A").ColumnWidth = xc1
    ' excel_app.Columns("B").ColumnWidth = xc2
    ' excel_app.Columns("C").ColumnWidth = xc3
    ' excel_app.Columns("D").ColumnWidth = xc4
    ' excel_app.Columns("E").ColumnWidth = xc5
    '  excel_app.Columns("F").ColumnWidth = xc6
    '  excel_app.Columns("G").ColumnWidth = xc7
    '  excel_app.Columns("H").ColumnWidth = xc8
    '  excel_app.Columns("I").ColumnWidth = xc9
    '  excel_app.Columns("J").ColumnWidth = xc10
    '  excel_app.Columns("K").ColumnWidth = xc11
    '  excel_app.Columns("L").ColumnWidth = xc12
    '  excel_app.Columns("M").ColumnWidth = xc13
    '  excel_app.Columns("N").ColumnWidth = xc14
    '  excel_app.Columns("O").ColumnWidth = xc15

The below code results in

"Runtime error '1004' Unable to set the ColumnWidth property of the Range Class.

    For temp = 1 To 15
        tempa = "ABCDEFGHIJKLMNO"
        middle = Mid(tempa, temp, 1)
        foo = ("xc" & temp)
        excel_app.Columns(middle).ColumnWidth = foo
    Next temp
    
End Sub
vba6 vba
1 Answers

As mentioned in the comments a ParamArray would be one solution. You could also use just arrays.

The code below assumes that at some stage you may wish to use column Ids with more than one letter.

Option Explicit


'XLSetCol = "A,B,C,D,AF,BZ,DY", 10,10,10,20,30,40,40,40
Private Sub XLSetCol(ByVal ipColumns As String, ParamArray ipColWidths() As Variant)

    ' ipColumns is a list of comma separated column ids
    ' we use comma separated so we can have columsn such as 'df' etc
    ' also, it means the columns don't have to be consecutive
    ' you just need to match the column id's with the
    ' widths in the param array
    
    Dim myColumns As Variant
    myColumns = VBA.Split(ipColumns, ",")
    
    Dim myIndex As Long
    For myIndex = LBound(ipColWidths) To UBound(ipColWidths)
       
       excel_app.Columns(myColumns(myIndex)).ColumnWidth = ipColWidths(myIndex)
       
   Next
   
End Sub

' Or the dual array version

' XLSetcol Array("A","B","C","D"),Array(10,10,20,20,30))
Private Sub XLSetCol(ByRef ipColIds As Variant, ByRef ipColWidths As Variant)

    Dim myIndex As Long
    For myIndex = LBound(ipColIds) To UBound(ipColIds)
        excel_app.Columns(ipColIds(myIndex)).ColumnWidth = ipColWidths(myIndex)
   Next
End Sub

Please note that I haven't tested the above code. Bit it is useful as a pointer.

freeflow0
Related