Dim rng as Range
set rng = ActiveCell.DirectPrecedents
for each cell in rng
msgbox cell.address & ": " & cell.Value
Next

this only works for cells on the same worksheet.

--
Regards,
Tom Ogilvy


"Andrew Smith" wrote in message
om...
Hi,
Is there a way to recover the values of linked cells in formulas? For
instance, say that I have a macro that operates on the concatenate
function, taking the first argument and adding the phrase "the first
argument is: " before it. If say cell "A1" has the formula
"=CONCATENATE("hello ","world")" than it is easy for me to figure out
that the first argument is "hello " by using a command like
"wholeformula = Range("b16").formula". However, what if the "hello "
was in cell b2 so that the formula in A1 is
"=CONCATENATE(b2,"world")". Is there a way for me to look at cell A1
in VBA and recover "hello " instead of b2?

Thanks