Click here to Skip to main content
15,867,833 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:
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")
    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

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!
 
Share this answer
 
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
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.
 
Share this answer
 
Comments
Patrice T 25-Aug-19 3:57am    
Excel is my main app, I can't loose compatibility with Excel.
Adding the type of first parameter solved the problem.
VB
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.
 
Share this answer
 

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