Formulas containing hard coded values

R

Rick Rothstein

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 said:
Sleep well!!
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


Rick Rothstein said:
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 said:
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 ..."


:

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)


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)


... 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

:

....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)


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
will
always
return True for that formula even if there is no numeric or text
constants
in it. Here is a modified function which I believe works
correctly
in
all
circumstances...

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 = R.Formula
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
Fml = Replace(Fml, Cel.Row, "")
Next
Next
If Not Fml Like "*#*" Then IsRefOnly = True
End If
End Function

My suggestion in my parallel post for you to use these
immediately
before
your loop through the range returned by the SpecialCells
property
...

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and reset them after immediately after the loop and in any On
Error
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

I don't think you can limit the precedent lookup that way. If you had a
formula constructed with ranges that overlapped other cell references
something like this made up example...

=IF(A1=B101,SUM(B1:B200),"")

specifying the 100 limit would miss the B101 cell reference as it would not
be listed as a separate precedent area; but, rather, it would be included
within the B1:B200 precedent area range.

--
Rick (MVP - Excel)


Bony Pony said:
My attempt to limit the processed range. I'm sure there is a more elegant
way :)

Function IsRefOnly(R As Range) As Boolean
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 precedent lookup
z = 0 ' counter for precedent 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
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


I've learnt a lot from this! Thank you.

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


Bony Pony said:
No - I can see currentregion will not work. It doesn't allow the
For each cel in Rng to fully replace. Dang!
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


Also - an amendment to the above post - for vlookup and hlookup, the ,1
is a
range modifyer OR a column or row identifyer. In Match, the ,0 ,1 or ,-1
is
valid but does different things.

I guess it is unrealistic to expect this to work without some creative
judgement orinput ... what do you think?

Kind regards,
Robert

Bony Pony said:
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 ..."


:

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)


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)


... 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

:

....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)


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
will
always
return True for that formula even if there is no numeric or
text
constants
in it. Here is a modified function which I believe works
correctly in
all
circumstances...

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 = R.Formula
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
Fml = Replace(Fml, Cel.Row, "")
Next
Next
If Not Fml Like "*#*" Then IsRefOnly = True
End If
End Function

My suggestion in my parallel post for you to use these
immediately
before
your loop through the range returned by the SpecialCells
property ...

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and reset them after immediately after the loop and in any On
Error
trap
sections still holds.

--
Rick (MVP - Excel)


message
Hi Rick,
This looks like it will do exactly what I want! Absolutely
awesome!
It
works like a charm. Fantastic result!

Thank you very VERY much!!

Kind regards,
Bony

:

Here is a function that I am pretty sure does what you
want... it
tests
if a
*single* cell is "pure" in the sense you have described (no
text or
number
constants) returning True if it is and False if it is not.
Use it in
conjunction with the SpecialCells call you mentioned, looping
through
each
cell in the range it returns, testing each cell with the
function
and
highlighting in anyway you chose those cells for which the
function
returns
False...
Click to expand...
Click to expand...
Click to expand...
 
B

Bony Pony

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 said:
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 said:
Sleep well!!
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


Rick Rothstein said:
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)


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 ..."


:

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)


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)


... 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

:

....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)


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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

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 said:
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 said:
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 said:
Sleep well!!
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


:

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)


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 ..."


:

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)


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)


... 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

:

....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)


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Code To find Hardcoded Values in Formula 5
Lookup function to cell containing multiple values 3
Transfere a workbook's all cell's formulas and references to VBA "fingerprint library" ? 2
Function cannot obtain cell values 1
Can I lock only the cells containing formulas? 3
Multiply/Sum cells containing formulas 3
Copying and Pasting Different Formulas 2
Formulas are skipping a row 8

Top