类型为Double的Excel VBA中的溢出错误 [英] Overflow Error in Excel VBA with Type Double

问题描述

我在Excel VBA中遇到溢出错误，找不到我的方法。虽然Microsoft的文档指出双精度的范围应达到〜1.8E308，但是我收到的数字大大低于该阈值的溢出错误。我的代码如下：

 公共函数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）*种子）Mod CDbl（2 ^ 31  -  1）
 
对于i = 2到n 
x（i）=（CDbl（48271）* CDbl（x（i-1）））Mod（CDbl（2 ^ 31-1））
y（i）= CDbl（x（i））/ CDbl（2 ^ 31  -  1）
下一步i 
 
 Fixed_Sample_Nums = y 
 
结束函数
 
'我在
'种子的for循环的第一次迭代中收到错误等于任何值> = 1（即w / seed = 1）：
 
 Debug.Print（（CDbl（48271）* CDbl（48271））Mod（CDbl（2 ^ 31  -  1） ））
 
'导致溢出错误
  

我正在尝试创建一个伪随机数发生器，可以接受任何种子值直到并包括2 ^ 31 - 1。for循环应该能够在leas中迭代t 9,999次（即n = 10000）。如果在前几次迭代中没有遇到溢出错误，则很可能不会遇到任何后续迭代。

可以看出，在进行任何计算之前，我将每个整数转换为一个double。我知道数组大大增加了计算的字节大小，但这似乎不是当前的问题，因为我直接将上面的示例计算复制到立即窗口中，仍然收到溢出错误。我试图在线寻找解决方案已经没有效果，所以我真的很感激任何意见。谢谢提前！

解决方案

尝试使用Chip Pearson的 XMod function： / p>

  x（i）= XMod（（CDbl（48271）* seed），CDbl（2 ^ 31  -  1））
  

他注意到：

您也可以使用
非常大的数字的Mod运算符来获取VBA中的溢出错误。例如，

  Dim Number As Double 
 Dim Divisor As Double 
 Dim Result As Double 
 
数字= 2 ^ 31 
除数= 7 
结果=数字Mod Divisor'这里溢出错误。 
  

功能代码：

 函数XMod（ByVal Number As Double，ByVal Divisor As Double）As Double 
''''''''''''''' ''''''''''''''''''''''''''''''''''''''
'XMod 
'执行与Mod相同的功能，但不会溢出
'与非常大的数字。 Mod和整数除法（\）
'会溢出非常大的数字。 XMod不会。 
'现有代码如：
'Result = Number Mod Divisor 
'应该更改为：
'Result = XMod（Number，Divisor）
'输入值不是整数被截断为整数。负数
'的数字将转换为邮寄号码。 
'这可以在VBA代码中使用，可以直接从
'工作表单元调用。 
'''''''''''''''''''''''''''''''''''''''''' 
 Number = Int（Abs（Number））
除数= Int（Abs（除数））
 XMod = Number  - （Int（数/除数）*除数）
结束函数
  

p>

http://www.cpearson.com/ excel / ModFunction.aspx

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)
    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) * CDbl(48271)) Mod (CDbl(2 ^ 31 - 1))) 

'results in an overflow error 

I am attempting to create a pseudo-random number generator that can take in any 'seed' value up to and including 2 ^ 31 - 1. The for loop should be able to iterate at least 9,999 times (i.e. n = 10000). If the overflow error is not encountered within the first few iterations, it most likely will not be encountered for any subsequent iteration.

As you can see, I am converting each integer to a double before any calculation. I am aware of the fact that arrays substantially increase the byte size of the calculation, but that does not appear to be the current issue as I directly copied the example calculation above into the immediate window and still received the overflow error. My attempts to find a solution online have resulted in no avail, so I would really appreciate any input. Thanks in advance!

解决方案

Try using Chip Pearson's XMod function:

x(i) = XMod((CDbl(48271) * seed), CDbl(2 ^ 31 - 1))

As he notes:

You can also get overflow errors in VBA using the Mod operator with very large numbers. For example,

Dim Number As Double
Dim Divisor As Double
Dim Result As Double

Number = 2 ^ 31
Divisor = 7
Result = Number Mod Divisor ' Overflow error here.

Code for the function:

Function XMod(ByVal Number As Double, ByVal Divisor As Double) As Double
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' XMod
' Performs the same function as Mod but will not overflow
' with very large numbers. Both Mod and integer division ( \ )
' will overflow with very large numbers. XMod will not.
' Existing code like:
'       Result = Number Mod Divisor
' should be changed to:
'       Result = XMod(Number, Divisor)
' Input values that are not integers are truncated to integers. Negative
' numbers are converted to postive numbers.
' This can be used in VBA code and can be called directly from 
' a worksheet cell.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Number = Int(Abs(Number))
    Divisor = Int(Abs(Divisor))
    XMod = Number - (Int(Number / Divisor) * Divisor)
End Function

Additional details:

http://www.cpearson.com/excel/ModFunction.aspx

这篇关于类型为Double的Excel VBA中的溢出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！