Fix Excel VBA Run-Time Error 5 With Empty Array()
Question details
The user needs to resolve Run-time error 5 when Excel VBA creates or stores an empty array.

- Product
- Microsoft Excel VBA
- Device & OS
- Windows 8.1
- Scenario
- VBA code assigns Array() to create a zero-length array or stores the result in a Scripting.Dictionary.
- Observed behavior
- Run-time error 5 occurs on some Excel and Windows configurations, including a reported Excel 2010 and Windows 8.1 combination.
Identify the Excel and Windows versions on the affected computer, and locate the VBA line that calls Array(). Check whether the error also occurs when the result is stored in a Scripting.Dictionary.
Update Office and Windows, then retest the VBA code
Use this approach when a platform-specific VBA issue may be causing the error.
In Excel, open File > Account > Update Options > Update Now, if available. In Excel 2010, check File > Help for update options.
Open Windows Update in Settings or Control Panel, depending on your Windows version, and install available updates.
Open the workbook, press Alt+F11, and run the affected macro from the VBA editor. Check whether the Array() line still produces Run-time error 5.
Create an empty array from a dictionary
Use this workaround if Array() still fails on the affected configuration.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easier to move everyday workbook tasks between applications. It does not fix Excel's VBA error; test any macros separately before migrating.

Frequently Asked Questions
Why does Array() cause Run-time error 5 in Excel VBA?
Some Excel and Windows configurations have exhibited a platform-specific VBA issue when Array() creates a zero-length array. Check Office and Windows updates and test the same macro on the affected configuration.
How can I create an empty array without calling Array()?
In the VBA editor, create an empty Scripting.Dictionary with CreateObject("Scripting.Dictionary") and assign its Items property to a Variant, such as data = dict.Items.
Can I add items to the dictionary before reading its Items property?
Not if you need an empty array. Read dict.Items from a newly created dictionary before adding any entries.
Will WPS Office fix this Excel VBA error?
No. WPS Office is an alternative for spreadsheet work, but it does not repair Microsoft Excel's VBA runtime. Test macro-dependent workbooks separately before moving them.




