天道酬勤,学无止境

excel VBA Overflow error when it shouldn't

Sub TestFunction()
     Dim var As Double
     var = 25 * 24 * 23 * 22 * 21 * 20
End Sub

I am receiving an overflow error for this vba operation. when i run it in a cell with functions i get 127,512,000

what could possibly be an error for this? this should be well below the size limit for this data type right?

标签

评论

VBA annoyingly converts the first term to an Integer because in your case, it is small enough.

Amend this line, that it is converted explicitly to a double:

var = CDbl(25) * 24 * 23 * 22 * 21 * 20

Try it like this:

Option Explicit
Sub TestFunction()
     Dim var As Double
     var = 25
     var = var * 24 * 23 * 22 * 21 * 20
     Debug.Print var
End Sub

The problem in your case is that when VBA tries to sum numbers it has its own logic, going through Integer first and then parsing it to the number on the left. If the first number is bigger than integer (32767) or is explicitly converted as a Double, Long, Single, then it is ok. Here you will not have any problems, because 43333 is automatically converted to long and it is ok:

Option Explicit
Sub TestFunction()    
     Dim var As Double
     var = 43333 * 6 * 6         
End Sub

In your case, if the first number is 25.1, instead of 25, it gets automatically converted to double, thus later you will not have problems, as far as Double*Integer = Double

Sub TestFunction()
     Dim var As Double
     var = 25.1 * 24 * 23 * 22 * 21 * 20
     Debug.Print var
End Sub

Going a little deeper, using the VarType() function. Declare k as Variant, and see what VBA converts it to, depending on its values and the mathematic operation:

Sub TestFunction()

    Dim k As Variant

    k = 32767
    Debug.Print VarType(k) = vbInteger

    k = k + 1
    Debug.Print VarType(k) = vbLong

    k = 15.5
    Debug.Print VarType(k) = vbDouble

    k = 15
    Debug.Print VarType(k) = vbInteger

    k = 1 ^ 1 'Just because of the power operation, it gets converted to double
    Debug.Print VarType(k) = vbDouble

End Sub

