Hi,

I’m trying to write a function that will construct SQL statements to read data from an Access database.

Returning the Existing Value
Because the SQL queries could take a while to run, I'd like to be able to control exactly when the function reads data from the database. That is, I'd like one of the function parameters to specify whether the function should read from the database or just return the existing cell value.

I've tried using Application.Caller.Value to return the existing value, but this creates a circular reference. I've had some success using Application.Caller.Text, but this restricts the precision to that currently displayed.

Changing the Worksheet
Ideally, I'd also like the function to provide some obvious indication of where the value came from. The font could be green for “read from database” or red for “old value”. I realise that UDFs cannot (in theory) change the worksheet, but I've had some success using comments to show a value's source. Are there any other exceptions or work-arounds that could be used?

Alternatively, I'd like the cell value to show, say, “*Calc*” whilst the function is reading from the database, before displaying the returned value. I've seen commercially-available add-ins that do this.

Any ideas? I'm wondering whether Macro 4.0 code might work? Or something else other than VBA?

Many thanks,

Mike.