Converting an excel spread sheet to Libre Calc "macros will not work"

[k-jackson]

Trying to get one last macro to work. What the macro is suppose to do is resize the Y axis on two different charts based on the data range of the entered data.

I do understand that Libre macros are written in Basic and i have no clue how to change the VBA to basic.

Here is the excel VBA

Sub Set_My_Axis_Values()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    

 'Setting y-axis min and max values
    ActiveChart.Axes(xlValue).MinimumScale = ActiveSheet.Range("B22").Value
    ActiveChart.Axes(xlValue).MaximumScale = ActiveSheet.Range("B23").Value

    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.Axes(xlValue).Select

 'Setting y-axis min and max values
    ActiveChart.Axes(xlValue).MinimumScale = ActiveSheet.Range("B22").Value
    ActiveChart.Axes(xlValue).MaximumScale = ActiveSheet.Range("B23").Value

    Range("A1").Select
    Range("A1").Activate

End Sub

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]

[Zizi64]

@k-jackson

I do understand that Libre macros are written in Basic and i have no clue how to change the VBA to basic.

You must use the Calc API functions instead of the MS Excel VBA functions.

API: Application Programming Interface

[Zizi64]

“Activesheet”:

oSheet = thiscomponent.getcurrentcontroller.activesheet

Of (if you want to use the oController or the oDoc individually in the other part of the code):

oDoc = Thiscomponent
oController = oDoc.Currentcontroller
oSheet = oController.Activesheet

How to get a value from a cell:

XValue = oSheet.getCellrangeByName("B22").Value
YValue = oSheet.getCellrangeByName("B23").Value

[JohnSUN]

Welcome @k-jackson !
If you had searched, you would have probably seen this topic.

Chart macro vba to libre office

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 …

The code there is written out in too much detail, it is boring to read. But in principle, the subroutine you cited in your question can be rewritten approximately like this:

Option Explicit 

Sub Set_My_Axis_Values()
Dim oActiveSheet As Variant
Dim MinY As Double, MaxY As Double
Dim oChart As Variant
Dim oYAxis As Variant
	oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
	MinY = oActiveSheet.getCellRangeByName("B22").getValue()
	MaxY = oActiveSheet.getCellRangeByName("B23").getValue()
	
	For Each oChart In oActiveSheet.getCharts()
		oYAxis = oChart.getEmbeddedObject().getDiagram().getYAxis()
		oYAxis.setPropertyValues(Array("AutoMin","AutoMax", "Min","Max"), Array(False, False, MinY, MaxY) )
	Next oChart
End Sub

This is not a completely accurate translation of your code - this macro does not search for charts by name, the program simply sets the Y-axis parameters for ALL charts on the current sheet.

[k-jackson]

Thanks, JohnSUN

I did read a lot of the topic’s about this and it was just beyond my knowledge skills and abilities.

I put in your code you posted and it work’s great.

Thanks for all your help!!!