Trending February 2024 # Top 4 Vba Macro Excel Examples For Beginners # Suggested March 2024 # Top 6 Popular

You are reading the article Top 4 Vba Macro Excel Examples For Beginners updated in February 2024 on the website Flu.edu.vn. 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 Top 4 Vba Macro Excel Examples For Beginners

Excel VBA Examples

This is very thing when I started working and learning VBA Macros, I was confused where to start. We have already seen VBA Editor which has all the definitions of VBA Editor Window options and tabs. As a beginner, we always do not know the way to start the thing until we find one. This article is the solution for those who face difficulty in using and learning VBA Examples and creating Macros. We all have faced the time when certain things in Excel could have been automated using Macro. But, someone who doesn’t know how to use VBA will not able to create one. This will be a useful guide for those who are new to VBA Macro coding.

Examples of VBA in Excel for Beginners

Below are the examples of VBA in Excel:

Watch our Demo Courses and Videos

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

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

VBA Example #1

Let’s see a simple example of using VBA Coding. For this, follow the below steps:

Step 1: Open a VBA Module where we will be writing our code from Insert menu tab as shown below.

Step 2: Now write the subprocedure of VBA macro in any name.

Code:

Sub

VBA_Examples1()

End Sub

Step 3: Define a variable using DIM where we will be storing numbers as Integers.

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

End Sub

Step 4: Now give any number to the above-defined variable, as shown below.

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

A = 100

End Sub

Step 5: Now we will use the message box to print the value stored in Variable A.

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

A = 100 MsgBox A

End Sub

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

A = 100 MsgBox A

End Sub

Step 7: This could have been done using Debug Print function as well, whose values are seen in Immediate Window. (Note: Press Ctrl+ G to open immediate window)

Code:

Sub

VBA_Examples1()

Dim

A

As Integer

A = 100 Debug.Print A

End Sub

VBA Example #2

Now in this example, we will see another example where we will use the For-Next loop to print the sheet names. For this, follow the below steps:

Step 1: Open a Module and write the subprocedure as shown below. We can use any name to define this.

Code:

Sub

VBA_Examples2()

End Sub

Step 2: Choose a variable as an Integer. We can choose the name variable as we did in example-1.

Code:

Sub

VBA_Examples2()

Dim

A

As Integer

End Sub

Step 3: Open a For-Next loop as shown below.

Code:

Sub

VBA_Examples2()

Dim

A

As Integer

For

Next

A

End Sub

Step 4: Select the position of cell from where we want to see the Sheet names. Here as we don’t have any header reference so we can start from cell 1.

Code:

Sub

VBA_Examples2()

Dim

A

As Integer

For

A = 1

To

Sheets.Count

Next

A

End Sub

Step 5: Now we assign the variable A with cell Value to see the Sheet name as shown below.

Code:

Sub

VBA_Examples2()

Dim

A

As Integer

For

A = 1

To

Sheets.Count Cells(A, 1).Value = Sheets(A).Name

Next

A

End Sub

We will see, as we had 3 sheets, all named default so their name got printed from cell A1 to below till the number of Sheets we have.

VBA Example #3

In this example, we will learn how to print the numbers. This process is quite the same as we have seen in example-2. For this, follow the below steps:

Step 1: Open a Module and write the subprocedure.

Code:

Sub

VBA_Examples3()

End Sub

Step 2: Consider a variable using DIM as Integer. As we are using the numbers to Integer data type should be used.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

End Sub

Step 3: Again open a For-Next loop as shown below.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

For

Next

A

End Sub

Step 4: Now select the cell range from where we want to see number till the last in For syntax.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

For

A = 1

To

10

Next

A

End Sub

Step 5: Now fix the cell position with variable A. Here, second position vertex in Cell shows Column number. As we chose 1 so we expect to see the numbers starting from cell A1.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

For

A = 1

To

10 Cells(A, 1).Value = A

Next

A

End Sub

Step 7: What if we try to add these numbers in the second column? Change the column vertex from 1 to 2. Or else add another line of code as shown below.

Code:

Sub

VBA_Examples3()

Dim

A

As Integer

For

A = 1

To

10 Cells(A, 1).Value = A Cells(A, 2).Value = A

Next

A

End Sub

VBA Example #4

In this example, we will see how to change the color of blank cells from the selected ranges. For this, we have used the same number as we did in the above example but we have deleted some of the cells to get the output.

Step 1: Open a Module, write the subprocedure as shown below.

Code:

Sub

VBA_Example4()

End Sub

Step 2: Define a variable using DIM as Range, as we are selecting the exiting number range.

Code:

Sub

VBA_Example4()

Dim

A

As Range

End Sub

Step 3: Now set this variable as Selection

Code:

Sub

VBA_Example4()

Dim

A

As Range

Set

A = Selection

End Sub

Step 4: Now select the Range of numbers using defined variable A and choose xlCellTypeBlanks to select the blank cells only.

Code:

Sub

VBA_Example4()

Dim

A

As Range

Set

A = Selection A.Cells.SpecialCells(xlCellTypeBlanks).

End Sub

Step 5: Now to change the interior color of blank cells, use Interior.Color and choose the color by which we want to highlight the blank cells. Use vbBlue or vbGreen type to define the color. The use of vb is a must.

Code:

Sub

VBA_Example4()

Dim

A

As Range

Set

A = Selection A.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue

End Sub

Pros and Cons of Excel VBA Examples

VBA increases the efficiency of work.

By this, we want to reduce the repetitive tasks in one go.

We can create any type of Macro we want.

We can also record a Macro if doing the coding is not easy.

People who do not have coding knowledge or idea may find difficult using VBA Macro.

Things to Remember

Beginner in VBA should keep in mind to save the code in Macro enabled excel format. This will allow us to retain the code in the same file.

Always compile the complete code, even if the code is of one line. This will reduce our time in debugging if you face any error while after code is run.

Always start the learning process of VBA or any other coding language with small and easy code. This will give a better understanding and confidence.

Recommended Articles

This is a guide to VBA Examples in Excel. Here we discuss some useful examples of VBA Macro code in Excel along with downloadable excel template. You can also go through our other suggested articles –

You're reading Top 4 Vba Macro Excel Examples For Beginners

Excel Vba Instr Function – Explained With Examples

Yesterday, I got an email from one of my readers – June.

She wanted to know how to apply bold font format to a specific part of a string within a cell. For example, apply the bold format to only the word ‘Hello’ from ‘Hello World’.

And she wanted to do this for hundreds of cell at once.

Since there is no inbuilt functionality in Excel that can do that, I created a simple macro that uses the Excel VBA InStr function (you will see how to do this in Example 4 in this tutorial).

But first, let’s see how the Excel VBA InStr function works!

In this tutorial, I will explain the usage of InStr function in Excel VBA and see some practical examples where it can be used.

InStr function finds the position of a specified substring within the string and returns the first position of its occurrence.

For example, if you want to find the position of ‘x’ in ‘Excel’, using the Excel VBA InStr function would return 2.

InStr(

[Start]

,

String1

,

String2

,

[Compare]

)

[Start] – (optional argument) this is an integer value that tells the InStr function the starting position from which it should start looking. For example, if I want the search to start from the beginning, I will enter the value as 1. If I want it to begin with the third character onwards, I will use 3. If omitted, the default value of 1 is taken.

String1 – This is the main string (or the parent string) in which you want to search. For example, if you’re looking for the position of x in Excel, String 1 would be “Excel”.

String2 – This is the substring that you are searching for. For example, if you’re looking for the position of x in Excel, String2 would be x.

[Compare] – (optional argument) You can specify one the following three values for [compare] argument:

vbBinaryCompare – This would do a character by character comparison. For example, if you’re looking for ‘x’ in ‘Excel’, it will return 2, but if you’re looking for ‘X’ in ‘Excel’, it will return 0 as X is in upper case. You can also use 0 instead of vbBinaryCompare. If the [Compare] argument is omitted, this is the taken as default.

vbTextCompare – This would do a textual comparison. For example, if you look for ‘x’ or ‘X’ in Excel, it would return 2 in both the cases. This argument ignores the letter case. You can also use 1 instead of vbTextCompare.

vbDatabaseCompare –  This is used for Microsoft Access only.  It uses the information in the database to perform the comparison. You can also use 2 instead of vbDatabaseCompare.

InStr is a VBA function and not a worksheet function. This means that you can not use it within the worksheet.

If String2 (which is the substring whose position you’re looking for) is empty, the function would return the value of the [Start] argument.

If the InStr function can not find the substring within the main string, it would return 0.

Now let’s have a look at some example of using the Excel VBA InStr Function

In this example, I will use the InStr function to find the position of ‘V’ in ‘Excel VBA’ from the beginning.

The code for this would be:

Sub FindFromBeginning() Dim Position As Integer Position = InStr(1, "Excel VBA", "V", vbBinaryCompare) MsgBox Position End Sub

When you run this code, it will show a message box with the value 7, which is the position of ‘V’ in the string ‘Excel VBA’.

Suppose, I want to find the position of ‘the’ in the sentence – ‘The quick brown fox jumps over the lazy dog’

However, I want the search to begin with the second word onwards.

In this case, we need to change the [Start] argument to make sure it specifies the position from where the second word starts.

Here is the code that will do this:

Sub FindFromSecondWord() Dim Position As Integer Position = InStr(4, "The quick brown fox jumps over the lazy dog", "the", vbBinaryCompare) MsgBox Position End Sub

This code will show the message box with the value 32 as we have specified the starting position as 4. Hence it ignores the first ‘The’ and finds the second ‘the’ in the sentence.

If you want to make it more dynamic, you can enhance the code so that it automatically ignore the first word.

Here is the enhanced code that will do this:

Sub FindFromSecondWord() Dim StartingPosition As Integer Dim Position As Integer StartingPosition = InStr(1, "The quick brown fox jumps over the lazy dog", " ", vbBinaryCompare) Position = InStr(StartingPosition, "The quick brown fox jumps over the lazy dog", "the", vbBinaryCompare) MsgBox Position End Sub

This code first finds the position of a space character and stores it in the variable StartingPosition.

It then uses this variable as the starting position to look for the word ‘the’.

Hence it returns 32 (which is the starting position of ‘the’ after the first word).

You can easily create a custom function to find the position of @ in an email address using the Excel VBA InStr function.

Here is the code to create the custom function:

Function FindPosition(Ref As Range) As Integer Dim Position As Integer Position = InStr(1, Ref, "@") FindPosition = Position End Function

Now you can use this custom function as any other worksheet function. It will take a cell reference as input and give you the position of @ in it.

Similarly, you can create a custom function to find the position of any substring within the main string.

This is the query that was asked by June (my reader who also inspired me to write this tutorial).

Here is a sample data in the format June sent me:

Her query was to make the numbers outside the bracket bold.

Here is the code I created that does this:

Sub Bold() Dim rCell As Range Dim Char As Integer For Each rCell In Selection CharCount = Len(rCell) Char = InStr(1, rCell, "(") rCell.Characters(1, Char - 1).Font.Bold = True Next rCell End Sub

The above code uses the For Each loop to go through each of the cells in the selection. It identifies the position of the opening bracket character using the InStr function. It then changes the font of the text before the bracket.

To use this code, you need to copy and paste in a module in the VB editor.

Once you have copy pasted the code, select the cells in which you want to do this formatting and run the macro (as shown below).

You May Also Like the following Excel VBA Tutorials:

How To Use Excel Vba Mid With Examples?

MID Function in Excel VBA

MID Function is commonly used to extract a substring from a full-text string. It is categorized under String type variable. VBA Mid function allows you to extract the middle part of the string from a full-text string. VBA string functions do not change the original string. Usually, they will return a new string based on the function you input in the code. VBA Mid function returns a substring from within a supplied text or a string.

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

Syntax of VBA MID in Excel

The syntax for VBA Mid function in excel is as follows:

Mid(string_to_search, starting position, number_of_characters)

Start_number or Starting point: Character position or number from where to start extracting the sub-string.

Note: If you enter a greater than the number of characters in a string, then it will return an empty string (“”)

char_numbers or Length (Optional): Number of characters to extract or to return from the start position.

How to Use Excel VBA MID?

Below are the different examples to use Mid in Excel using VBA code.

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

Excel VBA MID – Example #1

Follow the below steps to use excel VBA MID.

Excel VBA MID – Example #2

Now the blank module is created, it is also called as a code window, where you can start writing VBA MID function statement codes.

Suppose, I have the word “[email protected]” and you want to extract email domain i.e. “OUTLOOK” from this sentence with the help of VB MID function macro code.

Step 1: In the VBA editor, I have a given a name as VBA_MID_1() after typing Sub.

Code:

Sub

VBA_MID_1()

End Sub

Step 2: As the MID function is categorized under string type variables, DIM (Dimension) is used in a VBA code to declare a variable name, it’s type.

Code:

Sub

VBA_MID_1()

Dim

MiddleValue

As String

End Sub

Code:

Sub

VBA_MID_1()

Dim

MiddleValue

As String

MiddleValue = Mid

End Sub

Step 4: First parameter or argument is String i.e. It is a text String from where you want to start extracting the specified number of characters. Here it is “[email protected]” when using strings argument, you have to surround the text in quotation mark.

Start As Long: It is the starting position of the character from where you want to extract. Here, in this case, it is “10”

Length: It is a number of characters to extract or to return from the start position, Here, in this case, it is “7”

Code:

Sub

VBA_MID_1()

Dim

MiddleValue

As String

MiddleValue = Mid("[email protected]", 10, 7) MsgBox (MiddleValue)

End Sub

We have completed the MID function arguments here. Now, I want to display this result of the variable in the message box. Let’s press Ctrl + Space, type Msg, in the bracket you can mention a variable name.

Excel VBA MID – Example #3

Instead of output appearing in the message box, I want the result or output data to appear in the worksheet, i.e. in cell “D5”. In the worksheet, I have a data, i.e. Email id of the employee in cell “C5”, now I want to extract email domain form this, I can use the MID function with a slight modification of code.

Step 1: After declaring a variable, I need to input the variable name again and cell address of the full-text string with the help of Range or cell function.

Code:

Sub

VBA_MID_2()

Dim

MiddleValue

As String

MiddleValue = Range("C5")

End Sub

Step 2: Now, again I need to enter the variable name, and apply mid function & input its arguments.

Code:

Sub

VBA_MID_2()

Dim

MiddleValue

As String

MiddleValue = Range("C5") MiddleValue = Mid("[email protected]", 10, 7)

End Sub

In the previous example, we entered a msg box for the result to be displayed, now, I want the result to appear in a specific cell in a worksheet. For this, I  need to enter the range or cell function for the result to be displayed.

Step 3: Let’s apply range function, initially we need to input the range or cell function and Later enter the variable name, so that in that specific cell (“D5”), the result appears.

Code:

Sub

VBA_MID_2()

Dim

MiddleValue

As String

MiddleValue = Range("C5") MiddleValue = Mid("[email protected]", 10, 7) Range("D5") = MiddleValue

End Sub

Things to Remember

In Excel VBA MID function, Length argument is an optional parameter. If you fail to enter any argument or ignore this, VBA Mid function returns all characters from the supplied start position to the end of the string.

In Excel VBA MID function, if the start number argument is greater than the length of the text string, then the MID function returns an empty string (zero-length).

The mid function is very significant and useful along with loops function, as it helps you to examine one character at a time from a string of text.

Recommended Articles

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

How To Record A Macro In Excel

Performing the same actions over and over again isn’t just boring, but it can also be a waste of time and a drain on your productivity. This is especially true for beginner Excel users, who might not realise that it’s easy to automate common tasks by recording a macro. But what is an Excel macro? 

Excel macros allow you to automate many of the common tasks you’ll perform, from inserting formulas to formatting data. You don’t need to be a programmer to create them, as Excel can record your actions as you perform them. If you want to know how to record a macro in Excel, you’ll need to follow the steps below.

Table of Contents

What Is an Excel Macro?

An Excel macro is a recorded set of instructions, created manually in VBA (Visual Basic for Applications) or recorded automatically using Excel’s Macro Recorder tool. A macro allows you to save any number of common actions, such as deleting blank columns, changing text formatting, or inserting new formulae into a workbook.

How to Record a Macro in Excel

The easiest way to create a new macro in Microsoft Excel is to record it using the Macro Recorder tool, which you can use in Excel on Windows or Mac. Unfortunately, it isn’t possible to record a macro in Excel Online. 

To record a new macro in Excel, open a new or existing workbook. In the Developer tab on the ribbon bar, select the Record Macro button. Alternatively, press the Alt + T + M + R keys on your keyboard.

