Pengaturcaraan Makro dalam Excel: Panduan Lengkap Langkah demi Langkah

Informatec Digital » Pengaturcaraan » Pengaturcaraan Makro dalam Excel: Panduan Langkah demi Langkah Lengkap

Jika anda mendapati diri anda melakukan tugas yang sama dalam Excel berulang kali, makro adalah sahabat baik anda untuk menjimatkan masa. Dengannya, anda boleh mengautomasikan keseluruhan urutan tindakan (ketahui lebih lanjut tentang pengaturcaraan VBA ), daripada pemformatan kepada mengimport data atau melakukan pengiraan, dan mencetuskannya bila-bila masa anda mahu dengan satu klik atau pintasan papan kekunci.

Apabila anda mencipta makro, Excel merekodkan klik dan ketukan kekunci anda dan melaksanakannya semula dengan tepat. Anda kemudian boleh mengubah suai kod untuk memperhalusi pelaksanaan dan merujuk sumber untuk Excel . Adalah perkara biasa untuk bermula dengan perakam makro dan kemudian memperhalusinya dengan VBA, mencapai kawalan, fleksibiliti dan sifar ralat yang lebih baik disebabkan oleh kecuaian.

Apakah sebenarnya makro dalam Excel?

Makro ialah senarai arahan yang dilaksanakan oleh Excel dalam susunan yang ditetapkan . Ia boleh semudah menggunakan gaya peratusan atau serumit menyediakan laporan dengan jadual pangsi , memformatkannya, mencipta lembaran kerja dan menghantar e-mel dalam Outlook. Makro disimpan dalam buku kerja anda dan boleh dijalankan bila-bila masa diperlukan.

Anda tidak perlu tahu cara memprogram untuk bermula: perakam menterjemahkan tindakan anda ke dalam kod VBA. Walaupun begitu, sedikit pengetahuan VBA akan membolehkan anda mencipta fungsi, borang dan juga aplikasi mini tersuai dalam Excel.

Mengapa menggunakan makro? Kelebihan sebenar

Tujuan utama makro adalah untuk mengelakkan tugasan berulang dan meminimumkan ralat . Jika anda menyediakan laporan yang sama setiap bulan, makro akan menyelamatkan anda daripada langkah mekanikal dan membolehkan anda fokus pada perkara yang penting.

Manfaatnya termasuk penjimatan masa, pelaksanaan yang konsisten (tanpa langkah yang terlepas), dan keupayaan untuk mencipta pintasan dan butang untuk mempercepatkan proses . Jika anda hanya menggunakan Excel sekali-sekala, ia mungkin tidak berbaloi; tetapi jika anda menggunakannya setiap hari, anda akan melihat perbezaannya dari hari pertama.

Dayakan tab Pembangun (Windows dan Mac)

Tab Pembangun disembunyikan secara lalai. Dalam Windows, klik kanan reben dan pilih "Sesuaikan Reben." Tandakan "Pembangun" dan klik OK. Selepas itu, anda akan melihat tab Pembangun di sebelah Paparan.

Pada Mac, pergi ke Excel > Keutamaan > Bar Alat & Reben. Di bawah "Tab Utama", pilih "Pembangun" dan simpan. Ini akan memberi anda akses kepada perakam, VBA dan kawalan . Jika anda baharu menggunakan Excel, semak panduan asas Excel untuk membiasakan diri dengan reben dan pilihannya.

Cara membuat makro dengan Perakam

Perakam ini sesuai untuk bermula. Dalam Pembangun > Makro Rakam (atau Alt+T+M+R pada Windows), tetapkan nama deskriptif. Ingat: nama mesti bermula dengan huruf, tanpa jarak atau simbol.

Pilih tempat untuk menyimpan: "Buku kerja ini" akan berfungsi dalam kebanyakan kes. Jika anda mahu ia sentiasa tersedia, pilih "Buku Kerja Makro Peribadi" dan Excel akan mencipta/menyimpannya dalam Personal.xlsb , yang akan dibuka secara tersembunyi dengan Excel.

