Excel is powerful. If you use it a lot, you probably already know a lot of tricks using formulas or auto-formatting, but making use of Ranges and Cells function in VBA, you can take your Excel analytics to a whole new level.

The problem with using the Cells and Range functions in VBA macros is that at the advanced levels, most people have a hard time understanding how these functions actually work. Using them can get very confusing. Here's how you can make use of them in ways you probably never imagined.

Visual Basic Cells Function

The Cells and Range functions let you tell your VBA script exactly where on your worksheet you want to obtain, or place data. The main difference between the two cells is what they reference.

The VBA cells function usually references a single cell at a time, while Range references a group of cells at once.

Here's the format for the cells function:

            Cells(row, column)
    

This references every single cell in the entire sheet. This is one such example where the VBA macro cells function doesn't reference a single cell:

            Worksheets("Sheet1").Cells
    

On the contrary, this piece of code references the third cell from the left, in row number one, i.e., Cell C1:

            Worksheets("Sheet1").Cells(3)
    

Let's get a little adventurous, by referring cell D15 using the VBA cells function:

            Worksheets("Sheet1").Cells(15,4)
    

If you wanted to, you could also reference the cell D15 with the following cells function VBA code:

            Cells(15,"D")"
    

---you're allowed to use the column letter.

There is a lot of flexibility using the VBA cell function, as you can reference a cell using a number for column and cell, especially with scripts that can loop through many cells (and perform calculations on them) very quickly.

The Range Function

In many ways, the Range function is far more powerful than using Cells, because it lets you reference either a single cell or a specific range of cells, all at once. You aren't going to want to loop through a Range function, because the references for cells aren't numbers (unless you embed the VBA Excel cells function inside it).

The format for this function is as below:

            Range(Cell #1,Cell #2)
    

Each cell can be designated by a letter number. Let's look at a few examples.

The following code is a range function referencing cell A5:

            Worksheets("Sheet1").Range("A5")
    

Here, the range function is referencing all cells between A1 through E20:

            Worksheets("Sheet1").Range("A1:E20")
    

As mentioned above, you don't have to use number-letter cell assignments. You could actually use two VBA cell functions inside a Range function to identify a range on the sheet, like this:

            

With Worksheets("Sheet1")
 .Range(.Cells(1, 1), _ 
 .Cells(20, 5))
End With

    

This code references the same range as Range("A1:E20") function does. The value in using it is that it would allow you to write code that dynamically works with ranges using loops.

Now that you understand how to format the Cells and Range functions, let's dive into how you can make creative use of these functions in your VBA code.

Processing Data With Cells Function

The Visual Basic cells function is quite useful when you have a complex formula that you want to use across multiple ranges of cells. These ranges can also exist across multiple sheets.

Let's take a simple example. Let's say you manage a sales team of 11 people, and every month you want to look at their performance.

You might have Sheet1 that tracks their sales count and their sales volume.

processing data - excel functions vba

On Sheet2, you have data to track their feedback rating for the last 30 days from your company's clients.

processing data - excel functions vba

If you want to calculate the bonus on the first sheet using values from the two sheets, there are multiple ways to do this. You could write a formula in the first cell that performs the calculation using data across the two sheets and drag it down. That'll work.

An alternative to this is creating a VBA script that runs whenever you open the sheet, or you trigger it via a command button on the sheet, so that you can control when it calculates. You might use a VBA script to pull in all the sales data from an external file anyway.

So why not just trigger the calculations for the bonus column in the same script at that time?

Excel Macro Cells Function in Action

If you've never written VBA code in Excel before, you'll need to enable the Developer menu item. To do this, go to File > Options. Click on Customize Ribbon. Finally, choose Developer from the left pane, Add it to the right pane, and make sure the checkbox is selected.

Microsoft Excel customize ribbon

Now, when you click OK and go back to the main sheet, you'll see the Developer menu option.

You can use the Insert menu to insert a command button, or just click View Code to start coding.

Microsoft Excel view code

In this example, you will set up the script to run every time the workbook opens. To do this, just click View Code from the developer menu, and paste the following new function into the code window.

            Private Sub Workbook_Open() 

End Sub
    

Your code window will look something like this.

excel functions vba code

Now you're ready to write the code to handle the calculation. Using a single loop, you can step through all 11 employees, and with the Excel VBA cells function, pull in the three variables needed for the calculation.

Remember the worksheets.cells function has row and column as parameters to identify each individual cell. You need to replace x with the row reference and use a number to request each column's data. The number of rows is the number of employees, so this will be from 1 to 11. The column identifier will be 2 for Sales Count, 3 for Sales Volume, and 2 from Sheet 2 for Feedback Score.

The final calculation uses the following percentages to add up to 100 percent of the total bonus score. It's based on an ideal sales count being 50, sales volume of $50,000, and a feedback score of 10.

  • (Sales Count/50) x 0.4
  • (Sales Volume/50,000) x 0.5
  • (Feedback Score/10) x 0.1

This simple approach gives sales employees a weighted bonus. For a count of 50, a volume of $50,000, and a score of 10---they get the entire maximum bonus for the month. However, anything under perfect on any factor reduces the bonus. Anything better than ideal boosts the bonus.

Now let's look how all of that logic can be pulled off in a very simple, short VBA script:

            Private Sub Workbook_Open()
For x = 2 To 12
Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _
 + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _
 + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

    

This is what the output of this script will look like.

excel functions vba output

If you wanted to have the Bonus column show the actual dollar bonus rather than the percentage, you could multiply it by the maximum bonus amount. Better yet, place that amount in a cell on another sheet, and reference it in your code. This would make it easier to change the value later without having to edit your code.

The beauty of the cells' method in VBA is that you can build some pretty creative logic to pull in data from many cells across many sheets and perform some pretty complex calculations with them.

You can perform all sorts of actions on cells using the Cells function---things like clearing the cells, changing font formatting, and much more.

To explore everything you can do further, check out the Microsoft MSDN page for the Cells object.

Formatting Cells With Range Function

For looping through many cells one at a time, the cells' function is perfect. But if you want to apply something to an entire range of cells all at once, the Range function is far more efficient. In the ongoing comparison of VBA's range vs cells, there is a lot to look forward to.

One use case for this might be to format a range of cells using script if certain conditions are met.

formatting cells - excel functions vba

For example, let's say if the tally of all sales volume across all sales employees surpasses $400,000 in total, you want to highlight all cells in the bonus column in green to signify that the team has earned an extra team bonus.

Let's take a look at how you can do that with an IF statement.

            Private Sub Workbook_Open()
If Worksheets("Sheet1").Cells(13, 3).Value > 400000 Then
 ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4
End If
End Sub
    

When this runs, if the cell is over the team goal, all cells in the range will be filled in green.

This is just one simple example of the many actions you can perform on groups of cells using the Range function. Other things you can do include:

  • Apply an outline around the group
  • Check the spelling of text inside a range of cells
  • Clear, copy, or cut cells
  • Search through a range with the Find method

Make sure to read the Microsoft MSDN page for the Range object to see all the possibilities.

Take Excel to the Next Level

Now that you understand the differences between the Cells and the Range functions, it's time to take your VBA scripting to the next level. Dann's article on using Counting and Adding functions in Excel will allow you to build even more advanced scripts that can accumulate values across all of your data sets very quickly.