In the Record Macro window, you can set a name for your macro to identify its purpose in the Macro name box, as well as provide a description for other users in the Description box. You can also add a shortcut key (such as Ctrl + T) to assign the new macro to a keyboard shortcut.

In the Store macro in drop-down menu, you can set where you’d like to save the macro once it’s recorded, such as This Workbook (to save it to the open workbook), New Workbook (to save it to a new workbook), or Personal Macro Workbook (to allow you to use it in multiple workbooks). Select the OK button to begin recording once you’ve confirmed your choices.

Running, Editing or Deleting Existing Microsoft Excel Macros

Depending on the save location you selected in the Record Macro window, your recorded Macro will be ready to run in your open workbook or in a newly opened workbook.

In the Macro window, a list of available macros to run in your open workbook will be listed. To run the macro, select it from the list, then select the Run button. You can also use the keyboard shortcut you selected when you created the macro to run it instead.

To delete the macro, select it from the list, then select the Delete button.

Excel will ask you to confirm that you want to delete the macro. Select Yes to confirm this. Once deleted, you’ll need to record the macro again using the steps listed above.

Saving Excel Files with Recorded Macros

Excel workbooks are typically saved in the XLSX file format (or XLS for workbooks created in Excel 2007 and older). This file type supports most Excel data, but excludes any saved Excel macros.

To save an Excel file with recorded macros, you’ll need to use the XLSM file format instead.

In the Save As window, select Excel Macro-Enabled Workbook (*.xlsm) from the drop-down menu. Choose a location to save the file, then select the Save button to save it.

When you open the macro-enabled Excel file on another PC or Mac, you may need to authorize Excel to run any included macros first. Select the Enable Macros button to do this.

Sharing Excel Files with Recorded Macros

Excel macros are useful, but they also include potential security risks. If you open an Excel file containing macros from a source that you don’t recognize or trust, then you’re allowing that file to run dangerous code on your PC.

Because of this, online email providers like Gmail automatically block users from sending XLSM (macro-enabled Excel workbook) files to other users. If this happens, you may be able to use file sharing services like Google Drive to share it online with other users.

If you want to share your macro-enabled Excel file with another user nearby, then you could also look at local file transfer methods to share it with another PC or Mac, or with other devices like smartphones.

Macro-enabled Excel files can be packaged with viruses or other malware, so if you open an Excel file that you don’t trust, you may need to scan for malware afterwards to ensure that your PC isn’t compromised. Or scan the file itself before opening it.

Advanced Excel Tips and Tricks

Recording a macro is just one Excel trick that can help you save time, but there are other Excel features that can make you more productive as well. Power users might be interested in using Excel to scrape data online, while data analysts might be curious about how to use COUNTIFS, SUMIFS and AVERAGEIFS for extensive data analysis.

How To Use Vba Isnull Function In Excel?

VBA ISNULL Function

ISNULL function in VBA is used for finding the null value in excel. This seems easy but when we have a huge database which is connected to multiple files and sources and if we asked to find the Null in that, then any manual method will not work. For that, we have a function called IsNull in VBA which finds the Null value in any type of database or table. This can only be done in VBA, we do not have any such function in Excel.

Syntax of ISNULL in Excel VBA

Watch our Demo Courses and Videos

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

The syntax for the VBA ISNULL function in excel is as follows:

As we can see in the above screenshot, IsNull uses only one expression and to as Boolean. Which means it will give the answer as TRUE and FALSE values. If the data is Null then we will get TRUE or else we will get FALSE as output.

How to Use VBA ISNULL Function in Excel?

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

Example #1 – VBA ISNULL

Follow the below steps to use IsNull in Excel VBA.

Step 1: To apply VBA IsNull, we need a module. For this go to the VBA window and under the Insert menu select Module as shown below.

Step 2: Once we do that we will get a blank window of fresh Module. In that, write the subcategory of VBA IsNull or in any other name as per your need.

Code:

Sub

VBA_IsNull()

End Sub

Step 3: For IsNull function, we will need one as a Variant. Where we can store any kind of value. Let’s have a first variable Test as Variant as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

End Sub

Step 4: As we know that IsNull works on Boolean. So we will need another variable. Let’s have our second variable Answer as Boolean as shown below. This will help us in knowing whether IsNull is TRUE or FALSE.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

