Help Finding Plugged Formulas in Excel

[itn04]

I am trying to develop a macro that would loop through the cells in a workbook and identify the cells that contain formulas that have been "plugged". By "plugged" I mean the formula contains references to other cells but also contains a hard-coded amount(s).

Example formulas:
=A5*B7/C8
=134+44290+3912
=A5*B7+42
=OtherSheet!A3*OtherSheet!B4+56

The first two example formulas are fine. I need to identify the third and fourth formulas.

I was using the Precedents property to find formulas that contain cell references but I need to be able to identify cells that contain references to other worksheets as well so I don't think Precedents is going to work for me. Everything I've read says it only works on the activesheet.

Does anyone know a way around this?

Also, after I identify the formulas that have cell references, I need to determine whether the formula has been "plugged". Does anyone have any ideas on how to parse up the formula and decide if it has been "plugged"?

 

[SkipVought]

Hi,

For a start, any formula that has numeric literal will have values between, for instance...
[tt]
"=0" and "=9"
"+0" and "+9"
...
[/tt]
Reference to other sheets contain a BANG !


Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[WinblowsME]

Private Sub TEST()
   Dim i As Integer
   
   For i = 1 To 4
      Debug.Print Is_Plugged(Cells(i, 1).Formula)
   Next i
End Sub

Private Function Is_Plugged(aline As String) As Boolean
   Dim regex As Object, plugged As Boolean
   
   Set regex = CreateObject("VBScript.RegExp")
   regex.Global = True
   regex.Pattern = "[a-zA-Z][0-9]"
   plugged = False
   
   If regex.TEST(aline) Then
      regex.Pattern = "[=+-/*][0-9]"
      If regex.TEST(aline) Then
         plugged = True
      End If
   End If
   
   Is_Plugged = plugged
End Function