How to Create an Excel VBA Chart with Celsius and Fahrenheit Y-Axes
Question details
The user needs to create a chart in Excel using VBA that correctly displays and aligns Celsius and Fahrenheit temperature scales on the primary and secondary Y-axes.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating the generation of a dual-scale temperature chart for reports that require both metric and imperial units.
- Observed behavior
- A VBA script is required to calculate the correct axis bounds (MinimumScale and MaximumScale) so that the primary (Celsius) and secondary (Fahrenheit) gridlines align perfectly.
Ensure that the Developer tab is enabled in your ribbon to access the VBA Editor, and verify that your source data is organized with clear headers before running any macro.
Use VBA to Calculate and Align the Secondary Axis
Write a VBA macro that mathematically links the primary Celsius axis limits to the secondary Fahrenheit axis limits to ensure the gridlines align.
To make a dual-axis chart look professional, the gridlines for both scales must match. Since Fahrenheit equals Celsius multiplied by 1.8 plus 32, we can use VBA to read the primary axis bounds, apply this formula, and set the secondary axis bounds accordingly.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. Go to Insert > Module to create a new blank script area.
Write a macro to set your active chart as a variable. Use code like 'Set cht = ActiveChart' to target the chart you want to modify.
Extract the MinimumScale and MaximumScale values from the primary Y-axis (xlValue, xlPrimary) and store them as variables in your script.
Enable the secondary axis using 'HasAxis(xlValue, xlSecondary) = True'. Set its MinimumScale to '(CelsiusMin * 1.8) + 32' and MaximumScale to '(CelsiusMax * 1.8) + 32'.
Close the VBA editor, select your chart in the spreadsheet, and press Alt + F8 to run your new macro. The axes will instantly align.
Create a Dual-Axis Chart Manually Without VBA
If VBA is too complex for your current project, you can achieve the same result by manually configuring a secondary axis and typing in the calculated bounds.
Automate Your Charts Seamlessly with WPS Office
WPS Spreadsheet offers comprehensive support for VBA and macros, allowing you to execute complex scripts for dual-axis charts just like Microsoft Excel, but in a more lightweight and efficient environment.
- 1. Open Your Data in WPS: Launch WPS Spreadsheet and open your workbook containing the temperature data.
- 2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' to open the VBA editor.
- 3. Paste and Run Code: Paste your axis-alignment VBA code into a new module and execute it to automatically format your dual-axis chart.
Frequently Asked Questions
Why are my primary and secondary axis gridlines not aligning?
Gridlines fail to align when the mathematical ratio between the primary and secondary axis bounds (Minimum, Maximum, and Major Unit) does not match. For temperature, the secondary bounds must strictly equal the primary bounds multiplied by 1.8, plus 32.
How do I show a secondary Y-axis in my Excel chart?
To display a secondary Y-axis, you must first assign a data series to it. Right-click the specific data series in your chart, choose 'Format Data Series', and select 'Secondary Axis' from the Series Options panel.
Does WPS Spreadsheet support Excel VBA macros?
Yes, WPS Spreadsheet supports VBA (Visual Basic for Applications). You can write, edit, and run macros to automate chart creation and data processing just as you would in Microsoft Excel.
