Excel VBA Overflow Error

Question

I have the following line in Excel VBA:

ActiveCell.Offset(r, 1).Value = IIf(rs2.Fields("SalesRelatedCallsQTD").Value = 0, "--", FormatPercent(rs2.Fields("SoldCallsQTD").Value / rs2.Fields("SalesRelatedCallsQTD").Value, 2))

When I run the macro in Excel I get a "Run-time error '6': Overflow" error.

I know there is the possibility of getting a 0 value in the denominator hence the use of IIf to check for this. Is it possible that Excel is still trying to calculate the

FormatPercent(rs2.Fields("SoldCallsQTD").Value / rs2.Fields("SalesRelatedCallsQTD").Value, 2)

and throwing the error? If so how do I get around this or is there something else wrong with this code?

EDIT

When I add a watch here are the values I get:

rs2.Fields("SalesRelatedCallsQTD").Value : 0 : Variant/Long 
rs2.Fields("SoldCallsQTD").Value : 0 : Variant/Long
rs2.Fields("SoldCallsQTD").Value / rs2.Fields("SalesRelatedCallsQTD").Value : <Overflow> : Variant/Integer

Can't that problem be linked to Null values here and there ? THe best way is to get step by step mode and see what your values look like, and/or include a few debug.Print in your code. — Patrick Honorez
– Patrick Honorez, Commented Mar 9, 2012 at 15:30
Can you check the values of ActiveCell (its address), r, rs2.Fields("SoldCallsQTD").Value and rs2.Fields("SalesRelatedCallsQTD").Value when you get the error? — assylias
– assylias, Commented Mar 9, 2012 at 15:32

assylias · Accepted Answer · 2012-03-09 15:29:37Z

1

The 3 arguments of an IIf are evaluated before the test is actually done. You can try it with the code below for example, which will return a division by zero. However the error you get is not a "Division by 0" but an "Overflow" error so this is not the only problem.

Public Sub test()

  Dim a As Long
  Dim b As Long

  a = IIf(b = 0, 1, 1 / b)

End Sub

answered Mar 9, 2012 at 15:29

assylias

330k84 gold badges680 silver badges806 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BrianKE Over a year ago

So what is the optimal way to check for the division by zero? Is a simple If(b > 0) Then do the work sufficient?

assylias Over a year ago

@BrianKE yes, use a conventional If b=0 Then a = 1 Else a = 1/b End If

BrianKE · Accepted Answer · 2012-03-09 17:26:42Z

I am not sure exactly what caused the error but changing to the following fixed it. I also created a simple NotNullOrZero macro as checking for > 0 was not enough (Null / Null doesn't throw an error, go figure). I know the NotNullOrZero is very simplistic but I know all my values are Null or Int so I didn't make it very robust.

   If (NotNullOrZero(rs2.Fields("EstimateCallsYTD").Value)) Then
      ActiveCell.Offset(r, 2).Value = FormatPercent(rs2.Fields("EstimateSalesYTD").Value / rs2.Fields("EstimateCallsYTD").Value, 2)
   Else
      ActiveCell.Offset(r, 1).Value = "--"
   End If


Public Function NotNullOrZero(aValue As Variant) As Boolean
    ' Returns true if the value is not null and greater than zero

    If Not IsNull(aValue) Then
       If (aValue > 0) Then
           NotNullOrZero = True
       End If
    End If

    NotNullOrZero = False

End Function

Collectives™ on Stack Overflow

Excel VBA Overflow Error

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related