[Solved] UserDefinedAttributes property of Calc cell

[Alex6361]

I'm following Andrew Pitonyak's example of setting and retreiving userdefinedattributes for a CALC cell but finding that the command to set that property is not doing anything. I'm wondering if this property has been deprecated, or the means to set it has been changed since Andrew did his research. The test code I'm trying is:

Code: Select all

REM  *****  BASIC  *****
Function Main(Arg1, Arg2)

	Dim CellObject
	Dim UDAcopy
	Dim CellAttribute as New com.sun.star.xml.AttributeData
	
	CellObject = thisComponent.Sheets(0).getCellByPosition(0,0)  'cell "A1"
	CellAttribute.Type = "CDATA"
	CellAttribute.Value = Arg1 + Arg2
	UDAcopy = CellObject.UserDefinedAttributes
	If NOT UDAcopy.HasByName("AATRv") then
		UDAcopy.InsertByName("AATRv", CellAttribute)
	Else
		UDAcopy.ReplaceByName("AATRv", CellAttribute)
	End If
	
	'following statement gets no run time complaints but doesn't do anything
	CellObject.UserDefinedAttributes = UDAcopy
	
	If CellObject.UserDefinedAttributes.HasByName("AATRv") then
		Main = CellObject.UserDefinedAttributes.getByName("AATRv").Value
	Else
		Main = "no attribute found"
	End If
End Function

Am I missing something?

[Zizi64]

viewtopic.php?t=76896
MrProgrammer's answer:

I don't understand what you are trying to accomplish, but spreadsheet cells do not have user-defined attributes.

[JeJe]

If you want to store some information for a cell you could use a custom document property.

[Zizi64]

I just tested the macro code from OOME_4_0.odt. The UDA of the cells really exists and it works.

I can GET the UDA from a cell by a custom macro Cell function.
But I can SET an UDA by a SUBROUTINE only. A Cell function can not modify the properties of another cells.

See my sample document (created in LibreOffice 7.5.8):

UDAs.ods

(15.19 KiB) Downloaded 45 times

[Alex6361]

Tibor - I was aware that a user-defined-function could not change the value of cells other than the cell it was called from via its return value, but didn't extend that thinking to userdefinedattributes for a cell, inasmuch as changing such a property would not affect re-calculation and, therefore, should not pose any problems. So, thanks for correcting my bad assumptions!

JeJe - what is a "custom document property"?

The user-defined-function (UDF) I'm trying to convert from an Excel workbook solves a problem through iteration based on the arguments (cell ranges) it is called with. Since it has to start with a guess and then refine that guess via iteration, the number of iterations can be drastically reduced if the guess can be the result it last calculated, inasmuch as the data it is working with doesn't change much between calculations. Excel 5.0 and its VBA allows a UDF to get what was displayed in the cell at the time of the call, before the UDF then goes on to change it. That allowed my UDF to start with that value and then go on to refine it, rather than start with a guess. So far, I have not been able to find a way to get that from CALC and StarBasic, so thought maybe the userdefinedattribute might be a way. Unfortunately, it's not.

I'll mark this as SOLVED, inasmuch as Tibor answered my original question with the fact that what I was trying to do can't be done. However, I still haven't found a way to get CALC Basic to do what Excel VBA could do 30 years ago.

[Zizi64]

JeJe - what is a "custom document property"?

From the Menu:
File - Properties... - Custom (TAB).

The available types of these properties:
Text
DateTime
Date
Duration
Number
Yes or no (boolean)

You can Add, Edit, and Delete the custom properties. And you can do it by macros too.
You can use these Custom properties as Fields in the text body directly (in the Writer application):
Insert - Field - More Fields - Custom

[Zizi64]

Since it has to start with a guess and then refine that guess via iteration, the number of iterations can be drastically reduced if the guess can be the result it last calculated, inasmuch as the data it is working with doesn't change much between calculations.

You can use the
- Custom properties of the document (the actual value will be saved in the document)
- Global variables in the StarBasic (the actual value will NOT be saved in the document)
for this task (to store the last calculated value).

Or you can use an Event triggered macro SUBROUTINE for the calculation - in this case you will able to store the last calculated value in an another Cell (then it will be visible), or in the UDA of the cell.

[JeJe]

Can't you use getdataarray to get the data in the cells and then the FunctionAccess Service to run calc functions on that array?

[Alex6361]

