VBA & Macro Problems

How to Fix VBA Average by Month Dictionary Errors in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to calculate monthly averages using a VBA macro, but the script fails when trying to access array elements stored inside a single Dictionary object.

Product: Excel 2016
Device & OS: not provided
Scenario: Grouping dates and values by month to compute averages via a VBA macro.
Observed behavior: The macro throws an error when attempting to read or modify array elements such as dict(key)(0) and dict(key)(1) from the dictionary item.

Before you start

Before modifying your macro code, open your VBA Editor and ensure that the 'Microsoft Scripting Runtime' reference is enabled under Tools > References, which is required to declare and use Dictionary objects.

Solution 1Recommended

Use a Two-Dictionary Approach for Totals and Counts

Avoid storing arrays inside a single dictionary. Instead, utilize two separate dictionaries to track monthly totals and counts independently, which prevents array access errors.

VBA dictionaries do not handle embedded array modifications well. When you retrieve an array from a dictionary, VBA often returns a copy rather than a reference. By separating your logic into two flat dictionaries, your code becomes highly reliable and much easier to troubleshoot.

Declare Two Separate Dictionaries

In your VBA module, create two Dictionary objects. Use one to accumulate the sum of the values (e.g., dictTotals) and the other to count the occurrences (e.g., dictCounts).

Populate Both Dictionaries

Set up a loop to iterate through your data rows. For each row, extract the month identifier. Add the current row's value to dictTotals(monthKey) and increment dictCounts(monthKey) by 1.

Calculate and Output the Averages

Iterate through the keys of your dictionaries. To get the average for each month, divide the stored total by the stored count using the formula: MonthlyAverage = dictTotals(key) / dictCounts(key), then write this result to your worksheet.

Pro Tip: Always include an If statement to verify that dictCounts(key) is greater than 0 before performing the division to prevent runtime divide-by-zero errors.

Calculate Monthly Averages Seamlessly Using WPS Spreadsheet

If you want to avoid writing complex VBA dictionary scripts altogether, WPS Spreadsheet provides an intuitive PivotTable feature that groups dates and calculates averages in seconds. It also fully supports running your existing .xlsm macros.

1. Open Your Data in WPS: Launch WPS Spreadsheet and open the file containing your date and value columns.
2. Insert a PivotTable: Highlight your data range, go to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
3. Group Dates by Month: Drag the Date field into the Rows area. Right-click any date in the generated table, select 'Group', and choose 'Months'.
4. Change Summary to Average: Drag your numerical value field into the Values area. Click on the field's drop-down, select 'Value Field Settings', and change the calculation from 'Sum' to 'Average'.

Fully compatible with Microsoft Excel formats and VBA macro files (.xlsm).Built-in PivotTable grouping functionality to easily average data by month.Lightweight application that processes large datasets quickly and without lag.

microsoft office alternative - wps office

Frequently Asked Questions

Why does assigning an array to a VBA dictionary item cause errors?

In VBA, assigning an array to a dictionary key and then retrieving it yields a copy of that array, not a memory reference. Trying to directly read or modify elements using syntax like dict(key)(0) modifies this temporary copy or fails entirely instead of updating the actual stored array.

How do I enable the Dictionary object in the VBA Editor?

To use Dictionary objects, you need to add a reference to the Microsoft Scripting Runtime library. Open the VBA Editor, navigate to Tools > References in the top menu, scroll down, and check the box next to 'Microsoft Scripting Runtime'.

Can I use VBA Collections instead of Dictionaries for this task?

Yes, VBA Collections can be used, but Dictionaries are strongly preferred for tasks involving grouping unique keys (like months). Dictionaries feature a built-in .Exists method, which makes it significantly easier to check if a month key has already been added.

Excel & Data Problems