Calc named range Err:508

MikeMcClain46 · June 25, 2025, 1:14pm

mike@RPI4b3:~> uname -a
Linux MikesPI 6.1.0-rpi7-rpi-v8 #1 SMP PREEMPT Debian 1:6.1.63-1+rpt1 (2023-11-24) aarch64 GNU/Linux

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: x11
Locale: en-US (C); UI: en-US
Raspbian package version: 4:7.4.7-1+rpi1+deb12u8
Calc: threaded

When used as the formula in assigning to a named range in a Calc basic macro these three functions all fail with Err:508 which suggests a missing bracket or paren.
All three give no problem when pasted into a cell in the UI.
Before turning in a bug report I thought to run it by you guys thinking a newer version may have fixed it.
These are the failing formulas:
If((EY5-EX5)>0,10*(EM5-EX5)/(EY5-EX5),0)
If((EY5>EX5),10*(EM5-EX5)/(EY5-EX5),0)
If(EY5>EX5,10*(EM5-EX5)/(EY5-EX5),0)
All nearly the same as I tried to get a working named range.
Thanks,
Mike

Sorry for the omission, this is what I’m using.
Dim oRanges as Object
oRanges = ThisComponent.NamedRanges

If NOT oRanges.hasByName(fnName) Then
	oCellAddress = oCell.getCellAddress()

’ addNewByName( aName as string, aContent as string, aPosition as struct, nType as long )
oRanges.addNewByName(fnName, fnStr, oCellAddress, 0)
End If

Where fnStr is one of the above formulas.

And the instruction to make the assignment to the cell is:
Cell.formula = “=rangeNamed”
I’ve made many named range assignments and this is the only formula that is giving problems.
The Calc UI is not at all involved.
Mike

JohnSUN · June 25, 2025, 1:16pm

I want to clarify - have you also tried the option where the function parameter separator is not a comma, but a semicolon?

Steph1 · June 25, 2025, 1:59pm

Please add a sample file, using the 7th icon on the toolbar of the message box.

mikekaganski · June 25, 2025, 2:15pm

As @JohnSUN already answered you - let me just reword that.

You omitted the code that assigns the formulas to cells; but I am sure that you used XCell.setFormula - or maybe its cousin XCellRangeFormula.setFormulaArray. And that is good; but both of them require that you pass formulas using the standard formula syntax, which means, among others, use of semicolon to separate function arguments. Your localized formulas that you type in your GUI are not suitable there; or elsewhere, with a different UI language, your macro will stop working.

There is an option to use SheetCell.FormulaLocal property - but that’s a choice, that should only be used when you transfer a user input to the program - e.g., when you show a input box with “please enter a formula”, and user will indeed enter it in their localized version; and then you take that string, and pass to the program using that property. When you are dealing with fixed functions, always use standard syntax.

MikeMcClain46 · June 25, 2025, 2:58pm

oRanges.addNewByName(fnName; fnStr; oCellAddress; 0)

In basic the semicolon stops execution and gives a ‘Missing )’ error after fnName.

JohnSUN · June 25, 2025, 3:00pm

Just show this code fragment. Or better yet, attach to the next comment a sample file with the code and an explanation of what named formulas you want to create

mikekaganski · June 25, 2025, 3:19pm

Did you try that in Basic?
The standard formula syntax is expected in the formula string, not in Basic expressions.

If you use XNamedRanges.addNewByName, it would look like this:

fnStr = "IF((EY5-EX5)>0;10*(EM5-EX5)/(EY5-EX5);0)" ' < HERE is the standard formula syntax '
oRanges.addNewByName(fnName, fnStr, oCellAddress, 0) ' and here we use normal Basic syntax '

MikeMcClain46 · June 25, 2025, 3:28pm

As requested here is a working procedure in Calc basic using nothing in the Calc UI.

Sub demo4ask_libre()
	Dim oRanges as Object
	Dim fnName as String, fnStr as String
	Dim oCell, oCellAddress, currentSheet
	
	currentSheet = ThisComponent.CurrentController.ActiveSheet
	oRanges = ThisComponent.NamedRanges
	fnName = "myNamedFunction"
	'	this assumes you have values in those cells
	fnStr = "If((EY5-EX5)>0,10*(EM5-EX5)/(EY5-EX5),0)"
	' WARNING: if you try to runthis pick an empty cell
	oCell = currentSheet.getCellRangeByName( "L8" )
	oCellAddress = oCell.getCellAddress()
	
	If oRanges.hasByName(fnName) Then
		oRanges.removeByName(fnName)
	End If
	If NOT oRanges.hasByName(fnName) Then
' addNewByName( aName as string, aContent as string, aPosition as struct, nType as long )					
		oRanges.addNewByName(fnName, fnStr, oCellAddress, 0)
	End If

	oCell.formula = "=myNamedFunction"
End Sub

Please try this and tell me if it passes in a more recent version of LibreOffice.
Mike

JohnSUN · June 25, 2025, 3:52pm

I tried it. It works this way.
createNmdRngFrmla.ods (10.6 KB)

MikeMcClain46 · June 26, 2025, 12:43am

