Bug with CALC macro and MINVERSE(array)?

English
#1

wondering if this is a bug or some new changes
in latest BASIC macro

CALC Macro BASIC function

trying to make an older macro work in latest version
but it does not work!

there is example of code to calculate matrix inverse

https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=84063

appreciate any help to determine if this is a bug or
what changes have been done to make it work again in latest version

thanks for any help

#2

With what version did you try?
Please tell what actually happens instead of “it does not work”.
Can you give a specific matrix which you tried to invert?

Did your macro try to invert a matrix passed to it from a Calc formula OR did you create the matrix and fill it with elements inside the macro OR did you try to get the matrix from the contents of a CellRange OR did you try to return the result as an (array-)function-result to a range of a Calc sheet?

#3

For the record, this code works for me using Version: 7.2.0.2 (x64) / LibreOffice Community
Build ID: 614be4f5c67816389257027dc5e56c801a547089
CPU threads: 12; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded:

Function matrix_inverse(a)
  matrix_inverse = createUnoService("com.sun.star.sheet.FunctionAccess").callFunction("MINVERSE", array(a))
End Function

Sub matrix_inverse_test
  Dim a(2,2) As double, b, s$, i%, j%
  a(0, 0) = 1
  a(1, 0) = 2
  a(2, 0) = 3
  a(0, 1) = 2
  a(1, 1) = 1
  a(2, 1) = 0
  a(0, 2) = 3
  a(1, 2) = 3
  a(2, 2) = 5
  b = matrix_inverse(a)
  for i = LBound(b) to UBound(b)
    For j = LBound(b(i)) To UBound(b(i))
      s = s + "b(" & i & ")(" & j & ") = " & b(i)(j) & chr(10)
    Next j
  Next i
  MsgBox s
End Sub

The result is

b(0)(0) = -0,833333333333333
b(0)(1) = 1,66666666666667
b(0)(2) = -0,5
b(1)(0) = 0,166666666666667
b(1)(1) = 0,666666666666667
b(1)(2) = -0,5
b(2)(0) = 0,5
b(2)(1) = -1
b(2)(2) = 0,5
#4

(For the record only?)
Yes similar tests also worked for me. (I didn’t send the results to a MsgBox, but inspected them with the debugger.)
In addition I applied (fa being a FunctionAccess instance)
fa.callFunction("MINVERSE", Array(inData))
as well with inData being a “UnoStyleSequence” of row-segments (object(0 to 2) e.g; no array-concept in Uno!) as with inData passed from a function-call in Calc. In the second case Basic shows and addresses the elements regarding its own array-concept (being te same as in many programming systems).
The fa-calls (also with diffenent matrix-functions; only quadratic matrices tested) worked flawlessly with both these array representations for the input, but always returned the results the UnoStyle.
The funny (annoying) fact now is that such a result can’t be returned to Calc without an explicit conversion to the ordinary matrix representation.
Obviously there is an efficient core algorithm for the conversion between the two mentioned representations of 2D-arrays (or making the distinction obsolete), but no respective service is available for the API-user.

@mikekaganski: Do you think a respective feature-request (as a bug) makes sense?
Of course, It’s simple to supply the functionality in any script language, but working with the two needed nested loops in an interpreting language should be expected to be very inefficient (time * 100?) and actually is in Basic.

#5

I don’t see a big problem implementing it here, given that we already have the syntactic sugar passing multi-dimensional arrays to API as arrays of arrays.

So please file such a request (and maybe refer to the code for the context).

#6

I surely don’t understand the code sufficiently. When I was younger and first (about 1993 probably) had reason to look into C-code written by some of my students, I found the language too ugly to be worth serious studies. Now I’m too old.

Concerning the API, however, my problem isn’t code, but concepts: How can an API-service (with its interfaces) take and return arrays if there are no arrays at all?

#7

I measured the speed. Converting from an array of arrays to a 2-dimensional array takes about 3 seconds for 1000x1000, which seems satisfactory to me for LO Basic.

#8

why not:

 ```python
 import numpy as np
 arr = np.matrix([[1, 2, 3], [2, 1, 0], [3, 3, 5]])

 arr_inv = np.linalg.inv(arr)
 print(arr_inv)
 ```

the output:
[[-0.83333333 0.16666667 0.5 ]
[ 1.66666667 0.66666667 -1. ]
[-0.5 -0.5 0.5 ]]

#9

Why?
You surely noticed that the problem wasn’t the matrix inversion. LibO comes with a very efficient algorithm for the purpose. It may be mathematically (end even on the level C-code) the same one as provided by Python. No import needed. For any routine the API gives access to the inverting function by efficient means.

The annoying fact is that the result is provided in a “format” allowing to fill a SheetCellRange with it, but not directly accepted as a function result by Calc.

Suppose you calculated your np.linalg.inv(arr) intending to fill it into a CellRange (not as a formula result, but as content) one time, but to return it as a formula result another time. How is this done?

#10

Of course, if I’ll have to work in calc I’m just use the Calc-function, …

If I need it to do from Script, so its done via:

import numpy as np
arr = np.array([[1, 2, 3], [2, 1, 0], [3, 3, 5]])

arr_inv = np.linalg.inv(arr)
output = tuple(map(tuple, arr_inv))
#…
some_cellrange.setDataArray( output )
# of course the size of  'some_cellrange' should be the same as 'output'

[edit: I’m pretty sure the efficience is on the same scale as the Calc-function from Calc]

#16

question is that can this numpy be imported in BASIC function /sub or have to set up Python lib in LO
and use Python instead of BASIC

#11

goal

