Excel VBA StatusBar
StatusBar adalah properti dari vba yang digunakan untuk menampilkan status kode selesai atau selesai pada saat eksekusi, ditampilkan di sudut kiri lembar kerja saat makro dijalankan dan status ditampilkan dalam persentase kepada pengguna.
Ketika makro berjalan di belakang, itu adalah hal yang membuat frustasi untuk menunggu tanpa mengetahui berapa lama waktu yang dibutuhkan. Jika Anda berada pada tahap di mana kode dijalankan, Anda setidaknya dapat menghitung waktu yang dibutuhkan. Jadi, idenya adalah memiliki bilah status yang menunjukkan persentase pekerjaan yang telah diselesaikan sejauh ini, seperti di bawah ini.
Apa itu Application.StatusBar?
Application.StatusBar adalah properti yang dapat kita gunakan dalam pengkodean makro untuk menunjukkan status saat makro berjalan di belakang layar.
Ini tidak seindah "Bilah Kemajuan VBA" kami tetapi cukup baik untuk mengetahui status proyek makro.
Contoh Membuat StatusBar menggunakan VBA
Ikuti langkah-langkah di bawah ini untuk membuat bilah status.
Langkah 1: Pertama, tentukan variabel VBA untuk menemukan baris yang terakhir digunakan di lembar kerja.
Kode:
Sub Status_Bar_Progress () Redupkan LR Sebagai Sub Panjang
Langkah 2: Temukan baris yang terakhir digunakan dengan menggunakan kode di bawah ini.
Kode:
Sub Status_Bar_Progress () Redupkan LR As Long LR = Sel (Rows.Count, 1). End (xlUp) .Row End Sub
Langkah 3: Selanjutnya, kita perlu menentukan variabel untuk menampung jumlah bar yang akan ditampilkan.
Kode:
Sub Status_Bar_Progress () Dim LR As Long LR = Cells (Rows.Count, 1) .End (xlUp) .Row Dim NumOfBars As Integer End Sub
Ini akan menahan berapa banyak bilah yang diizinkan untuk ditampilkan di bilah status.
Langkah 4: Untuk variabel ini, simpan batas bilah sebagai 45.
Kode:
Sub Status_Bar_Progress () Dim LR As Long LR = Sel (Rows.Count, 1) .End (xlUp) .Row Dim NumOfBars As Integer NumOfBars = 45 End Sub
Langkah 5: Tentukan dua variabel lagi untuk menahan status saat ini dan persentase selesai saat makro sedang berjalan.
Kode:
Sub Status_Bar_Progress () Dim LR As Long LR = Cells (Rows.Count, 1). End (xlUp) .Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer End Sub
Langkah 6: Sekarang, untuk mengaktifkan bilah status, gunakan kode di bawah ini.
Kode:
Sub Status_Bar_Progress () Dim LR As Long LR = Sel (Rows.Count, 1) .End (xlUp) .Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Spasi ( NumOfBars) & ")" End Sub
Apa yang akan dilakukan ini akan menambahkan tanda kurung (() dan menambahkan 45 karakter spasi sebelum mengakhiri teks dengan tanda kurung tutup ()).
Jalankan kodenya, dan kita bisa melihat di bawah ini di status bar excel VBA.
Keluaran:
Langkah 7: Sekarang, kita perlu menyertakan loop For Next di VBA untuk menghitung persentase makro yang telah selesai. Tentukan variabel untuk memulai makro.
Kode:
Sub Status_Bar_Progress () Dim LR As Long LR = Sel (Rows.Count, 1) .End (xlUp) .Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Spasi ( NumOfBars) & ")" Dim k As Long For k = 1 To LR Next k End Sub
Langkah 8: Di dalam loop, kita perlu menghitung apa itu "Status Sekarang". Jadi untuk variabel "PresentStatus", kita perlu menerapkan rumus seperti di bawah ini.
Kode:
Sub Status_Bar_Progress () Dim LR As Long LR = Sel (Rows.Count, 1) .End (xlUp) .Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Spasi ( NumOfBars) & ")" Dim k As Long For k = 1 To LR PresentStatus = Int ((k / LR) * NumOfBars) Next k End Sub
Kami telah menggunakan fungsi " INT " untuk mendapatkan nilai integer sebagai hasilnya.
Langkah 9: Sekarang, kita perlu menghitung apa itu " Persentase Penyelesaian ", jadi kita bisa menerapkan rumus seperti yang ditunjukkan di bawah ini.
Kode:
Sub Status_Bar_Progress() Dim LR As Long LR = Cells(Rows.Count, 1).End(xlUp).Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Space(NumOfBars) & ")" Dim k As Long For k = 1 To LR PresentStatus = Int((k / LR) * NumOfBars) PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0) Next k End Sub
In this case, we have used the ROUND function in excel because whatever the decimal places, we need to round to the nearest zero value, so ROUND with zero as the argument has been used here.
Step 10: We have already inserted the starting bracket and end bracket to the status bar, now we need to insert the updated result, and it can be done by using the below code.
Code:
Sub Status_Bar_Progress() Dim LR As Long LR = Cells(Rows.Count, 1).End(xlUp).Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Space(NumOfBars) & ")" Dim k As Long For k = 1 To LR PresentStatus = Int((k / LR) * NumOfBars) PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0) Application.StatusBar = "(" & String(PresentStatus, "|") & Space(NumOfBars - PresentStatus) & _") " & PercetageCompleted & "% Complete" Next k End Sub
In the above code, we have inserted the opening bracket “(“ and to show the progress of the macro, we have inserted a straight line (|) by using the STRING function. When the loop is running, it will take the “PresentStatus,” and those many straight lines will be inserted in the status bar.
Code:
Application.StatusBar = "(" & String(PresentStatus, "|")
Next, we need to add space characters between one straight line to the other, so this will be calculated by using “NumOfBars” minus “PresentStatus.”
Code:
Application.StatusBar = "(" & String(PresentStatus, "|") & Space(NumOfBars - PresentStatus)
Then we close out the bracket “).” Next, we have combined the “PercentageCompleted” variable value while the loop is running with the word in front of it as “% Completed.”
Code:
Application.StatusBar = "(" & String(PresentStatus, "|") & Space(NumOfBars - PresentStatus)& _") " & PercetageCompleted & "% Complete"
When the code is running, we allow the user to access the worksheet, so we need to add “Do Events.”
Code:
Sub Status_Bar_Progress() Dim LR As Long LR = Cells(Rows.Count, 1).End(xlUp).Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Space(NumOfBars) & ")" Dim k As Long For k = 1 To LR PresentStatus = Int((k / LR) * NumOfBars) PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0) Application.StatusBar = "(" & String(PresentStatus, "|") & Space(NumOfBars - PresentStatus) & _ ") " & PercetageCompleted & "% Complete" DoEvents Next k End Sub
Step 11: After adding “Do Events,” we can write the codes that need to be executed here.
For example, I want to insert serial numbers to the cells, so I will write code as below.’
Code:
Sub Status_Bar_Progress() Dim LR As Long LR = Cells(Rows.Count, 1).End(xlUp).Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Space(NumOfBars) & ")" Dim k As Long For k = 1 To LR PresentStatus = Int((k / LR) * NumOfBars) PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0) Application.StatusBar = "(" & String(PresentStatus, "|") & Space(NumOfBars - PresentStatus) & _") " & PercetageCompleted & "% Complete" DoEvents Cells(k, 1).Value = k 'You can add your code here Next k End Sub
Step 12: Before we come out of the loop, we need to add one more thing, i.e., If the loop near the last used row in the worksheet then we need to make the status bar as normal.
Code:
Sub Status_Bar_Progress() Dim LR As Long LR = Cells(Rows.Count, 1).End(xlUp).Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Space(NumOfBars) & ")" Dim k As Long For k = 1 To LR PresentStatus = Int((k / LR) * NumOfBars) PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0) Application.StatusBar = "(" & String(PresentStatus, "|") & Space(NumOfBars - PresentStatus) & _") " & PercetageCompleted & "% Complete" DoEvents Cells(k, 1).Value = k 'You can add your code here 'You can Add your code here 'You can Add your code here 'You can add your code here 'You can add your code here 'You can add your code here If k = LR Then Application.StatusBar = False Next k End Sub
Ok, we are done with coding. As you execute the code here, you can see the status bar updating its percentage completion status.
Output:
Below is the code for you.
Code:
Sub Status_Bar_Progress() Dim LR As Long LR = Cells(Rows.Count, 1).End(xlUp).Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Space(NumOfBars) & ")" Dim k As Long For k = 1 To LR PresentStatus = Int((k / LR) * NumOfBars) PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0) Application.StatusBar = "(" & String(PresentStatus, "|") & Space(NumOfBars - PresentStatus) & _") " & PercetageCompleted & "% Complete" DoEvents Cells(k, 1).Value = k 'You can add your code here 'You can Add your code here 'You can Add your code here 'You can add your code here 'You can add your code here 'You can add your code here If k = LR Then Application.StatusBar = False Next k End Sub
Hal-hal untuk diingat
- Kami hanya dapat menambahkan tugas yang perlu dilakukan dalam loop.
- Anda dapat menambahkan tugas yang perlu Anda lakukan setelah menambahkan prosedur "Lakukan Acara".
