Milis Belajar Excel

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

Classic

List

Threaded

10 messages Options

Hilman

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

CONTENTS DELETED

The author has deleted this message.

Kid

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

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.

Hilman

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

CONTENTS DELETED

The author has deleted this message.

Kid

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

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.

Hilman

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

CONTENTS DELETED

The author has deleted this message.

Kid

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

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

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

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

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

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

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

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

Sent from Yahoo Mail on Android

On Sat, Apr 10, 2021 at 3:20 PM, Mr. Kid
<[hidden email]> wrote:

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

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

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

Sent from Yahoo Mail on Android

On Sat, Apr 10, 2021 at 3:20 PM, Mr. Kid
<[hidden email]> wrote:

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.