VBA olekuriba - Kuidas lubada StatusBar VBA-koodi abil?

Lang L: none (table-of-contents)

Exceli VBA olekuriba

Exceli VBA olekuriba

StatusBar on vba omadus, mida kasutatakse täitmise ajal lõpetatud või lõpetatud koodi oleku kuvamiseks, seda kuvatakse makro käivitamisel töölehe vasakus servas ja olek kuvatakse protsentides kasutajale.

Kui makro jookseb taga, on masendav asi oodata, teadmata, kui kaua see aega võtab. Kui olete koodi käitamise etapis, saate vähemalt arvutada, millise aja see võtab. Nii on idee, et olekuriba, mis näitab tööprotsenti, on siiani täidetud, nagu allpool.

Mis on Application.StatusBar?

Application.StatusBar on omadus, mida saame makrokodeerimisel kasutada oleku kuvamiseks, kui makro töötab stseeni taga.

See pole nii ilus kui meie VBA edenemisriba, kuid piisavalt hea, et makroprojekti olekut teada saada.

Näide StatusBari loomiseks VBA abil

Olekuriba loomiseks toimige järgmiselt.

1. samm: määrake kõigepealt VBA muutuja, et leida töölehelt viimati kasutatud rida.

Kood:

Sub Status_Bar_Progress () Dim LR kui pika otsa alam

2. samm: leidke allpool oleva koodi abil viimati kasutatud rida.

Kood:

Ala oleku_riba_progress () Dim LR As Long LR = lahtrid (read.Count, 1) .End (xlUp). Rea lõppu alam

3. samm: Järgmisena peame määratlema muutuja kuvatavate ribade arvu hoidmiseks.

Kood:

Ala oleku_riba_progress () Dim LR As Long LR = lahtrid (Rows. Count, 1). End (xlUp). Rida Dim NumOfBars kui täisarvu lõpp-alam

See hoiab, kui palju ribasid on lubatud olekuribal näidata.

4. samm: salvestage selle muutuja jaoks riba piiriks 45.

Kood:

Alam-olekuriba_progress () Dim LR As Long LR = lahtrid (read.Count, 1) .End (xlUp). Rida Dim NumOfBars täisarvuna NumOfBars = 45 lõpp-alam

5. samm: määrake veel kaks muutujat, et makro töötamise ajal oleks praegune olek ja protsent täidetud.

Kood:

Alam-olekuriba_progress () Dim LR As Long LR = lahtrid (read.Count, 1) .End (xlUp). Rida Dim NumOfBars kui täisarv NumOfBars = 45 Dim PresentStatus kui täisarv Dim Percetage lõpetatud täisarvu lõpuna

6. samm: olekuriba lubamiseks kasutage allolevat koodi.

Kood:

Sub Status_Bar_Progress () Dim LR As Long LR = lahtrid (Rows.Count, 1) .End (xlUp). Rida Dim NumOfBars kui täisarv NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted as Integer Application.StatusBar = "(" & Space ( NumOfBars) & ")" Lõpeta alam

Mida see teeb, lisab sulg (() ja lisab 45 tühikut enne teksti lõppu sulgudes ()).

Käivitage kood ja allpool näeme Exceli VBA olekuribal.

Väljund:

7. samm: nüüd peame kaasama VBA-tsükli VBA-sse, et arvutada lõpetatud makro protsent. Makro käivitamiseks määrake muutuja.

Kood:

Sub Status_Bar_Progress () Dim LR As Long LR = lahtrid (Rows.Count, 1) .End (xlUp). Rida Dim NumOfBars kui täisarv NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted as Integer Application.StatusBar = "(" & Space ( NumOfBars) & ")" Dim k nii kaua, kui k = 1 kuni LR Järgmine k End Sub

8. samm: silmuse sees peame arvutama, mis on “praegune olek”. Seega peame muutuja “PresentStatus” jaoks kasutama valemit nagu allpool.

Kood:

Sub Status_Bar_Progress () Dim LR As Long LR = lahtrid (Rows.Count, 1) .End (xlUp). Rida Dim NumOfBars kui täisarv NumOfBars = 45 Dim PresentStatus As Integer Dim PercetageCompleted as Integer Application.StatusBar = "(" & Space ( NumOfBars) & ")" Dim k nii kaua kui k = 1 LR PresentStatus = Int ((k / LR) * NumOfBars) Järgmine k End Sub

Selle tulemusena oleme täisarvu saamiseks kasutanud funktsiooni “ INT ”.

9. samm: nüüd peame arvutama, mis on „täitmise protsent “, et saaksime rakendada valemit, nagu allpool näidatud.

Kood:

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