Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Add Button To Excel Worksheet

    We often need to add buttons to worksheets. We can use buttons for many things. Triggering a macro and navigate through worksheets are two very common use of buttons. Sometimes appearance of the button is not very important. But there are situations where we need to give higher concern for its appearance. Specially when creating dashboards. Because it is very important to give professional look to your dashboard.

    There are several types of buttons available. In this post I will show you how to add Form Control Button to your excel worksheet. Lots of people use this button to trigger macros. However I don't recommend these buttons for dashboards. Because we can't even change the color of these buttons to match with our dashboards. Also we can't change the font color of the button. But this type of buttons are very useful if you want to trigger macros. Because these buttons are compatible with all excel versions. Specially with Mac versions. So if you use this kind of button you can assure that your users will able to run macros by clicking the button from any OS and any Excel version.

    If you want to add a form control button to your worksheet, first go to the Developer tab of your excel application. If Developer tab is not visible in your Excel application,  below post will explain you how to do that.

How to show the Developer tab

After go to the developer tab, click on the insert menu. Then you will see this kind of icons.





















Then click on the Button (Form Control). Then your mouse pointer will look like this.




















Then click somewhere on the worksheet and drag like below to form small rectangle.
























Then release the mouse. You will see this kind of pop up window.





















This pop up window helps you to assign macros directly when you create the button. In this case no macros listed because there are no macros in opened files. So you can click OK to create the button without assigning any macro. Then you can assign macro later if you need. However if you have macros in your files, then this pop up will look like below.





















So there are two macros in the files. One is MyFirstMacro and other is MyMacro. So if you need to assign any of these macros to your button, first click on the macro name and then click on the OK button.

When you click OK button, form control button will created in the worksheet like this.






















Then you can edit text and also you can change font type, font size etc. But you can not change the color of font or color of the button.

Create Excel Timer Using VBA

    Today I'm going to show you how to create a Excel Timer. First we need to add two buttons to the excel sheet. One is to start timer. Other one is to stop the timer. And we need to reserve one cell to display the value. Here is an example interface.


We can give it nice look by removing grid-lines


Now add new module in the VBA editor of your workbook. And add below code to it.


Public StopMacro As Boolean


Sub StartTimer()

StopMacro = False

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")

Dim StartTime, timeNow

StartTime = Now

Do Until StopMacro = True
    DoEvents
    timeNow = Now
    WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss")
Loop

End Sub


Sub StopTimer()

StopMacro = True

End Sub


Then assign StartTimer macro to start button and StopTimer macro to Stop button. I think you will able to understand this code easily. However I will explain few important points. In this program we have declared one public variable of type Boolean. We use that variable to detect when user click stop button. So when user click stop button, value of StopMacro variable become true. Then it is detected in do until loop.

In this program we used inbuilt Excel function call Now which returns date and time.

Value entering cell and format of the value is given by below line of code.

WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss")

Also it is very important to use DoEvents within the do until loop. Otherwise user will not get any chance to click on the stop button and loop will become infinite loop.


Do Until StopMacro = True
    DoEvents
    timeNow = Now
    WS.Range("F8").Value = Format(timeNow - StartTime, "hh:mm:ss")
Loop

And this is how timer shows the time.


Pause Program Execution In VBA - Sleep and Wait Functions

Sometimes we need to pause the execution of our program for specific time. In VBA, we can use few methods to do this. Wait and Sleep functions are two popular methods use by VBA developers.
I will show you how to use these two methods from below.

So if you want to use Wait method, it is very simple. You can do it like this.

Sub WaitForOneTenSeconds()

Application.Wait (Now + TimeValue("0:00:10"))

End Sub

So this will pause the code for 10 seconds.

However Sleep is a windows function. It is not a VBA function like Wait. So you have to declare the name of the API at top of your module. So this is how you can pause the program for 10 seconds using Sleep function.

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Sub WaitForTenSeconds()

Sleep 10000     'You need to input the time in milliseconds

End Sub

So functionality of both of these methods are very same. However Wait is more accurate than Sleep. But Sleep is more flexible as you can give the time in milliseconds. If you use Wait method, shortest time you can pause the program is 1 second.
Also there is another important thing you need to know about these two functions. That is, if you use these methods you will notice that these functions suspend all other activities of Microsoft Excel. Even you can't click in the excel sheets. But you can work in other applications.



But sometimes you may need to pause the program for a longer time. And while program is paused, you may need to do some work in excel application like typing, navigate between sheets, scroll the sheets etc. We don't often need this kind of requirement. But it may needed specially in web scraping applications. Because sometime user need to analyze gathered data while web scraping program runs. However there is no in-built function in VBA for this. But we can create our own custom pausing method. Read this post if you want to know how.

How to pause a macro for specific time

Contact Form

Name

Email *

Message *

Popular Posts