Trending December 2023 # How To Use Object In Excel Vba With Examples? # Suggested January 2024 # Top 12 Popular

You are reading the article How To Use Object In Excel Vba With Examples? updated in December 2023 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 January 2024 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 –

You're reading How To Use Object In Excel Vba With Examples?

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

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

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

As String

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

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

As String

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

As String

A = "LIKE WISE"

"*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

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"

"*[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 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

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

For

ColumnCount = 1

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

For

ColumnCount = 1

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

For

ColumnCount = 1

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

ColumnCount

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

For

ColumnCount = 1

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

ColumnCount

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 –

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 Isblank With Examples

ISBLANK Function

Checks if a specified cell is blank or not

Written by

CFI Team

Published July 2, 2023

Updated July 7, 2023

What is Excel ISBLANK Function?

The ISBLANK Function[1] is an Excel Information function that returns true if the argument cell has no information in it. ISBLANK checks a specified cell and tells us if it is blank or not. If it is blank, it will return TRUE; else, it will return FALSE. The function was introduced in MS Excel 2007.

In financial analysis, we deal with data all the time. The ISBLANK function is useful in checking if a cell is blank or not. For example, if A5 contains a formula that returns an empty string “” as a result, the function will return FALSE. Thus, it helps in removing both regular and non-breaking space characters.

However, if a cell contains good data, as well as non-breaking spaces, it is possible to remove the non-breaking spaces from the data.

Formula

=ISBLANK(value)

Where:

Value (required argument) is the value that we wish to test. (This function takes in a cell)

How to use the Excel ISBLANK Function

As a worksheet function, ISBLANK can be entered as part of a formula in a cell of a worksheet. To understand the uses of this function, let us consider a few examples:

Highlight Missing Values – Example

Suppose we are given the following data:

Suppose we wish to highlight cells that are empty. We can use the ISBLANK coupled with conditional formatting. For example, suppose we want to highlight the blank cells in the range A2:F9, we select the range and use a conditional formatting rule with the following formula: =ISBLANK(A2:F9).

How to do conditional formatting?

The input formula is shown below:

We will get the results below.

Conditional formatting didn’t highlight cell E5. After checking, there is a formula inserted into the cell.

The Excel ISBLANK function will return TRUE when a cell is actually empty. If a cell is an empty string (“”), ISBLANK will return FALSE, as it is not technically blank, and it won’t be highlighted as shown above.

Extracting the first NON-Blank value in an array

Suppose we wish to get the first non-blank value (text or number) in a one-row range. We can use an array formula based on the INDEX, MATCH, and ISBLANK functions.

We are given the data below:

Here, we want to get the first non-blank cell, but we don’t have a direct way to do that in Excel. We could use VLOOKUP with a wildcard *, but that will only work for text, not numbers.

Hence, we need to build the functionality by nesting formulas. One way to do it is to use an array function that “tests” cells and returns an array of TRUE/FALSE values that we can then match with MATCH. Now MATCH looks for FALSE inside the array and returns the position of the first match found, which, in this case, is 2. Now the INDEX function takes over and gets the value at position 2 in the array, which, in this case, is the value PEACHES.

As this is an array formula, we need to enter it with CTRL+SHIFT+ENTER.

We get the results below:

Additional Resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling and valuation analysis. To learn more, check out these additional CFI resources:

Top Examples With Excel Template

Definition of Quick Ratio

The term “Quick Ratio” refers to the liquidity ratio that assesses the ability of a company to cover its short-term liabilities by utilizing all those assets that can be easily converted into cash. The name “quick ratio” comes from the underlying idea that the ratio considers only those assets that can be quickly liquidated. The ratio is also known as the name acid test ratio.

Start Your Free Investment Banking Course

Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

If a company’s quick ratio is greater than 1, it means that it has more than enough liquid assets that can be used to repay the current liabilities immediately. On the other hand, if it is less than 1, it indicates that the company’s liquidity is inadequate to pay off its current liabilities in case it is required to pay immediately.

Formula:

The formula for Quick Ratio can be derived by dividing the sum of cash, marketable securities, accounts receivables, and other current assets (other than inventories and prepaid expenses) by the total current liabilities. Mathematically, it is represented as,

Quick Ratio = (Cash + Marketable Securities + Accounts Receivable + Other Current Assets) / Total Current Liabilities

One can also derive the formula for Quick Ratio by subtracting inventories and prepaid expenses from the total current assets and then dividing the resulting figure by the total current liabilities. Mathematically, this is represented as:

Quick Ratio = (Total Current Assets – Inventories – Prepaid Expenses) / Total Current Liabilities.

Examples of Quick Ratio (With Excel Template)

Let’s take an example to understand the calculation of the Quick Ratio formula in a better manner.

You can download this Quick Ratio Excel Template here – Quick Ratio Excel Template

Example – #1

Let us take the example of a company that has applied for a bank loan in order to remodel its storefront. The company has provided the following balance sheet information to the bank:

Based on the given information, Calculate the quick ratio of the company.

Solution:

Of the above-mentioned current assets, only cash, marketable securities, and accounts receivable can be considered quick assets.

Calculate Quick Ratio using the formula given below:

QR = ($15,000 + $20,000 + $10,000) / $35,000

QR = 1.29

Therefore, the quick ratio for the company stood at 1.29, which indicates a fairly comfortable liquidity position.

Example – #2

Let us take the latest annual report of Apple Inc. to explain the quick ratio calculation. As per the annual report for the year ended on Sep 29, 2023, the following information is available:

Based on the given information, Calculate the quick ratio for Apple Inc. for the year ending Sep 29, 2023.

Solution:

Out of the above-mentioned current assets, only cash and cash equivalents, marketable securities, net accounts receivable, vendor non-trade receivables, and other current assets can be considered quick assets.

Quick Ratio = (Cash and Cash Equivalents + Marketable Securities + Accounts Receivable + Net Accounts Receivable + Vendor Non-Trade Receivables + Other Current Assets) / Total Current Liabilities

QR = ($25,913 Mn + $40,388 Mn + $23,186 Mn + $25,809 Mn + $12,087 Mn) / $116,866 Mn

QR = 1.09

Therefore, the QR for Apple Inc. for the year ending Sep 29, 2023, stood at 1.09, indicating a moderate liquidity position.

Source:d18rn0p25nwr6d.cloudfront.net

Advantages

It assesses the ability to repay current liabilities based on the assets a company can quickly convert to cash. As such, it is a relatively more conservative liquidity ratio.

Its calculation omits inventories because converting inventories into cash could take too long. Elimination of inventories from its calculation helps management and other stakeholders to have a precise idea about the liquidity position of the concerned company.

This ratio is one of the easiest ratios to understand, and as such, it can be very helpful for people who do not have a deep understanding of accounting and finance.

It doesn’t provide any information about the timing of cash flows which can be a defining factor in the assessment of the liquidity position of a company.

Some of the assumptions of the QR are not realistic. For instance, it assumes that accounts receivable is readily available for collection, which is not always true.

It does not consider the situation that may arise in times of crisis. During the crisis, even the most easily saleable securities may find it difficult to trade in the market.

Conclusion

So, it can be seen that the quick ratio is a moderate conservative liquidity measure which is more conservative than the current ratio but less conservative than the cash ratio. This ratio helps the creditors in the assessment of the liquidity position of a company more accurately.

Recommended Articles

Update the detailed information about How To Use Object In Excel Vba With Examples? 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!