MOD函数在Excel中独家使用长变量VBA
已更新 我正在清理这篇文章后的决议。总而言之,我正在为Excel计算出下一个最高质数(=NextHighestPrimeNumber(100)
返回101
)并为用户定义的公式。在我开始试验时,我注意到这个公式在21亿左右会出错。我认为它可能已经连接到我的变量,所以我尝试了DOUBLE,但我仍然收到错误。MOD函数在Excel中独家使用长变量VBA
下面是函数:
Function NextHighestPrimeNumber(StartingNumber As Double) As Variant
Dim CeilingTest As Long
Dim i As Long
If StartingNumber < 11 Then
If StartingNumber > 6 Then
NextHighestPrimeNumber = 11
ElseIf StartingNumber > 4 Then
NextHighestPrimeNumber = 7
ElseIf StartingNumber > 2 Then
NextHighestPrimeNumber = 5
ElseIf StartingNumber > 0 Then
NextHighestPrimeNumber = 3
ElseIf StartingNumber = 0 Then
NextHighestPrimeNumber = 1
Else
NextHighestPrimeNumber = "Pick A Positive Integer"
End If
Exit Function
Else
'Create Array
ReDim Prime_Array(0 To 4) As Double
GoTo StartArrayPopulate
DoneWithStartingArray:
If StartingNumber Mod 2 = 0 Then
StartingNumber = StartingNumber - 1
End If
NewNumber:
StartingNumber = StartingNumber + 2
CeilingTest = Int(VBA.Sqr(StartingNumber))
'Array loop
For i = LBound(Prime_Array) To UBound(Prime_Array)
If Prime_Array(i) > CeilingTest Then
NextHighestPrimeNumber = StartingNumber
Exit Function
ElseIf StartingNumber Mod Prime_Array(i) = 0 Then GoTo NewNumber
End If
Next i
'Add new Array Value
ExpandDim:
ReDim Preserve Prime_Array(UBound(Prime_Array) + 1)
Prime_Array(UBound(Prime_Array)) = NextHighestPrimeNumber(Prime_Array(UBound(Prime_Array) - 1))
'test if bigger than cieling
If Prime_Array(UBound(Prime_Array)) > CeilingTest Then
NextHighestPrimeNumber = StartingNumber
Exit Function
ElseIf StartingNumber Mod Prime_Array(UBound(Prime_Array)) = 0 Then GoTo NewNumber
Else
GoTo ExpandDim
End If
End If
Exit Function
StartArrayPopulate:
Prime_Array(0) = 3
Prime_Array(1) = 5
Prime_Array(2) = 7
Prime_Array(3) = 11
Prime_Array(4) = 13
GoTo DoneWithStartingArray
End Function
的问题是,因为VBA MOD功能分子总是转换成一个长变量,即使它以前被定义为双人或一个硬编码的数字!
考考你:
MsgBox (2147483647) mod 3 'Maximum Long Value
比。
MsgBox (2147483647 + 1) mod 3'Oh False!
我用INT函数复制MOD计算Numerator -(INT(Numerator /Denominator)*Denominator)
但也有很多其他的创造性的方式来避免。即使Evaluate
与Excel MOD功能正常工作。
底线:我永远不会使用MOD对于有可能被超过2亿
因为我的第一篇文章我已经清理了我的代码显著任何计算,离开了坏行注释掉扔用`ZZ标记错误而导致失败代码:
Function NextHighestPrimeNumber(StartingNumber As Double) As Variant
Dim CeilingTest As Long
Dim i As Long
If StartingNumber < 11 Then
If StartingNumber > 6 Then
NextHighestPrimeNumber = 11
ElseIf StartingNumber > 4 Then
NextHighestPrimeNumber = 7
ElseIf StartingNumber > 2 Then
NextHighestPrimeNumber = 5
ElseIf StartingNumber > 0 Then
NextHighestPrimeNumber = 3
ElseIf StartingNumber = 0 Then
NextHighestPrimeNumber = 1
Else
NextHighestPrimeNumber = "Pick A Positive Integer"
End If
Exit Function
Else
'Create Array
ReDim Prime_Array(0 To 4) As Double
GoTo StartArrayPopulate
DoneWithStartingArray:
'zz failed code: If StartingNumber Mod 2 = 0 Then
If StartingNumber - (Int(StartingNumber/2) * 2) = 0 Then
StartingNumber = StartingNumber - 1
End If
NewNumber:
StartingNumber = StartingNumber + 2
CeilingTest = INT(VBA.Sqr(StartingNumber))
'Array loop
For i = LBound(Prime_Array) To UBound(Prime_Array)
If Prime_Array(i) > CeilingTest Then
NextHighestPrimeNumber = StartingNumber
Exit Function
'zz failed code: ElseIf StartingNumber Mod Prime_Array(i) = 0 Then
GoTo NewNumber
ElseIf StartingNumber - (Int(StartingNumber/Prime_Array(i)) * Prime_Array(i)) = 0 Then GoTo NewNumber
End If
Next i
'Add new Array Value
ExpandDim:
ReDim Preserve Prime_Array(UBound(Prime_Array) + 1)
Prime_Array(UBound(Prime_Array)) = NextHighestPrimeNumber(Prime_Array(UBound(Prime_Array) - 1))
'test if bigger than ceiling
If Prime_Array(UBound(Prime_Array)) > CeilingTest Then
NextHighestPrimeNumber = StartingNumber
Exit Function
'zz failed code: ElseIf StartingNumber Mod Prime_Array(UBound(Prime_Array)) = 0 Then GoTo NewNumber
ElseIf StartingNumber - (Int(StartingNumber/Prime_Array(UBound(Prime_Array))) * Prime_Array(UBound(Prime_Array))) = 0 Then GoTo NewNumber
Else
GoTo ExpandDim
End If
End If
Exit Function
StartArrayPopulate:
Prime_Array(0) = 3
Prime_Array(1) = 5
Prime_Array(2) = 7
Prime_Array(3) = 11
Prime_Array(4) = 13
GoTo DoneWithStartingArray
End Function
对于VBA7-64,这将显示* 1 *:'Dim i As LongLong:i = 9123372036854775807:MsgBox i Mod 3' - [Data Type Summary - MSDN VBA](https://msdn.microsoft.com/en-us/VBA /语言参考VBA /物品/数据类型的摘要) –
* “LONG变量,它应该有一个范围达到9,223,372,036,854,775,807” *这是在** ** VB.net不** ** VBA。在后一种类型中,“长”是4个字节。 –
“长整型为4字节(32位),范围从-2,147,483,648到 2,147,483,647。您是从哪里找到MSDN中的定义?” [Chip Pearson](https://www.pcreview.co.uk/threads/vba-long-data-type-overflow.959041/) – pnuts
显然你已经咨询了VB.NET的参考。请参阅[VBA参考](https://msdn.microsoft.com/zh-cn/library/aa263420(VS.60).aspx)。 – GSerg