Division by zero with positive or no divisor (vba)

Question

I'm new to macros and vba in Excel. Currently, I'm working on a vba macro for an invoice template at work. However, I'm running in a division by zero error that I'm having trouble tracing the cause of.
There are two specific lines of code where it pops up, sometimes..
First part:

    VATRMB = 0

Second part:

    VATRMB = VATRMB + (0.0593 * (ActiveSheet.Range("I" & i).Value / (1 + 0.0593)))

The Dim VATRMB is stored as follows:

        Dim startRow As Integer, endRow As Integer, VATRMB As Single, VATEUR As Single, VATUSD As Single, VATRMBCell As Range, VATEURCell As Range, VATUSDCell As Range

The way I see it these lines should never throw up a division by zero error. In the first case there is no divisor whatsoever and in the second it is always positive.
Have any of you got an idea as to why this might cause an error? Could it have anything to do with the fact that the sub gets called multiple times, reusing the same VATRMB Dim? It should be reset after each call of the sub, right? Or could it have to do with the fact that I specify VATRMB as Single? This is appropriate for 'small' (sub-1,000,000) floating numbers, correct?

EDIT:
1. Added exact line used for calling Dim storage

2. Here is the full block of code used, maybe it helps to clarify a thing or two:

'Debug.Print Tab(10); ("Items will be searched in rows " & startRow & " thru " & endRow) 'Serves for debugging and testing
For i = startRow To endRow 'Loop the following code through all rows mentioned above
    If ActiveSheet.Range("B" & i).Find("Membership") Is Nothing Then 'If nothing is returned when searching for "Membership"; i.e. if the item in this row is not a membership payment
        If Not ActiveSheet.Range("H" & i).Find("RMB") Is Nothing Then 'If the value for this item is RMB denoted
            'Debug.Print Tab(20); "Item on Row " & i & " is RMB denoted, VAT = " & ((ActiveSheet.Range("I" & i).Value / (1 + 0.0593)) * 0.0593) 'Serves for debugging and testing
            VATRMB = VATRMB + (0.0593 * (ActiveSheet.Range("I" & i).Value / (1 + 0.0593))) 'Add row's VAT to VAT total
        End If
        If Not ActiveSheet.Range("H" & i).Find("EUR") Is Nothing Then 'If the value for this item is EUR denoted
            'Debug.Print Tab(20); "Item on Row " & i & " is EUR denoted, VAT = " & ((ActiveSheet.Range("I" & i).Value / (1 + 0.0593)) * 0.0593)  'Serves for debugging and testing
            'MsgBox VATEUR + 0.0593 * ActiveSheet.Range("I" & i).Value / (1 + 0.0593)
            VATEUR = VATEUR + (0.0593 * (ActiveSheet.Range("I" & i).Value / (1 + 0.0593))) 'Add row's VAT to VAT total
        End If
        If Not ActiveSheet.Range("H" & i).Find("USD") Is Nothing Then 'If the value for this item is USD denoted
            'Debug.Print Tab(20); "Item on Row " & i & " is USD denoted, VAT = " & ((ActiveSheet.Range("I" & i).Value / (1 + 0.0593)) * 0.0593)  'Serves for debugging and testing
            VATUSD = VATUSD + (0.0593 * (ActiveSheet.Range("I" & i).Value / (1 + 0.0593))) 'Add row's VAT to VAT total
        End If
    Else 'Else, i.e. if the row contains a membership payment, then essentially nothing happens
        'Debug.Print Tab(20); ("Item on Row " & i & " is a membership payment; no VAT paid.") 'Serves for debugging and testing
    End If
Next

So what I'm trying to do is basically loop through all the items in the invoice, from startRow to endRow, and determine whether the item is a membership payment by parsing the 'type' string (column B). Then, depending on whether or not it is a membership payment determine the VAT, also checking the currency in which it is paid. The amount for the payment is stored in Column I as a floating number.

