Understanding Excel Cells vs. Range Functions in VBA

Excel is highly effective. If you happen to use it quite a bit, you most likely already know plenty of tips utilizing formulation or auto-formatting, however making use of Ranges and Cells perform in VBA, you may take your Excel analytics to a complete new stage.

The issue with utilizing the Cells and Vary features in VBA macros is that on the superior ranges, most individuals have a tough time understanding how these features really work. Utilizing them can get very complicated. This is how one can make use of them in methods you most likely by no means imagined.

Visible Primary Cells Perform

The Cells and Vary features allow you to inform your VBA script precisely the place in your worksheet you need to acquire, or place information. The principle distinction between the 2 cells is what they reference.

The VBA cells perform normally references a single cell at a time, whereas Vary references a gaggle of cells without delay.

This is the format for the cells perform:

Cells(row, column)

This references each single cell in the complete sheet. That is one such instance the place the VBA macro cells perform does not reference a single cell:

Worksheets("Sheet1").Cells

Quite the opposite, this piece of code references the third cell from the left, in row primary, i.e., Cell C1:

Worksheets("Sheet1").Cells(3)

Let’s get a bit of adventurous, by referring cell D15 utilizing the VBA cells perform:

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

If you happen to wished to, you may additionally reference the cell D15 with the next cells perform VBA code:

Cells(15,"D")"

—you’re allowed to make use of the column letter.

There’s plenty of flexibility utilizing the VBA cell perform, as you may reference a cell utilizing a quantity for column and cell, particularly with scripts that may loop by many cells (and carry out calculations on them) in a short time.

The Vary Perform

In some ways, the Vary perform is way extra highly effective than utilizing Cells, as a result of it permits you to reference both a single cell or a selected vary of cells, unexpectedly. You are not going to need to loop by a Vary perform, as a result of the references for cells aren’t numbers (until you embed the VBA Excel cells perform inside it).

The format for this perform is as beneath:

Vary(Cell #1,Cell #2)

Every cell might be designated by a letter quantity. Let’s take a look at a number of examples.

The next code is a spread perform referencing cell A5:

Worksheets("Sheet1").Vary("A5")

Right here, the vary perform is referencing all cells between A1 by E20:

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

As talked about above, you do not have to make use of number-letter cell assignments. You possibly can really use two VBA cell features inside a Vary perform to establish a spread on the sheet, like this:

With Worksheets("Sheet1")
.Vary(.Cells(1, 1), _ 
.Cells(20, 5))
Finish With

This code references the identical vary as Vary(“A1:E20”) perform does. The worth in utilizing it’s that it will mean you can write code that dynamically works with ranges utilizing loops.

Now that you simply perceive easy methods to format the Cells and Vary features, let’s dive into how one can make artistic use of those features in your VBA code.

Processing Knowledge With Cells Perform

The Visible Primary cells perform is kind of helpful when you may have a posh system that you simply need to use throughout a number of ranges of cells. These ranges may exist throughout a number of sheets.

Let’s take a easy instance. For example you handle a gross sales workforce of 11 folks, and each month you need to have a look at their efficiency.

You may need Sheet1 that tracks their gross sales depend and their gross sales quantity.

On Sheet2, you may have information to trace their suggestions score for the final 30 days out of your firm’s shoppers.

If you wish to calculate the bonus on the primary sheet utilizing values from the 2 sheets, there are a number of methods to do that. You possibly can write a system within the first cell that performs the calculation utilizing information throughout the 2 sheets and drag it down. That’ll work.

A substitute for that is making a VBA script that runs everytime you open the sheet, otherwise you set off it by way of a command button on the sheet, to be able to management when it calculates. You may use a VBA script to tug in all of the gross sales information from an exterior file anyway.

So why not simply set off the calculations for the bonus column in the identical script at the moment?

Excel Macro Cells Perform in Motion

If you happen to’ve by no means written VBA code in Excel earlier than, you will must allow the Developer menu merchandise. To do that, go to File > Choices. Click on on Customise Ribbon. Lastly, select Developer from the left pane, Add it to the appropriate pane, and ensure the checkbox is chosen.

Now, while you click on OK and return to the principle sheet, you will see the Developer menu choice.

You need to use the Insert menu to insert a command button, or simply click on View Code to begin coding.

On this instance, you’ll arrange the script to run each time the workbook opens. To do that, simply click on View Code from the developer menu, and paste the next new perform into the code window.

Personal Sub Workbook_Open() 
Finish Sub

Your code window will look one thing like this.

Now you are prepared to put in writing the code to deal with the calculation. Utilizing a single loop, you may step by all 11 workers, and with the Excel VBA cells perform, pull within the three variables wanted for the calculation.

Bear in mind the worksheets.cells perform has row and column as parameters to establish every particular person cell. It’s worthwhile to substitute x with the row reference and use a quantity to request every column’s information. The variety of rows is the variety of workers, so this shall be from 1 to 11. The column identifier shall be 2 for Gross sales Depend, 3 for Gross sales Quantity, and a pair of from Sheet 2 for Suggestions Rating.

The ultimate calculation makes use of the next percentages so as to add as much as 100% of the entire bonus rating. It is primarily based on a great gross sales depend being 50, gross sales quantity of $50,000, and a suggestions rating of 10.

• (Gross sales Depend/50) x 0.4
• (Gross sales Quantity/50,000) x 0.5
• (Suggestions Rating/10) x 0.1

This easy method offers gross sales workers a weighted bonus. For a depend of fifty, a quantity of $50,000, and a rating of 10—they get the complete most bonus for the month. Nonetheless, something underneath excellent on any issue reduces the bonus. Something higher than supreme boosts the bonus.

Now let’s look how all of that logic might be pulled off in a quite simple, brief VBA script:

Personal Sub Workbook_Open()
For x = 2 To 12
Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Worth / 50) * 0.4 _
+ (Worksheets("Sheet1").Cells(x, 3).Worth / 50000) * 0.5 _
+ (Worksheets("Sheet2").Cells(x, 2).Worth / 10) * 0.1 _
Subsequent x
Finish Sub

