0

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
Community
  • 1
  • 1
Estimator73
  • 1
  • 1
  • 2
    No, the string "xc1" is not equivalent to the variable `xc1`. However, might be easier to pass a `ParamArray` of arguments and iterate it. – BigBen Sep 20 '22 at 19:21
  • Also, if you're working on refining this code, maybe see if you can avoid having to call a function with 13 required variables for what might be a simpler fix with e.g. auto-fit? Looking around, it doesn't look like VBA has a way to loop through a function parameters as you have it. – BruceWayne Sep 20 '22 at 20:17
  • Thank you, I will look into this today. There may have been some standardization issues that prevented the use of auto-fit. – Estimator73 Sep 21 '22 at 12:05

1 Answers1

0

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.

freeflow
  • 4,129
  • 3
  • 10
  • 18