Excel ran out of resources while attempting to calculate one or more formulas

  1. 8 bulan yang lalu
    Di sunting 8 bulan yang lalu oleh manweljs_
    Public Sub LoadBarcode()
    Application.ScreenUpdating = False
    b = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    c = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row + 1
    
    On Error Resume Next
        With ActiveSheet
            .Cells(c, 3).Select.
           Selection.FormulaArray = _
            "=IFERROR(INDEX(LHP!C[-2]:C[16],MATCH(1,(RC[-2]=LHP!C[-2])*(RC[-1]=LHP!C[-1]),0),3),"""")"
            .Range("C" & b).Select
            Range(Selection, Selection.End(xlUp)).Select
            Selection.FillDown
        End With
    On Error GoTo 0
    
    Range("C3:C" & b).Copy
    Range("C3").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Cells(c & 3).Select
    
    Application.ScreenUpdating = True
    End Sub


    Untitled-1.png

    Dear All,

    saya punya masalah dengan kode diatas, beberapa kali saya coba masih berjalan lancar. namun ketika sekitar 10-15 menit file tersebut dibuka kemudian kode diatas dijalankan, excel menampilkan pesan seperti pada gambar

    mungkin ada yang punya solusi agar kode tersebut berjalan baik. colek mas @Caton

    Note: total data pada sheet LHP untuk uji coba tersebut sebanyak 5ribu row. actualnya bisa sampai 60-80 ribu row

    Cheers!

  2. Caton

    21 Apr 2018 Terverifikasi Indonesia + 13.255 Poin

    @manweljs_ ...

    Coba tes dengan data < 500 baris data. Bila tidak ada masalah, naikkan jumlah baris data yang diuji. Saya duga, penggunaan Array Formula yang menjadi penyebabnya. Mungkin, coba terapkan:

    Public Sub LoadBarcode()
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        ....

    pada awal prosedur. Dan pada bagian pada akhir prosedur berikan delay beberapa milidetik sebelum mengaktifkan mode kalkulasi:

        ...
        DoEvents
        Application.Wait (Now + TimeValue("0:00:01"))
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        ActiveSheet.Calculate
    End Sub

    Mungkin juga perlu dipikirkan solusi lainnya selain menggunakan Array Formula. Saran saja, lebih baik Array Formula yang digunakan langsung diambil nilainya saja, jadi jangan dilakukan Fill Down. Misalkan saja pilih per 10 baris yang ada Array Formula-nya, salin range tang terseleksi lalu pada range yang sama, lakukan Paste Special As Value. Kemudian, maju 10 baris, lalu ulangi proses yang sama sampai akhir baris data. Tujuannya agar jangan sampai ada Array Formula yang tereksekusi nantinya.

    Demikian.

  3. Di sunting 8 bulan yang lalu oleh manweljs_

    mas @Caton

    sarannya untuk delay suksess!!!
    saya coba pada 5000 baris lancar jaya mas. actualnya pengambilan barcode hanya akan dibawah 800 baris data per sekali load.

    tapi itu dikomputer saya - gak tau kalo komputer dikantor :D. nanti kalo error sy update lagi

    tengkiu mas ;)

  4. Caton

    21 Apr 2018 Terverifikasi Indonesia + 13.255 Poin

    Mas @manweljs_ ...

    Apa benar delay prosesnya yang membuatnya sukses ya ... (^_^)/ Saya kok menduga justru karena pengaktifan proses kalkulasi manual yang menjadi kuncinya... :) Tapi ya saya sendiri belum mencoba. Masih sekedar dugaan, karena proses kalkulasi dengan Array Formula pada dasarnya akan menghabiskan resource Excel, maka memperkecil range yang berisi Array Formula kemudian mengisi kembali range tersebut dengan nilai yang dihasilkan Array Formula tersebut umumnya dapat mencegah Excel kehabisan resource. Kira-kira seperti yang mas lakukan, yakni dengan 800 data per proses...

    Jika dirasa waktu delay agak terlalu lama, coba gunakan fungsi SLEEP dari WinAPI. Fungsi WAIT pada Excel tidak bisa melakukan penundaan dalam orde milidetik, karena minimal ya 1 detik itu... :) Untuk penggunaannya, deklarasikan fungsi eksternalnya pada awal modul:

    #If VBA7 Then  
        Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems  
    #Else  
        Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds as Long) 'For 32 Bit Systems  
    #End If

    lalu panggil fungsinya (misalkan) dengan:

    Sleep 500

    dimana untuk nilai 500 tersebut sama dengan 0.5 detik (1 detik = 1000).

    Demikian.

 

atau Mendaftar untuk ikut berdiskusi!