Contents
If you often work with spreadsheets, it is strongly recommended that you familiarize yourself with macros. These are microprograms configured to automate certain actions. They allow you to significantly expand the capabilities of both Excel itself and a particular user.
One of the key concepts of the VBA programming language (namely, macros are written on it) is a function. This is a program block that processes certain data and returns some specific result.
One of these functions is InStr. Let’s take a closer look at its main arguments, as well as real-life examples describing its use in real life.
But let’s first find out why macros are needed? Because many novice Excel users often mistakenly believe that this feature is redundant. But in fact, they just worked very little with this program. As soon as we increase the duration of interaction with spreadsheets, it turns out that we often perform thousands of similar actions that a computer program can perform in just a few seconds. It saves a lot of time and effort, and also unloads the brain.
So let’s take a closer look at the InStr function.
Description of the InStr function
Using the InStr function, the user can find the location of the first occurrence of some text within another. The return data type implemented by this function is Variant.
The scope of this function is very wide, and it is impossible to do without it when using other functions such as Left, Mid, Right. In addition, it can be used to search for some text.
There is a similar function in VBA called InStrB. Its difference lies in the fact that byte data is processed. With its help, you can find out the location of a byte, not a character. You can learn more about this function in the third example.
The advantages of the InStr function are as follows:
- The ability to quickly process huge amounts of data in just a few seconds. When a person searches manually, it only seems that everything is done by a computer. In fact, it only does part of the job. But the main thing is done by the person himself. for example, you need to drive in a search string, click on the arrows to search for an entry specific to the account, and so on. This is very costly. In the case of the VBA InStr function, this goal can be achieved using macros: using a formula, pass arguments to the function, and then continue to use the resulting result in another function. Thus, a huge number of actions are automated, which, other things being equal, would have to be performed manually.
- Saving time. There are employees who, due to macros, were able to significantly increase the efficiency of their work and actually not work most of the time. All they had to do was write the script once, and then simply pass the necessary parameters to it. And the InStr function is an important part of this process, since you have to deal with strings all the time if a person actively uses spreadsheets.
- Saving resources, including intellectual ones. This is also a very important point. It’s no secret that the constant performance of the same type of action is incredibly tiring. Therefore, the InStr function allows you to get rid of the mortal burden of constantly manually searching for values, especially if a formula is used for this.
By the way! Using the InStr function gives you the opportunity to greatly improve your creativity!
How? Very simple. Researchers have proven that the main factor that hinders the creative process is an excessive amount of routine activities. Within certain limits, they help to concentrate (for example, when a person draws something on an automaton), but after a while, the corresponding neural connections begin to lose speed in the conduction of nerve impulses. Consequently, there is not enough strength for other tasks. And creativity is an intellectually costly process.
If you use the InStr function, you can free yourself from this mortal fate and pay attention to other important things and create not just a beautiful, but a functional table that works completely automatically and at the same time contains all the necessary data.
And now, after a lyrical digression, let’s move on to examining the syntax of the InStr function.
InStr function syntax, parameters, values
Syntax of InStr function
If we talk about this function in general, it is written in this way.
InStr([start], line1, line2, [comparison])
But this option is complete, but very often a shortened version of this function is used, containing only two arguments.
InStr(string1, string2)
As a rule, it is the abbreviated version that is used in practice. This is not to say that it does not have a first and last argument at all, just those values that are defined as “default” are automatically applied.
InStr Function Parameters
Let’s describe in more detail what each of the arguments means:
- Start. This parameter is optional. It contains a number that records the initial position from which the search begins.
- Line1. This argument must not be forgotten. This is exactly the text in which you need to search.
- Line 2. This is what we are looking for.
- Comparison. With this argument, the user can specify how strings are parsed and matched.
It is important to consider some nuances:
- If the user wrote down the last value, then the first one must be entered.
- If the “Comparison” parameter is not specified in the function, then Excel uses the default value (0 or another, subject to the presence of the Option Compare instruction).
- If the user specifies NULL in the optional arguments, the formula will throw an error. This must be kept in mind.
Well, how? Difficult? Probably, everything is simple. At first glance, it may seem that remembering all this is quite difficult. But in practice, the skill is honed. Therefore, it is highly recommended to practice “in the sandbox”.
Values of the “comparison” argument
With the compare argument, a person can specify the type of comparison. This is done by specifying a value expressed in digital format. If 1 is used as the value of this argument, then the parameter means to compare two measures using the Option Compare statement.
If this argument is set to 1, then the function performs a binary comparison. In simple terms, this type searches for a case-sensitive value. In the case of a text comparison, the case is not taken into account.
Each value returned by the function indicates the fulfillment of one of the possible conditions:
- The position of the first match found. If a string is returned that is similar to what was found in the very first case, then the search was successful.
- 0. This value indicates that the search was unsuccessful. In simple words, it was not possible to find the second line in the first line. It is also not uncommon to encounter such an error in situations where, when trying to search, it turns out that the first line does not contain any values, or the value passed to the “beginning” argument turns out to be greater than the length of the first line.
- The same value that is passed to the start argument. This tells you that the second line does not contain any values.
- NULL. This issue occurs in situations where either line 1 or line 2 contains a similar error. The most common cause of this problem is when the user specified a range incorrectly (for example, they specified the wrong range operator). Also, if several overlapping ranges are entered, this error can occur if a person incorrectly applied the range operator (which is the space character).
In the latter case, you need to make sure that a colon was used to indicate the range. For example, if the range from A1 to A10 is used, then you need to write it as A1:A10.
If you need to specify several sets of cells that do not intersect each other, then the semicolon symbol is used (some say that a comma is used, but this is incorrect). For example, the correct input is: A1:A10;C1:C10.
If a space is used, you need to make sure that the ranges really intersect. If they are located independently of each other, this can give rise to many problems. Therefore, you need to either replace the sign separating the ranges with a semicolon, or change the ranges so that they intersect in one place or another.
Well, a simple example for clarity. Suppose we have ranges A1: A5 C1: C3.
In this case, the cells do not overlap in any way. Accordingly, the value NULL will be returned. Therefore, the user will see, after evaluating the formula using arguments that return this value, also NULL. After all, it is impossible to search in those cells where there is no normal value, even if it is a numeric value (although the InStr formula is better suited for searching text matches).
If automatic error checking is enabled in Excel, then an exclamation mark will appear next to the erroneous cell. If you click on it, you will be able to select the situation that suits the current problem.
Examples of using the InStr function in VBA Excel
There are many uses for the InStr function when developing macros. Let’s look at three simple examples to make it more clear how you can use it in real (or imaginary) conditions.
Example 1
This example is designed specifically for beginners as it demonstrates pure use of the InStr function. Therefore, it is the simplest.
Sub Test1()
Dim x As Variant
x = InStr(“On Mount Fernando Po, where Hippo Po walks”, “Fernando”)
MsgBox x
‘Here x will be equal to 9
End Sub
Example 2
This example shows how textual and binary comparison can be used to find matches where case is important and not. In this case, for example, two strings with the same content were used to make it easier to visualize how Excel is case-sensitive or does not respect a character in macros.
Sub Test2()
Dim x As Variant
x = InStr(10, “On Mount Fernando Po, where Hippo Po walks”, “po”, 0)
MsgBox x
‘Here x will be equal to 36 (case sensitive search)
x = InStr(10, “On Mount Fernando Po, where Hippo Po walks”, “po”, 1)
MsgBox x
‘Here x will be 18 (case insensitive search)
End Sub
Here we see that the tenth character is designated as the initial value. But despite this, the first occurrence is analyzed, starting with the line in which the information is analyzed for compliance with the given value.
Example 3
And in this case, comparisons of character-by-character and byte-by-byte types are compared. Here, too, for greater clarity, we used the same strings and substrings that we will search for. And here is the example itself.
Sub Test 3()
Dim x As Variant
x = InStr(“On Mount Fernando Po, where Hippo Po walks”, “mountains”)
MsgBox x
‘Here x will be equal to 4
x = InStr(“On Mount Fernando Po, where Hippo Po walks”, “mountains”)
MsgBox x
‘Here x will be equal to 7
End Sub
Conclusions
Thus, the InStr function in the VBA programming language, which is used in macros, has a lot of uses. It is useful when there is a large amount of data that cannot be analyzed manually, and built-in automation tools such as search are also time-consuming and labor-intensive.
In fact, this function is the same search, with the only exception that you can ask Excel in advance under what conditions and how it will be carried out.
There are many other magical functions used to write macros, and we will definitely introduce you to them next time.