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.

Detect VBA InputBox Cancel - Two Solutions

We often use InputBox to get inputs from users. But do you know how to detect if a user cancels an InputBox. If you don’t detect it then it may cause some errors. Because if you have lines of codes after the InputBox, and if you don’t detect the cancel and handle it accordingly, then the program will execute the lines of codes after the InputBox method. And this can cause various errors. So you should always detect InputBox cancel and handle it accordingly when you use this method.

So let’s look at how we can detect when a user cancels an InputBox. Let’s consider this sample subroutine.

Sub AskUserName()

     Dim Response As String

     Response = InputBox("Enter the user name:", "Username")

     Debug.Print "Hello " & Response & "!"

End Sub

If we run this, the program will ask to enter the username.

Get the username via InputBox

Then when we click “OK”, the program will print this in the immediate window.

Print username in the immediate window

However if we click the “Cancel” button, the program still prints in the immediate window.

Even user click cancel button the program still print in the immediate window

But if the user clicks the “Cancel” button, the program should not print anything in the immediate window. So how do we modify the subroutine to do that. When a user clicks the Cancel button, the InputBox method returns an empty string. So we can use that returned value to detect the cancellation action.

Sub AskUserName()

     Dim Response As String

     Response = InputBox("Enter the user name:", "Username")

     If Len(Response) = 0 Then
          Exit Sub
     Else
          Debug.Print "Hello " & Response & "!"
     End If

End Sub

So in the above code first we check the length of the string. If it is 0 then we exit the subroutine using the Exit statement. However there is a small problem in this method. If a user clicks the OK button without entering anything then the program still sees it as a cancellation. Because the InputBox method returns an empty string as the user didn’t enter anything. So if you want to act differently, for the user clicks cancel than to when the user clicks ok without entering anything, then you can’t use the above method. If you want to treat the user in different ways for those two actions, then you should use your own userform and handle the cancellation as desired. This second method explains how to do that.

First go to the VBA editor and insert a userform.

Open VBA editor and click insert
Insert Userform

Then add label, textbox and two command buttons to the form and format them to your preference.

Also see
How to add labels to VBA Userforms
How to Add Textboxes to VBA Userforms
How to Format Label Text in VBA Forms (To Give Professional Look)

Userform with OK and cancel buttons

In this method you have more freedom. You can apply any colors you want and also add additional buttons if needed. Next, insert a module.

Insert a module

Then add this code to the module.

Sub Main()

     UserForm1.Show

End Sub

When you run the “Main” Sub procedure, it will show the form. We can add the following code to the OK button of the form.

Private Sub cmdOK_Click()

     Debug.Print "Hello " & txtUsername.Value & "!"

End Sub

So this will print the username when you click the OK button. Finally we can add below code to the cancel button.

Private Sub cmdCancel_Click()

     Unload Me

End Sub

Now the form will close when the user clicks the cancel button.

Lock Macro Execution (Using Password)

In the last post, we learnt how to lock a VBA project with a password. So if a user wants to see the code then he/she has to input the correct password. But we realized, although the codes are locked, users can run the macros from the developer tab. But there may be times you want to also lock the macros from being executed as well. Then users will need to provide the password before running the macro. So in this post you can learn how to lock the macro execution using a password.

Let’s consider this simple macro

Sub WriteNumbers()

     Dim i As Integer

     For i = 1 To 10000
          Worksheets("Sheet1").Range("A" & i).Value = i
     Next i

End Sub

What this macro does is it writes the numbers from 1 to 10000 in column A of the Sheet1. Now let’s look at how we can modify the above macro to protect it from being executed. First we need to declare an additional variable.

Dim Res As String
Dim i As Integer

Then we can use an InputBox as follows.

Res = InputBox("Please enter the password!", "Password")

Now the program will ask for the password when the user tries to run the macro.

InputBox

Once the user enters the password, the program needs to verify whether it is correct or not. We can use an IF Statement for this. Also we can use the StrComp function to compare the user input with the password. Assume our password is “atyo45#4Yt”. Then we can develop the validating section as follows.

If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
     For i = 1 To 10000
          Worksheets("Sheet1").Range("A" & i).Value = i
     Next i
Else
     MsgBox "Sorry! Incorrect password"
     Exit Sub
End If

