PDA

View Full Version : Functions can change some properties?



levanduyet
05-15-2009, 11:55 PM
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
05-16-2009, 01:18 AM
No, not if you are calling it from a worksheet, that is not possible.

mikerickson
05-16-2009, 07:21 AM
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
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
and this in the ThisWorkbook code modulePrivate 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

Norie
05-16-2009, 07:55 AM
mike

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

mikerickson
05-16-2009, 08:19 AM
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
05-17-2009, 09:49 AM
Thanks mikerickson,

LVD