Why does the use of "Debug.Print" lead to "Overflow" error?

Question

I create a function to sum all the cells within a range as following

Function SumTest(rg As Range) As Double
  Debug.Print "SumTest..." 

  Dim s As Double
  s = 0 ' ------> Show runtime error "Overflow" at this line
  Dim i As Range
  For Each i In rg.Cells
      s = s + i.Value2
  Next i

  SumTest = s
End Function

When I run the function at the immediate window it complains runtime error "Overflow", and it's very weird that the error message disappears after commenting the "Debug.Print" line. Does the function Dbbug.Print relate to double variable initialization? And why does the overflow happen here (I just assign the zero to a variable with Double data type)?

More information added:

I test the function at the immediate window as ?Sumtest(Range("A1:C1")) (The range A1:C1 is filled with trivial test data 1 2 3). And the version of Excel is Office365 on Mac.

To simplify the test, I use another shorter program like this:

Function SumTest2() As Double

  Dim s As Double

  Debug.Print "SumTest2"
  s = 0  ' ---> Still "Overflow" here
  SumTest2 = s
End Function

After having testing a few programs I am considering this maybe a bug for Excel 2016 on Mac. It seems once you using Debug.print then you cannot assign value (0 or other value) to a variable with Double data type (Integer, String or other data types are ok..) any more. Following is another typical test program:

Sub DoubleTest2()
  Dim a As Double

  a = 0
  Debug.Print a  ' when this line appears, next assignment to variable `b` will complain `overflow` error message.

  Dim b As Double
  b = 100# ' *overflow error message*
  Debug.Print b
End Sub

How did you execute the function? In Excel or VBA (in a sub or function)? How many cells did you include? Just some more info, please. — VBasic2008
– VBasic2008, Commented Dec 17, 2018 at 5:38
@VBasic2008 Thanks for reply! More information has bee added. — hliu
– hliu, Commented Dec 17, 2018 at 5:47
I have tested the function on windows 7 32 bit and office 2016 and it works without that overflow error — YasserKhalil
– YasserKhalil, Commented Dec 17, 2018 at 5:51
I've tested it on 2003, it works but is slow. I would use this SumTest = WorksheetFunction.Sum(rg) — VBasic2008
– VBasic2008, Commented Dec 17, 2018 at 6:00
Really weird! If I execute the function in Excel =SumTest2() everything goes well. It only happens when using immediate window to run function.. — hliu
– hliu, Commented Dec 17, 2018 at 6:17

vbAdder · Accepted Answer · 2022-04-25 22:15:15Z

1

I have the same issue on a MacBook Pro 2.7 GHz Dual-Core Intel Core i5; Retina, 13-inch, Early 2015 running Excel for mac 16.59 (2022)

I commented out a couple of my debug.print statements, and noticed that some assigments did work even after a debug.print statement... The thing that "cured" the problem was an assigment txt= space(20)

playing around a bit, it appears that simply putting space 0 after the last debug.print statement avoids the overflow error.... the mind boggles

I adapted your example

Function SumTest() As Double
1   Debug.Print "SumTest...":          ' s=s+... overflows
'2   Debug.Print "SumTest...": Space 0  '<<<< s=s+... works

    Dim s As Double
    Dim i As Long
    For i = 1 To 10
        s = s + CDbl(i)
    Next i
    
3   Debug.Print s: Space 0
    SumTest = s
End Function

Note 1: as is, this code gives an overflow error at s=s+cdbl(i), for no discernible reason
Note 2: uncommenting line 2, "repairs" the code, again no clear reason, its a serendipitous hack
Note 3: Commenting out the Space 0 on line3 gives the overflow error again.
Note 4: calling the function from a cell in the spreadsheet (=SumTest()) does indeed not raise the error. it only happens when excecuting the code from within the VBE; either by pressing run, or entering it in SumTest in the immediate window

My guess is that debug.print requests memory for strings, but leaves a bit 'dangling', something that is corrected if when we reserve some more memory for strings with the space function. just conjecture though...

edited Apr 25, 2022 at 22:15

answered Apr 6, 2022 at 1:09

vbAdder

6776 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Keneda Jun 4 at 2:10

Thank you SO MUCH for figuring this out. I've been driven crazy by overflow errors, and I assumed it was an issue with my logic somewhere. Adding Space 0's fixed me right up! I'm also on a MacBook Pro (2018) running Excel for Mac 16.86.

jainashish · Accepted Answer · 2018-12-17 23:00:41Z

-2

Source: Microsoft Docs : An overflow results when you try to make an assignment that exceeds the limitations of the target of the assignment. This error has the following causes and solutions:

The result of an assignment, calculation, or data type conversion is too large to be represented within the range of values allowed for that type of variable.
- Assign the value to a variable of a type that can hold a larger range of values.
An assignment to a property exceeds the maximum value the property can accept.
- Make sure your assignment fits the range for the property to which it is made.

Solution:

Modify your code as below and you won't see the error again.

Function SumTest(rg As Range) As Double
  Debug.Print "SumTest..." 

  Dim s As Double
  Dim i As Range
  For Each i In rg.Cells
      s = s + CDbl(i.Value2)
  Next i

  SumTest = s
End Function

edited Dec 17, 2018 at 23:00

answered Dec 17, 2018 at 6:23

jainashish

5,2717 gold badges41 silver badges52 bronze badges

1 Comment

hliu Over a year ago

Thanks for reply! But I don't think here the CDbl is the solution. It's just assignment without any calculation process, let alone the value to be assigned is 0 which fits Double data type size limit.

Collectives™ on Stack Overflow

Why does the use of "Debug.Print" lead to "Overflow" error?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related