Sebagai pilihan, tambahkan pintasan. Untuk mengelakkan konflik (contohnya, kehilangan Ctrl+Z), disyorkan untuk menggunakan pintasan dengan Ctrl+Shift. Anda juga boleh menulis penerangan yang ringkas dan jelas (sangat berguna apabila anda mempunyai banyak makro).

Klik OK dan lakukan tindakan yang hendak dirakam. Contohnya: pilih A1 dan gunakan format "Peratusan". Elakkan klik atau perubahan pilihan yang tidak perlu yang bukan sebahagian daripada proses yang dimaksudkan, kerana perakam merekodkan hampir semua perkara.

Setelah selesai, hentikan rakaman. Untuk menjalankannya, pergi ke Pembangun > Makro (atau Alt+F8), pilih makro anda dan klik Jalankan. Anda akan melihat Excel mengulangi langkah-langkah dalam susunan yang sama.

Jalankan, edit dan urus makro anda

Daripada Pembangun > Makro (Alt+F8), anda boleh menjalankan, mengedit atau memadam makro. Jika anda memilih Edit, Editor Visual Basic akan dibuka dengan kod yang dirakam, yang boleh anda bersihkan dan optimumkan (ia selalunya merangkumi langkah-langkah yang berlebihan).

Menetapkan makro pada butang atau bentuk adalah sangat mudah: masukkan bentuk, klik kanan > Tetapkan Makro, pilih makro dan anda selesai. Apabila anda mengklik bentuk, ia akan berjalan serta-merta.

Untuk memastikan ia mudah didapati pada Bar Alat Akses Pantas, buka menu bar alat (anak panah ke bawah) > Lebih Banyak Perintah > di bawah "Perintah tersedia dalam", pilih "Makro", tambahkan milik anda dan tukar ikon jika anda mahu. Kini anda akan mempunyai butang kekal walaupun anda tidak berada dalam mod Pembangun.

Jika anda perlu menggunakan semula makro dalam buku kerja lain, anda boleh menyalin modul tersebut daripada Editor Visual Basic. Buka VBE (Alt+F11), seret modul ke buku kerja terbuka yang lain atau gunakan fail .bas Export/Import.

Pengaturcaraan makro dalam Excel dengan Editor Visual Basic (VBA).

Untuk melangkaui perakam, buka Editor dengan Alt+F11. Sisip Modul (Sisip > Modul) dan cipta subrutin pertama anda. Corak asas ialah Sub Name() … End Sub.

Contohnya, untuk memaparkan mesej dan menulis nilai dalam sel A1, anda boleh mulakan dengan sesuatu yang mudah. ​​Dengan cara ini anda memahami struktur dan cara memanggil makro daripada Excel (Alt+F8) atau menetapkannya kepada butang, dengan sangat sedikit baris kod :

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Satu lagi contoh ringkas untuk menggunakan gaya peratusan pada pilihan semasa. Makro ini mereplikasi apa yang anda lakukan dengan perakam , tetapi ditaip secara langsung:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

Gabungan ideal ialah: rakam untuk mempercepatkan sesuatu, kemudian buka VBE untuk membersihkan langkah yang tidak perlu, menambah pembolehubah, syarat atau gelung. Dengan cara ini, anda menukar rakaman "literal" menjadi makro yang mantap dan boleh diguna semula.

Rujukan mutlak lwn. relatif

Secara lalai, perakam mencipta makro dalam mod mutlak: ia sentiasa bertindak pada sel yang sama (contohnya, B3) tanpa mengira di mana anda berada semasa menjalankannya. Jika data berubah kedudukan dalam proses anda, ini mungkin bukan yang anda perlukan.

Dengan "Gunakan rujukan relatif", makro berfungsi relatif kepada sel aktif apabila pelaksanaan bermula. Contohnya, jika anda mula merakam dalam sel B3 dan menaip "Nama Pertama", "Nama Akhir", dsb., bergerak ke kanan, apabila dijalankan dari sel C4, ia akan menaip maklumat yang sama relatif kepada sel C4, D4, E4, dsb.

