Legacy excel VBA code suddenly fail on W10

Question

0.00/5 (No votes)

See more:

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 28-Jan-21 3:49am

Patrice T

Updated 9-Feb-21 4:28am

v5

Add a Solution

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.

3 solutions

Add a Solution

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

thatraja · Accepted Answer · 2021-01-28T06:02:00

I did quick search & found these(with out error message). These might help you for sure.
How to Fix Runtime Error 429 on Windows 10 and Earlier Versions - Appuals.com[^]
ActiveX component can't create object or return reference to this object (Error 429) | Microsoft Docs[^]
You receive run-time error 429 when you automate Office applications[^]
Creating Word Application using Excel VBA: Run-time error '429': ActiveX component can't create object - Stack Overflow[^]

Maciej Los · Accepted Answer · 2021-01-28T21:01:00

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[^]

Patrice T · Accepted Answer · 2021-02-09T04:28:00

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

Legacy excel VBA code suddenly fail on W10

3 solutions

Solution 1

Solution 2

Which form of binding should I use?

Solution 3

Add your solution here

Preview 0

Legacy excel VBA code suddenly fail on W10

3 solutions

Solution 1

Solution 2

Which form of binding should I use?

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us