?
Solved

VBA Division by zero

Posted on 2014-08-06
8
Medium Priority
?
395 Views
Last Modified: 2014-08-13
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
Comment
Question by:JohnRobinAllen
  • 6
  • 2
8 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40245284
You need to append literal F to the number 300, like this:
If 300F Mod x = 0 Then Stop

Open in new window

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

by:JohnRobinAllen
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

by:JohnRobinAllen
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Accepted Solution

by:
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

by:JohnRobinAllen
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

by:chaau
chaau earned 600 total points
ID: 40247603
I found the reason for your troubles. Please read this article, especially check out this:
(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

by:JohnRobinAllen
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

by:JohnRobinAllen
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

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.

Join & Ask a Question