ok, i combined them all into a case by case function, reversed the order such
that my old sf4 is now case 1 and subsequently the default, and this is what
i have come up with...any suggestions for optimization or anything else?
Function SF(rg As Range, Optional N As Long = 1)
Dim str As String
Dim re As Object, mc As Object
Dim sRepl As String
Dim c As Range, rg2 As Range
Dim t() As String
Dim i As Long
If rg.Count <> 1 Then 'test for valid single cell reference
SF = CVErr(xlErrRef)
Exit Function
End If
Select Case N
Case Is = 1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
str = rg.Formula
Set re = CreateObject("vbscript.regexp")
re.Global = False
'Pattern should recognize only cell references in range A1:IV65536
'Does NOT recognize range reference, e.g: A1:A10
re.Pattern = "([^:$]|^)\$?\b(([A-Z]|[A-I][A-V])\$?([1-9]\d{0,3}" & _
"|[1-5]\d{0,4}|6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6]))\b(?!
"
Do Until re.test(str) = False
Set mc = re.Execute(str)
sRepl = mc(0).submatches(0) & Range(mc(0).submatches(1)).Text
str = re.Replace(str, sRepl)
Loop
'This pattern recognizes ONLY range references
re.Pattern = "\$?\b(([A-Z]|[A-I][A-V])\$?([1-9]\d{0,3}|[1-5]\d{0,4}" _
& "|6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6]))\b:\$?\b(([A-Z]|[A-I]" _
& "[A-V])\$?([1-9]\d{0,3}|[1-5]\d{0,4}|6[0-5][0-4]\d\d|655[0-2]" _
& "\d|6553[0-6]))\b"
Do Until re.test(str) = False
Set mc = re.Execute(str)
Set rg2 = Range(mc(0))
ReDim t(rg2.Count - 1)
i = 0
For Each c In rg2
t(i) = c.Text 'The .Text property returns the decimal places
from the cell referenced, and can only return a maximum of 1024 characters
from the cell. If your cells might contain longer data, you will need to use
the .Value property.
i = i + 1
Next c
sRepl = Join(t, ", ")
str = re.Replace(str, sRepl)
Loop
SF = str
Case Is = 2
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
str = rg.Formula
Set re = CreateObject("vbscript.regexp")
re.Global = False
'Pattern should recognize only cell references in range A1:IV65536
'Does NOT recognize range reference, e.g: A1:A10
re.Pattern = "([^:$]|^)\$?\b(([A-Z]|[A-I][A-V])\$?([1-9]\d{0,3}" & _
"|[1-5]\d{0,4}|6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6]))\b(?!
"
Do Until re.test(str) = False
Set mc = re.Execute(str)
sRepl = mc(0).submatches(0) & Range(mc(0).submatches(1)).Text
str = re.Replace(str, sRepl)
Loop
'This pattern recognizes ONLY range references
re.Pattern = "\$?\b(([A-Z]|[A-I][A-V])\$?([1-9]\d{0,3}|[1-5]\d{0,4}" _
& "|6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6]))\b:\$?\b(([A-Z]|[A-I]" _
& "[A-V])\$?([1-9]\d{0,3}|[1-5]\d{0,4}|6[0-5][0-4]\d\d|655[0-2]" _
& "\d|6553[0-6]))\b"
Do Until re.test(str) = False
Set mc = re.Execute(str)
Set rg2 = Range(mc(0))
ReDim t(rg2.Count - 1)
i = 0
For Each c In rg2
t(i) = c
i = i + 1
Next c
sRepl = Join(t, ", ")
str = re.Replace(str, sRepl)
Loop
SF = str
Case Is = 3
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
str = rg.Formula
Set re = CreateObject("vbscript.regexp")
re.Global = False
'Pattern should recognize only cell references in range A1:IV65536
'Does NOT recognize range reference, e.g: A1:A10
re.Pattern = "([^:$]|^)\$?\b(([A-Z]|[A-I][A-V])\$?([1-9]\d{0,3}" & _
"|[1-5]\d{0,4}|6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6]))\b(?!
"
'should 0-2 be 0-3?
Do Until re.test(str) = False
Set mc = re.Execute(str)
sRepl = mc(0).submatches(0) & Range(mc(0).submatches(1)).Text 'The
..Text property returns the decimal places from the cell referenced, and can
only return a maximum of 1024 characters from the cell. If your cells might
contain longer data, you will need to use the .Value property.
str = re.Replace(str, sRepl)
Loop
SF = str
Case Is = 4
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
str = rg.Formula
Set re = CreateObject("vbscript.regexp")
re.Global = False
'Pattern should recognize only cell references in range A1:IV65536
'Does NOT recognize range reference, e.g: A1:A10
re.Pattern = "([^:$]|^)\$?\b(([A-Z]|[A-I][A-V])\$?([1-9]\d{0,3}" & _
"|[1-5]\d{0,4}|6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6]))\b(?!
"
Do Until re.test(str) = False
Set mc = re.Execute(str)
sRepl = mc(0).submatches(0) & Range(mc(0).submatches(1))
str = re.Replace(str, sRepl)
Loop
SF = str
Case Is = 5
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SF = rg.Formula
Case Else
SF = CVErr(xlErrNum)
End Select
End Function