Ask Your Question

Can calc. create a rectangle based on cell data? [closed]

asked 2017-08-01 12:48:57 +0200

T-buch gravatar image

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 flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-11 23:23:19.937317

2 Answers

Sort by » oldest newest most voted

answered 2017-08-01 15:48:54 +0200

librebel gravatar image

updated 2017-08-01 20:25:56 +0200

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
    Dim rSize As New
    rPosition.X = x
    rPosition.Y = y
    rSize.Width = w
    rSize.Height = h
    oDrawPage = ThisComponent.getDrawPages().getByIndex( 0 )
    oRectangleShape = ThisComponent.createInstance( "" )

    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 )
    oDrawPage.add( oRectangleShape )

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.

edit flag offensive delete link more

answered 2017-08-01 16:54:00 +0200

T-buch gravatar image

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

edit flag offensive delete link 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)

librebel gravatar imagelibrebel ( 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.

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

Okay but where do the function draw the rectangle?

T-buch gravatar imageT-buch ( 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.

librebel gravatar imagelibrebel ( 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?

T-buch gravatar imageT-buch ( 2017-08-01 19:39:15 +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.

librebel gravatar imagelibrebel ( 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.

librebel gravatar imagelibrebel ( 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

T-buch gravatar imageT-buch ( 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.

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

Question Tools

1 follower


Asked: 2017-08-01 12:48:57 +0200

Seen: 712 times

Last updated: Aug 01 '17