Trending February 2024 # How To Use Screenupdating Application In Vba? # Suggested March 2024 # Top 3 Popular

You are reading the article How To Use Screenupdating Application In Vba? updated in February 2024 on the website Kientrucdochoi.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 How To Use Screenupdating Application In Vba?

Excel VBA ScreenUpdating

When we run any VBA macro with a huge set of code, the code completes but in the background we get processing data in the form of running or waiting for the pointer. And once it is done, we can only see the output. The regular way is not the proper way to see how the values are updating by running code. For example, let’s say we have written the code to generate some numbers in 100 cells. Now that code could simply generate the output without showing how the numbers are getting printed. To resolve this, we have VBA ScreenUpdating. ScreenUpdating in VBA helps us the see how the code is generating the output. This could be numbers, text, alphabets or combination. Once we run the code loop, by VBA ScreenUpdating we could able to see the numbers getting generated.

How to Use ScreenUpdating Application in Excel VBA?

We will learn how to use the ScreenUpdating application in Excel by using the VBA Code.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA ScreenUpdating Excel Template here – VBA ScreenUpdating Excel Template

VBA ScreenUpdating can be noticed while we run the code or macro. While the code is running, we will be able to see how our screen is getting updated by the values generated by written code in VBA. Instead of seeing the older waiting sign, by the help of VBA ScreenUpdating we can see how the screen is updating the values of output by VBA ScreenUpdating. Now by that, we can also see the name of the article itself defines it work, VBA ScreenUpdating.

VBA ScreenUpdating – Example #1

In this example, we will see a simple code for updating the screen. For this, we need some cells with numbers, as shown below. For this, follow the below steps:

Step 1: Open a Module from the Insert menu tab as shown below.

Step 2: Now write the subprocedure in the name of VBA ScreenUpdating as shown below.

Sub

Screen_Update1()

End Sub

Step 3: We will now copy the numbers from cell A1: A3 to any other cells. Let’s say that cell be C1: C3 with the help of the below code.

Code:

Sub

Screen_Update1() Range("A1").Copy Range("C1") Range("A2").Copy Range("C2") Range("A3").Copy Range("C3")

End Sub

Step 4: Now if we run this code, we could only get the output which is copied values from column A to C. Now we will use ScreenUpdating application as shown below.

Code:

Sub

Screen_Update1() Application.ScreenUpdating Range("A1").Copy Range("C1") Range("A2").Copy Range("C2") Range("A3").Copy Range("C3")

End Sub

Step 5: We put an equal sign to select the Boolean values which are TRUE or FALSE. We will select FALSE to stop the screenupdating.

Code: 

Sub

Screen_Update1() Application.ScreenUpdating =

False

Range("A1").Copy Range("C1") Range("A2").Copy Range("C2") Range("A3").Copy Range("C3")

End Sub

This could be seen more clearly if we have a huge set of numbers.

VBA ScreenUpdating – Example #2

Let’s see another example for ScreenUpdating. This time let’s consider the number from 1 to 20 from cell A1 to A20 as shown below.

For using screenupdating application, follow the below steps:

Step 1: Write the subprocedure of VBA Screenupdating as shown below.

Code:

Sub

Screen_Update2()

End Sub

Step 2: Now write the code to select the range cell from A1 to A20 and copy them at B1 to F20 as shown below. In a similar fashion as we saw in example-1.

Code:

Sub

Screen_Update2() Range("A1:A20").Copy Range("B1:F20")

End Sub

Step 3: To apply the screenupdating application, we will again use a similar line of code which we have seen in example-1.

Sub

Screen_Update2() Application.ScreenUpdating =

False

Range("A1:A20").Copy Range("B1:F20")

End Sub

The above-used application ScreenUpdating as FALSE will allow us to see how the VBA code updates the screen. As we have more numbers so there are chances we can see screenupdating.

VBA ScreenUpdating – Example #3

