# Can calc. create a rectangle based on cell data?

I have made a spreadsheet that calculates the height and length of a rectangle

Can calc. create a rectangle based on these two calculations/cells?

edit retag close merge delete

Sort by » oldest newest most voted

Yes, that is possible to do in Calc. E.g. you could add a rectangle shape with given dimensions and position into your sheet Drawpage, using a macro such as the following, then you could pass your two cells as the w and h parameters respectively:

Function CalcDrawRectangle( x As Long, y As Long, w As Long, h As Long, Optional strName As String )
REM Draw a Rectangle shape in the DrawPage of the current Calc sheet.
REM ( x, y, w, h ) units are in 100th of a millimeter.
REM Specify a name to refer to the same Rectangle object, else a new object is added.
Dim oDrawPage as Object, oRectangleShape As Object
Dim rPosition As New com.sun.star.awt.Point
Dim rSize As New com.sun.star.awt.Size
rPosition.X = x
rPosition.Y = y
rSize.Width = w
rSize.Height = h
oDrawPage = ThisComponent.getDrawPages().getByIndex( 0 )
oRectangleShape = ThisComponent.createInstance( "com.sun.star.drawing.RectangleShape" )

If Not IsMissing( strName ) And strName <> "" Then
Dim i As Integer
For i = 0 To oDrawPage.getCount() - 1
If oDrawPage.getByIndex( i ).getName = strName Then
oRectangleShape = oDrawPage.getByIndex( i )
Exit For
End If
Next i
oRectangleShape.setName( strName )
End If

oRectangleShape.setPosition( rPosition )
oRectangleShape.setSize( rSize )

End Function


EDIT 17:13

changed into function, changed function name. Function is now callable from a Calc cell.

EDIT 20:23 added optional Name parameter to keep the same object when updating.

more

Okay thanks - but I had never done a macro before, so I am afraid for I have to ask you some more questions Where do I plot the macro in? Is a "sheet drawpage" the same as an ordinary sheet (just where you do the drawing)? How do I "pass my two cells as the w and h parameters respectively"? Lets say the two cells are Sheet1.B11 and Sheet1.C14

more

Hello @T-buch,

First copy-paste the macro into your LibreOffice as follows:

1. Select the menu "Tools : Macros : Edit Macros...",
2. Double-click on "My Macros & Dialogs",
3. Then Double-click on "Standard",
4. Then Double-click on any Module inside the Standard Library,
5. In the textbox on the right, paste the above macro.

Then you could enter a formula in e.g. cell G1 =CALCDRAWRECTANGLE( 4000;4000;Sheet1.B11;Sheet1.C14)

( 2017-08-01 17:28:39 +0200 )edit

The first two arguments are the X and Y local coordinates specifying the topleft corner of the rectangle in 100th of a millimeter. The rectangle from the above example would appear at 4cm from the top and 4cm from the left side of the Drawpage. NB. if you use this function inside a Calc cell, then changing the referenced cell values ( B11 or C14 ) causes the creation of a new Rectangle object each time. I'll see if it could be adapted to act upon the same Rectangle object per cell.

( 2017-08-01 18:31:24 +0200 )edit

Okay but where do the function draw the rectangle?

( 2017-08-01 18:48:32 +0200 )edit

You can specify where the rectangle is drawn, by changing the first two arguments to the desired coordinates ( instead of 4000,4000 ). You could also make 2 separate cells containing the coordinates and then pass these cells just like B11 and C14.

( 2017-08-01 19:09:57 +0200 )edit

First of all i got no drawings (but also no errors) 4cm from the top and 4cm from the left side I am looking at the sheet where the cells with the function call are?

( 2017-08-01 19:39:15 +0200 )edit
( 2017-08-01 19:49:27 +0200 )edit

The values you specified are too small ( 13,46 and 6 ) ... a height of 6/100th of a millimeter is too small to see, please try something like 13460 and 6000.

( 2017-08-01 20:10:17 +0200 )edit

i updated the function, you could now in cell G4 put something like: =CALCDRAWRECTANGLE( 4000;4000;C4;E4;"G4") , then the same rectangle is used.

( 2017-08-01 20:28:22 +0200 )edit

Chance the values (that make sense) - but nothing happen Just to be absolutely sure - It is supposed that the rectangle is drawn in the actual sheet? and by the way: in which colour

( 2017-08-01 20:35:58 +0200 )edit

That's odd... please verify if the macro runs at all, by adding Msgbox "Done" as the last line before End Function ... the colour of the rectangle is Blue in my case, all aspects of the rectangle can be adjusted to suit your taste.

( 2017-08-01 22:52:13 +0200 )edit