Thanks for the suggestions. Having never had a need to use document properties in Excel, the subject never came to my mind for storing data relative to individual cells, and I didn't even know that CALC allowed one to create custom document properties. I suppose I could use such a technique here, but there are over 50 sheets in the workbook that have a cell calling this UDF, so I'd have to come up with a way to associate each UDF call with a custom document property. Doable, but not very pretty. My work-around solution for now is to run a sub which goes through all of the sheets in the workbook looking for named cells having a particular name, and when one is found copy that cell's value to another named cell on that sheet, where the UDF on that sheet can get it the next time it executes. Not very pretty, either, but it works for now.

JeJe, I do use getDataArray to get data from sheet cell ranges and run CALC Functions via the Function Access service when they are useful, but there are no CALC functions which solve the problem my UDF is trying to solve.

Tibor, it isn't obvious to me what kind of Event might be used to initiate the saving of the last result for the next time external to the UDF. My use of events so far has been limited to dialog controls, and I am not well versed on that subject. Would each execution of the UDF somehow trigger an event that would launch a sub to make the copy? If so, how might that be done?

[Lupp]

... I was aware that a user-defined-function could not change the value of cells other than the cell it was called from via its return value, ...

The mentioned restriction only is in force for cells of the same sheet. It was more than once relieved for LibO, but later reinstated, and I never tested if it also inhibits changes to UserDefinedProperties of the currently evaluating cell.

... but didn't extend that thinking to UserDefinedAttributes for a cell, ... So, thanks for correcting my bad assumptions!

Extending thinking is a good idea. However, It may not lead to a specifically recommendable solution if it isn't practised to the needed extent.
Here: Custom properties are possibly not the final hit.
Comes a part which I don't clearly understand, probably because I have no Excel 5.0 to test anything.

The user-defined-function (UDF) I'm trying to convert from an Excel workbook solves a problem through iteration based on the arguments (cell ranges) it is called with. ...
... the number of iterations can be drastically reduced if the guess can be the result it {the UDF ?} last calculated, ...
... Excel 5.0 and its VBA allows a UDF to get what was displayed in the cell at the time of the call, before the UDF then goes on to change it.

First time that I read about this interesting feature. How do you access this "ResultBeforeRecalculation" in your VBA UDF?

That allowed my UDF to start with that value and then go on to refine it, rather than start with a guess. So far, I have not been able to find a way to get that from CALC and StarBasic, so thought maybe the userdefinedattribute might be a way. Unfortunately, it's not.

StarBasic has no means for this, and I'm afraid there isn 't sufficient support for it even by the RAM representation of a Calc document.

I'll mark this as SOLVED, inasmuch as Tibor answered my original question with the fact that what I was trying to do can't be done. However, I still haven't found a way to get CALC Basic to do what Excel VBA could do 30 years ago.

Excel VBA is basically different from a programming languiae. It is a huge apparatus containing lots of "knowledge" specifically about the respective Excel version. Regarded as a lanuage it's mainly outdated. (I think I can state this though not using it because I sudied lots of Excel-VBA-code in pursuit of questions in forums and help sites.)

Thanks for the suggestions. ...
... but there are over 50 sheets in the workbook that have a cell calling this UDF, so I'd have to come up with a way to associate each UDF call with ...
... My work-around solution for now is to run a sub which goes through all of the sheets in the workbook looking for named cells {How do you name cells? Are you talking of named ranges/expressions?} ...

Concerning the "SOLVED" you learned already that it was too rash. There may be (e.g.) a sheet event allowing to trigger the needed process to store old values to user defined properties, but ...

Tibor, it isn't obvious to me what kind of Event might be used to initiate the saving of the last result for the next time external to the UDF. ...

Nor do I know one, but @Zizi64 may have an idea.
My so far "best" idea is to integrate the creation of a history of previous values for specific cells with the help of a "framing" UDF ...
I even made a "simple" example, and I will supply it as an attachment and explain it a bit if you ask an apropriate question in this Macro forum like "How to make specific cells history aware?"
To post my suggestions in this thread would be a violation of the given subject.

[Alex6361]

I appreciate all the suggestions and efforts to help. The original topic was / is the question of why I was having a problem setting UserDefinedAttributes on a worksheet from a User Defined Function on that sheet. Tibor answered that by saying essentially that such an action simply was not allowed. My take is that it's not for technical reasons, but because those who decide such things either didn't think it should be done or needed to be done. That reality didn't solve my underlying problem, but I considered my question "solved" inasmuch as there was no need to pursue that approach as a solution. Like with so many other situations where the current state of CALC fails to match the abilities Excel had in 1995, I found a workaround which I can live with. So, I think this topic may be closed.