There is another way to see the screen getting updated. This could be done with the help of the For-Next Loop. In this example, we will print the numbers in a Row and Column combo matrix. For this, follow the below steps:

Step 1: Write the subprocedure of VBA ScreenUpdating.

Code:

Sub

Screen_Updating3()

End Sub

Step 2: Now declare the 2 variables for Row and Columns separately as data type Long.

Code:

Sub

Screen_Updating3()

Dim

RowCount

As Long

Dim

ColumnCount

As Long

End Sub

Step 3: Now declare another variable which we will use as a reference to start the numbers.

Code:

Sub

Screen_Updating3()

Dim

RowCount

As Long

Dim

ColumnCount

As Long

Dim

MyNumber

As Long

End Sub

Step 4: Now give the reference number from which position we want to start the counting. Here we are giving it as 0.

Code:

Sub

Screen_Updating3()

Dim

RowCount

As Long

Dim

ColumnCount

As Long

Dim

MyNumber

As Long

MyNumber = 0

End Sub

Code:

Sub

Screen_Updating3()

Dim

RowCount

As Long

Dim

ColumnCount

As Long

Dim

MyNumber

As Long

MyNumber = 0

For

RowCount = 1

To

50

End Sub

Step 6: To continue the loop give MyNumber variable +1.

Code:

Sub

Screen_Updating3()

Dim

RowCount

As Long

Dim

ColumnCount

As Long

Dim

MyNumber

As Long

MyNumber = 0

For

RowCount = 1

To

50

For

ColumnCount = 1

To

50 MyNumber = MyNumber + 1

End Sub

Step 7: Now select the Row and Column variables in Cell function. And then select the values stored in them and assign them to MyNumber variable.

Code:

Sub

Screen_Updating3()

Dim

RowCount

As Long

Dim

ColumnCount

As Long

Dim

MyNumber

As Long

MyNumber = 0

For

RowCount = 1

To

50

For

ColumnCount = 1

To

50 MyNumber = MyNumber + 1 Cells(RowCount, ColumnCount).Select Cells(RowCount, ColumnCount).Value = MyNumber

End Sub

Step 8: Now close the Loop by Next. Include Row and Column variables which we defined and used in the For-Next loop.

Code:

Sub

Screen_Updating3()

Dim

RowCount

As Long

Dim

ColumnCount

As Long

Dim

MyNumber

As Long

MyNumber = 0

For

RowCount = 1

To

50

For

ColumnCount = 1

To

50 MyNumber = MyNumber + 1 Cells(RowCount, ColumnCount).Select Cells(RowCount, ColumnCount).Value = MyNumber

Next

ColumnCount

Next

RowCount

End Sub

Step 9: Now we haven’t inserted the Screenupdating application yet. Now insert the Screenupdating application as FALSE before the start of the loop and as TRUE at the end of the loop as shown below.

Code:

Sub

Screen_Updating3()

Dim

RowCount

As Long

Dim

ColumnCount

As Long

Dim

MyNumber

As Long

Application.ScreenUpdating =

False

MyNumber = 0

For

RowCount = 1

To

50

For

ColumnCount = 1

To

50 MyNumber = MyNumber + 1 Cells(RowCount, ColumnCount).Select Cells(RowCount, ColumnCount).Value = MyNumber

Next

ColumnCount

Next

RowCount Application.ScreenUpdating =

True

End Sub

Now compile the complete code step by step by pressing the F8 function key and then run it if no error is found. We will see, how each cell of selected rows and columns is getting updated with the values stored in it.

Pros of Excel VBA ScreenUpdating

It is quite helpful in seeing how the screen is getting updated with the value stored in the loop.

We can use Screenupdating if we want to switch between worksheets and workbooks.

We can use any range of numbers.

Things to Remember

We use the insert For-Next loop as the frame or first, we can satisfy the condition of For loop and then close it by Next.

VBA ScreenUpdating is quite useful and visible if we are using a huge set of numbers.

