Extract All Excel Function Types From A String using Regex in VB

Question

How can i extract excel functions (only their names) from a string in visual basic?

For example i have: Sin(B6)*Sum(A2:A4) So i want to find a way to have "Sin" and "Sum" from this.

Note: I already have a code to extract cell addresses, it might give you a hint to help me out.

Thank you.

Dim result As Object
Dim testExpression As String
Dim objRegEx As Object
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True
objRegEx.Pattern = """.*"""  ' remove expressions
testExpression = Mystring
testExpression = objRegEx.Replace(testExpression, "")
objRegEx.Pattern = "(([A-Z])+(\d)+)"  'grab the address
    Dim i As Long
Dim k As Long
If objRegEx.test(testExpression) Then
    Set result = objRegEx.Execute(testExpression)
    If result.Count > 0 Then

        For Each Match In result

k = Len(Match.value)
i = 1
Do Until (i > k) Or (IsNumeric(Mid$(Match.value, i, 1)))
    i = i + 1
Loop

Debug.Print Match.value

        Next Match
       End if

Answer 1

Assuming a function (call) starts with a decent letter ([A-Z]), followed by at least one more identifier-character (\w+: letter, number, _), followed by optional withspace and the opening parentheses of the argument list:

  Dim aTests : aTests = Array( _
      "Sin(B6)*Sum(A2:A4)" _
    , "sum_02(B6) * Do_what_I_Mean( A2:A4 )" _
    , "Sum(Sum(Sum(A2:A4)))" _
    , "NoArgsFunc()" _
  )
  Dim reFunc : Set reFunc = New RegExp
  reFunc.Global     = True
  reFunc.IgnoreCase = True
  reFunc.Pattern    = "([A-Z]\w+)\s*\("
  Dim sTest
  For Each sTest In aTests
      WScript.Echo "----", qq(sTest)
      Dim oMT
      For Each oMT In reFunc.Execute(sTest)
          WScript.Echo qq(oMT.SubMatches(0))
      Next
  Next

output:

---- "Sin(B6)*Sum(A2:A4)"
"Sin"
"Sum"
---- "sum_02(B6) * Do_what_I_Mean( A2:A4 )"
"sum_02"
"Do_what_I_Mean"
---- "Sum(Sum(Sum(A2:A4)))"
"Sum"
"Sum"
"Sum"
---- "NoArgsFunc()"
"NoArgsFunc"

P.S.

Your RegExp to reduce string literals fails because of the meaning of . (everything exept EOL) and the greediness. Test it with "YourRegExp("""") & Sucks("""")" and consider a pattern like """[^""]*""" and a replacement with """""".