PDA

View Full Version : Heritage Excel VBA bombs in Excel 2011



jvbeaupre
03-06-2011, 05:18 AM
I was happy to learn that VBA was coming back to 2011 Excel.
But some of my heritage Excel workbooks with VBA macros, bomb out with Runtime Error 6, overflow.
For the most important workbook, all the values in the debugger highlighted line seem normal and in bounds.
Some modules approach the upper size limit of previous VBAs, but have always run flawlessly on 2004 for Mac & 2007 for Windows Excels.
The older VBA code does not use Activ-X, but does read data from workbooks and creates new workbooks (does that OK).
The bombs occur in different places when input is changed, but always on the same statement for the same input. Calculations appear appear correct up to the bomb. There is a lot of number-crunching and very little output. It's almost like it's a time-bomb or memory leak.
Does anyone know why this might happen? Does anyone else have the same problem?
Jim

mikerickson
03-07-2011, 08:15 AM
Overflow error?
Might it be that the new maximum number of rows exceeds the limits of data type Long?
Similarly, columns and Integer.

I've noticed that Userforms.Add is not in 2011 VBA, but nothing like this issue.

Could you post some example code of where the error occurs?

jvbeaupre
03-07-2011, 08:47 AM
It's not rows or columns. Most of my variables are called out as integer, double, boolean, etc.

I'm not sure a code sample would help:
- It bombs at different places with different input
- It bombs at lines that were successfully executed many times before
- It bombs on simple statements with all values in their correct ranges
For example:

Sub GetTemp()
' Sub GetTemp(j, q1, q2, u0, u1, u2, zz) '--thermal response
' fluxes q1,q2 temps u1,u2 step zz t(eq) u0
bb = (Abs(ae(2, j)) + ae(1, j)) * sig
u0 = (q2 / bb) ^ 0.25 ' <---BOMBS HERE
u3 = ut(j) / bb / ((u1 ^ 2 + u0 ^ 2) * (u0 + u1))
....
All variables in uo= ... double and in range and most transmitted by Public statements.
By changing the timestep from 5 to 4 , it bombs at the next statement: u3 = ....

mikerickson
03-07-2011, 08:55 AM
if bb=0 that would cause an error, but not Overflow. If bb=10^-100 it might cause an overflow, but....
After work, I'll post the MS help forum address, it sounds like you found a bug.

?Congatulations to the mighty bug hunter? :)

mikerickson
03-07-2011, 07:40 PM
Sorry about the delay. Here's a link to MicroSoft's Excel Office help line.

http://www.officeformac.com/ProductForums/Excel/

jvbeaupre
03-07-2011, 08:23 PM
Thanks, I'll follow up on that.
Jim

jvbeaupre
03-08-2011, 05:15 AM
I'd rather just have a normal code execution. Thanks for passing on the address of the Mac product forum. I've posted the issue the morning.
Thanks again,
Jim
:banghead: