getDataArray() and "Object variable not set" error

Question

Installation: LibreOffice 7.0.0.3, Win10 2004 19041.450

I get "Object variable not set" run time error when I run this code. In the LibreOffice debugger I can see the 2-D array with values properly. I am not able to access individual values inside the array. What am I doing wrong?

Function Test()
    Dim objPlay As Object
    Dim arrTable(1, 1) As String, strValue As String

    objPlay = ThisComponent.Sheets.getByName("Play")
    arrTable = objPlay.getCellRangeByPosition(0, 0, 1, 1).getDataArray()
    strValue = arrTable(0, 0)
End Function

JohnSUN · Accepted Answer · 2020-09-06 18:09:43Z

2

No, in the debugger you see not a two-dimensional array, but an array of arrays - an array, each of whose elements represents an array of values for one row of the range.

In the description of the getDataArray method, this is called sequence< sequence< any > >

Just repeat the syntax for accessing the desired element from the debugger window:

Function Test
Dim objPlay As Object
Dim arrTable As Variant
Dim strValue As String
    objPlay = ThisComponent.getSheets().getByName("Play")
    arrTable = objPlay.getCellRangeByPosition(0, 0, 1, 1).getDataArray()
    strValue = arrTable(0)(0)
Rem And don't forget to return the function result
    Test = strValue
End Function

answered Sep 6, 2020 at 18:09

JohnSUN

2,5692 gold badges7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sandeep S D Over a year ago

Thanks JohnSUN it helped me get unblocked! I had hit a wall with this error. Appreciate your help.

Tomerikoo · Accepted Answer · 2021-05-02 17:33:30Z

I also was not enlightened by the sparse API documentation. I assumed one had to declare the size/shape of the array before calling getDataArray. Doing that is a mistake because then you can't use the arr(0)(0) syntax required to access the elements.

Here's a read-modify-write routine I couldn't get to work until I read JohnSUN's explanation:

Sub RangeArrayTest()
    Dim oSheets, inSheet, outSheet, in_range, out_range
    Dim range_data As Variant
    
    oSheets = ThisComponent.getSheets()
    inSheet = oSheets.getByName("Sheet1")
    outSheet = oSheets.getByName("Sheet2")

    range_str = "A3:C8"
    in_range =  inSheet.getCellRangebyName(range_str)
    range_data = in_range.getDataArray()
    
    MSgBox(LBound(range_data))  ' prints 0
    MSgBox(UBound(range_data))  ' prints 5
    MSgBox(LBound(range_data(0)))   ' prints 0
    MSgBox(UBound(range_data(0)))   ' prints 2
    
    for r = 0 to UBound(range_data)     ' do something to array
         for c = 0 to UBound(range_data(0))
               range_data(r)(c) = range_data(r)(c) + 1
        next c
    next r
    out_range =  outSheet.getCellRangebyName(range_str)
    out_range.setDataArray(range_data)
End Sub

Collectives™ on Stack Overflow

getDataArray() and "Object variable not set" error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related