[Solved] OpenOffice Basic Commands Documentation?

[RylanYoung]

Hi, I'm both new to OpenOffice, and to this forum, so forgive me if I have posted this in the wrong place or am asking a dumb question. I would very much appreciate some help or advise, though.

I am trying to make a macro that relates to RGB cell colors in Calc, based on a VBA script that I have managed to convert so far, despite knowing pretty much nothing about either of the programming languages, and have never written anything in either of them before. Honestly I'm surprised I managed to get as far as I have, but I suppose the script is pretty basic in principle.

One of the biggest issues I am having, however, relates to the documentation of OpenOffice basic. I have searched just about everywhere using every combination of words and phrasing I could think of and I cannot find any official documentation on the Basic commands. No list, no reference, nothing of help to me so far.

Like, where would I find something like: "getCellByPosition - select a cell in Calc with provided cell position in (position x, position y)" for ALL existing Basic commands? I only know what this does because I found a post with someone asking about how to change cell colors on Google. Any document or list I find only has a few commands with no real provided context, and the rest of the documentation is for Calc formulas, not macros. If I search for "getCellByPosition" every page or document I find seems to assume you already understand exactly what that does. How would I find out without someone specifically telling me what it does?


If it is of any relevance, the specific task I am trying to achieve right now is "normalizing" and RGB color, so the brightness isn't a factor visually, and it just displays the color, if that makes any sense. Basically if there is a very dark red-blue color that looks almost black, I would want to make it much easier to see without the hue/color being drastically altered, so I would see that the color is red-blue, and I could have the original darker RGB color visualized in a different cell. My line of thinking here is to somehow select the highest value out of the three RGB values, and then find what number to multiply it by to get it to 255, and then multiply the other two remaining color values by that same number (and then round to the nearest whole number). This way, the color is a bright as it can be visually without the actual color being different. I might be approaching that wrong, but I think it would work in theory.

So far, these colors are calculated in a macro, and the first thing I am having a problem with is finding a way to calculate the max color value. I would think, based on my very limited general programming knowledge, it would be something like "max(r,g,b)" but that doesn't seem to work. I know there is a very similar formula code, but that doesn't help when I need to make this calculation inside the macro.

I also have several rows of colors that need this calculation, but I can't figure out how to pass variables or arguments to the macro through the button it is activated with. Basically, OffsetX and OffsetY would be what I want to send to the macro, so I can color the correct cells.

I can't find documentation on how to do either of these fairly basic things.


I would very much appreciate some advise or pointers, thanks.

[FJCC]

It is important to distinguish between the OpenOffice Basic language, which is fairly simple, and the Application Programming Interface (API) that is very complicated and allows Basic or some other language to talk to the OpenOffice program and read or change document properties. Writing a for loop or declaring a variable is part of Basic, using getCellrangeByName() is part of the API.

There is a lot of information about macros in general at https://www.pitonyak.org/oo.php. You can use either the Macros Explained Book or the English Macro document. That is probably the best place to get information.

The Help section of OpenOffice itself has good information about the Basic language. Just set the drop down list at the top left to OpenOffice Basic.

There is a guide for Basic here. It is old but nothing much has changed.

You should get an object inspector like MRI. That will allow you to see the properties and methods of the objects you are working with. This forum has a short tutorial to get you started here.

[RusselB]

Welcome to the Forums.
The author Andrew Pitonyak has written many documents regarding OpenOffice Macros which can be downloaded from https://www.pitonyak.org/oo.php
This, imo, is your best resource.

[RylanYoung]

It is important to distinguish between the OpenOffice Basic language, which is fairly simple, and the Application Programming Interface (API) that is very complicated and allows Basic or some other language to talk to the OpenOffice program and read or change document properties. Writing a for loop or declaring a variable is part of Basic, using getCellrangeByName() is part of the API.

Thanks for the reply.

