How to Set Markers on an Excel Combo Chart Series Using VBA
Question details
The user needs to set markers on an Excel combo chart series using VBA, but the MarkerStyle property is being ignored.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing VBA macros to format a combo chart, specifically aiming to add markers to a data series that uses a secondary axis.
- Observed behavior
- The VBA code runs without errors but fails to apply the MarkerStyle property to the series, even though the same markers can be successfully added manually through the user interface.
Ensure your workbook is saved in a macro-enabled format (.xlsm) and that you have enabled macros in your Trust Center settings before running or testing VBA scripts.
Verify Chart Type and Target Specific Series Explicitly
Ensure you are targeting the exact series object and enforcing the correct chart type before applying marker properties.
When working with combo charts, especially those utilizing secondary axes, VBA can sometimes fail to apply marker properties if the chart type of the specific series isn't explicitly defined first. A series might default to a state that doesn't support markers until explicitly told to behave like a line chart with markers.
Begin by explicitly defining the chart object in your VBA code, for example: Set myChart = ActiveSheet.ChartObjects("Chart 1").Chart
Specify the exact series index within the combo chart that requires markers: Set mySeries = myChart.SeriesCollection(2)
Ensure the series chart type is explicitly set to a line with markers before applying styles: mySeries.ChartType = xlLineMarkers
Assign the MarkerStyle, MarkerSize, and MarkerForegroundColor properties directly to the targeted series object.
Test the Code on a Standard Line Chart
Isolate the issue to determine if it is a specific combo chart limitation or a broader VBA syntax error.
Create and Format Charts with Macros in WPS Spreadsheet
WPS Spreadsheet offers robust VBA and macro support, allowing you to easily automate complex formatting tasks like adding markers, managing combo charts, and handling secondary axes seamlessly.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open your .xlsm macro-enabled file.
- 2. Access the Macro Editor: Press Alt + F11 to open the WPS Macro Editor interface.
- 3. Run Chart Formatting Scripts: Insert or edit your chart formatting module and execute the VBA script directly to apply your desired marker styles.
Frequently Asked Questions
Why does my VBA code work for standard charts but not combo charts?
Combo charts contain mixed series types (e.g., columns and lines). If a series is initially processed by VBA as a column type, applying the MarkerStyle property will silently fail. You must explicitly set the ChartType of that specific series to xlLineMarkers before applying marker styles.
What is the correct VBA syntax to change marker size?
Once you have correctly targeted the series, you can adjust the size of the markers using the MarkerSize property. For example: ActiveChart.SeriesCollection(1).MarkerSize = 8.
Can I apply custom colors to chart markers using macros?
Yes. You can use properties like MarkerBackgroundColor and MarkerForegroundColor along with standard RGB values (e.g., RGB(255, 0, 0) for red) to fully customize marker colors via VBA.