Well Sir I must say Thank You.
I’ve created about a dozen named ranges using oCell.formula= and never thought there might be a difference so didn’t try oCell.setFormula().
This is not the first thing I’ve learned from you.
Again, my Thanks,
Mike

mikekaganski · June 26, 2025, 4:12am

Please! You didn’t learn anything, because there is no difference between using setFormula, and its Basic-specific syntax sugar formula - the difference is explicitly articulated above, in the comment that came almost 10 minutes before you posted your “solution” with “here is a working procedure in Calc basic”.

JohnSUN · June 27, 2025, 8:15am

What I’m about to say has no direct relation to the topic of the question. But since you continue to study, I must respond to your comment

This is not the only place in your macro where I replaced a direct access to an object’s properties with the corresponding set/get. For example, I rewrote your line
ThisComponent.CurrentController.ActiveSheet as
ThisComponent.getCurrentController().getActiveSheet(),
I try to do this whenever possible. Why do I do this?
I don’t know if the idea of setters and getters existed before May 1995 when Java was born, I got this idea from Java. (You might be interested to know that Java appeared in the same Sun Microsystems where OpenOffice.org, a distant ancestor of the current LibreOffice, was also)
In simple terms: an object is a set of data (properties, fields) and methods (procedures, functions) for working with this data. The correct approach to programming is to equip each field in the object with a “set-FieldName” procedure for setting the value and a “get-FieldName” function for getting this value.
@mikekaganski is absolutely right when he writes

Today this is exactly the case. However, who can guarantee that this will not change tomorrow?
For example, developers will add a high-quality string check to the setFormula method - so that it will be simply impossible to enter an invalid formula into a cell. Or they will add a call to the “Formula entered into cell!” event to the code of this method to notify other programs about this change. In this case, the .Formula field will remain the same string and you will still be able to enter a new value into this field using oCell.Formula=…, but the result of this code will be different from oCell.setFormula(…).
The same applies to get() functions. You can simply read a field into a variable, execute a bunch of code, and as a result write the changed value back into the field. At the same time, the get() method can, for example, set the flag “the field is occupied by such-and-such process, it cannot be changed now” and until you have completed all your calculations, no other process or user will replace this value. Indeed, you read from the field, for example, the value 1 and perform a lot of calculations focusing on this value. And at the same time, some other process writes 1313 into this field and all your calculations become one big mistake.
It is precisely to ensure that my macros work with possible future changes in the office code that I adhere to the rule - if there are corresponding set/get methods for any object field, I use them. I urge you to do the same.

MikeMcClain46 · June 28, 2025, 3:13am

I’m not a touch typist so I’ll likely not always put in those extra 10 characters but at least in functions like CurrentSheet, currentCell, currentSelection that seems worthwhile and will to serve me as a reminder. Yes I knew Sun birthed java and OO. I even ran Solaris 2.6 back when until something scrubbed the install.
Thanks for the lesson.
Mike

MikeMcClain46 · June 26, 2025, 6:18pm

In response to mikekaganski, you are right I haven’t learned anything.
It wasn’t until I dumped my demo and JohnSun’s procedure in createNmdRngFrmla.ods to text files and diffed them that I saw where using semis";" in the formula was what made the difference.
Sorry I’m so dense and taking up so much of your time.
Mike

mikekaganski · June 27, 2025, 8:21am

I’m curious then: did you even read Calc named range Err:508 - #7 by mikekaganski ? Just asking to know if it makes sense to try to answer at all, in case the answers are silently ignored. The incredible @JohnSUN discusses very fundamental concepts above, comparing different approaches, and explaining his take on API stability; but it might make zero sense, if the reading skill is what is missing here.

My point is: you ask questions - at this moment, you have created 37 topics already; but please, appreciate the effort of answerers.

JohnSUN · June 27, 2025, 8:30am

You may not believe it, but I read this myself after I posted my version of the code. This is the problem with long threaded discussions - comments appear in the middle of the thread just as we are typing our message, and it is impossible to follow them. This violates the correct format of the resource “question - one or more answers”. Perhaps it would be correct to add a “discussion panel” where you can ask clarifying questions and get more information about the problem, discuss possible solutions, and only post the final answer to the thread.

mikekaganski · June 27, 2025, 8:31am

@JohnSUN you don’t have to: it’s not you who asked the question But the person who asked, has some kind of moral obligation to make sure and read all answers.

JohnSUN · June 27, 2025, 8:39am

If I had seen #7 from mikekaganski in time, which was actually the exact answer to the question asked, I would have simply not published my answer (which, by and large, confused @MikeMcClain46 even more due to the replacement of oCell.formula= with oCell.setFormula())

mikekaganski · June 27, 2025, 8:41am

This was not what happened there. What did, was that @MikeMcClain46 created solutions when they needed to post new comments; and that changed the order of the discussion; that was another neglect; but I digress…

MikeMcClain46 · June 28, 2025, 2:51am

Yes, Mike I read it.
Unfortunately, like many I often see what I expect to see and didn’t see semis “;” nor did I realize there is a scroll bar hidden until the mouse pointer enters the box.