Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hello, I have a big spreadsheet which should be purely formula based however
I need to check that there are no constants (i.e. hard coded values) typed into the formulas for example '=sum(a1:b10)+25000'. Is there a way to check each cell reference to be totally sure that there are no hardcodings? vb solution perhaps? I know you can switch to view formulas on the spreadsheet but as there are so many and some are quite long it isnt always easy to spot via the naked eye. Any help much appreciated. Thanks Brian Manchester, England |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hi Brian,
Try: '================= Sub ConstantsInFormulas() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address rng2.Select Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Co-op Bank" wrote in message ... Hello, I have a big spreadsheet which should be purely formula based however I need to check that there are no constants (i.e. hard coded values) typed into the formulas for example '=sum(a1:b10)+25000'. Is there a way to check each cell reference to be totally sure that there are no hardcodings? vb solution perhaps? I know you can switch to view formulas on the spreadsheet but as there are so many and some are quite long it isnt always easy to spot via the naked eye. Any help much appreciated. Thanks Brian Manchester, England |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hello Brian, As far as I know the only to do it is using the slow and painful metho of visual inspection. It is difficult for me to imagine a scenario wher all constants would be bad and needed to be removed. The formula ar strings that are brokendown by Excel's interpreter, which is a progra similar to a compiler. I don't see using programming as a viable mean to a solution in this case. Sorry. Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=47971 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Norman:
This is soooo cool,, Thanks for sharing the code. Note my temporary modification of the output lines: If Not rng2 Is Nothing Then 'do something e.g.: MsgBox "Cells containing constants a " & rng2.Address 'Debug.Print rng2.Address ' rng2.Select ' use this line to highlight cells Right now the above is producing the Messagebox with: Cells containing constants a $B$10,$G$15,$G$20,$J$45 How could I change above lines to have to show in messagebox as: Cells containing constants a $B$10 $G$15 $G$20 $J$45 Is there a maximum a MsgBox can display (like this)? Tks in advance, Jim May "Norman Jones" wrote in message ... Hi Brian, Try: '================= Sub ConstantsInFormulas() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address rng2.Select Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Co-op Bank" wrote in message ... Hello, I have a big spreadsheet which should be purely formula based however I need to check that there are no constants (i.e. hard coded values) typed into the formulas for example '=sum(a1:b10)+25000'. Is there a way to check each cell reference to be totally sure that there are no hardcodings? vb solution perhaps? I know you can switch to view formulas on the spreadsheet but as there are so many and some are quite long it isnt always easy to spot via the naked eye. Any help much appreciated. Thanks Brian Manchester, England |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hi Jim,
Thank you for your comments: they are very much appreciated. How could I change above lines to have to show in messagebox as: Cells containing constants a $B$10 $G$15 $G$20 $J$45 You could loop through the cells and build a string with a vbNewLine separator. Is there a maximum a MsgBox can display (like this)? Yes there is, and IIRC, there is no accompanying warining, just simple truncation. Best might be to add a report sheet showing the formuale anf the corresponding cell/sheet addresses. To illustrate each option, try something like: '================= Sub ConstantsInFormulas1A() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Dim msg As String Dim iCtr As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address SH.Activate rng2.Select Sheets.Add ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd") For iCtr = 1 To rng2.Cells.Count msg = msg & vbNewLine & rng(i).Address(False, False) With ActiveSheet .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True) .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula End With Next iCtr MsgBox msg Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Jim May" wrote in message news:%N08f.4793$mV4.1171@dukeread02... Norman: This is soooo cool,, Thanks for sharing the code. Note my temporary modification of the output lines: If Not rng2 Is Nothing Then 'do something e.g.: MsgBox "Cells containing constants a " & rng2.Address 'Debug.Print rng2.Address ' rng2.Select ' use this line to highlight cells Right now the above is producing the Messagebox with: Cells containing constants a $B$10,$G$15,$G$20,$J$45 How could I change above lines to have to show in messagebox as: Cells containing constants a $B$10 $G$15 $G$20 $J$45 Is there a maximum a MsgBox can display (like this)? Tks in advance, Jim May "Norman Jones" wrote in message ... Hi Brian, Try: '================= Sub ConstantsInFormulas() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address rng2.Select Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Co-op Bank" wrote in message ... Hello, I have a big spreadsheet which should be purely formula based however I need to check that there are no constants (i.e. hard coded values) typed into the formulas for example '=sum(a1:b10)+25000'. Is there a way to check each cell reference to be totally sure that there are no hardcodings? vb solution perhaps? I know you can switch to view formulas on the spreadsheet but as there are so many and some are quite long it isnt always easy to spot via the naked eye. Any help much appreciated. Thanks Brian Manchester, England |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Norman at this point (in your most recent code):
For iCtr = 1 To rng2.Cells.Count ' msg = msg & vbNewLine & rng(i).Address(False, False) With ActiveSheet .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True) .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula End With Next iCtr Prior to the above (from the immediate window) ? rng2.address $B$10,$G$15 but also (from the immediate) ? rng2(2).address $B$11 << Which is a blank cell !! My report produces $B$10 and $B$11 versus the $B$10 and $G$15 any observations? TIA, Jim "Norman Jones" wrote in message ... Hi Jim, Thank you for your comments: they are very much appreciated. How could I change above lines to have to show in messagebox as: Cells containing constants a $B$10 $G$15 $G$20 $J$45 You could loop through the cells and build a string with a vbNewLine separator. Is there a maximum a MsgBox can display (like this)? Yes there is, and IIRC, there is no accompanying warining, just simple truncation. Best might be to add a report sheet showing the formuale anf the corresponding cell/sheet addresses. To illustrate each option, try something like: '================= Sub ConstantsInFormulas1A() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Dim msg As String Dim iCtr As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address SH.Activate rng2.Select Sheets.Add ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd") For iCtr = 1 To rng2.Cells.Count msg = msg & vbNewLine & rng(i).Address(False, False) With ActiveSheet .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True) .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula End With Next iCtr MsgBox msg Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Jim May" wrote in message news:%N08f.4793$mV4.1171@dukeread02... Norman: This is soooo cool,, Thanks for sharing the code. Note my temporary modification of the output lines: If Not rng2 Is Nothing Then 'do something e.g.: MsgBox "Cells containing constants a " & rng2.Address 'Debug.Print rng2.Address ' rng2.Select ' use this line to highlight cells Right now the above is producing the Messagebox with: Cells containing constants a $B$10,$G$15,$G$20,$J$45 How could I change above lines to have to show in messagebox as: Cells containing constants a $B$10 $G$15 $G$20 $J$45 Is there a maximum a MsgBox can display (like this)? Tks in advance, Jim May "Norman Jones" wrote in message ... Hi Brian, Try: '================= Sub ConstantsInFormulas() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address rng2.Select Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Co-op Bank" wrote in message ... Hello, I have a big spreadsheet which should be purely formula based however I need to check that there are no constants (i.e. hard coded values) typed into the formulas for example '=sum(a1:b10)+25000'. Is there a way to check each cell reference to be totally sure that there are no hardcodings? vb solution perhaps? I know you can switch to view formulas on the spreadsheet but as there are so many and some are quite long it isnt always easy to spot via the naked eye. Any help much appreciated. Thanks Brian Manchester, England |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hi Jim,
any observations? Sloppily, I was iterating though a potentially non-contiguous range as if it were contiguous. In the case of a non-contiguous range, use of the index will produce results such as those you have observed. Try, instead, therefo '================= Sub ConstantsInFormulas1B() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Dim msg As String Dim iCtr As Long Dim aCell As Range Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address SH.Activate rng2.Select Sheets.Add ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd") For Each aCell In rng2.Cells iCtr = iCtr + 1 With ActiveSheet .Cells(iCtr, "A") = aCell.Address(external:=True) .Cells(iCtr, "B") = "'" & aCell.Formula msg = msg & vbNewLine & aCell.Address(external:=True) End With Next aCell MsgBox msg Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Jim May" wrote in message news:t338f.4794$mV4.2353@dukeread02... Norman at this point (in your most recent code): For iCtr = 1 To rng2.Cells.Count ' msg = msg & vbNewLine & rng(i).Address(False, False) With ActiveSheet .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True) .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula End With Next iCtr Prior to the above (from the immediate window) ? rng2.address $B$10,$G$15 but also (from the immediate) ? rng2(2).address $B$11 << Which is a blank cell !! My report produces $B$10 and $B$11 versus the $B$10 and $G$15 any observations? TIA, Jim |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
how about just
msg = "Cell Containing Constants: " & vbNewLine msgbox msg & replace(rng2.Address(0,0),",",chr10) demo'd from the immediate window: set rng2 = Selection ? rng2.Address $B$5,$D$7,$C$9,$F$11,$D$3,$F$4:$G$5 ? replace(rng2.address(0,0),",",chr(10)) B5 D7 C9 F11 D3 F4:G5 I believe a message box is limited to 255 characters. -- Regards, Tom Ogilvy "Jim May" wrote in message news:%N08f.4793$mV4.1171@dukeread02... Norman: This is soooo cool,, Thanks for sharing the code. Note my temporary modification of the output lines: If Not rng2 Is Nothing Then 'do something e.g.: MsgBox "Cells containing constants a " & rng2.Address 'Debug.Print rng2.Address ' rng2.Select ' use this line to highlight cells Right now the above is producing the Messagebox with: Cells containing constants a $B$10,$G$15,$G$20,$J$45 How could I change above lines to have to show in messagebox as: Cells containing constants a $B$10 $G$15 $G$20 $J$45 Is there a maximum a MsgBox can display (like this)? Tks in advance, Jim May "Norman Jones" wrote in message ... Hi Brian, Try: '================= Sub ConstantsInFormulas() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address rng2.Select Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Co-op Bank" wrote in message ... Hello, I have a big spreadsheet which should be purely formula based however I need to check that there are no constants (i.e. hard coded values) typed into the formulas for example '=sum(a1:b10)+25000'. Is there a way to check each cell reference to be totally sure that there are no hardcodings? vb solution perhaps? I know you can switch to view formulas on the spreadsheet but as there are so many and some are quite long it isnt always easy to spot via the naked eye. Any help much appreciated. Thanks Brian Manchester, England |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hi Jim,
Sloppily, I was iterating though a potentially non-contiguous range as if it were contiguous. In the case of a non-contiguous range, use of the index will produce results such as those you have observed. To demonstrate: '================= Sub ShowIt() Dim rng As Range Dim i As Long Dim rCell As Range Set rng = Union(Range("A1"), Range("IV65536")) 'Sloppily iterating through a non contiguous range! For i = 1 To rng.Cells.Count MsgBox rng(i).Address(0, 0) Next i 'Iterating the range cells! For Each rCell In rng.Cells MsgBox rCell.Address(0, 0) Next rCell End Sub '<<================= --- Regards, Norman "Norman Jones" wrote in message ... Hi Jim, any observations? Sloppily, I was iterating though a potentially non-contiguous range as if it were contiguous. In the case of a non-contiguous range, use of the index will produce results such as those you have observed. Try, instead, therefo '================= Sub ConstantsInFormulas1B() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Dim msg As String Dim iCtr As Long Dim aCell As Range Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address SH.Activate rng2.Select Sheets.Add ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd") For Each aCell In rng2.Cells iCtr = iCtr + 1 With ActiveSheet .Cells(iCtr, "A") = aCell.Address(external:=True) .Cells(iCtr, "B") = "'" & aCell.Formula msg = msg & vbNewLine & aCell.Address(external:=True) End With Next aCell MsgBox msg Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Jim May" wrote in message news:t338f.4794$mV4.2353@dukeread02... Norman at this point (in your most recent code): For iCtr = 1 To rng2.Cells.Count ' msg = msg & vbNewLine & rng(i).Address(False, False) With ActiveSheet .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True) .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula End With Next iCtr Prior to the above (from the immediate window) ? rng2.address $B$10,$G$15 but also (from the immediate) ? rng2(2).address $B$11 << Which is a blank cell !! My report produces $B$10 and $B$11 versus the $B$10 and $G$15 any observations? TIA, Jim |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hi Norman/Jim,
Thanks excellent thankyou very much! Best regards Brian "Norman Jones" wrote: Hi Jim, Sloppily, I was iterating though a potentially non-contiguous range as if it were contiguous. In the case of a non-contiguous range, use of the index will produce results such as those you have observed. To demonstrate: '================= Sub ShowIt() Dim rng As Range Dim i As Long Dim rCell As Range Set rng = Union(Range("A1"), Range("IV65536")) 'Sloppily iterating through a non contiguous range! For i = 1 To rng.Cells.Count MsgBox rng(i).Address(0, 0) Next i 'Iterating the range cells! For Each rCell In rng.Cells MsgBox rCell.Address(0, 0) Next rCell End Sub '<<================= --- Regards, Norman "Norman Jones" wrote in message ... Hi Jim, any observations? Sloppily, I was iterating though a potentially non-contiguous range as if it were contiguous. In the case of a non-contiguous range, use of the index will produce results such as those you have observed. Try, instead, therefo '================= Sub ConstantsInFormulas1B() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Dim msg As String Dim iCtr As Long Dim aCell As Range Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address SH.Activate rng2.Select Sheets.Add ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd") For Each aCell In rng2.Cells iCtr = iCtr + 1 With ActiveSheet .Cells(iCtr, "A") = aCell.Address(external:=True) .Cells(iCtr, "B") = "'" & aCell.Formula msg = msg & vbNewLine & aCell.Address(external:=True) End With Next aCell MsgBox msg Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Jim May" wrote in message news:t338f.4794$mV4.2353@dukeread02... Norman at this point (in your most recent code): For iCtr = 1 To rng2.Cells.Count ' msg = msg & vbNewLine & rng(i).Address(False, False) With ActiveSheet .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True) .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula End With Next iCtr Prior to the above (from the immediate window) ? rng2.address $B$10,$G$15 but also (from the immediate) ? rng2(2).address $B$11 << Which is a blank cell !! My report produces $B$10 and $B$11 versus the $B$10 and $G$15 any observations? TIA, Jim |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hi Tom,
A good suggestion! Thank you --- Regards, Norman "Tom Ogilvy" wrote in message ... how about just msg = "Cell Containing Constants: " & vbNewLine msgbox msg & replace(rng2.Address(0,0),",",chr10) demo'd from the immediate window: set rng2 = Selection ? rng2.Address $B$5,$D$7,$C$9,$F$11,$D$3,$F$4:$G$5 ? replace(rng2.address(0,0),",",chr(10)) B5 D7 C9 F11 D3 F4:G5 I believe a message box is limited to 255 characters. -- Regards, Tom Ogilvy "Jim May" wrote in message news:%N08f.4793$mV4.1171@dukeread02... Norman: This is soooo cool,, Thanks for sharing the code. Note my temporary modification of the output lines: If Not rng2 Is Nothing Then 'do something e.g.: MsgBox "Cells containing constants a " & rng2.Address 'Debug.Print rng2.Address ' rng2.Select ' use this line to highlight cells Right now the above is producing the Messagebox with: Cells containing constants a $B$10,$G$15,$G$20,$J$45 How could I change above lines to have to show in messagebox as: Cells containing constants a $B$10 $G$15 $G$20 $J$45 Is there a maximum a MsgBox can display (like this)? Tks in advance, Jim May "Norman Jones" wrote in message ... Hi Brian, Try: '================= Sub ConstantsInFormulas() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address rng2.Select Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Co-op Bank" wrote in message ... Hello, I have a big spreadsheet which should be purely formula based however I need to check that there are no constants (i.e. hard coded values) typed into the formulas for example '=sum(a1:b10)+25000'. Is there a way to check each cell reference to be totally sure that there are no hardcodings? vb solution perhaps? I know you can switch to view formulas on the spreadsheet but as there are so many and some are quite long it isnt always easy to spot via the naked eye. Any help much appreciated. Thanks Brian Manchester, England |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Thanks VERY much Norman;
This is a real "Keeper"!! Jim "Norman Jones" wrote in message ... Hi Jim, any observations? Sloppily, I was iterating though a potentially non-contiguous range as if it were contiguous. In the case of a non-contiguous range, use of the index will produce results such as those you have observed. Try, instead, therefo '================= Sub ConstantsInFormulas1B() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rng2 As Range Dim rCell As Range Dim arr As Variant Dim sStr As String Dim i As Long Dim msg As String Dim iCtr As Long Dim aCell As Range Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next 'In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not rng2 Is Nothing Then Set rng2 = Union(rng2, rCell) Else Set rng2 = rCell End If End If Next i Next rCell Else 'No formulas found End If If Not rng2 Is Nothing Then 'do something e.g.: Debug.Print rng2.Address SH.Activate rng2.Select Sheets.Add ActiveSheet.Name = "Report" & Format(Date, "yyyymmdd") For Each aCell In rng2.Cells iCtr = iCtr + 1 With ActiveSheet .Cells(iCtr, "A") = aCell.Address(external:=True) .Cells(iCtr, "B") = "'" & aCell.Formula msg = msg & vbNewLine & aCell.Address(external:=True) End With Next aCell MsgBox msg Else MsgBox "No Formula constants found in " & SH.Name End If End Sub '<<================= --- Regards, Norman "Jim May" wrote in message news:t338f.4794$mV4.2353@dukeread02... Norman at this point (in your most recent code): For iCtr = 1 To rng2.Cells.Count ' msg = msg & vbNewLine & rng(i).Address(False, False) With ActiveSheet .Cells(iCtr, "A") = rng2(iCtr).Address(external:=True) .Cells(iCtr, "B") = "'" & rng2(iCtr).Formula End With Next iCtr Prior to the above (from the immediate window) ? rng2.address $B$10,$G$15 but also (from the immediate) ? rng2(2).address $B$11 << Which is a blank cell !! My report produces $B$10 and $B$11 versus the $B$10 and $G$15 any observations? TIA, Jim |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hi Jim,
Try this slightly revised and commented version: '================= Sub ConstantsInFormulas2() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim Rng2 As Range Dim rCell As Range Dim aCell As Range Dim arr As Variant Dim sStr As String Dim strName As String Dim msg As String Dim i As Long Dim iCtr As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next '\\ In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not Rng2 Is Nothing Then Set Rng2 = Union(Rng2, rCell) Else Set Rng2 = rCell End If End If Next i Next rCell Else '\\No formulas found End If If Not Rng2 Is Nothing Then '\\ do something e.g.: Debug.Print Rng2.Address '\\ Highlight Formulas with constants Rng2.Interior.ColorIndex = 6 '\\ Add a report sheet Sheets.Add '\\ Name the report sheet -include Report date & time strName = "FormulasReport" _ & Format(Now, "yyyymmdd hh-mm") ActiveSheet.Name = strName For Each aCell In Rng2.Cells iCtr = iCtr + 1 '\\ Write information to the Report sheet With ActiveSheet .Cells(iCtr, "A") = aCell.Address(external:=True) .Cells(iCtr, "B") = "'" & aCell.Formula End With Next aCell ActiveSheet.Columns("A:B").AutoFit '\\ Parse address string to produce columnar MsgBox report '\\ N.B. A Msgbox is limited to 255 characters. msg = "Cells holding formulas which include constants" _ & vbNewLine msg = msg & Replace(Rng2.Address(0, 0), ",", Chr(10)) Else msg = "No Formula constants found in " & SH.Name End If MsgBox prompt:=msg, _ Buttons:=vbInformation, _ Title:="Formulas Report" End Sub '<<================= --- Regards, Norman "Jim May" wrote in message news:QHc8f.4854$mV4.3704@dukeread02... Thanks VERY much Norman; This is a real "Keeper"!! Jim |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding constants in formulas
Hi Norman,
Told you it was a keeper :o) Just the hyperlinks still missing in the report sheet ;o) Cheers Sige Norman Jones wrote: Hi Jim, Try this slightly revised and commented version: '================= Sub ConstantsInFormulas2() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim Rng2 As Range Dim rCell As Range Dim aCell As Range Dim arr As Variant Dim sStr As String Dim strName As String Dim msg As String Dim i As Long Dim iCtr As Long Set WB = ActiveWorkbook '<<======== CHANGE Set SH = WB.Sheets("Sheet1") '<<======== CHANGE Set rng = SH.UsedRange '<<======== CHANGE On Error Resume Next '\\ In case no formulas! Set rng = rng.SpecialCells(xlFormulas) On Error GoTo 0 arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") If Not rng Is Nothing Then For Each rCell In rng.Cells For i = LBound(arr) To UBound(arr) sStr = "*" & arr(i) & "[0-9]*" If rCell.Formula Like sStr Then If Not Rng2 Is Nothing Then Set Rng2 = Union(Rng2, rCell) Else Set Rng2 = rCell End If End If Next i Next rCell Else '\\No formulas found End If If Not Rng2 Is Nothing Then '\\ do something e.g.: Debug.Print Rng2.Address '\\ Highlight Formulas with constants Rng2.Interior.ColorIndex = 6 '\\ Add a report sheet Sheets.Add '\\ Name the report sheet -include Report date & time strName = "FormulasReport" _ & Format(Now, "yyyymmdd hh-mm") ActiveSheet.Name = strName For Each aCell In Rng2.Cells iCtr = iCtr + 1 '\\ Write information to the Report sheet With ActiveSheet .Cells(iCtr, "A") = aCell.Address(external:=True) .Cells(iCtr, "B") = "'" & aCell.Formula End With Next aCell ActiveSheet.Columns("A:B").AutoFit '\\ Parse address string to produce columnar MsgBox report '\\ N.B. A Msgbox is limited to 255 characters. msg = "Cells holding formulas which include constants" _ & vbNewLine msg = msg & Replace(Rng2.Address(0, 0), ",", Chr(10)) Else msg = "No Formula constants found in " & SH.Name End If MsgBox prompt:=msg, _ Buttons:=vbInformation, _ Title:="Formulas Report" End Sub '<<================= --- Regards, Norman "Jim May" wrote in message news:QHc8f.4854$mV4.3704@dukeread02... Thanks VERY much Norman; This is a real "Keeper"!! Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding percentage formulas | New Users to Excel | |||
Excel sees cell formulas as constants, will not solve | Excel Worksheet Functions | |||
Finding constants in formulas | Excel Worksheet Functions | |||
Finding constants in formulas | Excel Discussion (Misc queries) | |||
Finding Array Formulas in VBA | Excel Programming |