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

Answer 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...

Answer 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