You can always use FALSE for 0, TRUE for 1 and -TRUE for -1 (note the minus sign in front of the keyword TRUE). Otherwise, in order to exempt these values, you would have to set up trap statements for each Excel worksheet function that could take these "switch" arguments.... this should be doable, but I imagine messy. If you want to identify them for me, I would be willing to try to see if there was a way to isolate their usage within each identified function you would ever expect to use.

--
Rick (MVP - Excel)


"Bony Pony" wrote in message ...
Hi Rick,
As you can see below, I have incorporated all your suggestions. I think it
is doing all I can expect it to now.

With regards to your observation point on my best practice points of
including the ,1 or ,0 as a number .. the big 5 consultancies do not see the
,0 or ,-1 or ,1 switches as literals - they are part of the formula and
whereas 0 and can be seen as False or True, there is no substitute for -1 ..
:)

I was hoping you would answer my question regarding the Relpace code - is
there a way to mask the ,0 or ,1 so two statements can be resolved into 1?

Have a great day!

Code follows:
Sub rc_cell_integrity()
Dim R As Range, sdoit As String
Set R = ActiveCell
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
sdoit = IsRefOnly(R)
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationSemiautomatic
If sdoit = False Then
MsgBox "Cell contains hard codes"
Else
MsgBox "Cell is good"
End If
'ActiveCell.Offset(1, 0).Select
End Sub

Function IsRefOnly(R As Range) As Boolean
' Returns True if the cell is "pure" or false if the cell has embedded
numbers or operators
' Grateful thanks to Rick Rothstein
' v4
Dim X As Long, Rw As Long
Dim y As Integer, z As Integer
Dim Rng As Range, Cel As Range
Dim Fml As String, LCtext As String
Dim UCtext As String, OriginalFormula As String
y = 100 ' limit for dependent lookup
z = 0 ' counter for dependent loop
If R.Count 1 Then
err.Raise vbObjectError + 1001, "IsRefOnly Function", _
"Only one cell permitted in Range for this function!"
Exit Function
End If
OriginalFormula = R.Formula
R.Formula = LCase(R.Formula)
LCtext = R.Formula
R.Formula = UCase(R.Formula)
UCtext = R.Formula
R.Formula = OriginalFormula
If LCtext = UCtext Then
Fml = Application.ConvertFormula(R.Formula, xlA1, xlA1, True)
Fml = Replace(Fml, ",0", "") ' replace range lookup or match qualifyer
Fml = Replace(Fml, ",1", "") ' replace range lookup or match qualifyer
Fml = Replace(Fml, ",-1", "") ' replace range lookup or match qualifyer
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
z = z + 1
If z y Then Exit For
If InStr(Fml, Cel.Address(True, True)) Then Fml = Replace( _
Fml, Cel.Address(True, True), "")

Next
Next
If Not Fml Like "*#*" Then
IsRefOnly = True
ElseIf Not Fml Like "*[!$]#*" Then
IsRefOnly = True
End If
End If
End Function


--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"Rick Rothstein" wrote:

I was kind of hoping you would have replied to my initial comment to the
quote I include from an earlier posting of yours and to question I asked
about whether you added the three Application property calls I mentioned in
an earlier post of mine so that I would know where we currently stand. Also,
did you try out the inner loop modification I proposed and, if you did, did
it help any?

--
Rick (MVP - Excel)


"Bony Pony" wrote in message
...
Sleep well!!
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"Rick Rothstein" wrote:

I figured when you said this...

"What I am trying to achieve is to highligt those
formulas that contain a numerical value or text
value that amends the result of the formula in a
non best practice way. Best practice dictates
that formulas should not contain hard coded
elements. All elements pertaining to a formula
should have a linked basis."

in your 2nd posting, that it was your practice to never have a numerical
constant in any of your formulas. From your statement, I would have
expected
you to have the 0 and 1 in your examples stored in a cell and a cell
reference to them in your formula. If you are going to allow numerical
constants in certain situations, then I don't think you will be able to
achieve what you want 100% of the time... to do that, you would have to
duplicate the full Excel parser in code (which I think would be
considerable
in size).

As for using CurrentRegion in place of the Precedents... no, I don't
think
that would work at all for multiple, individual cells whose current
regions
could extend well beyond their single occurrences. Did you use the three
Application property calls I mentioned in a previous message inside your
own
code where I indicated they should go? Also, perhaps changing the inner
loop
to this would help speed things up...

For Each Cel In Rng
If InStr(Fml, Cell.Address(True, True)) Then Fml = Replace( _
Fml, Cel.Address(True, True), "")
Next

--
Rick (MVP - Excel)


"Bony Pony" wrote in message
...
Hi Rick,
A typical formula that returns False would be the following:
match(a1,a5:a10,0) for absolute match or
match(a1,a5:a10,1) for closest match - both formulas are valid.

The ,0 or ,1 gets detected as an event.. which is why I included the
replace(fml,",0","") to eliminate them as these switches are always 0
or
1.

I can see a problem with using precedents.areas though - if you are
analysing a formula =sum(a:f), stepping through each dependent - even
on
my
Core i7 920 - takes forever ... so I changes .areas to ..currentregion
and
it
works faster. Is this ok?

