Vba chart y axis limits

The problem with these automatic limits is that they often leave a little too much white space around the outside of the plotted data, especially between the lowest data point and zero.

I modified it slightly, and corrected a minor algebraic error. The following is an entire module, including a user defined variable type scaleAxisScale to contain the axis scaling parameters, the main function procedure that does all of the work fnAxisScaleand a small function procedure that is the UDF interface with the worksheet udfAxisScale.

The procedure returns a four-element horizontal array. The following screen shot shows this in action. The min and max for an unseen axis are entered into B3 and B4. A three member horizontal array of axis min, axis max, and major unit is entered in B8:D8 using the formula shown in E8.

A three member vertical array of axis min, axis max, and major unit is entered in CC17 using the formula shown in D The following further illustrates the UDF. The cells in the upper three outlined rows contain X values 0 and 5 in the first column and Y values 4 and 5 in the second columnwhich are plotted in both charts. The cells in the lower three outlined rows contain calculated values for minimum, maximum, and major unit for the X axis first column and Y axis second column.

The following procedure takes an XY chart, finds the min and max of all series plotted in the chart, passes these to fnAxisScale above, then applies the calculated scale parameters to the chart.

If the chart is not an XY chart, the X axis cannot be rescaled in this way, so the code must be modified. The ScaleChartAxes procedure above can be called in several ways by the following procedures.

Each one is run by the user, and passes one or more charts into ScaleChartAxes for rescaling. This procedure rescales all charts that have been selected using Shift or Ctrl to select multiple charts. Posted: Friday, March 9th, under Chart Axes. Tags: Custom Axis Scale. Comments: I put a couple of formulas on my model and this keeps the date scaling for the horizontal bars in line with the xy scale. The ideal solution to the chart formatting issues would be to define a new Chart class. Then you could loop through all the charts in the worksheet, processing all the scales, formatting and colours along the way using custom functions like this.

Panasonic g85 connecting to pc

There already are the Chart and ChartObject classes, with all of these properties. Jon This is very nice. I was wondering if there is a way to specify the number of tick labels required when calculating the scaling parameters? When constructing panel charts I usually like to limit the number of tick labels.

The code calculates the major and minor spacing. If you need fewer tick labels, the major spacing should be larger. Changing dScale will have an effect on dMin and dMax.

Experiment to find settings that work for your particular chart. I graphing last twelve months of sales in columns and the margin as lines.

Sales are on the primary axis and margin is on the secondary axis. I removed all of the x scale code. The code is applying same values for the secondary axis. Any feedback would be appreciated. Jason — You have to keep your calculations straight, so you use the appropriate values to compute the primary and secondary axis scales.

In my code. Axes xlValue is shorthand for. Axes xlValue, xlPrimary and references the primary Y axis.It only takes a few seconds, but all that time starts to add up. There are various chart objects we can link to worksheet cells; source data, chart titles and data labels can all be linked to cells, but the chart axis is set by hardcoding a number into the Format Axis options window.

I decided to build a more dynamic solution. I turned to my old friend VBA, and started to tinker. Link that formula to a cell and suddenly it is possible to set the chart axis based on a cell value. Hopefully, by the end of this post, you too can share in the automatic cell linked bliss I now experience. Whether on purpose or by accident, Microsoft have made it possible to control various objects with UDFs.

Thankfully, the minimum and maximum values of the chart axis are controllable using a UDF. The animated gif below shows the solution in action. The values in the cell are automatically applied to the chart.

vba chart y axis limits

Whether these values are typed in the cell or created using formulas, they will update the chart. Follow these instructions if the Developer Tab is not visible. Add the code below to the Module as shown. The code for UDFs must be within a standard module to work. Therefore, the best options are:. Any non-numeric value entered as the Value or in the cell reference linked to the value will cause the chart to be reset to automatic. The function is now available in all your workbooks.

If others open the workbook they will be able to see the function, but it will not work. If you want other users to use the function save it in each individual file. I am experiencing the same issues as Divya. Could you possibly forward the file mentioned above to me as well?

I have updated the file in the post to show how to use all axis. So if you download the file see the link in the post you should be able to see how it works. I got the same error. Using the fx for help by filling in, it looks that the syntax is a little different. I was wondering if the script could be adapted to also change the units of the graph stepsize.

vba chart y axis limits