All debug.print return True.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • VBA Overflow error with type double, Excel
    I don't know the overflow error very well, so I'm pretty at a loss here. Writing in VBA I'm calling a VBA function, which is a wrapper of a C++ function from a dll (which I have no access to the innards of). The VBA/C++ function returns a double. It is a derivative valuation using numerical methods. For a time yesterday (but it may have gone away now) I was getting the overflow error when trying assign the function return to a variable declared as double. But on the error halt, I could see the correct value assigned to the double, in the locals window (8.17...). And when I instead assign the
  • 类型为 double 的 VBA 溢出错误,Excel(VBA Overflow error with type double, Excel)
    问题 我不太了解溢出错误,所以我在这里很茫然。 用 VBA 编写我正在调用一个 VBA 函数,它是一个来自 dll 的 C++ 函数的包装器(我无法访问它的内部)。 VBA/C++ 函数返回一个双精度值。 它是一种使用数值方法的衍生估值。 昨天有一段时间(但现在它可能已经消失了)我在尝试将函数返回分配给声明为 double 的变量时遇到溢出错误。 但是在错误停止时,我可以在本地窗口(8.17 ...)中看到分配给双精度值的正确值。 当我将函数返回分配给一个变体时,我得到了相同的值,没有错误,变体类型为 Variant/Double。 知道这里会发生什么吗? 我完全迷失了。 提前感谢您的任何想法。 电阻 编辑:我尝试的另一件事是在将 CDbl 分配给变体时将其应用于函数返回。 这是一些伪代码,只是为了澄清我的情况: Dim db as Double, va as Variant db = DerivativeValue(a, b, c...) 这会导致溢出,但 db 在本地窗口中的错误值正确。 va = DerivativeValue(a, b, c...) 没有错误,相同的值。 va 是 Variant/Double 类型 va = CDbl(DerivativeValue(a, b, c...)) 现在又溢出了。 在这种情况下,我忘记了 va 在 Locals 窗口中做了什么
  • Excel VBA: Overflow error from too many undestroyed objects?
    when performing optimization tasks on a large dataset I receive an overflow runtime error 6 from time to time (generally after 1 hour or 2). The error goes away when I restart my macro from where it stopped, i.e. launch the macro again from the point where the error occured. Could an overflow error be related to some issue of having created too many objects that are not destroyed properly after use? Here's a (simplified version) of my container class, which is used destroyed (via Set ... = nothing) and reconstructed (via Set ... = New) thousands of times. 'CG_data_point custom collection class
  • Excel VBA:溢出错误(Excel VBA: Overflow error)
    问题 刚开始用 VBA 编程,我有一个问题,我不知道如何解决这个问题。 我认为一切都很好。 当我想运行这个宏时,会显示运行时错误“6”溢出。 Sub Działaj() Dim Tablica(1 To 5000) As String Dim Dni() Dim kolumna As Integer Dim wiersz As Integer Dim licznik As Integer Dim PF As Boolean Dim tmp As Integer Dim i As Integer Dim tmp2 As String licznik = 2 tmp = 0 PF = False kolumna = 22 wiersz = 2 Do If Worksheets("Początkowe").Cells(wiersz, kolumna).Value <> vbNullString Then For i = 1 To licznik If Worksheets("Początkowe").Cells(wiersz, kolumna).Value = Tablica(i) Then 'debugger shows problem here i guess PF = True tmp = i End If Next i End If If Worksheets(
  • Run-time error ' 6' overflow: Microsoft Excel VBA
    I tried to solve this overflow in my excel sheet and still not able to solve it. I knew that my data is really a lot (more than 32767 rows). please take a look a my code and help me advise on this. Thanks. This is remove Macro Sub Remove() ' ' Remove Macro ' Dim Ctr As Integer Dim CtrS As String Dim EOF As Boolean Dim LstStr As String Dim CurStr As String Ctr = 8 LstStr = "" Cutstr = "" EOF = False Do While Not EOF CtrS = CStr(Ctr) CurStr = Range("A" & CtrS).Value & "-" & _ Range("B" & CtrS).Value & "-" & _ Range("C" & CtrS).Value & "-" & _ Range("D" & CtrS).Value & "-" & _ Range("E" & CtrS)
  • Excel VBA 通过简单的除法引发溢出错误(Excel VBA throws overflow error with a simple division)
    问题 Excel 2013。VBA:此代码: Sub test() On Error GoTo Err: Dim p As Double p = (362 * 100) / 2005 Exit Sub Err: If Err.Description <> "" And Err.Source <> "" Then MsgBox Err.Description, vbCritical, Err.Source End If End Sub 引发溢出数 6 错误。 为什么? 回答1 错误在362 * 100 。 它被认为是Integer并且最多为32767 。 像这样尝试: Public Sub TestMe() Dim pct As Double pct = (CDbl(362) * 100) / 2005 End Sub 问题是,如果您有以下 4 个数学表达式 ( +-/* ) 中的任何一个,其值低于2^15-1或32767 ,VBA 会将它们视为Integer并且结果也会尝试解析为Integer 。 ( Integer数值实际上是从-32768到32767 ) 因此, 32767+1给出了一个推翻错误。 如果您尝试使用32768+1就可以了,因为32768将被解析为Long 。 对于幂运算符^ ,VBA 解析为Double并且非常好 - 2 ^ 10 + 32000 回答2 因为
  • 类型为 Double 的 Excel VBA 中的溢出错误(Overflow Error in Excel VBA with Type Double)
    问题 我在 Excel VBA 中遇到了溢出错误,但找不到解决方法。 虽然 Microsoft 的文档表明双打的范围应达到 ~1.8E308,但我收到明显低于该阈值的数字的溢出错误。 我的代码如下: Public Function Fixed_Sample_Nums(ByVal n As Long, seed As Long) As Double() Dim x() As Double, y() As Double, i As Long ReDim y(1 To n) ReDim x(1 To n) x(1) = (CDbl(48271) * seed) Mod CDbl(2 ^ 31 - 1) For i = 2 To n x(i) = (CDbl(48271) * CDbl(x(i - 1))) Mod (CDbl(2 ^ 31 - 1)) y(i) = CDbl(x(i)) / CDbl(2 ^ 31 - 1) Next i Fixed_Sample_Nums = y End Function 'I receive the error in the first iteration of the for loop with 'seed equal to any value >= 1 (i.e. w/ seed = 1): Debug.Print((CDbl(48271) *
  • Excel VBA: Overflow error
    Just started programming in VBA, I have a problem, and i don't know how to solve this. I think everything is ok. There shows Run-Time Error '6' Overflow when i want to run this macro. Sub Działaj() Dim Tablica(1 To 5000) As String Dim Dni() Dim kolumna As Integer Dim wiersz As Integer Dim licznik As Integer Dim PF As Boolean Dim tmp As Integer Dim i As Integer Dim tmp2 As String licznik = 2 tmp = 0 PF = False kolumna = 22 wiersz = 2 Do If Worksheets("Początkowe").Cells(wiersz, kolumna).Value <> vbNullString Then For i = 1 To licznik If Worksheets("Początkowe").Cells(wiersz, kolumna).Value =
  • Overflow Error in Excel VBA with Type Double
    I have run into an overflow error in Excel VBA and cannot find my way around it. While Microsoft's documentation indicates that the range for doubles should reach ~1.8E308, I am receiving an overflow error for numbers significantly lower than that threshold. My code is as follows: Public Function Fixed_Sample_Nums(ByVal n As Long, seed As Long) As Double() Dim x() As Double, y() As Double, i As Long ReDim y(1 To n) ReDim x(1 To n) x(1) = (CDbl(48271) * seed) Mod CDbl(2 ^ 31 - 1) For i = 2 To n x(i) = (CDbl(48271) * CDbl(x(i - 1))) Mod (CDbl(2 ^ 31 - 1)) y(i) = CDbl(x(i)) / CDbl(2 ^ 31 - 1)
  • Excel VBA:来自太多未破坏对象的溢出错误?(Excel VBA: Overflow error from too many undestroyed objects?)
    问题 在大型数据集上执行优化任务时,我不时收到溢出运行时错误 6(通常在 1 小时或 2 小时后)。 当我从它停止的地方重新启动我的宏时,错误就会消失,即从发生错误的地方再次启动宏。 溢出错误是否与创建了太多使用后没有正确销毁的对象有关? 这是我的容器类的(简化版本),它被销毁(通过 Set ... = 无)和重建(通过 Set ... = New)数千次。 'CG_data_point custom collection class Public data_points As Collection Private Sub Class_Initialize() Set data_points = New Collection End Sub Public Sub AddDataPoint(mydate as date, price as double) Dim new_data_point As CG_data_point Set new_data_point = New CG_data_point new_data_point.EnterData mydate, price data_points.Add new_data_point Set new_data_point = Nothing 'I assume this one could also be skipped End
  • 由于长数据类型导致的 VBA-Excel 溢出错误(VBA-Excel Overflow Error due to Long data type)
    问题 这似乎太容易了,但我很绝望。 我需要做的是获取列“D”的最后一个值 大量的数字,例如。 987654321,如果值只有两位数,代码就可以了。 我只是无法确定问题所在。 Dim lastRow As Long lastRow = Cells(Rows.Count, "D").End(xlUp).Value Sheets("Sheet1").TxtBox1.Value = lastRow 回答1 就像我在评论中提到的那样,对于如此大的数字,您必须将其声明为双倍。 Dim lastRow As Double 或者,由于您想将其存储在文本框中,您可以做两件事 将其声明为字符串将其直接存储在文本框中。 Option Explicit Sub Sample1() Dim lastRow As String With Sheets("Sheet1") lastRow = .Cells(.Rows.Count, "D").End(xlUp).Value .TextBox1.Value = lastRow End With End Sub Sub Sample2() With Sheets("Sheet1") .TextBox1.Value = .Cells(.Rows.Count, "D").End(xlUp).Value End With End Sub 回答2 Long 最多只能处理 2
  • 使用以下 Excel 2010 VBA 出现溢出错误 6(Overflow Error 6 with the following Excel 2010 VBA)
    问题 以下代码将按照我想要的方式正确格式化我的模板。 但是,如果模板为空并且用户点击工作表上的准备上传按钮,我将收到溢出错误 6。有什么方法可以消除导致此错误的原因? Sub PrepForUpload() Dim cel As Range, rng As Range Set rng = Range("A2", Range("A65536").End(xlUp)) For Each cel In rng If cel.Value = "" Then If cel.Offset(, 2).Value = "" Then cel.EntireRow.Delete End If End If Next cel Dim rowNumber As Integer With Sheets("Initiatives") If Len(.Cells(2, 1)) = 0 Then rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1 Else: rowNumber = .Cells(2, 1).End(xlDown).Row + 1 End If .Rows(rowNumber & ":" & .Rows.Count).Clear End With End Sub 调试指向以下行作为问题: rowNumber = .Cells(2, 1)
  • 缩放互补误差函数,erfcx(x),避免算术溢出的计算 - VBA/Excel(Scaled Complementary Error Function, erfcx(x), computation avoiding arithmetic overflow - VBA/Excel)
    问题 我需要一个算法/近似值来计算缩放互补误差函数,erfcx(x) 到双浮点精度。 我在工作 PC 上,所以我只能使用 Excel 和 VBA,不能使用外部库或加载项:我需要自己编写代码。 Excel 仅提供 erf() 和 erfc() 函数。 关系 erfcx(x) = exp(x^2) erfc(x) 显然很有用,但是 x 大于 26.5 时存在算术上溢/下溢,我需要比这更大。 下面的帖子讨论了一个 python 实现——但它似乎没有解决我所知道的问题。 它提供了使用其他库或不足以满足我需要的近似值的解决方案。 python中是否有可用的缩放互补误差函数? 有什么建议? 更新: 我使用了我在维基百科上找到的这个连续分数表示以及用于解决连分数的算法的修改版本 http://finance4traders.blogspot.nl/2009/07/continued-fractions-and-modified-lentzs.html 以下代码似乎有效,实际上对于较大的输入参数需要较少的迭代。 Function erfcx(x) As Variant Dim Ai As Double Dim Bi As Double Dim Ci As Double Dim Di As Double Dim Ei As Double Dim Fi As Double Dim X2 As
  • 运行时错误“6”溢出:Microsoft Excel VBA(Run-time error ' 6' overflow: Microsoft Excel VBA)
    问题 我试图在我的 excel 表中解决这个溢出问题,但仍然无法解决。 我知道我的数据真的很多(超过 32767 行)。 请查看我的代码并帮助我就此提出建议。 谢谢。 这是删除宏 Sub Remove() ' ' Remove Macro ' Dim Ctr As Integer Dim CtrS As String Dim EOF As Boolean Dim LstStr As String Dim CurStr As String Ctr = 8 LstStr = "" Cutstr = "" EOF = False Do While Not EOF CtrS = CStr(Ctr) CurStr = Range("A" & CtrS).Value & "-" & _ Range("B" & CtrS).Value & "-" & _ Range("C" & CtrS).Value & "-" & _ Range("D" & CtrS).Value & "-" & _ Range("E" & CtrS).Value & "-" & _ Range("F" & CtrS).Value & "-" & _ Range("G" & CtrS).Value & "-" & _ Range("H" & CtrS).Value & "-" & _ Range("I" & CtrS)
  • excel 2010 vba 中的“允许文本溢出形状”(“Allow text to overflow shape” in excel 2010 vba)
    问题 Could I please be pointed to how I could target the "Allow Text to Overflow Shape" of a Textbox created via VBA using AddShape? I have looked through the object reference textframe/textframe2 with no luck. This can be achieved without vba[but I have lots of textboxes] by: right click [on the textbox shape] >> format shape >> textbox >> Allow Text to overflow shape [tick box under the "Autofit" group] I have also done macro recording to no avail Suggestions much appreciated 回答1 您所指的设置似乎影响 Excel 对象模型中的两个属性TextFrame.HorizontalOverflow和TextFrame.VerticalOverflow 。 VerticalOverflow可以设置为:
  • Scaled Complementary Error Function, erfcx(x), computation avoiding arithmetic overflow - VBA/Excel
    I need an algorithm/approximation to compute the Scaled Complementary Error Function, erfcx(x) to double-float precision. I'm on a work PC so I’m limited to using Excel and VBA and I cannot use external libraries or add-ins: I need to code this myself. Excel only provides erf() and erfc() functions. The relationship erfcx(x) = exp(x^2) erfc(x) is obviously useful, however there is arithmetic over/underflow for x larger than around 26.5 and I need to go larger than this. The below post discussed a python implementation – but it doesn’t seem to resolve the issue from what I can tell. It provides
  • Excel VBA throws overflow error with a simple division
    Excel 2013. VBA: This code: Sub test() On Error GoTo Err: Dim p As Double p = (362 * 100) / 2005 Exit Sub Err: If Err.Description <> "" And Err.Source <> "" Then MsgBox Err.Description, vbCritical, Err.Source End If End Sub Throws Overflow number 6 error. Why?
  • Overflow Error 6 with the following Excel 2010 VBA
    The following code will format my template correctly the way I want. However, in the event the template is empty and a user hits the prep upload button on the sheet, I will receive an Overflow Error 6. Is there any way to remove what is causing this error? Sub PrepForUpload() Dim cel As Range, rng As Range Set rng = Range("A2", Range("A65536").End(xlUp)) For Each cel In rng If cel.Value = "" Then If cel.Offset(, 2).Value = "" Then cel.EntireRow.Delete End If End If Next cel Dim rowNumber As Integer With Sheets("Initiatives") If Len(.Cells(2, 1)) = 0 Then rowNumber = .Cells(2, 1).End(xlDown)
  • 在不同版本的Excel中对单元格进行计数时,值不正确(Incorrect value when counting cells in different versions of Excel)
    问题 我已经看到一些SO用户在尝试使用Cells.Count某些变体时Cells.Count ; 在某些情况下,VBA代码会引发溢出错误。 供参考,请参阅对此答案的评论: 我认为这可以解决问题,但出现“溢出”错误,它指向代码“ If Master.Cells.SpecialCells(xlCellTypeVisible).Count> 0 Then” ---似乎没有在过滤任何特定内容– user1556069 和这个答案: 这个Onyl是否可以正常工作(而Cells.Count却无法正常工作),因为后者使用了一个整数(16位),最大值为65,536,整个电子表格返回的数值大了多少? – fast_code 我假设VBA在幕后某个地方试图将单元格计数强制为小整数(16位)或长整数(32位)。 Excel 2007工作表的单元格计数将溢出这两种数据类型。 不幸的是,我现在无法隔离它,因为我没有便携式的Excel 2007副本,并且实际上无法重现您的错误。 – mwolfe02 为了理解这一点,我试图重现自己,并在尝试将Cells.Count分配为Integer时出现溢出。 这是有意义的,因为对于Integer数据类型,该值太大。 在Excel 2003和2010中使用下面的代码,尝试分配为Long或Variant时得到了数值结果。 Option Explicit Sub
  • VBA-Excel Overflow Error due to Long data type
    This may seem too easy, but I am so desperate. What I need to do is get the last value of the column "D" which has a big amount of number, ex. 987654321, the code is fine if the value is only two-digit. I just can't identify the problem. Dim lastRow As Long lastRow = Cells(Rows.Count, "D").End(xlUp).Value Sheets("Sheet1").TxtBox1.Value = lastRow