My goal is that i need to solve some linear equations for some electrical circuits
rectangular matrix - not singular - resistive model

with variable Matrix size = Resistive model so no complex numbers

Later on i would like to use complex numbers model but not possible using integrated BASIC in LO

general description

problem is that i will have some rows representing data’s on one sheet
so macro has to read a column and determine the size of the matrix needed so solve the linear equations then do all the matrix calculations on another sheet
independent of the original data’s location

then i have to get the Matrix inversion results and do other calculations to get final results and write back to the first sheet.

my data’s represent a physical electrical circuit so always rectangular and not singular

is there some way to test if input matrix is singular cause of some errors?

so in macro i will built an upper triangular matrix then do the inverse then keep going till i get the final results required

@karolus
interesting using Phython
but this won’t work in BASIC !
BASIC is already included in LO so easier to use

i can sent an LO file with macro and can be use directly with Python user first need to install python which is complicated for a basic users!

i would prefer to work with Python which i use in another soft call Blender 3D modelisation

never took the time to install LO to use Python
and looks much more complicated
but looks interesting even using numpy can it also use sympy and Plot modules?
can it do Complex Matrix too using sympy ?

@mikekaganski

i use the safer version of LO not the 7 version yet

i tested your little code snippet and looks like it works
i can see the results on MSG box - very nice

i can see that the uno command has been changed for this array function call

from

svc = createUnoService(“com.sun.star.sheet.FunctionAccess”)

to

matrix_inverse = createUnoService(“com.sun.star.sheet.FunctionAccess”).callFunction(“MINVERSE”, array(a))

i did search the WEB for this and could not find anything for latest LO version

so thanks for pointing it out

Note:
is it possible to upload sample file in LO thread ?
i could use another free site call PASTALL and change file name!

will try to modify my macro and see if it works

will come back later on

thanks a lot to all for feedback
have a nice day

#12

Some Facts:
python is also included (in case of windows-os resp. Downloads directly from tdf) or in case of most Linux-distributions LO is linked to the original python3 which comes also from the Distroprovider.

the APSO-extension is very good in terms of Organisation-of-python-code.
BASIC seems mostly easier for users who does basically copy&paste

#13

https://help.libreoffice.org/latest/en-US/text/sbasic/guide/basic_2_python.html?DbPAR=BASIC

https://help.libreoffice.org/latest/en-US/text/sbasic/python/python_2_basic.html?DbPAR=BASIC

It was only me changing the two lines, one creating a service, and one using that service’s method, into one. It works exactly the same, and exactly the same function as mentioned on old forum topic still works fine.

1 Like
#14

(We again mix up answers and comments. The long explanation by the OriginalQuestioner surely wasn’t an answer.)
@Rickblender2017:
If you actually need quasi inverses for singular matrices -and this eventually with complex elments- you won’t find the needed functionality in LibO, I’m afraid. Isn’t there to high a risk to invest a lot of time/energy in approaches you will not be able later to generalize in the needed way?
In addition: Inversion of large matrices will need much of all your resources if there can be large matrices - and in electric engineering I wouldn’t expect only small ones.
Summary: Might it not be better to look for professional software made by experts (in engineering and in maths as well)?

BTW: If insisting on the usage of spreadsheets you may already know this article I found interesting, but did not yet study thoroughly:

(A few years ago I also experimented with matrix inversion based on (real/imaginary decomposition, and was surprised that it worked at all.)

#15

True. Today we already have the related button named “Suggest a solution”, but nothing stops people from making mistakes :frowning:

#17

i can use Numpy / Python in other soft to solve complex matrix and will do that later on

as a first approximation for resistive circuit CALC can do it with Matrix MACRO
it should give an answer inside 5 % of precision which is good enough

also easy to share with other people

i hope Complex Matrix can be integrated some how in LO BASIC
it would make LO much more powerful and more people would use it

#18

I hope Complex Matrix can be integrated some how in LO BASIC
it would make LO much more powerful and more people would use it

  1. You sure? A few users might be glad. Others would get furious as soon as they notice that their sheets aren’t sharable with Excelists. You think Calc might defeat Excel? So God help!
  2. That’s all but an easy hack. Current spreadsheets cannot keep a complex number as a pair of Double in a single cell. What’ s called a “complex number” then in a misleading way is actually a string acceptable under a specific (regular) syntax. For even the simplest step of arithmetics the operands need to be analysed by string functions before anything relevant can be done. To return the result wrapping into a string is then required. The strings are based on decimal representations. Expensive conversions needed all the time! Every chance to get rounding and comparison problems!
    When I considered a set of procedures or probably even an extension “TrueComplex”, I came to the conclusion that reasonable complex arithmetic requires pairs of sheets. But then you need a very efficient and very special formatting for the cells of such sheets allowing to display the full result in one of the sheets (or in both of them).
    The idea is free. Have a lot of fun implementing it.
#19

might be possible using Numpy with Python in LO
but then still complicated
so i will do it using other soft with Python and Numpy
faster to do and Numpy is a lot more faster anyway

i’m gone work this afternoon to see if i can modify my Macro for real Matrix and see if i can make it work

after i can continue to add other calculations for doing what i want

so thanks for comments

and be back to let you know if it works

#20

i got a first problem with array thing

redim A( 4 , 4 )

A = oSheet.getCellRangeByName("B2:d4")

so i dimensions the A array function of input matrix
then try to pass the cell ranges but it does not like it somehow when i do the

oData() = A.getDataArray()

i get error that A is already define

do i need to pass each cell value one by one instead of using
oSheet.getCellRangeByName

any advice for this ?

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