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.

Create a Masked Password Using VBA

In this lesson you will learn how to create a masked password using VBA. Masked passwords are essential for the security of the data of the user. Because if you enter a password to an unmasked textbox or inputbox then the password will be visible to others around you. But if you use a masked textbox or inputbox then Excel will hide the password with asterisks.

Here is a custom VBA form designed to get the password from a user.

Designed custom userform

But as the textbox is not masked, the password entered will be visible.

Unmasked textbox

So now let’s look at how to mask a password in Excel VBA forms.

First, open the visual basic editor. You can use “Developer” tab or the keyboard shortcut Alt + F11

Can’t see the developer tab in your Excel application? Learn how to show the Developer tab

Next, select the textbox you want to apply the mask to.

Select the password textbox

Go to the properties window and find “PasswordChar” field

Find PasswordChar field - properties window

Now select the character you want to show when the user types password. The asterisk(*) is the typical symbol used for this.

Use asterisk as PasswordChar

All set with the form. Now asterisks will be displayed when you enter characters in the textbox.

Asterisks displayed instead of real characters

Retrieve password from the textbox

Now you learnt how to hide passwords with asterisks in Excel VBA textboxes. But do you know how to retrieve the password entered by the user? You can use the below code to print the password in the immediate window. cmdOK is the name of the OK button used in the form.

Private Sub cmdOk_Click()

     Dim Pwd As String

     Pwd = frmPwd.TextBox1.Value
     Debug.Print Pwd

End Sub

Now when the user clicks the OK button after entering the password, the program will assign that to the variable Pwd of type string. Then you can use that variable for the next steps such as password validation or password matching.

Hide password with asterisks in an inputbox

You can use inputbox to get input from users. But is it possible to mask a password with asterisks in an inputbox? Unfortunately you can’t use the above method for an inputbox. But this page explains how to use a private InputBox to mask the password with asterisks.

Private InputBox to mask the password with asterisks

Get Row Number of a Matching Value

In this lesson you can learn how to find the row number of a matching value. This page explains about 3 ways to do this. You can adapt a solution according to your preference. These techniques will be very helpful when you develop applications like data entry systems. Because in these types of applications you may need to show the matching result to the user. For example you might want to let the user search with a text using a textbox or a inputbox.

I will use this sample worksheet to explain these three methods.

Sample excel sheet with data

Suppose user search for the code “C00KLCMU14”. So the program should return the matching row number as 14.

Method 1

'Row number of matching value
Sub MatchingRowNumber()

Dim WS As Worksheet
Dim MatchingRow As Long
Dim i As Long
Dim SearchedValue As String

Set WS = Worksheets("Sheet1")

SearchedValue = "C00KLCMU14"

For i = 1 To 10000
     If StrComp(WS.Range("B" & i).Value, SearchedValue, vbTextCompare) = 0 Then
          MatchingRow = i
          Exit For
     End If
Next i

MsgBox MatchingRow

End Sub

Program will show the row number of the matching value in a message box

In the above macro the For Next loop is used assuming there are 10,000 rows of data. But if you have data up to x rows then you should replace For i = 1 to 10000 with For i = 1 to x

If no match is found, the message box will output 0 because zero is the default value of the Long data type.

However there is one problem with this solution. It is that if you have data in a higher number of rows in your Excel sheet then the program will take considerable time to output the result. But we can avoid this problem if we use arrays.

Method 2

Assume we have one million rows of data in our sheet. Here is how you can use an array to get the required result without delay.

Sub MatchingRowNumber_ArraySolution()

Dim WS As Worksheet
Dim MatchingRow As Long
Dim i As Long
Dim SearchedValue As String
Dim DataArr() As Variant

Set WS = Worksheets("Sheet1")

DataArr = WS.Range("A1:E1000000")
SearchedValue = "C00KLCMU14"

For i = 1 To 1000000
     If StrComp(DataArr(i, 2), SearchedValue, vbTextCompare) = 0 Then
          MatchingRow = i
         Exit For
     End If
Next i

MsgBox MatchingRow

End Sub

This is an easy way to write a range to an array.

