VBA Division by zero

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
JohnRobinAllenRetired professor of FrenchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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
JohnRobinAllenRetired professor of FrenchAuthor Commented:
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
JohnRobinAllenRetired professor of FrenchAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

JohnRobinAllenRetired professor of FrenchAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnRobinAllenRetired professor of FrenchAuthor Commented:
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
chaauCommented:
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
JohnRobinAllenRetired professor of FrenchAuthor Commented:
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
JohnRobinAllenRetired professor of FrenchAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Regular Expressions

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.