Divide by Zero error

[Mike Barton]

A portion of code inside a form has suddenly started misbehaving in Excel
2004 v 11.1.0. The same code works without problems in Excel X & 2001 and
has worked OK in 2004 before.

* The code is located inside a form.
* Three different integer variables are assigned three different constants
(all integer values).
* The third assignment fails and produces a 10: Divide by zero error.
* On subsequent passes (using Set Next Statement), a 6: Overflow error is
generated.

Can anyone suggest a reason for this?

Many thanks,

Mike Barton

 

[Bob Greenblatt]

A portion of code inside a form has suddenly started misbehaving in Excel 2004
v 11.1.0. The same code works without problems in Excel X & 2001 and has
worked OK in 2004 before.

* The code is located inside a form.
* Three different integer variables are assigned three different constants
(all integer values).
* The third assignment fails and produces a 10: Divide by zero error.
* On subsequent passes (using Set Next Statement), a 6: Overflow error is
generated.

Can anyone suggest a reason for this?

Many thanks,

Mike Barton

Post the code. Then we can help.

 

[Mike Barton]

Please find relevant portion of code below. The code breaks on the line
reading ŒintMaxWidth = CONST_MAX_WIDTH_MAC10¹.

Private Const CONST_MIN_WIDTH_MAC10 As Single = 230
Private Const CONST_MAX_WIDTH_MAC10 As Single = 582
Private Const CONST_MAX_HEIGHT_MAC10 As Single = 364
Private Const CONST_MIN_HEIGHT_MAC10 As Single = 324

Private Sub cmbLineCode_Change()
Dim sngCentre As Single
Dim intMinWidth As Integer
Dim intHeight As Integer
Dim intMaxWidth As Integer

On Error GoTo cmbLineCode_Change_Error

If bResetDefaults Then Exit Sub

If InStr(Application.OperatingSystem, "Mac") > 0 Then
If CInt(Application.Version) >= 10 Then 'if Excel 10 or more
intMinWidth = CONST_MIN_WIDTH_MAC10
intHeight = CONST_MIN_HEIGHT_MAC10

ŒERROR GENERATED BY FOLLOWING LINE

intMaxWidth = CONST_MAX_WIDTH_MAC10

Else
intMinWidth = CONST_MIN_WIDTH_MAC
intHeight = CONST_MIN_HEIGHT_MAC
intMaxWidth = CONST_MAX_WIDTH_MAC
End If
Else
intMinWidth = CONST_MIN_WIDTH_PC
intHeight = CONST_MIN_HEIGHT_PC
intMaxWidth = CONST_MAX_WIDTH_PC
End If

fraEntries.Enabled = True

With Me
If .Width = intMinWidth Then
'get form centre
sngLeftStartPos = .Left
sngCentre = sngLeftStartPos + .Width / 2

'resize form
.Height = intHeight
.Width = intMaxWidth

'recentre form
.Left = sngCentre - intMaxWidth / 2
End If
End With

On Error GoTo 0
Exit Sub
cmbLineCode_Change_Error:
EBErrorHandler "frmReconciliation-cmbLineCode_Change"
End Sub

 

[Bob Greenblatt]

I don¹t see anything wrong. And, it works fine for me, no error is generated
on that line, and intmaxwidth obtains the proper value. Try ³cleaning² the
vba code.

Copy ALL the code to text files. Delete the modules. Remove all the code
from the form pane(s). Save and close the workbook. Then reopen it and paste
the code back, compile and test it again.

 

[JE McGimpsey]

Please find relevant portion of code below. The code breaks on the line
reading ŒintMaxWidth = CONST_MAX_WIDTH_MAC10¹.

As Bob said, there's nothing wrong with your code at that point. It runs
fine for me using XL04. Perhaps cleaning will help.

Note that your original post said that

That parenthetical statement's not true, since you've defined your
constants as Single (floating point). That requires an implicit coercion
at assignment. It's probably not related to your problem, but it might
be relevant to the overflow statement (I'm not sure where you set your
Set Next command, so I don't know how you generated a second pass).

I rarely use anything but Long and Double for numeric variables. Using
the shorter forms doesn't save significant amounts of memory (or any, in
the case of integer vs. long), and doesn't require freqent explicit or
implicit coercions.

 

[Fredrik Wahlgren]

Re: Divide by Zero error
I don't see anything wrong. And, it works fine for me, no error is generated on that line, and intmaxwidth obtains the proper value. Try "cleaning" the vba code.

Copy ALL the code to text files. Delete the modules. Remove all the code from the form pane(s). Save and close the workbook. Then reopen it and paste the code back, compile and test it again.


--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom


I think the file may have become corrupt. That would explain why bob doesn't get any errors. Bob's suggestion may fix the problem. I'm pretty sure there won't be a problem if you create a new file from scratch.

/Fredrik

 

[Mike Barton]

Bob,

I created a new add-in workbook, inserted new modules/form and copied the
code and the form controls to the new version.

I have tried this new file under both Excel X and Excel 2004. It still works
fine under Excel X and still generates the same error under Excel 2004.

If you think the corruption might be in the controls, I will re-enter the
controls rather than copying.

 

[spacemodler]

I was getting a divide by zero error at odd places after I upgraded to
2004 from X. (Do an assignment, did a comparaions of the variable just
assigned poof divide by zero error, but only while in the debigger.)
It went away after I purged all of my old microsoft preferences. I
suspect that Excel was referencing the wrong libraries for my VBA code.


I recogized this as a preferences problem, when I experimented with the
same version of excel under different accounts on my machine. New
accounts didn't have the problem, my regular account did. Cleaned
preferences problem went away.