Formulas containing hard coded values

Bony Pony · Aug 4, 2009

Hi all,
I am in receipt of a workbook in which someone has "amended" random formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains hardcoded
numbers or text?

Thanks in advance!
Bony

Simon Lloyd · Aug 4, 2009

I'm not sure what you are trying to achieve, anyway, try this, don'
forget to change the range
Code
-------------------
Sub formulae(
Dim MyCell As Rang
Dim C As String, D As Strin
For Each MyCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row
If IsNumeric(MyCell) The
C = C & vbLf & MyCell.Addres
ElseIf MyCell.HasFormula And MyCell.Text <> vbNullString The
D = D & vbLf & MyCell.Addres
End I
Nex
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf
& "Cells with Text values:" & vbLf &
C = "
D = "
End Su
-------------------

Hi all
I am in receipt of a workbook in which someone has "amended" rando
formula
with harcoded additions - e
=a1*b1*1.
o
=if(a1=0,"None,"OK"

Does anyone have a vba approach to identify a cell that contain
hardcode
numbers or text

Thanks in advance
Bon

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

Bony Pony · Aug 4, 2009

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

Barb Reinhardt · Aug 4, 2009

I believe what you were supplised was code that could help you find the cells
with formulas. Since formulas can typically contain what you're calling
"hard coded values", it's tough to know exactly what you are looking for.

Are you saying that for your IF Statement, you want something like this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations you may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you want it.
end if

Alternatively, you can use the Formula Auditing functionality to see all of
the formulas as written out.

HTH,
Barb Reinhardt

Bony Pony · Aug 4, 2009

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded constants
that unless you know they are there, will always affect the result of C1

I want to highlight C1 as a cell that contains a formula driven by literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

Barb Reinhardt · Aug 4, 2009

Sorry, I can't help right now. Maybe someone else can.

Rick Rothstein · Aug 4, 2009

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

Bony Pony · Aug 4, 2009

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

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Bony Pony said:

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result of C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

Click to expand...

Rick Rothstein · Aug 4, 2009

You are quite welcome... I'm glad it will work out for you. One thought for
you to consider in your own code that loops through the SpecialCells
range... you might want to put this code before you start your loop...

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

and then put this code

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

either directly after the loop or, if you have an On Error statement
covering the loop, in the target of the On Error statement. I think the
above may be helpful in keeping your loop running efficiently because each
call to the function I posted physically changes the formula a couple of
times in the cell that it passed into it and that may trigger some internal
actions on the part of Excel that could slow things down. Anyway, even if it
is not "needed", doing so can't really hurt anything either.

--
Rick (MVP - Excel)

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

Rick Rothstein said:

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Bony Pony said:

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result of
C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

:

I believe what you were supplised was code that could help you find
the
cells
with formulas. Since formulas can typically contain what you're
calling
"hard coded values", it's tough to know exactly what you are looking
for.

Are you saying that for your IF Statement, you want something like
this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations you
may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you
want
it.
end if

Alternatively, you can use the Formula Auditing functionality to see
all
of
the formulas as written out.

HTH,
Barb Reinhardt
:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

:

I'm not sure what you are trying to achieve, anyway, try this,
don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text <> vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------

Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended"
random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains
hardcoded
numbers or text?

Thanks in advance!
Bony

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=122224

Click to expand...

Click to expand...

Rick Rothstein · Aug 4, 2009

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)

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

Rick Rothstein said:

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Bony Pony said:

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result of
C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

:

I believe what you were supplised was code that could help you find
the
cells
with formulas. Since formulas can typically contain what you're
calling
"hard coded values", it's tough to know exactly what you are looking
for.

Are you saying that for your IF Statement, you want something like
this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations you
may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you
want
it.
end if

Alternatively, you can use the Formula Auditing functionality to see
all
of
the formulas as written out.

HTH,
Barb Reinhardt
:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

:

I'm not sure what you are trying to achieve, anyway, try this,
don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text <> vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------

Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended"
random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains
hardcoded
numbers or text?

Thanks in advance!
Bony

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=122224

Click to expand...

Click to expand...

Rick Rothstein · Aug 4, 2009

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

Bony Pony said:

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

Rick Rothstein said:

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result of
C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

:

I believe what you were supplised was code that could help you find
the
cells
with formulas. Since formulas can typically contain what you're
calling
"hard coded values", it's tough to know exactly what you are looking
for.

Are you saying that for your IF Statement, you want something like
this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations
you
may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you
want
it.
end if

Alternatively, you can use the Formula Auditing functionality to see
all
of
the formulas as written out.

HTH,
Barb Reinhardt
:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

:

I'm not sure what you are trying to achieve, anyway, try this,
don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text <> vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------

Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended"
random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that
contains
hardcoded
numbers or text?

Thanks in advance!
Bony

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=122224

Click to expand...

Click to expand...

Bony Pony · Aug 5, 2009

Hi Rick,
I really can't thank you enough. I did spot the problem with the row
numbers but thank you for being thorough. You have saved me many hours.

Have a great day!

Kind Regards,
Robert

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

Click to expand...

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 said:

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)

