You can utilize the built-in Excel Worksheet Functions such as the VLOOKUP Function, the CHOOSE Function and the PMT Function in your VBA code and applications as well. In fact, most of the Excel worksheet functions can be accessed and used in VBA code. Only a few worksheet functions are exempt from this rule, and this is in the case where there is already a VBA equivalent function.

Why would you want to use Excel Worksheet functions in your VBA code? Well to extend the functionality of the code you are using. Also, you don’t have to come up with your own functions, unless you really need to, if the functionality is already there. All you basically need to do is access the function you need since it’s already there and there is then no need to reinvent the wheel.

So, let’s get started with an example showing how to utilize Excel Worksheet Functions in VBA code.

We are going to utilize the INDEX and MATCH Functions in our VBA code, in order to create a simple UserForm. Using the form, the user selects a name of the student, and then the corresponding gender of said student and eye color is retrieved and returned.

Introduction

Using Index and Match within VBA Code

The INDEX and MATCH Functions are often used in combination in formulas, in order to perform advanced lookups. The two in combination offer certain advantages over VLOOKUP.

We have already covered in detail, how to use INDEX and MATCH to perform advanced lookups in an Excel workbook as a straight worksheet formula, in a previous tutorial. We are now going to see how to use the INDEX and MATCH Functions together in VBA code, in order to confer similar functionality to the look up UserForm we are going to create.

1) We are starting off with two sheets in our macro-enabled workbook. One is an empty sheet called UserForm, the other is a sheet called StudentInformation, which contains a range showing student names, their corresponding gender, and eye color as shown below.

=INDEX(B2:B31,MATCH(“Diana Graham”,A2:A31,0))2)

Let’s remind us quickly if we wanted to use the INDEX and MATCH Functions in one formula, in the actual worksheet to give us the gender of the name of the student we want to look up. We would use the following formula:

3) Upon pressing CTRL-ENTER, we get the value of Female returned, as the gender as shown below.

4) We will now name the range A2: A31, StudentNames as shown below.

5) Hide the StudentInformation sheet, by right-clicking and selecting Hide. It’s good to idea to superficially hide the back-end worksheets that contains the information, that you don’t want the user to edit or see.

6) Now with the UserForm sheet activated, we go to Developer>Code>Visual Basic in order to open the Visual Basic Editor (VBE).

7) Once in the VBE interface, we go to Insert, UserForm as shown below.

8) Using the Properties Window, we will rename our form to StudentLookup, change the Caption to Look up Student Information, change the BackColor to light blue and set the height to 300 px and the width to 350 px. If the Properties Window is not showing up, press the F4 key on your keyboard in order to see it.

9) We will now insert a label using the Toolbox (if you cannot see the Toolbox, for some reason go to View, Toolbox), change the Caption to Choose a student and we will change the BackColor to white in this case. We will set the font to Georgia, the font style to bold, the font size to 12, and center align the text. The special effect used will be the 1– fmSpecialEffectRaised as shown below.

10) Now we will insert a combo box below the label. Name this combo box cmdStudentName and for the RowSource, type StudentNames (this is the named ranged containing the student names that we named in the actual worksheet).

11) In order to see the effect of setting the RowSource of the combo box, click the Run Sub/UserForm button as shown.

12) Now because of setting the RowSource to the named range, when the user clicks on the drop-down arrow on the UserForm, the combo box is now populated with the student names from the named range, automatically as shown below.

13) Close the UserForm by clicking on the close button. Press Alt-F11 in order to go back to the VBE.

14) Once back in the VBE, add another label to the UserForm (below the combo box) and change the Caption to Gender and we will change the BackColor to white in this case. We will set the font to Georgia, the font style to bold, the font size to 12, and center align the text. The special effect used will be the 1– fmSpecialEffectRaised as shown below.

15) Create a textbox below the Gender label, and name it txtGender, as shown below.

16) Add another label called Eye Colour and a textbox named txtEyeColour as shown below. Use the same properties for the label as for the two other labels previously added to the form, in order to ensure that the UserForm has a consistent look.