End Sub

Step 5: Now give any value to the first variable Test. Let’s give it a text value “VBA Macro” as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro"

End Sub

Step 6: Now we will use our second variable Answer with IsNull function as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(

End Sub

As we have seen in the explanation of VBA IsNull, that syntax of IsNull is Expression only. And this Expression can be a text, cell reference, direct value by entering manually or any other variable assigned to it.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(Test)

End Sub

Step 8: Once done, then we will need a message box to print the value of IsNull if it is TRUE or FALSE. Insert Msgbox and give any statement which we want to see. Here we have considered “Is the Test is null?” as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(Test) MsgBox "Is the Test is null? : "

End Sub

Step 9: And then add rest of the variable which we defined above separated by the ampersand (&) as shown below which includes our second variable Answer and name of the message box as “VBA ISNULL Function Example”.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "VBA Macro" Answer = IsNull(Test) MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

Step 10: Now compile the code by pressing F8 and run it by pressing the F5 key if there is no error found. We will see the Isnull function has returned the Answer as FALSE. Which means text “VBA Macro” is not null.

Step 11: Now we will see if numbers can be null or not. For this, we use a new module or we can use the same code that we have written above. In that, we just need to make changes. Assign any number to Test variable in place of text “VBA Macro”. Let’s consider that number as 123123 as shown below.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = 123123 Answer = IsNull(Test) MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

Step 12: Now again compile the code or we can compile the current step only by putting the cursor there and pressing F8 key. And run it. We will get the message box with the statement that our Test variable which is number 123123 is also not a Null. It is FALSE to call it a null.

Step 13: Now it is clear that neither Text nor Number can be Null. To test further, now we will consider a blank. A reference which has no value. For this, in the same previously written code put the double inverted (“”) commas with nothing in it in Test variable as shown below. Now we will is if a Blank can be a null or not.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = "" Answer = IsNull(Test) MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

Step 14: We will get a message which says the Blank reference is also not null. It is FALSE to call it so.

Step 15: We have tried Text, Number and Blank for testing if they are null or not. Now we will text Null itself under variable Test as see if this is a null or not.

Code:

Sub

VBA_IsNull()

Dim

Test

As Variant

Dim

Answer

As Boolean

Test = Null Answer = IsNull(Test) MsgBox "Is the Test is null? : " & Answer, vbInformation, "VBA ISNULL Function Example"

End Sub

Step 16: Now run the code. We will in the message box the statement for “Is the Test is null?” has come TRUE.

Which means, in data if there are any cells with Blank, Space, Text or Number. Those cells will not be considered as Null.

Pros of VBA IsNull

We can find if a cell is Null or not.

We can test any variable if it is null or not.

This quite helps in a big database which is fetched from some source.

Things to Remember

IsNull finds only Null as Null. Text, Numbers, and Blanks are not null.

IsNull is only applicable in VBA. Excel doesn’t have any function as IsNull or other matching function which can give the same result as IsNull.

To use the code multiple times, it is better to save the excel in Macro Enable Excel format. This process helps in retaining the code for future use.

IsNull only returns the value in the Boolean form, means in TRUE and FALSE

Considering the Variant as Test variable allow us to use numbers, words and blank values in it. It considers all the type of values majorly used for Boolean.

Recommended Articles

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

Split Cell In Excel (Examples)

Introduction to Split Cell in Excel

Split Cell in Excel means dividing a single cell’s data into multiple cells. It can be super useful when data from multiple columns or rows are included in a single cell. Splitting allows you to analyze and present the information more organized and meaningfully.

There are different ways to split cells in Excel, depending on what you want to achieve.

If you want to save time, then use a keyboard shortcut. You can select the data and press ALT + A + E keys to split cells simultaneously. You can also split a cell in Excel with fixed width if you want a specific length to break or use delimiters with special characters like commas, semicolons, spaces, etc.

In this article, we will learn various methods to split cells in Excel using different examples.

Example #1

You can download this Split Cell Excel Template here – Split Cell Excel Template

Split Merged Cells in Excel

This is the simplest method of splitting or Unmerging cells in Excel.

Result: All merged cells are split successfully, as seen below.

Note: Each merged cell’s content will be displayed on the upper-left cell, and all other merged cells will be empty.

Example #2 Split a Cell Diagonally in Excel

A Format Cells dialog box will appear.

Note: The” Border” preview section will display how the selected diagonal will appear. In the “Format Cells” dialogue box, you can apply multiple formatting per your demand to the selected cell.

We want to separate the heading (Year and Month) across the diagonal line.

A Format cells dialog box will appear.

“Month” appears at the top of the cell, as shown above. Now, Adjust the font size and alignment of “Year” and “Month“.

Result: Cell A1 is diagonally separated with “Year” representing rows and “Month” representing Columns.

Example #3 Split Cell in Excel Using Power Query

We can also split cells in Excel using Power Query. In a power query, a column’s data can be split into numerous columns per the requirement. We can split our data by delimiters, positions, numbers of characters, digit-to-non-digits, etc.

A “Create Table” dialog box will open.

Step 5: Select Space and press OK, as shown below.

Note: The first name and last name of the original data are separated by space.

A dialog box named Import Data will open.

Step 8: Change the column heading to “First Name” and “Last Name”.

Result: The first name and last name is separated using the power query.

Example #4 Split Cell Using Text-to-Column Function

We can also Split Cell in Excel using the text-to-column Function. Text to Columns function in Excel separates text strings by a specific delimiter such as comma, semicolon, space, and fixed character count.

A “Convert Text to Columns Wizard” will open.

Result: The data of column A is separated into three columns, as shown below.

Example #5 Split Cell in Excel Using Flash Fill

Another method to Split Cells in Excel is by Flash Fill. Flash Fill automatically populates cells with desired data. The Flash Fill method is easy to use and quickly splits data into multiple columns.

Flash Fill will automatically populate similar data (first name) into other cells. The entire column of B displays the first name of the original names.

Step 4: Repeat steps 3 on Cell C2 or select cell C2 and press “Ctrl + E“.

Note: The keyboard shortcut for Flash Fill is “Ctrl + E“.

Result: The first and second names are split successfully.

Example #6 Split Cell in Excel Using Text Functions

Although Flash Fill is easy to use, the results are not dynamic. It does not automatically update the output if the source data is changed. Thus to Split Cell in Excel, we can use various Text functions or create formulas to fetch results if we want dynamic results.

Solution:

Step 3: Now, select cell B2 and drag it down.

Note: The formula will display “#VALUE1″ if the original data contains no middle name.

Result: Using text functions, we have successfully separated the first, middle, and last names.

Note: Spilt Cell in Excel using the Text function is dynamic means if the value of the original data is changed, it will automatically get reflected in the output. For example, we have changed Matthew in Cell A2 to Soloman and James to John in Cell A6. The function automatically altered the value of Cell B2 to Soloman and Cell B6 to John, as shown below.

Step 9: Press “Enter“.

Result: The cells which show “#VALUE!” will be blank.

Things to Remember

The keyboard shortcut for unmerging cells is “Alt + H+ M + U“.

The shortcut for flash fill is “Ctrl+ E“. The result from Flash Fill is static.

The text functions in Excel are dynamic, which means if you change the values of the original data, it will automatically get reflected in the result.

Frequently Asked Questions(FAQs)

Answer: Split Cell in Excel is a feature to split or divide the content of single cells into numerous cells depending upon the task. For example, split cells are useful in separating first and last names.

Answer: There are various methods to Split Cell in Excel to extract data which are as follows:

Method

Description

Power Query Splits a column’s data into multiple columns based on delimiters, positions, characters, etc.

Text to Columns Splits the contents of a cell into two or more columns based on comma, semicolon, space, and fixed character count.

Text Functions Uses functions like MID, RIGHT, and LEFT for dynamic results.

Flash Fill Automatically splits strings into cells in the simplest way.

Answer: Merging of cells is joining two or more cells into a single cell. In contrast, splitting is the opposite of merging. Splitting cells means separating or dividing one cell content into two or more cells.

Recommended Articles

This has been a guide to Split Cell in Excel. Here we discuss how to use the Split Cell in Excel along with practical examples, and we have also provided a downloadable Excel template. You can also go through our other suggested articles –

Update the detailed information about Top 4 Vba Macro Excel Examples For Beginners on the Flu.edu.vn 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!