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?
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 )
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.
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
Hello @T-buch1,
First copy-paste the macro into your LibreOffice as follows:
- Select the menu “Tools : Macros : Edit Macros…”,
- Double-click on “My Macros & Dialogs”,
- Then Double-click on “Standard”,
- Then Double-click on any Module inside the Standard Library,
- 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)
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.
Okay but where do the function draw the rectangle?
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.
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?
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.
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.
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
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.
This is what I am doing
Let’s try some remote debugging…
Replace the Msgbox “Done” with:
Dim s As String
s = s & "Visible: " & oRectangleShape.Visible & chr(13)
s = s & "SizeProtect: " & oRectangleShape.SizeProtect & chr(13)
s = s & "MoveProtect: " & oRectangleShape.MoveProtect & chr(13)
s = s & "FillColor: " & oRectangleShape.FillColor & chr(13)
s = s & "LineColor: " & oRectangleShape.LineColor & chr(13)
Msgbox sOkay - you are the patience type J
Visible: True
SizeProtect: False
MoveProtect: False
FillColor: 10079487
LineColor: 0
Please check in the menu "Tools : Options... : LibreOffice Calc : View : Objects" if the value for "Drawing objects" is set to "Show".
@librebel FYI your macro works for me in all respects.
OF NOTE: From video @T-buch1 doesn’t seem to be looking at the first sheet which your macro is set for. It appears to be on second sheet.
Thanks for your confirmation @Ratslinger,
Hopefully these latest remarks would get the macro running for @T-buch1…