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?

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.

--
Regards,
Tom Ogilvy

"Nicklas Karlsson" wrote in message
m...
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?

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
HIS


"Nicklas Karlsson" wrote in message
m...
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?