285 questions
0
votes
1
answer
44
views
Totals from specific parts of a large spreadsheet by row and column
Here is a large spreadsheet with several years' worth of data in a lot of categories.
Y = year, M = month, D = day
A B C D E
1| Y | M | D | 1 | 2 |... Initial dataset
2| 19 | 1 | 1 | ...
- 1
2
votes
1
answer
110
views
How to separate built-in object from user objects?
I'm trying to write a function TypeNameEx which will return a string which, using a call to VarType(), effectively identifies the type of a a (variant) variable, eg VarType 2 returns integer and ...
- 21
0
votes
1
answer
60
views
The "Sheet" function in Uno FunctionAccess always returns "2"
This function:
Function SheetIndex() As Long
Dim oFunctionAccess As Object
oFunctionAccess = CreateUnoService("com.sun.star.sheet.FunctionAccess")
SheetIndex = oFunctionAccess....
- 1,137
0
votes
1
answer
91
views
MyEval creates a circular reference, how is this possible?
When there are multiple cells using this UDF, they give results but they are recalculated endlessly and most of the time most of the cells give circular reference error (Err:522):
Option Explicit
...
- 1,137
1
vote
1
answer
60
views
LO Basic: ThisComponent.CurrentController does not exist when the spreadsheet is started
After the spreadsheet is fully started, then it works, but not on startup, resulting in some cells not calculated.
I'm talking about a User Defined Function (UDF):
Function Eval(Formula As Variant) As ...
- 1,137
1
vote
0
answers
62
views
When loading the library for the first time via GlobalScope.BasicLibraries.loadLibrary, an error occurs. Is that correct?
Environment: LibreOffice Calc
My program loads the ScriptForge library when a button is clicked:
Function ParseInequality(inequality As String) as Variant
GlobalScope.BasicLibraries.loadLibrary(&...
- 147
0
votes
2
answers
95
views
What is the equivalent syntax of this "$@" from the bash language?
Is there an equivalent of the bash language "$@" in the libreoffice Basic language to pass all the parameters passed to a function, to a variable?
As an example:
Function prova(a, b, c)
...
- 1,137
0
votes
0
answers
62
views
SQL Server does not accept polish specific characters when entering data from Libre Base
I'm using Libre Base to insert data into a SQL Server database. In the connection properties of Libre I have selected the ODBC driver with Middle Europe (Windows-1250/Latin 2) as the character-set. ...
- 1
0
votes
0
answers
97
views
BASIC runtime error, argument is not optional
I'm trying to write a basic function to check if the text in a cell is bolded, but I keep getting the mentioned runtime error on the second line. I've looked up various documentations and tried other ...
1
vote
1
answer
74
views
How to use userDefinedProperties to record persistent variables between 2 calls
i have hard to find solutions to record some variables i would like to be persistent during session (untill i close libreoffice-cal application).
First, i would like to know if i can record complex ...
- 87
0
votes
1
answer
58
views
Is there any method to get information of dll used when clalling a function from a VBScript macro?
When I run a VBScript that calls a function, is it possible to somehow get information about which .dll library the WScript engine used to implement the function?
I haven't tried anything yet due to I ...
- 1
0
votes
0
answers
113
views
How to speed up Rnd in LibreOffice Calc?
I am simulating the life of wolves and rabbits. Wolves should move to a random cell. When I run it, it works relatively slowly at first and then slows down even more; I suspect this is due to Rnd. How ...
- 147
0
votes
2
answers
82
views
Remove command from a toolbar with LibreOffice Basic
I want to remove a command from a toolbar / set the command to inactive. I can open the dialog, but I can't go any furter.
sub SetStatusOfCommand
dim document as object
dim dispatcher as object
...
1
vote
1
answer
147
views
Python User Defined Function for LibreOffice Calc
I'm trying to create a Python UDF that operates on a range of spreadsheet values and returns a single value.
From what I've gleaned, I need a Basic wrapper that calls my Python script. Here is what ...
- 107
1
vote
2
answers
70
views
Anagram check in Libreoffice Calc
How can I check in LibreOffice Calc whether two words have the same letters, i. e. are anagrams? Is this doable with the built-in functions?
Edit:
My idea is: convert the letters in both words to ...
1
vote
1
answer
200
views
Libreoffice basic: how to pass CellRange variables to python scripts
I'm working on my first python script for Libre office calc.
Following various guides I installed APSO and successfully created a Basic wrapper that calls the python script.
This is its signature:
...
- 1,289
1
vote
1
answer
1k
views
How to select a range of cells in a macro in Calc, using Basic, not Python?
(Context is below, for those who want to know.)
I am slowly developing a set of macros for LibreOffice Calc, working from "record macro" and then trying to edit until I have a general ...
- 535
0
votes
1
answer
117
views
If I create a recursion with GoTo in LibreOffice Basic, will the stack overflow?
I'm working on developing a timer that performs some action every 30 ms. I want to do this using only LibreOffice Basic. I tried creating a recursion, but the stack overflowed. I also tried using ...
- 147
1
vote
0
answers
77
views
How to run Application.Ontime directly in LibreOffice Basic?
I have written a code that executes every second. I have a global array that I modify, but with each run of the Logic function, this global variable is empty, from which I conclude that it is being ...
- 147
0
votes
0
answers
68
views
How slow are For, While, and recursion in LibreOffice Basic?
When the button is pressed, I want an infinite loop (For/While/Recursion or for + recursion, as the stack limits infinite recursion) to start, which will do something and then Wait 50 or any other ...
- 147
0
votes
1
answer
223
views
How to create array in LO basic with support VBA 1
i have a code
Const maxPump As Byte = 3
Public ArrPump(1 to maxPump) As TPump
it work until i write
Option VbaSupport 1
after i got error like this
basic error '9' index out of defined range
i use ...
- 147
0
votes
1
answer
77
views
LO Calc BASIC wrapper for python array functions
How can I construct simple LibreOffice basic wrapper, that could read an arbitrary array from LO calc spreadsheet to preform some python function on it, say add all the values?
Here is example of some ...
- 261
0
votes
1
answer
22
views
Error inserting new Sheet not data is not getting stored
Sub ImportSheetFromAnotherWorkbook2
' Declare variables
Dim oSourceDoc As Object
Dim oTargetDoc As Object
Dim oSourceSheet As Object
Dim oTargetSheet As Object
Dim ...
2
votes
1
answer
160
views
How to convert solution from MS Excel (VBA) to Libreoffice-Calc
How to do it in Libreoffice-Calc?
OnTime for less than 1 second without becoming Unresponsive
This is the solution:
Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, _
...
- 139
-1
votes
1
answer
75
views
prevent blank lines from being numbered [closed]
I have a long list of items that I would like to be able to number without spending an hour numbering each piece by hand, and without every blank line also having a number.
Is there a way to do that?
- 32.6k
1
vote
2
answers
127
views
Copy values and formats with a BASIC-Script
I want to copy the values, the formats and all conditional formats from an LibreOffice-Calc worksheet-range (A1:H100) to another (new/empty) worksheet of the same workbook.
This is, what I do actually:...
- 23
0
votes
1
answer
176
views
How do I turn the first word in a cell into a hyperlink using a macro in LibreOffice Calc
I have a large spreadsheet and want to automate the process of converting the first word of each cell into a hyperlink, but keeping the rest of the text as it is. I.e. linktext appendedtext to be ...
0
votes
1
answer
63
views
saving a macro that formats cells as text to an extension
I have this calc macro that is working as expected.
It formats the cells as "text" so that I can type February 2024 without any problem.
Is it possible to save this macro as an extension?
...
- 32.6k
0
votes
0
answers
927
views
How to select entire row in a Libreoffice Basic macro?
I'm trying to find out how I could select an entire row in a Calc sheet using the Macro language.
Macro recording is of no help, as it doesn't reveal the secret, even if I have recording on while ...
3
votes
1
answer
195
views
A macro to remove the first space
Is it possible to write a macro for the problem explained in this discussion?
https://ask.libreoffice.org/t/can-you-auto-delete-the-space-before-an-auto-corrected-word/101757
If the following sequence ...
- 32.6k
0
votes
1
answer
196
views
Convert working VBA macro into libre basic
I have a simple VBA macro for word that works fine. I searched the libre forum and also tried to find any documentation regarding the libre object model, I was not able to find anything that would ...
- 183
0
votes
2
answers
403
views
Unable to change shape color using macro in LibreOffice Impress presentation mode
I'm trying to use Basic macros to make a shape change its color when clicked during presentation mode. My goal is to create versatile code that works on any slide, regardless of the number of shapes.
...
1
vote
1
answer
84
views
Remove save keyboard shortcut using script
Let's assume I need to remove the Ctrl+S keboard shortcut that is used to save a document in Libreoffice writer.
I can change the function using this code...
<node oor:name="S_MOD1&...
- 32.6k
0
votes
1
answer
556
views
Paste formula in Libre Calc using Basic macro
I am trying to replicate an excel macro in Libre Calc using the native Basic.
I've come up against what I feel should be a simple problem/resolution (for someone who has a clue what they are doing).
I ...
- 3
0
votes
1
answer
96
views
LibreOffice, Writer, Macros, How to replace a embedded object formula with its string contents
I have a Open Office Writer document with math formulas (com.sun.star.formula.FormulaProperties) and i want to make a macro that replace that formula objects with its string contents.
I am very novice!...
- 3
0
votes
1
answer
567
views
Excel Formula to validate valid IP and valid URL in librecalc excel sheet
Excel formulas to validate valid IP and valid URL in librecalc excel sheet.
For Valid IP got one formula as below,
=AND((LEN(A1)-LEN(SUBSTITUTE(A1,".","")))=3,ISNUMBER(SUBSTITUTE(...
3
votes
0
answers
721
views
Writing libreoffice-calc macros in Visual Studio Code
I'd like to write libreoffice-calc macros (libreoffice-basic) in Visual Studio Code instead of the embedded macro editor.
I know xlwings enables such functionality when working with excel, as ...
- 128
1
vote
1
answer
943
views
Insert image into LibreOffice as Link using API/Macro
Starting from Pitonyak, I adapted some code that will insert a picture into a Writer/Draw/Impress/Calc document
import uno
from com.sun.star.awt import Point, Size
furl = 'https://upload.wikimedia.org/...
- 5,943
0
votes
1
answer
92
views
libeoffice conected to oracle registeroutparameter to return resultset
Good Morning,
I have the following code:
Dim oraCon As Object
Sub Main
openOracle("DB_LOC")
stmt = oraCon.prepareCall("VARIABLE x REFCURSOR DECLARE V_Sqlstatement Varchar2(...
- 87
0
votes
1
answer
428
views
LibreOffice Calc Input Box and mathematical expressions
I'd like to use an input box to get a mathematical expression, (1+7, 6-2, 1+5-6, etc) and use a macro to evaluate that expression. I've tried to use Expression(), but that doesn't seem to do anything. ...
0
votes
1
answer
110
views
Libre Calc - Populate Cells from CSV string
How do I populate multiple cells from a macro function in Libre Calc from a csv string?
Sample function
Function GetCsv
Dim csvData As String
csvData = "column1,column2,column3" &...
- 276
1
vote
1
answer
188
views
LibreOffice Impress macro to read a slide's animation event duration and delay times
I'm writing a basic macro in LibreOffice Impress which builds a custom slide show that will run for a specified length of time. The slide show consists of a set of auto-advancing slides. My problem is ...
- 69
0
votes
1
answer
254
views
How to return a matrix in a libre office calc basic function?
To enter a function that returns a matrix, I know I have to select all the cells for the returning variant and press CTRL SHIFT ENTER. The function can be defined as this minimal example:
Function ...
- 1,876
1
vote
1
answer
261
views
How to store in a variable, the range address from a user selected range
I want a variable to store the "range address" (e.g. "B2:E4") of a range that was selected by a user.
I'm trying things along these lines:
Sub print_selected_range
Dim oSheet as ...
- 197
0
votes
2
answers
110
views
If (Windows-)Username equals NAME then change Background-Color from Cell
I'm using LibreOffice-Calc and I want to "read" the Windows-Login username. If a user opens the spreadsheet, it should change the specific Cell-Background-Color to yellow.
In the Spreadsheet ...
- 62
0
votes
0
answers
98
views
LibreOffice basic get all checkboxes
I'm currently working on a project that involves migrating VBA macros to LibreOffice Basic macros. However, I've encountered a challenge with one particular macro where I need to iterate over all ...
- 33
0
votes
1
answer
128
views
A way to add subroutines by code at runtime in libreoffice basic
Is there a way to add / delete subroutines or functions by code at runtime in libreoffice basic?
I would very like to be able to create, delete and run subroutines or functions by code at runtime. I ...
- 41
0
votes
1
answer
122
views
creating a new module in a chosen library using libreoffice basic
How can I create a new module in a chosen library using libreoffice basic code?
I've tried:
ThisComponent.getLibraryContainer().getByName("C_calc").getModuleContainer().insertByName("...
- 41
0
votes
1
answer
160
views
LibreOffice CLI Highlighting Words in Generated PDF using Macros
I wanted to make a generic system in my application where I could input a file of a readable type, let's say a Text Document and convert it to a PDF.
My research has lead me to the CLI version of ...
- 33
0
votes
1
answer
74
views
Is LibreOffice's SimpleCommandMail handling of commas for Title, Body and Attachment buggy?
(I am new at stackoverflow, I apologize if I fail to meet established styling conventions). In LibreOffice Basic it is possible to construct a mail to be sent with the default system mail client. This ...
- 21