Function Problems

How to Compare Formatted Currency Values in Excel VBA

Rana GarciaRana Garcia Nov 2, 2026 870 views

Question details

The user needs to accurately compare numerical values that are formatted as currency text (e.g., with thousands separators) in VBA.

How to Compare Formatted Currency Values 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press Alt + F11 to launch the Visual Basic for Applications editor and locate the UserForm or Module containing your comparison logic.

2
Strip the Thousands Separator

Use the Replace function to remove the comma (or regional separator) from your first value: Replace(Me.Reg5.Value, Application.ThousandsSeparator, "").

3
Convert the String to a Value

Wrap the Replace function in the Val function to change the raw text into a number: Val(Replace(Me.Reg5.Value, Application.ThousandsSeparator, "")).

4
Update the Comparison Statement

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

Use Val and Replace Functions to Convert to Numbers
Handling Decimals in VBA: The Val function always assumes a period (.) is the decimal separator regardless of regional settings. If your locale uses commas for decimals, consider using the CDbl or CCur functions instead to avoid type mismatch errors.
Advanced Spreadsheet Management

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. 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your VBA project.
  2. 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. 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.
100% compatible with Microsoft Excel VBA syntax and functionsLightweight and fast execution of complex automated macrosBuilt-in developer tools for tracking variables and debugging codeFree to download and integrates smoothly with existing Excel files
microsoft office alternative - wps office

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.