[milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Hilman
CONTENTS DELETED
The author has deleted this message.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Kid
Administrator
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



On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
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

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Hilman
CONTENTS DELETED
The author has deleted this message.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Kid
Administrator
Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

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


On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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



On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
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

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Hilman
CONTENTS DELETED
The author has deleted this message.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Kid
Administrator
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.


On Fri, Apr 9, 2021 at 4:53 PM Hilman <[hidden email]> wrote:
Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:


Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

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


On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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



On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
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

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPs1JcObqU45JqHdATgucb%2BhLcx--uyhJ3Op%3DvPR4UhW3A%40mail.gmail.com.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Kid
Administrator
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 <[hidden email]> wrote:
Masih sama hasil nya.... 
Apanya lagi ya ?
Hehehe... btw thanks ya...

On Friday, April 9, 2021, 07:42:34 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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.


On Fri, Apr 9, 2021 at 4:53 PM Hilman <[hidden email]> wrote:
Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:


Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

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


On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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



On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
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

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPs1JcObqU45JqHdATgucb%2BhLcx--uyhJ3Op%3DvPR4UhW3A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPu8zJKv8CbJmXXX1zopPD3RbG8e%2Befb7sS_kb%2BSCgub5g%40mail.gmail.com.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Kid
Administrator
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 <[hidden email]> 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


On Saturday, April 10, 2021, 08:26:34 AM GMT+7, Mr. Kid <[hidden email]> wrote:


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 <[hidden email]> wrote:
Masih sama hasil nya.... 
Apanya lagi ya ?
Hehehe... btw thanks ya...

On Friday, April 9, 2021, 07:42:34 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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.


On Fri, Apr 9, 2021 at 4:53 PM Hilman <[hidden email]> wrote:
Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:


Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

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


On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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



On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
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

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPs1JcObqU45JqHdATgucb%2BhLcx--uyhJ3Op%3DvPR4UhW3A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPu8zJKv8CbJmXXX1zopPD3RbG8e%2Befb7sS_kb%2BSCgub5g%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPtibV6BJ08fWZY2e5CQCg7ewUAGte_7_aK0NWFRX6Ek8Q%40mail.gmail.com.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Kid
Administrator


Sent from my smart enough phone

On Apr 11, 2021, at 07:37, hendrik karnadi <[hidden email]> 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
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 <[hidden email]> 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


On Saturday, April 10, 2021, 08:26:34 AM GMT+7, Mr. Kid <[hidden email]> wrote:


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 <[hidden email]> wrote:
Masih sama hasil nya.... 
Apanya lagi ya ?
Hehehe... btw thanks ya...

On Friday, April 9, 2021, 07:42:34 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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.


On Fri, Apr 9, 2021 at 4:53 PM Hilman <[hidden email]> wrote:
Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:


Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

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


On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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



On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
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

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPs1JcObqU45JqHdATgucb%2BhLcx--uyhJ3Op%3DvPR4UhW3A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPu8zJKv8CbJmXXX1zopPD3RbG8e%2Befb7sS_kb%2BSCgub5g%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPtibV6BJ08fWZY2e5CQCg7ewUAGte_7_aK0NWFRX6Ek8Q%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/F4BD069B-F062-4831-A1E4-D29E250A0365%40gmail.com.
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Kid
Administrator
In reply to this post by Kid
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 <[hidden email]> 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
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 <[hidden email]> 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


On Saturday, April 10, 2021, 08:26:34 AM GMT+7, Mr. Kid <[hidden email]> wrote:


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 <[hidden email]> wrote:
Masih sama hasil nya.... 
Apanya lagi ya ?
Hehehe... btw thanks ya...

On Friday, April 9, 2021, 07:42:34 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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.


On Fri, Apr 9, 2021 at 4:53 PM Hilman <[hidden email]> wrote:
Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:


Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

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


On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:


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



On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
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

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPs1JcObqU45JqHdATgucb%2BhLcx--uyhJ3Op%3DvPR4UhW3A%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPu8zJKv8CbJmXXX1zopPD3RbG8e%2Befb7sS_kb%2BSCgub5g%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPtibV6BJ08fWZY2e5CQCg7ewUAGte_7_aK0NWFRX6Ek8Q%40mail.gmail.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/30A9E36D-6D3F-44CB-BA2E-37AEA92F1210%40gmail.com.