[Solved] Problem using setDataArray with a vector

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Keith Wild
Posts: 56
Joined: Wed Aug 12, 2009 1:14 pm
Location: London, UK

[Solved] Problem using setDataArray with a vector

Post by Keith Wild »

I am trying to use setData Array with a vector, but get the error message "Basic run-time error : Object variable not set". Strangely, when I try with a matrix there is no problem. Below is the example code.

Code: Select all

Option Explicit

Sub Main
SetDataM
'SetDataV
End Sub

Sub SetDataM
Dim i As Integer, j As Integer
Dim Doc As Object
Dim Sheet As Object, Sheets As Object
Dim Range as Object
Dim DA (4,1) As Integer 'r c

Doc = ThisComponent
Sheets = Doc.Sheets
Sheet = Sheets.getByName("Test")
Range = Sheet.getCellRangeByName("A1:B5")
Randomize()
	For i = 0 to 4					'r
		For j = 0 to 1				'c
		DA (i,j) = Int((99 * Rnd))
		Next j
	Next i
MRI Range
MRI DA
Range.setDataArray(DA())
End Sub

Sub SetDataV
Dim i As Integer, j As Integer
Dim Doc As Object
Dim Sheet As Object, Sheets As Object
Dim Range as Object
Dim DA (4) As Integer 'r
  
Doc = ThisComponent
Sheets = Doc.Sheets
Sheet = Sheets.getByName("Test")
Range = Sheet.getCellRangeByName("A1:A5")
Randomize()
	For i = 0 to 4					'r
		DA (i) = Int((99 * Rnd))
	Next i
MRI Range
MRI DA
Range.setDataArray(DA())
End Sub
The error occurs in the last line of sub SetDataV "Range.setDataArray(DA())".

Is the object variable Range (as opposed to DA)?
Both Range and DA appear to be ok in MRI. I also note that setData Array is an available method of the Range object.
Also when I put them both on watch and with breakpoints at the "Range.setDataArray(DA())" line the items also appear to be ok in the Watch window.
Is the problem simply that in AOO an array means a 2 dimensional array and vectors are not treated as a 1 dimensional array and therefore setDataArray simply doesn't work on them?
Last edited by Keith Wild on Wed Nov 01, 2017 2:10 pm, edited 1 time in total.
AOO 4.1.10
macOS Big Sur version 11.6.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem using setDataArray with a vector

Post by Villeroy »

The structure of the data array remains the same.
The array derived from A1:A4 consists of 4 sub-arrays with one element each.
The array derived from A1:D1 consists of 1 sub-array with four elements.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem using setDataArray with a vector

Post by Villeroy »

Alternative approach using a fill-down, fill-right appropach (like Ctrl+Drag in the GUI)

Code: Select all

Sub Main
Dim Sheets, Sheet, Range, Cell, Addr, Target
Sheets = ThisComponent.getSheets()
Sheet = Sheets.getByName("Test")
Range = Sheet.getCellRangeByName("A1:B5")
Addr = Range.getRangeAddress()
Cell = Range.getCellByPosition(0,0)
Cell.setFormula("=ROUND(RAND()*99)")
REM copy down
Target = Sheets.getCellRangeByPosition(Addr.StartColumn, Addr.StartRow, Addr. StartColumn, Addr.EndRow, Addr.Sheet)
Target.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM,com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
REM fill the rest of the range to right
Range.fillSeries(com.sun.star.sheet.FillDirection.TO_RIGHT,com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
REM convert formulas to values
Range.setDataArray(Range.getDataArray())
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Problem using setDataArray with a vector

Post by Lupp »

I would put it this way:
The CellRange properties being arrays filled per cell (.Dat .DataArray .FormulaArray) are always 2D, even if the number of rows or/and the number of columns is 1.
Though they use a special indexing like DA(2)(0) the respective Set method also accepts arrays declared in BASIC with Dim in the usual way with first index for rows, second for columns. Only the sizes are relevant then. No check for starting indexes. Just as usual when exchanging dynamic arrays. However the indexing defined in Dim is changed without notice if the respective Get method is used.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Keith Wild
Posts: 56
Joined: Wed Aug 12, 2009 1:14 pm
Location: London, UK

Re: [Solved] Problem using setDataArray with a vector

Post by Keith Wild »

Lupp wrote:I would put it this way:
The CellRange properties being arrays filled per cell (.Dat .DataArray .FormulaArray) are always 2D, even if the number of rows or/and the number of columns is 1.
Given this it appears that the array must always be 2d in format, even if in reality it is a 1d vector. So I tried the following, which works.

Code: Select all

Sub SetDataV3							'Works
'Declare the Data Array DA as a 2d array, with 5 rows and 1 column
' i.e. a 1d vector in all but name and format

Dim i As Integer
Dim Doc As Object
Dim Sheet As Object, Sheets As Object
Dim Range as Object
Dim DA (4,0) As Integer 'r c

Doc = ThisComponent
Sheets = Doc.Sheets
Sheet = Sheets.getByName("Test")
Range = Sheet.getCellRangeByName("A1:A5")
Randomize()
	For i = 0 to 4					'r
		DA (i,0) = Int((99 * Rnd)
	Next i
Range.setDataArray(DA())

End Sub
Thanks both for your help.
P.S. Code edited to tidy up a bit.
AOO 4.1.10
macOS Big Sur version 11.6.2
Post Reply