Once done, do save the excel file as Macro enables excel format.

VBA ScreenUpdating can also be used for creating a macro through which we can send emails.

Recommended Articles

This is a guide to VBA ScreenUpdating. Here we discuss how to use ScreenUpdating application in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

You're reading How To Use Screenupdating Application In Vba?

How To Use Vba Like Function In Excel?

Introduction to VBA Like

VBA Like is used when we have some special characters, spaces in the string and we need to get exact or most relevant output from that word. VBA Like allows us to match the pattern in alphabetical sequence so that if any word contains some special characters then with the help of VBA Like we can complete word. We can also determine if that string is in the proper format or not.

In VBA Like, we have some conditions on that basis we can define what we need to get and how we need to fill the space of missing blank words.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Question Mark (?) – By this, we can match only one character from the string. Suppose we have string “TAT” and the pattern is “T?T” then VBA Like will return TRUE. If we have the string as “TOILET” and the pattern is still “T?T” then VBA Like will return FALSE.

Asterisk (*) – By this, we can match 0 or more characters. Suppose we have the string as “L**K” then VBA Like will return TRUE.

[Char-Char] – By this, we can match any single character in the range Char-Char.

[!Char] – By this, we can match any single character but not in the list.

[!Char-Char] – By this, we can match any single character but not in Char-Char.

How to Use VBA Like Function in Excel?

We will learn how to use a VBA Like function with a few examples in excel.

You can download this VBA Like Excel Template here – VBA Like Excel Template

Example #1 – VBA Like

To find if the available string is TRUE or FALSE for VBA Like first, we need a module. For that,

Step 2: Now in the opened window of Module in VBA, write the subcategory of VBA Like as shown below.

Code:

Sub

VBA_Like()

End Sub

Step 3: Now first, we will define a variable A as String as shown below. Here, we can use the Long variable as well as it too allows to store any text value in it.

Code:

Sub

VBA_Like()

Dim

A

As String

End Sub

Step 4: Next, we will assign a word to variable A. Let’s consider that word as “LIKE”.

Code:

Sub

VBA_Like() Dim A As String A = "Like"

End Sub

Step 5: Now with the help of If-End If loop we will create VBA Like condition.

Code:

Sub

VBA_Like()

   Dim

A

As String

A = "Like" If

End If

End Sub

We will use the above code in the upcoming example as well directly.

Step 6: Now in If-End If loop write the condition as variable A like “L?KE” is a TRUE condition then give us Yes in a message box or else give us No in the message box for FALSE.

Code:

Sub

VBA_Like()

   Dim

A

As String

A = "Like"

If

A

Like

"L?KE"

Then

MsgBox "Yes"

Else

MsgBox "No"

End If

End Sub

We have kept a question mark in the second position. But this can be kept anywhere in whole string.

We will get the message box as NO. Which means, the word which chose “LIKE” in variable A may have other alphabets in place of a question mark and instead of only “I”.

Example #2 – VBA Like

In this example, we will implement Asterisk (*)

Step 1: Now we will use the same code structure which we have seen in example-1 with the same word “LIKE”.

Code:

Sub

VBA_Like2()

  Dim

A

As String

A = "LIKE" If

End If

End Sub

Step 2: As we know that with Asterisk we have a match 0 or more characters from any string. So in If-End If loop we will write, if VBA Like matches “*Like*” is TRUE then we will get the message as Yes, else we will get No if it is FALSE.

Code:

Sub

VBA_Like2()

  Dim

A

As String

A = "LIKE"

If

A

Like

"*Like*"

Then

MsgBox "Yes"

Else

MsgBox "No"

End If

End Sub

Step 3: Again compile the complete code and run it. We will get the message as NO because VBA Like is failed to match any alphabet apart from defined string “Like”.

Step 4: Now if we change the string A from “Like” to “Like Wise” and try to match any letter from the string, let’s say it is “W” in asterisk then what will we get?

As said above, we have used “LIKE WISE” as our new string.

