RegExp in VBA

[prograplex]

Hi,

I am trying to use RegExp in VBA to find all named ranges in a formula.

To find it, I'm basically looking for a string that doesn't end with a ( and that is only made up of a-z, A-Z, 0-9, \, _.

I'm a bit confused about atomic groups and look arounds. As far as I understand it:

strPattern = "[^=]((?!\()[a-zA-Z0-9\\_])*"

will ignore the = sign at the start of the formula, then look ahead for an open bracket, and if there isn't one, it will collect all the relevant characters. If there is an open bracket however, it ignores everything left of the open bracket.

This isn't working as I though and I don't understand why.

Any help would be greatly appreciated.

Thanks.

 

[pgc01]

Hi
Welcome to the board

Finding a named range in a formula seems to me a difficult task, unless you have a very specific type of formula in mind.

Your post makes no sense to me. For ex. in the formula

=23+45

you are saying that 23 is a named range, because

a string that doesn't end with a ( and that is only made up of a-z, A-Z, 0-9, \, _.

Can you explain?

 

[prograplex]

Oh sorry i search for a letter, \ or _ as the first character. I've been testing with letter only named ranges and formulas so far.

 

[prograplex]

I think the crux of my problem is excluding an entire match if the next character is a (

 

[pgc01]

Oh sorry i search for a letter, \ or _ as the first character. I've been testing with letter only named ranges and formulas so far.

Hi

Still no go.

For ex.

=A1+1

You are saying that A1 is a named range.

As I said it seems to me that finding a named range is not an easy task.

 

[Peter_SSs]

Not using regular expressions, but see if this is any use. Not greatly tested.

Sub test()
  Dim pr As Range
  Dim NamedRangesUsed As String
  Dim bit As Variant
  
  On Error Resume Next
  Set pr = Range("A1").Precedents
  On Error GoTo 0
  If Not pr Is Nothing Then
    For Each bit In Split(pr.Address, ",")
    On Error Resume Next
    NamedRangesUsed = NamedRangesUsed & ", " & Range(bit).Name.Name
    On Error GoTo 0
    Next bit
    MsgBox "Named ranges used: " & Mid(NamedRangesUsed, 3)
  End If
End Sub

 

[pgc01]

Hi Peter

Not an easy task.

I tried with a simple formula. In Sheet1!A1

=FirstName

FirstName is a workbook named range defined as

=MyNames!$A$1

Your code did not give me the name. You are using .Precedents that only works in the local worksheet.

I remember some years ago doing some code to extract names out a formula and I remember it was a lot or work.

 

[prograplex]

I don't mind if it picks up normal formulas.i already have a reg exp pattern that does that so this can replace that. As long as it picks up the named ranges and not formulas

 

[Peter_SSs]

Finding a named range in a formula seems to me a difficult task, ..

PGC, I agree, but it does to some extent depend on your other remark:

.. unless you have a very specific type of formula in mind.

So if the formula does only use named ranges from that sheet, my suggestion may be of some use.


@prograplex
If the named ranges could be on different sheets, then possibly the following may help.
I do note that this below suggestion may return a false positive if there was, say, a named range "elephants" and the formula was:
="I saw " & SUM(K1:K3) & "elephants today"
I have not tried to exclude this as I don't know of your circumstances, see ** below.

This will also return the same name multiple times if it is used multiple times in the formula. Not sure if that would be desired in your case.

** It all comes back to knowing a bit more about what you actually have (or might have) in your workbook.
It would be helpful to know a bit more about that so that we are not trying to implement some code that would cover every possible scenario if many are not relevant to you.

Sub FindRangeNames()
  Dim RX As Object, M As Object
  Dim Itm As Variant
  Dim NamedRangesUsed As String
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "[\\_A-Za-z][A-Za-z0-9\._]*"
  Set M = RX.Execute(Range("A1").Formula)
  For Each Itm In M
    On Error Resume Next
    NamedRangesUsed = NamedRangesUsed & ", " & Range(Itm).Name.Name
    On Error GoTo 0
  Next Itm
  MsgBox "Named ranges used: " & Mid(NamedRangesUsed, 3)
End Sub

 

[pgc01]

Hi

Like I said this will not solve the problem, but since you asked, this will extract sequenced of characters:

- starting with _ \ a-z A-Z
- followed by some characters _ \ a-z A-Z 0-9
- not ending in (

Run:

Sub Test()
Dim sFormula As String
Dim regex As Object, regexMatches As Object
Dim j As Long

sFormula = "=3+Name1(4)+Name2+7+(Name3+Name4)*(Name5(34,56)/Name6)-F1"

Set regex = CreateObject("VBScript.RegExp")
With regex
    .Pattern = "([a-z\\_][a-z0-9\\_]+)(?![a-z0-9\\_]*\()"
    .IgnoreCase = True
    .Global = True

    Set regexMatches = .Execute(sFormula)
    If regexMatches.Count = 0 Then
        MsgBox "Not found"
    Else
        For j = 1 To regexMatches.Count
            MsgBox "Result " & j & ": " & regexMatches(j - 1).submatches(0)
        Next j
    End If
End With

End Sub

Remarks
- Name1 and Name5 are not matched because they are followed by a (
- F1 is matched although it's not a named range

... and, as I said, this does not solve the problem of extracting named ranges out of a formula (not by far), simply extracts strings according to the assumptions stated supra.

Please test.
​

Kind regards
PGC

To understand recursion, you must understand recursion.

​

Edit Post Reply Reply With Quote