As the copying of streams is more an action than an object and the action has not to be extended in the way of OOD, I think the functional approach is more natural. Progress statistics. Now the progress can be tracked, but displaying only the number of bytes copied in contrast to the number of bytes which will be copied, is not enough for most. Show progress meter when copying files. I am currently using the FileCopy command to copy files in part of my excel vba program, the problem i am having is that the files being copied can often be upto 10GB and while the file is being copied excel appears to the user to have 'hung' and often they will try to force close excel using taskmanager. How to use Progress Bar in excel Macro (VBA) Progress bar is nothing but a placeholder, where you see the Progress of the operation which is getting performed. Like Visual Studio there is NO already built progress bar in Excel Macro (VBA), which you can use it as an object and it will act like a Progress bar. Try this: Start a new Windows Forms application and replace the code on Form1 with. Imports System.Net Imports System.IO Public Class Form1 Protected Overrides Sub OnLoad(e As EventArgs) MyBase.OnLoad(e) CopyBtn.Text = 'Copy File' CopyBtn.Parent = Me ProgBar.Left = CopyBtn.Right End Sub Dim WithEvents CopyBtn As New Button Dim ProgBar As New ProgressBar Dim WithEvents FileCopier As New.

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.

Vba File Copy Progress Bar

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:

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:

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:

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:

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:

The result:

VBA Send Email Notification

Vba File Copy Progress Bar Examples

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.

We might want to spice things up with maybe sending also the time it took to complete the macro:

Cool right? Makes the VBA Message Box hide in shame doesn’t it? The email should look like this:

• VB.Net Basic Tutorial

• VB.Net Advanced Tutorial

• VB.Net Useful Resources

• Selected Reading

It represents a Windows progress bar control. It is used to provide visual feedback to your users about the status of some task. It shows a bar that fills in from left to right as the operation progresses.

Let's click on a ProgressBar control from the Toolbox and place it on the form.

The main properties of a progress bar are Value, Maximum and Minimum. The Minimum and Maximum properties are used to set the minimum and maximum values that the progress bar can display. The Value property specifies the current position of the progress bar.

The ProgressBar control is typically used when an application performs tasks such as copying files or printing documents. To a user the application might look unresponsive if there is no visual cue. In such cases, using the ProgressBar allows the programmer to provide a visual status of progress.

Properties of the ProgressBar Control

The following are some of the commonly used properties of the ProgressBar control −

Methods of the ProgressBar Control

The following are some of the commonly used methods of the ProgressBar control −

Events of the ProgressBar Control

The following are some of the commonly used events of the ProgressBar control −

Example

In this example, let us create a progress bar at runtime. Let's double click on the Form and put the follow code in the opened window.

Vba File Copy Progress Bar Example

When the above code is executed and run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window −

Vba File Copy Progress Bar Excel