Code:

Sub

VBA_Like2()

  Dim

A

As String

A = "LIKE WISE"

If

A

Like

"*W*"

Then

MsgBox "Yes"

Else

MsgBox "No"

End If

End Sub

Step 5: Now compile the code and run it again. We will get the message as YES. Which means that VBA Like is able to match any alphabet from our string “LIKE WISE”.

In the same manner, if we match any other letter from “LIKE WISE” we may get the same results.

Example #3 – VBA Like

In this example, we will see, how Char-Char works in matching the strings of characters.

Step 1: For this also, we will use the same frame of code which we have seen in example-2 for defined variable A as “LIKE WISE”.

Code:

Sub

VBA_Like4()

  Dim

A

As String

A = "LIKE WISE" If

End If

End Sub

Step 2: In if-End If loop, write the condition VBA Like matches letters from I to K (In Asterisk and Char) then it will be TRUE and give us the message as YES. If not then it will be FALSE and we will get the message as NO.

Code:

Sub

VBA_Like4() Dim A As String A = "LIKE WISE"

If

A

Like

"*[I-K]*"

Then

MsgBox "Yes"

Else

MsgBox "No"

End If

End Sub

Step 3: Again compile the code and run it. We will see, VBA Like is able to match the characters from letter I to K and gave us the message as YES.

Pros and Cons of VBA Like

In a set of database where it is quite frequent to see such special characters, there using VBA Like will allow us to frame hidden words.

As it has very limited application, so it is very rarely used.

Things to Remember

We can compare and match only strings. Any other variables such as integers, double cannot be used.

It is not recommended to record a macro on VBA Like. As we don’t know any excel function on it. And also, doing this process with other ways may result in getting incorrect match results.

Though VBA Like is very rarely used, but the kind of output it gives may not be accurately given by other functions and command of the same type.

Save the file in Macro Enable Excel file format only. This format is mainly used when we create any macro.

Recommended Articles

This is a guide to VBA Like. Here we discuss how to use Excel VBA Like function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Use Go Sub Return Statement In Vba?

VBA Return Statement

In this article, we will see an outline on Excel VBA Return. It is a statement in VBA which is used for the user-defined functions with the GoSub statement. This statement can also be used outside of a subprocedure when we are using a user-defined function. And when we are using the GoSub statement with the return statement then both GoSub and return statements should be in the same procedure. Now let us discuss what the difference between these two procedures is. First, we will begin with the GoSub statement. In this statement, we stay in the same procedure but move to another statement and get a result with some lines execution of code and then return to the original procedure.

The next is the use of the return statement in a user-defined function. In the user-defined function, a function is created which is returns a value through return statement. We will discuss both types of use of the return statement through some examples.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Use Go Sub Return Statement in VBA?

We will learn how to use Go Sub Return Statement in Excel by using the VBA Code.

You can download this VBA Return Excel Template here – VBA Return Excel Template

Example #1 – VBA Return Statement

Let us first discuss how Return with GoSub Statement works in VBA. How do we execute them and what we will learn in this example?. For this, follow the below steps:

Code:

Sub

Example1()

End Sub

Step 3: Now let us send the execution to another line by the GoSub Statement but within the same procedure.

Code:

Sub

Example1()

GoSub

Sample

End Sub

Step 4: Now let us exit the subprocedure, for now, let us keep this in mind exit sub and end sub are two different.

Code:

Sub

Example1()

GoSub

Sample

Exit Sub

End Sub

Step 5: Now we can perform some execution of lines of code for the subroutine.

Code:

Sub

Example1()

GoSub

Sample

Exit Sub

Sample: MsgBox "This is a sample document"

End Sub

Step 6: Now we will use the return statement to move back to the original procedure.

Code:

Sub

Example1()

GoSub

Sample

Exit Sub

Sample: MsgBox "This is a sample document"

Return

End Sub

Step 7: When we press F8 we can see how this procedure works step by step.

