Click here to Skip to main content
14,580,625 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi all,
I have an Excel WorkBook with VBA macros, There is no problem with it on Excel.
As I try to open it on LO Calc, I got a lot of compatibility problems, I fixed them all but 1.
I fixed problems like conditional compilation (#If ... #End If) incompatibility.
I use Libre Office 6.1 Calc.
I use Libre Office 6.3 Calc.

I got 1 remaining error on this code:
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")
    re.Global = True
    If IsMissing(Balise) Then
        '   pas de balise, on cherche les valeurs numeriques
        re.pattern = "[0-9,.]+"

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

Error Message:
Erreur d'exécution BASIC.
'1'

Type: com.sun.star.script.CannotConvertException
Message: [automation bridge]UnoConversionUtilities<T>::anyToVariant 
Cannot convert the value of type :"com.sun.star.uno.XInterface"  to the expected Automation type of VARTYPE: 8

The execute is supposed to return collection of object.
Usage:
A1= "100x10x150"
A2= Extract(A1) => 100
A3= Extract(A1,2) => 10
A4= Extract(A1,3) => 150

What I have tried:

This code works perfectly in Excel, but fail to compil in Calc.
What did I missed?
Posted
Updated 25-Aug-19 4:59am
v4
Rate this:
Please Sign up or sign in to vote.

Solution 1

At a guess, it's to do at least in part with what is being passed to that function, probably in Chaine - and we have no idea what that is. Since the problem occurs at run time, you need both the macro and the data it is associated with (the spreadsheet) working together to try and fix it - and we have no access to either, let alone both!

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. A quick Google for "LibreOffice Calc debugger" should give you the info you need.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
   
Comments
Patrice T 25-Aug-19 2:34am
   
Usage:
A1= "100x10x150"
A2= Extract(A1) => 100
A3= Extract(A1,2) => 10
A4= Extract(A1,3) => 150
investigating :)
Patrice T 25-Aug-19 11:06am
   
"time for you to learn a new (and very, very useful) skill: debugging!"
It was a LO internal error that didn't gave access to debugger on error.
See S3
Rate this:
Please Sign up or sign in to vote.

Solution 2

There is a regular expression function in Libre Office:
REGEX[^]
This is for version 6.2, though; don't know if it is present in 6.1.
   
Comments
Patrice T 25-Aug-19 3:57am
   
Excel is my main app, I can't loose compatibility with Excel.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Adding the type of first parameter solved the problem.
Function Extract(Chaine As String, Optional Pos = 1, Optional Balise)

The debugger did not helped much because it is an internal error.
When Chaine is a Range, LO is unable to get the value of the cell to feed it as a string as parameter of Re.Execute.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100