在数组上进行计算时出现“溢出”错误（6）-“Overflow”Error(6)whendoingcalculationsonarrays

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.

我在VBA中创建一个宏,它在一个工作表中查找值并使用它们填充数组MyArr(11,4),然后进行计算。查找和填充部分工作很好,问题是后半部分。

.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).

这是有问题的代码片段。它会导致溢出错误(6)。我知道这个错误是由于数组中的一个值为空而发生的。是否有一种简单的方法可以停止使用空值但仍然执行已填充的代码?因此,如果例如MyArr(9,4)为空,它仍将执行第二行代码,因为(10,4),(11,4)中的值有省略(9,4)。

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

如果需要其余代码,请通知我。

1 个解决方案

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