StrComp function compare the user input and the password (atyo45#4Yt). Then if the user input and password are matching then “If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0” becomes true. So the program goes to the For Next loop and writes the numbers. If the user input and password are not matching then the program shows the below message.

Password Incorrect message

This is the completed code.

Sub WriteNumbers()

     Dim Res As String
     Dim i As Integer

     Res = InputBox("Please enter the password!", "Password")

     If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
          For i = 1 To 10000
               Worksheets("Sheet1").Range("A" & i).Value = i
          Next i
     Else
          MsgBox "Sorry! Incorrect password"
          Exit Sub
     End If

End Sub

However there is one small problem in this macro. Typically, if a user clicks the cancel button of the input box or closes the input box, the program should not do anything. It should stop the execution without even showing any message. But in this macro,if a user cancels the Input box or closes it, still the program shows the password incorrect message. This is because when the user cancels or closes the input box it returns an empty string. So to avoid this what we can do is we can first check whether the variable “Res” holds an empty string. If it holds an empty string program will not perform any other actions. Here is the improved macro.

Sub WriteNumbers()

     Dim Res As String
     Dim i As Integer

     Res = InputBox("Please enter the password!", "Password")

     If Res = "" Then
          'Do nothing
     ElseIf StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
          For i = 1 To 10000
               Worksheets("Sheet1").Range("A" & i).Value = i
          Next i
     Else
          MsgBox "Sorry! Incorrect password"
          Exit Sub
     End If

End Sub

However note that, when a user clicks the “OK” button without entering password (i.e. When the user input an empty string as password) the program doesn’t show the password incorrect message. Instead the program behaves similar to when a user clicks the cancel button.

How to Lock a VBA Macro

So far we developed various macros according to numerous requirements. So today I’m going to show you how to lock them with passwords. Because once you developed a macro you may want to lock it as it is your intellectual property. Also you may want to lock the project to protect it from being wrecked by other users.

So now let’s see how we can lock the project. Follow these easy steps. First open the Excel file containing the macro. Then click somewhere in the worksheet. Next press Alt + F11 on your keyboard. This will open the VBA editor.

Open VBA editor

Alternatively, you can go to the “Developer” tab and click on “Visual Basic” to open the VBA editor as well.

Go to the “Developer” tab

Click on the Visual Basic

Note that the “Developer” tab is not displayed by default. This post explains how to add it to the ribbon.

How to show the Developer tab

As you can see in the first image, I have a very simple macro in the module1. To lock the project first click on the “Tools” menu.

Click on the Tools menu

Then select “VBAProject Properties...”

Select VBAProject Properties

“Project Properties” dialog box will open. Go to the “Protection” tab and check the “Lock project for viewing”. Then enter the password in both textboxes and click OK.

Project Properties dialog box

Save the file, close it and reopen. Now when you go to the VBA editor and try to expand the project from project explorer it will ask you the password to unlock.

VBA project is protected

So if a user doesn't have a password then he/she can't view the code. But they can still run the macros from the developer tab. Thus anyone can run the macro without messing it up.

However if you want you can also protect the macro execution with a password too.

Lock Macro Execution (Using Password)

Get the Day Name of a Date Independent of Regional Settings

In the last post we developed a macro to get the weekday name of a date. But then we had to use the format of the dates according to the regional settings. So in this lesson I will show you how to develop a more advanced solution, to get the day name of a date independent of regional settings. For that I will use two more functions called “Split” and “DateSerial” in addition to the “Format" function. Let’s consider this sample excel sheet. As specified in the top row of column A, dates should be added in the given format (ex - mm/dd/yyyy). However you can easily change the code to suit with the format you need.

Add the dates in the given format

Dates have been added to column A in "mm/dd/yyyy" format. Now our macro should write the day name in front of each date.

So let’s start the macro by declaring the variables.

Dim WS As Worksheet
Dim i As Long
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim ColA_Text As String
Dim CurrentDate As Date
Dim DayName As String

Assign the activesheet to the WS variable.

Set WS = ActiveSheet

We have dates in multiple rows. Therefore we need a For Next statement to iterate through each row. As we have dates from row 2 to row 10 we can use the For Next statement as follows.

For i = 2 To 10

Next i

When looping through each row assign the date of column A to ColA_Text variable.

ColA_Text = WS.Range("A" & i).Text

Now this variable holds the date as string. So we can extract day, month and year separately using the split function.

iDay = Split(ColA_Text, "/")(1)
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)

