VBA Iterate Through Variables In Cell Formula

I have a spread sheet that has various references

on the same sheet
on a different sheet ( in same workbook )
on a different sheet ( in a different workbook )

If I click on a example cell it might say

Code:
=5*C4*$D$1-Sheet1!C9

Is there a way for excel to be able to iterate through these separate variables ? and know if the reference is on the same sheet ?

Say I wanted to evaluate the references on other sheets ( replacing the link with the value )

So if Sheet1!C9 contained the value 10 the formula would be replaced with

Code:
=5*C4*$D$1-10