Microsoft is the industry leader in providing user applications which offer both functionality and power. The Microsoft office applications are a great example of this. The office suite of applications are not merely standard spreadsheets and word processors, but are in fact powerful applications. Due to the inclusion of Visual Basic for Applications, or VBA, within their suite of office applications, programs like Excel are far more powerful than a mere spreadsheet.
This tutorial will show you how to access the power of VBA in Excel. It will show you how to use the VBA instr function to search within a target string to confirm the presence of a string within the target string. The tutorial assumes that you have some basic Excel skills and abilities. To get the most out of this tutorial, it is highly advisable that you have completed a course like Excel Course (Basic and Advanced), which will ensure you have a solid foundation in general Excel.
How the to access VBA functions and commands
Depending on the function, you can access VBA commands within Excel via the cell itself or via the VBA editor. To enter a VBA function into the cell, you need to begin in the cell where you would like the answer to appear. You start by typing the equal sign followed by the function you would like to call. To run the IF function for example, you can select a cell and then type “=IF”. The VBA function syntax will appear as a guide to show you how to use the function. Take a look at the example code below:
Some VBA functions are not available within the cell, but must be entered via the VBA editor. To access the VBA editor, you can either add an ActiveX control to your worksheet, or you can access the editor directly using the developer tab on the menu. To access it directly, click the developer tab and then click Visual Basic. This will open the editor.
For this tutorial, we will add a command button to our worksheet to validate email addresses.
How the Excel VBA instr function works
The VBA instr function allows you to search a target string to see if the string contains a specific search string. Instr is short for “in String”. The parameters of the instr function include the following:
inst( [start], [string1], [string2], [compare])
The start parameter allows you to specify where the search should start within the string. This parameter is an optional parameter. If unspecified then the search will begin at the first character within the cell.The string 1 parameter indicates the target string to be searched and must be specified. The string 2 parameter indicates the string to be searched for and must be specified. The compare parameter is optional and can be used to specify how the strings will be compared.
Practical VBA instr example
For the purposes of this tutorial, we will create a spreadsheet that contains the names, email addresses and phone numbers of a group of people.
This is what your initial spreadsheet should look like:
For the tutorial, let us assume we want to ensure that all of the data we have entered into the email field is valid. We know that a valid email address must contain an “@” sign within the address. You could manually check each field, but in a spreadsheet with a few thousand email addresses, this may not be the fastest or the most practical solution.
This is where the VBA instr function can help us to ensure that all emails look like valid email addresses. We can use the VBA instr function to search the email address string to ensure that the string contains an @ character. To use this function in our worksheet we need to add an ActiveX control to insert the function within the worksheet. We will add a command button for our spreadsheet. To add the command button, choose the developer tab, choose insert and choose the command button. Double click the command button to enter the VBA editor.
First you need to declare a few variables so that we can refer to the cells within the worksheet. If you don’t know what variables are or how to declare them then a course like macros and VBA excel from udemy will teach you how to work with variables within VBA.
You can use the following code to declare the variables:
You then need to assign initial values to the variables and use a for statement within VBA to move through the range within your worksheet. You also need to assign a value to your email_Add variable using the variables to determine the row and column of the worksheet.
Now that you have a value to compare, you can use the instr function. The instr function would be formulated as follows:
The above function would initialize the instr function. It would begin the search of the string at the very first character within the cell because the first parameter has not been specified. It would search the field we called email_Add which is given the value of each cell in our range. The function would check if the cell contained an @ character. If it finds the @ character it will return the position where the @ character is found. If it doesn’t find the @ character, then it returns a zero.
Now that we have compared the string with the search string and we have a result we need to write the code which will tell our command button what to do if it finds the character as well as what to do if the character is not found. The code to achieve this would look as follows:
The result of the above code in our worksheet would be as follows:
The code we used includes a number of VBA functions other than instr. For training on how to implement and use other advanced VBA functions within Excel, the ultimate excel VBA course will teach you how to harness the power of VBA within your own spreadsheets.