Any idea what name should be used to refer to the attribute of the major units? Yes, easy enough to add the units.Excel offers two ways to scale chart axes. You can let Excel scale the axes automatically; when the charted values change, Excel updates the scales the way it thinks they fit best. Or you can manually adjust the axis scales; when the charted values change, you must manually readjust the scales.

This page shows how to use VBA to accomplish this. There are a few pieces you need to make this technique work. You need a chart, a set of values for the scaling parameters, and some VBA code to change the axis scales.

You need a place to put the axis scale parameters. This example can be expanded to include secondary axes, or to change other charts as well. The cells BC16 can contain static values which the user can manually change, or they can contain formulas with your favorite axis scaling algorithms. MinimumScale.

MaximumScaleand. These properties can be set equal to constant values, to VBA variables, or to worksheet range or named range values, as illustrated in this code sample:. The maximum and minimum values of a category axis cannot be changed, and you can only adjust. If the X axis is a time-scale axis, you can adjust. MaximumScale. MinimumScaleand. You should turn on the macro recorder and format an axis manually to make sure you use correct syntax in your procedure. In the Project Explorer window, find the workbook to which you want to add code.

vba chart y axis limits

Double click on the module to open it. Up will pop a blank code module. If your module does not say Option Explicit at the top, type it in manually. This will save innumerable warnings about errors you already know about because the editor turns the font of the offending code red.

You can use a simple procedure that changes the axes on demand. Select the chart, then run the code. You can type all this into the code module, or you can copy it and paste it in. Select the chart and run the code.

Axis.MaximumScale property (Excel)

Here is the chart after running the code. A more elegant approach is to change the relevant axis when one of the cells within BC16 changes.Learn anything from creating dashboards to automating tasks with VBA code!

By default, Excel will automatically decide the minimum and maximum values of your vertical Y axis. For the most part this will visually work for graphing your data, but there are certain instances where Excel's guessing may make your chart look weird or prevent it from telling the story you are trying to portray. Let's look at a couple of examples to show you what I mean. Waterfall charts are used to show causes for the ups and downs variances between two data points.

They are very good ways to quickly display root causes for changes in metrics and are used quite frequently in the corporate world. And this brings us to a fork in the road where you will need to make a decision based on your audience. However, if you were a journalist writing a piece on the economy for the general public you might not want to risk this distorted view as some in your target audience may not be able to process both the visual and the data to come to a conclusion.

In case you were wondering, these charts were created with my Waterfall Chart Creator add-in for Excel. If you like Waterfalls you'll love this add-in there's even a free trial! If you wanted to play it safe, you might display your chart with an added metric to right size the data. I would add percent change to the graphic as shown below. There will be times as an analyst where you are not trying to create a pretty chart, you are just trying to simply "analyze" data! This can often times take the form of looking a seasonal trends over multiple years to ensure a forecast is directionally correct.

Let's take a look at the below two line charts that are analyzing crop yields ie what percentage of your land created food you could sell. This might be useful if you are trying to determine if a forecast looks in line with historical trends.

Calculate Nice Axis Scales in Excel VBA

The problem that you might pick up on while looking at the first chart is depending on your data, it may be very difficult to look at the changes for the trended years. Adjusting your minimum and maximum bounds to your chart axis is pretty straight-forward, however it can get rather cumbersome if your data is frequently changing think dashboard and you are required to keep adjust your axis bounds to make the chart useful.

This is where VBA can be extremely useful! Below is a VBA macro I created that will go throw all the charts on your worksheet and adjust the y-axis according to the minimum and maximum values within the charts specific data. Max srs. Min srs.It may be tedious to combine dozens of sheets from different workbooks into one sheet.

Excel VBA: Dynamic Chart techniques Part 1

Full Feature Free Trial day! Step 2: Find a blank range besides source data, says Range E1: G4, add titles as below screen shot shown:.

vba chart y axis limits

Note : In above formulas, A2:A18 is the Price column that you figure out x axis scale based on, B2:B18 is the Weight column that you figure out y axis scale based on. And you can adjust the maximum value or minimum value with changing the numbers in above formulas based on your needs. Step 4: Select the scatter chart, and right click current worksheet name on the Sheet tab bar, and select the View Code from the right-clicking menu.

