ByRef strangeness

[Nicklas Karlsson]

Hello,

I have a formula foo which is used in a worksheet as =foo(A1:A10)
and it is defined in a module as

Public Function foo(ByRef data As Range) As Double

There I would like to do some visual formatting on the data such as

data.select
data.font.colorindex = 3

But there is no reaction. Is there something I have misunderstood
about passing variables to custom functions?

 

[Tom Ogilvy]

Functions used in a worksheet are not permitted to alter the excel
environment (such as formatting) or change the value of other cells. They
can only return a value to the cell in which they are used.

 

[Nick Hodge]

Nicklas

XL UDFs can only pass back values, they cannot change objects. You use Subs
for this

eg

Sub Foo()
.......do any changes here
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)