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. |
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:
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. |
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:
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/1561047487.235246.1617694626649%40mail.yahoo.com. |
Administrator
|
Boleh share workbook sample berisi dummy data ? Sent from my smart enough phone -- 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. |
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
-- 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/907710356.193264.1617961983251%40mail.yahoo.com. ![]() ![]() |
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:
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. |
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:
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. |
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:
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. |
Free forum by Nabble | Edit this page |