Division by zero error on WorksheetFunction.Sum

[agalick]

Hello,

I have a VBA sub that reads some values off a worksheet, calculates some statistics and prints them into different cells. I built the workbook using Excel 2011 for Mac.

At one point I am getting a runtime 11 error: "Division by zero". The line highlighted by the debugger only uses WorksheetFunction.Sum, so I am mystified.

Dim temp As Double

With Application.WorksheetFunction
    'The dynamic array prereq1 had a few values assigned earlier; it's Dimmed as Double
    temp = .Sum(prereq1) '******division by zero here
End With

The actual sum is less than 300, and when I use the Immediate window the same line of code works just fine. When I use the Debugger to "Step Into" there is also no problem, and I can step all the way to the end of the program with no more errors.

The same code (with same inputs) runs without throwing an error when I tested it on Excel 2007 on Windows XP.

What could be causing this?

-Amy Galick

 

[ImranMalek]

Hi Amy,
This is known problem in Excel 2011 for Mac I'm not aware if they have already fixed it!
Hopefully by declaring all the variables explicitly which are used in your code should solve your problem.
P.S I've done this with range but you can do the same with array.

Change your code to

Dim temp As Double
Dim sumrng As Range
Set sumrng = Range("prereq1")


With Application.WorksheetFunction
    temp = .Sum(sumrng) '******division by zero here
End With

Please check this link for more information.
Bogus Excel for Mac VBA error - Microsoft Community

Hope it helps !

 

[agalick]

Thanks Imran for the good link.

I actually had already declared all variables explicitly. But following the link suggested to me that this might have been just some weird memory thing. I got the error to go away by setting a few larger things to Nothing once the procedure is done with them. At least I think that is what fixed it.

-Amy Galick