ExcelBanter - View Single Post

Ron Rosenfeld

On Thu, 07 May 2009 19:08:44 -0400, wrote:

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

I'm not quite sure exactly what you want to do.

But if you have defined the value that you wish to extract from the string as
being any numeric value that is preceded by an arithmetic operator, then the
following code is one example of how to do that, using Regular Expressions:

===========================
Option Explicit
Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"
If re.test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub
========================================
--ron