Want to learn about split function. Read this post.

Split Function

Now we have day, month and year in 3 separate variables. So we can use the DateSerial function to get the date correctly independent on the region user lives.

CurrentDate = DateSerial(iYear, iMonth, iDay)

Next, use the “Format” function to get the day name like we did in the previous lesson.

DayName = Format(CurrentDate, "dddd")

Finally we can write the day name to column B of the worksheet.

WS.Range("B" & i).Value = DayName

Below is the completed code.

Sub GetDayName()

     Dim WS As Worksheet
     Dim i As Long
     Dim iYear As Integer
     Dim iMonth As Integer
     Dim iDay As Integer
     Dim ColA_Text As String
     Dim CurrentDate As Date
     Dim DayName As String

     Set WS = ActiveSheet
     For i = 2 To 10
          ColA_Text = WS.Range("A" & i).Text
          iDay = Split(ColA_Text, "/")(1)
          iMonth = Split(ColA_Text, "/")(0)
          iYear = Split(ColA_Text, "/")(2)
          CurrentDate = DateSerial(iYear, iMonth, iDay)
          DayName = Format(CurrentDate, "dddd")
          WS.Range("B" & i).Value = DayName
     Next i

End Sub

And this is the output of the macro.

Macro output the day name independent of region settings

In the above example we used “mm/dd/yyyy” as the date format. But as I said you can easily change the code to suit other formats. Because you need to change only these three lines to suit with the format.

iDay = Split(ColA_Text, "/")(1)
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)

For example if dates are written in “dd/mm/yyyy” format you can modify the above three lines as follows.

iDay = Split(ColA_Text, "/")(0)
iMonth = Split(ColA_Text, "/")(1)
iYear = Split(ColA_Text, "/")(2)


Get the Day Name of a Date Using VBA

In this lesson you can learn how to get the weekday name of a date. Here is an example worksheet. Column A of this sheet has a list of dates. We are going to get the day name of each date into column B using a macro.

Sample worksheet containing dates

First let’s give our macro a name. I will name the macro as “GetDayName”.

Sub GetDayName()

End Sub

We need a few variables for this job.

Dim WS As Worksheet
Dim i As Long
Dim CurrentDate As Date
Dim DayName As String

Next I will assign Activesheet to the WS variable.

Set WS = ActiveSheet

We have dates from row 2 to row 10. So we need a For Next statement to iterate through each row.

For i = 2 To 10

Next i

Then when the program loops through each row we can assign the date to CurrentDate variable like this.

CurrentDate = WS.Range("A" & i).Value

Next, the day name can be obtained using Format function.

DayName = Format(CurrentDate, "dddd")

Finally we can write the result to column B

WS.Range("B" & i).Value = DayName

Below is the completed macro.

Sub GetDayName()

     Dim WS As Worksheet
     Dim i As Long
     Dim CurrentDate As Date
     Dim DayName As String

     Set WS = ActiveSheet
     For i = 2 To 10
          CurrentDate = WS.Range("A" & i).Value
          DayName = Format(CurrentDate, "dddd")
          WS.Range("B" & i).Value = DayName
     Next i

End Sub

And this is the result produced by the macro.

Macro returned the day name for each date

In this example I entered the dates in dd/mm/yyyy format. Also I have used the same in regional settings. But if I send this file to a user whose regional date format is “mm/dd/yyyy” then this program will output incorrect results. So if you want to use this macro, then you should format the dates in column A similar to your regional settings.

Calculate Duration Between Two Times of Two Adjacent Days

In the last post we learnt how to calculate the duration between two times of the same day. Today let’s look at how to calculate duration between two times of two adjacent days. Let’s consider this sample data. This is a start and end time of work for some employees.

Times are on same day or adjacent days

If you look at the start and end times of the work, you will notice that some employees have ended the work on the same day as started and others have ended the work on the next day. So if we use the code of our previous post it will only give correct results for cases where employees started and ended the work on the same day. Now let’s look at how to develop a solution which suits both of these types.

As usual, first we need to declare a few variables.

Dim WS As Worksheet
Dim StartTime As Date
Dim EndTime As Date
Dim i As Integer
Dim DurationInHours As Double

Let’s assume we have the data in the sheet called “Data”. Then we can assign that sheet to the WS variable as follows.

Set WS = Worksheets("Data")

