Объект Excel.Worksheet, программное создание, обнаружение, удаление листа Excel средствами VBA, свойства, методы и события объекта Excel.Worksheet
В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.
Чаще всего для ввода данных в Excel (напрямую или из базы данных) нам потребуется в первую очередь определиться с листом, на который пойдет ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать.
Процесс создания выглядит очень просто:
Dim oExcel As New Excel.Application ‘Запускаем Excel
oExcel.Visible = True ‘Делаем его видимым
Dim oWbk As Excel.Workbook
Set oWbk = oExcel.Workbooks.Add () ‘Создаем новую книгу
Dim oSheet As Excel.Worksheet
Set oSheet = oWbk.Worksheets.Add() ‘ Создаем новый лист
oSheet.Name = «Новый лист» ‘Присваиваем ему имя «Новый лист»
Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.
Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем «Лист1» и переименовываем его в «Новый лист»:
Dim oExcel As New Excel.Application ‘Запускаем Excel
oExcel.Visible = True ‘Делаем его видимым
Dim oWbk As Excel.Workbook
Set oWbk = oExcel.Workbooks.Add() ‘Создаем новую книгу
Dim oSheet As Excel.Worksheet
Set oSheet = oWbk.Worksheets.Item(«Лист 1») ‘ Находим Лист1
oSheet.Name = «Новый лист» ‘Присваиваем ему имя «Новый лист»
Обратите внимание, что в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются «Sheet1», «Sheet2» и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.
У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.
У объекта Worksheet — множество важных свойств и методов:
Некоторые важные методы объекта Worksheet:
Самое важное событие объекта Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.
У объекта Worksheet есть еще два очень удобных события (их сильно не хватает объекту Document в Word). Это — события BeforeRightClick() и BeforeDoubleClick(). Как понятно из названия, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т.п.) на действия пользователя.
Объекты 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 (на английском).
Из объекта Workbook можно получить доступ к объекту Sheets, который является коллекцией всех листов рабочей книги (рабочие листы и диаграммы), а также к объекту Worksheets, который представляет из себя коллекцию всех рабочих листов книги Excel.
Кроме этого Вы можете использовать ActiveSheet для доступа к активному в данный момент рабочему листу. Из объекта Worksheet можно получить доступ к объектам Rows и Columns, которые являются коллекцией объектов 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.
Рассмотрим несколько примеров
Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.
В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.
В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.
Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.
Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.
Свойства и методы Worksheet
Мечтатель начинает с чистого листа бумаги и переосмысливает мир
Эта статья содержит полное руководство по использованию Excel VBA Worksheet в Excel VBA. Если вы хотите узнать, как что-то сделать быстро, ознакомьтесь с кратким руководством к рабочему листу VBA ниже.
Если вы новичок в VBA, то эта статья — отличное место для начала. Мне нравится разбивать вещи на простые термины и объяснять их на простом языке.
Вы можете прочитать статью от начала до конца, так как она написана в логическом порядке. Или, если предпочитаете, вы можете использовать оглавление ниже и перейти непосредственно к теме по вашему выбору.
Краткое руководство к рабочему листу VBA
Вступление
Три наиболее важных элемента VBA — это Рабочая книга, Рабочий лист и Ячейки. Из всего кода, который вы пишете, 90% будут включать один или все из них.
Наиболее распространенное использование Worksheet в VBA для доступа к его ячейкам. Вы можете использовать его для защиты, скрытия, добавления, перемещения или копирования листа.
Тем не менее, вы будете в основном использовать его для выполнения некоторых действий с одной или несколькими ячейками на листе.
Использование Worksheets более простое, чем использование рабочих книг. С книгами вам может потребоваться открыть их, найти, в какой папке они находятся, проверить, используются ли они, и так далее. С рабочим листом он либо существует в рабочей книге, либо его нет.
Доступ к рабочему листу
В VBA каждая рабочая книга имеет коллекцию рабочих листов. В этой коллекции есть запись для каждого рабочего листа. Эта коллекция называется просто Worksheets и используется очень похоже на коллекцию Workbooks. Чтобы получить доступ к рабочему листу, достаточно указать имя.
Приведенный ниже код записывает «Привет Мир» в ячейках A1 на листах: Лист1, Лист2 и Лист3 текущей рабочей книги.
Коллекция Worksheets всегда принадлежит книге. Если мы не указываем рабочую книгу, то активная рабочая книга используется по умолчанию.
Скрыть рабочий лист
В следующих примерах показано, как скрыть и показать лист.
Если вы хотите запретить пользователю доступ к рабочему листу, вы можете сделать его «очень скрытым». Это означает, что это может быть сделано видимым только кодом.
Защитить рабочий лист
Другой пример использования Worksheet — когда вы хотите защитить его.
Индекс вне диапазона
При использовании Worksheets вы можете получить сообщение об ошибке:
Run-time Error 9 Subscript out of Range
Это означает, что вы пытались получить доступ к рабочему листу, который не существует. Это может произойти по следующим причинам:
Если у вас остались проблемы, используйте один из циклов из раздела «Циклы по рабочим листам», чтобы напечатать имена всех рабочих листов коллекции.
Использование индекса для доступа к рабочему листу
До сих пор мы использовали имя листа для доступа к листу. Указатель относится к положению вкладки листа в рабочей книге. Поскольку положение может быть легко изменено пользователем, не рекомендуется использовать это.
В следующем коде показаны примеры использования индекса.
Использование кодового имени рабочего листа
Лучший способ получить доступ к рабочему листу — использовать кодовое имя. Каждый лист имеет имя листа и кодовое имя. Имя листа — это имя, которое отображается на вкладке листа в Excel.
Изменение имени листа не приводит к изменению кодового имени, что означает, что ссылка на лист по кодовому имени — отличная идея.
Если вы посмотрите в окне свойств VBE, вы увидите оба имени. На рисунке вы можете видеть, что кодовое имя — это имя вне скобок, а имя листа — в скобках.
Вы можете изменить как имя листа, так и кодовое имя в окне свойств листа (см. Изображение ниже).
Если ваш код ссылается на кодовое имя, то пользователь может изменить имя листа, и это не повлияет на ваш код. В приведенном ниже примере мы ссылаемся на рабочий лист напрямую, используя кодовое имя.
Это делает код легким для чтения и безопасным от изменения пользователем имени листа.
Кодовое имя в других книгах
Есть один недостаток использования кодового имени. Он относится только к рабочим листам в рабочей книге, которая содержит код, т.е. ThisWorkbook.
Однако мы можем использовать простую функцию, чтобы найти кодовое имя листа в другой книге.
Использование приведенного выше кода означает, что если пользователь изменит имя рабочего листа, то на ваш код это не повлияет.
Существует другой способ получения имени листа внешней рабочей книги с использованием кодового имени. Вы можете использовать элемент VBProject этой Рабочей книги.
Вы можете увидеть, как это сделать, в примере ниже. Я включил это, как дополнительную информацию, я бы рекомендовал использовать метод из предыдущего примера, а не этот.
Резюме кодового имени
Ниже приведено краткое описание использования кодового имени:
Активный лист
Объект ActiveSheet ссылается на рабочий лист, который в данный момент активен. Вы должны использовать ActiveSheet только в том случае, если у вас есть особая необходимость ссылаться на активный лист.
В противном случае вы должны указать рабочий лист, который вы используете.
Если вы используете метод листа, такой как Range, и не упоминаете лист, он по умолчанию будет использовать активный лист.
Объявление объекта листа
Объявление объекта листа полезно для того, чтобы сделать ваш код более понятным и легким для чтения.
В следующем примере показан код для обновления диапазонов ячеек. Первый Sub не объявляет объект листа. Вторая подпрограмма объявляет объект листа, и поэтому код намного понятнее.
Вы также можете использовать ключевое слово With с объектом листа, как показано в следующем примере.
Доступ к рабочему листу в двух словах
Из-за множества различных способов доступа к рабочему листу вы можете быть сбитыми с толку. Так что в этом разделе я собираюсь разбить его на простые термины.
2. Если лист находится в той же книге, что и код, используйте кодовое имя.
3. Если рабочая таблица находится в другой рабочей книге, сначала получите рабочую книгу, а затем получите рабочую таблицу.
Если вы хотите защитить пользователя от изменения имени листа, используйте функцию SheetFromCodeName из раздела «Имя кода».
Добавить рабочий лист
Примеры в этом разделе показывают, как добавить новую рабочую таблицу в рабочую книгу. Если вы не предоставите никаких аргументов для функции Add, то новый рабочий лист будет помещен перед активным рабочим листом.
Когда вы добавляете рабочий лист, он создается с именем по умолчанию, например «Лист4». Если вы хотите изменить имя, вы можете легко сделать это, используя свойство Name.
В следующем примере добавляется новый рабочий лист и изменяется имя на «Счета». Если лист с именем «Счета» уже существует, вы получите сообщение об ошибке.
В предыдущем примере вы добавляете листы по отношению к активному листу. Вы также можете указать точную позицию для размещения листа.
Для этого вам нужно указать, какой лист новый лист должен быть вставлен до или после. Следующий код показывает вам, как это сделать.
Удалить рабочий лист
Чтобы удалить лист, просто вызовите Delete.
Excel отобразит предупреждающее сообщение при удалении листа. Если вы хотите скрыть это сообщение, вы можете использовать код ниже:
Есть два аспекта, которые нужно учитывать при удалении таблиц.
Если вы попытаетесь получить доступ к рабочему листу после его удаления, вы получите ошибку «Subscript out of Range», которую мы видели в разделе «Доступ к рабочему листу».
Вторая проблема — когда вы назначаете переменную листа. Если вы попытаетесь использовать эту переменную после удаления листа, вы получите ошибку автоматизации, подобную этой:
Если вы используете кодовое имя рабочего листа, а не переменную, это приведет к сбою Excel, а не к ошибке автоматизации.
В следующем примере показано, как происходят ошибки автоматизации.
Если вы назначите переменную Worksheet действительному рабочему листу, он будет работать нормально.
Цикл по рабочим листам
Элемент «Worksheets» — это набор рабочих листов, принадлежащих рабочей книге. Вы можете просмотреть каждый лист в коллекции рабочих листов, используя циклы «For Each» или «For».
В следующем примере используется цикл For Each.
В следующем примере используется стандартный цикл For.
Вы видели, как получить доступ ко всем открытым рабочим книгам и как получить доступ ко всем рабочим листам в ThisWorkbook. Давайте сделаем еще один шаг вперед — узнаем, как получить доступ ко всем рабочим листам во всех открытых рабочих книгах.
Примечание. Если вы используете код, подобный этому, для записи на листы, то сначала сделайте резервную копию всего, так как в итоге вы можете записать неверные данные на все листы.
Использование коллекции листов
Рабочая книга имеет еще одну коллекцию, похожую на Worksheets под названием Sheets. Это иногда путает пользователей. Чтобы понять, в первую очередь, вам нужно знать о типе листа, который является диаграммой.
В Excel есть возможность создать лист, который является диаграммой. Для этого нужно:
Теперь у вас есть рабочая книга, в которой есть типовые листы и лист-диаграмма.
Ниже приведены два примера кода. Первый проходит через все листы в рабочей книге и печатает название листа и тип листа. Второй пример делает то же самое с коллекцией Worksheets.
Чтобы опробовать эти примеры, вы должны сначала добавить лист-диаграмму в свою книгу, чтобы увидеть разницу.
Если у вас нет листов диаграмм, то использование коллекции Sheets — то же самое, что использование коллекции WorkSheets.
Заключение
На этом мы завершаем статью о Worksheet VBA. Я надеюсь, что было полезным.
Три наиболее важных элемента Excel VBA — это рабочие книги, рабочие таблицы, диапазоны и ячейки.
Эти элементы будут использоваться практически во всем, что вы делаете. Понимание их сделает вашу жизнь намного проще и сделает изучение VBA увлекательнее.
Microsoft Excel
трюки • приёмы • решения
Объекты Microsoft Excel, которые позволяют работать с информацией
В большинстве примеров вводной части раздела «Офисные решения» мы рассматривали возможности VBA без связи с информацией, находящейся на рабочем листе. Лишь несколько ситуаций продемонстрировали синтаксические конструкции, которые позволяли извлекать и записывать данные в ячейки листов Microsoft Excel. В этой части раздела мы детально рассмотрим объекты, которые позволяют работать с информацией, содержащейся в рабочих книгах Microsoft Excel. Приводимые здесь примеры, являются фундаментом для более сложных разработок, рассматриваемых в последующих статьях.
Файл рабочей книги Excel представлен в объекте Workbook, который имеет большое количество свойств и методов. Справочная информация по ним присутствует как в электронной справке по VBA, так и в большом количестве изданий по данной теме. Мы не будем углубляться в чисто справочную информацию и во вводной части рассмотрим только те сведения, с которыми далее встретимся в приведенных примерах.
Так, свойство Worksheets объекта Workbook представляет семейство всех рабочих листов книги. И для обращения к конкретному листу книги с помощью этого свойства следует просто указать в качестве параметра номер листа, что выглядит так: Worksheets(номер листа). В качестве номера листа выступает просто его порядковый номер в книге Microsoft Excel. Другой вариант заключается в указании в качестве параметра названия листа: Worksheets («Название листа»). Одним из наиболее часто программируемых событий, связанных с книгой в целом, является событие Open, которое происходит при открытии рабочей книги. Так, если мы хотим, чтобы при открытии книги выполнялись определенные действия, следует расположить необходимый программный фрагмент внутри процедуры Workbook_Open. Программирование этого события будет рассматриваться в части приводимых далее примеров.
Следующим объектом в порядке иерархии после Workbook является объект Worksheet, представляющий рабочий лист. Из многообразия методов этого объекта широко используется Activate, который существует и для семейства листов Worksheets, о котором мы уже сказали выше. Например, если при работе на первом листе книги Microsoft Excel требуется активизировать второй лист, то синтаксис программной строки в процедуре (выполняться она может, например, по щелчку на кнопке) должен выглядеть следующим образом: Worksheets(2).Activate. Если же требуется активизировать лист, называемый Информация о фирмах, то следует написать в процедуре такую конструкцию: Worksheets(«Информация о фирмах»).Activate.
Пользователи Microsoft Excel знают, что это приложение предлагает сервис, связанный с защитой рабочих книг и составляющих их листов. Так, метод Protect (семейства Worksheets) обеспечивает защиту рабочего листа от внесения в него изменений. Для программной установки защиты с паролем (пароль указывается в параметре Password этого метода) листа с названием Сотрудники можно поступить следующим образом:
Здесь и далее символ «нижнего подчеркивания» является информацией для Microsoft Visual Basic о том, что следующая строка является продолжением текущей.
Каждая рабочая книга состоит из листов. Совокупность всех листов рабочей книги задается коллекцией Sheets. Основных типов листов в рабочей книге два – Worksheet и Chart, соответственно имеются две коллекции для каждого типа листов. На практике для этих коллекций чаще всего используется метод Add, добавляющий новый лист в книгу. Метод возвращает соответствующий объект в качестве результата. Добавленный лист становится активным. Свойство Count у всех коллекций показывает количество элементов 15 коллекции. Для Sheets оно равно количеству листов. Так, например, можно добавить в активную книгу новый лист перед последним листом, назвав его «Новый»:
Set NewSheet = Sheets.Add(before:=Sheets(Sheets.Count))
Ссылка на добавленный лист сохранена в переменной NewSheet класса Worksheet, которую далее в программе можно использовать для работы с этим листом.
Основные свойства и методы объекта Worksheet приведены в табл. 12.25 и 12.26.
Основные события объекта Worksheet следующие: Activate, Calculate, Change, Deactivate, SelectionChange.
Таблица 12.25
Основные свойства объекта Worksheet
Возвращает объект Range. Позволяет задать как отдельную ячейку, так и диапазоны ячеек. Свойство имеет параметры. Чаще всего в качестве значений параметра используются адрес ячейки, диапазон ячеек или выражение над диапазонами, содержащее операции объединения (запятая) и пересечения (пробел), например:
Возвращает объект Range. Обычно свойство используется для указания конкретной ячейки с помощью ее номера строки и столбца. Например: ActiveSheet.Cells(5, 1) – это ячейка А5 активного листа
Возвращают коллекции, содержащие все строки и столбцы рабочего листа. Для доступа к конкретной строке или столбцу нужно указать его номер:
Таблица 12.26
Основные методы объекта Worksheet
Активизирует рабочий лист
Удаляет рабочий лист
Создает копию листа, помещая ее перед или после указанного в параметре (Before, After) листа:
Перемещает рабочий лист в позицию, указанную параметром Before или After
Помещает содержимое буфера обмена на рабочий лист. Позицию вставки можно указать с помощью параметра Destination
Выполняет вычисления формул рабочего листа
Объект Range
Это основной объект, с которым приходится работать программисту. Объект является коллекцией ячеек электронной таблицы, которая может содержать как единственную ячейку таблицы, так и столбец или строку, некоторую связную и не связную прямоугольную область, а также объединение и пересечение всех подобных элементов. Для создания объекта чаще всего используются свойства Range, Cells, Rows, Columns, рассмотренные выше. Основные свойства объекта приведены в табл. 12.27.
Таблица 12.27
Основные свойства объекта Range
Значение ячейки. Если она пуста, то возвращается значение Empty (можно проверить, вызвав функцию IsEmpty). Если объект Range содержит более одной ячейки, то возвращается массив значений (можно проверить, вызвав функцию IsArray). Функции IsNumber,LsText позволяют определить тип значения, хранимого в ячейке. Это свойство по умолчанию, поэтому часто опускается. Например, вместо Range(«Al»). Value = 2 можно написать Rangc(«Al”) = 2
Возвращает строку текста, связанного с ячейкой. Имеет статус только для чтения
Формула в формате А1. Если формула присваивается диапазону, то относительные ссылки ведут себя как обычно. Например:
Формула в формате А1 с учетом языка пользователя (для неанглоязычных версий Excel). Например:
Формула в формате R1С1. Например:
Формула в формате R1C1 с учетом языка пользователя (для неанглоязычных версий Excel)
Возвращает объект Range, в состав которого входит заданный диапазон. Границами возвращаемого объекта являются пустые строки и столбцы или границы таблицы. Например, так можно выделить диапазон, содержащий ячейку A2:
Возвращает строку, задающую ссылку на объект Range. Вид возвращаемого значения определяют параметры свойства
Возвращают соответственно номер первого столбца или первой строки объекта Range
Возвращает объект Font (шрифт). Например:
With Worksheets(nHncTl «).Range(”B5»).Font
Если при записи макроса вы будете вставлять в ячейку какую-либо формулу, то Excel в тексте, скорее всего, использует свойство FormulaRlCl и вставленная формула покажется вам непонятной. Дело в том, что для ссылок на ячейки в Excel используются два стиля: А1 и R1C1. Обычно вы работаете со ссылками в стиле А1: ссылка состоит из имени столбца (обозначаются латинскими буквами) и номера строки. Признаком абсолютной ссылки является знак доллара перед именем строки или столбца. Макросы используют стиль R1C1. В этом случае после буквы R указывается номер строки ячейки, после буквы С – номер столбца. Для задания относительной ссылки в стиле R1C1 указывается смещение по отношению к активной ячейке (в квадратных скобках). Знак определяет направление смещения. Например, R[-1]C (относительная ссылка на ячейку, расположенную в предыдущей строке и в том же столбце), R[3]C[2] (относительная ссылка на ячейку, расположенную на три строки ниже и на два столбца правее), R2C2 (абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце), R[–1 ] (относительная ссылка на предыдущую строку), R (абсолютная ссылка на текущую строку).
Объект Range имеет около 80 методов. Все, что вы можете сделать с диапазоном ячеек в Excel, скорее всего реализовано с помощью метода объекта Range. Например, сортировка – это метод Sort, заполнение диапазона – метод AutoFill, выделение диапазона – метод Select и т.д. Большинство методов имеют параметры. Для изучения методов рекомендуется записать макрос, выполняющий нужную команду, и проанализировать полученный код. Описание методов можно найти в справочной системе MS Excel. Воспользуйтесь контекстной справкой (клавиша F1) или в редакторе Visual Basic откройте окно справки, в строке поиска наберите «Range Object Members» и перейдите по найденной гиперссылке.
Объект Selection
В MS Excel нет класса объектов Selection. В программе объект Selection мы получаем либо в результате работы метода Select, либо при вызове свойства Selection. Тип объекта может быть различным. Свойства и методы объекта Selection определяются типом выделенного объекта. Чаще всего объект Selection принадлежит классу Range, и тогда при работе с ним можно использовать все свойства и методы объектов класса Range.
