Need help creating a user-defined function in Calc

English
,
#1

I need some help creating a user-defined function in Calc.

The UDF should have one argument, a cell address.

The UDF should return the “number format code” of the argument, i.e. the cell’s number format code, as a text string.

The argument (the cell’s address) could be located within the same worksheet where the UDF is used, or it could be located on a different worksheet of the same workbook.

Searching around, I found a hint about it, but I was not able to make it actually work. Everything I tried ended with some kind of error.

The hint is/was:


format = ThisComponent.NumberFormats.getByKey(cell.NumberFormat).FormatString

IIUC, something similar to that needs to go between some “Function - End Function” clause (or something similar to such statement?) in the LO BASIC editor.

I am not used to do these sort of things myself, and I am not a developer, so please don’t assume any specific prior knowledge or experience from me.

Any help is appreciated.

#2

https://forum.openoffice.org/en/forum/viewtopic.php?t=2762

Cell_FormatCode.ods (24.6 KB)

1 Like
#5

EDIT: (trying to correct inadequate reply)

#3

International Islamic University Chittagong?

That’s a bad idea.

  • You would need to split the address by your code to be able to get the sheet as an object.
  • The function would not take part in automatic recalculation if the format was changed. A workaround would cause complications, not just in your code, but also in the formulas using the UDF.
  • To enter the cell address as a literal would be unhandy.
  • An alternative would be to describe your cell by three numeric arguments. Also unhandy!

Alas! An UDF in LibreOffice doesn’t get a reference to the cell or the cell range given as a parameter, but only the values.

Any remedy?
Hmmm ...
Programming an UDF in Basic you can use the option Option VBAsupport 1. It will change the mode of passing parameters which actually contain pure references.
It comes with disadvantages´, and you should create an extra Basic module using that option only for code which actually needs that option.

Not quite the way to get good (usasble) code.
You touched the probably most complicated case of cell introspection.

Well, study the attachment:
disask_134290_getNumberFormatString.ods (11.6 KB)

1 Like
#7

More likely: IIUC - Wiktionary, the free dictionary.

#4 
Function NumberFormatCell(endereco As String, nomePlanilha As String) As String
' Function created in ChatGPT.
' For use: NumberFormatCell("E10"; "Sheet1")
    On Error GoTo erro
    
    Dim oSheet As Object
    Set oSheet = ThisComponent.Sheets.getByName(nomePlanilha)
    
    Dim oCell As Object
    Set oCell = oSheet.getCellRangeByName(endereco)
    
    Dim formato As Long
    formato = oCell.NumberFormat
    
    Dim oFormats As Object
    Set oFormats = ThisComponent.NumberFormats
    
    FormatoNumericoPorEndereco = oFormats.getByKey(formato).FormatString
    Exit Function

erro:
    FormatoNumericoPorEndereco = "Erro"
End Function

Ela não é automática, se alterar a célula fonte, é necessário editar a função (apague o ultimo parênteses), e Ctrl+Shift+Enter

It’s not automatic; if you change the source cell, you need to edit the function (delete the last parenthesis) and press Ctrl+Shift+Enter.

#6

First, thank you all for your prompt replies. They have been very useful.

Regarding the OOo forum (to which @Villeroy linked), it has been at least several months (if not more) that I could successfully reach sites dependent or related to AOO (forum, wiki…). I was not able to connect this time either (time out). I used the wayback machine. I have no idea whether the info I could gather is up-to-date enough. Not being a developer myself, that info was a little bit overwhelming to me, at least for my current purpose.

@Villeroy’s “Cell_FormatCode.ods” file was more useful, as I could focus on the goal I want to achieve.

@Villeroy, would the “CELL_FORMATCODE” function work if I only leave the “CELL_FORMATCODE” snippet code? Does the specific “CELL_FORMATCODE” code depend on some other part of the whole “modCELL” module? If I delete the other parts of “modCELL”, would / should the “CELL_FORMATCODE” function work exactly the same? Is it independent of the other parts?

@Lupp,

I don’t know if you were really asking, but… IIUC: “If I Understand Correctly”.

As for studying the “disask_134290_getNumberFormatString.ods” file…

_ Questions about the “numberFormatString” UDF:

It says it requires VBA support, but you don’t recommend using it. Since I am not a developer, I have no idea about potential problems and limitations, nor about potential advantages of using this one over the others, so I guess I should not choose this alternative considering that there are other ones(?).

I should anyway mention that the code says:

Will return ":fail: if pRef doesn’t reference a single cell
or a range consisting of exactly one cell.

… but cell D4 actually complies with those conditions, and yet it returns a valid result.

_ Questions about the “numberFormatString2” UDF:

_ In cell D13, would you please explain the (original / real) intention of the second part of the concatenation? It seems some unintentional remnant or typo of some kind, but I don’t really know.

From the point of view of the user, @Lupp’s “numberFormatString2” has the same syntax as @Villeroy’s “Cell_FormatCode”; on the other hand, the LO BASIC code is slightly different, so… Is there a chance that a combination of @Villeroy’s “Cell_FormatCode” mixed with @Lupp’s “numberFormatString2” could result in an improved LO BASIC code? Any suggestions?

@schiavinatto, I am yet to test that code. If I understand correctly, I would have to come up with some way to use cell’s addresses as strings between double quotation marks (e.g. the ADDRESS() function) as argument for that code / function, or alternatively, manually type-in each cell address. Not being a developer myself, at this time it seems easier for me to start with the alternatives already provided as ods files, but my lack of experience on these matters could very easily fool me.

1 Like
#8

That’s what @schiavinatto meant, when he added

The keys tell Calc to treat this as an Array-function…
https://help.libreoffice.org/latest/ug/text/scalc/01/04060107.html

#9

Thank you. My understanding of that phrase was that I would need to Recalc or refresh the function when changing the number format code of the argument / cell.

When I referred to either using the ADDRESS() function or to type-in the address manually, I meant that (IIUC) I see the argument in that code as a string in-between double quotation marks, which is not the same as a real reference. That’s in contrast to the other suggestions, that accept arguments such as functions (e.g. ROW()) or references to cells where I could use other functions, allowing (for example) to drag the UDF without depending on the ADDRESS() function before dragging the UDF to other cells.

#10

Like the other functions, this one calls getSheetCell which itself calls getSheet.

#11

No problem here; this is one of the standard methods.
Here’s another version of the same function, suggested by colleague @Lupp:

Option VBASupport 1

Function GetNumberFormat(oVBACell)
  Dim oRange as Object
  On Error Goto ErrLabel
  oRange = oVBACell.CellRange
  With oRange.RangeAddress
    If .StartRow = .EndRow And .StartColumn = .EndColumn Then
      GetNumberFormat = ThisComponent.NumberFormats.getByKey(oRange.NumberFormat).FormatString
    End If  
  End With
ErrLabel: 
End  Function

Notes:

  1. The function will be recalculated when the file is opened and when the cell value is changed.
    Changing only the cell format will not automatically recalculate the function (like other Calc functions). To recalculate, select from the menu: Menu / Data / Calculate / Recalculate Hard.

  2. The cell’s number format as a string may not be sufficient; information about the cell’s locale is also required. See this thread for an excellent example from colleague @Villeroy (cells with non-default locales are highlighted in column A).

1 Like
#12

@sokol92,

So that is yet another alternative? Is there any practical difference between that one and the first that @Lupp suggested, both depending on the VBA support option?

You also mention some thread for an example. Do you mean the same OOo’s forum link that @Villeroy posted?

To all of you that were so kind to reply, thank you. I still have some questions about the code.

About the second alternative in @Lupp’s “disask_134290_getNumberFormatString.ods” file, “numberFormatString2”, which does not depend on VBA support, I would like to understand the difference between that and @Villeroy’s.

Now I know that @Villeroy’s “CELL_FORMATCODE” depends on some other snippets of his code – thank you @Villeroy for your reply to that question – so, what’s the difference with @Lupp’s “numberFormatString2”? Are there some cases or results that I would get when using one but not with the other? Does one have better performance / speed than the other (e.g. when applying the same UDF on multiple cells)? Is there some situation in which one of the alternatives would be better suited than the other?

If it matters, I don’t need the locale to which the number format code is related; I only need the complete string, the number format code itself. I will use the same UDF on multiple cells, and on multiple worksheets of the same workbook.

Hopefully someone can help me understand, so I can decide which alternative is more adequate for this case.

Thank you in advance.

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