Next we need a For Next statement to iterate through each row. As we have employee names from 2nd row to 10th row we can write the For Next statement like this.

For i = 2 To 10

Next i

Now when the program loops through each row we can assign values for StartTime and EndTime variables.

StartTime = WS.Range("B" & i).Value
EndTime = WS.Range("C" & i).Value

Then duration can be calculated using the DateDiff function as follows.

DurationInHours = DateDiff("n", StartTime, EndTime) / 60

But now the problem is that the DateDiff function calculates the time difference assuming both times belong to the same day. If the end time is on the next day, then the program returns a negative value. Therefore we can use the sign of the returned value to identify whether end time is on the same day or not. And once we detect an end time which is on the next day, we can correct the returned value easily by adding 24 to it. Here is the reason for adding exactly 24. Even when the end time is on the next day, DateDiff calculates the value assuming the end time is on the morning of the same day. So we can easily fix it by finding the duration in the opposite direction by adding 24 to the returned result.

Also if ending time is on the same day, then we can use the value returned from the DateDiff function as it is.

If DurationInHours < 0 Then
     DurationInHours = DurationInHours + 24
End If
WS.Range("D" & i).Value = DurationInHours

You can see that we have altered the DurationInHours variable only if it is negative. So here is the full code.

Sub CalculateHoursWorked()

     Dim WS As Worksheet
     Dim StartTime As Date
     Dim EndTime As Date
     Dim i As Integer
     Dim DurationInHours As Double
     Set WS = Worksheets("Data")
     For i = 2 To 10
          StartTime = WS.Range("B" & i).Value
          EndTime = WS.Range("C" & i).Value
          DurationInHours = DateDiff("n", StartTime, EndTime) / 60
          If DurationInHours < 0 Then
               DurationInHours = DurationInHours + 24
          End If
          WS.Range("D" & i).Value = DurationInHours
     Next i

End Sub

Now the program calculates the duration correctly for both types.

Duration calculated correctly for same and adjacent days

Calculate Time Difference Using VBA

Sometimes we need to calculate time differences in VBA applications. It is often required when we develop solutions related to employment. Here is an example.

Employee names and start and end time of their work

This sheet contains names of employees and the start and end time of their work. Then we need to calculate the hours worked for each employee. So in this lesson you can learn how to calculate the duration of two times using a macro.

First we need to declare a few variables.

Dim WS As Worksheet
Dim StartTime As Date
Dim EndTime As Date
Dim i As Integer

If the name of the sheet is Sheet1, we can assign that sheet to the WS variable like this.

Set WS = Worksheets("Sheet1")

In this example we have only nine names. We can use a For Next loop to loop through each row.

For i = 2 To 10

Next i

Now in each iteration assign the start and end time to variables StartTime and EndTime respectively.

StartTime = WS.Range("B" & i).Value
EndTime = WS.Range("C" & i).Value

Once the start and end times are assigned, use the VBA function called “DateDiff” to calculate the time difference.

DateDiff("n", StartTime, EndTime)

This function has five arguments. But the last two are optional and not needed for this type of calculation. First argument is the interval. We can give the interval as year, month, day, hour, minute etc. In here we used “n” and it refers to the minutes. So we will get the duration in minutes. Then we can divide the result by 60 to get the values in hours. So the final code should look like this.

WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60

Below is the complete macro.

Sub CalculateHoursWorked()

     Dim WS As Worksheet
     Dim StartTime As Date
     Dim EndTime As Date
     Dim i As Integer
     Set WS = Worksheets("Sheet1")
     For i = 2 To 10
          StartTime = WS.Range("B" & i).Value
          EndTime = WS.Range("C" & i).Value
          WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60
     Next i

End Sub

This is the result of the above code.

Duration between start and end time

You may think why I didn’t use the hour as interval to directly calculate the number of hours like below.

WS.Range("D" & i).Value = DateDiff("h", StartTime, EndTime)

Instead of

WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60

But then the problem is DateDiff function returns only the whole number part disregarding the decimal part. So if we use “h” as the period then we will get below result which is incorrect.

DateDiff function returns only whole number part when hour is used as the interval

And here is the next challenge. What if some employees start work in the evening or night and end it in the next day. Then our program generates incorrect results like this.

When start and end times belongs to two adjacent days

So in my next post I will show you how to calculate the time difference of two adjacent days like that.

Contact Form

Name

Email *

Message *