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

- 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.
Open the workbook and press Alt+F11 to inspect the VBA procedure. Identify the IMPRODUCT call that fails and the variables supplied to it.
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.
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.
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.
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.
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.

Multiply the real and imaginary components in VBA
Use numeric arithmetic when repeated IMPRODUCT calls or complex-string parsing remain unreliable.
Test the calculation in worksheet cells
Use worksheet formulas to isolate a VBA-specific problem or perform the calculation on a sheet.
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.

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.




