7.6 The Set Keyword in Excel VBA

The Set keyword is used to create new objects, creating a new Range, for example. The Set keyword comes in handy when you want to simplify long lines of code. It is also speeds up your code when VBA executes it.

Take these two lines as an example:

Dim NewRange As Range
Set NewRange = Range("A2:A6")

The first line introduces you to a new variable type - As Range. This object variable type is used to hold a range of cells from your spreadsheet.

The next line starts with the word Set. Next, you need the name of your object variable, which is NewRange for us. After an equal sign, you type what it is you're trying to Set as a new object.

The code above, however, is pretty pointless, as we're not doing anything with this new range. In this next example, we'll use the inbuilt Find method to search the cells in the new range.

Suppose you had a spreadsheet that looked like this:

What you want to do is to find out the name of the person with the highest score and the name of the person with the lowest score. How would you do it with Excel VBA?

The first thing to do is to set up some variables:

Dim HighestScore As Long
Dim LowestScore As Long
Dim TopScorer As Range
Dim BottomScorer As Range

Notice that the first two variables are As Long and the last two or As Range.

Next we can get the Min and Max scores from the B column:

LowestScore = WorksheetFunction.Min(Range("B1:B5"))
HighestScore = WorksheetFunction.Max(Range("B1:B5"))

Once we have these scores we can use the Find method and Set a new Range:

Set BottomScorer = Range("B1:B5").Find(What:=LowestScore)
Set TopScorer = Range("B1:B5").Find(What:=HighestScore)

In between the round brackets of Find, you need at least one parameter - the item that you want to find. (Find has an optional 7 parameters which you can read about here Find Method Parameters.)

In our code we want to find the lowest and highest score from the specified range. This score is the Set up as a new range, in the variables BottomScorer and TopScorer.

Because these are now new range objects you can use things like Offset with them:

Range("B7").Value = BottomScorer.Offset(, -1).Value
Range("B8").Value = TopScorer.Offset(, -1).Value

Here, we're using Offset to get the next cell to the left of the BottomScorer and TopScorer. This will give is the name of the person with those scores.

The whole of the code looks like this:

The reason why we're doing it this way is so that we don't have one very long line that is hard to read. This line:

LowScore = Range("B1:B5").Find(WorksheetFunction.Min(Range("B1:B5"))).Offset(, -1).Value

The line above does exactly the same job as our code. But our code is easier to read and debug. Plus, the single line will actually execute slightly slower than the full code.

Don't worry about Set too much. Just bear in mind that you can create new objects variables with the Set keyword.

In the next section, we're going to move on and discuss how to open text files with Excel VBA code.

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney