Macro pada Excel (Office 365) sangat lambat

30 views
Skip to first unread message

Hilman

unread,
Apr 5, 2021, 2:44:51 PM4/5/21
to Milis Belajar-Excel
Selamat malam Om
Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,
kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards
Hilman

Mr. Kid

unread,
Apr 6, 2021, 1:50:10 AM4/6/21
to Milis Belajar-Excel
Hai Hilman,

Coba hindari penggunaan Select, Activate, Selection, ActiveCells, ActiveWorkbook
hindari buka tutup proteksi sheet maupun workbook dengan memanfaatkan userinterfaceonly saat akan mem-protect, supaya VBA bisa tetap bekerja seperti biasa tanpa perlu membuka proteksi.
hindari conditional formatting pada cells dalam jumlah banyak (apalagi di cells yang masih kosong [belum ada datanya]) 
Coba ditelaah ulang setiap prosesnya, mungkin bisa disusun alur proses yang lebih efisien.
Coba ditelaah ulang setiap baris program di setiap prosesnya, mungkin bisa disusun ulang agar lebih sederhana.

Excel 2016 ke atas adalah era baru Excel setelah era Excel 2003. Jadi Excel 2007 s.d. Excel 2013 bisa disebut versi transisi.
Jadi, perbaikan (tuntutan untuk lebih efisien dalam proses otomasi) VBA juga diperlukan.

Regards,
Kid



--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to milis-belajar-e...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

Hilman

unread,
Apr 6, 2021, 3:54:15 AM4/6/21
to Milis Belajar-Excel, Mr. Kid
berikut coding nya...

Sub SalinDataPoultry()

    Dim RgSumber As Range
    Dim wsSumber As Worksheet
    Dim wbSumber As Workbook
    
    Dim rgData As Range
    Dim wsData As Worksheet
    Dim wbData As Workbook
    Dim rwData As Long
    
    Dim rg As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.PrintCommunication = False
    
    Set wbData = ThisWorkbook
    Set wsData = wbData.Worksheets("Daily Production")
    
    rwData = wsData.Range("A1").End(xlDown).Row
        
    Set wbSumber = Workbooks("STOCK 1220-PA (003).xlsx") 'Ganti
    Set wsSumber = wbSumber.Worksheets("MASTER")
    Set RgSumber = wsSumber.Range("I75:GL75") 'Ganti
    
    For Each rg In RgSumber
        
        
        If rg.Value > 0 Then
            rwData = rwData + 1
            wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
            wsData.Cells(rwData, 6).Value = "Crumbler"
            wsData.Cells(rwData, 7).Value = rg.Value
            
            Select Case wsSumber.Cells(4, rg.Column).Value
                Case "Prod."
                    wsData.Cells(rwData, 5).Value = "Production"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case "Retur"
                    wsData.Cells(rwData, 5).Value = "Retur"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case "Sales"
                    wsData.Cells(rwData, 5).Value = "Sales"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case "Depo"
                    wsData.Cells(rwData, 5).Value = "Depo"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case "Remix"
                    wsData.Cells(rwData, 5).Value = "Remix"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case vbNullString
                    wsData.Cells(rwData, 5).Value = "Stock"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case Else
                    MsgBox "Header tidak ketemu"
                    Application.ScreenUpdating = True
                    Application.EnableEvents = True
                    Application.Calculation = xlCalculationAutomatic
                    Exit Sub
            End Select
    
        End If
        
        
        If rg.Offset(1, 0).Value > 0 Then
            rwData = rwData + 1
            wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
            wsData.Cells(rwData, 6).Value = "Mash"
            wsData.Cells(rwData, 7).Value = rg.Offset(1, 0).Value
            Select Case wsSumber.Cells(4, rg.Column).Value
                Case "Prod."
                    wsData.Cells(rwData, 5).Value = "Production"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case "Retur"
                    wsData.Cells(rwData, 5).Value = "Retur"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case "Sales"
                    wsData.Cells(rwData, 5).Value = "Sales"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case "Depo"
                    wsData.Cells(rwData, 5).Value = "Depo"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case "Remix"
                    wsData.Cells(rwData, 5).Value = "Remix"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case vbNullString
                    wsData.Cells(rwData, 5).Value = "Stock"
                    wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
                Case Else
                    MsgBox "Header tidak ketemu"
                    Application.ScreenUpdating = True
                    Application.EnableEvents = True
                    Application.Calculation = xlCalculationAutomatic
                    Exit Sub
            End Select
    
        End If
    
    Next rg
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.PrintCommunication = True
    
