Stavový riadok VBA - Ako povoliť StatusBar pomocou kódu VBA?

Obsah

Stavový riadok Excel VBA

Stavový riadok Excel VBA

StatusBar je vlastnosť súboru VBA, ktorá sa používa na zobrazenie stavu kódu dokončeného alebo dokončeného v čase vykonania. Zobrazuje sa v ľavom bočnom rohu tabuľky, keď sa vykonáva makro, a stav sa zobrazuje v percentách. používateľovi.

Keď makro zaostáva, je frustrujúce počkať bez toho, aby ste vedeli, ako dlho to bude trvať. Ak ste v štádiu, keď je kód spustený, môžete aspoň vypočítať čas, ktorý bude trvať. Ide teda o to, aby ste mali stavový riadok, ktorý zobrazuje percentuálny podiel práce, ktorá bola doposiaľ dokončená, ako je ten uvedený nižšie.

Čo je Application.StatusBar?

Application.StatusBar je vlastnosť, ktorú môžeme použiť v kódovaní makra na zobrazenie stavu, keď je makro spustené za scénou.

To nie je také pekné ako náš „Panel postupu VBA“, ale dosť dobré na to, aby ste poznali stav makroprojektu.

Príklad na vytvorenie StatusBaru pomocou VBA

Podľa nasledujúcich pokynov vytvoríte stavový riadok.

Krok 1: Najskôr definujte premennú VBA, aby ste našli posledný použitý riadok v hárku.

Kód:

Sub Status_Bar_Progress () Dim LR ako dlhý koniec Sub

Krok 2: Vyhľadajte posledný použitý riadok pomocou nižšie uvedeného kódu.

Kód:

Sub Status_Bar_Progress () Dim LR As Long LR = Cells (Rows.Count, 1). End (xlUp). Row End Sub

Krok 3: Ďalej musíme definovať premennú, ktorá bude obsahovať počet pruhov, ktoré sa majú zobraziť.

Kód:

Sub Status_Bar_Progress () Dim LR As Long LR = Cells (Rows.Count, 1). End (xlUp). Row Dim NumOfBars As Integer End Sub

Takto sa udrží, koľko pruhov sa môže zobrazovať v stavovom riadku.

Krok 4: Pre túto premennú uložte limit pruhu ako 45.

Kód:

Sub Status_Bar_Progress () Dim LR As Long LR = Cells (Rows.Count, 1). End (xlUp). Row Dim NumOfBars As Integer NumOfBars = 45 End Sub

Krok 5: Definujte ďalšie dve premenné, ktoré udržia aktuálny stav a percento dokončené, keď je makro spustené.

Kód:

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

Krok 6: Ak chcete povoliť stavový riadok, použite nasledujúci kód.

Kód:

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) & ")" End Sub

Čo to urobí, bude pridanie zátvorky (() a pridanie 45 medzier pred ukončením textu so zatváracou zátvorkou ()).

Vykonajte kód a na stavovom riadku VBA programu Excel sme mohli vidieť nižšie.

Výkon:

Step 7: Now, we need to include the For Next loop in VBA to calculate the percentage of the macro that has been completed. Define a variable to start the macro.

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 Next k End Sub

Step 8: Inside the loop, we need to calculate what the “Present Status” is. So for the variable “PresentStatus,” we need to apply the formula 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) Next k End Sub

We have used the “INT” function to get the integer value as a result.

Step 9: Now, we need to calculate what the “Percentage Completion” is, so we can apply the formula as shown 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) 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