Excel Error Codes

Fix Excel IMPRODUCT Runtime Error 1004 in VBA

Huma Ashraf ChHuma Ashraf Ch Nov 20, 2026 869 views

Question details

Find out why Excel VBA returns Runtime Error 1004 when IMPRODUCT processes calculated complex numbers and how to continue the calculation.

Fix Excel IMPRODUCT Runtime Error 1004 in VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A VBA procedure passes intermediate results between Excel complex-number functions over a series of calculations.
Observed behavior
WorksheetFunction.ImProduct returns Runtime Error 1004 for certain calculated complex-number strings, although similar calculations may work in worksheet cells.
Before you start

Open the workbook and press Alt+F11 to inspect the VBA procedure. Identify the IMPRODUCT call that fails and the variables supplied to it.

Solution 1Recommended

Inspect and normalize the complex-number inputs

Use this approach to identify a problematic intermediate string before passing it to IMPRODUCT.

Very small floating-point components or unexpectedly formatted strings can make complex-number results difficult to process. Test each intermediate value rather than assuming the first input is the source of the error.

1
Print the inputs

In the VBA editor, add Debug.Print statements immediately before the failing call, such as Debug.Print "input1: [" & v1 & "]", "input2: [" & v2 & "]". Run the procedure and inspect the values in the Immediate window, opened with Ctrl+G.

2
Check intermediate results

Place a breakpoint on the IMPRODUCT line by clicking its margin or pressing F9. Run the procedure and use F8 to step through successive calculations until you find the input or result that triggers the error.

3
Construct values from components

Where the real and imaginary components are available as numbers, use WorksheetFunction.Complex(realPart, imaginaryPart, "i") to construct each complex value instead of manually concatenating a string. Pass the resulting values to WorksheetFunction.ImProduct.

4
Handle negligible components

Before calling COMPLEX, check each numeric component against a tolerance appropriate to your calculation. For example, use If Abs(realPart) < tolerance Then realPart = 0. Repeat for the imaginary part only if treating it as zero is acceptable for your results.

Inspect and normalize the complex-number inputs
Choose the tolerance carefully: Rounding a small value to zero changes the calculation. Use a tolerance based on the scale and accuracy your workbook requires.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and compatibility with common Excel formats, making it easier to move everyday workbook tasks. It does not fix Microsoft Excel VBA errors; check macro compatibility before migrating VBA-dependent workbooks.

Open and edit common Excel workbook formats, including XLSX and XLSUse a familiar spreadsheet interface for formulas and calculationsMove everyday spreadsheet work to a lightweight Office suiteUse core Office tools for free
microsoft office alternative - wps office

Frequently Asked Questions

Why does IMPRODUCT return error 1004 in VBA?

WorksheetFunction.ImProduct can raise error 1004 when it cannot process an input or result. Print the exact inputs with Debug.Print and inspect each intermediate value, particularly complex-number strings with very small components.

How can I see the value that caused the error?

Press Alt+F11 to open the VBA editor, add Debug.Print statements before the IMPRODUCT call, and press Ctrl+G to open the Immediate window. You can also set a breakpoint with F9 and step through the code with F8.

Can I avoid complex-number strings in my VBA calculation?

Yes. Keep the real and imaginary components in numeric variables and calculate the product as ac − bd for the real part and ad + bc for the imaginary part. Use WorksheetFunction.Complex only when you need a formatted complex value.

What if IMPRODUCT works in a worksheet but fails in VBA?

Enter the exact VBA inputs in worksheet cells and test them with =IMPRODUCT(A1,B1). If the formula works, you can use worksheet cells for the calculation and read the result from VBA, checking for cell errors first.