24 Contoh Makro Excel Berguna untuk Pemula VBA (Sedia untuk Digunakan)

Menggunakan makro Excel boleh mempercepatkan kerja anda dan menjimatkan banyak masa.

Satu cara untuk mendapatkan kod VBA adalah dengan merekod makro dan mendapatkan kod yang dihasilkannya.Walau bagaimanapun, kod perakam makro selalunya penuh dengan kod yang sebenarnya tidak diperlukan.Perakam makro juga mempunyai beberapa had.

Oleh itu, adalah berbaloi untuk mempunyai set makro VBA yang berguna yang boleh anda simpan dalam poket belakang anda dan gunakan apabila anda memerlukannya.

Walaupun mungkin mengambil sedikit masa untuk mengodkan makro Excel VBA pada mulanya, setelah anda selesai, anda boleh menggunakannya sebagai rujukan dan menggunakannya pada kali berikutnya anda memerlukannya.

Dalam artikel besar ini, saya akan menyenaraikan beberapa contoh makro Excel berguna yang sering saya perlukan dan simpan dalam peti besi peribadi saya.

Saya akan terus mengemas kini tutorial ini dengan lebih banyak contoh makro.Sila tinggalkan komen jika anda rasa sesuatu harus ada dalam senarai.

Anda boleh menanda halaman ini untuk rujukan masa hadapan.

Sekarang, sebelum saya masuk ke dalam contoh makro dan memberi anda kod VBA, izinkan saya menunjukkan kepada anda cara menggunakan kod contoh tersebut.

Menggunakan kod daripada contoh makro Excel

Berikut ialah langkah-langkah yang perlu diikuti untuk menggunakan kod daripada mana-mana contoh:

• Buka buku kerja yang anda ingin gunakan makro.
• Tahan kekunci ALT dan tekan F11.Ini akan dibukaeditor VB.
• Klik kanan pada mana-mana objek dalam Project Explorer.
• Pergi ke Sisipkan -> Modul.
• Salin dan tampal kod dalam tetingkap kod modul.

Jika contoh mengatakan anda perlu menampal kod ke dalam tetingkap kod lembaran kerja, klik dua kali objek lembaran kerja dan salin-tampal kod ke dalam tetingkap kod.

Selepas memasukkan kod dalam buku kerja, anda perlu menyimpannya dengan sambungan .XLSM atau .XLS.

Bagaimana untuk menjalankan makro

Selepas menyalin kod dalam editor VB, langkah-langkah untuk menjalankan makro adalah seperti berikut:

• Pergi ke tab pembangun.
• Klik Makro.

• Dalam kotak dialog Makro, pilih makro untuk dijalankan.
• Klik butang Jalankan.

Jika anda tidak menemui tab pembangun dalam reben, baca tutorial ini untuk mengetahui cara mendapatkannya.

Jika kod itu ditampal dalam tetingkap kod lembaran kerja, anda tidak perlu risau tentang menjalankan kod tersebut.Ia berjalan secara automatik apabila tindakan yang ditentukan berlaku.

Sekarang, mari kita lihat contoh makro berguna yang boleh membantu anda mengautomasikan kerja anda dan menjimatkan masa.