Contoh tipikal: menambah tajuk pada jadual yang kadangkala bermula dalam sel B3 dan kadangkala dalam sel C5. Dengan mod mutlak, ia akan sentiasa menulis dalam sel B3. Dengan mod relatif, ia akan menyesuaikan diri dengan sel permulaan yang dipilih.

Kesilapan biasa semasa merakam proses

Satu kelemahan biasa ialah merekodkan formula "seret" untuk, katakan, tiga baris, dan berfikir ia akan berfungsi untuk jadual yang lebih panjang. Perakam akan mendaftarkan sesuatu yang bersamaan dengan " isi tiga sel ke bawah ," bukan "hingga ke hujung jadual."

Penyelesaiannya adalah dengan mengubah pendekatan anda: gunakan julat dinamik (contohnya, mengesan baris terakhir), gelung atau kaedah CurrentRegion atau laraskan makro dalam VBA selepas merakamnya supaya ia diskalakan kepada sebarang saiz.

Amalan baik semasa merakam

Sebelum merakam, jalankan proses tersebut secara manual beberapa kali untuk memperhalusinya. Buat rakaman yang pendek dan khusus dan bukannya satu rakaman yang besar; ini menjadikannya lebih mudah untuk diselenggara dan digabungkan.

Elakkan klik yang tidak perlu, menatal reben dan pilihan yang tidak menyumbang apa-apa. Pastikan nama dan perihalan makro jelas supaya anda atau mana-mana rakan sekerja dapat mengenal pasti tujuannya dengan serta-merta.

Jalankan makro dalam pelbagai cara

Selain kotak dialog Makro (Alt+F8), anda boleh melancarkan makro menggunakan bentuk, butang pada Bar Alat Akses Pantas atau dengan membuka buku kerja. Anda juga boleh meminta Excel menjalankan makro daripada aplikasi Office yang lain, yang membolehkan anda mengautomasikan tugas merentas aplikasi (contohnya, mengemas kini jadual dan menghantar e-mel melalui Outlook).

Fikirkan dengan teliti tentang pintasan papan kekunci: jika anda menetapkan Ctrl+Z pada makro, anda akan kehilangan keupayaan untuk Buat Asal semasa buku kerja dibuka. Oleh itu, adalah disyorkan untuk menggunakan kombinasi Ctrl+Shift untuk mengelakkan konflik.

Menyimpan buku kerja dengan makro

Apabila fail mengandungi kod VBA, simpannya sebagai fail .xlsm supaya kod tersebut dikekalkan dan boleh dijalankan. Menyimpan sebagai fail .xlsx akan memusnahkan projek VBA . Jika anda mahukan makro global, gunakan Buku Kerja Makro Peribadi (Personal.xlsb).

Keselamatan dan membolehkan makro

Makro mungkin disekat bergantung pada tetapan keselamatan anda. Semak Pusat Amanah untuk mendayakannya secara selektif dan jangan sekali-kali mendayakan makro daripada sumber yang tidak diketahui. Memahami tahap keselamatan anda membantu anda mengimbangi perlindungan dan produktiviti.

Tetapkan makro kepada butang, bentuk dan kawalan

Untuk membolehkan pengguna menjalankan makro secara automatik, tetapkan makro pada butang, bentuk atau kawalan. Klik kanan objek > Tetapkan Makro > pilih. Anda juga boleh meletakkan ikon pada reben atau Bar Alat Akses Pantas dan pautkannya ke makro.

Jika anda menggunakan borang atau kawalan ActiveX, anda boleh menetapkan makro atau prosedur peristiwa (contohnya, semasa klik) kepadanya. Ini membolehkan antara muka yang lebih mesra pengguna dan proses berpandu.

Contoh makro berguna (VBA)

Contoh-contoh ini menggambarkan tugasan biasa. Anda boleh menampalnya ke dalam Modul (Alt+F11 > Sisip > Modul) dan menyesuaikannya. Ingat untuk menandatangani buku kerja atau melaraskan tetapan keselamatan jika organisasi anda memerlukannya supaya anda boleh menjalankannya tanpa amaran.

