Ask Your Question
0

Chart macro vba to libre office

asked 2021-02-11 02:54:16 +0200

420_rottie gravatar image

Im trying to figure out how to use maco to adjust min-max scale value if x & y axis of calc chart,

I found a code but it was writen on VBA

Function setChartAxis(sheetName As String, chartName As String, MinOrMax As String, _ ValueOrCategory As String, PrimaryOrSecondary As String, Value As Variant)

'Create variables Dim cht As Chart Dim valueAsText As String

'Set the chart to be controlled by the function Set cht = Application.Caller.Parent.Parent.Sheets(sheetName) _ .ChartObjects(chartName).Chart

'Set Value of Primary axis If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _ And PrimaryOrSecondary = "Primary" Then

With cht.Axes(xlValue, xlPrimary)
    If IsNumeric(Value) = True Then
        If MinOrMax = "Max" Then .MaximumScale = Value
        If MinOrMax = "Min" Then .MinimumScale = Value
    Else
        If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
        If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
    End If
End With

End If

'Set Category of Primary axis If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _ And PrimaryOrSecondary = "Primary" Then

With cht.Axes(xlCategory, xlPrimary)
    If IsNumeric(Value) = True Then
        If MinOrMax = "Max" Then .MaximumScale = Value
        If MinOrMax = "Min" Then .MinimumScale = Value
    Else
        If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
        If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
    End If
End With

End If

'Set value of secondary axis If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _ And PrimaryOrSecondary = "Secondary" Then

With cht.Axes(xlValue, xlSecondary)
    If IsNumeric(Value) = True Then
        If MinOrMax = "Max" Then .MaximumScale = Value
        If MinOrMax = "Min" Then .MinimumScale = Value
    Else
        If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
        If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
    End If
End With

End If

'Set category of secondary axis If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _ And PrimaryOrSecondary = "Secondary" Then With cht.Axes(xlCategory, xlSecondary) If IsNumeric(Value) = True Then If MinOrMax = "Max" Then .MaximumScale = Value If MinOrMax = "Min" Then .MinimumScale = Value Else If MinOrMax = "Max" Then .MaximumScaleIsAuto = True If MinOrMax = "Min" Then .MinimumScaleIsAuto = True End If End With End If

'If is text always display "Auto" If IsNumeric(Value) Then valueAsText = Value Else valueAsText = "Auto"

'Output a text string to indicate the value setChartAxis = ValueOrCategory & " " & PrimaryOrSecondary & " " _ & MinOrMax & ": " & valueAsText

End Function

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2021-02-11 12:03:41 +0200

JohnSUN gravatar image

It's not much harder in Calc than in Excel:

Sub ResizeAxis
Dim oSheet As Variant
Dim oCharts As Variant
Dim oChart As Variant
Dim oEmbeddedObject As Variant
Dim oDiagram As Variant
Dim oAxis As Variant
Rem Choose the sheet with the chart (in this case, let's take the active sheet)
  oSheet = ThisComponent.getCurrentController().getActiveSheet()
Rem Get all the charts on this sheet
  oCharts = oSheet.getCharts()
  If oCharts.getCount() < 1 Then Exit Sub

  oChart = oCharts.getByIndex(0)    ' Get First Chart or Enumerate all 
' of them with For i = 0 To oCharts.getCount()-1
  oEmbeddedObject = oChart.getEmbeddedObject()  ' Get Object in selected frame
  oDiagram = oEmbeddedObject.getDiagram()   ' Get diagram from it

Rem Now change values of any property - in this case XAxis and YAxis:
  oAxis = oDiagram.getXAxis()
  oAxis.AutoMin = False
  oAxis.AutoMax = False
  oAxis.Min = oAxis.Min / 2
  oAxis.Max = oAxis.Max * 1.5
  oAxis = oDiagram.getYAxis()
  oAxis.AutoMin = False
  oAxis.AutoMax = False
  oAxis.Min = oAxis.Min - 10
  oAxis.Max = oAxis.Max + 10
End Sub

Please remember that not every chart type will allow you to change the X axis!

edit flag offensive delete link more

Comments

That was great !!! Big help, now i just need to figure out to how set the axis min-max value from cell

420_rottie gravatar image420_rottie ( 2021-02-11 12:47:51 +0200 )edit

Oh, it's simple - see here or here

JohnSUN gravatar imageJohnSUN ( 2021-02-11 14:38:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-02-11 02:54:16 +0200

Seen: 51 times

Last updated: Feb 11