Now we can see in the steps that process moved to the line of Sample subroutine when the compiler was on the Gosub Statement and it again moved back to exit sub after giving us the result to Exit substatement.

Example #2 – VBA Return Statement

In the above example, we use a single subroutine and what happens if there are multiple subroutines with multiple GoSub and multiple Return statements. Let us find out in example 2. For this, follow the below steps:

Step 1: We will proceed with the same module just with another subprocedure.

Code:

Sub

Example2()

End Sub

Code:

Sub

Example2()

GoSub

Sample1

GoSub

Sample2

End Sub

Step 3: Now let us exit the sub and define what those subroutines will do.

Code:

Sub

Example2()

GoSub

Sample1

GoSub

Sample2

Exit Sub

Sample1: MsgBox "This is first Execution"

Return

Sample2: MsgBox "This is second Execution"

Return

End Sub

Step 4: Run this code by hitting F5 or Run button.

In this example, Code worked in the same way, the compiler went to GoSub Sample1 and executed the lines of code for sample1 then it returned it to GoSub Sample2 and then again went to execute the lines of code for sample2 and then the return statement took it back to the exit substatement.

Example #3 – VBA Return Statement

Let us use a somewhat realistic example for the GoSub statement such as for addition. For this, follow the below steps:

Step 1: Declare a subprocedure for example 3.

Code:

Sub

Example3()

End Sub

Step 2: Define two integer variables and ask the user to provide input as integers.

Code:

Sub

Example3()

Dim

i

As Integer

, b

As Integer

i = InputBox("Enter first number") b = InputBox("Enter second number")

End Sub

Code:

Sub

Example3()

Dim

i

As Integer

, b

As Integer

i = InputBox("Enter first number") b = InputBox("Enter second number")

GoSub

Addition MsgBox "Execution Complete"

Exit Sub

Addition: MsgBox i + b

End Sub

Step 4: Now we can use the return statement to go back and inform that execution is complete.

Code:

Sub

Example3()

Dim

i

As Integer

, b

As Integer

i = InputBox("Enter first number") b = InputBox("Enter second number")

GoSub

Addition MsgBox "Execution Complete"

Exit Sub

Addition: MsgBox i + b

Return

End Sub

Step 5: Run this code by hitting F5 or Run button placed at the uppermost ribbon of Visual Basic Editor.

Example #4 – VBA Return Statement

For this example look at the below example of code as follows.

Code:

Sub

Example4()

Dim

i

As Integer

, k

As Integer

, area1

As Integer

i = 2 k = 3 area1 = area(i, k) MsgBox area1

End Sub

Public Function

area(x

As Integer

, y

As Integer

)

As Integer

area = x * y

End Function

In this code, there is no return statement used but it still returns the function with a value.

Explanation of VBA Return

We have seen why we use the return statement,  in both of the cases with the use of GoSub statement and User-defined functions. However, there is a slight difference. In the GoSub function, we used a Return statement so that the compiler can return to the next code after the subroutine, and in user-defined functions return statement returned the value to be generated by the function.

We have seen two different methods of using the Return statement through the examples above.

Method 1: The first method is the use of GoSub statement with the return statement.

Method 2: The second method is to use return values in a user-defined function.

Things to Remember

There are few things that we need to remember about the Return statement in VBA and they are as follows:

The return statement is placed where the execution of codes for the procedures is completed.

For the GoSub statement, the GoSub and return statement should be in the same procedure.

For User-defined functions, the values are returned using the function name.

There can be more than one return statement in the same procedure.

Recommended Articles

This is a guide to the VBA Return. Here we discuss how to Use Go Sub Return Statement in Excel VBA along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

How To Use Object In Excel Vba With Examples?

VBA Object

In Microsoft Excel, a VBA Object can contain one or more than one object. Such as a single workbook can have one or more than one worksheet. Workbook, Worksheet, Range, and cells are the objects in Excel. Each object has its own properties. And they all have a different method of application. Let say the workbook is the main object which has 2 worksheets in it. Those 2 worksheets will be its child object. One of the sheets has one range, so that sheet will become the main object, and the range will be its child object.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Use Object in VBA