Salin kandungan yang dipilih ke lokasi lain pada helaian yang sama: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Cetak helaian aktif: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Simpan fail sebagai buku kerja yang didayakan makro (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Gunakan pemformatan pada julat (laraskan julat mengikut keperluan anda): Dengan Julat(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Cari nilai dalam julat dan paparkan padanan pertama: Julat(…).Cari

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Kes praktikal: memformat sebagai peratusan

Rakaman: Mulakan rakaman, namakannya, pilih tempat untuk menyimpannya dan tetapkan pintasan dan perihalan secara pilihan. Pilih sel dan gunakan "Peratus" daripada tab Laman Utama. Hentikan rakaman dan kemudian jalankan semula dengan Alt+F8. Jika anda ingin memainkannya semula dengan kod minimalis, gunakan Selection.Style = "Peratus" dalam VBA.

Petua: Semasa rakaman, elakkan menukar sel secara tidak perlu. Pergerakan bolak-balik ini dirakam dan boleh memperlahankan serta merosakkan makro.

Automatikkan tugasan yang panjang: makro kecil adalah lebih baik

Jika anda mempunyai proses dengan banyak langkah, pertimbangkan untuk membahagikannya kepada beberapa makro yang lebih kecil dan memanggilnya secara berurutan. Ini menjadikan penyelenggaraan lebih mudah, membolehkan anda menggunakan semula blok dan memudahkan penyahpepijatan.

Contohnya, satu makro untuk menyediakan data, satu lagi untuk memformat, satu lagi untuk menjana cetakan dan satu lagi untuk dieksport ke PDF atau dihantar melalui e-mel . Anda boleh memanggilnya satu persatu atau semua sekaligus.

Edit dan bersihkan kod yang direkodkan

Perakam merakam "semuanya": pilihan, klik dan perubahan tab. Apabila VBE dibuka, ia mengalih keluar pilihan yang tidak perlu (Pilih/Pilihan) dengan menggantikannya dengan rujukan langsung (contohnya, Julat("A1").Value = ... ) dan mengumpulkan format ke dalam blok Dengan/Akhir Dengan.

Di samping itu, ia menambah pembolehubah dan pengendalian ralat asas. Beberapa pernyataan jika dan semakan mudah menghalang ralat apabila julat kosong atau pilihan tidak seperti yang dijangkakan.

Soalan yang kerap ditanya

Bagaimanakah saya mencipta UserForm dalam Excel? Buka Editor Visual Basic (Alt+F11), pergi ke Sisip > UserForm dan tambahkan kawalan (kotak teks, butang, dll.). Anda boleh memaparkannya menggunakan makro.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Dari situ, tetapkan prosedur kepada peristiwa kawalan (contohnya, klik butang) untuk melaksanakan logik yang anda perlukan.

Bagaimanakah saya melindungi/menyahlindungi helaian dengan makro? Gunakan rutin ini dengan menukar kata laluan seperti yang dikehendaki. Ia membolehkan anda mengaktifkan atau menyahaktifkan perlindungan dengan satu klik.

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Bagaimanakah saya memadam makro? Pergi ke Pembangun > Makro, pilih makro dan tekan Padam. Jika anda ingin memadam semua kod daripada modul, buka VBE (Alt+F11), cari modul dalam projek, klik kanan dan pilih Alih Keluar Modul (anda boleh mengeksportnya terlebih dahulu jika anda ingin menyimpannya).

Satu petua praktikal terakhir: Jika anda memformat laporan bulanan di mana pelanggan dengan baki tertunggak harus diserlahkan dengan warna merah dan tebal, rekodkan urutan sekali, laraskan kod VBA untuk digunakan pada julat yang betul (menggunakan pengesanan atau penapis baris terakhir) dan simpan makro dalam buku kerja makro peribadi anda. Lain kali, pintasan atau butang mudah sudah memadai untuk menggunakan pemformatan serta-merta, tanpa cacat cela, dalam beberapa saat.