LibreOffice Calc Userdefine Function for Formula

[AniruddhaMohod]

I am using Ubuntu 22.04 and LibreOffice 24.8.4.2. I don’t have knowledge of any programming language. I want to create two user defined functions from following formulas. Please suggest required changes in the following.

The function ANIRUDDHA is supposed to extract First Name Vs First Name from a cell. Means if A1 cells contains the text State of Maharashtra Vs Aniruddha Purushottamrao Mohod the function will result in State Vs Aniruddha
Function ANIRUDDHA(a)
ANIRUDDHA=REGEX(C3, “((?:(?:\w+.|M/s|Mr.|Shri|Mrs.|P|PSO|Sk|P S O|P S|Md|Sau|Sd|Dr|Ms) )?\w+).* Vs ((?:(?:\w+.|M/s|Mr.|Shri|Mrs.|Sk|Md|Sau|Sd|Dr|Ms) )?\w+).*” , “$1 Vs $2” )
End Function

The function is suppose the show the difference between two dates which are not formatted as dates.
Function MOHOD(a,b)
MOHOD=DATEDIF(DATE(RIGHT(A2,4),MID(A2,4,2) ,LEFT(A2,2) ),DATE(RIGHT(B2,4),MID(B2,4,2) ,LEFT(B2,2) ),“y”) & " Years " & DATEDIF(DATE(RIGHT(A2,4),MID(A2,4,2) ,LEFT(A2,2) ),DATE(RIGHT(B2,4),MID(B2,4,2) ,LEFT(B2,2) ),“ym”) & " Months " & DATEDIF(DATE(RIGHT(A2,4),MID(A2,4,2) ,LEFT(A2,2) ),DATE(RIGHT(B2,4),MID(B2,4,2) ,LEFT(B2,2) ),“md”) & " Days"

End Function

[AniruddhaMohod]

With the help of Chat GPT the following code is working but only when it is put in Standard

Function DateDifference(startDate As String, endDate As String) As String
    Dim d1 As Date, d2 As Date
    Dim years As Integer, months As Integer, days As Integer

    ' Convert DD/MM/YYYY or similar format into a real date
    d1 = DateSerial(Right(startDate, 4), Mid(startDate, 4, 2), Left(startDate, 2))
    d2 = DateSerial(Right(endDate, 4), Mid(endDate, 4, 2), Left(endDate, 2))

    ' Calculate differences
    years = DateDiff("yyyy", d1, d2)
    months = DateDiff("m", DateAdd("yyyy", years, d1), d2)
    days = DateDiff("d", DateAdd("m", months, DateAdd("yyyy", years, d1)), d2)

    ' Format result
    DateDifference = years & " Years " & months & " Months " & days & " Days"
End Function

but the code

Function ExtractVsParties(text As String) As String
    Dim regex As Object
    Dim matches As Object
    Dim pattern As String
    Dim result As String

    ' Regular expression pattern similar to the REGEX formula
    pattern = "((?:(?:\w+\.|M/s|Mr\.|Shri|Mrs\.|P|PSO|Sk|P S O|P S|Md|Sau|Sd|Dr|Ms) )?\w+).* Vs ((?:(?:\w+\.|M/s|Mr\.|Shri|Mrs\.|Sk|Md|Sau|Sd|Dr|Ms) )?\w+).*"

    ' Create the regex engine
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = pattern
    regex.Global = False
    regex.IgnoreCase = True

    ' Execute regex match
    If regex.Test(text) Then
        Set matches = regex.Execute(text)
        result = matches(0).Submatches(0) & " Vs " & matches(0).Submatches(1)
    Else
        result = text ' Return original text if no match is found
    End If

    ExtractVsParties = result
End Function

is giving the error on the line
Set regex = CreateObject(“VBScript.RegExp”)

any solutions

[AniruddhaMohod]

@KamilLanda

[Wanderer]

@AniruddhaMohod

is giving the error on the line

