Chart macro vba to libre office

English
, , ,
#1

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

#2

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!

2 Likes
Converting an excel spread sheet to Libre Calc "macros will not work"
#3

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

#4

Oh, it’s simple - see here or here

1 Like
#5

I am in a similar position to the original poster, attempting to transition from MS Office to Libre Office. I have a spreadsheet with multiple charts, usually more than one on each sheet. I would like to manipulate axes (with scrollbars) individually so would like to chose charts by name. I have entered oChart = “Chart1” but then get stuck at get diagram. I am a newbie and am struggling to get my head around the concept of ‘Diagrams’
TIA

#6

Learn how to program or use Excel forever.

#7

Thanks for your help, that’s what I am trying to do

#8

Having been in\Being in a similar position to NoatRobot2 …

LO/OO chart and diagram API can be awkward, obscure, difficult, convoluted… and in some aspects are just limited > limitations however which did force me to reexamine what goals/needs were being met, what other ways to illustrate key data, construct tables etc.

try this snippet to get a chart By_Name:
Dim oEmbeddedObjects AS Object
Dim oObj1 AS Object
oEmbeddedObjects = ThisComponent.getEmbeddedObjects()
oObj1 = oEmbeddedObjects.getByName(“name_of_chart”)

#9

Thanks for the response GNK

I am attempting to modify the script suggested by JohnSUN earlier in this thread. I already have the name(s) of the charts and want to modify the axes of each chart individually, not simultaneously (so will have to have a Macro for each chart, which isn’t a problem). I thought if I could replace…

“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”

with something simple like, for a chart named Chart2 …

oChart = “Chart2”
oEmbeddedObject = oChart

then…

oDiagram = oEmbeddedObject

and then try to change the values of any property of the Diagram(?)
All goes well with the Macro until it hits the first property…

oAxis = oDiagram.getXAxis()

where it keels over with BASIC runtime error-Object variable not set (I will probably just want to change the Y axis but that doesn’t matter for the purpose of getting the Macro to run).

I guess I have not correctly identified the chart.

#10

idk - but i have noticed in past that chart-index does not match up with navigator GUI : ie rename Chart1 to B and Chart2 to A, and B is still getByIndex(0) …

#11

And what is the problem then? That you don’t know the magic spells by heart? What did you actually do to analyse the above code? Did you get some picture of the object hierarchies with their properties and methods?

#12

Hi GNK, I have so far failed to find any information on manipulating a chart so in my inimitable style have fudged it!

I have given up on using a scroll bar and will use two push buttons for each chart, an up and a down, executing up and down Macros for just the Y axis.

It’s early days, but I think I have found that if I use oChart = oCharts.getByIndex(0) it will change the first chart on the Sheet and if I change it to oChart = oCharts.getByIndex(1) for the next pair of Macros it will change the second chart.

Who needs to learn to program! :blush: Actually, I think this coding rather than programming!

Thanks for your interest and assistance.

Regards

#13

It would appear your problem is greater than mine. Goodbye

#14

It would appear your problem is greater than mine. Goodbye

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.