Setting array formula Err:508

English
, , , ,
#1

Starting from Calc content changed events. I want to respond when, for instance, perhaps Cell contents have changed. May include things like event arguments, a way to identify columns, rows, and so forth.

Assuming there is, when I see that event, I want to set an Array Formula to a Cell Range, if possible. As well as update some other Formulae on other cells, then fill right and so on.

All programmatic via the LO Basic macros if possible, responding to said event.

I do not even know if this is possible, to be honest, and given some of the other areas I was stumbling over, I would be surprised if it was, but I thought I might ask.

Cheers, thank you.

#2

Okay so short answer, apparenrtly “yes, there is” doing it now. Sub given the oCell an ScCellObj, FWIW.

How do I get the sheet containing the cell object?

#4

Is there a better more up to date document than this one, ThisComponent is an XModel. There are a ton more properties than what is enumerated by the docs. I want to align a sheet with the oCell in the event, if possible.

#5

So with a bit of parsing I can work from Cell AbsoluteName to sheet name.

Private Function GetSheetNameFromAbsolute(strAbsoluteName)

		Dim strSheetName As String

		strSheetName = Split(strAbsoluteName, ".")(0)
		strSheetName = Right(strSheetName, Len(strSheetName) - 1)

		GetSheetNameFromAbsolute = strSheetName

End Function
#6 
Sub show_sheet_name( event )

    msgbox( event.Spreadsheet.Name)

End Sub
1 Like
#7

Moving along… How do I set a formula, never mind formula array, for an oCellRange? I’m trying this, but getting the object not set. The object is set, so something internally must not be working quite right…

strFormula = "=PARSELOCATION(" + strRelativeName + ", 0)"
oCellRange.FormulaArray = strFormula

I’ve also tried .setFormulaArray(...) to no avail.

#8

would make (much) more sense to (first) close this topic, then open a new one.

#9

Actually I wanted the sheet name to get the sheet object itself, so thanks for pointing out oCell.Spreadsheet, although that is not documented by ScCellObj class reference, or perhaps it is one of the parent classes.

#10

I have a problem with the comments.
They seem to take for sure that the event parameter is a single cell.
What if that’s a SheetCellRange object or a SheetCellRanges object?

BTW: Contents of multi-sheet selections can be changed in one go. Such an action (case delete contents e.g.) may be executed for all sheets, but the event “Content changed” may only call the handler for the “top” (currently viewed) sheet.

Seems everybody understood this. I didn’t. Please explain how to “align a sheet with…”.

#11

It kinda sorta works, I set the array formula, but I get an ugly runtime exception and Err:508 across the range. Interestingly enough I can visit the fx dialog afterwards, click OK, and the formula recalculates. But I want to do that programmatically from the concent changed EH, if possible.

strArrayFormula = "=PARSELOCATION(" + strCellRelativeName + ", 0)"
oCellRange.ArrayFormula = strArrayFormula

On the upside, content changed EH responding to either cell or cell range was easy enough to navigate after all, all things considered. The array formulae were even set correctly, from what I can determine. Except for that Err:508 issue.

Stuck on this one. How do I persuade Calc to allow the array formula to correctly evaluate? Without having to manually visit each range?

#12

What’s this to do with the subject?
Did you want to ask how to set an array formula for a SheetCellRange?

#13

Apparently the EH can also receive an ScCellRangeObj when the change was a cell range.

And using the ImplementationName, SupportedServiceNames, etc, can determine the type in order to do the next best right thing in the EH.

#14

I’m out now.

1 Like
#15

Turns out was the perennial, infamous comma versus semi-colon issue. Works like a champ!

#16

The Event will launch a SUB.
The SUB will set Cell Formulas in a Range.
The Cell Formulas will do the parsing.
(It is too complicated.)
.
.
Why do you not do the parsing by the SUB? You can modify all of the cell content by usage a subroutine.
.
SheetEvent_Sub.ods (17.6 KB)

You can paste or delete one or more data in same time into the Column B…

#17

Thanks for bearing with me. Bit sporadic chasing the overall goals, appreciate the bits of feedback. I tried to zero in on the core question hanging me up in the whole effort with the title edit.

#18

Because it is an incremental iterative process. Perhaps the whole procedure could be reduced, simplified, etc. But to start with, I needed to prove the simpler baby steps first.

#19

It might be better if you describe what you want to achieve (with an example file)… Maybe there is one (or more) other way…

[Calc] Workbook events sheet management
#3

definitely.
could also search :wink:
Ask/Guide - How to use the Ask site - The Document Foundation Wiki #Are_there_any_answers_to_my_question_yet

→ Which Event Macro on spreadsheet contents change? - #2 by peterwt

→ Macros: How to enable and disable onChange events? - #4 by Lupp
etc …

EDIT: for reference OP was Responding to Calc spreadsheet events
then the troll started :frowning:

#20

This isn’t actually a solution, but a hint:
See this old topic.

The decimal-separator-problem may also afflict inline arrays.
My suggestion: Always choose settings which not use the comma as the separator in any position where numbers may be listed.
Unfortunately there isn’t a globally working replacement for the comma as “column separator” in this case.
Fortunately inline arrays aren’t used/needed frequently.
(User code is a different thing.)
The ISO has failed to define a mandatory decimal separator. (But if it had, it would surely be ignored by many people for bad reasons. See crazy date and time formats.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.