How to Compare Formatted Currency Values in Excel VBA
Question details
The user needs to accurately compare numerical values that are formatted as currency text (e.g., with thousands separators) in VBA.

- Product
- Excel VBA
- Device & OS
- not provided
- Scenario
- Comparing userform control values or cell data where numbers are stored or formatted as text containing commas.
- Observed behavior
- Comparing the formatted text directly yields incorrect mathematical results because VBA performs a string comparison rather than a numerical comparison.
Identify the exact names of the form controls or variables holding your currency text, and back up your VBA project before modifying the conditional logic.
Use Val and Replace Functions to Convert to Numbers
This method removes the thousands separator from the text and converts the remaining string into a standard numeric value for accurate comparison.
When VBA evaluates strings like "5,000" and "10,000", it compares characters from left to right. Since '5' is greater than '1', VBA incorrectly determines that "5,000" is larger. Converting these strings to pure numbers resolves this.
By utilizing Application.ThousandsSeparator, you ensure the code works dynamically across different regional settings where the separator might be a space or a dot.
Press Alt + F11 to launch the Visual Basic for Applications editor and locate the UserForm or Module containing your comparison logic.
Use the Replace function to remove the comma (or regional separator) from your first value: Replace(Me.Reg5.Value, Application.ThousandsSeparator, "").
Wrap the Replace function in the Val function to change the raw text into a number: Val(Replace(Me.Reg5.Value, Application.ThousandsSeparator, "")).
Apply the exact same logic to your second value and combine them in your If statement: If Val(Replace(Me.Reg5.Value, Application.ThousandsSeparator, "")) < Val(Replace(Me.Reg6.Value, Application.ThousandsSeparator, "")) Then

Write and Execute Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to automate tasks and evaluate complex data flawlessly. You can implement the exact same string-to-number VBA logic in WPS Office without needing to modify your existing macro code.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your VBA project.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic', or press Alt + F11 to open the code editor.
- 3. Implement the Conversion Logic: Insert the Val and Replace logic directly into your conditional statements, then run the macro to compare your formatted currency values perfectly.

Frequently Asked Questions
Why does comparing "5000" and "10000" as text give wrong results?
When numbers are compared as text strings, VBA evaluates them character by character from left to right. Because the character "5" has a higher ASCII value than "1", VBA considers the string "5000" to be greater than "10000", ignoring the actual length or numerical worth.
Can I use CCur or CDbl instead of Val?
Yes. CCur (Convert to Currency) and CDbl (Convert to Double) are excellent alternatives. In fact, they are often better suited for international compatibility because they automatically respect local decimal and thousands separators defined in Windows regional settings, often eliminating the need for the Replace function entirely.
How do I handle empty form controls during a VBA comparison?
If a text box is completely empty, the Val function will safely return 0 without crashing. However, if you opt to use CDbl or CCur, passing an empty string will throw a 'Type Mismatch' error. It is best practice to validate that the control isn't empty (e.g., If Me.Reg5.Value <> "" Then) before performing the conversion.
What exactly is Application.ThousandsSeparator?
Application.ThousandsSeparator is a built-in Excel property that returns the specific character currently being used by the application to separate thousands (e.g., a comma in the US, or a dot/space in many European regions). Using this instead of a hardcoded "," ensures your macro will work correctly on computers in different countries.




