UBound dimension 2 persistent Error

[danielb1]

Getting a “Inadmissible value or data type. Index out of defined range.” on the variable nnnCols when copying the contents of a sheet into a 2D array of 1000 rows and 300 columns)

Intention is to load a entire sheet contents into RAM to run faster a long set of checks then dump it back on file at the end (otherwise the checks run for hours).
This example should just fill the first column with “-” so that filtering later on does not skip rows.

Dim oooDoc As Object
Dim oooSheet As Object
Dim oooRange As Object
Dim aaaData() As Variant 
Dim nnnRows As Long
Dim nnnCols As Long
Dim z As Long
Dim y As Long 
Dim temp_cell As Variant
Dim temp_string As String

oooDoc = ThisComponent
oooSheet = oooDoc.CurrentController.ActiveSheet
oooRange = oooSheet.getCellRangeByPosition(0,0, 300, 1000) ' Range is A1 to KI100001
  
aaaData = oooRange.getDataArray()   ' Read all data into RAM array
nnnRows = UBound(aaaData, 1)      
nnnCols = UBound(aaaData, 2)    	 

MsgBox "Found " & nnnRows + 1 & " rows and " & nnnCols + 1 & " columns of data."

For z = LBound(aaaData, 1) To UBound(aaaData, 1) 
    For y = LBound(aaaData, 2) To UBound(aaaData, 2)
        temp_cell = aaaData(z, y)  
        temp_string = CStr(temp_cell)  
        
        If temp_string = "" Then
            aaaData(z, y) = "_"
        End If
        
    Next y
Next z
oooRange.setDataArray(aaaData)  ' dump the whole modified array back to the file

The sheet is not empty, contains both strings and values across multiple cells towards the start.
Ran the code by Google AI and it said it runs on his 7.6 version of Libre Office. Many of the fancy code is its.
So I am stumped as to the problem. It seems to not load as 2D array for some reason…

How do I solve this?

[danielb1]

Here’s the Google AI comment on the problem : If the error persists even with the IsArray(aaaData(LBound(aaaData, 1))) check added, you likely have an environment-specific issue, or the range genuinely has zero columns despite your getCellRangeByPosition call (which would be highly unusual).

[Zizi64]

@danielb1

Ran the code by Google AI and it said it runs on his 7.6 version of Libre Office.

Never trust in any AI.
YOU must write your own macros.

AI always says what you want to hear. Now it says for me about the function Ubuond :

With getDataArray() (Common Calc Use Case):

• When getting data from a range, UBound(Array()) on the first dimension often returns 0 because it’s treated as a single row array, but the inner dimension holds the actual column count.
• To get the number of columns in a range: UBound(DataArray(0))

Here is my modified code using the getDataArray():

REM  *****  BASIC  *****
Option Explicit

Sub EditArray

 Dim oDoc As Object
 Dim oSheet As Object
 Dim oRange As Object
 Dim aData(), aRowData() As Variant 
 Dim lRows As Long
' Dim lCols As Long
 Dim z, y As Long
 Dim sTemp As String

'loadxray

	oDoc = ThisComponent
	oSheet = oDoc.CurrentController.ActiveSheet
	oRange = oSheet.getCellRangeByPosition(0,0, 300, 1000) ' Range is A1 to KI100001  
	aData = oRange.getDataArray()   ' Read all data into RAM array
'Xray aData
	'nnnRows = lBound(aData()) 
	'print lRows     
	'nnnCols = lBound(aData(0))
	'Print lCols
	'MsgBox "Found " & lRows + 1 & " rows and " & lCols + 1 & " columns of data."
	for z = LBound(aData()) To UBound(aData()) 
    	aRowData() = aData(z)
    	for y = LBound(aRowData()) To UBound(aRowData())
        	sTemp = aRowData(y)        
        	if sTemp = "" then
            	aRowData(y) = "_"
        	end if        
    	next y
    	aData(z) = aRowData
	next z
	oRange.setDataArray(aData)  ' dump the whole modified array back to the file
End Sub

'_____________________________________________________________________________________________


Sub LoadXray

	if (Not GlobalScope.BasicLibraries.isLibraryLoaded("XrayTool")) then
		GlobalScope.BasicLibraries.LoadLibrary("XrayTool")
	End If
end sub
'_____________________________________________________________________________________________

I suggest you to download, install and use one of the excellent Object Inspection Tools: MRI or XrayTool. Then you will able to examine the existing properties and mertods of the programming objects.

[mikekaganski]

@danielb1

It seems to not load as 2D array for some reason

Of course. getDataArray returns an array of arrays, not a two-dimensional array.
nnnCols = UBound(aaaData(0)) would work.

[danielb1]

Still gets me the “Inadmissible value or data type. Index out of defined range.”

However, found a quicker, more hands on approach by this guy ( Profile - JohnSUN - Ask LibreOffice) that does what I need

Sub Fill100CellsWithStrOfNum()
Dim oSheet As Variant
Dim oCellRangeByPosition As Variant
Dim oDataArray As Variant
Dim i As Long 
	oSheet = ThisComponent.getSheets().getByIndex(0)
	oCellRangeByPosition = oSheet.getCellRangeByPosition(0, 0, 300, 100000) rem (column,row..)
 

	oDataArray = oCellRangeByPosition.getDataArray()
	For i = 0 To 100000
		oDataArray(i)(9) = Cstr(i)		rem (row)(column)
		oDataArray(i)(10) = "nimic"		rem (row)(column)
		oDataArray(i)(0) = ""		rem (row)(column)
	Next i
	oCellRangeByPosition.setDataArray(oDataArray)
End Sub

For me, getting the run time to 3 minutes instead of 3 hours in rocket science