Create a dynamic left axis in Calc

English
#1

Is it possible to create a dynamic left axis in a chart in Calc?
If I set the minimum at say 10,000 and the data input is in the range 10,000 to 30,000 it shows the 10,000 at the bottom left of the chart, so all good BUT the data for the chart can change significantly say with the range 1,000,000 to 3,000,000 and I want the range of the left axis to dynamically change so that the 1,000,000 now is shown in the bottom left of the chart and the range is out to 3,000,000.
If I select Automatic it adjusts the range as expected BUT starts the data point half way up the chart instead of in the bottom left corner?

Format Axis / Scale / then Automatic selected as Minimum causes the data point to start mid way up of the chart with “0” in the bottom left?

Hopefully I have explained that clearly enough for people to understand and someone may have a solution for me.

#2

Please upload your ODF type sample file with some different data set, and with one or more chart.

#3

Hi Zizi I thought this would be just a settings selection that I was unaware of but see attached file. And thank you for your interest.
Monthly and interest and running totals.ods (20.3 KB)

#4

Here is a macro for rescaling the y axis of a specific chart automatically, when you change the input value in the cell B10.

Monthly and interest and running totals_Macro.ods (24.1 KB)

#5

…and here is the macro code:

REM  *****  BASIC  *****
Option explicit 


function RescaleYmin(ShNr as integer, ChartName as string, Ymin as double) as string

 dim oDoc as object
 dim oSheets, oSheet as object
 dim oGrObjects, oGrObject as object
 dim oCharts, oChart as object
 dim oChartModel as object
 dim oYAxisProp as object
 dim iNumOfGrObjects, iNumOfCharts, i as integer
 dim sResult as string
 
 
	sResult = "Error"
	oDoc = Thiscomponent
	oSheets = oDoc.Sheets
	oSheet = oSheets.getByIndex(ShNr-1)
	oGrObjects = oSheet.DrawPage 
	iNumOfGrObjects = oGrObjects.Count
	For i = 0 to iNumOfGrObjects-1
		oGrObject = oGrObjects.getByIndex(i)
		
		If oGrObject.Name = ChartName then
			oYAxisProp = oGrObject.Model.Diagram.YAxis
			oYAxisProp.AutoMin = false
			oYAxisProp.Min = Ymin
			sResult = "Done"
		end if
	next i	

 RescaleYmin = sResult
End function
#6

Zizi Wow thank you very much that is just perfect, it works at all different scales. I am not sure how to get this to run in the real sheet but I have tried copy and paste of your code into the real sheet under Standard Module1 and tried to run it but I get a BASIC runtime error.
Argument is not optional at 20 oSheet = oSheets.getByIndex(ShNr-1)
It is Sheet 1 of

#7

On the sheet in question: =RESCALEYMIN(SHEET();X1;Y1 where
– SHEET() returns the current sheet’s index number
– cell X1 provides the name of the chart on that sheet
– Y1 provides the minimum value for the y-axis.

103407.ods (26.8 KB)

#8

Please upload that file here.

#9

Zizi Please see attached file. I have Assigned your macro and copy pasted the yellow boxes at A23 to B25 but I get an error and no scaling occurs. I have two charts in this sheet that I want to scale if possible?

Scaled y min rates and rewards predictions.ods (82.3 KB)

As an aside the file with your macro in that you kindly sent to me performs as designed and scales the chart BUT if I choose Tools/Macros/Run and select rescaleYmin it produces an error
A Scripting Framework error occurred while running the Basic script Standard.Module1.RescaleYmin.

Message: wrong number of parameters!

#10

Villeroy Thank you for that but I must admit that I do not understand it. Running the scaling by entering a number at K3 generates what seems to be a random data chart which does not replicate each time the same number is input???
What am I missing here??

#11

There is not any macro in your new sample file. Where you copied the macro code to?

#12

Zizi I think I have saved it to this sheet, hopefully.
Scaled y min rates and rewards predictions.ods (85.5 KB)

#13

Please add a Name to the chart by
Right click on the chart - Name
Then use that name in the parameters of the macro function.
The macro can identify your chart by on the Sheet number and the Chart name. (Maybe there are more sheets, and more charts in your other documents.)

#14

…and set the Major Interval to automatic for the proper working of the macro controlled Chart. (Or you can modify the maro code for controlling the interval and the maximal values of the Y axis.)

#15

Zizi
Thank you very much I now have a properly scaling chart thanks to your help. I entered the Chartname into the yellow box within the sheet but is it supposed to go into the script code somewhere and not the yellow box at B24???

I would like to run this scaling macro on the 2nd chart in the same sheet which I have just named but not sure of the next step? Tried copying it to the yellow box at name separated by a , but this did not work.
Have set Maximum to Automatic

#16

You neet not only the maximum as automatic, but the major interval too.

Scaled y min rates and rewards predictions_SameMacro_TwoCharts.ods (76.9 KB)

See the passed parameters for the macro calling in the last row of the yellow ranges.

#17

Thank you I have now set the major interval as well and that cleans it up nicely.
Just so that I am clear with my understanding of this there is no requirement to put the name of the two charts into the actual macro script that you have supplied just into the 2 separate yellow boxes? Is that correct?

#18

Yes. The name is a PASSED PARAMETER, one of the three passed parameters of the macro function.

#19

Zizi Thank you again for all of your help and guidance so just the yellow boxes, all good :pray:
I will try not to bother you again for a while. Enjoy your weekend.

1 Like

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