Let’s see the examples of object in Excel VBA.

You can download this VBA Object Excel Template here – VBA Object Excel Template

Example #1 – VBA Object

It can be written in many ways. Suppose we need to print any text in a cell, so this can be done in various ways. We can directly use the range value to that cell. This direct method is not a part of VBA Object but a process of performing a task that could be done by VBA Object as well. For this:

Step 1: Go to VBA and insert a Module from the Insert menu option as shown below.

Step 2: Now write the Sub Category of performed function, like VBA Object, in any other name as per your choice, as shown below.

Sub

VBAObject2()

End Sub

Step 3: Now select the range function considering any cell, let’s say cell B3 with Value as shown below.

Code:

Sub

VBAObject2() Range("B3").Value =

End Sub

Step 4: Now add text or word in that range cell as shown below.

Code:

Sub

VBAObject2() Range("B3").Value = "VBA Object"

End Sub

Step 5: Now, compile the code and run it by pressing the play button located below the menu bar.

Example #2 – VBA Object

This is the normal way of printing text to any cell. How we will see how the same process can be done when we use VBA Object. For this, we will need another fresh module. And in that,

Step 1: Go to VBA and insert a Module from the Insert menu option as shown below.

Step 2: Write the Sub Category of VBA Object as shown below.

Code:

Sub

VBAObject1()

End Sub

Step 3: Here, we will see the complete VBA Object from the main to a child category. First, select the currently opened workbook with the help of the command ThisWorkBook with Application, as shown below. This will select the workbook which is actually opened and last selected.

Code:

Sub

VBAObject1() Application.ThisWorkbook

End Sub

Step 4: Now select the sheet which is currently opened in the workbook, or we can write the name of the worksheet as well. Here, we have written the name of sheet Sheet1 in inverted commas, as shown below.

Code:

Sub

VBAObject1() Application.ThisWorkbook.Sheets ("Sheet1")

End Sub

Step 5: Now comes the range. Select the range of the cell where we need to print or insert the text. Here we are selecting cell B4, as shown below.

Code:

Sub

VBAObject1() Application.ThisWorkbook.Sheets("Sheet1").Range("B4").Value

End Sub

Code:

Sub

VBAObject1() Application.ThisWorkbook.Sheets("Sheet1").Range("B4").Value = "VBA Object"

End Sub

Step 7: Now, compile and run the code. We will see cell B4 has the text “VBA Object”.

This complete process is called VBA Object. In which we have first selected the main object, i.e. Workbook, which has its child object, i.e. Sheet1, and that has another child object range, i.e. cell B4.

Example #3 – VBA Object

There are many different ways to add text to any cell. One can be with Excel VBA Object, and others can be without it. In these categories, again, there are many ways to do it. Let’s see another way to add VBA Object. For this, we will need a module.

Step 1: In that module, add a subcategory; better make it with sequence number as shown below.

Code:

Sub

VBAObject3()

End Sub

Step 2: Select the worksheet by adding the name of the current worksheet, which is Sheet1, as shown below.

Code:

Sub

VBAObject3() Worksheets("Sheet1").

End Sub

Step 3: Now, add the range of the cell where we want to see the output, as shown below.

Code:

Sub

VBAObject3() Worksheets("Sheet1").Range("A3").Value

End Sub

Step 4: At last, give it a value that we can see once we run the code. We are considering the same text as seen in example 1.

Sub

VBAObject3() Worksheets("Sheet1").Range("A3").Value = "VBA Object"

End Sub

Step 5: Now run the code. We will see; cell A3 got the text which we wanted to add there, as shown below.

In this process, we have directly added the worksheet. So Worksheet will become our Object, and Range will become its child object.