End Sub

select, activate, selection dll, sudah tidak pernah saya gunakan lagi..

Regards
Hilman


Mr. Kid

unread,
Apr 7, 2021, 8:52:23 PM4/7/21
to Hilman, Milis Belajar-Excel
Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

Hilman

unread,
Apr 9, 2021, 8:01:49 AM4/9/21
to Mr. Kid, Milis Belajar-Excel
Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

Daily Report Recap Belajar Excel.xlsm
Data 2021-03.xlsx

Mr. Kid

unread,
Apr 9, 2021, 8:42:34 AM4/9/21
to Milis Belajar-Excel
Coba di Excel Options -> pane formula -> bagian error checking dikurangi yang dicentang
Kemudian, di Excel Table, formula yang pakai @ diganti dengan merujuk alamat cells nya
Kalau office 365 nya sign in di account ms, di sign out saja.

Mr. Kid

unread,
Apr 9, 2021, 9:26:34 PM4/9/21
to milis Belajar-Excel
Biasanya office 365 bermasalah dengan hardware graphics (bisa di-disable dengan dicentang di Excel Options Display).
Protect unprotect file, workbook, sheets (termasuk lock unlock cells dalam sheet yang diprotect), set visible sheets juga.
Kalau file dibuat dengan versi sebelum Office 365, coba update office 365 (supaya fitur konversi dan compatibility-nya bisa lebih baik).

Kalau masalah compatibility, biasanya dengan membuat ulang langsung di office 365 dari new workbook bisa sering menyelesaikan masalah speed.
Bikin ulang persis, kalau copy paste, pastikan paste values lalu diformat atau dipasangi formula atau dibuat tabelnya ulang. Script VBA bisa di-copy paste apa adanya.

Kalau dengan bikin ulang masih tetap lambat, barulah dicari alur proses dalam script yang lebih efisien.

Regards,
Kid



On Fri, Apr 9, 2021 at 8:55 PM Hilman <hilman...@yahoo.com> wrote:
Masih sama hasil nya.... 
Apanya lagi ya ?
Hehehe... btw thanks ya...

Mr. Kid

unread,
Apr 10, 2021, 4:20:52 AM4/10/21
to milis Belajar-Excel
Excel versi yang lebih baru memang lebih lambat di-vba-nya, umumnya lebih lambat sekitar 2-3 kali ketika ada proses menulis ke cells satu per satu.



On Sat, Apr 10, 2021 at 8:58 AM Hilman <hilman...@yahoo.com> wrote:
Saya coba dengan buat code yang sederhana, yakni mengisi cell A1:A10000, seperti di bawah ini :

Sub TestMacro()

    Dim cntTime As Double
    Dim ws As Worksheet
    Dim rg As Range
    
    cntTime = Timer
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.PrintCommunication = False
    
    Set ws = ThisWorkbook.Worksheets("sheet1")
    
    For Each rg In ws.Range("A1:A10000")
        rg.Value = "coba" & rg.Row
    Next rg
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.PrintCommunication = True
    
    cntTime = Timer - cntTime
    
    MsgBox cntTime
    
    Debug.Print cntTime

End Sub

Waktu proses nya 4 detik.
Kalo dicoba di excel 2013, ini gak nyampe 0.1 detik

Ini saya buat ulang di excel 2016.... 

Regards
Hilman


Mr. Kid

unread,
Apr 10, 2021, 8:57:14 PM4/10/21
to hendrik...@yahoo.com, milis-bel...@googlegroups.com


Sent from my smart enough phone

On Apr 11, 2021, at 07:37, hendrik karnadi <hendrik...@yahoo.com> wrote:

Mungkin krn versi yang lebih baru lebih mengandalkan Power Query ya Mr. Kid ?

Regards,

Mr. Kid

unread,
Apr 10, 2021, 9:00:51 PM4/10/21
to milis-bel...@googlegroups.com
Tidak juga.
Excel lebih baru mengoptimalkan proses formula dan banyak fitur dasar seperti filter, conditional formatting, dan sebagainya.
Mungkin proses optimasi area tersebut berdampak pada bertambahnya proses saat akan menulis ke cells.


Sent from my smart enough phone
On Apr 11, 2021, at 07:37, hendrik karnadi <hendrik...@yahoo.com> wrote:

Mungkin krn versi yang lebih baru lebih mengandalkan Power Query ya Mr. Kid ?

Regards,
HK


On Sat, Apr 10, 2021 at 3:20 PM, Mr. Kid
Reply all
Reply to author
Forward
0 new messages