How to Fix Excel VBA Run-Time Error 5 in Conditional Formatting
Question details
The user is attempting to apply conditional formatting via Excel VBA but encounters an invalid procedure call error due to incorrect formula syntax.
- Product
- Excel VBA
- Device & OS
- not provided
- Scenario
- Running a VBA macro to add conditional formatting to a range using the FormatConditions.Add method with a formula expression.
- Observed behavior
- Excel throws 'Run-Time Error 5: Invalid Procedure Call or Argument' because the formula string uses semicolons instead of commas, conflicting with the required list separator.
Verify your computer's regional and language settings, as they dictate whether commas or semicolons are expected as list separators in your system.
Replace Semicolons with Commas in VBA Formulas
Modify your VBA code to use standard comma separators in the Formula1 argument string, satisfying the VBA engine's formatting requirements.
Even if your local regional settings use semicolons as list separators in the standard Excel user interface, the VBA editor generally expects US-centric formatting. This means function arguments within a formula string evaluated by VBA must use commas. Using semicolons in the Formula1 parameter will trigger an Invalid Procedure Call or Argument error.
Press Alt + F11 in Excel to open the Visual Basic for Applications editor, and locate the module containing your conditional formatting macro.
Find the line of code using '.FormatConditions.Add Type:=xlExpression'.
Inspect the 'Formula1' argument (e.g., Formula1:="=$N10=SUBTOTAL(104;$N$10:$N$1000)"). Change the semicolons separating the function arguments to commas (e.g., change to Formula1:="=$N10=SUBTOTAL(104,$N$10:$N$1000)").
Save the changes to your VBA script and press F5 or return to the Excel workbook to run the macro. The conditional formatting should now apply without throwing Run-Time Error 5.
Adjust Windows Regional List Separator Settings
Change your system-wide list separator settings to match your VBA inputs if you must dynamically use specific characters.
Run Macros and Apply Formatting Smoothly in WPS Office
WPS Office provides excellent built-in support for VBA macros and advanced conditional formatting. You can seamlessly run your existing Microsoft Excel scripts, edit VBA code, and automate your spreadsheet tasks in a lightweight, user-friendly environment.
- 1. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm or .xls workbook.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor' to view your code.
- 3. Adjust Formatting Formulas: Update your FormatConditions strings to use standard comma separators directly within the WPS VBA interface.
- 4. Run the Macro: Execute the macro to automatically apply the conditional formatting rules precisely as intended.
Frequently Asked Questions
Why does VBA Run-Time Error 5 specifically happen with conditional formatting?
Run-Time Error 5, 'Invalid Procedure Call or Argument', happens when the string passed to the Formula1 argument uses a list separator (like a semicolon) that the VBA engine rejects. The VBA compiler strictly expects standard US formatting (commas) for arguments inside string-based formulas.
How do I check my computer's default list separator?
You can check your system's default list separator in Windows by opening the Control Panel, going to 'Clock and Region' > 'Region', clicking 'Additional settings', and viewing the 'List separator' input field.
Can I use Local properties to bypass the list separator issue in VBA?
While cell formulas can sometimes be assigned using the FormulaLocal property to respect regional settings, the FormatConditions.Add method relies on the standard Formula1 argument. This argument natively expects comma-separated parameters regardless of your localized UI settings.
Will changing my Windows regional settings affect other applications?
Yes. Modifying the system-wide list separator in the Windows Control Panel changes how lists, CSV files, and database parameters are formatted globally across all installed programs on your computer.