Step 6: There is another way to perform the same task. In the bracket of the worksheet, instead of writing the sheet name, we can write the sequence of the sheet, which is 1, as shown below.

Code:

Sub

VBAObject3()

'

Worksheets("Sheet1").Range("A3").Value = "VBA Object"

Worksheets(1).Range("B3").Value = "VBA Object"

End Sub

Step 7: Now run the modified code. We will see cell B3 got the same text VBA Object as cell A3, as shown below.

By keeping both the code in line, we can see and compare the changes we made. In another way,

Step 8: Instead of the Worksheet function, if we use the Sheet with sequence and selecting cell C3 as range as shown below.

Code:

Sub

VBAObject3()

'Worksheets("Sheet1").Range("A3").Value = "VBA Object"

'Worksheets(1).Range("B3").Value = "VBA Object"

Sheet1.Range("C3").Value = "VBA Object"

End Sub

Step 9: Now run this code. We will see, again the same text will get added in range cell C3.

In all the methods which we have seen in this example, Worksheet is our object, and the range of the cell is child object or Sub-object.

Pros and Cons of  Excel VBA Object

We can make as many objects and link them together to sink them.

It makes use of Workbook, Sheet, and Range easy.

This allows a user to make changes in a specific Workbook, Worksheet or Range.

The same process can be performed by a much shorter code with having the same result.

Things to Remember

Worksheet and Sheet both have the same use.

We can select any worksheet of the same workbook of any number sequence.

While writing big lines of code, it is important to form an Object in which the Workbook, Worksheets, and Ranges of different cells are linked.

Must save the file in Macro-Enabled Excel format. It allows us to use the code and change the code whenever required.

Recommended Articles

This is a guide to VBA Object. Here we discuss how to use Object in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Implement & Apply Lookup Function In Excel Vba?

Excel VBA Lookup Function

In excel, we have used VLookup many times. It is there in Insert function which is used to fetch or map any kind of values we want. Similarly, in VBA we have Lookup application, which works as same as Excel Vlookup. VBA Lookup has a flexible data structure as it can be used to map any kind of value from any kind of table array. This means if we apply Excel Vlookup, then we won’t be able to map the right column data with left column data in one syntax. Whereas in VBA Lookup, there is no proper structure of mapping. We just need to follow the syntax of VBA Lookup. If the syntax is satisfied and properly framed then we can fetch the values from the right or left side of the table.

Syntax of VBA Lookup Function:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Where,

Arg1 = Value we want to lookup.

Arg2 = Range of the columns or table from where we want to lookup.

Arg3 = Result as Boolean or Vector.

How to Use the Lookup Function in Excel VBA?

We will learn how to use the Lookup function in Excel by using the VBA Code.

You can download this VBA Lookup Excel Template here – VBA Lookup Excel Template

VBA Lookup – Example #1

There are many ways to write the VBA Lookup code. In this example, we will see a simple way to write the VBA Lookup. For this, we have a data set as shown below. This table has the number of races and the average speed of the racers. Now we will be using this data to apply VBA Lookup in the below table with the Blue headers in A8 to B8. For this, follow the below steps:

Step 1: Open a Module from the Insert menu tab as shown below.

Code:

Sub

VBA_Lookup1()

End Sub

Step 3: Select the output cell where we need to see the lookup value. Here that cell is B9 where we will be lookup with the value concerning the name “Aniket” which is our lookup value.

Code:

Sub

VBA_Lookup1() Range("B9").Value

End Sub

Step 4: Now we will use Worksheet Function and select Lookup function inbuilt in the list as shown below.

Code:

Sub

VBA_Lookup1 () Range("B9").Value = WorksheetFunction.Lookup

End Sub

Step 5: Once we select the Lookup function, we will see the Arg1, Arg2, and Arg3 in its syntax. For that, we will first put our Lookup value range which is cell A9 in place of Arg1.

Code:

Sub

VBA_Lookup1() Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value,

End Sub

