香港新浪網 MySinaBlog
« 上一篇 | 下一篇 »
Emily | 23rd Jun 2006, 12:34 PM | General | (1599 Reads)

Excel 計算一定範圍內質數的小程式

EH 問題

'---------------------------------------------------------------------------------------
' Module    :
' DateTime  : 23/6/2006 12:39
' Author    : Emily
' Purpose   : List Prime Number in a range
'---------------------------------------------------------------------------------------

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub Emily_230606()
    Dim m&, n&, k&, a&, b&, arr(), t1
    t1 = timeGetTime
    ReDim arr(1 To 65536)
    k = 2
    arr(1) = 2

    Range("E2:E" & Range("E65536").End(xlUp).Row + 1).Clear
    For a = 3 To [A2]     ' A2 is the required range
        If IsPrime(a) Then
            arr(k) = a
            k = k + 1
        End If
    Next
a
    ReDim Preserve arr(1 To k - 1)
    Range("E2:E" & k) = Application.Transpose(arr)
    Debug.Print timeGetTime - t1 & "ms"
End Sub

Function IsPrime(TestNumber As Long) As Boolean  ' 2 is prime, not tested

Dim Count As Long
Dim Half As Long
If (TestNumber Mod 2) = 0 Then
         Exit Function
End If

Half = Sqr(TestNumber)
For Count = 3 To Half Step 2
     If (TestNumber Mod Count) = 0 Then
         Exit Function
     End If
Next

IsPrime = True
End Function

'

參考 ExcelHelp 質數