# Trending February 2024 # How To Use Len Function In Excel? # Suggested March 2024 # Top 6 Popular

You are reading the article How To Use Len Function In Excel? 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 Len Function In Excel?

Excel functions, formula, charts, formatting creating excel dashboard & others

Introduction to LEN in Excel

LEN function in Excel, where Len is taken from a length, is used to count the number of characters in a cell. LEN function considers special characters, spaces, and alphabets and counts all these values if a selected cell exists. The most important thing to know about the LEN function is it only counts the character length for only one cell. If we select any range of cells, we will get the #Value error.

LEN Formula in Excel:

Below is the LEN Formula.

LEN Function consists of only one parameter, i.e., TEXT

TEXT: The text you want to find the length of the characters.

Note: Space is also considered as one character.

How to Use the LEN Function in Excel?

This LEN Function is very simple and easy to use. Let us now see how to use the LEN function in Excel with the help of some examples.

You can download this LEN function Excel Template here – LEN function Excel Template

Example #1

I got 13 on the result.

Example #2

In the below example, I have used the LEN function to count letters in a cell. Here I have taken a date as an example.

Even though the above date has 10 characters, I got only 5. The reason behind it is that LEN Function counts the characters in the value of a cell and is not concerned with formatting.

Example #3

Now I will explain the huge benefit of the LEN function when you want to compare two similar sets of data sets.

LEN Function with TRIM is a beautiful combination in Excel. We have two similar values in cells A1 and B1, and we need to compare whether both are the same.

The values in Cell A1 & B1 look similar, but comparing them in Cell C1 shows me FALSE. Looking at the outset looks similar, but not able to recognize the difference here.

The LEN function can save me here. Apply LEN to A1 and determine how many characters are in the A1 cell.

So in cell A1, 14 characters are there.

Apply LEN to B1 to find out how many characters are there in the B1 cell, and in cell B1, 13 characters are there

, which is why the formula shows it as two different values.

Because of one extra trailing space after the word Excel Formula, it is showing as 14 characters. We can remove unwanted spaces by using the TRIM function.

Therefore, TRIM has removed the unwanted space and returned only the text value; now, our formula in cell C1 shows both values as TRUE.

Example #4

Below are the names of the employees, and you needed to extract the Last Name separately.

In the above example, we cannot just apply RIGHT with specified numbers because the last name of every employee is different.

For the first employee, the last 4 characters are 5, but for the second one,e it is 11 (including space, i.e., Robert Adam), and for the third one, it is 12 (including space, i.e., J. Johnston)

Here we need to use FIND & LEN function to determine the number of characters from the given text.

Similarly, We Find the other two

Part 1: This part determines the desired text that you want to extract from the characters.

Part 2: LEN function will give you the total number of characters in the list. We will see the detailed article on LEN in the upcoming articles.

Part 3: The FIND function determines from which number space begins, i.e., The End of the first name. We will see the detailed article on FIND in the upcoming articles.

Len gives 16 characters and Finds 5 for the first employee. That means LEN(E3) – FIND(F3) (16 – 5) = 11 characters from the right side

the result will be Robert Adam

Len gives 17 characters and Find 5 for the first employee. That means LEN(E4) – FIND(F4) (17 – 5) = 12 character from the right side

the result will be J. Johnston

VBA Code to Use LEN Function

We can also use the MID function in VBA code, like Excel. The below code illustrates the usage of the LEN function in VBA macros.

Sub LEN_Function_Example Dim Length_String as string Len_String = LEN (“Data Analysis & Reporting”) Msgbox Len_String End Sub

If you run the above code message box will display 25 as your result.

Things to Remember

LEN Function is useful when you count the number of characters in some text.

Formatting of numbers is not a part of the LEN function, i.e., if your value shows \$1500.00, then it will show as 4 only instead of 8.

Numbers and dates will also return a length.

If a cell is blank,

it will return 0 in the result.

A cell with an error value will return that error in the result.

You can refer to only one cell at a time.

Otherwise, it will show an error as #Value.

Recommended Articles

This has been a guide to LEN Function. Here we discuss the LEN Formula and how to use the LEN function in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

You're reading How To Use Len Function In Excel?

## 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 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.

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

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.

## 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 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 Use Kotlin Jointostring With Function?

Introduction to Kotlin joinToString

