Barra de estado de Excel VBA
StatusBar es la propiedad de un vba que se usa para mostrar el estado del código terminado o completado en el momento de la ejecución, se muestra en la esquina izquierda de la hoja de trabajo cuando se ejecuta una macro y el estado se muestra en porcentaje al usuario.
Cuando la macro se está quedando atrás, es frustrante esperar sin saber cuánto tiempo llevará. Si se encuentra en la etapa en la que se está ejecutando el código, al menos puede calcular el tiempo que llevará. Entonces, la idea es tener una barra de estado que muestre el porcentaje de trabajo que se ha completado hasta ahora, como la siguiente.
¿Qué es Application.StatusBar?
Application.StatusBar es la propiedad que podemos usar en la codificación de macros para mostrar el estado cuando la macro se está ejecutando detrás de escena.
Esto no es tan hermoso como nuestra "barra de progreso de VBA", pero lo suficientemente bueno para conocer el estado del proyecto macro.
Ejemplo para crear StatusBar usando VBA
Siga los pasos a continuación para crear una barra de estado.
Paso 1: Primero, defina la variable VBA para encontrar la última fila utilizada en la hoja de trabajo.
Código:
Sub Status_Bar_Progress () Dim LR As Long End Sub
Paso 2: busque la última fila utilizada utilizando el siguiente código.
Código:
Sub Status_Bar_Progress () Dim LR As Long LR = Cells (Rows.Count, 1) .End (xlUp) .Row End Sub
Paso 3: A continuación, debemos definir la variable para contener el número de barras que se mostrarán.
Código:
Sub Status_Bar_Progress () Dim LR As Long LR = Cells (Rows.Count, 1) .End (xlUp) .Row Dim NumOfBars As Integer End Sub
Esto contendrá cuántas barras se pueden mostrar en la barra de estado.
Paso 4: Para esta variable, almacene el límite de la barra como 45.
Código:
Sub Status_Bar_Progress () Dim LR As Long LR = Cells (Rows.Count, 1) .End (xlUp) .Row Dim NumOfBars As Integer NumOfBars = 45 End Sub
Paso 5: Defina dos variables más para mantener el estado actual y el porcentaje completado cuando se ejecuta la macro.
Código:
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
Paso 6: Ahora, para habilitar la barra de estado, use el siguiente código.
Código:
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
Lo que hará esto es agregar el corchete (() y agregar 45 caracteres de espacios antes de que termine el texto con el corchete de cierre ()).
Ejecute el código y podríamos ver lo siguiente en la barra de estado de Excel VBA.
Salida:
Paso 7: Ahora, necesitamos incluir el ciclo For Next en VBA para calcular el porcentaje de la macro que se ha completado. Defina una variable para iniciar la macro.
Código:
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
Paso 8: Dentro del ciclo, necesitamos calcular cuál es el "Estado actual". Entonces, para la variable "PresentStatus", debemos aplicar la fórmula que se muestra a continuación.
Código:
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) Siguiente k End Sub
Hemos utilizado la función " INT " para obtener el valor entero como resultado.
Paso 9: Ahora, necesitamos calcular cuál es el " Porcentaje de finalización ", para que podamos aplicar la fórmula como se muestra a continuación.
Código:
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
Cosas para recordar
- Podemos agregar solo las tareas que deben realizarse dentro del ciclo.
- Puede agregar las tareas que necesita hacer después de agregar el procedimiento "Hacer eventos".
