Ask Your Question

Create user-defined function in javascript

asked 2019-11-06 21:18:54 +0100

afroveo gravatar image

I want to create a function called MAXCONS that counts the maximum consequent occurrences of a specified value within given range. E.g.

If A1="X", A2="X", A3="Z", A4="X", A5="Z", A6="Z", A7="Z", then MAXCONS(A1:A7, "X") should return 2, because the value "X" appeared maximum 2 consequent times. And MAXCONS(A1:A7, "Z") should return 3.

My questions: 1) Where can I learn how to do this? 2) Can I do this in Javascript? If yes, how? 3) Can you provide a sample/dummy function definition (without implementing the above logic), to which I can add the function body and use the function within a calc document?

Thank you!

edit retag flag offensive close merge delete


I have your same problem, I know javascript very well and I would like to use it in LO. JS allows things that the star basic does not allow, for example the use of collection (hashed objects)

However, I don't find the interface with star basic and LO documents so simple. So for now I postponed the problem

Just to get an idea, to start, I'll give you a few links I had found in the past. There is not much documentation available on js in the LO environment

torreone gravatar imagetorreone ( 2019-11-06 22:49:48 +0100 )edit


Know very little about Javascript but gave it a go some time back. Only posting to relay a bit more on what is out there (somewhat sparse). See my answer here:

Solved: How to call JavaScript function from Basic

but the OP could not get it to work. Instead this question was answered here:

My Basic call to a JavaScript does nothing

An answer mentions, this appears to be able to be done without macros.

Ratslinger gravatar imageRatslinger ( 2019-11-07 00:51:46 +0100 )edit

JS allows things that the star basic does not allow, for example the use of collection (hashed objects)

Dim MyColl As New Collection

See VBA documentation; does not need VBA support.

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-07 06:10:43 +0100 )edit

Thank you! It seems that using JS would be more complex than I expected. I coded the function in Basic.

public function MAXCO(vCellRangeValues as variant, val as string) as integer

    dim vCellValue as variant
    dim count as integer
    dim maxCount as integer

    for each vCellValue in vCellRangeValues
        if vCellValue = val then
            count = count + 1
            if count > maxCount then
                maxCount = count
            end if
            count = 0
        end if 

    MAXCO = maxCount
end function
afroveo gravatar imageafroveo ( 2019-11-07 13:20:11 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-11-07 00:31:46 +0100

m.a.riosv gravatar image

It's possible to do it with a formula without any macro.

image description

Adjusting the ranges:
CONCAT(A$1:A$9999) to the maximum of rows to analyze
ROW($1:$512) to the maximum expected of char's repetitions, to avoid slow down, reduce this one as much as possible.

  • SUMPRODUCT it's use only to no introduce the formula as array.
edit flag offensive delete link more


Thanks I tried it, but I get the error #NAME?

Here is the formula I tried: =SUMPRODUCT(MAX(LEN(CONCAT(A$1:A$9999)))-MIN(LEN(regex(CONCAT(A$1:A$9999),B2&"{"&ROW($1:$512)&"}",""))))

I get the same #NAME? error if I try: =regex(CONCAT(A$1:A$9999),B2&"{"&ROW($1:$512)&"}","")

My LO converts your ";" into ",". Not sure why and whether it is important.

My LO Calc settings (I tried both with wildcards and regex):

ON: "Enable wildcards in formulas" ON: "Enable regular expressions in formulas"

Formula Separators: Function , Array Column , Array row ;

Any ideas how to get it work?

afroveo gravatar imageafroveo ( 2019-11-07 12:29:24 +0100 )edit

Anyway, I coded it in Basic, so personal issue solved! Thanks!

afroveo gravatar imageafroveo ( 2019-11-07 13:28:36 +0100 )edit

Regex() function was introduced starting from version 6.2. So if you have older version installed, Regex() is not recognized, causing #NAME? error. “;” delimeter is acceptable in any LO localization, while “,” can be confused with decimal delimeter depending on your locale. “;” is locale independent and is advised to use as standart delimeter globaly and thus on this LO Ask site too.

SM_Riga gravatar imageSM_Riga ( 2019-11-07 16:11:04 +0100 )edit

Maybe your version it's a bit old and doesn't have REGEX() function.

m.a.riosv gravatar imagem.a.riosv ( 2019-11-07 23:11:38 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-06 21:18:54 +0100

Seen: 228 times

Last updated: Nov 07 '19