Excel VBA 通过大量计算返回奇怪的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1446723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA returning weird results with large calculations
提问by bschaeffer
I've created a function in excel which basically searches a dynamic range in a For statement for a string and returns the value of the cell one column over. It's basically a budgeting function, but that's beside the point.
我在 excel 中创建了一个函数，它基本上在 For 语句中搜索字符串的动态范围，并返回一列上单元格的值。它基本上是一个预算功能，但这无关紧要。
Here's the problem, everything works with small results, but when the results get too large (say around 32000... for some reason that appears to be the number) the function starts returning 0.
这就是问题所在，一切都适用于小结果，但是当结果变得太大时（比如大约 32000 ......出于某种原因，这似乎是数字）函数开始返回0。
Has anybody had problems like this?
有没有人遇到过这样的问题？
Here's the code in question:
这是有问题的代码：
Function Material(item As String, Optional sheetType As String) As Integer
Dim wSheet As Worksheetr
Dim count As Integer
Dim offSet As Integer
Dim ref As Integer
Dim bottomRight As Integer
Dim upperLeft As Integer
Dim rng As Range
Dim cVal As Integer
For Each wSheet In Worksheets
If wSheet.Name = "Drywall Pricing" Then
dwIndex = wSheet.Index - 1
End If
Next wSheet
If IsMissing(sheetType) Then
sheetType = " "
Else
sheetType = UCase(sheetType)
End If
For i = 1 To dwIndex
wSheetName = Sheets(i).Name
If InStr(UCase(wSheetName), sheetType) > 0 Then
count = 9
offSet = 44
ref = 27
For wall = 0 To count - 1
On Error Resume Next
Err.Clear
upperLeft = (ref + 12) + (offSet * wall)
bottomRight = (ref + 30) + (offSet * wall)
Set rng = Sheets(i).Range("A" & upperLeft & ":B" & bottomRight)
cVal = Application.WorksheetFunction.VLookup(item, rng, 2, False)
If Err.Number > 0 Then
cVal = 0
End If
units = units + cVal
Next wall
Else
units = units + 0
End If
Next i
If units > 0 Then
Material = units
Else
Material = 0
End If
End Function
I've set up a spreadsheet to manually count a certain number of items ( i.e. "=A13+B5+B26" etc), and compared it to the result of running the function in question, and when the results are low, they are equal to each other, so I know that the function itself is working correctly. Is this a memory issue, then?
我已经设置了一个电子表格来手动计算一定数量的项目（即“=A13+B5+B26”等），并将其与运行相关函数的结果进行比较，当结果较低时，它们是彼此相等，所以我知道函数本身工作正常。那么这是内存问题吗？
Any help would be greatly appreciated.
任何帮助将不胜感激。
Thanks in advance!
提前致谢！
回答by Ryan Lundy
An Integer in VBA is 16 bits, which means a maximum value of 32,767 (and minimum of -32,768).
VBA 中的整数是 16 位，这意味着最大值为 32,767（最小值为 -32,768）。
Instead of Integer, use Long to store your results, which gives you more than 2 billion before it hits the limit.
使用 Long 代替 Integer 来存储您的结果，这在达到限制之前为您提供了超过 20 亿的结果。
回答by Atmocreations
may you post an excerpt of the content of the to be searched excel document?
你能把要搜索的excel文档的内容摘录一下吗？
two little comments:
两个小意见：
on the lines
上线了
If wSheet.Name = "Drywall Pricing" Then
dwIndex = wSheet.Index - 1
End If
you might want to Exit Foras you've found your sheet and you don't want to continue searching.
您可能想要，Exit For因为您已经找到了您的工作表并且不想继续搜索。
Is there any reason you want the found sheet MINUS 1?
您是否有任何理由想要找到的工作表 MINUS 1？
and the other comment is that units = units + 0in the Else-clause does nothing at all.
另一个评论是units = units + 0在Else-clause 中什么都不做。
regards
问候
回答by RBarryYoung
In VBA and "Integer" isn't an Integer 16 bits? I.e., -32,768 to +32,767? If so then this code right here is your culprit:
在 VBA 中，“整数”不是整数 16 位吗？即，-32,768 到 +32,767？如果是这样，那么这里的代码就是你的罪魁祸首：
Dim cVal As Integer
...
cVal = Application.WorksheetFunction.VLookup(item, rng, 2, False)
If Err.Number > 0 Then
cVal = 0
End If
Also, I'd suggest using "Option Explicit".
另外，我建议使用“Option Explicit”。
回答by Fink
maximum value for an integer is (2^15) which is 32,768. Your Error catching is forcing the cVal = 0. Try changing the datatype from integer to long. Max Long is (2^31 -1) which is 2,147,483,647, which should easily handle your values.
整数的最大值是 (2^15)，即 32,768。您的错误捕获强制 cVal = 0。尝试将数据类型从整数更改为长整数。Max Long 是 (2^31 -1)，即 2,147,483,647，它应该可以轻松处理您的值。
