Click here to Skip to main content
15,029,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have an Excel 2003 workbook which include VBA functions.
Suddenly, the code crash with no error, no message, nothing, just a #Value in the cell. The only hint is that this PC is on Windows 10, most of other PCs are W7.

Crash is on
VB
Set re = CreateObject("VBScript.RegExp")

and this is not working either:
VB
Set re = New VBScript_RegExp_55.regexp


VB
Function Extract(Chaine, Optional Pos = 1, Optional Balise)
    If IsNumeric(Chaine) Then
        ' c'est numerique, on retourne la valeur
        Extract = Chaine
        Exit Function
    End If
    Set re = CreateObject("VBScript.RegExp") ' crash here
    re.Global = True
    If IsMissing(Balise) Then
        '   pas de balise, on cherche les valeurs numeriques
        re.pattern = "[0-9,.]+"

        Set A = re.Execute(Chaine)
        If A.Count >= Pos Then Extract = val(Replace(A(Pos - 1), ",", "."))
    Else
        '   la balise sert de séparateur
        re.pattern = Balise
        A = Strings.Split(re.Replace(Chaine, vbNullChar), vbNullChar)
        If UBound(A) >= Pos - 1 Then
            Extract = val(Replace(A(Pos - 1), ",", "."))
        End If
    End If
End Function


I am in Europe and parameter separator in Excel is ";" because comma is decimal separator.
Usage is :
BASIC
extract("100*6*300") => 100
extract("100*6*300";2) => 6
extract("100*6*300";3) => 300

First parameter is flat steel size in millimeters.
Second parameter is position of value I want, default is first value.

What I have tried:

Haven't found anything useful til now.
Do you have an idea of why I get this problem ?
Posted
Updated 9-Feb-21 4:28am
v5
Comments
thatraja 28-Jan-21 11:28am
   
what's the error message? Also version of Regexp?
Patrice T 28-Jan-21 11:36am
   
As said in question "the code crash with no error, no message, nothing, just a #Value in the cell. "
thatraja 28-Jan-21 11:40am
   
My bad, I missed that, sorry. Possibly version thing might creating issue, not sure. Can you check this one
https://stackoverflow.com/questions/21139938/vba-regexp-causes-compile-error-while-vbscript-regexp-works
CHill60 28-Jan-21 12:23pm
   
I can't reproduce this - what are you passing in as parameters so I can try to replicate the problem.
Have you tried replacing this with an explicit reference to Microsoft VBScript Regular Expressions to see if you get the same behaviour.
I really would have expected an error along the lines of Error 429 - there isn't anything suppressing that error in earlier code?
Patrice T 29-Jan-21 8:41am
   
It works on my PC W7 Excel 2003 and Excel 2010.
CHill60 29-Jan-21 11:36am
   
It does seem likely to be a versioning issue - have you tried Maciej's solution
Patrice T 29-Jan-21 12:39pm
   
Not yet.
Was macro was working on same PC last week with no change on PC or in vba code.

I'd avoid of using:
VB
Set re = CreateObject("VBScript.RegExp") 


I'd suggest to create Regex object via using reference to Microsoft VBScript Regular Expressions x.x.dll, where x.x is version number.

Then, you'll be able to create object via early binding instead of late binding. The reasons to use early biding are explained here: Use early binding and late binding in Automation - Office | Microsoft Docs[^]

Quote

Which form of binding should I use?



The answer to this question depends as much on the design of your project as anything else. Microsoft recommends early binding in almost all cases. However, there may be reasons for choosing late binding.

Early binding is the preferred method. It is the best performer because your application binds directly to the address of the function being called and there is no extra overhead in doing a run-time lookup. In terms of overall execution speed, it is at least twice as fast as late binding.


So, the recommeded way is:
VB
Dim regEx As New RegExp
With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .pattern = regexPattern
End With

'your code here


As the VBA compiler is an compiler and interpreter, it tries to create object as calls to the CoCreateInstanceAPI. See:
GetObject and CreateObject functions in VBA - Office | Microsoft Docs[^]
Visual Basic for Applications - Wikipedia[^]
   
v2
Last news: suddenly, it works again.
My search lead me to an information which is that MS desactivated RegEx on IE on W10 a few weeks ago.
Today, all workbooks that crashed since 3 weeks are working nice again.

In between, programmed a solution to bypass RegEx:
VB
Function Extract(Chaine, Optional Pos = 1, Optional Balise)
    If IsNumeric(Chaine) Then
        ' c'est numerique, on retourne la valeur
        Extract = Chaine
        Exit Function
    End If
    On Error GoTo erreur:
    ' Set re = CreateObject("VBScript.RegExp")
    Set re = New VBScript_RegExp_55.RegExp
    re.Global = True
    If IsMissing(Balise) Then
        '   pas de balise, on cherche les valeurs numeriques
        re.Pattern = "[0-9,.]+"
        Set A = re.Execute(Chaine)
        If A.Count >= Pos Then Extract = val(Replace(A(Pos - 1), ",", "."))
    Else
        '   la balise sert de séparateur
        '   n'est plus utilisé
        re.Pattern = Balise
        A = Strings.Split(re.Replace(Chaine, vbNullChar), vbNullChar)
        If UBound(A) >= Pos - 1 Then
            Extract = val(Replace(A(Pos - 1), ",", "."))
        End If
    End If
    On Error GoTo 0
    Exit Function
erreur:
    ' pour le cas ou RegEx ne fonctionne pas
    Db = 1
    Ln = 0
    Set A = New Collection
    For Scan = 1 To Len(Chaine)
        If Mid(Chaine, Scan, 1) Like "[0123456789,.]" Then
            If Ln = 0 Then
                Db = Scan
            End If
            Ln = Ln + 1
        Else
            If Ln <> 0 Then
                A.Add Mid(Chaine, Db, Ln)
                Ln = 0
            End If
        End If
    Next
    If Ln <> 0 Then
        A.Add Mid(Chaine, Db, Ln)
        Ln = 0
    End If
    '   pas de balise, on cherche les valeurs numeriques
    If A.Count >= Pos Then Extract = val(Replace(A(Pos), ",", "."))

End Function
   
Comments
Maciej Los 9-Feb-21 11:21am
   
Early binding rules!
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900