"Overflow" Error (6) when doing calculations on arrays

Question

I am creating a macro in VBA which finds values in one sheet and uses them to populate array MyArr(11,4) and then does calculation. Find and populating part works great, problem is the latter part.

.Range("C2").Value = MyArr(11, 4) / MyArr(11, 1)
.Range("D2").Value = (MyArr(9, 4) + MyArr(10, 4) + MyArr(11, 4)) / (MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1))
.Range("E2").Value = (MyArr(0, 4) + MyArr(1, 4) + MyArr(2, 4) + MyArr(3, 4) + MyArr(4, 4) + MyArr(5, 4) + MyArr(6, 4) + MyArr(7, 4) + MyArr(8, 4) + MyArr(9, 4) + MyArr(10, 4) + MyArr(11, 4)) / (MyArr(0, 1) + MyArr(1, 1) + MyArr(2, 1) + MyArr(3, 1) + MyArr(4, 1) + MyArr(5, 1) + MyArr(6, 1) + MyArr(7, 1) + MyArr(8, 1) + MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1))

This is the fragment of code in question. It causes Overflow error (6). I know this error happens due to one of the values in array being empty. Is there an easy way to stop using empty values but still execute the code for the filled ones? So if for example MyArr(9,4) is empty it would still execute the 2nd line of code because there are values in (10, 4), (11, 4) with omitting (9,4).

If the rest of the code is needed please inform me.

Either look before you leap (test the denominator before division) or use error handling to respond to any errors that might pop up. — John Coleman
– John Coleman, Commented Feb 9, 2018 at 15:13
More code (but not necessarily the rest of the code) would help. In particular -- you are leaving out the declarations. Please give a minimal reproducible example. — John Coleman
– John Coleman, Commented Feb 9, 2018 at 15:21
We need to see how MyArr is declared. If everything is implicitly typed, Specifically which instruction is overflowing? The 2nd one? What does TypeName(MyArr(9,4)) say, and what kind of values are involved? See VBA data types - like @JohnColeman said, we don't need the rest of the code, just enough to know what we're looking at. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 9, 2018 at 15:24
Error handling is a natural approach for this sort of thing. On Error Resume Next by itself isn't adequate and is even harmful (as @Mat'sMug points out). The only disciplined way to use it is if you explicitly check Err.Number after statements that can cause errors and handle them as needed. A more reliable way is to use the construct On Error GoTo err_handler where err_handler is a label for a block of error handling code. See this: cpearson.com/excel/ErrorHandling.htm — John Coleman
– John Coleman, Commented Feb 9, 2018 at 17:06

braX · Accepted Answer · 2018-02-09 15:40:07Z

3

As @Jon mentioned, check for division by zero before doing the division:

If MyArr(11, 1) <> 0 Then 
  .Range("C2").Value = MyArr(11, 4) / MyArr(11, 1)
Else
  .Range("C2").Value = 0
End If

If (MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1)) <> 0 Then
  .Range("D2").Value = (MyArr(9, 4) + MyArr(10, 4) + MyArr(11, 4)) / (MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1))
Else
  .Range("D2").Value = 0
End If

If (MyArr(0, 1) + MyArr(1, 1) + MyArr(2, 1) + MyArr(3, 1) + MyArr(4, 1) + MyArr(5, 1) + MyArr(6, 1) + MyArr(7, 1) + MyArr(8, 1) + MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1)) <> 0 Then
  .Range("E2").Value = (MyArr(0, 4) + MyArr(1, 4) + MyArr(2, 4) + MyArr(3, 4) + MyArr(4, 4) + MyArr(5, 4) + MyArr(6, 4) + MyArr(7, 4) + MyArr(8, 4) + MyArr(9, 4) + MyArr(10, 4) + MyArr(11, 4)) / (MyArr(0, 1) + MyArr(1, 1) + MyArr(2, 1) + MyArr(3, 1) + MyArr(4, 1) + MyArr(5, 1) + MyArr(6, 1) + MyArr(7, 1) + MyArr(8, 1) + MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1))
Else
  .Range("E2").Value = 0
End If

answered Feb 9, 2018 at 15:40

braX

11.9k5 gold badges24 silver badges38 bronze badges

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

3 Comments

Seidhe Over a year ago

Solution that you and @John proposed might be the easiest, when not sure if handling it by On Error Resume Next is proper. Thanks!

braX Over a year ago

Never use On Error Resume Next - All it takes is one ignored error and the problems are bound to get worse as the code continues.

Scott Craner Over a year ago

@braX never say never. On Error Resume Next has its place, but it should be used rarely and always followed by On Error Goto 0 directly after.

Collectives™ on Stack Overflow

"Overflow" Error (6) when doing calculations on arrays

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related