Masukkan Waktu Tanpa Titik Dua - Tips Excel

Pertanyaan Excel minggu ini datang dari John yang ditempatkan di Okinawa.

Saya sedang membangun spreadsheet Excel untuk mencerminkan keberangkatan dan kedatangan. Pada dasarnya akan ada tiga sel: Waktu Keberangkatan Aktual, Perkiraan Waktu Perjalanan, dan Perkiraan Waktu Tiba. Saya ingin orang tersebut dapat memasukkan (misalnya) 2345 dan membuat sel secara otomatis memformat tampilan untuk menunjukkan 23:45. Yang saya dapatkan adalah 0:00, terlepas dari rumus atau pemformatannya. Dan, kalkulasi tidak akan menampilkan apapun kecuali 0:00 jika pengguna gagal menggeser tombol dan titik dua. Saya tahu tampaknya mudah untuk melakukannya, namun, setiap detik yang dihemat sangat berarti, terutama saat memasukkan data serupa berulang kali di Excel.

Untuk membuat ini bekerja, Anda perlu menggunakan penangan kejadian. Penangan kejadian baru di Excel 97 dan dibahas kembali di Menjalankan Makro Setiap Saat Nilai Sel Berubah di Excel. Namun, kembali ke tip itu, pengendali kejadian menerapkan format yang berbeda ke sel tertentu. Aplikasi ini sedikit berbeda, jadi mari kita kembali ke event handler.

Pengendali peristiwa adalah sedikit kode makro yang dijalankan setiap kali peristiwa tertentu terjadi. Dalam kasus ini, kami ingin makro berjalan setiap kali Anda mengubah sel. Untuk menyiapkan penangan peristiwa, ikuti langkah-langkah berikut:

  • Penangan kejadian dikaitkan hanya dengan satu lembar kerja. Mulai dari lembar kerja itu dan tekan alt-F11 untuk membuka editor VB.
  • Di jendela kiri atas (Proyek - Proyek VBA) klik dua kali nama lembar kerja Anda.
  • Di panel kanan, klik drop-down kiri dan ubah umum ke Lembar Kerja.
  • Di drop-down kanan, pilih Ubah.

Ini akan menyebabkan Excel memasukkan shell makro berikut ini untuk Anda:

Private Sub Worksheet_Change(ByVal Target As Range) UserInput = Target.Value If UserInput> 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End Sub

Setiap kali sel diubah, sel yang diubah diteruskan ke program ini dalam variabel yang disebut "Target". Ketika seseorang memasukkan waktu dengan titik dua di lembar kerja, itu akan mengevaluasi ke angka kurang dari satu. Blok If memastikan hanya mengubah sel jika lebih besar dari satu. Saya menggunakan fungsi left () dan right () untuk memecah input pengguna menjadi jam dan menit dan menyisipkan titik dua di antaranya.

Setiap kali pengguna memasukkan "2345", program akan mengubah entri ini menjadi 23:45.

Peningkatan yang Mungkin

Jika Anda ingin membatasi program agar hanya bekerja pada kolom A&B, Anda dapat memeriksa nilai Target.Column dan hanya mengeksekusi blok kode jika Anda berada di dua kolom pertama:

Private Sub Worksheet_Change(ByVal Target As Range) ThisColumn = Target.Column If ThisColumn 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End If End Sub

Jika Anda ingin membuat perubahan pada lembar kerja tanpa titik dua dimasukkan (misalnya Anda perlu menambahkan rumus atau mengubah judul, dll.), Anda dapat mematikan pengendali kejadian dengan makro singkat ini:

Sub TurnEventHanderOff() Application.EnableEvents = False End Sub You can turn event handlers back on with this macro: Sub TurnEventHanderOff() Application.EnableEvents = True End Sub

Jika Anda mengambil konsep ini dan mengubahnya, ada konsep penting yang harus diperhatikan. Saat makro pengendali kejadian menetapkan nilai baru ke sel yang direferensikan oleh Target, Excel menghitungnya sebagai perubahan lembar kerja. Jika Anda tidak segera mematikan penangan kejadian, maka Excel akan secara rekursif mulai memanggil penangan kejadian dan Anda mendapatkan hasil yang tidak diharapkan. Sebelum membuat perubahan pada lembar kerja di penangan kejadian perubahan, pastikan untuk sementara menangguhkan penanganan kejadian dengan baris Application.EnableEvents.

Artikel yang menarik...