BASIC runtime error 423 with Numberformats

[Adspectus]

Hello,

with help from online resources I wrote my first macro. It is a function which converts the currency format from a given cell into its textual representation, i.e. if the currency in a given cell is “$”, it will output “USD”, if it is “€”, it will output “EUR”. I am using the text for another function, which will fetch exchange rates from ECB.

Example usage: Let’s have the numeric value of “19” formatted as currency in € in cell A1, I can use the formula “=CURRENCYABBREV(A1)” in cell A2 and it gives me “EUR”.

The function works well when I insert it into the sheet. I can save the sheet and exit, but upon reopening the file, I get an error message “BASIC Runtime error 423 Numberformats”. If I click OK the error disappears, I can see the sheet but the cell A2 is empty. This error message appears as many times as I have cells where I am using the function.

The strange thing is, if I fill in the function again, the result appears in the cell.

Maybe I am missing some other basics of using macros here, because obviously the function itself is working.

This is the definition of CURRENCYABBREV:

Option VBAsupport 1

Function CurrencyAbbrev (sAddress As Variant) As String
	Dim oDoc as Object: oDoc = ThisComponent
	Dim oNumberFormats as Variant: oNumberFormats = oDoc.Numberformats
	Dim nRow As Long: nRow = sAddress.Row - 1
	Dim nCol As Long: nCol = sAddress.Column - 1
	Dim oSheet As Variant: oSheet = ThisComponent.CurrentController.ActiveSheet
	Dim oCell As Variant: oCell = oSheet.getCellByPosition(nCol,nRow)
	Dim nFormatCodeKey as Long: nFormatCodeKey = oCell.NumberFormat
	Dim oFormat as Variant: oFormat = oNumberFormats.getByKey(nFormatCodeKey)
	CurrencyAbbrev = oFormat.CurrencyAbbreviation
End Function

Any help is appreciated. I am using LibreOffice 6.4.7.2 on Ubuntu 20.04.

CurrencyAbbrevTest.ods (9.3 KB)

[Zizi64]

There is not 100% compatibility, even if you use the compatibility options for the MS VBA codes.

.
Please upload your ODF tpe sample file here with the embedded macro code and some function calling (applied custom functions).

[JohnSUN]

Warning: What follows is deliberately simplified - in fact, everything is a little more complicated. But a simplified presentation is enough to understand the essence of the error.

Sheet functions begin to be calculated even before the document is fully loaded. That is, the macro is already running, but ThisComponent.CurrentController does not yet exist. Just check that it already exists, and if not, then stop running the macro - yes, the cell will remain uncalculated, but you can easily fix this with Ctrl+Shift+F9.

[Villeroy]

Option VBAsupport 1

is not needed here.
But you have to pass an UNO struct to the function:

Sub Main()
a = createUnoStruct("com.sun.star.table.CellAddress")
a.Sheet = 0
a.Column = 1
a.Row = 2
print CurrencyAbbrev(a)
End Sub

[Adspectus]

I have uploaded a test document attached to the OP.

[sokol92]

You can change your macro like this:

Option VBAsupport 1
Function CurrencyAbbrev (vbaRange) As String
	Dim oNumberFormats: oNumberFormats = ThisComponent.NumberFormats
	Dim oRange: oRange=vbaRange.CellRange
	Dim nFormatCodeKey as Long: nFormatCodeKey = oRange.NumberFormat
	Dim oFormat: oFormat = oNumberFormats.getByKey(nFormatCodeKey)
	CurrencyAbbrev = oFormat.CurrencyAbbreviation
End Function

[Zizi64]

Without VBAsupport

The LO Calc will not pass the cell adress (it will pass the cell content only) to a function. Therefore you need to determine the Sheet() the Column() and the Row() properties of the cell what you want to examine BEFORE you call the function, and you must pass these parameters:

REM Option VBAsupport 1
Option Explicit

Function CurrencyAbbrev (iSheetNumber as integer, lColNr as long, lRowNr as long) As String
 Dim oDoc as Object
 Dim oSheets as Object
 Dim oSheet as Object
 Dim oCell as Object

 Dim oNumberFormats as Variant
 Dim nFormatCodeKey as Long
 Dim oFormat as Variant
	
	oDoc = ThisComponent
	oSheets= oDoc.Sheets		
	oSheet = oSheets().getByIndex(iSheetNumber-1)
	oCell = oSheet.getCellByPosition(lColNr-1, lRowNr-1)	
		
	oNumberFormats = oDoc.Numberformats
	nFormatCodeKey = oCell.NumberFormat	
	oFormat = oNumberFormats.getByKey(nFormatCodeKey)
 CurrencyAbbrev = oFormat.CurrencyAbbreviation
End Function

CurrencyAbbrevTest_Zizi64.ods (10.5 KB)

[Villeroy]

Basically another case of cargo-cult programming. Now I understand why he subtracts 1 from the row/col indices. He saw it somewhere in some working code.

[Adspectus]

Thanks for all of your answers. The answer by @sokol92 works well and is the most straightforward solution. Anyway, I will try to keep learning from that and try the other ones as well. While this is my first macro with LibreOffice, it should not be the last.

[Zizi64]

@Adspectus

While this is my first macro with LibreOffice,

Then the first lesson: forget the VBA.