Overflow error when calculating stock return using Do While Loop in VBA [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed last month.

Improve this question

I am new to programming and I am doing an exercise to calculate stock return using For...Next and Do While Loop. I have no problem with the For...Next part but my code for the Do While Loop part has "overflow" error. I found that the error was because of the division at the end: "Sheets("Task1-Returns").Cells(Counter, 3).Value = MicroSPrice1 / MicroSPrice0", but I dont know how to fix it.

Please let me know how I can fix this. Thank you.

Code for For...Next

Sub CalculateReturn_ForNext()
    'Declare variables
        Dim TeslaPrice0, TeslaPrice1, MicroSPrice0, MicroSPrice1, Counter
    For Counter = 2 To 251
        'Insert date from sheet Raw Data
            Sheets("Task1-Returns").Cells(Counter, 1).Value = Sheets("Task1-RawData").Cells(Counter + 1, 1)
        'Calculate return of Tesla.
            TeslaPrice0 = Sheets("Task1-RawData").Cells(Counter, 2).Value
            TeslaPrice1 = Sheets("Task1-RawData").Cells(Counter + 1, 2).Value
            Sheets("Task1-Returns").Cells(Counter, 2).Value = TeslaPrice1 / TeslaPrice0 - 1
        'Calculate return of MicroSoft
            MicroSPrice0 = Sheets("Task1-RawData").Cells(Counter, 3).Value
            MicroSPrice1 = Sheets("Task1-RawData").Cells(Counter + 1, 3).Value
            Sheets("Task1-Returns").Cells(Counter, 3).Value = MicroSPrice1 / MicroSPrice0 - 1
    Next
End Sub

Code for Do While Loop

Sub CalculateReturn_DoWhile()
    'Declare variables
    Dim TeslaPrice0, TeslaPrice1, MicroSPrice0, MicroSPrice1, Counter
    Counter = 2
    Do While Sheets("Task1-RawData").Cells(Counter, 1).Value <> ""
        'Assign value to MicroSPrice0 and MicroSPrice1
            MicroSPrice0 = Sheets("Task1-RawData").Cells(Counter, 3).Value
            MicroSPrice1 = Sheets("Task1-RawData").Cells(Counter + 1, 3).Value
        'Insert date from sheet Raw Data
            Sheets("Task1-Returns").Cells(Counter, 1).Value = Sheets("Task1-RawData").Cells(Counter + 1, 1)
        'Calculate return of MicroSoft
            Sheets("Task1-Returns").Cells(Counter, 3).Value = MicroSPrice1 / MicroSPrice0
        Counter = Counter + 1
    Loop
End Sub

I tried to calculate stock return using Loop in VBA but received overflow error with the division.

What are the values of MicroSPrice1 and MicroSPrice0 when the error occurs? — Shrotter
– Shrotter, Commented Oct 5 at 14:52
They are regular integers. When I left the code as "Sheets("Task1-Returns").Cells(Counter, 3).Value = MicroSPrice1" or "Sheets("Task1-Returns").Cells(Counter, 3).Value = MicroSPrice0", it ran fine. — Duong Duy
– Duong Duy, Commented Oct 5 at 15:14
There are Run-time error '6': Overflow and Run-time error '11': Division by zero. Which one is it? Is the last (bottom-most) row with a date row 251 or 252? Are you expecting as many resulting rows as there are dates or one less? Also, share some sample data using this markdown generator, e.g., for rows 1 to 11, for both sheets, i.e., for the existing data in sheet Raw and the expected (resulting) data in sheet Returns. — VBasic2008
– VBasic2008, Commented Oct 5 at 18:31
If the last row with a date is row 252, then, to kind of mimic the functionality of the For...Next loop, you need to use Do While Sheets("Task1-RawData").Cells(Counter + 1, 1).Value <> "" (note the + 1). But that doesn't explain why there is a run-time error. — VBasic2008
– VBasic2008, Commented Oct 5 at 18:43

cyberbrain · Accepted Answer · 2025-10-06 14:05:31Z

Your code for the while loop has two issues:

The value check of the cell for the condition of the loop can be improved with Not IsEmpty(Sheets("Task1-RawData").Cells(Counter, 1).Value) (See also https://stackoverflow.com/a/13360963/2846138)
You have to add a RAnge-Check for your divisor, MicroSPrice0. Probably it is related to the first issue above that your loop runs too far down, but nevertheless please include an If to check whether MicroSPrice0 < 1e-100 before you do your calculation - you can then either stop the loop with Exit Do (see https://stackoverflow.com/a/9415163/2846138) or handle the situation differently - default value, custom error message, simply ignoring,... up to you.

Update: I just changed the zero-check in issue 2 to a check for values near zero. The number I picked (1e-100) is a bit of a guess - I just guessed that this number would be small enough for your calculations with financial figures. Effectively the result of that division must fit within the range of a Double data type of VBA - see the link for the exact numbers.

I could reproduce the overflow with the division on positive numbers if the divisor was "nearly zero" - but not exactly zero, as this would cause a different error ("Division by 0") as pointed out in the comments. With a "nearly zero" divisor, even small dividends (but >=1) will cause a result too big to store inside a Double variable.

Bonus tip:

Assign MicroSPrice1 ahead of the loop once with the initial value MicroSPrice1 = Sheets("Task1-RawData").Cells(Counter, 3).Value and inside the loop then just assign MicroSPrice0 = MicroSPrice1 before you read MicroSPrice1 from the cells.

Collectives™ on Stack Overflow

Overflow error when calculating stock return using Do While Loop in VBA [closed]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related