Step 6: Now for Arg2, select the lookup range which is from cell A2:A5.

Code:

Sub

VBA_Lookup1 () Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value, Range("A2:A5"),

End Sub

Step 7: At last, select lookup values ranges which is from B2 to B5 in place of Arg3.

Code:

Sub

VBA_Lookup1() Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value, Range("A2:A5"), Range("B2:B5"))

End Sub

VBA Lookup – Example #2

There is another way to apply a Lookup function in Excel VBA. For this, we will be using the same data that we have seen in example-1. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Lookup, as shown below.

Code:

Sub

VBA_Lookup2()

End Sub

Step 2: Define a variable as String which will be used to map the Name column.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

End Sub

Step 3: In the defined variable Name, we will apply the Vlookup application as shown below.

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup(

End Sub

Step 4: Let’s say our lookup value is named “Ashwani” from the table.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani",

End Sub

Step 5: And the range is from A1 to C6 from Sheet1.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"),

End Sub

Step 6: Now if we want to see the average speed of rider “Ashwani” here, we need to map the cell in the Lookup syntax which is at 3rd place.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)

End Sub

Step 7: Now to see the Average speed of rider “Ashwani”, we can use MsgBox and Debug Print both. But using Debug Print is way better than MsgBox. So Assign Debug Print with defined variable Name.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)

Debug.Print

Name

End Sub

Step 8: Now open Immediate Window which is there in View menu tab to see the output.

Step 9: Compile the code and run it. We will see, Lookup has mapped with speed of Ashwani and fetched that into Immediate window as 86.

Code:

Sub

VBA_Lookup2()

Dim

Name

As String

Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)

Debug.Print

Name

End Sub

VBA Lookup – Example #3

For using the lookup function in excel VBA, follow the below steps:

Code:

Sub

VBA_Lookup3()

End Sub

Step 2: Declare a variable for Name as String as shown below.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

End Sub

Step 3: Now assign the name which wants to lookup to defined variable Name as shown below.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder"

End Sub

Step 4: Now use any word to define and use Lookup lets say LUp. And in that use Worksheet Function with Vlookup as shown below.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder" LUp = Application.WorksheetFunction.VLookup( MsgBox "Average Speed is : " & LUp

End Sub

Step 5: Now use the same Vlookup syntax as we use in Excel. In Arg1 put the Name variable, then select the range matrix and look for up value which we want to get. Here that column is 3 as shown below.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder" LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3,

False

) MsgBox "Average Speed is : " & LUp

End Sub

Step 6: Now use MsgBox to see the output.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder" LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3,

False

) MsgBox "Average Speed is : " & LUp

End Sub

Step 7: Compile and run the code. We will see, for the Name “Deepinder” the Average Speed is coming as 88. Whereas the same value is given for the name Deepinder in the table.

Code:

Sub

VBA_Lookup3()

Dim

Name

As String

Name = "Deepinder" LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3,

False

) MsgBox "Average Speed is : " & LUp

End Sub

Pros of Excel VBA Lookup Function

It is as easy to use and implement as applying regular excel Vlookup formulas in Excel.

We can use any kind of range and matrix in VBA Lookup.

There are very few or no constraints while applying VBA Lookup.

Things to Remember

We can use Lookup in place of Vlookup in any situation.

Range for Lookup vector and result vector should be the same.

Once done the application, save the file as Macro Enable format to retain the code.

There is no mandatory requirement to put the result column every time to be at the right of the lookup value.

Recommended Articles

This is a guide to the VBA Lookup function. Here we discuss how to use the Lookup function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Use Vlookup In Excel

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

Table of Contents

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

Type =VLOOKUP(

Select the School field, which in this example is I2. Type a comma.

Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.

Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

lookup_value: F2, the final averaged grade

table_array: I2:J8, The letter grade lookup range

index_column: 2, the second column in the lookup table

[range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2023.

Update the detailed information about How To Use Screenupdating Application In Vba? on the Kientrucdochoi.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!