Инструкция worksheets лист2 select используется для

Хитрости »

26 Июль 2015              80479 просмотров

Все начинающие изучать VBA сталкиваются с тем, что записанные через макрорекордер коды пестрят методами Select и Activate.
Если не знакомы с работой макрорекордера — Что такое макрос и где его искать?
Это значительно ухудшает читабельность кода и, как ни странно — быстродействие. Но есть недостатки и куда более критичные. Если код выполняется достаточно долго и он постоянно что-то выделяет — пользователь может заскучать и забыться и начнет тыкать мышкой по листам и ячейкам, выделяя не то, что выделил ранее код. Что повлечет ошибки логики. Т.е. код может и выполнится, но совершенно не так, как ожидалось. Поэтому избавляться от Select и Activate необходимо везде, где это возможно.

Для начала рассмотрим два кода, выполняющие одни те же действия — запись в ячейку А3 листа Лист2 слова «Привет». При этом сам код запускается с Лист1 и после выполнения код Лист1 должен остаться активным. Чтобы сделать эти действия вручную потребуется сначала перейти на Лист2, выделить ячейку А3, записать в неё слово «Привет» и вернуться на Лист1. Поэтому запись макрорекордером этих действий приведет к такому коду:

Sub Макрос1()
    Sheets("Лист2").Select            'выделяем Лист2
    Range("A3").Select                'выделяем ячейку А3
    ActiveCell.FormulaR1C1 = "Привет" 'записываем слово Привет
    Range("A4").Select                'после нажатия Enter автоматически выделяется ячейка А4
    Sheets("Лист1").Select            'возвращаемся на Лист1
End Sub

Нигде не говорится, что в большинстве случаев все эти Select и Activate в кодах не нужны. Однако вышеприведенный код можно значительно улучшить, если убрать все ненужные Select и Activate:

Sub Макрос1()
    Sheets("Лист2").Range("A3").FormulaR1C1 = "Привет"
End Sub

Как видно, вместо 5-ти строк кода получилась одна строка. Которая выполняет ту же задачу, что и код из 5-ти строк.
Прежде чем понять как правильно избавляться от лишнего давайте разберемся зачем же тогда VBA записывает эти Select и Activate? Как ни странно, но здесь все очень просто. VBA просто не знает, что Вы будете делать после того, как выделили Лист2. И когда Вы переходите на Лист2 — VBA записывает именно переход(его активацию, выделение). Когда выделяете ячейку — так же именно это действие записывает VBA. Захотите ли Вы затем выделить еще что-то, или закрасить ячейку, или записать в неё формулу/значение — VBA не знает. Поэтому в дальнейшем VBA работает именно с выделенным объектом Selection на активном листе.
Но при написании кода вручную или при правке записанного рекордером мы уже вольны в выборе и знаем, чего хотели добиться и какие действия нам точно не нужны.
Итак, чтобы записать в ячейку слово «Привет» рекордер предложит нам такой код:

Sub Макрос1()
    Range("A3").Select                'выделяем ячейку А3
    ActiveCell.FormulaR1C1 = "Привет" 'записываем слово Привет
    Range("A4").Select                'после нажатия Enter автоматически выделяется ячейка А4
End Sub

однако выделять ячейку(Range(«A3»).Select) совершенно необязательно. Значит один Select уже лишний. После этого идет обращение к активной ячейке — ActiveCell. .FormulaR1C1 = «Привет» означает запись значения «Привет» в эту ячейку.
Пусть не смущает FormulaR1C1 — VBA всегда так указывает запись и значения и формулы. Т.к. перед словом «Привет» нет знака равно — то это значение.
Т.к. ActiveCell является обращением к выделенной ячейке, а выделили мы до этого А3, значит их можно просто «сократить»:

Sub Макрос1()
    Range("A3").FormulaR1C1 = "Привет"
    Range("A4").Select                'после нажатия Enter автоматически выделяется ячейка А4
End Sub

Теперь у нас код получился короче и понятнее. Однако остался один Select: Range(«A4»).Select. Если нет необходимости выделять ячейку А4 после записи в А3 значения, то надо просто удалить эту строку и после выполнения кода активной будет та ячейка, которая была выделена до выполнения(т.е. выделенная ячейка просто не изменится). Таким образом мы с трех строк сократим код до 1-ой:

Sub Макрос1()
    Range("A3").FormulaR1C1 = "Привет"
End Sub

Теперь несложно догадаться, что с листами все в точности так же. Sheets(«Лист2»).Select — Select хоть и не нужен, но и ActiveSheet после него нет. Здесь необходимо знать некоторую иерархию в Excel. Сначала идет сам Excel — Application, потом книга — Workbook. В книгу входят рабочие листы(Worksheets), а уже в листах — ячейки и диапазоны — Range и Cells(Application ->Workbook ->Worksheet ->Range). Если перед Range или Cells не указывать явно лист: Range(«A3»).FormulaR1C1 = «Привет», то значение будет записано на активный лист. Подробнее можно прочесть в статье: Как обратиться к диапазону из VBA

Маленький нюанс: если сокращаем обращение к объектам, то Select-ов быть не должно вообще. Иначе есть шанс получить ошибку «Subscript out of range»:

буквально это означает, что указанный индекс вне досягаемости. А появляется эта ошибка потому, что нельзя выделить ячейку НЕактивного листа или лист НЕактивной книги. Легко эту ошибку получить например в таком коде:

Sub Макрос2()
    Windows("Книга3").Activate
    'здесь появится ошибка, т.к. пытаемся выделить лист в Книга2 
    'а на данный момент активной является Книга3
    Windows("Книга2").Sheets("Лист3").Select
End Sub

Ошибка обязательно появится, т.к. сначала мы активировали кодом книгу «Книга3», а потом пытаемся активировать лист НЕактивной на этот момент книги «Книга2». А это сделать невозможно без активации той книги, в которой активируемый лист. Т.е. активация должна происходить именно последовательно: Книга ->Лист ->Ячейка. И никак иначе, если мы хотим активировать именно конкретную ячейку конкретного листа в конкретной книге.
И пример с ячейками:

Sub Макрос2()
    Sheets("Лист3").Select
    'здесь появится ошибка, т.к. пытаемся выделить ячейку на листе "Лист1"
    'а на данный момент активным является Лист3
    Sheets("Лист1").Range("C7").Select
End Sub

Так же такая ошибка может появиться и по той простой причине, что указанного листа или книги нет(листа с указанным именем нет в данной книге или книга, к которой обращаемся просто закрыта).

Еще небольшой пример оптимизации:

Sub Макрос2()
    Windows("Книга3").Activate
    Sheets("Лист3").Select
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "Привет"
    Range("C7").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Этот код записывает в ячейку С7 Лист3 книги «Книга3» слово «Привет», потом делает жирным шрифт и назначает желтый цвет заливке. Убираем активацию книги, листа и ячейки, заменив их прямым обращением:

Workbooks("Книга3").Sheets("Лист3").Range("C7").FormulaR1C1 = "Привет"

далее делаем для ячейки жирный шрифт:

Workbooks("Книга3").Sheets("Лист3").Range("C7").Font.Bold = True

и цвет заливки:

With Workbooks("Книга3").Sheets("Лист3").Range("C7").Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

Тут есть нюанс. Windows необходимо всегда заменять на Workbooks — в кодах я сделал именно так. Если этого не сделать, то получите ошибку 438 — объект не поддерживает данное свойство или метод(object dos’t support this property or metod), т.к. коллекция Windows не содержит определения для Sheets.

Важный момент: лучше всегда указать имя книги вместе с расширением(.xlsx, xlsm, .xls и т.д.). Если в настройках ОС Windows(Панель управления —Параметры папок -вкладка Вид —Скрывать расширения для зарегистрированных типов файлов) указано скрывать расширения — то указывать расширение не обязательно — Workbooks(«Книга2»). Но и ошибки не будет, если его указать. Однако, если пункт «Скрывать расширения для зарегистрированных типов файлов» отключен, то указание Workbooks(«Книга2») обязательно приведет к ошибке.

Вместо Workbooks(«Книга3.xlsx») можно использовать обращение к активной книге или книге, в которой расположен код. Обращение к Лист3 активной книги, когда активен Лист2 или другой:

ActiveWorkbook.Sheets("Лист3").Range("A1").Value = "Привет"

Но бывают случаи, когда необходимо производить действия исключительно в той книге, в которой сам код. И не зависеть при этом от того, какая книга активна в данный момент и как она называется. Ведь в процессе книга может быть переименована. За это отвечает ThisWorkbook:

ThisWorkbook.Sheets("Лист3").Range("A1").Value = "Привет"

ActiveWorkbook — действия с активной на момент выполнения кода книгой
ThisWorkbook — действия с книгой, в которой записан код

Однако так же бывают случаи, когда необходимо обращаться к новой книге или листу, которые были созданы в процессе работы кода. В таком случае необходимо использовать переменные:

Sub NewBook()
    'объявляем переменную для дальнейшего обращения
    Dim wbNewBook As Workbook
    'создаем книгу
    Set wbNewBook = Workbooks.Add
    'теперь можно обращаться к wbNewBook как к любой другой книге
    'но уже не указывая её имя
    wbNewBook.Sheets(1).Range("A1").Value = "Привет"
    'Sheets(1) - обращение к листу по его порядковому номеру
    '(отсчет с начинается с 1 слева)
End Sub
Sub NewSheet()
    'объявляем переменную для дальнейшего обращения
    Dim wsNewSheet As Worksheet
    'добавляем новый лист в активную книгу
    Set wsNewSheet = ActiveWorkbook.Sheets.Add
    'теперь можно обращаться к wsNewSheet как к любому другому листу
    'но уже не указывая его имя или индекс
    wsNewSheet.Range("A1").Value = "Привет"
End Sub

Не везде Activate лишний
Но есть и такие свойства и методы, которые требуют обязательной активации книги/листа. Одним из таких свойств является свойство окна FreezePanes(Закрепление областей):

Sub Freeze_Panes()
    ThisWorkbook.Activate
    Sheets(2).Activate
    Range("B2").Select
    ActiveWindow.FreezePanes = True
End Sub

В этом коде нельзя убирать Select и Activate, т.к. свойство FreezePanes применяется исключительно к активному листу и активной ячейке, потому что является оно именно методом окна, а не листа или ячейки.
Так же сюда можно отнести свойства: Split, SplitColumn, SplitHorizontal и им подобные. Иными словами все свойства, которые работают исключительно с активным окном приложения, а не с объектами напрямую.

Так же см.:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?
Как обратиться к диапазону из VBA
Что такое переменная и как правильно её объявить?

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Типовые ситуации в работе с ячейками и диапазонами с примерами.

Содержание:

1.1. Работа со всеми ячейками на рабочем листе. How to: Refer to All the Cells on the Worksheet. 

1.2. Обращение к ячейкам и диапазонам с использованием нотации «А1». How to: Refer to Cells and Ranges by Using A1 Notation. 