DataArr = WS.Range("A1:E1000000")

Things you need to remember when creating an array using the above method.

  • This method always creates a multidimensional array.
  • Indexes of both dimensions will start from 1.

Want to learn more about arrays? Check these lessons.

Multidimensional Arrays in VBA
Quick Multidimensional Array from Excel Range
Quickly Write Multidimensional Array to Excel Range

So far we have discussed two methods of finding the row number of a matching value. But sometimes cells can contain spaces before or after the values. So if there are spaces like that, then the program will not be able to find the matching value. To avoid this issue we can use the Trim function to remove unwanted spaces before or after the values.

This is how you can use the Trim function for the first method.

Replace

If StrComp(WS.Range("B" & i).Value, SearchedValue, vbTextCompare) = 0 Then

With

If StrComp(Trim(WS.Range("B" & i).Value), SearchedValue, vbTextCompare) = 0 Then

And here is how you can use the Trim function for the second method.

Replace

If StrComp(DataArr(i, 2), SearchedValue, vbTextCompare) = 0 Then

With

If StrComp(Trim(DataArr(i, 2)), SearchedValue, vbTextCompare) = 0 Then

Also if you get the SearchedValue from the user from a method such as input box or textbox then you can use the Trim function to remove unwanted spaces from SearchedValue as well.

If StrComp(Trim(DataArr(i, 2)), Trim(SearchedValue), vbTextCompare) = 0 Then

Method 3

Now let’s look at the third method to find the row number of a matching value. You can use the Find method to return the row number of the matching value.

Sub MatchingRowNumber_FindFunction()

Dim WS As Worksheet
Dim MatchingRow As Long
Dim SearchedValue As String

Set WS = Worksheets("Sheet1")
SearchedValue = "C00KLCMU14"
MatchingRow = WS.Cells.Find(What:=SearchedValue, After:=Cells(1, 1), LookIn:=xlFormulas, _
     LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
     MatchCase:=False, SearchFormat:=False).Row

MsgBox MatchingRow
End Sub

This will output the correct row number if there is a match. However if there is no match then the program will throw an error.

Program will throw an error if can’t find a match

Clicking debug will highlight the line where error occurred

To avoid this error, we can use the error handling technique as follows. So if there is no match, the program will output 0.

Sub MatchingRowNumber_FindFunction()

Dim WS As Worksheet
Dim MatchingRow As Long
Dim SearchedValue As String

Set WS = Worksheets("Sheet1")
SearchedValue = "C00KLCMU14"

On Error Resume Next
MatchingRow = WS.Cells.Find(What:=SearchedValue, After:=Cells(1, 1), LookIn:=xlFormulas, _
     LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
     MatchCase:=False, SearchFormat:=False).Row
If Err.Number <> 0 Then
     If Err.Number = 91 Then
         MatchingRow = 0
     Else
         MsgBox "Unknown Error"
         On Error GoTo 0
         Exit Sub
     End If
End If
On Error GoTo 0

MsgBox MatchingRow
End Sub

However this method will not work if cells contain extra spaces before or after the values.

Access Formula Bar Using Keyboard - Excel 2013

In the last post we discussed how to activate the edit mode for the active cell using a keyboard shortcut. From this post you can learn how to access the formula bar in Excel 2013 using the keyboard. Here are the steps to follow.

Click on the “File” menu.

Click on the File menu

Then click on the “Options”

Click on the Options

Go to “Advanced” tab and remove tick from the “Allow editing directly in cells”

Go to Advanced tab and untick Allow editing directly in cells

Now the cursor will jump to the formula bar when you press F2 on the keyboard.

Cursor jumps to the formula bar

Activate Edit Mode For The Active Cell - Keyboard Shortcut

In the last post we discussed how to copy paste data to an Excel sheet from a different source without formatting. I explained three methods to do that. One method was double clicking on the cell and paste. However you can also activate this edit mode using the keyboard as well.

Below is the shortcut key to activate the edit mode.