However, I will try to respond to Lupp's comments. Yes, Excel 5's VBA strains to be called a programming language. However, its creators apparently had spreadsheet users in mind when they created it, not professional software scientists. As a result, it is relatively easy for a spreadsheet user to create embellishments to augment their workbooks. That cannot be said for CALC and StarBasic.

Microsoft made some considerable changes to Excel and its VBA after 5.0. I tried the 1997 version, didn't like it, and have continued to use 5.0 ever since. It still has everything I need from a spreadsheet, but the OS environment it has to live in today has changed radically, such that it can't interface with printers nor store its user-customizations in the Windows directory. So, I'm being forced to migrate all the VBA enhancements I've made over 3 decades to LibreOffice, and it's been rough.

You asked how a UDF could get the original value of its calling cell in Excel. The statement "X = Application.caller.Text"
will leave X with whatever appeared in that cell at the time of the call, including any formatting the cell has. Whether that still works in newer versions of Excel I don't know. Perhaps it was an accident or oversight that it even worked in Excel 5.0, but it works and is very useful. In CALC Basic, a UDF can't even find out the sheet on which it was called from or anything else about its calling arguments unless the UDF uses "Option VBASupport 1". Excel 5.0 can access a cell or cell range by its name or indices very quickly, but CALC Basic is cumbersome and slow in comparison.

[Zizi64]

In CALC Basic, a UDF can't even find out the sheet on which it was called from

Yes, it is true, but you can pass the Sheet(), the Row() and the Column() parameters to your UDF, and then the you can get the sheet and the cell by the code of the UDF.

The MS VBA feature and the LO StarBasic+API "combo" are basicly different. They are not compatible.

[JeJe]

Code: Select all

thiscomponent.currentselection.string

That gives the information I think you want just as slickly, though yes, much of the API is more complex to call.

If you use MRI to inspect thiscomponent.currentselection you can see all the properties and methods available which include the formatting of the cell.

viewtopic.php?t=49294

[MrProgrammer]

ThisComponent.CurrentSelection.String

I don't understand how this helps. Say A1 is 5, A2 is =FOO(A1+1), and perhaps the result for A2 is 17. Now I enter 9 in A1 and A2 begins to calculate the new result. As I understand the goal, Alex6361 wants FOO to know A2's existing value 17 as it calculates FOO(10). But the current selection will be A1, not A2, and ThisComponent.CurrentSelection.String will be the new value 9 in A1, not 17.

I was aware that a user-defined-function could not change the value of cells other than the cell it was called from via its return value, but didn't extend that thinking to userdefinedattributes for a cell, inasmuch as changing such a property would not affect re-calculation and, therefore, should not pose any problems.

I interpret the restriction to be: A user-defined function called from a Calc cell can only return a value. Any spreadsheet side effects which the function attempts to produce are silently ignored. I don't know of any documentation which states that explicitly, but this is my understanding of how Calc works.

[JeJe]

Thanks Mr. Programmer... I think I'm with it now... the problem is that before the function is called the function cell value is set back to 0 and the string to "".

If you're changing a cell by pressing the enter key you can find out the current value using a keyhandler before this happens (see attached document).

Or you can use the formula calculated event to store the result of a calculation somewhere (other document). Another option not mentioned for storing the value would be a text file.

Edit:
Another workaround perhaps might be to show a dialog to input a change in the value instead of by changing the cell directly?

[Alex6361]

This has strayed from the original question, but I do appreciate everyone's efforts to help with finding a way to "prime" an iterative UDF with an advantageous starting value. Perhaps I should start a new topic for what this has become, but it appears to me from the comments so far and my own trial and error that CALC and its BASIC simply don't provide a direct solution, and one is left to come up with the least cumbersome alternative. And, that alternate may be different depending on how such a UDF is employed. As JeJe and Mr. Programmer indicated, even "ThisComponent.CurrentSelection.String" fails to get the last value. It apparently gets set to zero or "" the moment that the UDF gains control. Whether there's a fundamental technical reason for this or it's just the way the developers and caretakers of the "black box" chose to make it, I don't know, but I do not believe anyone is going to change it anytime soon, if ever. With "ThisComponent.CurrentSelection.String" -- even if it worked -- one would have to select the cell from within the UDF (using Tibor's solution of Sheet(), Row() and Column() in the UDF's arguments) and if this UDF is on many sheets in the workbook, every sheet containing it then gets selected and made active as the wave of calculations propagates through the workbook, eventually leaving the user on a different sheet than when data entry and calculation started.

