Re: Writing values to cells

Here is an example. In a standard module:

Public extra As Range
Public extrav As Variant

Function bobs_function(r As Range) As Variant
bobs_function = 10 * r.Value
If bobs_function > 100 Then
Set extra = Range("B9")
extrav = Now
End If
End Function


and in the work*** code area:

Private Sub Worksheet_Calculate()
If extra Is Nothing Then Exit Sub
extra.Value = extrav
Set extra = Nothing
End Sub


Somewhere on the work*** we have:
=bobs_function(A1)

As we change the value of A1, the function displays the result. If we
change the value in A1 so as to make the function return a value greater than
100, the function also sets some Public variables. The range variable is a
"wake up" to the event macro.

When the event macro is invoked, it sees the values. It takes the value and
puts it in the desired range (in this example the cell B9).. The macro then
clears the "wake up" message.

The net result is that two cells get changed:

1. the cell containing the UDF
2. cell B9
--
Gary''s Student - gsnu2007e


"Bob" wrote:

Thanks for your help. So, is there any way of writing a value to a cell (in
a UDF) that is not adjacent to the cell where the function is called?
Thanks;

Bob

"Gary''s Student" <GarysStudent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:84BB5B5E-9AD8-4683-95C7-3C46FF2BC660@xxxxxxxxxxxxxxxx
With some very rare exceptions, VBA functions can only return a value to
the
cell in which the function resides.

1. It can cause other cells to change only with the help of a "helper"
macro
2. It can't change the format of cells
3. It CAN insert or change comments
4. It can return values to several adjacent cells if the UDF returns an
array.
--
Gary''s Student - gsnu200771


"Bob" wrote:

Hi everyone:

Does anyone know why in VBA for excel, why I cannot write a value to a
cell
in a function, but I can in a sub without arguments? Is this a bug? For
example;

Public Function w(x As Double) As Double
Range("A1").Value = x
End Function

does not work. Whereas;

Public Sub w()
Range("A1").Value = 3.23
End Sub

Does work.

So, inside a function, how can I write to a cell? I appreciate your
help.

Bob






.