Nota: Anda akan menjumpai banyak apostrof (') diikuti dengan satu atau dua baris.Ini adalah ulasan yang diabaikan semasa menjalankan kod dan diletakkan sebagai ulasan diri/pembaca.

Jika anda menemui sebarang ralat dalam artikel atau kod, sila beritahu saya.

Contoh Makro Excel

Artikel ini menerangkan contoh makro berikut:

Nyahsembunyikan semua lembaran kerja sekaligus

Jika anda sedang mengerjakan buku kerja dengan berbilang helaian tersembunyi, anda perlu menyahsembunyikan helaian ini satu demi satu.Ini mungkin mengambil sedikit masa jika terdapat banyak helaian tersembunyi.

Berikut ialah kod untuk menyahsembunyikan semua helaian dalam buku kerja.

'Kod ini akan menyahsembunyikan semua helaian dalam buku kerja Sub UnhideAllWoksheets() Dim ws As Worksheet For each ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

Kod di atas menggunakan gelung VBA (Untuk Setiap) untuk lelaran melalui setiap lembaran kerja dalam buku kerja.Kemudian ia menukar sifat boleh dilihat helaian kepada kelihatan.

Berikut ialah tutorial terperinci tentang cara menyahsembunyikan lembaran kerja dalam Excel menggunakan pelbagai kaedah.

Sembunyikan semua lembaran kerja kecuali lembaran kerja aktif

Anda boleh menggunakan kod makro ini jika anda sedang mengusahakan laporan atau papan pemuka dan ingin menyembunyikan semua helaian kecuali helaian yang mempunyai laporan/papan pemuka.

'Makro ini akan menyembunyikan semua lembaran kerja kecuali helaian aktif Sub HideAllExceptActiveSheet() Malapkan ws Sebagai Lembaran Kerja Untuk Setiap ws Dalam Buku Kerja Ini. Lembaran Kerja Jika ws.Name <> ActiveSheet.Name Kemudian ws.Visible = xlSheetHidden Next ws End Sub

Isih lembaran kerja mengikut abjad menggunakan VBA

Kod makro ini boleh berguna jika anda mempunyai buku kerja dengan banyak helaian dan anda ingin mengisih helaian tersebut mengikut abjad.Ini boleh berlaku jika anda mempunyai nama helaian sebagai tahun atau nama pekerja atau nama produk.

'Kod ini akan mengisih lembaran kerja mengikut abjad Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount Jika Helaian(j).Nama < Helaian(i).Nama Kemudian Helaian(j).Alih sebelum:=Helaian(i) Tamat Jika Seterusnya j Seterusnya i Aplikasi.ScreenUpdating = True End Sub

Lindungi semua lembaran kerja sekaligus

Jika anda mempunyai banyak helaian dalam buku kerja dan anda ingin melindungi semua helaian, anda boleh menggunakan kod makro ini.

Ia membolehkan anda menentukan kata laluan dalam kod.Anda memerlukan kata laluan ini untuk menyahlindung helaian.

'Kod ini akan melindungi semua helaian sekali gus Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'gantikan Test123 dengan kata laluan yang anda inginkan Untuk Setiap ws Dalam Worksheets ws.Protect password:=password Next ws Tamat Sub

Nyahlindung semua lembaran kerja sekaligus

Jika anda melindungi beberapa atau semua lembaran kerja anda, anda boleh menyahlindunginya dengan hanya sedikit pengubahsuaian pada kod yang digunakan untuk melindungi lembaran kerja.

'Kod ini akan melindungi semua helaian sekaligus Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'gantikan Test123 dengan kata laluan yang anda inginkan Untuk Setiap ws Dalam Lembaran Kerja ws.Nyahlindungi kata laluan:=kata laluan Seterusnya ws Tamat Sub

Harap maklum bahawa kata laluan mestilah sama dengan kata laluan yang digunakan untuk mengunci helaian.Jika tidak, anda akan melihat ralat.

Nyahsembunyikan semua baris dan lajur

Kod makro ini akan menyahsembunyikan semua baris dan lajur tersembunyi.

Ini boleh berguna jika anda mengambil fail daripada orang lain dan ingin memastikan tiada baris/lajur tersembunyi.

'Kod ini akan menyahsembunyikan semua baris dan lajur dalam Subhelaian Kerja UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Nyahcantum semua sel yang digabungkan

Menggabungkan sel menjadi satu adalah amalan biasa.Walaupun ia berfungsi, anda tidak akan dapat mengisih data apabila sel digabungkan.

Jika anda menggunakan lembaran kerja dengan sel bercantum, gunakan kod di bawah untuk menyahcantum semua sel yang digabungkan sekali gus.

'Kod ini akan menyahgabung semua sel yang digabungkan Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Ambil perhatian bahawa saya mengesyorkan menggunakan pilihan "Pusat Merentas Pilihan" dan bukannya pilihan "Gabung dan Pusat".

Simpan buku kerja dengan cap masa dalam nama

Terdapat banyak masa apabila anda mungkin perlu mencipta versi yang berfungsi.Ini bagus untuk projek jangka panjang yang berurusan dengan fail dari semasa ke semasa.

Amalan yang baik ialah menyimpan fail dengan cap masa.

Menggunakan cap masa akan membolehkan anda kembali ke fail untuk melihat perubahan yang dibuat atau data yang digunakan.

Berikut ialah kod yang secara automatik menyimpan buku kerja dalam folder yang ditentukan dan menambah cap masa semasa menyimpan.

'Kod ini akan Menyimpan Fail Dengan Cap Masa dalam namanya Sub SaveWorkbookWithTimeStamp() Malapkan cap waktu Sebagai String cap masa = Format(Tarikh, "dd-mm-yyyy") & "_" & Format(Masa, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & Cap masa End Sub

Anda perlu menentukan lokasi folder dan nama fail.

Dalam kod di atas, "C:UsersUsernameDesktop ialah lokasi folder yang saya gunakan.Anda perlu menentukan lokasi folder tempat anda ingin menyimpan fail.Juga, saya menggunakan nama generik "WorkbookName" sebagai awalan nama fail.Anda boleh menentukan kandungan yang berkaitan dengan projek atau syarikat anda.

Simpan setiap lembaran kerja sebagai PDF yang berasingan

Jika anda menggunakan data daripada tahun atau jabatan atau produk yang berbeza, anda mungkin perlu menyimpan lembaran kerja yang berbeza sebagai fail PDF.

Walaupun ini boleh menjadi proses yang memakan masa jika dilakukan secara manual, VBA mempercepatkan perkara itu.

Berikut ialah kod VBA yang menyimpan setiap lembaran kerja sebagai PDF yang berasingan.

'Kod ini akan menyimpan setiap helaian kerja sebagai PDF Sub SaveWorkshetAsPDF() Malapkan ws Sebagai Lembaran Kerja Untuk Setiap ws Dalam Lembaran Kerja ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Seterusnya ws End Sub

Dalam kod di atas, saya telah menyatakan alamat lokasi folder tempat saya ingin menyimpan PDF.Selain itu, setiap PDF akan mendapat nama yang sama seperti helaian.Anda perlu mengubah suai lokasi folder ini (melainkan nama anda juga Sumit dan anda menyimpannya dalam folder ujian pada desktop anda).

Ambil perhatian bahawa kod ini hanya berfungsi untuk lembaran kerja (bukan untuk helaian carta).

Simpan setiap lembaran kerja sebagai PDF yang berasingan

Berikut ialah kod untuk menyimpan keseluruhan buku kerja sebagai PDF dalam folder yang ditentukan.

'Kod ini akan menyimpan keseluruhan buku kerja sebagai PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

Anda mesti menukar lokasi folder untuk menggunakan kod ini.

Tukar semua formula kepada nilai

Gunakan kod ini apabila anda mempunyai lembaran kerja dengan banyak formula dan anda ingin menukar formula tersebut kepada nilai.

'Kod ini akan menukar semua formula kepada nilai Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Kod ini secara automatik mengenali sel yang digunakan dan menukarnya kepada nilai.

Lindungi/kunci sel dengan formula

Apabila anda mempunyai banyak pengiraan dan tidak mahu memadam atau menukarnya secara tidak sengaja, anda mungkin mahu mengunci sel dengan formula.

Berikut ialah kod yang akan mengunci semua sel dengan formula dan membiarkan semua sel lain tidak berkunci.

'Kod makro ini akan mengunci semua sel dengan formula Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

Tutorial berkaitan: Cara mengunci sel dalam Excel.

Lindungi semua helaian dalam buku kerja

Gunakan kod berikut untuk melindungi semua helaian dalam buku kerja sekaligus.

'Kod ini akan melindungi semua helaian dalam buku kerja Sub ProtectAllSheets() Dim ws As Worksheet Untuk Setiap ws Dalam Worksheets ws. Lindungi Seterusnya ws End Sub

Kod ini akan melalui semua helaian satu demi satu dan melindunginya.

Jika anda ingin menyahlindung semua helaian, gunakan ws.Unprotect dan bukannya ws.Protect dalam kod anda.

Sisipkan baris selepas setiap baris pilihan

Gunakan kod ini apabila anda ingin memasukkan baris kosong selepas setiap baris dalam julat yang dipilih.

'Kod ini akan memasukkan baris selepas setiap baris dalam pilihan Sub InsertAlternateRows() Dim rng As Julat Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Masukkan ActiveCell.Offset(2, 0).Pilih Seterusnya i End Sub

Sekali lagi, anda boleh mengubah suai kod ini untuk memasukkan lajur kosong selepas setiap lajur dalam julat yang dipilih.

Masukkan tarikh dan cap masa secara automatik dalam sel bersebelahan

Cap masa ialah apa yang anda gunakan apabila anda ingin menjejaki aktiviti.

Sebagai contoh, anda mungkin ingin menjejaki aktiviti seperti apabila perbelanjaan tertentu ditanggung, apabila invois jualan dibuat, apabila data dimasukkan ke dalam sel, apabila laporan dikemas kini kali terakhir dan sebagainya.

Gunakan kod ini untuk memasukkan cap tarikh dan masa dalam sel bersebelahan apabila menaip atau mengedit kandungan sedia ada.

'Kod ini akan memasukkan cap masa dalam sel bersebelahan Private Sub Worksheet_Change(ByVal Target As Range) Pada Ralat GoTo Handler Jika Target.Column = 1 Dan Target.Value <> "" Kemudian Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End Jika Pengendali: End Sub

Ambil perhatian bahawa anda perlu memasukkan kod ini dalam tetingkap kod lembaran kerja (bukan dalam tetingkap kod modul seperti yang telah kami lakukan dalam contoh makro Excel lain setakat ini).Untuk melakukan ini, dalam editor VB, klik dua kali pada nama helaian yang anda inginkan fungsi ini.Kemudian salin dan tampal kod ini ke dalam tetingkap kod helaian itu.

Selain itu, kod ini berfungsi apabila kemasukan data dilakukan dalam lajur A (perhatikan bahawa kod itu mempunyai Sasaran. Lajur = 1 baris).Anda boleh mengubahnya dengan sewajarnya.

Serlahkan baris ganti dalam pemilihan

Menyerlahkan baris berselang-seli boleh meningkatkan kebolehbacaan data anda dengan banyak.Ini boleh berguna apabila anda perlu mencetaknya dan menyemak imbas data.

Berikut ialah kod yang akan segera menyerlahkan baris ganti dalam pemilihan.

'Kod ini akan menyerlahkan baris ganti dalam pemilihan Sub HighlightAlternateRows() Malapkan Myrange Sebagai Julat Malapkan Myrow Sebagai Julat Set Myrange = Pemilihan Untuk Setiap Myrow Dalam Myrange.Rows Jika Myrow.Row Mod 2 = 1 Kemudian Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

Ambil perhatian bahawa saya menyatakan warna sebagai vbCyan dalam kod.Anda juga boleh menentukan warna lain (cth vbRed, vbGreen, vbBlue).

Serlahkan sel yang salah eja

Excel tidak mempunyai semakan ejaan dalam Word atau PowerPoint.Walaupun anda boleh menjalankan semakan ejaan dengan menekan F7, tiada petunjuk visual untuk ralat ejaan.

Gunakan kod ini untuk menyerlahkan semua sel yang salah eja dengan serta-merta.

'Kod ini akan menyerlahkan sel yang mempunyai perkataan yang salah eja Sub HighlightMisspelledCells() Dim cl As Julat Untuk Setiap cl Dalam ActiveSheet.UsedRange Jika Tidak Aplikasi.CheckSpelling(word:=cl.Teks) Kemudian cl.Interior.Color = vbRed End If Next cl Tamat Sub

Ambil perhatian bahawa sel yang diserlahkan ialah sel dengan teks yang Excel fikir salah ejaan.Dalam kebanyakan kes, ia juga menyerlahkan nama atau istilah jenama yang tidak difahaminya.

Muat semula semua jadual pangsi dalam buku kerja

Jika anda mempunyai berbilang jadual pangsi dalam buku kerja, anda boleh menggunakan kod ini untuk memuat semula kesemuanya sekali gus.

'Kod ini akan memuatkan semula semua Jadual Pangsi dalam Sub Buku Kerja RefreshAllPivotTables() Malapkan PT Sebagai Jadual Pangsi Untuk Setiap PT Dalam ActiveSheet.Jadual Pangsi PT.RefreshTable Seterusnya PT End Sub

Anda boleh membaca lebih lanjut tentang menyegarkan jadual pangsi di sini.

Tukar huruf besar sel yang dipilih kepada huruf besar

Walaupun Excel mempunyai formula untuk menukar kes huruf teks, ia membolehkan anda melakukan ini dalam set sel yang lain.

Gunakan kod ini untuk menukar huruf besar teks dalam teks yang dipilih dengan serta-merta.

'Kod ini akan menukar Pemilihan kepada Sub Huruf Besar ChangeCase() Dim Rng Sebagai Julat Untuk Setiap Rng Dalam Pilihan.Sel Jika Rng.HasFormula = Palsu Maka Rng.Value = UCase(Rng.Value) Tamat Jika Rng Seterusnya Sub Tamat

Ambil perhatian bahawa dalam kes ini saya menggunakan UCase untuk menetapkan huruf besar teks.Anda boleh menggunakan LCase untuk huruf kecil.

Serlahkan semua sel dengan ulasan

Gunakan kod berikut untuk menyerlahkan semua sel dengan ulasan di dalamnya.

'Kod ini akan menyerlahkan sel yang mempunyai ulasan` Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

Dalam kes ini, saya menggunakan vbBlue untuk memberikan sel warna biru.Anda boleh menukarnya kepada warna lain jika perlu.

Serlahkan sel kosong dengan VBA

Walaupun anda boleh menggunakan pemformatan bersyarat atau menggunakan dialog Pergi Ke Khas untuk menyerlahkan sel kosong, jika anda perlu melakukan ini dengan kerap, sebaiknya gunakan makro.

Setelah dibuat, anda boleh mempunyai makro ini dalam Bar Alat Akses Pantas atau simpan dalam buku kerja makro peribadi anda.

Berikut ialah kod makro VBA:

'Kod ini akan menyerlahkan semua sel kosong dalam set data Sub HighlightBlankCells() Malapkan Set Data sebagai Julat Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

Dalam kod ini, saya menyatakan sel kosong yang diserlahkan dengan warna merah.Anda boleh memilih warna lain seperti biru, kuning, cyan, dll.

Cara mengisih data mengikut satu lajur

Anda boleh menggunakan kod berikut untuk mengisih data mengikut lajur yang ditentukan.

Sub SortDataHeader() 
Julat("Julat Data"). Kunci Isih1:=Julat("A1"), Pesanan1:=xlMeningkat, Pengepala:=xlYa 
Akhir Sub

Ambil perhatian bahawa saya mencipta julat bernama yang dipanggil "DataRange" dan menggunakannya sebagai ganti rujukan sel.

Terdapat juga tiga parameter utama yang digunakan di sini:

• Kunci1 - Ini ialah kunci yang anda mahu mengisih set data.Dalam kod sampel di atas, data akan diisih berdasarkan nilai dalam lajur A.
• Pesanan1 – Di sini anda perlu menentukan sama ada anda ingin mengisih data dalam susunan menaik atau menurun.
• Pengepala - Di sini anda perlu menentukan sama ada data anda mempunyai pengepala atau tidak.

Baca lebih lanjut tentang cara mengisih data dalam Excel menggunakan VBA.

Cara mengisih data mengikut berbilang lajur

Katakan anda mempunyai set data yang kelihatan seperti ini:

Berikut ialah kod untuk mengisih data berdasarkan berbilang lajur:

Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Julat("A1"), Order:=xlAscending .SortFields.Add Key:=Julat("B1"), Order:=xlAscending .Julat Set("A1 :C13") .Tajuk = xlYa .Guna Tamat Dengan Sub Akhir

Ambil perhatian bahawa di sini saya telah menentukan untuk mengisih pada lajur A dahulu dan kemudian pada lajur B.

Output akan kelihatan seperti ini:

Bagaimana untuk mendapatkan hanya bahagian berangka daripada rentetan dalam Excel

Jika anda hanya ingin mengekstrak bahagian berangka atau bahagian teks daripada rentetan, anda boleh mencipta fungsi tersuai dalam VBA.

Anda kemudian boleh menggunakan fungsi VBA ini dalam lembaran kerja anda (sama seperti fungsi Excel biasa) dan ia akan mengekstrak hanya nombor atau bahagian teks daripada rentetan.

Seperti yang ditunjukkan di bawah:

Berikut ialah kod VBA yang akan mencipta fungsi yang mengekstrak bahagian berangka daripada rentetan:

'Kod VBA ini akan mencipta fungsi untuk mendapatkan bahagian berangka daripada rentetan Fungsi GetNumeric(CellRef Sebagai Rentetan) Malapkan StringLength Sebagai Integer StringLength = Len(CellRef) Untuk i = 1 Kepada StringLength Jika IsNumeric(Mid(CellRef, i, 1) ) Kemudian Result = Result & Mid(CellRef, i, 1) Seterusnya i GetNumeric = Result End Function

Anda perlu meletakkan kod dalam modul dan kemudian anda boleh menggunakan fungsi =GetNumeric dalam lembaran kerja.

Fungsi ini akan mengambil hanya satu parameter, rujukan sel sel untuk mendapatkan bahagian berangka.

Begitu juga, fungsi di bawah hanya akan mendapat bahagian teks daripada rentetan dalam Excel:

'Kod VBA ini akan mencipta fungsi untuk mendapatkan bahagian teks daripada rentetan. 1))) Kemudian Result = Result & Mid(CellRef, i, 1) Seterusnya i GetText = Result End Function

Jadi, ini ialah beberapa kod makro Excel berguna yang boleh anda gunakan dalam kerja harian anda untuk mengautomasikan tugas dan meningkatkan produktiviti anda.