Thnk you for the time you are spending on this!
Kind regards,
Robert
--
"There are 10 types of people in this world. Those who understand
Binary
and those who don''t ..."


"Rick Rothstein" wrote:

Here is one more modification that add the handling of partial/full
row
references and partial/full column references (such as 3:3, 5:12, A:A
and
D:M)...

Function IsRefOnly(R As Range) As Boolean
Dim X As Long, Rw As Long
Dim Rng As Range, Cel As Range
Dim Fml As String, LCtext As String
Dim UCtext As String, OriginalFormula As String
If R.Count 1 Then
Err.Raise vbObjectError + 1001, "IsRefOnly Function", _
"Only one cell permitted in Range for this function!"
Exit Function
End If
OriginalFormula = R.Formula
R.Formula = LCase(R.Formula)
LCtext = R.Formula
R.Formula = UCase(R.Formula)
UCtext = R.Formula
R.Formula = OriginalFormula
If LCtext = UCtext Then
Fml = Application.ConvertFormula(R.Formula, xlA1, xlA1, True)
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
Fml = Replace(Fml, Cel.Address(True, True), "")
Next
Next
If Not Fml Like "*#*" Then
IsRefOnly = True
ElseIf Not Fml Like "*[!$]#*" Then
IsRefOnly = True
End If
End If
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in
message
...
Give me some examples of the formulas the function doesn't work with
and
I'll see if I can patch the code to account for them.

As for the last function I posted, it still has a flaw in it.
Because I
set it up to remove the row number of cell addresses, there is a
change
I
could be replacing a numerical constant as well. As an example,
3*Row("A3")... once I have identified A3, my code then replaced all
3's
in
the formula text... that would mean the 3 multiplier would be
removed
as
well, making it impossible for the code to see the 3 multiplier.
Here
is
some modified code that eliminates this flaw (which you can use
until
you
respond to my opening sentence)...

Function IsRefOnly(R As Range) As Boolean
Dim X As Long, Rw As Long
Dim Rng As Range, Cel As Range
Dim Fml As String, LCtext As String
Dim UCtext As String, OriginalFormula As String
If R.Count 1 Then
Err.Raise vbObjectError + 1001, "IsRefOnly Function", _
"Only one cell permitted in Range for this function!"
Exit Function
End If
OriginalFormula = R.Formula
R.Formula = LCase(R.Formula)
LCtext = R.Formula
R.Formula = UCase(R.Formula)
UCtext = R.Formula
R.Formula = OriginalFormula
If LCtext = UCtext Then
Fml = Application.ConvertFormula(R.Formula, xlA1, xlA1, True)
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
Fml = Replace(Fml, Cel.Address(True, True), "")
Next
Next
If Not Fml Like "*#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)


"Bony Pony" wrote in message
...
... interesting change to the second version ... I like how you
reference
the
precedents.areas - master stroke.

I did notice though that if you use either version of your function
with
a
formula that contains a range modifyer - e.g. match, it detects the
,1
or
,0
as a literal. So it seems to work for simple formulas but not for
formulas
with elements. sigh ...

So I tried this ...

Sub rc_cell_integrity()
Dim R As Range, sdoit As String
Set R = ActiveCell
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
sdoit = IsRefOnly(R)
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationSemiautomatic
If sdoit = False Then
MsgBox "Cell contains hard codes"
Else
MsgBox "Cell is good"
End If
End Sub

Function IsRefOnly(R As Range) As Boolean
' Returns True if the cell is "pure" or false if the cell has
embedded
numbers or operators
' Grateful thanks to Rick Rothstein
Dim X As Long, Rw As Long
Dim Rng As Range, Cel As Range
Dim Fml As String, LCtext As String
Dim UCtext As String, OriginalFormula As String
If R.Count 1 Then
err.Raise vbObjectError + 1001, "IsRefOnly Function", _
"Only one cell permitted in Range for this function!"
Exit Function
End If
OriginalFormula = R.Formula
R.Formula = LCase(R.Formula)
LCtext = R.Formula
R.Formula = UCase(R.Formula)
UCtext = R.Formula
R.Formula = OriginalFormula
If LCtext = UCtext Then
Fml = R.Formula
For Each Rng In R.Precedents.Areas
On Error Resume Next
Fml = Replace(Fml, ",0", "") ' replace
Fml = Replace(Fml, ",1", "") ' replace
For Each Cel In Rng
Debug.Print Fml
Fml = Replace(Fml, Cel.Row, "")
Next
Next
If Not Fml Like "*#*" Then IsRefOnly = True
End If
End Function

I spent a short time trying to mask the ,0 or ,1 so I could do it
in
one
statement but eh ...

This seems to work. It does what I want it to so once again many
thanks!!

Kins regards,
Robert

"Rick Rothstein" wrote:

....if you have a cell address with 2 or more digits in its row
number, the function will always return True for that formula
even if there is no numeric or text constants in it.

I misstated the flaw above... for row numbers of 2 or more digits,
the
function will always return **False** even if there is no numeric
or
text
constants in it.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in
message
...
Actually, the function I posted has a flaw in it... if you have
a
cell
address with 2 or more digits in its row number, the function