【轉載(Reprinted)】VBA Status Bar, Progress Bar, Sounds and Emails – Alerts in VBA【進度條,status bar,播放音效】
Some VBA Macros run for ages – be it minutes or even hours. If we optimized our VBA Macro to the fullest there is often not much more you can do to change that. Usually what is irritating is waiting for VBA Macro code completion. Sitting by your desk is a waste of time – why not tell Excel/Access to let you know when the code has completed? Today we will explore such tools as the VBA Status Bar, VBA Progress Bar, Sound Notifications in VBA and Sending Email Alerts from VBA.
VBA PROGRESS BAR
Progress Bars are ubiquitous anywhere anything takes more than a couple of seconds to complete. So why doesn’t VBA have a native Progress Bar – beats me.
To add a VBA Progress Bar to your Visual Basic for Applications macro read my post here.
EXCEL STATUS BAR
The VBA Status Bar is a panel that appears at the bottom of your Excel (or Access) Workbook. It is basically a Text Box to which you can display any non-blocking (non-modal) Message to your users.
Showing a Message in the VBA Status Bar
To show a message in the VBA Status Bar we need to Enable it using Application.DisplayStatusBar:
1 2 | Application.DisplayStatusBar = True Application.StatusBar = "My Message in the Status Bar" |
Hiding the VBA Status Bar
The VBA Status Bar when displayed, will remain displayed until ordered otherwise. It is best to hide and clean it after code execution completion:
1 2 | Application.StatusBar = vbNullString 'Clean the Status Bar message Application.DisplayStatusBar = False 'Hide the Status Bar altogether |
SOUND NOTIFICATIONS IN VBA
What if you are away from your Screen, getting a Coffee or simply chatting with a colleague or getting on with our tasks stressed that we might miss that moment when our VBA Macro completes its task. Well why not introduce a Sound Notification? Let Excel or Access run a Sound to inform you that your Visual Basic for Applications macro has finally completed.
Playing a Sound in VBA
Playing a Sound in VBA is easy. Just add the below code snippet to a VBA Module:
1 2 | Public Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" ( ByVal lpszSoundName As String , ByVal uFlags As Long ) As Long sndPlaySound32 "C:\Windows\Media\Chimes.wav" , &H0 |
It will play the Chimes.wav sound Once.
What if you don’t hear it? Running it once might obviously be not enough…
VBA Sound Alarm
The best approach is to create a VBA Sound Alarm that will run in a loop until we Turn it Off manually. What we will need is a Non-Modal UserForm as a pop-up to ask to stop the alarm.
First the macro code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Public Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" ( ByVal lpszSoundName As String , ByVal uFlags As Long ) As Long Public Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" ( ByVal lpszSoundName As String , ByVal uFlags As Long ) As Long Public stopPlaying As Boolean Sub SomeMacro() Dim uf As AlarmForm, startTime As Double , execTime As Double Set uf = New AlarmForm stopPlaying = False startTime = Timer '... 'Your macro '... execTime = Timer - startTime Call uf.SetMessageAndTitle( "Macro Execution Completed in " & TimeSerial(Round(execTime / 3600, 0), _ Round((execTime - Round(execTime / 3600, 0) * 3600) / 60), _ execTime - Round(execTime / 3600, 0) * 3600 - Round((execTime - Round(execTime / 3600, 0) * 3600) / 60) * 60), _ "Completed!" ) uf.Show ( False ) Do Until stopPlaying sndPlaySound32 "C:\Windows\Media\Chimes.wav" , &H0 DoEvents Loop End Sub |
What happens here?:
- I am measuring the execution time with the startTime and execTime variables
- I am using the stopPlaying global VBA Variable to Turn Off the Alarm from our Alarm UserForm changes this value to True
- I am displaying the AlarmForm with the Turn Off Alarm button
And now our AlarmForm code:
1 2 3 4 5 6 7 8 | Private Sub stopButton_Click() stopPlaying = True Hide End Sub Sub SetMessageAndTitle(msg As String , title As String ) Me .Caption = title lMessage.Caption = msg End Sub |
VBA SEND EMAIL NOTIFICATION
Lastly when we are on the run and leaving our Workstation to process our VBA Macro remotely we might appreciate an email from Excel saying: Hey there! I just completed running the Macro!
You can find the code for the SendEmailFromOutlook function in my post here.
1 2 3 4 5 6 | Sub SomeMacro() '... 'Your macro here '... Call SendEmailFromOutlook( "" , "VBA Macro Completed!" , "youremail@yourdomain.com" , "" , "" ) End Sub |
We might want to spice things up with maybe sending also the time it took to complete the macro:
1 2 3 4 5 6 7 8 9 10 11 12 | Sub SomeMacro() Dim startTime As Double , execTime As Double , body as String startTime = Timer '... 'Your macro here '... execTime = Timer - startTime body = "" & TimeSerial(Round(execTime / 3600, 0), _ Round((execTime - Round(execTime / 3600, 0) * 3600) / 60), _ execTime - Round(execTime / 3600, 0) * 3600 - Round((execTime - Round(execTime / 3600, 0) * 3600) / 60) * 60) Call SendEmailFromOutlook(body , "VBA Macro Completed!" , "youremail@yourdomain.com" , "" , "" ) End Sub |
Cool right? Makes the VBA Message Box hide in shame doesn’t it? The email should look like this:
留言