Odd things between runs...

I'm afraid I can't really post specifics as it's quite a complex system of class modules and I can't seem to reduce the problem down to the particular bit which is ballsing up to post an example, so this is more a broader question to all the experts out there who may have experienced this, or something similar.
Basically, every other time I run my code I get a run time error. The first time I run it, it's fine. Then, when it's finished, I don't do anything (and nothing has changed on my sheet, at the moment I'm just printing to debug window so data is read from a sheet but nothing actually written to it). Then I restart the exact same macro, and I get a subscript out of range error. I then press End, then straight away restart the macro and it works fine. Next time, it crashes again.

Basically, Has anyone else ever had problems anything like this, and could maybe give me some sort of hint as to what may be going wrong?? I'm running Excel 2000 on an NT4 computer. I know this is vague but I'm gonna start pulling my hair out shortly...

Hard to say much without a few more specifics, but ..

If the behaviour is different then something in the environment must be different. If you are not changing anything in your workbook (including the active sheet or cell) then it must be in your VBA environment - do you have any global variables? or do you create objects but not delete them afterwards?

It would help at least to know what statement is giving the error. What is the subscript? What is being subscripted? How are any variables defined?

Hi Tony, thanks for the pointers, having examined it fairly closely I have discovered what was going on. I managed to create a quick code sample which illustrates it as well. From my post count you can see that I'm new here so I hope I manage to format this correctly:

Code:

---

Option Explicit
Private Type NEW_TYPE
    alngNums() As Long
End Type
Private m_audtNewTypes() As NEW_TYPE
 
Public Sub BreakMe()
Dim i As Long, j As Long
On Error GoTo Uninitialized_Error:
        ReDim Preserve m_audtNewTypes(UBound(m_audtNewTypes) + 1)
Uninitialized_Resume:
    On Error GoTo 0
ReDim m_audtNewTypes(UBound(m_audtNewTypes)).alngNums(0 To 9)
For i = 0 To UBound(m_audtNewTypes)
        For j = 0 To 9
            m_audtNewTypes(i).alngNums(j) = j
        Next j
    Next i
'with this line in it fails every second run.
    ReDim m_audtNewTypes(0)
Exit Sub
Uninitialized_Error:
    ReDim m_audtNewTypes(0)
    Resume Uninitialized_Resume
End Sub

---

Basically my laziness in using

Code:

---

ReDim Preserve array(Ubound(array) + 1)

---

style array building was causing me problems cos at the very end of the routine I was calling

Code:

---

ReDim m_audtNewTypes(0)

---

which I hoped was an easy way of clearing the array. Problem is, VBA seems to remember this assignment so next time it runs, m_audtNewTypes already has one element at index 0 (this can be checked by putting a breakpoint on the error handler, it is never reached on the second run), so

Code:

---

ReDim Preserve m_audtNewTypes(UBound(m_audtNewTypes) + 1)

---

actually adds another element at index 1. Later, when each of the arrays of Long for each element is iterated, the element at 0 has never had its array of Long initialised, as this is only done on whatever is at the last array position, which I thought would always be 0, ie the only element, but as VBA is remembering the assignment just before execution stops on the first run, it is causing me to receive my old friend subscript out of range. Once you have clicked 'End' on this error, the next tim eit runs it works fine.

What is really puzzling me is why, when the offending line is omitted, there is no problem. The array has still been initialized with one element. It is never explicitly removed. hmmm

I was always under the impression that VBA released objects and memory when execution stopped. I am finding out increasingly that I was wrong!!

Sorry for the remarkably long post. Maybe someone will be able to explain to me what is going on!!

Hi,

To clear your array use the Erase keyword.
Your right excel should release objects when finish. But your example does not let excel know your finished. So the module level variables remain valid.
You can use the keyword End to tell excel you are completely finished.

Code:

---

Public Sub BreakMe()
   
    Dim i As Long, j As Long
   
On Error GoTo Uninitialized_Error:
    ReDim Preserve m_audtNewTypes(UBound(m_audtNewTypes) + 1)
Uninitialized_Resume:
    On Error GoTo 0
   
    ReDim m_audtNewTypes(UBound(m_audtNewTypes)).alngNums(0 To 9)
   
    For i = 0 To UBound(m_audtNewTypes)
        For j = 0 To 9
            m_audtNewTypes(i).alngNums(j) = j
        Next j
    Next i
   
    '***************
    'with this line in it fails every second run.
' Use Erase to clear array
    Erase m_audtNewTypes
'    ReDim m_audtNewTypes(0)
    '****************
   
' Use End to release variables declared at module level
'    End
    Exit Sub
   
Uninitialized_Error:
    ReDim m_audtNewTypes(0)
    Resume Uninitialized_Resume
   
End Sub

---

Hi alimcpill,

Welcome to VBAX! No problems with the long post which you have formatted just fine.

Essentially, variables have the same lifetime as their container. Module level variables are preserved for the lifetime of the module, which, for a standard module, is normally the same as the lifetime of the application. In other words, you are responsible for controlling the value of module level variables at all times.

Andy is correct that the End statement will reset them all but it's rather heavy handed and may have undesired effects in a complex system (as you say you have) with instances of objects (class modules) being abruptly destroyed without firing events, etc.

For various reasons, module level variables should only be used occasionally when you really need the functionality. A better solution is generally to define procedure level variables and pass them as parameters to routines which need them.

Thanks for the pointers guys, Yes I see how the End statement would work in that example I posted but I think it would be a bit dangerous in my actual system, as Tony said. I'm going to try declaring everything locally rather than module level and passing as and when they are needed.