Excel exceeding usable RAM and "crash"

vencaxxx · Mar 8th 2017

Hello all, I have problem with 2 inside cycles. Because Excel increasing amount of used RAM each cycle and when reach max amount of usable RAM (around 1,6GB) then stops, give me a error message that there are not free RAM and I have to force shut down the Excel. All others cycles what I using in other macros working OK, only this part of code:

Code

Sub test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False




jmeno2 = ActiveSheet.Name
Dim sPath As String
sPath = "\\cesta\"




Dim lastrow As Long, x As Long


lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row - 1
y = 0
yy = 0


'''''''''''''''''''''''''edited
If Worksheets("Data").Cells(1, 1) = "" Then
    Call Recurse2(sPath)
Else
y = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
End If
'''''''''''''''''''''''''


    Dim i As Long
    Dim test_cesta As String
    Dim test_str As String
    x = 2
    
    Do While x <= lastrow
            For i = 1 To y
            If Mid(Worksheets(jmeno2).Cells(x, 3), InStr(1, Worksheets(jmeno2).Cells(x, 3), "+") + 1, 1) = "H" Then
            test_str = Left(Worksheets(jmeno2).Cells(x, 3), InStr(1, Worksheets(jmeno2).Cells(x, 3), "+") - 1)
            Else
            test_str = Worksheets(jmeno2).Cells(x, 3)
            End If
                If InStr(1, Worksheets("Data").Cells(i, 1), test_str) <> 0 Then
                    test_cesta = Worksheets("Data").Cells(i, 2).Text
                    test1 = GetInfoFromClosedFile(Replace(Worksheets("Data").Cells(i, 2), Worksheets("Data").Cells(i, 1), ""), Worksheets("Data").Cells(i, 1), "Summary", "A1")
                        If IsError(test1) Then
                        test3 = GetInfoFromClosedFile(Replace(Worksheets("Data").Cells(i, 2), Worksheets("Data").Cells(i, 1), ""), Worksheets("Data").Cells(i, 1), "List1", "A1")
                            If IsError(test3) Then
                            Worksheets(jmeno2).Cells(x, 15) = "xxx"
                            Else
                            jmeno_listu = "List1'!$Z$7"
                            Worksheets(jmeno2).Cells(x, 16).FormulaLocal = "=" & "'" & _
                            Left(test_cesta, InStrRev(test_cesta, "\")) & "[" & _
                            Right(test_cesta, Len(test_cesta) - InStrRev(test_cesta, "\")) _
                            & "]" & jmeno_listu
                            Worksheets(jmeno2).Cells(x, 16).Value = Worksheets(jmeno2).Cells(x, 16).Value * 3600
                                If Round(Val(Worksheets(jmeno2).Cells(x, 16)), 2) <> Round(Val(Worksheets(jmeno2).Cells(x, 13)), 2) Then
                                Worksheets(jmeno2).Cells(x, 16).Font.Color = RGB(255, 0, 0)
                                Else
                                Worksheets(jmeno2).Cells(x, 16).Font.Color = RGB(0, 255, 0)
                                End If
                            End If
                        Else
                        jmeno_listu = "Summary'!$J$13"
                        jmeno_listu2 = "Summary'!$O$12"
                        Worksheets(jmeno2).Cells(x, 15).FormulaLocal = "=" & "'" & _
                        Left(test_cesta, InStrRev(test_cesta, "\")) & "[" & _
                        Right(test_cesta, Len(test_cesta) - InStrRev(test_cesta, "\")) _
                        & "]" & jmeno_listu
                        Worksheets(jmeno2).Cells(x, 15).Value = Worksheets(jmeno2).Cells(x, 15).Value
                        Worksheets(jmeno2).Cells(x, 16).FormulaLocal = "=" & "'" & _
                        Left(test_cesta, InStrRev(test_cesta, "\")) & "[" & _
                        Right(test_cesta, Len(test_cesta) - InStrRev(test_cesta, "\")) _
                        & "]" & jmeno_listu2
                        Worksheets(jmeno2).Cells(x, 16).Value = Worksheets(jmeno2).Cells(x, 16).Value
                            If Round(Val(Worksheets(jmeno2).Cells(x, 16)) * Val(Worksheets(jmeno2).Cells(x, 15)), 2) <> Round(Val(Worksheets(jmeno2).Cells(x, 13)), 2) Then
                            Worksheets(jmeno2).Cells(x, 16).Font.Color = RGB(255, 0, 0)
                            Else
                            Worksheets(jmeno2).Cells(x, 16).Font.Color = RGB(0, 255, 0)
                            End If
                        End If
                    DoEvents
                    Exit For
                End If
            Application.StatusBar = "Progress: " & x - 1 & " of " & lastrow - 1 & ": " & Format((x - 1) / (lastrow - 1), "0%") & " | "
            DoEvents
            test_str = Empty
            test1 = Empty
            test3 = Empty
            Next i
        x = x + 1
        DoEvents
        Application.CutCopyMode = False
        ThisWorkbook.Save
        
    Loop
    
x = Empty
y = Empty
yy = Empty
lastrow = Empty




Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True




ThisWorkbook.Save
End Sub

Display More

**Carim** · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

Hello,

Quite often these issues arise around too many formats ...:wink:

Have a look at this thread http://www.ozgrid.com/forum/showthread.php?t=42131

HTH

**rory** · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

What's the code for GetInfoFromClosedFile?

vencaxxx · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

Quote from rory;787385

What's the code for GetInfoFromClosedFile?

Code

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _    wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

But I mean it's not a problem, because when I erase all/most content inside cycle then it is the same situation - RAM usage still raising each cycle.

vencaxxx · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

I don't use any formats in the table, just simple texts and numbers.

**KjBox** · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

You have given the variable "y" a value of zero

Code

y=0

then in your loop you use

Code

For i = 1 to y

vencaxxx · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

I'm not sure if I understand right what you mean, because I need y to have value of number of rows in the sheet all the time. So you mean to set it to zero and in next cycle set it agait to the value I need?

**KjBox** · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

No, I do not mean that.

Look at your code, you set the value of y to be 0.
If you need it to be the number of rows then set it to that.

vencaxxx · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

Sorry my fault, y have to be set to last empty row of the sheet and it is in my original file (i have edited the example to the right version)

**KjBox** · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

OK that makes more sense now

Very difficult to tell why the crash occurs, could be because of the number of rows of data involved.

Can you attach your workbook.
[sw]*[/sw]

**KjBox** · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

You could try adding

Code

Application.ScreenUpdating = 1
Application.ScreenUpdating = 0

at the end of the first loop, that is above this line

Code

Loop

Updating the screen will release the data from the computer memory and thus restore available RAM. Also, remove the Saving of the workbook in the Do loop, there is no need to save it for every iteration of the Do loop when it gets saved at the end of the code, you probably don't need DoEvents either.

vencaxxx · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

It's still the same result. But if I erase all rows inside the i,y cycle, then it works fine, still slow, but memory is still on the same amount.

vencaxxx · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

Now it looks problem is in function below:

Code

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _    wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
'    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
'    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

**KjBox** · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

You really need to attach your workbook.

vencaxxx · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

It is difficult, because it contains sensitive data and links to files stored in company network...I will try prepare something with the same problem.

**Carim** · Mar 8th 2017

Re: Excel exceeding usable RAM and "crash"

Hello,

Just tested the following with is working fine ...

Code

Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String)
    Dim arg As String
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, -4150)
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

HTH

vencaxxx · Mar 9th 2017

Re: Excel exceeding usable RAM and "crash"

I have tried to use your code but with the same result, unfortunately. I will try to add some example later.

Please look attached test files.

vencaxxx · Mar 22nd 2017

Re: Excel exceeding usable RAM and "crash"

Nobody is able to help?

**Carim** · Mar 22nd 2017

Re: Excel exceeding usable RAM and "crash"

Hello,

Sorry ... I am unable to replicate your problem ... and private function does operate smoothly ...

vencaxxx · Mar 23rd 2017

Re: Excel exceeding usable RAM and "crash"

Maybe because it is just a sample of original file, where I have more than 9000 records? The test file works smoothly for me too.

Excel exceeding usable RAM and "crash"

Participate now!

Share

Users Viewing This Thread