I don't know how to tell the difference between these things to be perfectly honest. If getCellrangeByName() is part of the API, I would guess getCellByPosition must be as well. So I guess I am looking for documentation on the API. I'm not seeing anything in the links you provided or within the books you recommended. Ctrl+F search with "getCellByPosition" only shows examples that are already using it, and not a list or anything describing specifically what it does.

The "getCellByPosition" command (or function or whatever it technically is) is pretty simple and obvious based on context clues, but I only know that it exists because I found it in an example in a forum post. Where would I find a list of all of the other commands and what they do outside of this? The forum posts obviously haven't always existed, and the people answering the questions must have learned from somewhere, right?

Welcome to the Forums.
The author Andrew Pitonyak has written many documents regarding OpenOffice Macros which can be downloaded from https://www.pitonyak.org/oo.php
This, imo, is your best resource.

Hi, thanks. Glad to be a part of the community.

Is this the official documentation? Or is Andrew Pitonyak simply someone creating documents based on his own knowledge?

I am looking for the official list of commands/functions that someone would use to learn without any prior knowledge of the API or OpenOffice Basic. I would prefer not to have to learn everything based entirely on forum post questions that have been answered or a community resource that provides specific examples using commands or functions that I don't already understand, and that's what I am struggling with here.

I keep going back to this example, but the "getCellByPosition" command/function/whatever it is, if I didn't know anything about OpenOffice Basic or the API, how would I find out what it does without 1. The obvious context in it's name. 2. By dissecting other macros and looking at their own context. 3. By finding community forum posts explaining it or 4. Any other entirely community-based resources.

I would usually expect simply typing "getCellByPosition" into google would bring up some kind of official manual or wiki with official documentation and syntax but that isn't the case here

[Zizi64]

Is this the official documentation? Or is Andrew Pitonyak simply someone creating documents based on his own knowledge?

