Excel crashes when referencing the nth-1 array element

[IDidNotDoIt]

Hi, all!

I'm trying to play with prime numbers and implementing several methods for calculating them.
When arrived to Eratosthenes' sieve I need to use as much as memory as possible, so my algorithm starts dimensioning an array and sizing downwards until no Error 7 Out of Memory happen.
Then I initialize the redimensioned array and Excel always crashes when referencing the nth-1 array element. I tried different values for the upper bound limit (90%, 80%, 75% of max value, max value minus 2, minus 10...) and it keeps on crashing at the before last element reference.

Can anybody help me with this issue?

Tanks for your time and best regards.

Option Explicit

' public constants
Public Const gkiDefault = 255
Public Const gkiNumByt = 20
Public Const gkiCycle = 4
Public Const gksCycle = " 2 2 4 2"
Public Const gklLen = 50000
Public Const gknFactor = 1 '0.9 0.8 0.75
Public Const gkiKb = 1024

' public declarations
Public gArr() As String * gklLen

Sub TestSize()
    '
    ' constants
    '
    ' declarations
    Dim sCycle() As String, iCycle() As Integer
    Dim sDefault As String, sElement As String
    Dim lSize As Long, lSizeX As Long
    Dim I As Long, A As String
    '
    ' start
    '  array size
    lSize = gklLen
    On Error Resume Next
    Do
        Err.Clear
        lSize = lSize - 1
        ReDim gArr(1 To lSize)
    Loop Until Err.Number = 0
    Debug.Print "lSize act: "; lSize
    On Error GoTo 0
    '  initialize
    '   cycle
    sCycle = Split(gksCycle)
    ReDim iCycle(1 To UBound(sCycle))
    For I = 1 To UBound(sCycle)
        iCycle(I) = CInt(sCycle(I))
    Next I
    '   array
    sDefault = Chr(gkiDefault)
    sElement = String(gklLen, sDefault)
    lSizeX = lSize - 2
    For I = 1 To lSizeX
        gArr(I) = sElement
        DoEvents
        Debug.Print I;
    Next I
    For I = lSizeX + 1 To lSize
        ' Excel crashes here, in the nth-1 array element referencing.
        ' No matter which value has lSize, even tried with -2 and -10,
        ' and it always crashes when referencing before-last array element.
        gArr(I) = ""
    Next I
    '
    ' process
    '
    ' end
    '
End Sub

 

[IDidNotDoIt]

Hi!

Quoting forum rules... "Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight.", I think that the world has taken a couple of turns since I wrote this post... so...

Quoting Pink Floyd... "Is there anybody out there?" who can illuminate the darkness of my ignorance with a ray of wisdom light?

Thanks again.

 

[Hui]

Have you had a look at:

Sieve of Eratosthenes in Excel VBA

Learn about prime numbers and how to build the Sieve of Eratosthenes in Excel using VBA.

www.lejavbasolutions.com

or

sieve of eratosthenes vba code explanation

Hello Guys i need some help in understanding the following vba code Sub Sieve() Const n As Long = 10 ' numbers to test Dim colCand As Collection ' candidates Dim aiPrim() As Long ' primes for output Dim iPrim As Long ' a prime...

www.vbaexpress.com

 

[IDidNotDoIt]

Hi, Hui!

Thank you very much for your kindness. Even I have it yet coded for small numbers (<10^6) I'll give it a look today, it's never late to learn something new or different approachs for the same problem.

However I think that the crash error exceeds the scope of this problem and there's where I neither have a clue nor found
related info searching the web. It's a simple array dimensioning, referencing the nth-1 element and get Excel crashed. Please test this snippet, it gets to the same fatal end:

Option Explicit
Public Const gklLen As Long = 50000
Public gArr() As String * gklLen

Sub TestSize()
    Dim lSize As Long
    lSize = 20000 ' for reaching 15204 in my case, please adjust it properly
    Debug.Print "lSize max: "; lSize
    On Error Resume Next
    Do
        Err.Clear
        lSize = lSize - 1
        ReDim gArr(1 To lSize)
    Loop Until Err.Number = 0
    Debug.Print "lSize act: "; lSize
    gArr(lSize - 1) = "A"
End Sub

Thanks for your time, and let me ask if you could test the code and get the same error. Just curioius.
My best regards!