Vba эксель - Учим Эксель

VBA Cells

Cells are actually cells of the worksheet and in VBA when we refer to cells as a range property we are actually referring to the exact cells, in other words, cell is used with range property and the method of using cells property is as follows Range(.Cells(1,1)) now cells (1,1) means the cell A1 the first argument is for the row and second is for the column reference.

VBA Cell References

You don’t need any special introduction about what is a VBA cell. In VBA concepts, cells are also the same, no different from normal excel cells. Follow this article to have more knowledge of the VBA cells concept.

What is VBA Range & VBA Cell?

I am sure this is the question running in your mind right now. In VBA, Range is an object, but Cell is a property in an excel sheet. In VBA, we have two ways of referencing a cell object one through Range, and another one is through Cells.

For example, if you want to reference cell C5, you can use two methods to refer to the cell C5.

Using Range Method: Range (“C5”)

Using Cells Method: Cells (5, 3)

Similarly, if you want to insert value “Hi” to C5 cell, then you can use the below code.

Using Range Method: Range (“C5”).Value = “Hi”

Using Cells Method: Cells (5, 3).Value = “Hi”

Now, if you want to select multiple cells, we can only select through the Range object. For example, if I want to select cells from A1 to A10, below is the code.

Code: Range (“A1: A10”).Select

Range (“A1: C10”).Cells(5,2) mean in the range A1 to C10 fifth row and second column i.e., B5 cell.

You are free to use this image on your website, templates etc, Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For eg:
Source: VBA Cells (wallstreetmojo.com)

Интересно почитать:  Как убрать разлиновку в excel

The Formula of CELLS Property in VBA

Take a look at the formula of CELLS property.

  • Row Index: This nothing but which row we are referencing.
  • Column Index: This nothing but which column we are referencing.
  • Cells (1, 1) means A1 cell, Cells (2, 1) means A2 cell, Cells (1, 2) means B1 cell.
  • Cells (2, 2) means B2 cell, Cells (10, 3) means C10 cell, Cells (15, 5) means E15 cell.

#1 – How to Use CELLS Property in VBA?

Now I will teach you how to use these CELLS property in VBA.

Assume you are working in the sheet name called Data 1, and you want to insert a value “Hello” to the cell A1.

VBA cells example

The Below code would do that for you.

Result:

Now I will go to the sheet name called Data 2 and will run the code. Even there, it will insert the word “Hello.”

Actually, we can combine the CELLS property with a particular sheet name as well. To refer a particular sheet, use the WORKSHEET object.

VBA cells example 1-2

This will insert the word “Hello” to the sheet “Data 1” irrespective of which sheet you are in.

#2 – How to Use CELLS Property with Range Object?

Actually, we can use CELLS property with a RANGE object. For example, look at the below code.

For better understanding, I have entered a few numbers in the excel sheet.

example 1-4

The above code Range(“C2:E8”).Cells(1, 1).Select says in the range C2 to E8 select the first cell. Run this code and see what happens.

It has selected the cell C2. But Cells (1, 1) means A1 cell, isn’t it?

The reason it has selected the cell C2 because using range object, we have insisted on the range as C2 to E8, so Cells property treats the range from C2 to E8, not from regular A1 cell. In this example, C2 is the first row and first column, so Cells (1, 1).select means C2 cell.

Интересно почитать:  Впр в excel как расшифровывается

Now I will change the code to Range(“C2: E8”).Cells(3, 2).Select and see what happens.

Run this code and check which cell actually it will select.

It has selected the cell D4 i.e., No 26. Cells (3,2) mean starting from C2 cell moved down by 3 rows and move 2 columns to the right i.e., D4 cell.

#3 – Cells Property with Loops

CELLS property with loops has a very good relationship in VBA. Let’s look at the example of inserting serial numbers from 1 to 10 using FOR LOOP. Copy and paste the below code to your module.

Here I have declared the variable I as an integer.

Then I have applied FOR LOOP with I = 1 to 10 i.e., and the loop needs to run 10 times.

This means that when the loop first runs, the value of “I” will be 1, so wherever the value of “I” is 1 i.e., Cell(1,1).value = 1

When the loop returns the value of “I” for the second time, it is 2, so wherever the value of “I” is, it is 2. i .e., Cell(2,1).value = 2

This loop will run for 10 times and insert I value from A1 to A10.

Things to Remember in VBA Cells

  • CELLS is property, but the RANGE is an Object. We can use property with objects but not object to the property.
  • When the range is supplied, cells will consider only that range, not the regular range.
  • Cells (1, 2) is B1 cell, similarly Cells (1, ”B”) is also B1 cell.

Recommended Articles

This has been a Guide to VBA Cells. Here we learn how to use VBA Cell Reference Property with Range Object along with practical examples and downloadable excel templates. Below you can find some useful excel VBA articles –

Интересно почитать:  Excel поиск по двум критериям

VBA — Excel Terms

In this chapter, you will acquaint yourself with the commonly used excel VBA terminologies. These terminologies will be used in further modules, hence understanding each one of these is important.

Modules

Modules is the area where the code is written. This is a new Workbook, hence there aren’t any Modules.

Module in VBScript

To insert a Module, navigate to Insert → Module. Once a module is inserted ‘module1’ is created.

Within the modules, we can write VBA code and the code is written within a Procedure. A Procedure/Sub Procedure is a series of VBA statements instructing what to do.

Module in VBScript

Procedure

Procedures are a group of statements executed as a whole, which instructs Excel how to perform a specific task. The task performed can be a very simple or a very complicated task. However, it is a good practice to break down complicated procedures into smaller ones.

The two main types of Procedures are Sub and Function.

Module in VBScript

Function

A function is a group of reusable code, which can be called anywhere in your program. This eliminates the need of writing the same code over and over again. This helps the programmers to divide a big program into a number of small and manageable functions.

Apart from inbuilt Functions, VBA allows to write user-defined functions as well and statements are written between Function and End Function.

Sub-Procedures

Sub-procedures work similar to functions. While sub procedures DO NOT Return a value, functions may or may not return a value. Sub procedures CAN be called without call keyword. Sub procedures are always enclosed within Sub and End Sub statements.

Ссылка на основную публикацию
Adblock
detector