Kotlin joinToString is used to reduce the for loop used to create the list of values of comma-separated strings that are passed into the query of SQLite. The kotlin join to string function is used to convert an array list to a string that was separated from the separator. Comma, by using space, is the default separator used in different types of separators in a kotlin.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Key Takeaways

Using the function, we can use the separator, prefix, and postfix in kotlin. In addition, Kotlin joins to strings are very useful for creating comma-separated values.

We can use the limit by using kotlin joinToString to limit the specified number of outputs in kotlin.

What is Kotlin joinToString?

In kotlin, all elements specified in a list are joined in a specified string separated by a delimiter. So we do not need to add a solution delimiter after or before the string. The solution was to standard join the strings using the specified delimiter with the function of joining in stringing. Using the kotlin join to the string, we create a string using a separator, and we need to give a postfix and prefix after supplying. If our collection is large, we can specify the non-negative value in the limit to limit the data.

How to Use Kotlin joinToString?

To use the join to string in kotlin, a separator must create the string from all the separated elements. The join to string is a function that converts any array of a list of strings by using a separator. We are using a comma with space as a default separator; if suppose we want to use different types of separators, then we can mention the same. The below example shows how we can use the separator in the kotlin join to string as follows. In the below example, we are using the “-” separator to separate the listed numbers. Moreover, we are using the function list to provide a list of numbers to separate the exact string.

Code:

fun main() { val num = listOf(11, 13, 15, 17, 19, 21) println (num.joinToString (separator = "-")) }

Output:

When working with joinToString in kotlin, we also need to use the prefix. This is because it will allow us to add the element at the beginning of the specified string. The below example shows how we can use a prefix with the joinToString in kotlin as follows.

Code:

fun main() { val num = listOf(11, 13, 15, 17, 19, 21) println (num.joinToString(prefix = "@", separator = "_")) }

Output:

Postfix will allow us to add the element at the end of the specified string. The below example shows how we can use postfix with the joinToString in kotlin as follows.

Code:

fun main() { val num = listOf(11, 13, 15, 17, 19, 21) println (num.joinToString(prefix = "@", postfix = "@")) }

Output:

We are also using the limit function to tell the function that we need a limited number of records. The limit by default value is -1. The below example shows how we can use the limit function by using joinToString in kotlin.

Code:

fun main() { val num = listOf(11, 13, 15, 17, 19, 21) println(num.joinToString(limit = 3)) }

Output:

Kotlin joinToString Function

The function converts a list or an array string that was separated using a separator we used in a code. For example, we use joinToString to convert a list of classes using a separator we specified in the code. The default value of the separator is “,” In the below example, we are using “@” to separate the numbers as follows.

Code:

fun main() { val num = listOf(11, 13, 15, 17, 19, 21) println (num.joinToString (separator = "@")) }

Output:

We can also use truncated when using the joint string function in a kotlin. We are using limit and truncated arguments together. In kotlin, the default value is “…”. We can use anything we need to customize. The below example shows how we are using the joinToString function with limit and truncated as follows.

Code:

fun main() { val num = listOf(11, 13, 15, 17, 19, 21) println (num.joinToString (limit = 4, truncated = "@")) }

Output:

Code:

fun main() { val num = listOf(11, 13, 15, 17, 19, 21) println (num.joinToString (transform = { it + 1 })) }

Output:

Examples of Kotlin joinToString

Different examples are mentioned below:

Example #1

The below example shows joinToString variance as follows.

Code:

fun main() { println (num.joinToString("-")) }

Output:

Example #2

The below example shows a byte array by using joinToString as follows. We can combine the byte array by using joinToString as follows.

Code:

fun main() { val cset = Charsets.UTF_8 val barr = "kotlin".toByteArray (cset) println (barr.joinToString ("-")) }

Output:

Example #3

The below example shows combined collections as follows. We can join the collection object by using the joinToString method as follows.

fun main() { println (list.joinToString ("@")) }

Output:

We can also add the prefix and postfix to the result using kotlin joinToString. The below example shows kotlin joinToString by using postfix and prefix.

Code:

fun main() { val num = listOf(11, 13, 15) println (num.joinToString(separator = "*", prefix = "#", postfix = "#")) }

Output:

FAQ

Other FAQs are mentioned below:

Q1. What is the truncate result in kotlin joinToString?

If suppose our collection result is the same big time, we limit our result. We are using a truncate statement with the limit statement.

Q2.What is the use of a separator in kotlin joinToString?

Using a separator, we can separate the string as per the separator we used in our code.

Q3. What is a transform in kotlin joinToString?