Charts.addNewByName doesn't work

I’d like to be able to create a chart programmatically. My macro code:

Sub Main
   	Dim Doc As Object, Sheet As Object, Charts As Object, Chart As Object
    Dim Source(0) As New

    Doc = ThisComponent
    Sheet = Doc.Sheets.getByName("Sheet1")

	Const ChartH = 3, ChartW = 3, ChartName = "Test1"
    Charts = Sheet.Charts
    'Chart = Charts.getByName(ChartName)
    'Chart = Charts(ChartName)

	'selection of data
	Source(0).Sheet = 0
	Source(0).StartColumn = 0
	Source(0).StartRow = 0
	Source(0).EndColumn = ChartW - 1
	Source(0).EndRow = ChartH - 1
	oDataRanges = Doc.createInstance("")
	oDataRanges.addRangeAddress(Source(0), False)

    'If IsNull(Chart) Then
    'If not Charts.hasByName(ChartName) Then
    If not Charts.hasElements Then
	    'if there is no chart, create one
    	Dim Rect As new
		Rect.X = 15000
		Rect.Y = 1000
		Rect.Width = 12000
		Rect.Height = 6800
    	'Charts.addNewByName ChartName, Rect, Source, True, True
    	Charts.addNewByName ChartName, Rect, oDataRanges.RangeAddresses, False, False
    	'Chart = Charts.getByName(ChartName)
    	Chart = Charts(0)
    	'Chart = Charts.getByName(ChartName)
	    Chart = Charts(0)
		'Chart.setRanges Source
		Chart.setRanges oDataRanges.RangeAddresses
End Sub

When I try to run in LO Basic, first it raises an exception, on the line Charts.addNewByName…:

BASIC runtime error.
An exception occurred
Message: .

The next time I try, the addNewByName call silently fails, and Charts(0) fails with the following message:

Inadmissible value or data type.
Index out of defined range.

If I try using Charts.getByName instead of Charts(0), it also fails:

BASIC runtime error.
An exception occurred
Message: .

No chart is created anyway.

Version: (x64) / LibreOffice Community
Build ID: 87b77fad49947c1441b67c559c339af8f3517e22
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded


Tried in Ubuntu 20 and Win 10 with:

Version: (x64) / LibreOffice Community
Build ID: 9a9c6381e3f7a62afc1329bd359cc48accb6435b
CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

No error in either with this displayed as a result:

Screenshot 2021-10-04 205920

I did use a different sheet name but the remainder of the code was what you posted.

I honestly don’t know what to do. I tried running the macro on a fresh new workbook. Also I tried in LO options to switch the Java Runtime environment that LO uses, with the same results.
Any ideas?

Possibly safe mode / resetting user profile → LibreOffice user profile

I found something now. Playing with the options, specifically LibreOffice Calc > Formula > Formula Options, I set Formula syntax to “Calc A1” (it was at “Excel A1” until now), and the macro worked with no apparent issues.
There seems to be a bug anyway, as I wasn’t using range expression strings in the macro. Also I’d prefer to leave the syntax in Excel mode, because I still use Excel a lot (the difference is probably the sheet operator: “Sheet1!SomeName” in Excel and “Sheet1.SomeName” in Calc).

Sounds like a bug to me. Could you check in the bug tracker if a report exists already and if not create one? Thanks.

I created bug tdf#144970 for addNewByName() failing with Excel A1 or R1C1 syntax.

Whoa, thanks. I was gonna do that, but you were quicker.
And pardon my poor English.