1.3 Обращение к строкам и столбцам. How to: Refer to Rows and Columns. 

1.4 Обращение к ячейкам, используя индексный номер. How to: Refer to Cells by Using Index Numbers. 

1.5 Обращение к ячейкам с использование короткой нотации. How to: Refer to Cells by Using Shortcut Notation. 

1.6 Обращение к ячейкам расположенных относительно других ячеек. How to: Refer to Cells Relative to other Cells. 

1.7 Обращение к ячейкам с использованием объекта Range в виде переменной. How to: Refer to Cells By Using a Range Object. 

1.8 Обращение к именованным диапазонам. How to: Refer to Named Ranges. 

1.9 Обращение к нескольким диапазонам. How to: Refer to Multiple Ranges. 

2.0  Перебор ячеек в диапазоне. Looping Through a Range of Cells. 

2.1  Выбор и активация ячеек. Selecting and Activating Cells. 

2.2  Работа с 3-D диапазонами. Working with 3-D Ranges. 

2.3  Работа с активной ячейкой. Working with the Active Cell. 

2.4  Значения ошибок в ячейках. Cell Error Values. 

1.1. How to: Refer to All the Cells on the Worksheet. Работа со всеми ячейками на рабочем листе.

Когда вы применяете свойство Cells к рабочему листу без указания номера индекса (т.е. без указания адреса диапазона или ячейки), то этот метод возвращает объект Range, представляющий все ячейки этого листа.

Пример 1.1.1

Очистка данных во всех ячейках на листе активной рабочей книги (удаляются именно данные, т.е. цифры, буквы, символы, а не форматирование: цвет ячеек, ширина столбцов и т.д.)

Public Sub ClearSheet()

Worksheets(“Sheet1”).Cells.ClearContents

End Sub

Разбор кода:

Worksheets (“Sheet1”) — здесь мы вызываем коллекцию Worksheets (Рабочие листы), в которой в скобках и кавычках указываем название листа с которым хотим работать, т.е. “Sheet1” (можно прописать любое название листа из вашей книги, например Лист1 или Лист2).

Cells — это свойство, которое выделяет все ячейки на листе.

ClearContents — метод с помощью которого мы удаляем содержимое из всех ячеек.

Отметим, что после прописывания Cells. не появляется всплывающей подсказки свойств и методов. Свойство Cells здесь представляет собой объект Range, поэтому чтобы узнать, что можно писать после Cells. необходимо в справке посмотреть члены объекта Range (для этого нажмите F1, в поле поиска справки введите Range и нажмите поиск, далее выберите Range Object Members).

            Пример 1.1.2

Сделать для всех ячеек шрифт Arial с полужирным начертанием размером 12 зелёного цвета

Public Sub BoldFont()

With Worksheets(«Sheet1»).Cells.Font

    .Bold = True

    .Name = «Arial»

    .Size = 8

    .Color = RGB(0, 255, 0)

End With

End Sub

Разбор кода:

With Worksheets(«Sheet1″).Cells.Font – в этой строке, чтобы по много раз не писать Worksheets(«Sheet1″).Cells.Font мы написали слово With которое позволяет запомнить фразу после слова With и на следующих строках больше её не писать. После того как закончите работать с фразой, необходимо написать End With.

.Bold = True – выбираем свойство Bold (полужирный) и присваиваем параметр True, который символизирует логическое «да». А если выбрать False, то полужирный шрифт наоборот был бы отключён.

.Name = «Arial» – в данном свойстве указывается название шрифта, в данном случае “Arial”

.Size = 12 – в этом свойстве указываем высоту шрифта, например 12, можно выбрать от 1 до 409.

.Color = RGB(0, 255, 0) – здесь выбираем цвет с помощью формата RGB, каждое число должно быть в диапазоне от 0 до 255.

End With – этим выражением закрываем конструкцию With – это обязательное условие при использовании With.

1.2. How to: Refer to Cells and Ranges by Using A1 Notation. Обращение к ячейкам и диапазонам с использованием нотации «А1».

Вы можете обращаться к ячейке или диапазону ячеек с помощью ссылочного стиля «А1», используя свойство Range.

            Пример 1.2.1

Следующая процедура форматирует диапазон ячеек A1:C3 жирным выделением.

Public Sub RangeBold()

Workbooks(“Test.xlsm”).Sheets(“Sheet1”).Range(“A1:C3”).Font.Bold = True

End Sub

            Разбор кода:

Workbooks(“Test.xlsm”) – указываем необходимую книгу, в данном случае Test.xlsm

.Sheets(“Sheet1”) – далее указываем нужный лист, в данном случае Sheet1

.Range(“A1:C3”) – здесь указываем требуемый диапазон, в данному случае A1:C3

 .Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в диапазоне A1:C3

.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).

            Пример-таблица 1.2.2

            1.3 How to: Refer to Rows and Columns. Обращение к строкам и столбцам.

            Используйте свойство Rows или Columns для работы со строками или столбцами. Эти свойства возвращают объект Range, который представляет собой диапазон ячеек.

            Пример 1.3.1

            В этом примере ко всем ячейкам строки 1 будет применено жирное выделение.

Public Sub Row1Bold()

            Worksheets(“Sheet1”).Rows(1).Font.Bold = True

End Sub

            Разбор кода:

Worksheets(“Sheet1”) – указываем необходимый лист, например Sheet1.

.Rows(1) – указываем необходимую строку, например 1.

.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в строке 1.

.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).

            Пример 1.3.2

            Для работы с разными строками или колонками одновременно, т.е. для создания гибкого мультиобъекта используют метод Union, который позволяет соединять обращения к свойствам Rows или Columns.

            Пример 1.3.3

            Следующая процедура позволяет выделить текст жирным одновременно в строках 2, 4, 6.

Public Sub DifferentRows()

            Worksheets(“Sheet1”).Activate

            Dim myUnion As Range

            Set myUnion = Union(Rows(2), Rows(4), Rows(6))

            myUnion.Font.Bold = True

End Sub

            Разбор кода:

Worksheets(“Sheet1”).Activate – указываем необходимый лист, например Sheet1 и с помощью .Activate переходим на этот лист.

Dim myUnion As Range – создаём объект, где myUnion – это название, Range – это тип объекта (в данном случае диапазон).

Set myUnion = Union(Rows(2), Rows(4), Rows(6)) – слово Set присваивает myUnion значения после знака «=». После знака «=» мы видим метод Union, который позволяет объединить несколько строк в один объект (как бы в одну группу). В данном случае объединятся строки 2, 4 и 6 (Rows(2), Rows(4), Rows(6)) в один объект под названием myUnion.

myUnion.Font.Bold = True – теперь, имея объект myUnion с типом Range, можно применять свойства и параметры подходящие к типу Range. Здесь мы выбираем свойство .Font (Шрифт) и далее активируем параметр .Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).

            1.4 How to: Refer to Cells by Using Index Numbers. Обращение к ячейкам, используя индексный номер.

            Вы можете использовать свойство Cells для обращения к одной ячейке с помощью индексных номеров строки и столбца. Это свойство вернёт объект Range представленный одной ячейкой.

            Пример 1.4.1

            Следующая процедура присвоит ячейке B3, которая также имеет адрес R3C2, значение «5».

Public Sub Val5()

Worksheets(“Sheet1”).Cells(3,2).Value = 5

End Sub

            Разбор кода:

Worksheets(“Sheet1”) – указываем необходимый лист, например Sheet1.

.Cells(3,2) – обращаемся к ячейке B3(R3C2).

.Value – вызываем свойство Value и присваиваем ему с помощью знака «=» значение «5». То есть эта ячейка будет теперь содержать значение «5».

            Свойство Cells удобно использовать для перебора ячеек, т.к. можно подставить переменные вместо индексных номеров.

            Пример 1.4.2

            С помощью цикла For Next и индексных номеров запишем в ячейки C1(R1C3), C2(R2C3) и C3(R3C3) значения 1, 2 и 3 соответственно.

Public Sub Cycle1()

            Dim X As Integer

            For X = 1 To 3

            Worksheets(“Sheet1”).Cells(X,3).Value = X

            Next X

End Sub

            Разбор кода:

Dim X As Integer – объявляем переменную Х типа Integer.

For X = 1 To 3 – применяем цикл For Next. Для начала прописываем слово For и указываем переменную X с количеством повторов цикла (в данном случае от 1 до 3, т.е. 3 повтора).

Worksheets(“Sheet1”).Cells(X,3).Value = X – здесь указываем лист с которым хотим работать (например Worksheets(“Sheet1”)), далее ячейку, но вместо индексного номера строки вставим переменную Х (т.е. .Cells(X,3)). Свойство Value с помощью знака «=» позволит присвоить ячейке с адресом (X,3) значение переменной Х (от 1 до 3, смотря какой повтор цикла).

Next X – выражение (состоящие из слова Next и переменной X), запускающее цикл на повтор (будет 3 повтора, т.к. X равен от 1 до 3, т.е. максимум 3).

В итоге работа цикла начнётся с переменной равной 1. То есть для первого шага цикла в ячейку R1C3 будет записано значение 1. На втором шаге цикла в ячейке R2C3 будет записано значение 2. На последнем третьем шаге в ячейку R3C3 будет записано значение 3. Далее процедура будет завершена.

            1.5 How to: Refer to Cells by Using Shortcut Notation. Обращение к ячейкам с использование короткой нотации.

            Вы можете использовать стиль ссылок «А1» или именованный диапазон без скобок в виде короткого варианта свойства Range. Можно не писать слово Range и не использовать кавычки.

            Пример 1.5.1

            В этой процедуре диапазон ячеек А1:В2 будет очищен от содержимого (от текста).

Public Sub ClRange()

            Worksheets(“Sheet1”).[A1:B2].ClearContents

End Sub

            Разбор кода:

Worksheets(“Sheet1”) – указываем необходимый лист, например Sheet1.

.[A1:B2] – эта конструкция собственно и заменяет стандартную конструкцию .Range(“A1:B2”), т.е. здесь мы обращаемся к диапазону ячеек А1:B2.

.ClearContents — метод с помощью которого мы удаляем содержимое из диапазона ячеек А1:B2.

            Пример 1.5.2

            В следующей процедуре, ячейкам в заранее созданном диапазоне и получившем имя MyRan, присваивается значение “1”.

Public Sub MyRValue()

[MyRan].Value = 1

End Sub

            Разбор кода:

[MyRan] – конструкция в кратком варианте. Например, я заранее присвоил имя MyRan для диапазона ячеек A1:B2 на листе Sheet1. И в данном случае конструкция [MyRan] равноценна конструкции Worksheets(“Sheet1”).Range(“A1:B2”)

