Sequence element is not assignable by given value

English
,
#1

I’m trying some BASIC code for a Macro in LibreOffice-Calc to copy 16 cells from a spreadsheet, then paste them transposed to a new location on the same datasheet
I worked up an indexer to pick the cells starting at 3:18, then 23:38, then 43:58, etc.
It goes through fine on the first loop, but on the second loop it errs out.

BASIC runtime error.
An exception occurred
Type: com.sun.star.lang.IllegalArgumentException
Message: sequence element is not assignable by given value! at C:/cygwin64/home/buildslave/source/libo-core/stoc/source/corereflection/crarray.cxx:136.

Here’s the code:

REM  *****  BASIC  *****
sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
 
for i = 1 to 10
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
rem need a sequence that goes 3, 23, 43, 63, etc
rowfrom = 20*(i-1)+3
rowto = rowfrom+15
rem args1(0).Value = "$A$3:$A$18"
rowfromstr = mid(str(rowfrom),2,20)
rowtostr = mid(str(rowto),2,20)
buildstring = "$A$" & rowfromstr & ":$A$" & rowtostr
args1(0).Value = buildstring
 
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$B$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dispatcher.executeDispatch(document, ".uno:PasteTransposed", "", 0, Array())
next i
end sub

The string looks fine going in to the second variable assignment, but when it gets to the dispatcher, it errs out.

#3

Hallo

for i = 3 to 183 step 20
#7

That’s a good solution to my weird indexing arithmetic.

#2

Move this lines outsize your for

dim args1(0) as new com.sun.star.beans.PropertyValue
dim args3(0) as new com.sun.star.beans.PropertyValue

for i = 1 to 10

but, if you not change your target (B1), you only show last range.

1 Like
#5

Moving the variable declarations outside the for loop did the trick.
Thank you!

Now if I could only understand what is meant by “com.sun.star.beans.”

#6

I was in the process of adding an index for the placement of the transposed cells.
The latest code is here.

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)

dim args1(0) as new com.sun.star.beans.PropertyValue
dim args3(0) as new com.sun.star.beans.PropertyValue

for i = 1 to 10
rem ----------------------------------------------------------------------
args1(0).Name = “ToPoint”

rem need a sequence that goes 3, 23, 43, 63, etc
rowfrom = 20*(i-1)+3
rowto = rowfrom+15

rem need a sequence that goes 1, 21, 41, etc
tocell = 20*(i-1)+1

rem args1(0).Value = “$A$3:$A$18”
rowfromstr = mid(str(rowfrom),2,20)
rowtostr = mid(str(rowto),2,20)
buildstring = “$A$” & rowfromstr & “:$A$” & rowtostr
args1(0).Value = buildstring

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args1())
dispatcher.executeDispatch(document, “.uno:Copy”, “”, 0, Array())

rem ----------------------------------------------------------------------
args3(0).Name = “ToPoint”

rem args3(0).Value = “$B$1”
tocellstr = mid(str(tocell),2,20)
varstr = “$B$” & tocellstr
args3(0).Value = varstr

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args3())
dispatcher.executeDispatch(document, “.uno:PasteTransposed”, “”, 0, Array())
next i

end sub

#9

In modern programming something like PropertyValue might be “invented/used” by more than one library. To avoid conflicts names are prefixed with a text to make names unique. ( Hi Guy will not address you, even RadarGuy may be a group, Old Radar Guy may be you…)

You may recognize this name may come from netbeans universe by Sun.

#10

how exactly does »everything prefixed with com.sun.star « … avoid name-conflicts :rofl:

#11

Easily: it allows to disambiguate with e.g. namespace.by.mikekaganski.PropertyValue. The idea is not disambiguate inside the namespace, but between namespaces, allowing code from other sources to happily co-exist.

#12

thats obviously the theory! … but in practise I didnt see lot of something like »org.jupiter.kallisto…«

#13

Still it is there (open any at least slightly complex third-party extension); and anyhow, even if you were correct, that was the intention, while you are arguing about what you think was the resulting state after implementing that idea.

1 Like
#4

Hallo
guess in python:

def transpose_slices_of_16(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    t_data = list(zip(*sheet[ "A3:A183" ].DataArray))[0]
    out = []
    for row in range(0, 180, 20):
        out.append(t_data[row : row+16])
    
    sheet[ 0:len(out), 1:17 ].DataArray = out

takes slices of 16 rows from A3:A183 and »paste it transposed« to B1:Q9
eg.
A3:A18 ⇒ B1:Q1
A23:A38 ⇒ B2:Q2
…
…
A163:A178 ⇒ B9:Q9

#8

Thanks for that code.
I was thinking of trying Python if Basic was giving me trouble.

Does anyone know if cells can be indexed by row, col variables instead of string variables?

I used to do cell cut and paste using row,col indexing in that ‘other’ spreadsheet.

example 1

Sub EnterValue() 
 Worksheets("Sheet1").Cells(6, 1).Value = 10 
End Sub

example 2

Sub CycleThrough() 
 Dim Counter As Integer 
 For Counter = 1 To 20 
 Worksheets("Sheet1").Cells(Counter, 3).Value = Counter 
 Next Counter 
End Sub
#14

You can see some Variations of Index-Access in the python-example above

source = sheet["A1:D10"]   # Name-Access
## is equal to:
source = sheet[ :10 , :4 ]  # index-Access 
# sub_slice_last_three_rows:
last_three_rows = source[ -3: ,  : ]
# sub_slice_last_two_columns:
last_two_Columns = source[ : , -2: ]
## single_cell C7:
c7 = sheet[ 6 , 2 ]

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.