Daftar Isi:
Video: Membuat Dashboard Menggunakan Microsoft Excel 2024
Pernahkah Anda mencoba memahami buku kerja Excel yang asing (dan rumit)? Akan sangat membantu jika melihat peta yang memberikan gambaran umum tentang sel yang mengandung konstanta dan sel mana yang mengandung nilai.
Anda dapat membuat utilitas VBA yang menghasilkan peta lembar kerja aktif. Peta dihasilkan pada lembar kerja baru, dan terdiri dari sel kode warna yang memungkinkan Anda dengan cepat mengidentifikasi nilai, teks, dan formula.
Di bawah ini, Anda melihat contoh peta semacam itu. Sel yang mengandung teks berwarna hijau, yang mengandung nilai numerik berwarna kuning, dan sel yang mengandung formula berwarna merah. Peta semacam itu dapat membantu Anda menemukan potensi kesalahan. Misalnya, jika satu formula dalam blok formula telah ditimpa oleh sebuah nilai, sel itu akan menonjol dalam tampilan peta (seperti pada sel Q11 pada contohnya).
Kode VBA QuickMap
Prosedur VBA yang menghasilkan peta lembar kerja tercantum di bawah ini. Jika Anda ingin menggunakan utilitas ini, cukup salin kode dan tempelkan ke modul VBA. Kemudian, aktifkan lembar kerja dan jalankan subrutin QuickMap.
Sub QuickMap () Dim FormulaCells Sebagai Variant Dim TextCells Sebagai Variant Dim NumberCells Sebagai Variant Dim Area As Range Jika Worksheet TypeName (ActiveSheet) "Then Exit Sub 'Buat variabel objek untuk subset sel Pada Error Resume Next Set FormulaCells = Range ("A1"). SpecialCells _ (xlFormulas, xlNumbers + xlTextValues + xlLogical) Set TextCells = Range ("A1"). SpecialCells (xlConstants, xlTextValues) Set NumberCells = Range ("A1"). SpecialCells (xlConstants, xlNumbers) Pada Error GoTo 0 'Tambahkan lembar baru dan formatlah Sheets. Tambahkan Dengan Sel. ColumnWidth = 2. Font. Ukuran = 8. HorizontalAlignment = xlCenter End With Application. ScreenUpdating = False 'Lakukan sel-sel formula Jika Tidak IsEmpty (FormulaCells) Kemudian Untuk Setiap Area Di FormulaCells. Area Dengan ActiveSheet. Rentang (Area. Alamat). Nilai = "F". Pedalaman. ColorIndex = 3 End With Next Area End Jika 'Lakukan sel teks Jika Tidak IsEmpty (TextCells) Kemudian Untuk Setiap Area Di TextCells. Area Dengan ActiveSheet. Rentang (Area. Alamat). Nilai = "T". Pedalaman. ColorIndex = 4 Akhir Dengan Akhir Area Berikutnya Jika 'Lakukan sel numerik Jika Tidak IsEmpty (Nomor Panggil) Kemudian Untuk Setiap Area di Nomor Telepon. Area Dengan ActiveSheet. Rentang (Area. Alamat). Nilai = "N". Pedalaman. ColorIndex = 6 Akhir Dengan Akhir Area Berikutnya Jika End Sub
Cara kerjanya
Prosedur pertama memeriksa untuk memastikan lembar aktif adalah lembar kerja. Jika tidak, ada jalan keluar yang cepat tanpa tindakan lebih lanjut. Bila lembar aktif adalah lembar kerja, prosedur ini menciptakan tiga variabel objek dengan menggunakan metode SpecialCells untuk mengidentifikasi berbagai jenis sel.Metode SpecialCells sangat berguna. Jika Anda tidak mengenalnya, lihat di file bantuan online Excel. Perhatikan penggunaan On Error Resume Next. Ini untuk menghindari kesalahan yang terjadi jika tidak ada sel yang memenuhi syarat - misalnya, jika lembar kerja tidak memiliki rumus.
Selanjutnya, prosedur menambahkan lembar kerja baru, mengurangi lebar sel, dan menyetel kesejajaran horizontal ke tengah. Langkah ini bersifat kosmetik. Sub kemudian mematikan pemutaran layar untuk mempercepatnya sedikit.
Tiga blok kode berikutnya memproses sel. Jika tidak ada sel yang lolos, variabel objeknya kosong, jadi sub tes untuk ini. Kemudian, loop rutin melalui masing-masing Area pada objek Range dan memformat sel. Anda dapat dengan mudah menyesuaikan bagian subrutin ini untuk menerapkan format yang berbeda.
Simak Pak Utilitas Pakar untuk mendapatkan versi utilitas yang jauh lebih canggih ini.
