Her er så den samlede makro, som opretter et linjediagram på baggrund af de udfyldte celler (uanset hvor mange rækker).
Resultatet:
Makroen:
REM ***** BASIC *****
Sub GetAllCells
Dim oSheet
Dim oCell
Dim nEndCol As Integer
Dim nEndRow As Integer
oSheet = ThisComponent.Sheets.getByName("Statistik")
nEndCol = getLastUsedColumn(oSheet)
nEndRow = getLastUsedRow(oSheet)
oCell = oSheet.GetCellByPosition( nEndCol + 1, nEndRow + 1 )
Print oCell.AbsoluteName
oAddress = oSheet.getCellRangeByPosition(0,0,nEndCol,nEndRow)
oArea= oAddress.AbsoluteName 'på formen A3:B4
CreateChart (oArea) 'her kalder vi næste subrutine
End Sub
Sub CreateChart (sDataRng) 'Modtager parameter
sName = "Statistik-diagram"
oSheets = thisComponent.Sheets
oSheet = oSheets.getByName("Statistik")
oAddress = oSheet.getCellRangeByName( sDataRng ).getRangeAddress()
oCharts = oSheet.getCharts()
If NOT oCharts.hasByName(sName) Then
oRect = createObject("com.sun.star.awt.Rectangle")
oRect.X = 10000 '1/100mm
oRect.Y = 1000
oRect.width = 20000
oRect.Height= 10000
oCharts.addNewByName(sName, oRect, Array(oAddress), True, False)
End If
oChart = oCharts.getByName( sName )
oChart.setRanges(Array(oAddress))
oChart.HasRowHeaders=True
oChart.HasColumnHeaders=True
oChartDoc = oChart.getEmbeddedObject()
oTitle = oChartDoc.getTitle()
oTitle.String = "Nyhedsbrevet"
' Create a diagram.
oDiagram = oChartDoc.createInstance( "com.sun.star.chart.LineDiagram" )
oChartDoc.setDiagram( oDiagram )
oDiagram = oChartDoc.getDiagram()
oDiagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS
End Sub
Function GetLastUsedColumn(oSheet) As Integer
Dim oCursor
oCursor = oSheet.createCursor
oCursor.GotoEndOfUsedArea(True)
GetLastUsedColumn = oCursor.RangeAddress.EndColumn
End Function
Function GetLastUsedRow(oSheet) As Integer
Dim oCursor
oCursor = oSheet.createCursor
oCursor.GotoEndOfUsedArea(True)
GetLastUsedRow = oCursor.RangeAddress.EndRow
End Function