Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to check to see if cell has hard coded data in it
I want to write a function that will check to see if a cell has har coded data in it. For example in the formula below, you would want t check and see that all the operators (=,+,-,*,/) are followed by eithe a $ or a letter. So below the 2 would be picked up because it follows sign. =$C$10/E10+G10+2/C10*E10 I want to do this so that I can run a check on a spreadsheet to mak sure their are no hidden easter eggs in it. Thanks -- ExcelMonke ----------------------------------------------------------------------- ExcelMonkey's Profile: http://www.excelforum.com/member.php...nfo&userid=522 View this thread: http://www.excelforum.com/showthread.php?threadid=26906 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to check to see if cell has hard coded data in it
On Wed, 13 Oct 2004 16:53:51 -0500, ExcelMonkey
wrote: I want to write a function that will check to see if a cell has hard coded data in it. For example in the formula below, you would want to check and see that all the operators (=,+,-,*,/) are followed by either a $ or a letter. So below the 2 would be picked up because it follows + sign. =$C$10/E10+G10+2/C10*E10 I haven't tested it but, if you are only concerned about numeric constants, perhaps something like: =============== Function FormulaHasConstant(rg As Range) As Boolean Dim FormulaString As String Dim Operators As String Dim FormulaCharacter As String Dim i As Integer, j As Integer Operators = "=" & "/" & "+" & "-" & "*" & "^" FormulaHasConstant = False If rg.HasFormula = False Then Exit Function FormulaString = rg.Formula For i = 1 To Len(FormulaString) FormulaCharacter = Mid(FormulaString, i, 1) If InStr(1, Operators, FormulaCharacter) 0 Then If IsNumeric(Mid(FormulaString, i + 1, 1)) Then FormulaHasConstant = True Exit Function End If End If Next i End Function ==================== If you are also concerned about string constants, or array constants, you could also test to see if the following character (at i+1) is a quote mark, or a brace. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box / Hard Coded List | Excel Discussion (Misc queries) | |||
Hard coded formula fix, VBA for column ordering | Excel Worksheet Functions | |||
do the same thing to multiple hard coded cells | Excel Worksheet Functions | |||
Convert Hard-coded cell values to constants | Excel Discussion (Misc queries) | |||
Index Sheet which summarizes all cells with hard coded data. | Excel Programming |