This is not only useful when copy-pasting data without formatting but also when editing formulas or content of the active cell. For example if you want to edit a formula or a text inside a selected cell, you can activate the edit mode and then use left and right arrow keys to move the cursor to where you want. When you press F2, Cursor will initially move to the end of the text, value or formula. But then you can use left and right arrow keys to move the cursor along. But if you press the left or right arrow without activating the edit mode, Excel will send you to the next cell at left or right respectively.

.

How to Copy Paste Data to an Excel Sheet From a Different Source (Without Formatting)

Sometimes we need to copy data from various sources to Excel sheets. For an example you may want to copy data from a webpage into an Excel sheet. If you ever did that, you should have noticed that when you paste data to Excel, data is pasted with the source formatting.

Let's see an example. This is the default formatting of an Excel sheet when I create a blank workbook.

Default formatting

But if I copy one of the post titles from this website (excelvbasolutions.com) to this sheet, then it will be pasted like this.

Data pasted with the source formatting

As you can see formatting is totally different from the current formatting of the sheet. Even if you have already applied different formatting to the Excel sheet than default, the same thing will happen.

So how do we paste content from different sources to an Excel sheet without the source formatting. There are three ways to do this. First method is you can paste the content on the formula bar.

Click on the formula bar and paste the data

Then data will be pasted without source formatting.

The second method is double clicking on the cell and pasting the content. If you double click on a cell a cursor will appear inside the cell like this.

Cursor will appear inside the cell

Now you can press Ctrl+v from the keyboard or right click and select paste.

If you use one of the above two methods you can only paste data into one cell. But sometimes you may want to paste a large amount of data to different cells at once. For example you might want to copy paste a complete webpage to an Excel worksheet. You can use this third method to paste data to one cell or multiple cells.

Here are the steps of the third method. First copy the data from the source. Then select the cell you want to paste data from. Next, right click and select the “Match destination format” option under the “Paste options”.

Right click and select Match destination format

If User Close The VBA Userform With x

Today I’m going to show you a solution for a challenge faced by lots of newbie developers. Here is the challenge. When we create VBA forms, we often create a Close or Cancel button to close the form.


Form containing a close button

So users can use that button to close the form. Also sometimes we add some code to that Close button to carry out some tasks while closing the form. Below is an example.

Private Sub cmdClose_Click()
     Unload Me
     Call UpdateReport
     ThisWorkbook.Save
End Sub

So this Close button calls a sub procedure called “UpdateReport” and then saves the file while closing the form.

Suppose there is a VBA application which has a dashboard or report. Then the dashboard or report should be updated when the new data entered through the form. But the program doesn't need to update the dashboard/report on every single entry. Instead the program can update the dashboard/report while closing the form. Because the user can view the dashboard/report only after he closes the form. If you develop the program to update them on every entry then the program will have to call the update sub procedure every time when a user enters a data set. This is the same for the file saving function. You don’t need to save the file whenever the user enters a data set. Instead you can save it when the user closes the form.

And sometimes developers create VBA applications in Excel in such a way that users can only interact with forms. Developers might use the worksheets to store the data. But users will be restricted to only interact with forms. In such programs developers use the close button to close the entire workbook.

For these reasons developers create separate buttons to close the form. But then the problem is that some users use the default close (x) button at the top of the VBA form.

Default close x button of a VBA form

If a user clicks that button, the form will be closed. But the tasks in the Close or Cancel button will not be completed. So how do we solve this problem? We can use the Terminate event of the form to solve this. What you should do is place the code (you want to run when closing the form) inside the Terminate event of the form. Then write “Unload Me” in the Close or Cancel button you created. Then when a user clicks the Close or Cancel button program will close the form. It will also trigger the terminate event of the form. Therefore code inside the Terminate event will also be executed. Here is an example.

Private Sub cmdClose_Click()
     Unload Me
End Sub

Private Sub UserForm_Terminate()
     Call UpdateReport
     ThisWorkbook.Save
End Sub

Get Selected Cell Using VBA Macro

In some VBA macros, it is needed to get the selected cell (active cell) automatically. This may be required when developing simple as well as advanced macros. In a simple macro you might need to let the user select a cell before running the macro. Then macro will perform different tasks depending on the cell selected by the user. Also if you are developing advanced macros such as Games in Excel, then you may need to identify the active cell to make the game functional.