That is what the output of this script will seem like.

If you happen to wished to have the Bonus column present the precise greenback bonus reasonably than the proportion, you may multiply it by the utmost bonus quantity. Higher but, place that quantity in a cell on one other sheet, and reference it in your code. This could make it simpler to alter the worth later with out having to edit your code.

The great thing about the cells’ technique in VBA is that you would be able to construct some fairly artistic logic to tug in information from many cells throughout many sheets and carry out some fairly advanced calculations with them.

You may carry out all kinds of actions on cells utilizing the Cells function—things like clearing the cells, altering font formatting, and rather more.

To discover all the things you are able to do additional, take a look at the Microsoft MSDN page for the Cells object.

Formatting Cells With Vary Perform

For looping by many cells one by one, the cells’ perform is ideal. However if you wish to apply one thing to a whole vary of cells unexpectedly, the Vary perform is way extra environment friendly. Within the ongoing comparability of VBA’s vary vs cells, there’s a lot to stay up for.

One use case for this is perhaps to format a spread of cells utilizing script if sure circumstances are met.

For instance, as an instance if the tally of all gross sales quantity throughout all gross sales workers surpasses $400,000 in whole, you need to spotlight all cells within the bonus column in inexperienced to suggest that the workforce has earned an additional workforce bonus.

Let’s check out how you are able to do that with an IF assertion.

Personal Sub Workbook_Open()
If Worksheets("Sheet1").Cells(13, 3).Worth > 400000 Then
ActiveSheet.Vary("D2:D12").Inside.ColorIndex = 4
Finish If
Finish Sub

When this runs, if the cell is over the workforce purpose, all cells within the vary shall be stuffed in inexperienced.

This is only one easy instance of the various actions you may carry out on teams of cells utilizing the Vary perform. Different issues you are able to do embrace:

• Apply an overview across the group
• Examine the spelling of textual content inside a spread of cells
• Clear, copy, or minimize cells
• Search by a spread with the Discover technique

Make certain to learn the Microsoft MSDN page for the Vary object to see all the chances.

Take Excel to the Subsequent Stage

Now that you simply perceive the variations between the Cells and the Vary features, it is time to take your VBA scripting to the following stage. Dann’s article on utilizing Counting and Including features in Excel will mean you can construct much more superior scripts that may accumulate values throughout your entire information units in a short time.

Learn Subsequent

About The Creator