VBA MID Function

What is Excel VBA MID Function?

Excel VBA MID function is a built-in function used to extract a substring from a string, starting at a specified position and length. The function takes three arguments: the string from which to extract, the start position, and the length of the substring. It’s a very useful function when working with strings and extracting specific portions from them.

Let’s look at an example. Here, we are given a date as a string in the “dd-MMM-yyyy” format. The string is “25-Dec-2023.” We want to extract the day part of this date string, “25.” To do this, we first declare a variable “dateStr” to hold the date string and assign “25-Dec-2023” to it and another variable day to hold the extracted day. Next, we assign to “dateStr.”

We then use the VBA MID function to extract the day from “dateStr.” We get the “25” result assigned to the day variable.

We use the Debug.Print statement to print the value of the day to the Immediate Window.

VBA MID Function Syntax

Here is the syntax or the VBA MID function reference:

Mid(string, start, [length])

Where:

Parameter	Description
str	The string from which to extract the substring.
start	The position where the extraction should start. The first character in the string is at position 1.
length	(Optional) The number of characters to extract. If not provided, all characters from the start position to the end of the string are extracted.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to use VBA MID Function?

To use the MID function in VBA, follow these steps.

Step 1: Open Excel and press “ALT + F11” to open the VBA editor.

Step 2: Insert a new module by clicking the “Insert” in the menu bar and selecting “Module.”

Step 3: Write the following VBA code within the module.

Step 4: In this example, the VBA MID function is used to extract the substring “World” from the original string “Hello, World!” starting at position eight with a length of 5 characters. The extracted substring is then displayed in a message box using the MsgBox function. Close the VBA Editor.

Step 5: Run your code by pressing “Alt + F8” to open the “Macro” dialog box in Excel, selecting the desired macro, and clicking on the “Run” button.

Examples

Let us look at some examples on how to use the VBA MID function.

Example #1

In this example, we will extract a specific substring from the text string “Hello World!” using the VBA MID function and print the result to the Immediate Window in VBA.

Step 1: In the new module, we will first define a new subroutine named “Example1.”

Step 2: Here, we are declaring a variable named ‘str.’ The ‘Dim’ keyword is used to declare a variable in VBA. The ‘As String’ part specifies that the variable ‘str’ will be used to hold text (i.e., it’s a string variable).

Step 3: Now, we assign the text string “Hello World!” to the variable ‘str.’ It is the string from which we will later extract a substring.

Step 4: In the next line, we do two things.

“Mid(str, 7, 5)”: The MID function is used to extract a specific substring from a string.

• It takes three arguments: the original string, the starting position, and the length of the substring.
• In this case, the original string is ‘str’ (which contains “Hello World!”), the starting position is 7, and the length is 5.
• It means it will extract a substring of length five characters from the 7th character of ‘str.’
• Hence, it extracts “World” from “Hello World!”

“Debug.Print”: It is used to print the resulting string “World” to the Immediate Window in VBA, which is used for debugging and testing in VBA. If you don’t see the Immediate Window in your VBA editor, press Ctrl + G to open it.

Step 5: Now, save the module and run the code. You will see the word “World” populate in the Immediate window at the bottom, thus extracting a specific substring from the string.

Here is the complete code:

Sub Example1()
Dim str As String
str = “Hello World!”
Debug.Print Mid(str, 7, 5)
End Sub

Example #2

In this example, we will try to extract a substring from the string “Hello World!” starts from a specified position and prints the result to the Immediate Window in VBA.

Step 1: In the new module, let’s begin by defining a new subroutine named “Example2.”

Step 2: Next, we declare a new variable named ‘str’ using the ‘Dim’ keyword. ‘As String’ tells VBA that ‘str’ is a string variable, which means it will hold text.

Step 3: In the following line, we assign the text string “Hello World!” to the variable ‘str.’ We will later extract a substring from this string.

Step 4: In the next line of code, we define two things as follows.

“Mid(str, 7)”:

Here, ‘str’ holds the string “Hello World!” and the starting position is 7. The MID function will extract all characters from the 7th character till the end of the string because no length for the substring is specified. So, it extracts “World!”.

“Debug.Print”: This command prints the extracted substring “World!” to the Immediate Window in VBA. You can see the Immediate Window in your VBA editor by pressing Ctrl + G if it’s not already visible.

Step 5: Now, save the module and run the code. You will see the word “World!” populate in the Immediate window at the bottom. Thus, extracting the specific substring from a string.

Here is the complete code:

Sub Example2()
Dim str As String
str = “Hello World!”
Debug.Print Mid(str, 7)
End Sub

Example #3

In this example, we will extract a specific substring from the text in the cell “A1” of the active worksheet in Excel. It then prints the result to the Immediate Window in VBA.

Step 1: In the new module, begin the definition of a new subroutine named “Example3.”

Step 2: Then, we declare a new variable named ‘str’ and ‘As String’ which means that the variable ‘str’ is intended to hold text.

Step 3: in the next line we assign the value in cell “A1” of the active Excel worksheet to the variable ‘str.’ ‘Range(“A1”).Value’ gets the value from cell A1. It is the text from which we will extract a substring.

If cell A1 contains the text “John Smith,” then ‘str’ would now hold the value “John Smith.”

Step 4: Here we perform two actions:

“Mid(str, 1, 4)”: The MID function three arguments: the string from which to extract the substring, the position at which to start the extraction, and the number of characters to extract.

In this case, the string is ‘str’, the start position is 1, and the length of the substring is 4. This means the function extracts the first 4 characters from the string in ‘str’.

“Debug.Print”: This command prints the result of the MID function to the Immediate Window in VBA. The Immediate Window can be used for testing and debugging VBA code. If you can’t see this window in your VBA editor, you can open it by pressing Ctrl + G.

Step 5: Now, save the module and run the code. You will see the word “John” populate the Immediate window at the bottom.

Here is the complete code:

Sub Example3()
Dim str As String
str = Range(“A1”).Value
Debug.Print Mid(str, 1, 4)
End Sub

Important Things To Note

1. In VBA, the indexing of string characters starts from 1 and not 0. It means that the first character is at position 1. This is an important aspect to remember while specifying the start position in the MID function to correctly extract the desired substring.
2. If the ‘start’ position is larger than the total number of characters in the string, the MID function will return an empty string. There won’t be any error thrown in such a scenario.
3. If the ‘length’ parameter is greater than the number of characters available in the string starting from the ‘start’ position, the MID function will return all characters from the ‘start’ position to the end of the string. It does not generate an error.
4. If non-numeric values are provided as ‘start’ or ‘length’, VBA tries to convert them to numbers. If the conversion is unsuccessful, VBA will throw a ‘Type Mismatch’ error.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA MID Function. We learn how to extract substring from a string using MID Function in Excel with examples. You can learn more from the following articles –

• User Defined Function in VBA
• VBA ByRef Argument
• VBA Screen Update