VBA & Macro Problems

How to Create VBA Chart Data by Year and Ship Category

Khadija Khan Oct 1, 2026 869 views

Question details

The user wants to build a chart-data table using VBA that counts active items (e.g., ships) in service for each year and category based on their start and end dates.

How to Create VBA Chart Data by Year and Category Using Macros

Product: Spreadsheet
Device & OS: not provided
Scenario: Generating a summary table for charting by looping through records to count how many items fall within specific date ranges dynamically.
Observed behavior: The current VBA implementation relies on hard-coded categories and dates instead of correctly comparing the item's commissioning and decommissioning date fields against the target year.

Before you start

Ensure that the 'Commissioned' and 'Decommissioned' columns in your raw dataset are formatted as valid Date values, not as Text, to allow mathematical date comparisons in VBA.

Solution 1Recommended

Use Nested VBA Loops with Parameterized Date Logic

Dynamically count active records for each year by iterating through years and categories, applying strict date logic to handle start dates and blank end dates.

Instead of hard-coding criteria, the most reliable approach is to use a For loop that iterates through your required years, and an inner loop for your categories. During each iteration, the script should count records that were commissioned on or before the current year and either decommissioned after the year or not decommissioned at all (Null).

Define Variables and Setup the Target Table

Open the VBA Editor (ALT + F11) and declare your variables for StartYear, EndYear, Category, and your counting logic. Set references to your source data sheet and your target chart-data sheet.

Create the Outer and Inner Loops

Write a `For...Next` loop to iterate through each year (e.g., `For iYear = StartYear To EndYear`). Inside this, create another loop to iterate through each unique ship category.

Apply Accurate Date Comparison Logic

Within the loops, evaluate each record. Ensure the condition checks that `ShipType` matches the current category, the `Commissioned` date is `<= DateSerial(iYear, 12, 31)`, and the `Decommissioned` date is either `Null` (or blank) or `>= DateSerial(iYear, 1, 1)`.

Write the Results

Sum the matching records for the current iteration. Write the current year, category, and total count into the next available row of your chart-data summary table, then proceed to the next iteration.

Use Nested VBA Loops with Parameterized Date Logic

Handling Dates in SQL vs Native VBA: If you are using ADO/SQL within VBA to query the table instead of looping through cells, remember to wrap your constructed date parameters in `#` symbols (e.g., `#01/01/2020#`) so the SQL engine recognizes them as dates.

Advanced VBA Support in WPS

Process Complex VBA Macros with WPS Spreadsheet

WPS Spreadsheet natively supports VBA, allowing you to run custom macros, build dynamic chart tables, and automate date filtering without rewriting your Microsoft Excel scripts.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the workbook containing your raw dataset.
2. Access the VBA Editor: Go to the 'Tools' tab and click on 'VBA Editor', or simply press ALT + F11 on your keyboard.
3. Insert Your Code: Right-click your workbook project in the project pane, select 'Insert' > 'Module', and paste your chart data loop script.
4. Run the Macro: Press F5 or click the 'Run' button in the toolbar to execute the script and automatically generate your yearly summary table.

Seamlessly executes Microsoft Excel VBA macros and scriptsHigh compatibility with .xlsm and .xlsb file formatsBuilt-in VBA editor for quick code adjustments and debuggingLightweight application that handles large datasets smoothly

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle blank dates in my VBA logic?

You can handle blank dates by using the `IsNull()` function or by checking for empty strings (`""`) depending on how the cell data is read into VBA. In SQL queries, use the `IS NULL` operator.

Can I use SQL queries directly in VBA to count records?

Yes, you can use ADODB connections to run SQL `SELECT COUNT(*)` queries with `WHERE` clauses directly against your worksheet or Access database, which is often faster than looping through rows.

Why is my VBA date comparison ignoring the year limits?

This usually happens if dates are stored as text. Ensure your spreadsheet column is formatted as Date, and use the `DateSerial(Year, Month, Day)` function in VBA to build accurate date boundaries for your comparisons.

Excel & Data Problems