Мир ПК - Excel - Visual basic excel cell

VBA Cells

Excel VBA Cells Function

Cells are one type of element in excel VBA. (Three elements of excel is Workbooks, Worksheets and Ranges/Cells)

Hierarchical between these elements or object will be:

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

Basic Structure For Referring a Cell

Excel VBA allows you to refer to cells in many different ways, cells refers to single cells only. (Note: it can’t refer to multiple cells like Range (“A2:E7”)

It is used for referencing a cell object e.g. it can be written as Cells (6, 5) for referring a cell “F5” where 6 is the column number & 5 is the row number Cells takes row & column number as an argument, cells are located within the range object With the help of cells in VBA, we can perform three important tasks, i.e. we can

Read content from a cell
Write a value to a cell
Change the format of a cell

Difference between range & cells in VBA is Cells usually refer to a single cell at a time, while Range references a group of cells. The cell is a property of range in excel sheet, which is a characteristic, where it is used to describe a range Cells only returns one cell which is used to represent cells within a range of the worksheet.

This cells property is used to specify a single cell or all cells on the worksheet, where it returns a single cell in the Cells collection.

Syntax of VBA Cells in Excel

The syntax for VBA cells function in excel is as follows:

Cells argument will consider two numeric arguments to represent row & column, where the first one is for representing row number & and the second or last one referring to as column number.

Row Index: Which is row number which we are referring to.

Column Index: Which is column number which we are referring to.

Note: Either numbers or letters can be used to specify the Column index in CELLS E.G. Cells (5, “E”)

Comma (,): Separator between them is the union operator, which is used to combine several range of cells.

Expression = Cells (4, 2) indicates it is “B4” Cell on an active worksheet in row 4 and col 2

Expression = Cells (4, “E”) indicates it is “B4” Cell on an active worksheet in row 4 and col 2

Above either of the code can be used to

Difference between Cell & Range syntax VBA code

To select cell B4 on the active worksheet, you can use either of the following examples:

How to Use VBA Cells in Excel?

We will learn how to use VBA cells function with a few examples in excel.

Example #1 – VBA Cells

Step 1: Select or click on Visual Basic in the Code group on the Developer tab or you can directly click on Alt + F11 shortcut key.

Step 2: To create a blank module, right-click on Microsoft excel objects, in that click on Insert and under the menu section select Module, where the blank module gets created.

Step 3: Double click on it, it is also called a code window, where you need to type Sub Cells_Example1() as the first message without any quotes around it. Now, you can observe, Excel automatically adds the line End Sub below the first message line when you press Enter.

Code:

Step 4: Now, all the codes which you enter must be between these two lines, Now you can start typing CELLS VBA code or syntax. Before typing code, enter CTRL + SPACE where VBA Intellisense Drop-down Menu appears, which helps you out in writing the code & you can autocomplete words in the VB editor.

The dropdown contains a list of all the members of the VB OBJECT MODEL active reference (i.e. includes objects, properties, variables, methods, and constants). This feature helps out in saving time & prevent misspell the words or typo-error.

Step 5: After typing cells, click on the tab key to select it.

Code:

Step 6: Once you leave a space and enter open bracket “(”, CELLS argument will appear where it considers two numeric arguments to represent row & column index, where the first one is for representing row number & and the second or last one referring to as column number.

Code:

Step 7: Now, I enter the row index number as “4” & column index as “2” and close the bracket. Full stop and enter one space by clicking on the spacebar, now the cells argument is ready. And I want value in it. So, I enter again enter CTRL + SPACE where VBA Intellisense Drop-down Menu appears, type “Val” which means value.

Code:

Step 8: Suppose you want “HI” to appear in that cell. For that, you need to type = “HI”. and click enter.

Code:

Step 9: Now, the code is ready, you can run the macro by clicking the Run Sub button (i.e. green “play” button) or by pressing F5. You can observe “Hi” appears in the cell “B4”

Things to Remember

Only one cell can be referred at a time with the help of Cells property.
Cell property is very significant & useful in a programming loop.
If you missed out or don’t specify a worksheet (by its name in VBA code), Excel considers or assumes the ACTIVE Sheet in a workbook.
If you missed out or don’t specify row and column index argument, then Excel will refer to all cells on the worksheet. E.g. Cells is the syntax for all cells on the Active Sheet.
You can combine cells with range together to define a starting & ending pint of a range with variables.

Visual basic excel cell

There is no Cell object nor is there a Cells collection.
Individual cells are treated as Range objects that refer to one cell.

Contents of a Cell

The easiest way to find what the contents of a cell are is to use the Visual Basic TypeName function

Text Property

You can assign a value to a cell using its Value property.
You can give a cell a number format by using its NumberFormat property.
The Text property of a cell returns the formatted appearance of the contents of a cell.

Range Object

The Range object can consist of individual cells or groups of cells.
Even an entire row or column is considered to be a range.
Although Excel can work with three dimensional formulas the Range object in VBA is limited to a range of cells on a single worksheet.
It is possible to edit a range either using a Range object directly (e.g. Range(«A1»).BackColor ) or by using the ActiveCell or Selection methods (e.g. ActiveCell.BackColor )

Cells Property

When the cells property is applied to a Range object the same object is returned.
It does have some uses though:

Range.Cells.Count — The total number of cells in the range.

Range.Cells( row, column ) — To refer to a specific cell within a range.

To loop through a range of cells

Cells automatically refer to the active worksheet.
If you want to access cells on another worksheet then the correct code is:

Range Property

When Range is not prefixed and used in a worksheet module, then it refers to that specific worksheet and not the active worksheet.

Selection Property

Selection will return a range of cells
Be aware that the Selection will not refer to a Range object if another type of object, such as a chart or shape is currently selected.
Using the Selection object performs an operation on the currently selected cells.
If a range of cells has not been selected prior to this command, then the active cell is used.

It is always worth checking what is currently selected before using the Selection property.

Relative References

It is important to remember that when a Cells property or a Range property is applied to a Range object, all the references are relative to the upper-left corner of that range.

ActiveCell returns a reference to the currently active cell
This will only ever return a single cell, even when a range of cells is selected.
The active cell will always be one of the corner cells of a range of cells. — will it what if you use the keyboard ??

Total number of populated cells

Window Object Only

This property applies only to a window object
This will enter the value 12 into the range that was selected before a non-range object was selected.

Window.RangeSelection property is read-only and returns a Range object that represents the selected cells on the worksheet in the active window.
If a graphic object is active or selected then this will returns the range of cells that was selected before the graphic object was selected.

Counting

Range of a Range

It is possible to treat a Range as if it was the top left cell in the worksheet.
This can be used to return a reference to the upper left cell of a Range object
The following line of code would select cell «C3».

Remember that cells are numbered starting from A1 and continuing right to the end of the row before moving to the start of the next row.

An alternative to this is to use the Offset which is more intuitive.

Range.Address

The Address method returns the address of a range in the form of a string.

Using the parameters allows you to control the transformation into a string (absolute vs relative).

RowAbsolute — True or False, default is True
ColumnAbsolute — True or False, default is True
ReferenceStyle — xlReferenceStyle.xlA1
External — True to return an external reference, default is false
RelativeTo — Range representing a relative to cell. Only relevant when ReferenceStyle = xlR1C1

Range.AddressLocal

This is similar to Address but it returns the address in the regional format of the language of the particular country.

Объекты Excel

Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.

Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.

В Excel VBA существует особый тип объектов – коллекция. Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.

Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.

В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).

Объект	Описание
Application	Приложение Excel.
Workbooks	Коллекция всех открытых в данный момент рабочих книг в текущем приложении Excel. Доступ к какой-то конкретной рабочей книге может быть осуществлён через объект Workbooks при помощи числового индекса рабочей книги или её имени, например, Workbooks(1) или Workbooks(“Книга1”).
Workbook	Объект Workbook – это рабочая книга. Доступ к ней может быть выполнен через коллекцию Workbooks при помощи числового индекса или имени рабочей книги (см. выше). Для доступа к активной в данный момент рабочей книге можно использовать ActiveWorkbook. Из объекта Workbook можно получить доступ к объекту Sheets, который является коллекцией всех листов рабочей книги (рабочие листы и диаграммы), а также к объекту Worksheets, который представляет из себя коллекцию всех рабочих листов книги Excel.
Sheets	Объект Sheets– это коллекция всех листов рабочей книги. Это могут быть как рабочие листы, так и диаграммы на отдельном листе. Доступ к отдельному листу из коллекции Sheets можно получить при помощи числового индекса листа или его имени, например, Sheets(1) или Sheets(“Лист1”).
Worksheets	Объект Worksheets – это коллекция всех рабочих листов в рабочей книге (то есть, все листы, кроме диаграмм на отдельном листе). Доступ к отдельному рабочему листу из коллекции Worksheets можно получить при помощи числового индекса рабочего листа или его имени, например, Worksheets(1) или Worksheets(“Лист1”).
Worksheet	Объект Worksheet – это отдельный рабочий лист книги Excel. Доступ к нему можно получить при помощи числового индекса рабочего листа или его имени (см. выше). Кроме этого Вы можете использовать ActiveSheet для доступа к активному в данный момент рабочему листу. Из объекта Worksheet можно получить доступ к объектам Rows и Columns, которые являются коллекцией объектов Range, ссылающихся на строки и столбцы рабочего листа. А также можно получить доступ к отдельной ячейке или к любому диапазону смежных ячеек на рабочем листе.
Rows	Объект Rows – это коллекция всех строк рабочего листа. Объект Range, состоящий из отдельной строки рабочего листа, может быть доступен по номеру этой строки, например, Rows(1).
Columns	Объект Columns – это коллекция всех столбцов рабочего листа. Объект Range, состоящий из отдельного столбца рабочего листа, может быть доступен по номеру этого столбца, например, Columns(1).
Range	Объект Range – это любое количество смежных ячеек на рабочем листе. Это может быть одна ячейка или все ячейки листа. Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1). По-другому ссылку на диапазон можно записать, указав адреса начальной и конечной ячеек. Их можно записать через двоеточие или через запятую. Например, Worksheet.Range(“A1:B10”) или Worksheet.Range(“A1”, “B10”) или Worksheet.Range(Cells(1,1), Cells(10,2)). Обратите внимание, если в адресе Range вторая ячейка не указана (например, Worksheet.Range(“A1”) или Worksheet.Range(Cells(1,1)), то будет выбран диапазон, состоящий из единственной ячейки.

Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:

Присваивание объекта переменной

В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:

Активный объект

В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.

Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.

Если в коде VBA записана ссылка на рабочий лист, без указания к какой именно рабочей книге он относится, то Excel по умолчанию обращается к активной рабочей книге. Точно так же, если сослаться на диапазон, не указывая определённую рабочую книгу или лист, то Excel по умолчанию обратится к активному рабочему листу в активной рабочей книге.

Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:

Смена активного объекта

Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:

Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.

Свойства объектов

Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name. Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:

Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:

Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.

Некоторые свойства объекта доступны только для чтения, то есть их значения пользователь изменять не может. В то же время существуют свойства, которым можно присваивать различные значения. Например, чтобы изменить название активного листа на “Мой рабочий лист“, достаточно присвоить это имя свойству Name активного листа, вот так:

Методы объектов

Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.

Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:

Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.

Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2, а аргументу FileFormat – значение xlCSV:

Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания “:=” и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:

В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.

Рассмотрим несколько примеров

Пример 1

Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.

Пример 2

В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.

В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.

Пример 3

Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.

Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.

CELL function

The CELL function returns information about the formatting, location, or contents of a cell. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can use the following formula:

This formula calculates A1*2 only if cell A1 contains a numeric value, and returns 0 if A1 contains text or is blank.

Syntax

The CELL function syntax has the following arguments:

A text value that specifies what type of cell information you want to return. The following list shows the possible values of the Info_type argument and the corresponding results.

The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

info_type values

The following list describes the text values that can be used for the info_type argument. These values must be entered in the CELL function with quotes (» «).

Reference of the first cell in reference, as text.

Column number of the cell in reference.

The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).