Excel closes itself

[milfor]

I programmed a new Excel VBA file that is very simple and
is just writing down some odds to remember them. Some
odds are written down but after a couple of minutes Excel
closes itself with a meaningless error message. BA keeps
running. Also 2 old Excel VBA programs keep working
normally.

The new program doesn't divide (so no division by zero is
possible), it only uses a finite number of tables, rows and
columns and also all loops are finite.

Does someone have an idea what else could make Excel
close itself or how I could find out the 'cause of death'?

[Captain Sensible]

Most likely to be a coding issue especially if your other VBA sheets work fine without crashing. Maybe fire up task manager aand see if it's a memory issue with your code.

[milfor]

Today I tried it again and watched the task manager.
CPU was low and memory of Excel remained constant.
But the memory of BA went through the roof!
Under options I set "record hours of chart history" to 0.
This slowed the increase down a bit but it was still high:
1 GB more in 15 minutes!

Anything else I can do so that BA doesn't use so much
memory?

After 20 minutes an "unexpected error" occurred and
BA was still usable but Excel wasn't.

What should I do to find out what the problem is?

[alrodopial]

uninstall/install
ba
excel
windows
????????

[Captain Sensible]

Without seeing the code it's hard to guess why it's causing BA to eat up memeory and crash the sheet, does the BA memory reset once the sheet has crashed.

Maybe post up the code as there may be more efficient ways to acheive what youre trying to do.

[milfor]

No problem as it's not a strategy but only collecting data.

Code: Select all

Function lese(tabelle, zeile, spalte)
    lese = Worksheets(tabelle).Cells(zeile, spalte).Value
End Function
Sub schreibe(tabelle, zeile, spalte, inhalt)
    Worksheets(tabelle).Cells(zeile, spalte).Value = inhalt
End Sub
Function schritt(quote)
    If quote < 2 Then
        schritt = 0.01
    ElseIf quote < 3 Then
        schritt = 0.02
    ElseIf quote < 4 Then
        schritt = 0.05
    ElseIf quote < 6 Then
        schritt = 0.1
    ElseIf quote < 10 Then
        schritt = 0.2
    ElseIf quote < 20 Then
        schritt = 0.5
    ElseIf quote < 30 Then
        schritt = 1
    ElseIf quote < 50 Then
        schritt = 2
    ElseIf quote < 100 Then
        schritt = 5
    Else
        schritt = 10
    End If
End Function
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Columns.Count = 16 Then
        If Now - Int(Now) > 11 / 24 Then
            For zeile = 2 To 1000
                If lese(1, zeile - 1, 1) = "Selection name" Then
                    neu = lese(1, zeile, 15)
                   
                    spalte = 0
                    Do
                        spalte = spalte + 1
                    Loop Until lese(2, zeile, spalte) = ""
                   
                    If spalte = 1 Then
                        Call schreibe(2, zeile, spalte, neu)
                    ElseIf spalte < 4 Then
                        oben = lese(2, zeile, spalte - 1)
                        unten = oben
                        For i = 1 To 5
                            oben = oben + schritt(oben)
                            unten = unten - schritt(unten)
                        Next i
                       
                        If neu > oben - 0.001 Or neu < unten + 0.001 Then
                            Call schreibe(2, zeile, spalte, neu)
                        End If
                    End If
                End If
               
                DoEvents
            Next zeile
        End If
    End If
    Application.EnableEvents = True
End Sub

[milfor]

It's not the fault of my program. I have written a very basic program
that leads to a crash as well:

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Columns.Count = 16 Then
        For i = 1 To 1000
            If Worksheets(1).Cells(i, 1).Value = "" Then
            End If
        Next i
    End If
    Application.EnableEvents = True
End Sub

Therefore I will post this under "Bug reports" too because otherwise the Russells
probably wouldn't look into this.

[Captain Sensible]

Probably best , my German is worse than my VBA knowledge

[milfor]

LOL. I think most things on this world are worse than your VBA knowledge.
I am still puzzled why looking at 1000 cells again and again uses up all the computer memory.

[Captain Sensible]

I'd guess that's down to the way excel is coded and might be having to access the sheet on each of those 1000 requests. If you dumped the data into an array it'd quickly loop thru it rather than reading thru the sheet. i.e. we grab the exact data set you want into an array, loop thru the arraydumping whatever you want into an array, then dump that data back to the sheet and reset the array t nothing so it's not retaining the memory.