Step 5: In the opening Microsoft Visual Basic for Applications window, copy and paste the following code into the window:. Note : F3 is minimum value in X axis, F2 is maximum value in X axis, F4 is the tick value in X axis, G3 is minimum value in Y axis, G2 is maximum value in Y axis, G4 is the tick value in Y axis, and you can change all of them based on your needs. Then you will see the minimum value and maximum value of X axis and Y axis all are changed in the selected scatter chart at once.

Log in. Remember Me Forgot your password? Forgot your username? Password Reset. Please enter the email address for your account. A verification code will be sent to you. Once you have received the verification code, you will be able to choose a new password for your account.

Please enter the email address associated with your User account. Your username will be emailed to the email address on file. Read More Free Download Axes xlCategory, xlPrimary. Range "F3". Range "F2". Range "F4". Value End With With Application.

Axes xlValue, xlPrimary. Range "G3". Range "G2".In default mode, Excel auto scales the Y axis — i. Note that the article below describes only a generic approach, one that works for the most part but not for all cases. For other combinations, refer support. In the above section, we were able to determine the maximum Y-axis value of a chart.

We also saw that it is dependent on the Major Unit, which incidentally, also auto scales. Microsoft does not provide a direct answer. What is however most commonly observed is that all major units start with the numbers 1, 2 or 5. This rules extends on both side of the decimal place — the first non zero number has to be 1, 2 or 5. This means, for example, that gridlines will typically be spaced at a distance of 0.

But…this leads to another interesting problem. We know that the number of gridlines are not constant. For given max-min Y axis value range, how do we know whether the chart with use a 1. X as the major unit? Another observation comes in handy, the number of gridlines vary but within a permissible limit.

The number of gridlines is typically between 5 and Given both these assumptions, we can construct a grid which can throw up the right value for the y axis major unit interval. The trick is to shortlist all the permissible 1. X intervals and then within that set, pick up the exact interval that will limit the number of gridlines between 5 and The maximum data range value is 5 and minimum is 1. Once we shortlist permissible intervals, we can then determine that a major unit value of 1 will allow us to neatly fit 6 gridlines.

The closest alternatives would have been 12 or 3 gridlines, both of which are impermissible. Having identified the major unit as 1, the next gridline above 5. Note : Bear in mind that we are still talking about default rendering options — as we resize the chart, the maximum Y axis value and the number of gridlines will change. You can download an experimental worksheet containing a grid for determining Y axis maximum value and major unit here or click on the button below:.

Wow — SO nice to see you back posting! Have missed your always helpful posts. Take care! This is very informative and helpful!By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service.

The dark mode beta is finally here.

R leaflet opacity

Change your preferences any time. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.

Sudarshan kriya 20 40 40 mp3 download

I would like to know how to programmatically find and the Y axis maximum of an excel chart when there is more than one available. My end goal is to find the max y-axis values, compare them, and set them both to the greater of the two. If you want both axes to automatically show the same min and max values, you need both to plot the same spread of data.

In this simple example, I used the following data to create a chart, with "primary" plotted on the primary axis and "secondary" plotted, well, you know. In the next table, I've calculated the min and max of all the data. I calculated min and max twice and staggered them to clearly show what I'm doing. I copied the shaded range, celected the chart, and used Paste Special to add the data as new series, in columns, series names in first row.

The resulting chart is shown below left. I've ensured the the new series "pri" is on the primary axis and "sec" is on the secondary axis. Since both axes are using the same min and max data to autoscale, both have the same scales.

Truth table java source code

Below right I've hidden the dummy series by formatting them with no lines and no markers. I've hidden each unwanted legend entry by first clicking on the legend, then clicking on the individual legend entry, then pressing Delete. This technique can also be used to synchronize the axes of multiple charts.

Below are three small charts with different scales top row. In the middle row, the calculated min and max have been added to each chart, causing their axes to autoscale on the same min and max.

In the bottom row, the dummy series have been hidden, leaving behind uniform axis scales. Learn more. Asked 9 years, 10 months ago. Active 4 years, 9 months ago. Viewed 13k times. Do you mean that you are working on a chart with 2 y-axes primary and secondary? Active Oldest Votes. Axes xlValue, xlPrimary ActiveSheet.

Range "A1".


thoughts on “Vba chart y axis limits

Leave a Reply

Your email address will not be published. Required fields are marked *