E
EagleOne
2003, 2007
What is the smartest way to be able to keep a "." (period) or a number from an cell address or
worksheet name/link or a workbook name/link being considered in the Case line below:
Dim CheckStr as string
CheckStr = Activecell.formula
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
.....
' Is current character a Number or a "." (period)?
Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46
...... <Do things>
I need to consider the complete formula string so that I can obtain the starting position of every
constant (defined as a number preceeded by a mathmatical operator) in the formula string. So I
cannot delete anything from CheckStr.
In short, I do not want the numbers from a cell address, a sheetname, workbook name or from a
directory link to be considered.
For example, in the formula below:
=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+[C:\123]'Closing'!E31
I do not want the "." or 26 or 31 or 31 or 123 to be considered "acceptable" by:
Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46
My thoughts have included but not limited to:
1) ActiveWorkbook.LinkSources(xlExcelLinks)
2) Toggling Booleen True/False for alternate " ' " in the formula string (elim w/s references)
3) Toggling Booleen True/False for alternate "[" then "]" for links
Bottom line the VBA code to effectively:
Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46
(except "." and numbers which are not constants)
Hopefully there is an easy way to do this that I have not considered. ??
TIA EagleOne
What is the smartest way to be able to keep a "." (period) or a number from an cell address or
worksheet name/link or a workbook name/link being considered in the Case line below:
Dim CheckStr as string
CheckStr = Activecell.formula
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
.....
' Is current character a Number or a "." (period)?
Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46
...... <Do things>
I need to consider the complete formula string so that I can obtain the starting position of every
constant (defined as a number preceeded by a mathmatical operator) in the formula string. So I
cannot delete anything from CheckStr.
In short, I do not want the numbers from a cell address, a sheetname, workbook name or from a
directory link to be considered.
For example, in the formula below:
=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+[C:\123]'Closing'!E31
I do not want the "." or 26 or 31 or 31 or 123 to be considered "acceptable" by:
Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46
My thoughts have included but not limited to:
1) ActiveWorkbook.LinkSources(xlExcelLinks)
2) Toggling Booleen True/False for alternate " ' " in the formula string (elim w/s references)
3) Toggling Booleen True/False for alternate "[" then "]" for links
Bottom line the VBA code to effectively:
Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46
(except "." and numbers which are not constants)
Hopefully there is an easy way to do this that I have not considered. ??
TIA EagleOne