Could you share the exact line that you're using to Dim your variable? Sometimes people make mistakes in that area and don't Dim to what they think they're dim'ing to. — Chris Spicer, Jul 22 '13 at 08:46
Can you give an example of what values are housed in Column **I** in your sheet? I do not get a DIV0-Error with the calculation in Excel03 and Excel13! — AKDADEVIL, Jul 22 '13 at 08:47
@ChrisSpicer I added it to the original message, substituted the previous part with the whole line. — Martin, Jul 22 '13 at 09:30
@AKDADEVIL Column I houses the unit cost of the item on that line, essentially a floating number. — Martin, Jul 22 '13 at 09:32
if you get a division by zero error go into VBE (alt + F11) and see where the runtime highlights a yellow line. It will exactly show you which line throws an error and what the variable value is at the time. Research debugging in VBA for instructions — , Jul 22 '13 at 09:42
@mehow I am aware of the line highlighting, it highlights either two of the lines in the first two code blocks in my original post. Which confuses me as it is impossible to have a division by zero from those equations, I either set a value to (1) or divide by (1 + 0.0593), which is greater than zero (2). Thanks anyway, I did find out about 'watching' a variable and the locals window to try debugging thanks to you. — Martin, Jul 22 '13 at 10:01
what are the values of `ActiveSheet.Range("I" & i).Value` and `VATRMB` when the error is thrown? You mouse over the variable to see its value. Keep in mind that [Single](http://msdn.microsoft.com/en-us/library/xay7978z(v=vs.80).aspx) data type has its limits. What happens if you declare them as `Double` instead of `Single` ? — , Jul 22 '13 at 10:12
@mehow Thanks for the advice, I looked up some datatypes and have changed it to 'Currency' for now. I'm still working on the invoice but so far have not encountered any divide by zero errors yet. It's great if it works, but what I hate is that I don't know why it didn't work as 'Single'.. — Martin, Jul 24 '13 at 08:18

score 0 · Answer 1 · answered Aug 14 '17 at 16:31

Not sure if this is the answer to your problems since you would need to provide the entirety of your workbooks etc to confirm. Nevertheless, we can create this type of "it should be impossible" situation with 100% reproducibility for not only Div0, but also for pretty much any error, with a line like:

VarX = 10  ' we can make this fail with Div0, Overflow or whatever

In our test, the problem is not actually the "direct" or "explicit" code where the error is reported, but rather, the error occurs elsewhere, and VBA in its infinite wisdom just happens to "report" the error in an odd way at an odd time (in fact it should not be reporting certain errors at all, see below).

Does your package involve any external executables, dll's, addins', etc?

If so, then that is likely the place to start.

If not, the error may actually be occurring directly or indirectly in the Range you are accessing, but not necessarily in the cell currently accessed.

Here is an example creating a "Div0" via a DLL accessed in VBA as an addin: Suppose you write a bit of code in another language, here Fortran (we use Implicit None everywhere, and everything is declared correctly etc.):

Pure Subroutine FortDLL(x, U)
:
Real(DP), Intent(In)    :: x
Real(DP), Intent(Out)   :: U
:
Real(DP)                :: QQ
:
:
QQ = Log10(x) ! Notice this is not the return var and has nothing to do with anything on the VBA side (or so you would think)
:
U = 10.D0   ! Notice, this is the return var, and is a constant = 10.D0
:
End Subroutine FortDLL

compile as DLL and access in the usual way.

Then suppose you have some VBA as:

Function VBAFunc(x) as Variant
:
Call FortDLL(x, U)
:
Dim VarU as Variant
:
VarU = U   ; you can replace this with VarU = 10, or whatever, and will get same result/failure

Now, if x < 0, then the DLL will crap out since Log10 is not defined for x < 0. This will throw a Fortran run time error, and depending on how you set this up, you can get it to throw a Div0, an Overflow (e.g. on the Fortran side the MaxExponent for a Double here is 1024, whereas on the VBA side it is around 308 depending on a number of things, etc. etc. etc. )

Now even though QQ has nothing at all to do with the VBA side, when the VBA code executes FortDLL(), it returns U = 10, and it actually does that part correctly.

HOWEVER, the DLL would have thrown a Div0 (or whatever you desire to create) error, and that "error message" is/can be buried in the return to the Call FortDLL().

If you are not using DLL's etc, it is possible that something comparable is happening in your "range" or elsewhere during you looping etc.

We have not performed explicit tests as to why the Dim as Currency "fix" works, but we are guessing that as Currency is a very special Type (it is actually a structured type with at least TWO fields), the "error message" may be buried in one of those "fields", and which may not be required/relevant to the size of number you are using, and obviating the crash by "fluke" (i.e. a kind of "lucky KLUDGE". You can test this by putting in numbers too large for Double, and requiring the full machinery of the Currency Type. If it is a "lucky KLUDGE", then one day when you are not around and somebody else is using your code, and they enter a number requiring the full Currency machinery, then it will likely crash, and nobody will know why.

Here is an alternate test, suppose you have the crash on a line like VarX = 10, then replace/amend as follows:

:
On Error Resume Next
VarX = 10
VarX = 10
:

... if this "works" (i.e. obviates the error/crash), then your problem is likely along the lines explained above (whether "external" or "internal"). In this case, basically, the "Div0 problem" is treated as a VBA error on the first time VarX is assigned 10, since the Error Trap is set, that "first time" catches and ignores the "DLL side error", and moves on.

... clearly this is just a TEST, not a solution.

This may also be Excel/Win/Compiler (and especially with GCC, compiler VERSION also since they have some pretty wacky things/changes sometimes) dependent and so the reproducibility and exact behaviour may vary.

Division by zero with positive or no divisor (vba)

1 Answers1