Pass Worksheet to a function to autofit columns-CodePudding

I have the following working code as part of a bigger function

Dim SheetToModify As Worksheet
Set SheetToModify = Sheets(strNewProjSheetName)
Dim x As Integer

For x = 1 To SheetToModify.UsedRange.Columns.Count
    SheetToModify.Columns(x).EntireColumn.AutoFit
Next x

Where strNewProjSheetName is a string.

I'd like to extract the for loop in to a function. To do this I have

Public Function AutofitAllUsedColumns(mySheet As Worksheet)
    Dim x As Integer

For x = 1 To mySheet.UsedRange.Columns.Count
     mySheet.Columns(x).EntireColumn.AutoFit
Next x

End Function

and call it with the following

Dim SheetToModify As Worksheet
Set SheetToModify = Sheets(strNewProjSheetName)

AutofitAllUsedColumns (SheetToModify)

Unfortunately, this gives the error Object doesn't support this property or method, any help will be appreciated.

CodePudding user response：

Autofit Used Columns

Note that
```
mySheet.Columns(x).EntireColumn.AutoFit
```
refers to the columns of the worksheet which are only the same as the used range columns if the used range starts in column A. The correct line would be:
```
mySheet.UsedRange.Columns(x).EntireColumn.AutoFit
```
A function returns a result while a sub doesn't. AutofitAllUsedColumns is not a function, it's a sub.
You don't need a loop for this kind of operation. The code is more efficient without it.
Since it has only one line, its purpose is questionable i.e. you can just use
```
SheetToModify.UsedRange.EntireColumn.AutoFit
```
in the calling procedure.

Option Explicit

Sub AutoFitTEST()
    
    Const strNewProjSheetName As String = "Sheet1"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim SheetToModify As Worksheet
    Set SheetToModify = wb.Worksheets(strNewProjSheetName)
    
    AutofitAllUsedColumns SheetToModify

End Sub

Sub AutofitAllUsedColumns(ByVal mySheet As Worksheet)
    mySheet.UsedRange.EntireColumn.AutoFit
End Sub