Please: Always tell wich error, as there is more than one.
(When I started BASIC I got only “SN-Error in line xx”, but today there is much more available.
.
Anyway - VBScript hints to VisualBasic, wich is the environment created by Microsoft gor his Office. So your ChatGPT-Prompt may have created a solution for Excel, not for LibreOffice…

[mikekaganski]

@AniruddhaMohod

I am using Ubuntu 22.04

@AniruddhaMohod

error on the line
Set regex = CreateObject(“VBScript.RegExp”)

Did you mention your OS to ChatGPT? Just curious. Because indeed, there’s no “VBScript.RegExp” COM object available outside of Windows.

[KamilLanda]

@AniruddhaMohod
I will not solve the problems with some code from some ChatIdiot(s). I’m human-exact-good programmer and not some moonstruck fan of pseudomixed-and-randomized-CtrlCV unreliable pseudosolutions from ChatIdiot(s).
If you want to reapir a code from some ChatIdiot, I think it will be fair to request the fix from the authors or providers of ChatIdiot.

[AniruddhaMohod]

Then help me creating an user defined function for the formula

=REGEX(C3, “((?:(?:\w+.|M/s|Mr.|Shri|Mrs.|P|PSO|Sk|P S O|P S|Md|Sau|Sd|Dr|Ms) )?\w+).* Vs ((?:(?:\w+.|M/s|Mr.|Shri|Mrs.|Sk|Md|Sau|Sd|Dr|Ms) )?\w+).*” , “$1 Vs $2” )

[AniruddhaMohod]

I tried mentioning OS aa ubuntu. I also tried Copilot, Gemini and Meta AI. But nothing is working.

[mikekaganski]

It just tells something about the importance of internet search skill. People relying on magical understanding by engines / AI will always lose to people who excel that skill. E.g., searching for “macro regex” here on this Ask would bring you e.g. need calc macro regex help, which has this:

@mikekaganski

For using regular expressions in Basic, see here

… which, in turn, leads to another forum’s answer with code creating and using the built-in regex engine. And you can change your function like this:

Function ExtractVsParties(text As String) As String
    Dim regex As Object
    Dim matches As Object
    Dim pattern As String
    Dim result As String

    ' Regular expression pattern similar to the REGEX formula
    pattern = "((?:(?:\w+\.|M/s|Mr\.|Shri|Mrs\.|P|PSO|Sk|P S O|P S|Md|Sau|Sd|Dr|Ms) )?\w+).* Vs ((?:(?:\w+\.|M/s|Mr\.|Shri|Mrs\.|Sk|Md|Sau|Sd|Dr|Ms) )?\w+).*"

    ' Create the regex engine
    Set regex = createUnoService("com.sun.star.util.TextSearch")
    Dim opts As New com.sun.star.util.SearchOptions
    opts.searchString = pattern
    opts.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
    opts.transliterateFlags = com.sun.star.i18n.TransliterationModules.IGNORE_CASE
    regex.setOptions(opts)

    ' Execute regex match
    matches = regex.searchForward(text, 0, Len(text))
    If matches.subRegExpressions > 2 Then ' match 0 is the whole found text; plus two submatches
        result = Mid(text, matches.startOffset(1) + 1, matches.endOffset(1) - matches.startOffset(1)) & " Vs " & Mid(text, matches.startOffset(2) + 1, matches.endOffset(2) - matches.startOffset(2))
    Else
        result = text ' Return original text if no match is found
    End If

    ExtractVsParties = result
End Function

[fpy]

How to call LO Calc functions from a LO Basic function (macro)?

If you already have a cell object you can check for the properties .Type and .FormulaResultType. If you passed a cell reference to the formula in Calc and your user function does not specify a type for the respective parameter, the parameter gives access to a Variant variable which you can can ask for its actual type with the help of the Basic function TypeName(). Any subroutine (even running for a non-Calc compoinent) can create a service for calling Calc functions. To call a function you ne…

[Lupp]

If a user often needs FunctionAccess(), my “Code Snippet” can help.
If function access is needed frequently, it may be justified to create a module for the code from the above linked URL in “My Macros…” Standard library, and to place the global variable there in the respective module.