Search Modules for Cell Addresses in Range Variables

[ExcelMonkey]

I want to check and see if a cell address is referenced in VBA code So lets
say I have a macro in my Personal wkbk or XLA that allows me to open the
target model (wkbk1). Upon opening this workbook, I want to test to see if
wrbk1 has code modules. Then I want to test to see if it has range
variabless. These range variales could come in several forms. They might
be direct examples like EXAMPLE 1 below. Or the could be ranges which are
included in a broader range as in EXAMPLE 2. Or they could be ranges that
includes via a Method as in EXAMPLE 3. I can search the text of the module
for EXAMPLE 1. For Example 2 I neeed to evaluate the SUM function and test to
see if my cell address is in the range. How do you do this? It's almost the
reverse of the Union function. I need to break out the indivdual addresses
in a range. For Example 3 I need to be able to walk through the OFFSET
Method and test the value of the variable Var1. Is there a way to do this
(i.e. can you walk through the code of a target module without acutally
running that code to test variable values?)?

EXAMPLE 1
Var1 = Range("Sheet1!A1")

EXAMPLE 2
Var1 = Application.WorkbookFunction.Sum(Range("Sheet!A1:A5")

EXAMPLE 3
Var1 = Range("Sheet1!A1").Offset(0,X)


Thanks

EM