Vrstica stanja VBA - Kako omogočiti vrstico stanja z uporabo kode VBA?

Kazalo

Vrstica stanja VBA v Excelu

Vrstica stanja VBA v Excelu

StatusBar je lastnost vba, ki se uporablja za prikaz stanja končane ali dokončane kode v času izvajanja, prikazana je v levem kotu delovnega lista, ko se izvede makro in stanje je prikazano v odstotkih uporabniku.

Ko makro teče zadaj, je neprijetno čakati, ne da bi vedeli, kako dolgo bo trajalo. Če ste v fazi, ko se koda izvaja, lahko vsaj izračunate čas, ki bo potreben. Ideja je torej, da imamo statusno vrstico, ki prikazuje odstotek do zdaj opravljenega dela, kot je spodnja.

Kaj je Application.StatusBar?

Application.StatusBar je lastnost, ki jo lahko uporabimo pri kodiranju makrov, da prikažemo stanje, ko se makro izvaja za prizorom.

To ni tako lepo kot naša "VBA Progress Bar", vendar dovolj dobro, da poznamo stanje makro projekta.

Primer ustvarjanja vrstice stanja z uporabo VBA

Sledite spodnjim korakom, da ustvarite vrstico stanja.

1. korak: Najprej definirajte spremenljivko VBA, da poiščete zadnjo uporabljeno vrstico na delovnem listu.

Koda:

Sub Status_Bar_Progress () Dim LR As Long End Sub

2. korak: S spodnjo kodo poiščite zadnjo uporabljeno vrstico.

Koda:

Sub Status_Bar_Progress () Dim LR As Long LR = Celice (Vrstice. Število, 1). End (xlUp). Vrstica End Sub

Korak 3: Nato moramo definirati spremenljivko, ki bo vsebovala število vrstic, ki bodo prikazane.

Koda:

Sub Status_Bar_Progress () Dim LR as Long LR = Celice (Rows.Count, 1). End (xlUp). Vrstica Dim NumOfBars As Integer End Sub

To bo vsebovalo, koliko vrstic je dovoljeno prikazati v vrstici stanja.

4. korak: Za to spremenljivko shranite omejitev vrstice kot 45.

Koda:

Sub Status_Bar_Progress () Dim LR as Long LR = Celice (Rows.Count, 1). End (xlUp). Vrstica Dim NumOfBars As Integer NumOfBars = 45 End Sub

5. korak: Določite še dve spremenljivki, ki bosta ohranili trenutno stanje in odstotek dokončanih, ko se makro izvaja.

Koda:

Sub Status_Bar_Progress () Dim LR as Long LR = Celice (Rows.Count, 1) .End (xlUp) .Red Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer End Sub Sub

6. korak: Zdaj, da omogočite vrstico stanja, uporabite spodnjo kodo.

Koda:

Sub Status_Bar_Progress () Dim LR as Long LR = Celice (Rows.Count, 1) .End (xlUp) .Row Dim NumOfBars As Integer NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted As Integer Application.StatusBar = "(" & Space ( NumOfBars) & ")" End Sub

Kaj bo to storilo, bo dodalo oklepaj (() in dodalo 45 presledkov pred zaključkom besedila z zaključnim oklepajem ()).

Izvedite kodo in spodaj smo lahko videli v vrstici stanja excel VBA.

Izhod:

7. korak: Zdaj moramo v VBA vključiti zanko For Next, da izračunamo odstotek dokončanega makra. Določite spremenljivko za zagon makra.

Koda:

Sub Status_Bar_Progress () Dim LR as Long LR = Celice (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 Next k End Sub

Korak 8: Znotraj zanke moramo izračunati, kaj je »trenutno stanje«. Za spremenljivko "PresentStatus" moramo torej uporabiti spodnjo formulo.

Koda:

Sub Status_Bar_Progress () Dim LR as Long LR = Celice (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) Next k End Sub

Za pridobitev celoštevilske vrednosti smo uporabili funkcijo " INT ".

Korak 9: Zdaj moramo izračunati, kakšen je “ Odstotek dokončanja ”, tako da lahko uporabimo formulo, kot je prikazano spodaj.

Koda:

Sub Status_Bar_Progress () Dim LR as Long LR = Celice (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 Kako dolgo za k = 1 do LR PresentStatus = Int ((k / LR) * NumOfBars) PercetageCompleted = Round (PresentStatus / NumOfBars * 100, 0) Next k End Sub

V tem primeru smo v Excelu uporabili funkcijo ROUND, kajti ne glede na decimalna mesta moramo zaokrožiti na najbližjo ničto vrednost, torej je bil tu uporabljen ROUND z ničlo.

10. korak: Začetni in končni nosilec smo že vstavili v vrstico stanja, zdaj moramo vstaviti posodobljeni rezultat, kar lahko storimo s spodnjo kodo.

Koda:

Sub Status_Bar_Progress () Dim LR as Long LR = Celice (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 Dolgo za k = 1 do LR PresentStatus = Int ((k / LR) * NumOfBars) PercetageCompleted = Round (PresentStatus / NumOfBars * 100, 0) Application.StatusBar = "(" & String ( PresentStatus, "|") & presledek (NumOfBars - PresentStatus) & _ ")" & PercetageCompleted & "% Complete" Next k End Sub

V zgornjo kodo smo vstavili odpiralni oklepaj »(« in za prikaz napredka makra smo vstavili ravno črto (|) s funkcijo STRING. Ko se zanka zažene , bo zajela » PresentStatus , «In v vrstico stanja bodo vstavljene tiste številne ravne črte.

Koda:

Application.StatusBar = "(" & String (PresentStatus, "|")

Nato moramo dodati presledke med eno ravno črto v drugo, zato se bo to izračunalo z uporabo »NumOfBars« minus »PresentStatus«.

Koda:

Application.StatusBar = "(" & String (PresentStatus, "|") & presledek (NumOfBars - PresentStatus)

Nato zapremo oklepaj ")." Nato smo združili vrednost spremenljivke “PercentageCompleted”, medtem ko se zanka izvaja, beseda pred njo pa kot “% Completed”.

Koda:

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