.Value = 1 – с помощью слова Value присваиваем всем ячейкам диапазона MyRan (то есть всем ячейкам в диапазоне A1:B2) значение «1».

            1.6 How to: Refer to Cells Relative to other Cells. Обращение к ячейкам расположенных относительно других ячеек.

            Для работы с ячейкой расположенной относительно другой ячейки используют свойство Offset (смещение).

            Пример 1.6.1

            В этом примере выделим ячейку “D1” и далее в ячейку расположенную на 2 строки ниже и на 3 столбца левее (это уже будет ячейка “A3”) внесем значение «1». И затем эту ячейку отформатируем двойным подчёркиванием.

Public Sub RelCell()

Worksheets(«Sheet1»).Range(«D1»).Select

ActiveCell.Offset(2, -3).Value = 1

ActiveCell.Offset(2, -3).Font.Underline = xlDouble

End Sub

            Разбор кода:

Worksheets(“Sheet1”) – указываем необходимый лист, например «Sheet1».

.Range(«D1″) – указываем необходимуя ячейку, например «D1».

.Select – это свойство выделяют ячейку «D1» (выделение вручную кнопкой мыши ячейки «D1» приведёт к такому же результату).

ActiveCell – данное свойство говорит о том, что хотим работать с заранее выделенной ячейкой (у нас выделена ячейка «D1», значит с ней и будем работать)

.Offset(2, -3) – данное свойство указывает на сколько строк и столбцов надо сместиться, первый параметр указывает на сколько строк будет смещаться, в данном случае на 2 строки вниз (а если была бы цифра «-2», то смещение было бы вверх на две строки). Второй параметр «-3» указывает, что надо сместиться на 3 столбца влево (а если бы была цифра «3», то смещение было бы вправо на три столбца).

.Value = 1 – с помощью свойства Value записываем в текущую ячейку цифру “1”.

ActiveCell.Offset(2, -3).Font.Underline = xlDouble – то же самое, что на предыдущем шаге, только с помощью свойства .Font указываем, что хотим работать со шрифтом ячейки. А свойство .Underline позволит применить к содержимому ячейки подчёркивание и с помощью = xlDouble указываем, что подчеркивание будет двойным (слово xlDouble).

            Замечание! Вы можете записывать макросы с использованием свойства Offset, т.е. будут указываться относительные ссылки, вместо абсолютных ссылок (т.е. ссылок А1, С2 и т.д. не будет). Для этого на вкладке «Разработчик», надо нажать на кнопку «Относительные ссылки» (можно навести указатель мыши на эту кнопку и прочитать всплывающую подсказку).

            Для перебора ячеек в диапазоне используют свойство Cells и конструкцию For Next.

            Пример 1.6.2

            В этом примере заполним первые пять ячеек во втором столбце (столбце В) значениями увеличивающимися на 5 в каждой следующей ячейке, начиная с первой (в ней будет значение 5).

Public Sub CycleCells()

            Dim X As Integer

            For X = 1 to 5

            Worksheets(“Sheet1”).Cells(X,2).Value = X * 5

            Next X

End Sub

            Разбор кода:

Dim X As Integer – объявляем переменную Х типа Integer.

For X = 1 To 5 – применяем цикл For Next. Для начала прописываем слово For и указываем переменную X с количеством повторов цикла (в данном случае от 1 до 5, т.е. 5 повторов).

Worksheets(“Sheet1”).Cells(X,2).Value = X * 5 – здесь указываем лист с которым хотим работать (например Worksheets(“Sheet1”)), далее ячейку, но вместо индексного номера строки вставим переменную Х (т.е. .Cells(X,2)). Свойство Value с помощью знака «=» позволит присвоить ячейке с адресом (X,2) значение переменной Х * 5 (значения будут от 5 до 25 (т.к. каждая цифра еще умножается на 5), смотря какой повтор цикла).

Next X – выражение (состоящие из слова Next и переменной X), запускающее цикл на повтор (будет 5 повторов, т.к. X равен от 1 до 5, т.е. максимум 5).

В итоге работа цикла начнётся с переменной равной 5 (т.е. 1 * 5 = 5). Получается для первого шага цикла в ячейку R1C2 будет записано значение 5 (т.к. 1*5=5), т.е. Worksheets(“Sheet1”).Cells(1,2).Value = 1 * 5. На втором шаге цикла в ячейке R2C2 будет записано значение 10 (т.к. 2*5=10), т.е. Worksheets(“Sheet1”).Cells(2,2).Value = 2 * 5. На третьем шаге R3C2 = 15 (т.к. 3*5=15). На четвёртом шаге R4C2 = 20 (т.к. 4*5=20). На пятом шаге R5C2 = 25 (т.к.5*5=25). Далее процедура завершится.

            1.7 How to: Refer to Cells By Using a Range Object. Обращение к ячейкам с использованием объекта Range в виде переменной.

            Если вы запишите в объектную переменную объект Range, то сможете манипулировать диапазоном через эту переменную.

            Пример 1.7.1

            Здесь создадим объектную переменную типа Range, назовём её MyRan и присвоим ей диапазон A1:A3. После этого мы сможем вместо указания диапазона указывать просто переменную и разумеется прописывать для этой переменной свойства и методы соответствующие объекту Range. Например, запишем в диапазон случайные числа и применим к шрифту форматирование курсивом.

Public Sub MyRanObj()

            Dim MyRan As Range

            Set MyRan = Worksheets(“Sheet1”).Range(“A1:A3”)

            MyRan.Formula = “=Rand()”

            MyRan.Font.Italic = True

End Sub

Разбор кода:

Dim MyRan As Range – с помощью слова Dim создаем объектную переменную (например MyRan) с типом Range.

Set MyRan = Worksheets(“Sheet1”).Range(“A1:A3”) – с помощью специального слова Set (которое необходимо применять при использовании переменных в виде объекта) присваиваем для MyRan диапазон «A1:A3», где Worksheets(“Sheet1”) – указываем необходимый лист, например «Sheet1», .Range(«A1:A3″) – указываем необходимую ячейку, например «A1:A3».

MyRan.Formula = “=Rand()” – работаем с переменной, как с объектом Range, например внесем во все ячейки диапазона формулу случайного числа “=Rand()”

MyRan.Font.Italic = True – для шрифта (Font) применим форматирование курсивом (Italic) c помощью знака «=» и слова True (а если написать False, то курсив наоборот был бы убран, при условии, что шрифт уже был с курсивом).

            1.8 How to: Refer to Named Ranges. Обращение к именованным диапазонам.

Диапазоны с именами удобнее, чем диапазоны в нотации «А1». Для присвоения имени необходимо выделить требуемый диапазон, затем слева от строки формул ввести имя диапазона вместо его адреса и нажать ENTER.

            Пример 1.8.1

В этом примере вызовем диапазон «MyRan» в книге “Test.xlsm” и отформатируем шрифт курсивом.

Public Sub MyRanItalic()

            Range(“Test.xlsm!MyRan”).Font.Italic = True

End Sub

            Разбор кода:

Range(“Test.xlsm!MyRan”) – т.к. имеем именованный диапазон, то после слова Range указываем имя открытой книги “Test” с указанием расширения “.xlsm”, далее ставим «!» и пишем имя диапазона “MyRan”.

.Font.Italic = True – для шрифта (Font) применим форматирование курсивом (Italic) c помощью знака «=» и слова True (а если написать False, то курсив наоборот был бы убран, при условии, что шрифт уже был с курсивом).

            Пример 1.8.2

            Здесь обращаемся к именованному диапазону “MyRan”, который был создан для конкретного листа “Sheet1” в книге “Text.xlsm”. И выделяем этот диапазон тонкой границей.

Public Sub MyRanSheet()

Range(“[Test.xlsm]Sheet1!MyRan”).BorderAround Weight:=xlThin

End Sub

            Разбор кода:

Range(“[Test.xlsm]Sheet1!MyRan”) – т.к. в этом примере именованный диапазон создан только для листа “Sheet1”, то после слова Range сначала в квадратных скобках указываем имя открытой книги (например, [Test.xlsm]), затем пишем название книги (например, «Sheet1»), потом ставим восклицательный знак “!” и пишем имя диапазона (например, MyRan).

.BorderAround Weight:=xlThin – т.к. мы имеем объект типа Range, то с помощью метода .BorderAround (границы) сможем выделить диапазон границей, присвоив параметру Weight (толщина) значение xlThin (тонкая линия).

            Пример 1.8.3

            Для выделения именованного диапазона, используйте метод GoTo, который активирует книгу, затем лист и потом выделяет диапазон. Далее мы очистим диапазон от содержимого. Отметим, что именованный диапазон создан с доступом со всех листов (а не для конкретного листа).

Public Sub MyRanSelect()

Application.GoTo Reference:=“Test.xlsm!MyRan”

Selection.ClearContents

End Sub

            Разбор кода:

Application.GoTo Reference:=“Test.xlsm!MyRan” – метод GoTo («перейти к») принадлежит объекту Application, далее указываем параметр Reference («ссылка»), для которого прописываем значение Test.xlsm!MyRan, т.е. имя книги и имя диапазона.

.Selection – метод («выделение»), указывающий о том, что будем работать с текущим выделением.

.ClearContents – метод («очистить содержимое») с помощью которого мы удаляем содержимое из ячеек диапазона MyRan.

            Пример 1.8.4

            Этот пример аналогичен примеру 1.8.3, но здесь книга уже должна быть активна.

Public Sub MyRanSelect2()

            Application.GoTo Reference:=”MyRan”

            Selection.ClearContents

End Sub

            Разбор кода:

Application.GoTo Reference:=“MyRan” – метод GoTo («перейти к») принадлежит объекту Application («приложение»), далее указываем параметр Reference («ссылка»), для которого прописываем значение MyRan, т.е. имя диапазона.

.Selection – метод («выделение»), указывающий о том, что будем работать с текущим выделением.

.ClearContents – метод («очистить содержимое») с помощью которого мы удаляем содержимое из ячеек диапазона MyRan.

            Looping through cells in a named range. Перебор ячеек в именованном диапазоне.

         Пример 1.8.5

В этом примере, в заранее созданном именованном диапазоне MyRan (диапазон «A1:A3») введем случайные числа от 0 до 100. Далее переберём каждую ячейку диапазона, используя цикл For Each… Next. И если значение ячейки будет превышать значение X, то сменим цвет заливки ячейки на желтый.

Public Sub Example185()

[MyRan].Value = “=Rand() * 100”

Const X As Integer = 50

Dim Y As Range

                        For Each Y In Range(“MyRan”)

                                   If Y.Value > X Then

                                   Y.Interior.ColorIndex = 27

                                   End If

                        Next Y

End Sub

            Разбор кода:

[MyRan] – конструкция в кратком варианте. Например, я заранее присвоил имя MyRan для диапазона ячеек A1:A3 на листе Sheet1. И в данном случае конструкция [MyRan] равноценна конструкции Worksheets(“Sheet1”).Range(“A1:A3”)

.Value = “=Rand() * 100” – с помощью свойства Value присваиваем всем ячейкам диапазона MyRan формулу случайного числа (Rand()) от 0 до 1 и умножаем на 100 (* 100), чтобы получить случайные числа от 0 до 100. Таким образом ячейки А1, А2, А3 будут заполнены случайными числами от 0 до 100.

Const X As Integer = 50 – здесь объявляем константу с именем X типа Integer, которая будет иметь неизменяющееся значение 50. Константы служат для объявления какого-нибудь постоянного значения, которое не должно меняться.

Dim Y As Range – объявляем переменную Y объектного типа Range, к которой сможем применять методы и свойства соответствующие объекту Range.

For Each Y In Range(“MyRan”) – объявляем цикл For Each Next, где на каждом повторе цикла каждое значение Y будет приравниваться соответствующему значению ячейки в диапазоне MyRan и дальше будет проверятся на определенное условие. То есть на первом повторе значение Y будет равно числу из ячейки A1, на втором — А2, на третьем — А3. Количество повторов равно количеству ячеек в диапазоне.

If Y.Value > X Then Y.Interior.ColorIndex = 27 End If – вот собственно и условие, которое представлено конструкцией If Then. В нём говорится, что на каждом повторе цикла: если (If) значение ячейки (Y.Value) больше значения X, то (Then, после этого слова лучше писать код с новой строки) для этой ячейки (Y) применить заливку (.Interior) желтого цвета (.ColorIndex = 27). И потом закрываем эту конструкцию словами End If.

Next Y – выражение (состоящее из слова Next и переменной Y), запускающее цикл на повтор (будет 3 повтора, т.к. Y была связана с диапазоном MyRan в качестве значения каждой ячейки, а ячеек в диапазоне три).

            1.9 How to: Refer to Multiple Ranges. Обращение к нескольким диапазонам.

Используя подходящий метод, можно обращаться одновременно к нескольким диапазонам. Используйте методы Range (“диапазон”) и Union (“объединение”) для работы с группой диапазонов. Также используйте свойство Areas (“области”) для работы с группой выделений на рабочем листе.

            Using the Range Property. Использование свойства Range.

Вы можете обращаться к нескольким диапазонам одновременно с помощью свойства Range путём вставки запятых между двумя и более ссылками (адресами) диапазонов.

            Пример 1.9.1

Очистим на листе “Sheet1” диапазоны A1:A3, B1:B3, C1:C3 от содержимого.

Public Sub Example191()

            Worksheets(“Sheet1”).Range(“A1:A3, B1:B3, C1:C3”).ClearContents

End Sub

            Разбор кода:

Worksheets(“Sheet1”) – указываем необходимый лист, например Sheet1.

.Range(“A1:A3, B1:B3, C1:C3”) – соединяем диапазоны A1:A3, B1:B3, C1:C3.

.ClearContents – очищаем все три диапазона от содержимого.

            Именованные диапазоны еще больше облегчают использование свойства Range для работы с группой диапазонов

            Пример 1.9.2

            В этом примере одновременно очистим заранее созданные (для всех листов книги) именованные диапазоны RanA, RanB, RanC.

Public Sub Example192()

            Range(“RanA, RanB, RanC”).ClearContents

End Sub

            Разбор кода:

Range(“RanA, RanB, RanC”) – соединяем диапазоны RanA, RanB, RanC.

.ClearContents – очищаем все три диапазона от содержимого.

            Using the Union Method. Использование метода Union (“объединение”).

            Вы можете объединить несколько диапазонов в один объект Range, используя метод Union.

            Пример 1.9.3

            В этом примере создадим объект Range с названием RanX, который будет состоять из диапазонов A1:A3 и B1:B3. Далее применим к шрифту объекта RanX жирное выделение. То есть все значения в ячейках диапазонов A1:A3 и B1:B3 будут иметь жирный шрифт.

Public Sub Example193()

            Dim r1, r2, RanX As Range

            Set r1 = Sheets(“Sheet1”).Range(“A1:A3”)

            Set r2 = Sheets(“Sheet1”).Range(“B1:B3”)

            Set RanX = Union(r1, r2)

            RanX.Font.Bold = True

End Sub

Разбор кода:

Dim r1, r2, RanX As Range – создаём объектные переменные типа Range.

Set r1 = Sheets(“Sheet1”).Range(“A1:A3”) – с помощью слова Set (применяется к объектным переменным) привязываем к переменной r1 диапазон “A1:A3” расположенный на листе “Sheet1”.

Set r2 = Sheets(“Sheet1”).Range(“B1:B3”) — с помощью слова Set (применяется к объектным переменным) привязываем к переменной r2 диапазон “B1:B3” расположенный на листе “Sheet1”.

Set RanX = Union(r1, r2) — слово Set присваивает RanX после знака «=» диапазоны. После знака «=» мы видим метод Union, который позволяет объединить несколько диапазонов (в нашем случае r1 и r2) в  один объект (как бы в одну группу под названием RanX).

RanX.Font.Bold = True – теперь, имея объект RanX с типом Range, можно применять свойства и параметры подходящие к типу Range. Здесь мы выбираем свойство .Font (Шрифт) и далее активируем параметр .Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный). В итоге в ячейках в диапазонах A1:A3 и B1:B3 будет жирный шрифт.

            Using the Areas Property. Использование свойства Areas (“области”).

         Вы можете использовать свойство Areas для обращения к выделенному диапазону (области) или к группе выделенных диапазонов (областей).

            Пример 1.9.4

            В этом примере подсчитывается количество выделенных областей (например, с зажатой клавишей Ctrl можно мышью выделить диапазоны A1:A3, B5:B7, C1:C3, т.е. будет выделено три области (диапазона)) и если всё выделенное вами содержит в себе больше выделенных областей, чем 1, то выведется сообщение.

Public Sub Example194()

If Selection.Areas.Count > 1 Then

MsgBox “Warning! Multiple Selection. Some operations can`t work with multiple selection.”

            End If

End Sub

Разбор кода:

If Selection.Areas.Count > 1 Then – здесь применяется конструкция If Then, т.е если (If) наше выделение (Selection) содержит в себе областей (.Areas) в количестве (.Count) более 1 (>1), тогда (Then) выполним следующий шаг.

MsgBox “Warning! Multiple Selection. Some operations can’t work with multiple selection.” – вот собственно и сам шаг, который состоит из функции MsgBox, которая выводит окно с сообщением, текст сообщения необходимо написать в кавычках после MsgBox, например в этом сообщении написано «Внимание! Множественное выделение. Некоторые операции нельзя применить к множественному выделению».

End If – данными словами закрываем конструкцию If Then.

            2.0  Looping Through a Range of Cells. Перебор ячеек в диапазоне.

            При использовании Visual Basic часто приходится применять одинаковые действия к каждой ячейке в диапазоне ячеек. Для выполнения таких действий, можно объединять операторы перебора и один или несколько методов для каждой ячейки по очереди.

            Например, можно перебирать ячейки с помощью цикла For… Next и свойства Cells (“ячейки”).

            Используя свойство Cells, вы можете заменить счётчик цикла (или переменную, или выражение) на индексный номер ячейки.

            Пример 2.0.1

            В этом примере переменную X вставим вместо номера строки. Процедура переберёт ячейки диапазона A1:A3 и если абсолютное значение ячейки меньше 0,01, то значение этой ячейки заменится на 0.

Public Sub Example201()

Dim yCell As Range

            Dim X As Integer

            For X = 1 To 3

            Set yCell = Worksheets(«Sheet1»).Cells(X, 1)

                        If Abs(yCell) < 0.01 Then

                        yCell.Value = 0

                        End If

            Next X

End Sub

            Разбор кода:

Dim yCell As Range – объявляем объектную переменную yCell типа Range, которая будет принимать значения ячейки диапазона и к которой можно будет применить свойства характерные для объекта Range.

Dim X As Integer – переменная Х типа Integer, которая будет служить счетчиком повторов цикла

For X = 1 To 3 – объявляем цикл For… Next и указываем, что количество повторов цикла будет 3 (X = 1 To 3).

Set yCell = Worksheets(«Sheet1″).Cells(X, 1) – с помощью специального слова Set (которое необходимо применять при использовании переменных в виде объекта) присваиваем для yCell значение ячейки Cells(X, 1), где Worksheets(“Sheet1”) – указываем необходимый лист, например «Sheet1», .Cells(X, 1) – указываем необходимую ячейку, но номер строки заменим на X. И например, на первом повторе цикла X = 1, тогда адрес ячейки будет (1,1), для второго повтора X = 2, тогда адрес ячейки будет (2,1) и на третьем повторе цикла адрес ячейки будет (3,1).

If Abs(yCell) < 0.01 Then – вставляем внутрь цикла конструкцию If Then, которая говорит, что если (If) абсолютное значение (Abs) ячейки (yCell) меньше (<) 0.01, тогда (Then) выполнить следующее действие. Например, на первом повторе будет проверяться значение ячейки с адресом (1,1).

yCell.Value = 0 – вот само действие, которое заменит значение (.Value) ячейки (записанной в yCell) на 0.

End If – данными словами закрываем конструкцию If Then.

Next X — выражение (состоящее из слова Next и переменной X), запускающее цикл на повтор (будет 3 повтора, т.к. количество повторов для X указано от 1 до 3 включительно (For X = 1 To 3)).

Еще проще перебрать диапазон с помощью цикла For Each… Next и свойства Range. Visual Basic автоматически присвоит объектную переменную каждой ячейке при выполнении цикла.

            Пример 2.0.2

            Пример аналогичен 2.0.1 только применим цикл For Each… Next.

Public Sub Example202()

            Dim X As Range

            For Each X In Worksheets(“Sheet1”).Range(“A1:A3”).Cells

                        If Abs(X.value) < 0.01 Then

                        X.Value = 0

                        End if

            Next

End Sub

            Разбор кода:

Dim X As Range – объявляем объектную переменную X типа Range, которая будет принимать значения ячейки диапазона и к которой можно будет применить свойства характерные для объекта Range.

For Each X In Worksheets(“Sheet1”).Range(“A1:A3”).Cells – запускаем цикл For Each… Next, который как бы говорит, что для каждого X равное на листе (Worksheets(“Sheet1”)) в диапазоне .Range(“A1:A3”) в соответствующей ячейке (.Cells) применить действия в строках кода ниже. Отметим, что данным цикл удобнее тем, что он автоматически повторится столько раз, сколько ячеек в диапазоне, в нашем случае три (A1, A2, A3).

If Abs(X.value) < 0.01 Then — вставляем внутрь цикла конструкцию If Then, которая говорит, что если (If) абсолютное значение (Abs) ячейки (X) меньше (<) 0.01, тогда (Then) выполнить следующее действие. Например, на первом повторе будет проверяться значение ячейки с адресом A1, на втором повторе проверим ячейку A2, на третьем повторе – ячейку A3.

X.Value = 0 — вот само действие, которое изменит значение (.Value) ячейки (записанное в X) на 0.

End If – данными словами закрываем конструкцию If Then.

Next – закрываем цикл и запускаем на повторение (будет три повтора).

            Если вы не знаете границ диапазона, который хотите перебрать, вы можете использовать свойство CurrentRegion (“текущий регион”), который позволит работать с диапазоном ячеек вокруг активной ячейки. Обрабатываться будут все непустые ячейки вокруг активной ячейки, а также ячейки, примыкающие к этим непустым ячейкам. Обработка пустых ячеек показана после примера в виде рисунков.

            Пример 2.0.3

            В этом примере на заранее активированном листе будет перебран диапазон ячеек и для ячеек, абсолютное значение которых будет меньше 0,01, изменится значение на 0.

Public Sub Example203()

            Dim X As Range

                        For Each X In ActiveCell.CurrentRegion.Cells

                        If Abs(X.Value) < 0.01 Then

                        X.Value = 0

                        End If

            Next

End Sub

            Разбор кода:

Dim X As Range – объявляем объектную переменную X типа Range, которая будет принимать значения ячейки диапазона и к которой можно будет применить свойства характерные для объекта Range.

For Each X In ActiveCell.CurrentRegion.Cells – запускаем цикл For Each… Next, здесь говорится, что для каждого (For Each) X (количество повторов цикла будет равно количеству ячеек в диапазоне) в (In) диапазоне ячеек вокруг (.CurrentRegion.Cells) текущей ячейки (ActiveCell) необходимо выполнить действия в следующих строках.

If Abs(X.value) < 0.01 Then — вставляем внутрь цикла конструкцию If Then, которая говорит, что если (If) абсолютное значение (Abs) ячейки (X) меньше (<) 0.01, тогда (Then) выполнить следующее действие.

X.Value = 0 — вот само действие, которое изменит значение (.Value) ячейки (записанное в X) на 0.

End If – данными словами закрываем конструкцию If Then.

Next – закрываем цикл и запускаем на повторение (повторов будет столько, сколько ячеек в диапазоне).

Результаты данного примера для различных диапазонов:

1)

2)

3) Здесь полностью отделим диапазон активной ячейки пустыми ячейками и тогда другие ячейки листа не изменятся.

4)

            2.1  Selecting and Activating Cells. Выбор и активация ячеек.

При работе в Excel обычно выделяют ячейку или ячейки, а затем выполняют действия над ними такие, как форматирование ячеек или внесение данных в них. В VBA обычно нет необходимости выделять ячейки перед их изменением.

            Пример 2.1.1

Если вы хотите ввести формулу в ячейку “A2”, используя VBA, то можете не выделять её. Просто используйте объект Range для этой ячейке и с помощью свойства Formula введите необходимую формулу (например формулу суммирования значений в диапазоне (C1:C3).

Public Sub Example211()

Worksheets(“Sheet1”).Range(“A2”).Formula = “=SUM(C1:C3)”

End Sub

            Разбор кода:

Worksheets (“Sheet1”) — здесь мы вызываем коллекцию Worksheets (Рабочие листы), в которой в скобках и кавычках указываем название листа с которым хотим работать, т.е. “Sheet1” (можно прописать любое название листа из вашей книги, например Лист1 или Лист2).

.Range(“A2”) – здесь указываем требуемый диапазон, в данном случае диапазон из одной ячейки «A2».

.Formula – с помощью данного свойства и знака «=» записываем в «A2» формулу в том виде, в каком обычно вписываем формулы в ячейки (например, =SUM(C1:C3)).

            Другие примеры обработки ячеек без их выделения смотрите в теме How to: Reference Cells and Ranges. Обращение к ячейкам и диапазонам.

            Using the Select Method and the Selection Property. Использование метода Select и свойства Selection.

         Метод Select («выбрать») активирует листы и объекты на листах. Свойство Selection («выбранное») возвращает объект, представленный текущим выделением на активном листе в активной книге.

            Прежде чем использовать свойство Selection, необходимо активировать книгу, потом активировать или выбрать лист, а затем выделить диапазон (или другой объект) с помощью метода Select.

            Макрорекордер часто создаёт макрос, использующий метод Select и свойство Selection.

            Пример 2.1.2

            Эта процедура, созданная макрорекордером, иллюстрирует, как Select и Selection работают вместе. Внесём в ячейку А1 текст «Name», а в ячейку B1 текст «Address». Затем для диапазона A1:B1 отформатируем шрифт жирным.

Public Sub Example212()

Sheets(“Sheet1”).Select

Range(“A1”).Select

ActiveCell.FormulaR1C1 = “Name”

Range(“B1”).Select

ActiveCell.FormulaR1C1 = “Address”

Range(“A1:B1”).Select

Selection.Font.Bold = True

End Sub

            Разбор кода:

Sheets(“Sheet1”).Select – для начала с помощью .Select выбираем лист Sheet1 из коллекции Sheets.

Range(“A1”).Select – выбираем (.Select) ячейку A1 (Range(“A1”)).

ActiveCell.FormulaR1C1 = “Name” – и теперь для активной ячейки (ActiveCell) выберем свойство .FormulaR1C1 чтобы внести в эту ячейку текст «Name».

Range(“B1”).Select – выбираем (.Select) ячейку B1 (Range(“B1”)).

ActiveCell.FormulaR1C1 = “Address” – и теперь для активной ячейки (ActiveCell) выберем свойство .FormulaR1C1 чтобы внести в эту ячейку текст « Address».

Range(“A1:B1”).Select – выделяем (.Select) диапазон (Range) “A1:B1”.

Selection – обращаемся к диапазону A1:B1, который мы выделили.

.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в диапазоне A1:B1.

.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).

            Пример 2.1.3

            В этом примере выполняется та же задача, что и в примере 2.1.2, но без активации или выбора рабочего листа или ячеек.

Public Sub Example213()

            With Worksheets(“Sheet1”)

                        .Range(“A1”) = “Name”

                        .Range(“B1”) = “Address”

                        .Range(“A1:B1”).Font.Bold = True

            End With

End Sub

            Разбор кода:

With Worksheets(“Sheet1”) – в этой строке, чтобы далее по много раз не писать Worksheets(«Sheet1″) мы написали слово With которое позволяет запомнить фразу после слова With и на следующих строках больше её не писать. После того как закончите работать с фразой, необходимо написать End With.

.Range(“A1”) = “Name” – присваиваем ячейке A1 текст “Name”.

.Range(“B1”) = “Address” — присваиваем ячейке A1 текст “Address”.       

.Range(“A1:B1”) – указываем необходимый диапазон, в нашем случае A1:B1.

.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в диапазоне A1:B1.

.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).

            Selecting Cells on the Active Worksheet. Выделение ячеек на активном листе.

         Если вы используете метод Select для выбора ячеек, то помните, что этот метод работает только на активном листе. Если вы запускаете процедуру из модуля и предварительно не активировали лист, то при использовании метода Select появится ошибка.

            Пример 2.1.4

            Эта процедура копирует строку 1 с листа “Sheet1” на лист “Sheet2”.

Public Sub Example214()

            Worksheets(“Sheet1”).Rows(1).Copy

            Worksheets(“Sheet2”).Select

            Worksheets(“Sheet2”).Rows(1).Select

            Worksheets(“Sheet2”).Paste

End Sub

            Разбор кода:

Worksheets(“Sheet1”).Rows(1).Copy – указываем лист (Worksheets(“Sheet1”)), затем обращаемся к первой строке (.Rows(1)). Далее копируем (.Copy) строку.

Worksheets(“Sheet2”).Select – выбираем (.Select) другой лист (Worksheets(“Sheet2”)).

Worksheets(“Sheet2”).Rows(1).Select — указываем лист (Worksheets(“Sheet2”)), затем обращаемся к первой строке (.Rows(1)) и выделяем её (.Select).

Worksheets(“Sheet2”).Paste – указываем лист (Worksheets(“Sheet2”)) и вставляем (.Paste) в заранее выделенную строку первую строку из листа Sheet1.

            Activating a Cell Within a Selection. Активация ячейки внутри выделения.

            Вы можете использовать метод Activate для активации ячейки внутри выделения. Причём можно активировать только одну ячейку, даже когда выделенный диапазон содержит несколько ячеек.

            Пример 2.1.5

            Эта процедура выделяет диапазон “A1:C3” и затем активирует ячейку “B1” внутри выделенного, не изменяя выделение.

Public Sub Example215()

            Worksheets(“Sheet1”).Activate

            Range(“A1:C3”).Select

            Range(“B1”).Activate

End Sub

            Разбор кода:

Worksheets(“Sheet1”).Activate – обращаемся к листу (Worksheets(“Sheet1”)) и активируем его (.Activate).

Range(“A1:C3”).Select – указываем диапазон (Range(“A1:C3”)) и выделяем его (.Select).

Range(“B1”).Activate – указываем ячейку (Range(“B1”)) и активируем её (.Activate).

            2.2  Working with 3-D Ranges. Работа с 3-D диапазонами.

Если вы работаете с одним и тем же диапазоном на нескольких листах, то используйте функцию Array для выбора этих листов.

            Пример 2.2.1

В этом примере для 3-D диапазона «A1:B2» на листах Sheet1, Sheet2 и Sheet3 нарисуем границы.

Public Sub Example221()

            Sheets(Array(“Sheet1”, “Sheet2”, “Sheet3”)).Select

            Range(“A1:B2”).Select

            Selection.Borders(xlBottom).LineStyle = xlDouble

End Sub

            Разбор кода:

Sheets(Array(“Sheet1”, “Sheet2”, “Sheet3”)).Select – для объекта (Sheets) сначала указываем массив (Array) листов (“Sheet1”, “Sheet2”, “Sheet3”) и далее выделяем их (.Select).

Range(“A1:B2”).Select – указываем необходимый диапазон (Range(“A1:B2”)) и выделяем его (.Select).

Selection – обращаемся к выделенному, т.е. нашему диапазону.

.Borders(xlBottom) – для свойства .Borders («границы») указываем параметр xlBottom («нижняя граница»).

.LineStyle = xlDouble – и теперь с помощью свойства .LineStyle («стиль линии») и знака «=» указываем, что линия должна быть двойная (xlDouble).

            Пример 2.2.2

            В этом примере применяется метод FillAcrossSheets («заполнить листы») для переноса форматирования и других данных из диапазона «A1:B2» на листе «Sheet1» в диапазоны всех остальных листов активной книги. Сначала нарисуем границы для диапазона «A1:B2» на листе “Sheet1”, а затем сделаем эту операцию для таких же диапазонов на всех листах книги.

Public Sub Example222()

Worksheets(“Sheet1”).Range(“A1:B2”).Borders(xlBottom).LineStyle = xlDouble

Worksheets.FillAcrossSheets (Worksheets(“Sheet1”).Range(“A1:B2”))

End Sub

            Разбор кода:

Worksheets(“Sheet1”).Range(“A1:B2”) – обращаемся к листу (Worksheets(“Sheet1”)), затем указываем необходимый диапазон (.Range(“A1:B2”)).

.Borders(xlBottom) – для свойства .Borders («границы») указываем параметр xlBottom («нижняя граница»).

.LineStyle = xlDouble – и теперь с помощью свойства .LineStyle («стиль линии») и знака «=» указываем, что линия должна быть двойная (xlDouble).

Worksheets.FillAcrossSheets – для объекта Worksheets выбираем метод .FillAcrossSheets

(Worksheets(“Sheet1”).Range(“A1:B2”)) – после метода .FillAcrossSheets ставим пробел и указываем месторасположение диапазона (Worksheets(“Sheet1”).Range(“A1:B2”)), который хотим перенести на все листы.

            2.3  Working with the Active Cell. Работа с активной ячейкой.

            Свойство ActiveCell возвращает объект Range, представленный этой активной ячейкой. Вы можете применять свойства и методы, характерные для объекта Range, к активной ячейке.

            Пример 2.3.1

            В этом примере для активной ячейки внесём значение «10».

Public Sub Example231()

            Worksheets(“Sheet1”).Activate

            ActiveCell.Value = 10

End Sub

            Разбор кода:

Worksheets(“Sheet1”).Activate – указываем лист (Worksheets(“Sheet1”)), который хотим активировать (.Activate).

ActiveCell.Value = 10 – для активной ячейки (ActiveCell) применяем свойство (.Value), чтобы записать в ячейку значение «10».

            Примечание! Вы можете работать с активной ячейкой только после того, как активировали лист.

            Moving the Active cell. Сдвиг активной ячейки.

            Вы можете использовать метод Activate («активировать»), чтобы сделать ячейку активной.

            Пример 2.3.2

            Сделаем ячейку A3 активной и отформатируем шрифт в ней жирным.

Public Sub Example232()

            Worksheets(“Sheet1”).Activate

            Worksheets(“Sheet1”).Range(“A3”).Activate

            ActiveCell.Font.Bold = True

End Sub

Разбор кода:

Worksheets(“Sheet1”).Activate — указываем лист (Worksheets(“Sheet1”)), который активируем (.Activate).

Worksheets(“Sheet1”).Range(“A3”).Activate – активируем (.Activate) ячейку (.Range(“A3”)) на листе (Worksheets(“Sheet1”)).

ActiveCell – применяем свойство ActiveCell для работы с активной ячейкой.

.Font – это свойство «Шрифт» указывает на то, что будем обращаться ко всему шрифту в диапазоне A1:B1.

.Bold = True – активируем параметр Bold (жирный) с помощью слова True (истина). А если будет False (ложь), то жирность шрифта была бы убрана (если шрифт уже был жирный).

            Примечание! Для выбора диапазона ячеек используйте метод Select. Чтобы сделать одну ячейку активной используйте метод Activate.

            Вы можете использовать свойство Offset для сдвига активной ячейки.

            Пример 2.3.3

            В этом примере вставим текст (например “Hello”) в активную ячейку (“A1”) выделенного диапазона (“A1:B2”) и затем сделаем активной ячейку, находящуюся справа от активной, без снятия выделения.

Public Sub Example233()

            Worksheets(“Sheet1”).Activate

            Range(“A1:B2”).Select

            ActiveCell.Value = “Hello!”

            ActiveCell.Offset(0, 1).Activate

End Sub

Разбор кода:

Worksheets(“Sheet1”).Activate — указываем лист (Worksheets(“Sheet1”)), который активируем (.Activate).

Range(“A1:B2”).Select – указываем диапазон (Range(“A1:B2”)) и выделяем его (.Select).

ActiveCell.Value = “Hello!” – для активной ячейки (ActiveCell) применяем свойство (.Value), чтобы записать в ячейку значение “Hello!”.

ActiveCell – данное свойство говорит о том, что хотим работать с заранее выделенной ячейкой (у нас выделена ячейка «B2», значит с ней и будем работать)

.Offset(0, 1) – данное свойство указывает, на сколько строк и столбцов надо сместиться, первый параметр указывает, на сколько строк будет смещаться, в данном случае на 0 строк вниз (а если была бы цифра «-1», то смещение было бы вверх на 1 строку). Второй параметр «1» указывает, что надо сместиться на 1 столбец вправо (а если бы была цифра «-1», то смещение было бы влево на 1 столбец).

            Selecting the Cells Surrounding the Active Cell. Выбор ячеек окружающих активную ячейку.

            Свойство CurrentRegion («текущий регион») возвращает диапазон ячеек, ограниченный пустыми строками и столбцами.

            Пример 2.3.4

            В этом примере выделим все ячейки, которые примыкают к активной ячейке и содержат данные. Новый диапазон отформатируем числовым форматом «Финансовый» («Currency»).

Public Sub Example234()

            Worksheets(“Sheet1”).Activate

            ActiveCell.CurrentRegion.Select

            Selection.Style = “Currency”

End Sub

            Разбор кода:

Worksheets(“Sheet1”).Activate — указываем лист (Worksheets(“Sheet1”)), который активируем (.Activate).

ActiveCell.CurrentRegion.Select – для активной ячейки (ActiveCell) укажем область её окружающую (.CurrentRegion) и выделим её (.Select).

Selection.Style = “Currency” – для выделенного (Selection) применим формат (.Style) «Финансовый» (= “Currency”).

            2.4  Cell Error Values. Значения ошибок в ячейках.

            Вы можете вставить значение ошибки ячейки в саму ячейку или протестировать содержимое ячейки на наличие ошибок, используя CVErr функцию. Значения ошибок соответствуют константам xlCVError.

            Пример 2.4.1

            В этом примере вставим в диапазон A1:A7 на листе Sheet1. Все семь значений ошибок.

Public Sub Example241()

            Dim myArray() As Variant

            myArray() = Array (xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum, xlErrRef, xlErrValue)

Dim i As Integer

            For i = 1 to 7

                        Worksheets(“Sheet1”).Cells(i, 1).Value = CVErr(myArray(i-1))

            Next i

End Sub

            Разбор кода:

Dim myArray() As Variant – объявляем (Dim) массив с названием (myArray()) типа (Variant).

myArray() = Array (xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum, xlErrRef, xlErrValue) – в объявленный массив myArray() с помощью функции (Array) вносим константы ошибок.

For i = 1 to 7 – объявляем цикл For… Next, с количеством повторов 7.

Worksheets(“Sheet1”).Cells(i, 1).Value = CVErr(myArray(i-1)) – вставляем внутрь цикла операцию которая будет выполняться. В данном случае на листе (Worksheets(“Sheet1”)) в ячейки (.Cells(i, 1), где вместо i будет вставляться цифра, соответствующая повтору цикла (на первом повторе – цифра 1, на втором – 2 и т.д.)) в значение (.Value) вставим функцию проверки ошибок (CVErr) для значений из массива (myArray(i-1), отметим, что i-1 это значение массива для определённого шага, но вычитаем 1, т.к. логически очерёдность значений в массиве начинается с 0, а не с 1, поэтому чтобы для первого повтора цикла взять первое значение из массива приходится вычесть 1, чтобы получить 0, т.е. первое значение из массива).

Next i – закрываем цикл и запускаем его на повтор.

            Пример 2.4.2

            На дисплее появится сообщение с именем ошибки, если ячейка содержит ошибку. Вы можете использовать этот пример, как шаблон для обработки ошибок. Если ошибок в ячейке нет, то всплывёт сообщение «Нет ошибок».

Public Sub Example242()

            If IsError(ActiveCell.Value) Then

            Dim Errname As Variant

            Errname = ActiveCell.Value

                        Select Case Errname

                        Case CVErr(xlErrDiv0)

                                    MsgBox “#DIV/0! Error”

                        Case CVErr(xlErrNA)

                                   MsgBox “#N/A Error”

                        Case CVErr(xlErrName)

                                   MsgBox “#NAME? Error”

                        Case CVErr(xlErrNull)

                                   MsgBox “#NULL! Error”

                        Case CVErr(xlErrNum)

                                   MsgBox “#NUM! Error”

                        Case CVErr(xlErrRef)

                                   MsgBox “#Ref! Error”

                        Case CVErr(xlErrValue)

                                   MsgBox “#Value! Error”

                        Case Else

                        MsgBox “Нет ошибок”

                        End Select

            End If

End Sub

            Разбор кода:

If IsError(ActiveCell.Value) Then – вводим конструкцию If… Then, которая говорит, что если (If) функция проверки на ошибку (IsError) для значения (.Value) в активной ячейке (ActiveCell) обнаружит ошибку, то выполнять действия после слова (Then).

Dim Errname As Variant – объявляем переменную Errname типа Variant.

Errname = ActiveCell.Value – присваиваем переменной (Errname =) значение (.Value) активной ячейки (ActiveCell).

Select Case Errname – пишем конструкцию Select Case, для проверки значения Errname.

Case CVErr(xlErrDiv0) – вот и проверка с помощью специального слова Case. Если Errname равно значению CVErr(xlErrDiv0), то выполнять следующую строку кода.

MsgBox “#DIV/0! Error” – вот строка кода, которая будет выполняться, т.е. функция MsgBox выведет нам на экран сообщение “#DIV/0! Error”.

Для всех остальных Case логика такая же.

Case Else – эта конструкция говорит, что если все предыдущие Case не дадут логическое ИСТИНА, (т.е. будут неверны или можно сказать приведут к ответу «нет»), то следует выполнить следующую строку кода.

MsgBox “Нет ошибок” – в данном случае эта строка кода с помощью функции MsgBox выведет на экран сообщение «Нет ошибок».

End Select – закрываем конструкцию Select Case.

End If – закрываем конструкцию If… Then.

Всё о работе с ячейками в Excel-VBA: обращение, перебор, удаление, вставка, скрытие, смена имени.

Содержание:

Table of Contents:

• Что такое ячейка Excel?
• Способы обращения к ячейкам
  • Выбор и активация
  • Получение и изменение значений ячеек
    • Ячейки открытой книги
    • Ячейки закрытой книги 
  • Перебор ячеек
  • Перебор в произвольном диапазоне
• Свойства и методы ячеек
  • Имя ячейки
  • Адрес ячейки
  • Размеры ячейки
• Запуск макроса активацией ячейки

2 нюанса:

1. Я почти везде стараюсь использовать ThisWorkbook (а не, например, ActiveWorkbook) для обращения к текущей книге, в которой написан этот код (считаю это наиболее безопасным для новичков способом обращения к книгам, чтобы случайно не внести изменения в другие книги). Для экспериментов можете вставлять этот код в модули, коды книги, либо листа, и он будет работать только в пределах этой книги. 
2. Я использую английский эксель и у меня по стандарту листы называются Sheet1, Sheet2 и т.д. Если вы работаете в русском экселе, то замените Thisworkbook.Sheets(«Sheet1») на Thisworkbook.Sheets(«Лист1»). Если этого не сделать, то вы получите ошибку в связи с тем, что пытаетесь обратиться к несуществующему объекту. Можно также заменить на Thisworkbook.Sheets(1), но это менее безопасно.

Что такое ячейка Excel?

В большинстве мест пишут: «элемент, образованный пересечением столбца и строки». Это определение полезно для людей, которые не знакомы с понятием «таблица». Для того, чтобы понять чем на самом деле является ячейка Excel, необходимо заглянуть в объектную модель Excel. При этом определения объектов «ряд», «столбец» и «ячейка» будут отличаться в зависимости от того, как мы работаем с файлом.

Объекты в Excel-VBA. Пока мы работаем в Excel без углубления в VBA определение ячейки как «пересечения» строк и столбцов нам вполне хватает, но если мы решаем как-то автоматизировать процесс в VBA, то о нём лучше забыть и просто воспринимать лист как «мешок» ячеек, с каждой из которых VBA позволяет работать как минимум тремя способами:

1. по цифровым координатам (ряд, столбец),
2. по адресам формата А1, B2 и т.д. (сценарий целесообразности данного способа обращения в VBA мне сложно представить)
3. по уникальному имени (во втором и третьем вариантах мы будем иметь дело не совсем с ячейкой, а с объектом VBA range, который может состоять из одной или нескольких ячеек). Функции и методы объектов Cells и Range отличаются. Новичкам я бы порекомендовал работать с ячейками VBA только с помощью Cells и по их цифровым координатам и использовать Range только по необходимости.

Все три способа обращения описаны далее

Как это хранится на диске и как с этим работать вне Excel? С точки зрения хранения и обработки вне Excel и VBA. Сделать это можно, например, сменив расширение файла с .xls(x) на .zip и открыв этот архив.

Пример содержимого файла Excel:

Далее xl -> worksheets и мы видим файл листа

Содержимое файла:

 То же, но более наглядно:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{00000000-0001-0000-0000-000000000000}">
	<dimension ref="B2:F6"/>
	<sheetViews>
		<sheetView tabSelected="1" workbookViewId="0">
			<selection activeCell="D12" sqref="D12"/>
		</sheetView>
	</sheetViews>
	<sheetFormatPr defaultRowHeight="14.4" x14ac:dyDescent="0.3"/>
	<sheetData>
		<row r="2" spans="2:6" x14ac:dyDescent="0.3">
			<c r="B2" t="s">
				<v>0</v>
			</c>
		</row>
		<row r="3" spans="2:6" x14ac:dyDescent="0.3">
			<c r="C3" t="s">
				<v>1</v>
			</c>
		</row>
		<row r="4" spans="2:6" x14ac:dyDescent="0.3">
			<c r="D4" t="s">
				<v>2</v>
			</c>
		</row>
		<row r="5" spans="2:6" x14ac:dyDescent="0.3">
			<c r="E5" t="s">
				<v>0</v></c>
		</row>
		<row r="6" spans="2:6" x14ac:dyDescent="0.3">
			<c r="F6" t="s"><v>3</v>
		</c></row>
	</sheetData>
	<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

Как мы видим, в структуре объектной модели нет никаких «пересечений». Строго говоря рабочая книга — это архив структурированных данных в формате XML. При этом в каждую «строку» входит «столбец», и в нём в свою очередь прописан номер значения данного столбца, по которому оно подтягивается из другого XML файла при открытии книги для экономии места за счёт отсутствия повторяющихся значений. Почему это важно. Если мы захотим написать какой-то обработчик таких файлов, который будет напрямую редактировать данные в этих XML, то ориентироваться надо на такую модель и структуру данных. И правильное определение будет примерно таким: ячейка — это объект внутри столбца, который в свою очередь находится внутри строки в файле xml, в котором хранятся данные о содержимом листа.

Способы обращения к ячейкам

Выбор и активация

Почти во всех случаях можно и стоит избегать использования методов Select и Activate. На это есть две причины:

1. Это лишь имитация действий пользователя, которая замедляет выполнение программы. Работать с объектами книги можно напрямую без использования методов Select и Activate.
2. Это усложняет код и может приводить к неожиданным последствиям. Каждый раз перед использованием Select необходимо помнить, какие ещё объекты были выбраны до этого и не забывать при необходимости снимать выбор. Либо, например, в случае использования метода Select в самом начале программы может быть выбрано два листа вместо одного потому что пользователь запустил программу, выбрав другой лист.

Можно выбирать и активировать книги, листы, ячейки, фигуры, диаграммы, срезы, таблицы и т.д.

Отменить выбор  ячеек можно методом Unselect:

Selection.Unselect

Отличие выбора от активации — активировать можно только один объект из раннее выбранных. Выбрать можно несколько объектов.

Если вы записали и редактируете код макроса, то лучше всего заменить Select и Activate на конструкцию With … End With. Например, предположим, что мы записали вот такой макрос:

Sub Macro1()
' Macro1 Macro
    Range("F4:F10,H6:H10").Select 'выбрали два несмежных диапазона зажав ctrl
    Range("H6").Activate          'показывает только то, что я начал выбирать второй диапазон с этой ячейки (она осталась белой). Это действие ни на что не влияет
    With Selection.Interior       
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535            'залили желтым цветом, нажав на кнопку заливки на верхней панели
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Почему макрос записался таким неэффективным образом? Потому что в каждый момент времени (в каждой строке) программа не знает, что вы будете делать дальше. Поэтому в записи выбор ячеек и действия с ними — это два отдельных действия. Этот код лучше всего оптимизировать (особенно если вы хотите скопировать его внутрь какого-нибудь цикла, который должен будет исполняться много раз и перебирать много объектов). Например, так:

Sub Macro11()
'
' Macro1 Macro
    Range("F4:F10,H6:H10").Select '1. смотрим, что за объект выбран (что идёт до .Select)
    Range("H6").Activate
    With Selection.Interior       '2. понимаем, что у выбранного объекта есть свойство interior, с которым далее идёт работа
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub



Sub Optimized_Macro()
    With Range("F4:F10,H6:H10").Interior '3. переносим объект напрямую в конструкцию With вместо Selection
' ////// Здесь я для надёжности прописал бы ещё Thisworkbook.Sheet("ИмяЛиста") перед Range,
' ////// чтобы минимизировать риск любых случайных изменений других листов и книг
' ////// With Thisworkbook.Sheet("ИмяЛиста").Range("F4:F10,H6:H10").Interior
        .Pattern = xlSolid               '4. полностью копируем всё, что было записано рекордером внутрь блока with
        .PatternColorIndex = xlAutomatic
        .Color = 55555                   '5. здесь я поменял цвет на зеленый, чтобы было видно, работает ли код при поочерёдном запуске двух макросов
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Пример сценария, когда использование Select и Activate оправдано:

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

Sub Select_Activate_is_OK()
Thisworkbook.Worksheets(Array("Sheet1", "Sheet3")).Select 'Выбираем несколько листов по именам
Thisworkbook.Worksheets("Sheet3").Activate 'Показываем пользователю третий лист
'Далее все действия с выбранными ячейками через Select будут одновременно вносить изменения в оба выбранных листа

'Допустим, что тут мы решили покрасить те же два диапазона:
Range("F4:F10,H6:H10").Select
    Range("H6").Activate
    With Selection.Interior       
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub

Единственной причиной использовать этот код по моему мнению может быть желание зачем-то показать пользователю определённую страницу книги в какой-то момент исполнения программы. С точки зрения обработки объектов, опять же, эти действия лишние.

Получение и изменение значений ячеек

Значение ячеек можно получать/изменять с помощью свойства value. 

'Если нужно прочитать / записать значение ячейки, то используется свойство Value
a = ThisWorkbook.Sheets("Sheet1").Cells (1,1).Value 'записать значение ячейки А1 листа "Sheet1" в переменную "a"
ThisWorkbook.Sheets("Sheet1").Cells (1,1).Value = 1  'задать значение ячейки А1 (первый ряд, первый столбец) листа "Sheet1"

'Если нужно прочитать текст как есть (с форматированием), то можно использовать свойство .text:
ThisWorkbook.Sheets("Sheet1").Cells (1,1).Text = "1" 
a = ThisWorkbook.Sheets("Sheet1").Cells (1,1).Text

'Когда проявится разница:
'Например, если мы считываем дату в формате "31 декабря 2021 г.", хранящуюся как дата
a = ThisWorkbook.Sheets("Sheet1").Cells (1,1).Value 'эапишет как "31.12.2021"
a = ThisWorkbook.Sheets("Sheet1").Cells (1,1).Text  'запишет как "31 декабря 2021 г."

Ячейки открытой книги

К ячейкам можно обращаться:

'В книге, в которой хранится макрос (на каком-то из листов, либо в отдельном модуле или форме)
ThisWorkbook.Sheets("Sheet1").Cells(1,1).Value        'По номерам строки и столбца
ThisWorkbook.Sheets("Sheet1").Cells(1,"A").Value      'По номерам строки и букве столбца
ThisWorkbook.Sheets("Sheet1").Range("A1").Value       'По адресу - вариант 1
ThisWorkbook.Sheets("Sheet1").[A1].Value              'По адресу - вариант 2
ThisWorkbook.Sheets("Sheet1").Range("CellName").Value 'По имени ячейки (для этого ей предварительно нужно его присвоить)

'Те же действия, но с использованием полного названия рабочей книги (книга должна быть открыта)
Workbooks("workbook.xlsm").Sheets("Sheet1").Cells(1,1).Value 'По номерам строки и столбца
Workbooks("workbook.xlsm").Sheets("Sheet1").Cells(1,"A").Value                'По номерам строки и букве столбца
Workbooks("workbook.xlsm").Sheets("Sheet1").Range("A1").Value                 'По адресу - вариант 1
Workbooks("workbook.xlsm").Sheets("Sheet1").[A1].Value                        'По адресу - вариант 2
Workbooks("workbook.xlsm").Sheets("Sheet1").Range("CellName").Value           'По имени ячейки (для этого ей предварительно нужно его присвоить)

Ячейки закрытой книги

Если нужно достать или изменить данные в другой закрытой книге, то необходимо прописать открытие и закрытие книги. Непосредственно работать с закрытой книгой не получится, потому что данные в ней хранятся отдельно от структуры и при открытии Excel каждый раз производит расстановку значений по соответствующим «слотам» в структуре. Подробнее о том, как хранятся данные в xlsx см выше.

Workbooks.Open Filename:="С:\closed_workbook.xlsx"    'открыть книгу (она становится активной)
a = ActiveWorkbook.Sheets("Sheet1").Cells(1,1).Value  'достать значение ячейки 1,1
ActiveWorkbook.Close False                            'закрыть книгу (False => без сохранения)

Скачать пример, в котором можно посмотреть, как доставать и как записывать значения в закрытую книгу. 

Код из файла:

Option Explicit
Sub get_value_from_closed_wb() 'достать значение из закрытой книги
Dim a, wb_path, wsh As String
wb_path = ThisWorkbook.Sheets("Sheet1").Cells(2, 3).Value 'get path to workbook from sheet1
wsh = ThisWorkbook.Sheets("Sheet1").Cells(3, 3).Value
Workbooks.Open Filename:=wb_path
a = ActiveWorkbook.Sheets(wsh).Cells(3, 3).Value
ActiveWorkbook.Close False
ThisWorkbook.Sheets("Sheet1").Cells(4, 3).Value = a
End Sub

Sub record_value_to_closed_wb() 'записать значение в закрытую книгу
Dim wb_path, b, wsh As String
wsh = ThisWorkbook.Sheets("Sheet1").Cells(3, 3).Value
wb_path = ThisWorkbook.Sheets("Sheet1").Cells(2, 3).Value 'get path to workbook from sheet1
b = ThisWorkbook.Sheets("Sheet1").Cells(5, 3).Value 'get value to record in the target workbook
Workbooks.Open Filename:=wb_path
ActiveWorkbook.Sheets(wsh).Cells(4, 4).Value = b 'add new value to cell D4 of the target workbook
ActiveWorkbook.Close True
End Sub

Перебор ячеек

Перебор в произвольном диапазоне

Скачать файл со всеми примерами

Пройтись по всем ячейкам в нужном диапазоне можно разными способами. Основные:

1. Цикл For Each. Пример:

   Sub iterate_over_cells()
   
   For Each c In ThisWorkbook.Sheets("Sheet1").Range("B2:D4").Cells
   MsgBox (c)
   Next c
   
   End Sub​

   Этот цикл выведет в виде сообщений значения ячеек в диапазоне B2:D4 по порядку по строкам слева направо и по столбцам — сверху вниз. Данный способ можно использовать для действий, в который вам не важны номера ячеек (закрашивание, изменение форматирования, пересчёт чего-то и т.д.).

2. Ту же задачу можно решить с помощью двух вложенных циклов — внешний будет перебирать ряды, а вложенный — ячейки в рядах. Этот способ я использую чаще всего, потому что он позволяет получить больше контроля над исполнением: на каждой итерации цикла нам доступны координаты ячеек. Для перебора всех ячеек на листе этим методом потребуется найти последнюю заполненную ячейку. Пример кода:

   Sub iterate_over_cells()
   
   Dim cl, rw As Integer
   Dim x As Variant
   
   'перебор области 3x3
   For rw = 1 To 3 ' цикл для перебора рядов 1-3
   
       For cl = 1 To 3 'цикл для перебора столбцов 1-3
           x = ThisWorkbook.Sheets("Sheet1").Cells(rw + 1, cl + 1).Value
           MsgBox (x)
       Next cl
   Next rw
   
   
   
   'перебор всех ячеек на листе. Последняя ячейка определена с помощью UsedRange
   'LastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
   'LastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1
   'For rw = 1 To LastRow 'цикл перебора всех рядов
   '    For cl = 1 To LastCol 'цикл для перебора всех столбцов
   '        Действия 
   '    Next cl
   'Next rw
   
   
   End Sub​

3. Если нужно перебрать все ячейки в выделенном диапазоне на активном листе, то код будет выглядеть так:

   Sub iterate_cell_by_cell_over_selection()
       Dim ActSheet As Worksheet
       Dim SelRange As Range
       Dim cell As Range
       
    
       Set ActSheet = ActiveSheet
       Set SelRange = Selection
       
       'if we want to do it in every cell of the selected range
       For Each cell In Selection
       MsgBox (cell.Value)
       
       Next cell
   
   End Sub​

   Данный метод подходит для интерактивных макросов, которые выполняют действия над выбранными пользователем областями.

4. Перебор ячеек в ряду

   Sub iterate_cells_in_row()
       Dim i, RowNum, StartCell As Long
       
       RowNum = 3 'какой ряд
       StartCell = 0 ' номер начальной ячейки (минус 1, т.к. в цикле мы прибавляем i)
       
       For i = 1 To 10 ' 10 ячеек в выбранном ряду
       ThisWorkbook.Sheets("Sheet1").Cells(RowNum, i + StartCell).Value = i '(i + StartCell) добавляет 1 к номеру столбца при каждом повторении
       Next i
   
   End Sub

5. Перебор ячеек в столбце

   Sub iterate_cells_in_column()
       Dim i, ColNum, StartCell As Long
       
       ColNum = 3 'какой столбец
       StartCell = 0 ' номер начальной ячейки (минус 1, т.к. в цикле мы прибавляем i)
       
       For i = 1 To 10 ' 10 ячеек
       ThisWorkbook.Sheets("Sheet1").Cells(i + StartCell, ColNum).Value = i ' (i + StartCell) добавляет 1 к номеру ряда при каждом повторении
       Next i
   
   End Sub​

Свойства и методы ячеек

Имя ячейки

Присвоить новое имя можно так:

Thisworkbook.Sheets(1).Cells(1,1).name = "Новое_Имя"

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

ActiveWorkbook.Names("Старое_Имя").Delete

Пример кода для переименования ячеек:

Sub rename_cell()

old_name = "Cell_Old_Name"
new_name = "Cell_New_Name"

ActiveWorkbook.Names(old_name).Delete
ThisWorkbook.Sheets(1).Cells(2, 1).Name = new_name
End Sub

Sub rename_cell_reverse()

old_name = "Cell_New_Name"
new_name = "Cell_Old_Name"

ActiveWorkbook.Names(old_name).Delete
ThisWorkbook.Sheets(1).Cells(2, 1).Name = new_name
End Sub

Адрес ячейки

Sub get_cell_address() ' вывести адрес ячейки в формате буква столбца, номер ряда
  '$A$1 style
  txt_address = ThisWorkbook.Sheets(1).Cells(3, 2).Address
  MsgBox (txt_address)
End Sub

Sub get_cell_address_R1C1()' получить адрес столбца в формате номер ряда, номер столбца
  'R1C1 style
  txt_address = ThisWorkbook.Sheets(1).Cells(3, 2).Address(ReferenceStyle:=xlR1C1)
  MsgBox (txt_address)
End Sub

  'пример функции, которая принимает 2 аргумента: название именованного диапазона и тип желаемого адреса 
  '(1- тип $A$1 2- R1C1 - номер ряда, столбца)
Function get_cell_address_by_name(str As String, address_type As Integer)
  '$A$1 style
  Select Case address_type
    Case 1
      txt_address = Range(str).Address
    Case 2
      txt_address = Range(str).Address(ReferenceStyle:=xlR1C1)
    Case Else
      txt_address = "Wrong address type selected. 1,2 available"
    End Select
  get_cell_address_by_name = txt_address
End Function

'перед запуском нужно убедиться, что в книге есть диапазон с названием, 
'адрес которого мы хотим получить, иначе будет ошибка
Sub test_function() 'запустите эту программу, чтобы увидеть, как работает функция
  x = get_cell_address_by_name("MyValue", 2)
  MsgBox (x)
End Sub

Размеры ячейки

Ширина и длина ячейки в VBA меняется, например, так:

Sub change_size()
Dim x, y As Integer
Dim w, h As Double

'получить координаты целевой ячейки
x = ThisWorkbook.Sheets("Sheet1").Cells(2, 2).Value
y = ThisWorkbook.Sheets("Sheet1").Cells(3, 2).Value

'получить желаемую ширину и высоту ячейки
w = ThisWorkbook.Sheets("Sheet1").Cells(6, 2).Value
h = ThisWorkbook.Sheets("Sheet1").Cells(7, 2).Value

'сменить высоту и ширину ячейки с координатами x,y
ThisWorkbook.Sheets("Sheet1").Cells(x, y).RowHeight = h
ThisWorkbook.Sheets("Sheet1").Cells(x, y).ColumnWidth = w


End Sub

Прочитать значения ширины и высоты ячеек можно двумя способами (однако результаты будут в разных единицах измерения). Если написать просто Cells(x,y).Width или Cells(x,y).Height, то будет получен результат в pt (привязка к размеру шрифта). 

Sub get_size()
Dim x, y As Integer
'получить координаты ячейки, с которой мы будем работать
x = ThisWorkbook.Sheets("Sheet1").Cells(2, 2).Value
y = ThisWorkbook.Sheets("Sheet1").Cells(3, 2).Value

'получить длину и ширину выбранной ячейки в тех же единицах измерения, в которых мы их задавали
ThisWorkbook.Sheets("Sheet1").Cells(2, 6).Value = ThisWorkbook.Sheets("Sheet1").Cells(x, y).ColumnWidth
ThisWorkbook.Sheets("Sheet1").Cells(3, 6).Value = ThisWorkbook.Sheets("Sheet1").Cells(x, y).RowHeight

'получить длину и ширину с помощью свойств ячейки (только для чтения) в поинтах (pt)
ThisWorkbook.Sheets("Sheet1").Cells(7, 9).Value = ThisWorkbook.Sheets("Sheet1").Cells(x, y).Width
ThisWorkbook.Sheets("Sheet1").Cells(8, 9).Value = ThisWorkbook.Sheets("Sheet1").Cells(x, y).Height

End Sub

Скачать файл с примерами изменения и чтения размера ячеек

Запуск макроса активацией ячейки

Для запуска кода VBA при активации ячейки необходимо вставить в код листа нечто подобное:

3 важных момента, чтобы это работало:

1. Этот код должен быть вставлен в код листа (здесь контролируется диапазон D4)

2-3. Программа, ответственная за запуск кода при выборе ячейки, должна называться Worksheet_SelectionChange и должна принимать значение переменной Target, относящейся к триггеру SelectionChange. Другие доступные триггеры можно посмотреть в правом верхнем углу (2).

Скачать файл с базовым примером (как на картинке)

Скачать файл с расширенным примером (код ниже)

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        ' имеем в виду, что триггер SelectionChange будет запускать эту Sub после каждого клика мышью (после каждого клика будет проверяться:
          '1. количество выделенных ячеек и 
          '2. не пересекается ли выбранный диапазон с заданным в этой программе диапазоном.
        ' поэтому в эту программу не стоит без необходимости писать никаких других тяжелых операций

    If Selection.Count = 1 Then 'запускаем программу только если выбрано не более 1 ячейки


    'вариант модификации - брать адрес ячейки из другой ячейки:
    'Dim CellName as String
    'CellName = Activesheet.Cells(1,1).value 'брать текстовое имя контролируемой ячейки из A1 (должно быть в формате Буква столбца + номер строки)
    'If Not Intersect(Range(CellName), Target) Is Nothing Then
    'для работы этой модификации следующую строку надо закомментировать/удалить



        If Not Intersect(Range("D4"), Target) Is Nothing Then 
        'если заданный (D4) и выбранный диапазон пересекаются 
        '(пересечение диапазонов НЕ равно Nothing)

        'можно прописать диапазон из нескольких ячеек:
        'If Not Intersect(Range("D4:E10"), Target) Is Nothing Then
        'можно прописать несколько диапазонов:
        'If Not Intersect(Range("D4:E10"), Target) Is Nothing or Not Intersect(Range("A4:A10"), Target) Is Nothing Then

            Call program 'выполняем программу
        End If
    End If
End Sub

Sub program()

MsgBox ("Program Is running") 'здесь пишем код того, что произойдёт при выборе нужной ячейки


End Sub