17) Now select all the controls, added to the UserForm, thus far using the control key.

18) Center horizontally, as shown below.

19) Next, add a button to the form using the Toolbox. Change the Name of the button to cmdLookUp, the BackColor to light orange, keep the Tahoma font and change the style to bold, finally change the Caption of the button to Look up Student Details as shown below.

20) Right-click, the newly added button, and select View Code.

21) Enter the following code for the button click event:

Dim a As Variant

Dim b As Variant

Dim c As Variant

a = cmdStudentName.Value

Sheets(“StudentInformation”).Activate

If a = “” Then

b = “”

Let txtGender.Text = b

c = “”

Let txtEyeColour.Text = c

Else

b = Application.WorksheetFunction.Index(Sheets(“StudentInformation”).Range(“B2:B31”), Application.WorksheetFunction.Match(a, Sheets(“StudentInformation”).Range(“A2:A31”), 0))

Let txtGender.Text = b

c = Application.WorksheetFunction.Index(Sheets(“StudentInformation”).Range(“C2:C31”), Application.WorksheetFunction.Match(a, Sheets(“StudentInformation”).Range(“A2:A31”), 0))

Let txtEyeColour.Text = c

End If

We start off by declaring three variables and assigning the variant data type to these declared variant data types. The variant data type is a good data type to get started with, since when working with worksheet functions, you may not always be sure of the outputs, therefore use the variant data type, when you are starting out.

Later on, as you become more experienced with VBA and the different data types, it is advisable to use one of the other more specific data types such as integer or string, since, for more advanced longer code, the variant data type does not use memory as efficiently as the other data types.

Variable a’s value is drawn from the option the user selects in the drop-down combo box on the UserForm. If there is no selection, then all the other textboxes are empty.

If a student name is selected from the combo box on the UserForm then variable b’s value is attained by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code, as shown.

It looks up the value using basically the same syntax as the worksheet function.  When using worksheet functions in VBA, the VBA IntelliSense in this particular case is not very intuitive, therefore a familiarity with the syntax gleaned from worksheet knowledge is recommended.  Variable c’s value is also attained by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code when the user selects an option from the combo box.

Variable b’s value is sourced from the gender column in the worksheet, whereas variable c’s value is sourced from the Eye color column in the worksheet.

The gender textbox is populated with b’s value and the eye color textbox is populated with c’s value.

22) Now go to the worksheet called UserForm in your workbook. Format it, as shown below and insert the image provided by ExcelDemy.

23) Go to Developer>Controls> Insert> ActiveX Controls, and insert a button as shown.

24) With the button selected, go to Developer>Controls>Properties.

25) Change the Name of the button to cmdShowForm and the Caption to Look up Student Information.

26) Right-click the button and select View Code as shown below.

27) Enter the following code:

Private Sub cmdShowForm_Click()

StudentLookup.Show

End Sub

28) Return to the worksheet and make sure Design Mode is unchecked and then click the button in order to show the form.

29) Select a student name using the combo box and the student gender and eye color is returned automatically.

Remember to save your workbook as a macro-enabled workbook, if you haven’t done so already and there you have it, we have utilized the worksheet INDEX and MATCH Functions together in VBA in order to create a lookup form.

Download Working File

Using-Worksheet-Functionsin-VBA-Macro-Enabled

Conclusion

Excel has many useful worksheet functions, which can be utilized in VBA as well. These functions will allow you to extend your VBA code and if you already know how they work in a standard Excel worksheet then the learning curve is not that great, with respect to adapting the knowledge for VBA. Accessing the worksheet functions, in one’s VBA code can be a real time saver since then one does not have to develop custom functions for functionality that is already there.

Please feel free to comment and tell us if you use worksheet functions in your VBA code and applications.

Useful Links

How to Use the Index Function

How to Use the Match Function

How to Use Index and Match together to perform lookups

Excel VBA Data Types: The Complete Guide To 15 Important Data Types

Complete list of Excel Worksheet Functions available to VBA

The post Using Excel Worksheet functions in VBA appeared first on ExcelDemy.com.