VBA Function returns "Object required" error

[Kra]

Hi all,

I am trying to set up a function that will check if cell value overflows and change format to xlFill if so. But it returns "Object required" in Sub, it doesn't even move to the function itself


The parameter of this function is Range, but it seems like

ActiveCell

l is causing an error as this parameter. Any ideas what can go wrong?

Sub TestFillFunction()

FillCell (ActiveCell)

End Sub

Function FillCell(ActCel As Range)

Dim celBeforeAutoFitSize As Double
Dim celAfterAutoFitSize As Double


    celBeforeAutoFitSize = ActCel.ColumnWidth

    ActCel.Columns.AutoFit

    celAfterAutoFitSize = ActCel.ColumnWidth


    If celAfterAutoFitSize > celBeforeAutoFitSize Then

        ActCel.ColumnWidth = celBeforeAutoFitSize
        ActCel.HorizontalAlignment = xlFill
    
    Else

        ActCel.ColumnWidth = celBeforeAutoFitSize

    End If

End Function

 

[HaHoBe]

Hi kra,

maybe use

Sub TestFillFunction()
FillCell ActiveCell
End Sub

or

Sub TestFillFunction()
Dim var

var = FillCell(ActiveCell)
End Sub

Ciao,
Holger

 

[Kra]

Hi kra,

maybe use

Sub TestFillFunction()
FillCell ActiveCell
End Sub

or

Sub TestFillFunction()
Dim var

var = FillCell(ActiveCell)
End Sub

Ciao,
Holger

Hi Holger,

First option works... Thank you, cannot believe it was that simple!