Functions can change some properties?

[levanduyet]

Dear All,
Can I write the function that :
1. Return the value.
2. Change some properties.

Some things like:

Function RowsCount(Rng As Range)

On error goto ErrorHandler
Range("A1").Font.Bold = True
RowsCount = Rng.Rows.Count
ErrorExit:
   Exit Function

ErrorHandler:
   RowsCount = -1
   Resume ErrorExit
End Function

LVD

[Bob Phillips]

No, not if you are calling it from a worksheet, that is not possible.

[mikerickson]

A function called from a worksheet can change the evironment if the evironmental change is done in a companion Calculate event.

Put this in a normal module
[VBA]Public CellsToBold As New Collection

Function RowsCount(Rng As Range) As Long
On Error Resume Next
CellsToBold.Add Rng, key:=Rng.Address(, , , True)
On Error GoTo ErrorHandler
RowsCount = Rng.Rows.Count
errorExit:
On Error GoTo 0
Exit Function
ErrorHandler:
RowsCount = -1
Resume errorExit
End Function
[/VBA]and this in the ThisWorkbook code module[VBA]Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim oneCell As Range

If 0 < CellsToBold.Count Then
For Each oneCell In CellsToBold
oneCell.Font.Bold = True
Next oneCell
Set CellsToBold = Nothing
End If
End Sub[/VBA]

[Norie]

mike

I know this is possible, but is it 'really' the function thats changing the properties?

[mikerickson]

From the user's perspective (press this button, the screen changes that way), I'm don't think there's a difference between a function changing the environment and a Calculate event changing the environment.

I'd split the difference and call that a work-around.

[levanduyet]

Thanks mikerickson,

LVD