Loading ...

How to use the match function in VBA

If you program applications in Visual Basic, you can use processing equivalents of Microsoft Excel functions in your VBA code. This allows you to carry out processes you would use in a spreadsheet. For example, you can include the match function in your VBA code by utilising the Worksheet Function object match method. The match method allows you to look up a value in a range of values, retrieving the position of the value within the range array. The match function can be tailored to particular look-up preferences in terms of what it returns.

Loading ...
  1. Declare a variable to store the result of the match function. For example, you could use the following: Dim matchVar as Variant Your code will read the match method result into this variable, so make sure it is going to be accessible where you include the match code.

  2. Browse to the location in your code where you wish to execute the match function. Call the match method. You can access the match method through the Worksheet Function object. Include it in your code as follows, preparing to store the match result in your variable: matchVar = Application.WorksheetFunction.Match()

  3. This lets your code refer to the match method processing via the application's Worksheet Function object. You will be able to include the details of your match process between the brackets.

  4. Specify the value you want to look up. In the match method brackets, you can include three parameters, the third of which is optional. The first parameter is the value you want to look up. Add it as follows: matchVar = Application.WorksheetFunction.Match(5)

  5. This would apply if you wanted to look up the number five. You can include dynamic values or variables instead of a hard-coded value.

  6. Specify the range you want to look up. In an Excel spreadsheet, you would specify the range to look up by selecting a range of worksheet cells. In VBA, you can achieve the same effect by specifying a Range object to represent the series of data values to check within. For example, the following indicates the values in cells B1 to B20: Worksheets("Wages").Range("B1:B20")

  7. This would be the equivalent of including the following in a cell within a worksheet named "Wages": B1:B20

  8. Include the range parameter in your match method call: matchVar = Application.WorksheetFunction.Match(5, Worksheets("Wages").Range("B1:B20"))

  9. You could alternatively store the range as a variable.

  10. Specify the match type, which is the optional third parameter. If you enter zero, the default, the method returns the first value in the range that is found to be equal to the look-up value specified as first parameter. If you enter negative one, with your range array sorted into descending order, the method will return the least value that is either equal to or greater than the look-up value. If you include positive one with the range sorted in ascending order, the method will return the greatest value in the range which is either equal to or less than the look-up value. Add your type parameter: matchVar = Application.WorksheetFunction.Match(5, Worksheets("Wages").Range("B1:B20"), 0)

  11. This is the equivalent of the following in Excel: \=MATCH(5, B1:B20, 0)

  12. Tip

    If you do not include the third type parameter, the default value of zero will be used.


    If you use a value of negative or positive one as the type parameter and your range values are not sorted, your code may encounter an error.

Loading ...

About the Author

Sue Smith started writing in 2000. She has produced tutorials for companies including Apex Computer Training Software and articles on computing topics for various websites. Smith has a Master of Arts in English language and literature, as well as a Master of Science in information technology, both from the University of Glasgow.

Loading ...