Solved

# VBA Division by zero

Posted on 2014-08-06
Medium Priority
395 Views
Can anyone tell me why the following code does not work?
Sub Test()
Dim x As Single
x = 300 / 100000
If 300 Mod x = 0 Then Stop
End Sub

x is slightly larger than zero, but VBA says the 300 Mod x will not work because I'm dividing by zero.

Thanks for any help
JRA in Priddis, Alberta
0
Question by:JohnRobinAllen
• 6
• 2

LVL 25

Expert Comment

ID: 40245284
You need to append literal F to the number 300, like this:
``````If 300F Mod x = 0 Then Stop
``````
Otherwise VBA thinks that Mod operation should be performed on Integers and converts the x variable to Integer.
Read here about the Single data type
0

Author Comment

ID: 40245899
I wish Chaau's comment would work, but when I tried it in the VBA code inside a Word document, it produced a message "Syntax Error" and highlighted the line with "300F".
--JRA
0

Author Comment

ID: 40245911
I also tried this:
Sub Test()
Dim x As Double
Dim y As Double
y = 300
x = y / 100000
If y Mod x = 0 Then Stop
End Sub

Result: Division by zero error.
It goes without saying that using "Single" instead of "Double" does not work either.
I suspect there is some work-around, but I cannot think of anything else to try.
--JRA in Priddis, Alberta
0

Accepted Solution

JohnRobinAllen earned 0 total points
ID: 40247427
There is an obvious work-around to my problem that I should have thought about before I submitted the problem to EE. The solution is not elegant since it does not address the problem that MOD seems to think that very small numbers are equivalent to zero.
Anyway, the problem in my code is that x is a very small number created by dividing y by 100000. If we move that 100000 to the left side of the equation and have it multiply x, then we avoid any small number problem with MOD. Here is my revised version:

Sub Test2()
Dim x As Integer
Dim y As Long
x = 300
y = 100000
If x * y Mod x = 0 Then Stop
End Sub

That solves my problem, but I'll leave the question open for a couple of days to see if anyone can think of a better solution.
Thanks to Chaau for his suggested solution. I suspect that it would work in Visual Basic, but I'm using VBA.

--JRA in Priddis
0

Author Comment

ID: 40247447
There is an obvious work-around to my problem that I should have thought about before I submitted the problem to EE. The solution is not elegant since it does not address the problem that MOD seems to think that very small numbers are equivalent to zero.
Anyway, the problem in my code is that x is a very small number created by dividing y by 100000. If we move that 100000 to the left side of the equation and have it multiply x, then we avoid any small number problem with MOD. Here is my revised version:

Sub Test2()
Dim x As Integer
Dim y As Long
x = 300
y = 100000
If x * y Mod x = 0 Then Stop
End Sub

That solves my problem, but I'll leave the question open for a couple of days to see if anyone can think of a better solution.
Thanks to Chaau for his suggested solution. I suspect that it would work in Visual Basic, but I'm using VBA.

--JRA in Priddis
0

LVL 25

Assisted Solution

chaau earned 600 total points
ID: 40247603
(rounding floating-point numbers to integers)
This means that Mod operator for VBA is indeed different to the VB version. The VBA version works only with the whole numbers, but VB works with floating point numbers as well
0

Author Comment

ID: 40253805
Unfortunately I found a flaw in the solution I had proposed earlier. The calculation of x * y can produce an overflow that stops the program.
The solution is to divide both sides of the equation by some figure before the computer sees “x * y”. In the revised code below, I have divided both sides around the equals sign by 10:
Sub Test2()
Dim x As Integer
Dim y As Long
x = 300
y = 100000
If x * (y / 10) Mod (x / 10) = 0 Then Stop
End Sub

Apologies for my error.
--JRA
0

Author Closing Comment

ID: 40257841
Chaau's comment and link show why the problem occurred: in VBA "MOD" rounds decimal values to integers.
The work-around-solution I suggest solves the problem, but all the points should go to Chaau.
Thanks!
JRA in Priddis
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whatever be the reason, if you are working on web development side,  you will need day-today validation codes like email validation, date validation , IP address validation, phone validation on any of the edit page or say at the time of registration…
As most anyone who uses or has come across them can attest to, regular expressions (regex) are a complicated bit of magic. Packed so succinctly within their cryptic syntax lies a great deal of power. It's not the "take over the world" kind of power,…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
###### Suggested Courses
Course of the Month17 days, 5 hours left to enroll

#### 864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.