So now let’s see how to get the selected cell using VBA. I have selected cell B3 on my Excel sheet.

Cell B3 is selected

We can easily get the address of the selected cell like this.

Sub GetSelectedCellAddress()

     MsgBox ActiveCell.Address

End Sub

Address of the Active cell will be shown in the message box if we run the above macro.

Address of the selected cell is shown in the message box

Also you can use the Split function to extract the column letter and the row number from the above result.

Learn how to use Split Function

However there is another way to get the row number of the active cell easily.

Sub GetSelectedCellRowNo()

     MsgBox ActiveCell.Row

End Sub

Message box shows the row number of the active cell

You can use a similar way to get the column. But it will return the column number rather than the letter.

Sub GetSelectedCellColumnNo()

     MsgBox ActiveCell.Column

End Sub


Message box shows the column number of the active cell

Also see

    Get Selected Rows Using VBA Macro
    How to find the name of an active chart using VBA
    Save a Workbook as a Single PDF Using VBA

How to Copy Range Into a New Workbook Using VBA

In this lesson you can learn how to copy a range from an existing workbook to a new workbook. Existing workbook should stay open when we run the program. And we are going to paste the range to a new workbook. So the program will create a workbook automatically before paste. If you want to copy a range from a closed workbook to a new workbook then first you need to open the closed workbook using VBA.

Macros included in this lesson

  1. Macro to copy range with formatting
  2. Macro to copy range with original widths
  3. Macro to copy range as values (two macros using two different approaches)



So let’s consider this sample workbook. Suppose the name of the workbook is “Data File.xlsx” and the name of the sheet is “Sheet1”. So we have the data in range B5:E12

Workbook containing the data to be copy pasted

You can copy ranges from any Excel file format such as .xlsx, .xls, .xlsm etc. But you can save code only inside the .xlsm files. Or else add the macro to the personal macro workbook.

How To Record Macros In Personal Macro Workbook

Now what we are going to do is create a new workbook and copy paste this data into the range starting from cell A1.

First create two variables to hold the workbooks.

Dim WB_SourceFile As Workbook
Dim WB_New As Workbook

Then assign the Data File.xlsx worksheet to the variable WB_SourceFile

Set WB_SourceFile = Workbooks("Data File.xlsx")

Copy range B5:E12 from sheet1 of the Data File.xlsx

WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy

Create a new workbook and assign it to the variable WB_New

Set WB_New = Workbooks.Add

Select cell A1 of the activesheet of the newly created workbook.

WB_New.ActiveSheet.Range("A1").Select

Now you can paste the data.

ActiveSheet.Paste

So this is the final sub procedure.

Sub CopyPasteRange()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     ActiveSheet.Paste

End Sub

Here is the result of the above macro.

Range was pasted to the new workbook

As you can see the width of columns of the new workbook differ from the original file. However we can use the PasteSpecial method to paste the values with original widths. Use the below macro if you want to paste the data with original column widths.

Sub CopyPasteRange_OriginalWidth()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
          SkipBlanks:=False, Transpose:=False
     ActiveSheet.Paste

End Sub

In above examples range was copy pasted with formatting like colors, outlines etc. But you can also copy paste only values from the original workbook to the new workbook. This is how you can do it.

Sub CopyPasteRange_PasteValues()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False

End Sub

This is the result you will get.

Copy paste only values to the new file

Sometimes you may want to copy only a few values from an existing workbook to a new workbook. Here is an example.

Sheet of a existing workbook containing few values

Suppose this is the Sheet2 of the Data File.xlsx file. If we want to copy paste these two rates in the cell A1 and A2 of a new workbook, then we can do it easily using the Range.Value property as follows.

Sub CopyPasteValues()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Value = WB_SourceFile.Worksheets("Sheet2").Range("C4").Value
     WB_New.ActiveSheet.Range("A2").Value = WB_SourceFile.Worksheets("Sheet2").Range("C5").Value
End Sub

Values pasted to the new workbook

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.

Contact Form

Name

Email *

Message *