VBA & Macro Problems

How to Fix an Overflow Error in a VBA Do Loop

Maira Mehtab Sep 22, 2026 868 views

Question details

The user encounters an overflow error when running a VBA procedure at the condition statement of a Do Loop, despite variables being declared as Double.

Product: Spreadsheet
Device & OS: not provided
Scenario: Capturing numerical values with comma decimal separators via an InputBox and processing them within a Do Loop condition.
Observed behavior: An overflow error halts the macro at the loop condition, indicating the numerical values are exceeding the variable limits or failing to convert correctly due to regional decimal settings.

Before you start

Verify your system's regional settings to see which decimal separator (comma or period) is currently active, and save a backup of your code before making changes.

Solution 1Recommended

Validate and Convert the InputBox Value

Properly convert the InputBox string to a Double data type to account for regional decimal separators.

VBA defaults to US standard number formats under the hood. When users input a number using a comma as a decimal separator via an InputBox, VBA might misinterpret the string or fail to cast it correctly to a Double data type, ultimately resulting in an overflow.

Capture input as a string

Declare a temporary String variable to catch the exact text entered by the user in the InputBox before doing any math operations.

Convert string using CDbl

Use the CDbl() function on the captured string variable. CDbl is a locale-aware VBA function that will correctly translate a comma decimal separator into a Double based on your Windows regional settings.

Apply absolute value calculation

Once safely converted into the Double variable 'M', run the Abs(M) calculation in the Do Loop condition. The locale-aware conversion prevents unexpected massive number assignments.

System Settings Configuration: If your macro is distributed across computers in different countries, consider standardizing the input by using Replace(inputStr, ",", ".") or building custom validation before casting to Double.

Solution 2

Check Data Types and Infinite Loop Logic

Confirm that variables are not accidentally constrained by smaller data types elsewhere in the code, and ensure the loop iterates correctly.

Advanced VBA Support in WPS

Debug VBA Errors Seamlessly with WPS Spreadsheet

WPS Spreadsheet features a comprehensive built-in VBA editor, allowing you to easily write, debug, and troubleshoot complex macros natively.

1. Open the Developer Tools: Launch WPS Spreadsheet, navigate to the Developer tab, and click the 'Visual Basic' icon to open the code editor.
2. Set a Breakpoint: Locate the Do Loop causing the overflow error. Click in the left margin next to the code line to set a red breakpoint indicator.
3. Run and Inspect: Run the macro. When the execution pauses at the breakpoint, hover your mouse over the variable 'M' to inspect its exact value and data type.
4. Use the Watch Window: Add 'M' and 'ZeroLimit' to the Watch Window to monitor how their values change during each loop iteration to pinpoint the overflow trigger.

Full compatibility with Microsoft Excel VBA macros and syntaxBuilt-in debugging tools to trace code execution and watch variablesLightweight environment for faster processing of complex loopsExcellent multi-platform compatibility and seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

What does 'Overflow Error (Run-time error 6)' mean in VBA?

An overflow error occurs when you attempt to assign a value that exceeds the maximum or minimum limit of its declared data type. For example, trying to assign the number 50,000 to an Integer variable (which caps at 32,767) will cause an overflow.

Why does a comma decimal separator cause issues in VBA InputBoxes?

VBA internally uses the period (.) as the standard decimal separator regardless of local settings. When an InputBox collects a comma-separated number as a string, implicit conversions might fail or misinterpret the comma, resulting in massive unintended values.

What is the maximum limit of a Double data type in VBA?

A Double data type can hold massive values, ranging from roughly -1.79769313486231E308 to 1.79769313486231E308. If a Do Loop breaks this limit, it is highly likely that the code has entered an infinite scaling mathematical loop.

Does WPS Office support Excel VBA macros?

Yes, WPS Spreadsheet offers deep compatibility with Microsoft Excel VBA macros. You can enable, edit, and run existing .xlsm and .xlsb files without needing to rewrite your code.

Excel & Data Problems