Bony Pony said:

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result of
C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

:

I believe what you were supplised was code that could help you find
the
cells
with formulas. Since formulas can typically contain what you're
calling
"hard coded values", it's tough to know exactly what you are looking
for.

Are you saying that for your IF Statement, you want something like
this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations
you
may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you
want
it.
end if

Alternatively, you can use the Formula Auditing functionality to see
all
of
the formulas as written out.

HTH,
Barb Reinhardt
:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

:

I'm not sure what you are trying to achieve, anyway, try this,
don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text <> vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------

Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended"
random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that
contains
hardcoded
numbers or text?

Thanks in advance!
Bony

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=122224

Click to expand...

Click to expand...

Bony Pony · Aug 5, 2009

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

Click to expand...

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 said:

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)

Bony Pony said:

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result of
C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

:

I believe what you were supplised was code that could help you find
the
cells
with formulas. Since formulas can typically contain what you're
calling
"hard coded values", it's tough to know exactly what you are looking
for.

Are you saying that for your IF Statement, you want something like
this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations
you
may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you
want
it.
end if

Alternatively, you can use the Formula Auditing functionality to see
all
of
the formulas as written out.

HTH,
Barb Reinhardt
:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

:

I'm not sure what you are trying to achieve, anyway, try this,
don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text <> vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------

Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended"
random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that
contains
hardcoded
numbers or text?

Thanks in advance!
Bony

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=122224

Click to expand...

Click to expand...

Rick Rothstein · Aug 5, 2009

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

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

Click to expand...

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 said:

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)

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result
of
C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

:

I believe what you were supplised was code that could help you
find
the
cells
with formulas. Since formulas can typically contain what you're
calling
"hard coded values", it's tough to know exactly what you are
looking
for.

Are you saying that for your IF Statement, you want something like
this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations
you
may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you
want
it.
end if

Alternatively, you can use the Formula Auditing functionality to
see
all
of
the formulas as written out.

HTH,
Barb Reinhardt
:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

:

I'm not sure what you are trying to achieve, anyway, try this,
don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text <> vbNullString
Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf
_
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------

Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended"
random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that
contains
hardcoded
numbers or text?

Thanks in advance!
Bony

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=122224

Click to expand...

Click to expand...

Rick Rothstein · Aug 6, 2009

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 said:
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 said:

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

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

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)

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result
of
C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer?

regards,
Bony

:

I believe what you were supplised was code that could help you
find
the
cells
with formulas. Since formulas can typically contain what you're
calling
"hard coded values", it's tough to know exactly what you are
looking
for.

Are you saying that for your IF Statement, you want something
like
this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the
permutations
you
may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if
you
want
it.
end if

Alternatively, you can use the Formula Auditing functionality to
see
all
of
the formulas as written out.

HTH,
Barb Reinhardt
:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

:

I'm not sure what you are trying to achieve, anyway, try
this,
don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text <> vbNullString
Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf &
vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------

Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has
"amended"
random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that
contains
hardcoded
numbers or text?

Thanks in advance!
Bony

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=122224

Click to expand...

Click to expand...

Bony Pony · Aug 6, 2009

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 said:
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 said:

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 said:

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

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)

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded

Click to expand...

Click to expand...

Bony Pony · Aug 6, 2009

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

Rick Rothstein said:

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 said:

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)

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)

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

Click to expand...

Click to expand...

Rick Rothstein · Aug 6, 2009

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

Rick Rothstein said:

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 said:

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)

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

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which
is
logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard
coded

Click to expand...

Click to expand...

Bony Pony · Aug 6, 2009

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

Rick Rothstein said:

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)

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)

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)

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

Rick Rothstein · Aug 6, 2009

I'm sorry to do this to you, but it is after 4:00am in the morning here and
I'm going to sleep for the night... I'll look at this (and hopefully your
response to my previous message) in about 5 or 6 hours when I get up.

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

Improving Nested Formula Calculation Efficiency	2	Aug 3, 2011
VBA Code To find Hardcoded Values in Formula	4	Aug 11, 2006
Finding a Value Between Two Numbers and Returning Corresponding Value	12	Aug 19, 2011
simple code but it doesn't look like usual vba code	2	Jun 4, 2011
Re problem with getting formula in workbook	3	Feb 4, 2009
Copy and Paste exact formula	5	Jan 14, 2009
Lookup and return	2	Apr 19, 2010
How to refer to the cell containing the formula?	3	Jan 13, 2010

Formulas containing hard coded values

Bony Pony

Simon Lloyd

Bony Pony

Barb Reinhardt

Bony Pony

Barb Reinhardt

Rick Rothstein

Bony Pony

Rick Rothstein

Rick Rothstein

Rick Rothstein

Bony Pony

Bony Pony

Rick Rothstein

Rick Rothstein

Bony Pony

Bony Pony

Rick Rothstein

Bony Pony

Rick Rothstein

Ask a Question

Similar Threads