There is not any "Official" documentation. The office suite, the descriptions was created and maintained by volunteers (except the root of the project: the old StarOffice version. The companies (Sun, Oracle, Apache) only incubated, supported the project. Yes, there was a commercial version (StarOffice), but I do not know if there were an official support for it. Andrew Pitonyak is a "volunteer" who has a wide knowledge about the open source office suites.

The api description (IDL reference) is good for the expert programmers:
https://api.libreoffice.org/
https://api.libreoffice.org/docs/idl/re ... f62806dfd1

I strongly recommend you to download, install and use one of excellent object inspection tools (XrayTool, MRI). Then you will able to list ("in situ"- in your code) of the properties, methods, interfaces of the programming objects like the "Document", the "Sheet", the "Cell", etc... what you need to control by your macros in the Calc application.
Note: the object inspection tools are created by volunteers too.

[FJCC]

The official API documentation is at https://www.openoffice.org/api/docs/com ... le-ix.html but you will find it to be overwhelming. I believe the best way to work is to start by reading the foundational material in one of Andrew Pitonyak's books so you can get familiar with terminology like method (rather than command). You can also learn a lot from the many examples in his books. For example, I opened his Macro Document, looked at the Calc section of the table of contents and found "6.3. Set cell value, format, string, or formula". That sounds like a good place to find out how to refer to a particular cell and indeed it uses getCellByPosition(). Once you can do a very few things, you can write the beginnings of a macro and then use MRI to explore the properties and methods. For example, once you can get a reference to a single sheet, I will call it oSheet, you can run

Code: Select all

mri oSheet

and see that among its methods are getCellByPosition, getCellRangeByName, getCellRangeByPosition.
I am sure you would like a more structured way to learn, but that is what is available as far as I know.

[Zizi64]

MRI:
The MRI was written by hanya:
memberlist.php?mode=viewprofile&u=212
https://github.com/hanya/MRI/wiki
https://extensions.libreoffice.org/en/e ... ction-tool

XrayTool:
The XrayTool was written by Bernard Marcelly:
memberlist.php?mode=viewprofile&u=71
https://berma.pagesperso-orange.fr/index2.html
usage:
https://wiki.openoffice.org/wiki/Extens ... #Xray_tool


I am using the XrayTool.

[RylanYoung]

Thanks for the replies. I guess I'll just have to figure out how to get Xray tool to work.

https://api.libreoffice.org/ seems good but there is no syntax for anything so I can't make great use of it.

I'll see if I can't figure this out.

[RylanYoung]

Seems there is no "maximum" or "minimum" method

[Zizi64]

I guess I'll just have to figure out how to get Xray tool to work.

https://wiki.openoffice.org/wiki/Docume ... s_directly
How you can use the Xray in the sample code linked above:

Code: Select all

Function SumCellsAllSheets()
  Dim TheSum As Double
  Dim i As integer
  Dim oSheets
  Dim oSheet
  Dim oCell
 
If (Not GlobalScope.BasicLibraries.isLibraryLoaded("XrayTool")) Then
GlobalScope.BasicLibraries.LoadLibrary("XrayTool")
End If

  oSheets = ThisComponent.getSheets()
Xray oSheets
  For i = 0 To oSheets.getCount() - 1
    oSheet = oSheets.getByIndex(i)
Xray oSheet
    oCell = oSheet.getCellByPosition(0, 1) ' GetCell A2
Xray oCell
    TheSum = TheSum + oCell.getValue()
  Next
  SumCellsAllSheets = TheSum
End Function

[RylanYoung]

I got XRay working by running "Xray ThisComponent" in my script. There are no methods for calculating the max value listed in the method section

[Zizi64]

I got XRay working by running "Xray ThisComponent" in my script. There are no methods for calculating the max value listed in the method section

The "ThisComponent" is a document object, but not a cell or a cell range. What maximum value you want to calculete based on a document object?????

You must get a Sheet from the document, then get a Cell Range from the sheet, then must get the Values from the Cells of the range. Then you can write the maximum searching loop in the StarBasic, or you can call the Cell function MAX() by an API calling.

[Zizi64]

The object can contain other objects, and pure (textual, or numeric) values as properties.

Calling the ThisComponent from the Calc, the result will be a Document object. This object contains many-many things...

The Document object contains some Sheet objects (and many more other things).
A Sheet object contain Cell Range objects and Cell objects (and many more other things : page style, ...).
The Cell object contain values (and many more other things: formulas, formatting properties...).

Sometimes even the properties are objects too, but not only one value.

[RylanYoung]

The "ThisComponent" is a document object, but not a cell or a cell range. What maximum value you want to calculete based on a document object?????

I gotta be honest, I'm totally lost here. I don't really know what a document object it. The script I'm editing is in a macro script (procedure?) I'm trying to pick the max value out of a list of 3 RGB values. These values have been taken from the appropriate cells and are already variables named r, g, and b inside a for loop. The original values aren't 0-255 so they needed to be converted, and checked for anything greater than the equivalent of 255.

I have the values set properly, and they appear as they should color-wise, but I want to "normalize" them so the color values are as high/bright as possible with the max being 255, and the other numbers being proportionally the same.

My current method of getting the max value to normalize the RGB color is this:

Code: Select all

	If r > b Then
		If r > g Then
			maxV = r
		End If
	End If

	If b > r Then
		If b > g Then
			maxV = b
		End If
	End If

	If g > r Then
		If g > b Then
			maxV = g
		End If
	End If

	mulV = 255/maxV
	
	r = r*mulV
	g = g*mulV
	b = b*mulV

Which seems to work, but it is very inelegant and probably inefficient too.

This isn't really what my topic is about though, so maybe I should mark this one as solved?

[JeJe]

Code: Select all

maxV =r
if g>maxV then maxV = g
if b> maxV then maxV = b

[Zizi64]

I don't really know what a document object it.

You must learn about the Object-oriented programming...

The "document object" means the whole document (Thiscomponent). When you get the Document object, you "can not see by your macro" the parts, elements of the document. You must get some inner objects first:
- the Sheets object from the Document object
- a Sheet object from the Sheets object
- a Cell/CellRange object from the Sheet
- and the Value/s from the cells/cell range.


syntactical convention - but it is optional - to name the Object variables as oDoc, oSheets, oSheet, oCell: the "o" means "Object".
(sText is a String variable iNumber is an integer... Note: The prefix of the name not mean an automatic type declatation)

[RylanYoung]

I see. I'll definitely have to look more into Object-oriented programming as you said. Thanks for the info.

Code: Select all

maxV =r
if g>maxV then maxV = g
if b> maxV then maxV = b

This is much simpler, not sure why I didn't think of that. Thanks.



All that's left to do is get the hyperlink button to pass arguments to the macro. Currently, I have it set up like this: vnd.sun.star.script:Standard.Module2.CellRGB?language=Basic&location=document&?offsetx=2&offsety=2

The values for offsetx and offsety don't seem to actually get passed to the macro, though.

Code: Select all

Dim offsetx As Integer
Dim offsety As Integer

Dim col As Integer
Dim row As Integer

col = 2 + offsetx
row = 2 + offsety

This just leaves col and row at 2, instead of 4

[Villeroy]

offsetx equals 0.
offsety equals 0.
2+0 equals 2.

https://wiki.openoffice.org/wiki/Docume ... .org_Basic is a good introduction into the StarBasic language and the tools which help you to insect Basic variables and Basic routine calls. Click on offsetx and hit F7. This adds offsetx to the watch window. Then hit F8 repeatedly to run your code in step-by-step mode. Observe the watch window.
The StarBasic language has almost nothing to do with the office API. The language is a vehicle, the API is the terrain. Your StarBasic vehicle is tiny, almost insufficient. The terrain is vast and rough. It is a bad idea to learn programming in such environment.

[RylanYoung]

offsetx equals 0.
offsety equals 0.
2+0 equals 2.

https://wiki.openoffice.org/wiki/Docume ... .org_Basic is a good introduction into the StarBasic language and the tools which help you to insect Basic variables and Basic routine calls. Click on offsetx and hit F7. This adds offsetx to the watch window. Then hit F8 repeatedly to run your code in step-by-step mode. Observe the watch window.
The StarBasic language has almost nothing to do with the office API. The language is a vehicle, the API is the terrain. Your StarBasic vehicle is tiny, almost insufficient. The terrain is vast and rough. It is a bad idea to learn programming in such environment.

Ah, very useful to know, I'll keep that in mind when I am having trouble, seems like a good way to diagnose issues.

I found out that the issue was that I needed to make a function to retrieve the variables from the URL, and after doing that, it works like a charm.

The code I have is working just about exactly how I want it to at this point. There is some advanced math that is specifically related to what I am working on, which I can't get into, but that mostly relies on knowledge of how the calculations are done within the program I am using this Calc sheet with. It isn't a priority to get that part working right now anyway.

Thanks so much for the help and advice. That last thing I need to learn how to do is "Paste Special - Operators - Average" in LibreOffice, but that is a separate issue entirely.

Thanks again to everyone who replied.

[Lupp]

Coming late to this thread, I only will drop a few words.
Look at https://api.libreoffice.org/docs/idl/re ... paces.html and make your decision if you prefer to see it as
a promise?
a warning?
a challenge?

From my experiences with the topic itself and also with related discussions in this forum and in a few others I visit now and then:
Everybody comes with a question in mind roughly of the type
"What tool from the API should I use in what way to get this Abc.... task done?"
Many have partial success finding somebody who alreadey knows, or who did something similar, or who can point to an example, or to a famous text...
Over many years now there was nobody who could point to a tool answering questions of that kind.
Documentation always is telling what a specific tool (XThisInterface, SomeService) might do for you, but often it is even difficult to find the hosting objects you can use to get access.
Tending to consider many things without sufficient knowledge I would guess: A documentation well usable as the research tool we are dreaming of cannot be.

(Last time in 1965 I had a book telling me everything I needed to be able to design the programs I was interested in.)

[Villeroy]

Nobody has written an index generator looking up the services that support a given interface.