[Solved] Problem using setDataArray with a vector

Creating a macro - Writing a Script - Using the API

[Solved] Problem using setDataArray with a vector

Postby Keith Wild » Tue Oct 31, 2017 2:41 pm

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   Expand viewCollapse view
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.3
macOS High Sierra version 10.13.1
Keith Wild
 
Posts: 52
Joined: Wed Aug 12, 2009 1:14 pm
Location: London, UK

Re: Problem using setDataArray with a vector

Postby Villeroy » Tue Oct 31, 2017 3:01 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25579
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem using setDataArray with a vector

Postby Villeroy » Tue Oct 31, 2017 3:19 pm

Alternative approach using a fill-down, fill-right appropach (like Ctrl+Drag in the GUI)
Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25579
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem using setDataArray with a vector

Postby Lupp » Wed Nov 01, 2017 12:15 pm

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 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1914
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Problem using setDataArray with a vector

Postby Keith Wild » Wed Nov 01, 2017 2:09 pm

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   Expand viewCollapse view
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.3
macOS High Sierra version 10.13.1
Keith Wild
 
Posts: 52
Joined: Wed Aug 12, 2009 1:14 pm
Location: London, UK


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests