Pemrograman Makro di Excel: Panduan Lengkap Langkah demi Langkah

Informatec Digital » pemrograman » Pemrograman Makro di Excel: Panduan Lengkap Langkah demi Langkah

Jika Anda sering melakukan tugas yang sama berulang kali di Excel, makro adalah solusi terbaik untuk menghemat waktu. Dengan makro, Anda dapat mengotomatiskan seluruh rangkaian tindakan (pelajari lebih lanjut tentang pemrograman VBA ), mulai dari pemformatan hingga impor data atau melakukan perhitungan, dan memicunya kapan pun Anda mau hanya dengan sekali klik atau menggunakan pintasan keyboard.

Saat Anda membuat makro, Excel merekam klik dan penekanan tombol Anda dan mengeksekusinya kembali dengan tepat. Anda kemudian dapat mengubah kode untuk menyempurnakan eksekusi dan berkonsultasi dengan sumber daya untuk Excel . Biasanya, langkah pertama adalah menggunakan perekam makro dan kemudian menyempurnakannya dengan VBA, sehingga menghasilkan kontrol, fleksibilitas, dan nol kesalahan akibat kecerobohan.

Apa sebenarnya makro di Excel?

Makro adalah daftar instruksi yang dijalankan Excel dalam urutan yang telah ditentukan . Makro bisa sesederhana menerapkan gaya persentase atau serumit menyiapkan laporan dengan tabel pivot , memformatnya, membuat lembar kerja, dan mengirim email di Outlook. Makro disimpan dalam buku kerja Anda dan dapat dijalankan kapan pun dibutuhkan.

Anda tidak perlu tahu cara memprogram untuk memulai: perekam menerjemahkan tindakan Anda ke dalam kode VBA. Meskipun demikian, sedikit pengetahuan VBA akan memungkinkan Anda untuk membuat fungsi khusus, formulir, dan bahkan aplikasi mini di dalam Excel.

Mengapa menggunakan makro? Keuntungan nyata

Tujuan utama makro adalah untuk menghindari tugas berulang dan meminimalkan kesalahan . Jika Anda menyiapkan laporan yang sama setiap bulan, makro akan menyelamatkan Anda dari langkah-langkah mekanis dan memungkinkan Anda untuk fokus pada hal yang penting.

Manfaatnya meliputi penghematan waktu, eksekusi yang konsisten (tanpa langkah yang terlewat), dan kemampuan untuk membuat pintasan dan tombol untuk mempercepat proses . Jika Anda hanya menggunakan Excel sesekali, mungkin tidak sepadan; tetapi jika Anda menggunakannya setiap hari, Anda akan merasakan perbedaannya sejak hari pertama.

Aktifkan tab Pengembang (Windows dan Mac)

Tab Pengembang disembunyikan secara default. Di Windows, klik kanan pita (ribbon) dan pilih "Sesuaikan Pita". Centang "Pengembang" dan klik OK. Setelah itu, Anda akan melihat tab Pengembang di sebelah Tampilan.

Di Mac, buka Excel > Preferensi > Bilah Alat & Pita. Di bawah "Tab Utama," pilih "Pengembang" dan simpan. Ini akan memberi Anda akses ke perekam, VBA, dan kontrol . Jika Anda baru mengenal Excel, tinjau panduan dasar Excel untuk membiasakan diri dengan pita dan opsi-opsinya.

Cara membuat makro dengan Perekam

Perekam ini sangat cocok untuk memulai. Di Pengembang > Rekam Makro (atau Alt+T+M+R di Windows), berikan nama yang deskriptif. Ingat: nama harus diawali dengan huruf, tanpa spasi atau simbol.

Pilih tempat menyimpan: "Buku kerja ini" akan berfungsi dalam sebagian besar kasus. Jika Anda ingin agar selalu tersedia, pilih "Buku Kerja Makro Pribadi" dan Excel akan membuat/menyimpannya di Personal.xlsb , yang akan terbuka tersembunyi saat Excel dijalankan.

Secara opsional, tambahkan pintasan. Untuk menghindari konflik (misalnya, kehilangan Ctrl+Z), disarankan untuk menggunakan pintasan dengan Ctrl+Shift. Anda juga dapat menulis deskripsi singkat dan jelas (sangat berguna jika Anda memiliki banyak makro).

Klik OK dan lakukan tindakan yang akan direkam. Misalnya: pilih A1 dan terapkan format "Persentase". Hindari klik atau perubahan pilihan yang tidak perlu dan bukan bagian dari proses yang dimaksud, karena perekam merekam hampir semuanya.

Setelah selesai, hentikan perekaman. Untuk menjalankannya, buka Pengembang > Makro (atau Alt+F8), pilih makro Anda, dan klik Jalankan. Anda akan melihat Excel mengulangi langkah-langkah tersebut dalam urutan yang sama persis.

Jalankan, edit, dan kelola makro Anda

Dari Developer > Macros (Alt+F8) Anda dapat menjalankan, mengedit, atau menghapus makro. Jika Anda memilih Edit, Editor Visual Basic akan terbuka dengan kode yang direkam, yang dapat Anda bersihkan dan optimalkan (seringkali termasuk langkah-langkah yang berlebihan).

Memberikan makro pada tombol atau bentuk sangat mudah: sisipkan bentuk, klik kanan > Tetapkan Makro, pilih makro, dan selesai. Saat Anda mengklik bentuk tersebut, makro akan langsung berjalan.

Untuk membuatnya selalu tersedia di Toolbar Akses Cepat, buka menu toolbar (panah bawah) > Perintah Lainnya > di bawah "Perintah yang tersedia di", pilih "Makro", tambahkan makro Anda, dan ubah ikon jika Anda mau. Sekarang Anda akan memiliki tombol permanen bahkan saat Anda tidak dalam mode Pengembang.

Jika Anda perlu menggunakan kembali makro di buku kerja lain, Anda dapat menyalin modul dari Editor Visual Basic. Buka VBE (Alt+F11), seret modul ke buku kerja lain yang terbuka, atau gunakan Ekspor/Impor file .bas.

Memprogram makro di Excel dengan Editor Visual Basic (VBA)

Untuk melangkah lebih jauh dari perekam, buka Editor dengan Alt+F11. Sisipkan Modul (Sisipkan > Modul) dan buat subrutin pertama Anda. Pola dasarnya adalah Sub Nama() … End Sub.

Sebagai contoh, untuk menampilkan pesan dan menulis nilai di sel A1, Anda bisa mulai dengan sesuatu yang sederhana. Dengan cara ini, Anda akan memahami strukturnya dan cara memanggil makro dari Excel (Alt+F8) atau menetapkannya ke tombol, hanya dengan beberapa baris kode :

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

Contoh singkat lain menerapkan gaya persentase pada pilihan saat ini. Makro ini meniru apa yang Anda lakukan dengan perekam , tetapi diketik langsung:

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

Kombinasi idealnya adalah: rekam untuk mempercepat proses, lalu buka VBE untuk membersihkan langkah-langkah yang tidak perlu, menambahkan variabel, kondisi, atau perulangan. Dengan cara ini, Anda mengubah rekaman "harfiah" menjadi makro yang kuat dan dapat digunakan kembali.

Referensi absolut vs. referensi relatif

Secara default, perekam membuat makro dalam mode absolut: ia selalu bertindak pada sel yang sama (misalnya, B3) terlepas dari posisi Anda saat menjalankannya. Jika posisi data berubah dalam proses Anda, ini mungkin bukan yang Anda butuhkan.

Dengan opsi "Gunakan referensi relatif", makro akan bekerja relatif terhadap sel aktif saat eksekusi dimulai. Misalnya, jika Anda mulai merekam di sel B3 dan mengetik "Nama Depan", "Nama Belakang", dll., lalu bergerak ke kanan, saat dijalankan dari sel C4, makro akan mengetik informasi yang sama relatif terhadap sel C4, D4, E4, dan seterusnya.

Contoh tipikal: menambahkan judul ke tabel yang terkadang dimulai di sel B3 dan terkadang di sel C5. Dengan mode absolut, judul akan selalu ditulis di sel B3. Dengan mode relatif, judul akan menyesuaikan dengan sel awal yang dipilih.

Kesalahan umum saat merekam proses

Kesalahan umum adalah merekam rumus "drag" untuk, misalnya, tiga baris, dan mengira itu akan berfungsi untuk tabel yang lebih panjang. Perekam akan mencatat sesuatu yang setara dengan " isi tiga sel ke bawah ," bukan "sampai akhir tabel."

Solusinya adalah mengubah pendekatan Anda: gunakan rentang dinamis (misalnya, mendeteksi baris terakhir), perulangan, atau metode CurrentRegion, atau sesuaikan makro di VBA setelah merekamnya sehingga dapat diskalakan ke ukuran apa pun.

Praktik baik saat merekam

Sebelum merekam, jalankan prosesnya secara manual beberapa kali untuk menyempurnakannya. Buat rekaman pendek dan spesifik, bukan satu rekaman besar; ini akan memudahkan pemeliharaan dan penggabungan rekaman.

Hindari klik yang tidak perlu, menggulir melalui pita menu, dan pilihan yang tidak memberikan kontribusi apa pun. Jaga agar nama dan deskripsi makro tetap jelas sehingga Anda atau kolega Anda dapat langsung mengidentifikasi tujuannya.

Jalankan makro dengan berbagai cara

Selain kotak dialog Makro (Alt+F8), Anda dapat menjalankan makro menggunakan bentuk, tombol pada Toolbar Akses Cepat, atau dengan membuka buku kerja. Anda bahkan dapat meminta Excel menjalankan makro dari aplikasi Office lain, memungkinkan Anda untuk mengotomatiskan tugas lintas aplikasi (misalnya, memperbarui tabel dan mengirim email melalui Outlook).

Pertimbangkan dengan cermat pintasan keyboard: jika Anda menetapkan Ctrl+Z ke makro, Anda akan kehilangan kemampuan untuk membatalkan (Undo) saat buku kerja terbuka. Oleh karena itu, disarankan untuk menggunakan kombinasi Ctrl+Shift untuk menghindari konflik.

Menyimpan buku kerja dengan makro

Saat sebuah file berisi kode VBA, simpanlah sebagai file .xlsm agar kode tersebut tetap tersimpan dan dapat dijalankan. Menyimpan sebagai file .xlsx akan menghapus proyek VBA . Jika Anda menginginkan makro global, gunakan Buku Kerja Makro Pribadi (Personal.xlsb).

Keamanan dan pengaktifan makro

Makro mungkin diblokir tergantung pada pengaturan keamanan Anda. Periksa Pusat Kepercayaan untuk mengaktifkannya secara selektif, dan jangan pernah mengaktifkan makro dari sumber yang tidak dikenal. Memahami tingkat keamanan Anda membantu Anda menyeimbangkan perlindungan dan produktivitas.

Tetapkan makro ke tombol, bentuk, dan kontrol

Untuk memungkinkan pengguna menjalankan makro secara otomatis, tetapkan makro ke tombol, bentuk, atau kontrol. Klik kanan objek > Tetapkan Makro > pilih. Anda juga dapat menempatkan ikon pada pita atau Bilah Alat Akses Cepat dan menautkannya ke makro.

Jika Anda menggunakan formulir atau kontrol ActiveX, Anda dapat menetapkan makro atau prosedur kejadian (misalnya, saat diklik) ke dalamnya. Hal ini memungkinkan antarmuka yang lebih ramah pengguna dan proses yang terarah.

Contoh makro yang berguna (VBA)

Contoh-contoh ini mengilustrasikan tugas-tugas umum. Anda dapat menempelkannya ke dalam Modul (Alt+F11 > Sisipkan > Modul) dan menyesuaikannya. Ingatlah untuk menandatangani buku kerja atau menyesuaikan pengaturan keamanan jika organisasi Anda memerlukannya agar Anda dapat menjalankannya tanpa peringatan.

Salin konten yang dipilih ke lokasi lain pada lembar yang sama: Pilihan.Salin Tujuan

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 lembar aktif: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Simpan file sebagai buku kerja yang mendukung 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

Terapkan pemformatan ke suatu rentang (sesuaikan rentang sesuai kebutuhan Anda): Dengan Rentang(…)

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 suatu rentang dan tampilkan nilai yang cocok pertama: Range(…).Find

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

Kasus praktis: memformat sebagai persentase

Perekaman: Mulai merekam, beri nama, pilih tempat untuk menyimpannya, dan secara opsional tetapkan pintasan dan deskripsi. Pilih sel dan terapkan "Persen" dari tab Beranda. Hentikan perekaman lalu jalankan kembali dengan Alt+F8. Jika Anda ingin memutarnya kembali dengan kode minimalis, gunakan Selection.Style = "Persen" di VBA.

Tip: Selama perekaman, hindari berpindah sel secara tidak perlu. Gerakan bolak-balik ini akan terekam dan dapat memperlambat serta merusak makro.

Otomatiskan tugas yang panjang: makro kecil lebih baik

Jika Anda memiliki proses dengan banyak langkah, pertimbangkan untuk membaginya menjadi beberapa makro yang lebih kecil dan memanggilnya secara berurutan. Ini akan mempermudah pemeliharaan, memungkinkan Anda untuk menggunakan kembali blok kode , dan menyederhanakan proses debugging.

Sebagai contoh, satu makro untuk mempersiapkan data, satu lagi untuk memformat, satu lagi untuk menghasilkan cetakan, dan satu lagi untuk mengekspor ke PDF atau mengirim melalui email . Anda dapat memanggilnya satu per satu atau semuanya sekaligus.

Edit dan bersihkan kode yang direkam

Perekam menangkap "semuanya": pilihan, klik, dan perubahan tab. Saat VBE dibuka, ia menghapus pilihan yang tidak perlu (Pilih/Pilihan) dengan menggantinya dengan referensi langsung (misalnya, Rentang("A1").Nilai = ... ) dan mengelompokkan format ke dalam blok Dengan/Akhiri Dengan.

Selain itu, ia menambahkan variabel dan penanganan kesalahan dasar. Beberapa pernyataan if dan pemeriksaan sederhana mencegah kesalahan ketika rentang kosong atau pilihan tidak sesuai harapan.

Pertanyaan yang sering diajukan

Bagaimana cara membuat UserForm di Excel? Buka Editor Visual Basic (Alt+F11), masuk ke Sisipkan > UserForm, dan tambahkan kontrol (kotak teks, tombol, dll.). Anda dapat menampilkannya menggunakan makro.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Dari situ, tetapkan prosedur ke kejadian kontrol (misalnya, klik tombol) untuk menjalankan logika yang Anda butuhkan.

Bagaimana cara melindungi/melepas perlindungan lembar kerja dengan makro? Gunakan rutinitas ini dengan mengubah kata sandi sesuai keinginan. Rutinitas ini memungkinkan Anda untuk mengaktifkan atau menonaktifkan perlindungan hanya dengan satu klik.

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

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

Bagaimana cara menghapus makro? Buka Developer > Macros, pilih makro, dan tekan Delete. Jika Anda ingin menghapus semua kode dari sebuah modul, buka VBE (Alt+F11), temukan modul di proyek, klik kanan, dan pilih Remove Module (Anda dapat mengekspornya terlebih dahulu jika ingin menyimpannya).

Satu tips praktis terakhir: Jika Anda memformat laporan bulanan di mana pelanggan dengan saldo terutang harus disorot dengan warna merah dan tebal, rekam urutan tersebut sekali, sesuaikan kode VBA agar berlaku untuk rentang yang benar (menggunakan deteksi baris terakhir atau filter), dan simpan makro di buku kerja makro pribadi Anda. Lain kali, pintasan atau tombol sederhana akan cukup untuk menerapkan pemformatan secara instan, tanpa cela, dalam hitungan detik.