Excel is powerful. If you use it a lot, you probably already know a lot of tricks using formulas or autoformatting, but making use of Cells and Range functions in VBA, you can boost your Excel analytics to a whole new level.
The problem with using the Cells and Range functions in VBA 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.
The 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.
Cells usually reference a single cell at a time, while Range references a group of cells at once. The format for this function is Cells(row, column).
This references every single cell in the entire sheet. It’s the one example where the Cells function doesn’t reference a single cell:
Worksheets("Sheet1").Cells
This references the third cell from the left, of the top row. Cell C1:
Worksheets("Sheet1").Cells(3)
The following code references cell D15:
Worksheets("Sheet1").Cells(15,4)
If you wanted to, you could also reference cell D15 with “Cells(15,”D”)”—you’re allowed to use the column letter.
There is a lot of flexibility in being able to reference a cell using a number for column and cell, especially with scripts that can loop through a large number of cells (and perform calculations on them) very quickly. We’ll get to that in more detail below.
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 Cells function inside of it).
The format for this function is Range(Cell #1,Cell #2). Each cell can be designated by a letter-number.
Let’s look at a few examples.
Here, the range function is 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 Cells functions inside of a Range function to identify a range on the sheet, like this:
With Worksheets("Sheet1")
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
The code above references the same range as the 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 Cells function is most useful when you have a complex formula that you want to perform 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.
On Sheet2 is where you track their feedback rating for the last 30 days from your company’s clients.
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 VBA script that you either trigger to run whenever you open the sheet, or triggered by a command button on the sheet so you can control when it calculates. You might use a VBA script to pull in all of 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?
The Cells Function in Action
If you’ve never written VBA 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.
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.
In this example we’ll make the script run every time the workbook is opened. 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.
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 Cells function pull in the three variables needed for the calculation.
Remember the Cells function has row and column as parameters to identify each individual cell. We’ll make “x” the row, use a number to request each column’s data. The number of rows are 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 use 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 as $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, 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.
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 function is that you can build some pretty creative logic to pull in data from many cells across many differen 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.
One use case for this might be to format a range of cells using script, if certain conditions are met.
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
- Much more
Make sure to read the Microsoft MSDN page for the Range object to see all of 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.
And if you’re just getting started with VBA in Excel, don’t forget we’ve got a fantastic introductory guide to Excel VBA for you as well.
Read Full Article
No comments:
Post a Comment