[JeJe]

Sorry here, I've confused things by talking about the currentselection and as Mr. Programmer pointed out that was me not understanding the problem as he's put it. You can turn off autocalculate and then get the original cell values before you calculate the functions.

[Alex6361]

If autocalculate is turned off, then the UDF won't run until it is turned back on or F9 is pressed, at which time the cell's original value will be nulled out when the UDF starts runing. If you step through the UDF in the IDE, even though the original value still shows in the cell on the worksheet, what is given to the UDF is not what you see. If there was a way for the UDF to get the contents of the cell as they appear on the screen, then perhaps that would work, but I haven't been able to discover any method for accessing that.

[JeJe]

Yeah, if you have autocalculate turned off then instead of pressing F9 to update you run a sub that stores all the existing values you need first, say in a collection object, then runs update. Your UDF then has all the values in the collection.

[MrProgrammer]

Does the idea below help with the goal of starting iterations with the function's previous result? A StarBasic global variable can store your function's result, and then the function can retrieve it from the variable on the next call. To keep it simple, this sample function doesn't do any iterations.
Usage: Set B1 to formula =LAG("A";A1)
• A1 ← 5; B1 returns 0
• A1 ← 2; B1 returns 5
• A1 ← 8; B1 returns 2

Globals aren't retained when the file is saved. You'd have to copy/paste the last result to another cell before saving. After opening the file, use a formula to call a different (non-iteration) function to initialize the global from that cell. Macros, probably even recorded ones, could simplify those tasks.

Rem Globals (NA,NB,NC) for storing numbers
Global Lag_NA As Double ' Used for L = A
Global Lag_NB As Double ' Used for L = B
Global Lag_NC As Double ' Used for L = C
Rem Globals (SA,SB,SC) for storing strings
Global Lag_SA As String ' Used for L = A
Global Lag_SB As String ' Used for L = B
Global Lag_SC As String ' Used for L = C

Function LAG(L As String,V as Variant) As Variant
Rem L selects one of Lag_Nx if V is a number
Rem                  Lag_Sx if V is a string
Rem LAG returns one of the Lag_N or Lag_S variables, then
Rem     V is the new value for the Lag_N or Lag_S variable
Select Case VARTYPE(V)
   Case 5 ' Double
      Select Case L
         Case "A"  : LAG = Lag_NA : Lag_NA = V
         Case "B"  : LAG = Lag_NB : Lag_NB = V
         Case "C"  : LAG = Lag_NC : Lag_NC = V
         Case Else : LAG = V      ' Lag ignored
      End Select
   Case 8 ' String
      Select Case L
         Case "A"  : LAG = Lag_SA : Lag_SA = V
         Case "B"  : LAG = Lag_SB : Lag_SB = V
         Case "C"  : LAG = Lag_SC : Lag_SC = V
         Case Else : LAG = V      ' Lag ignored
      End Select
   Case Else       : LAG = V      ' Lag Ignored
End Select
End Function

Edit: Probably better to use arrays for Lag_N and Lag_S and pass a numeric index instead of characters for L.

[Alex6361]

Thanks for all of the suggestions. I think all of them have merit. And, depending on the context in which a User Defined Function (UDF) runs, one of the suggestions may be more suited or easier to implement than the others. My preference to to keep a sheet's data on that sheet. Although the iterative UDF I'm using is on many sheets, it is not on a single sheet more than once. I have given each cell containing the UDF a RangeName (call it UDF_original) which is local to that sheet, and have manually chosen another cell on each sheet containing that UDF and given it a slightly different local RangeName (call it UDF_copy). A macro (which can be invoked from a button on a toolbar) then enumerates all of the sheets in that workbook, looking for a NamedRange of UDF_original. If it finds one, it then looks for a NamedRange of UDF_copy. If it finds one, it copies the value of UDF_original to UDF_copy. The UDF macro looks for a NamedRange of UDF_copy on the sheet from which it is called, and if it finds one, it uses that value for the start of its iteration. If it doesn't find one, it guesses a value. In my case, the data upon which the iterations are performed doesn't change much between iterations, so a slightly "stale" value in UDF_copy still gives the iterative process a good starting point. This is a large workbook with many named ranges and named references, so I usually keep AutoCalc off and just hit F9 when I want everything updated.