Function won't update a cell

[DeveloperX]

Hi, This has been asked before but I've not found anything recent. I'm
using Excel 2003 with either VSTO or VBA.

I have an RTD server which works fine, but as we know RTD does not
trigger the worksheet's change event.

There have been lots of suggestions that don't work around this, but I
thought I was getting close with this one.

Create a function which points at the cell to monitor and then do
something, something like this:
----------------------------------------------------------------------------------------------------------------
Public Function Test(r As range) As String

On Error GoTo Failed:

r.Font.ColorIndex = 3

Test = "5"

CleanExit:
Exit Function
Failed:
Debug.Print Err.Description

End Function
-----------------------------------------------------------------------------------------------------------------

The function fires at the correct time, yay I thought, except you'll
note the line which changes the color. This does not work. No error,
it just doesn't do anything to the range.

I've tried all sorts of things, but I don't seem to be able to affect
other cells from this function.

Does anyone have any ideas of why? If it's just designed that way,
does anyone else have any idea of how to generate an event when RTD
data arrives?

Thanks

 

[paul.robinson]

Hi
VBA functions cannot change properties, only return a value. you
CANNOT change a font property for example.
regards
Paul

 

[DeveloperX]

Hi
VBA functions cannot change properties, only return a value. you
CANNOT change a font property for example.
regards
Paul

That is hugely annoying. Thank you for your prompt reply. If anyone
has figured out how to generate an event when RTD data arrives, please
let me know.

Cheers
DevX

 

[Bob Phillips]

Link a cell to one of the RTD cells, and use the worksheet_calculate.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi
VBA functions cannot change properties, only return a value. you
CANNOT change a font property for example.
regards
Paul

That is hugely annoying. Thank you for your prompt reply. If anyone
has figured out how to generate an event when RTD data arrives, please
let me know.

Cheers
DevX

 

[DeveloperX]

Link a cell to one of the RTD cells, and use the worksheet_calculate.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)





That is hugely annoying. Thank you for your prompt reply. If anyone
has figured out how to generate an event when RTD data arrives, please
let me know.

Cheers
DevX

Thanks Bob. I was trying to avoid that approach as it means comparing
lots of cells that could change as opposed to worrying about only the
cells that change.

 

[Bob Phillips]

You might be able to get away with one comparison, like

=COUNTIF(A1:M1000,"X")

shouldn't matter what you test for, the fact that one of those cells changes
should trigger a recalc for that formula, and thus your code.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Link a cell to one of the RTD cells, and use the worksheet_calculate.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)





That is hugely annoying. Thank you for your prompt reply. If anyone
has figured out how to generate an event when RTD data arrives, please
let me know.

Cheers
DevX

Thanks Bob. I was trying to avoid that approach as it means comparing
lots of cells that could change as opposed to worrying about only the
cells that change.