Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets ), строки (Rows ), столбцы (Columns ), диапазоны ячеек (Ranges ) и сама рабочая книга Excel (Workbook ) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.
Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.
В Excel VBA существует особый тип объектов – коллекция . Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.
Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks , который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.
В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).
| Объект | Описание |
|---|---|
| Application | Приложение Excel. |
| Workbooks | Коллекция всех открытых в данный момент рабочих книг в текущем приложении Excel. Доступ к какой-то конкретной рабочей книге может быть осуществлён через объект Workbooks при помощи числового индекса рабочей книги или её имени, например, Workbooks(1) или Workbooks(“Книга1”) . |
| Workbook | Объект Workbook
– это рабочая книга. Доступ к ней может быть выполнен через коллекцию Workbooks
при помощи числового индекса или имени рабочей книги (см. выше). Для доступа к активной в данный момент рабочей книге можно использовать ActiveWorkbook
.
Из объекта Workbook можно получить доступ к объекту Sheets , который является коллекцией всех листов рабочей книги (рабочие листы и диаграммы), а также к объекту Worksheets , который представляет из себя коллекцию всех рабочих листов книги Excel. |
| Sheets | Объект Sheets – это коллекция всех листов рабочей книги. Это могут быть как рабочие листы, так и диаграммы на отдельном листе. Доступ к отдельному листу из коллекции Sheets можно получить при помощи числового индекса листа или его имени, например, Sheets(1) или Sheets(“Лист1”) . |
| Worksheets | Объект Worksheets – это коллекция всех рабочих листов в рабочей книге (то есть, все листы, кроме диаграмм на отдельном листе). Доступ к отдельному рабочему листу из коллекции Worksheets можно получить при помощи числового индекса рабочего листа или его имени, например, Worksheets(1) или Worksheets(“Лист1”) . |
| Worksheet | Объект Worksheet
– это отдельный рабочий лист книги Excel. Доступ к нему можно получить при помощи числового индекса рабочего листа или его имени (см. выше).
Кроме этого Вы можете использовать ActiveSheet для доступа к активному в данный момент рабочему листу. Из объекта Worksheet можно получить доступ к объектам Rows и Columns , которые являются коллекцией объектов Range , ссылающихся на строки и столбцы рабочего листа. А также можно получить доступ к отдельной ячейке или к любому диапазону смежных ячеек на рабочем листе. |
| Rows | Объект Rows – это коллекция всех строк рабочего листа. Объект Range , состоящий из отдельной строки рабочего листа, может быть доступен по номеру этой строки, например, Rows(1) . |
| Columns | Объект Columns – это коллекция всех столбцов рабочего листа. Объект Range , состоящий из отдельного столбца рабочего листа, может быть доступен по номеру этого столбца, например, Columns(1) . |
| Range | Объект Range
– это любое количество смежных ячеек на рабочем листе. Это может быть одна ячейка или все ячейки листа.
Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells , например, Worksheet.Cells(1,1) . По-другому ссылку на диапазон можно записать, указав адреса начальной и конечной ячеек. Их можно записать через двоеточие или через запятую. Например, Worksheet.Range(“A1:B10”) или Worksheet.Range(“A1”, “B10”) или Worksheet.Range(Cells(1,1), Cells(10,2)) . Обратите внимание, если в адресе Range вторая ячейка не указана (например, Worksheet.Range(“A1”) или Worksheet.Range(Cells(1,1)) , то будет выбран диапазон, состоящий из единственной ячейки. |
Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:
Workbooks("Книга1").Worksheets("Лист1").Range("A1:B10")
В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set :
Dim DataWb As Workbook Set DataWb = Workbooks("Книга1.xlsx")
В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet , активный объект Range и так далее.
Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet , а на активный объект Range – как на Selection .
Если в коде VBA записана ссылка на рабочий лист, без указания к какой именно рабочей книге он относится, то Excel по умолчанию обращается к активной рабочей книге. Точно так же, если сослаться на диапазон, не указывая определённую рабочую книгу или лист, то Excel по умолчанию обратится к активному рабочему листу в активной рабочей книге.
Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:
Range("A1:B10")
Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:
Sub ActivateAndSelect() Workbooks("Книга2").Activate Worksheets("Лист2").Select Worksheets("Лист2").Range("A1:B10").Select Worksheets("Лист2").Range("A5").Activate End Sub
Методы объектов, в том числе использованные только что методы Activate или Select , далее будут рассмотрены более подробно.
Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name . Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:
Dim wbName As String wbName = ActiveWorkbook.Name
Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:
Workbooks("Книга1").Worksheets("Лист1")
Это возможно потому, что коллекция Worksheets является свойством объекта Workbook .
Некоторые свойства объекта доступны только для чтения, то есть их значения пользователь изменять не может. В то же время существуют свойства, которым можно присваивать различные значения. Например, чтобы изменить название активного листа на “Мой рабочий лист “, достаточно присвоить это имя свойству Name активного листа, вот так:
ActiveSheet.Name = "Мой рабочий лист"
Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate , Close , Save и ещё множество других.
Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:
ActiveWorkbook.Save
Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.
Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2 , а аргументу FileFormat – значение xlCSV :
ActiveWorkbook.SaveAs "Книга2", xlCSV
Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания “:= ” и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:
ActiveWorkbook.SaveAs Filename:="Книга2", :=xlCSV
В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2 .
Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each . В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet .
"Пролистываем поочерёдно все рабочие листы активной рабочей книги "и выводим окно сообщения с именем каждого рабочего листа Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox "Найден рабочий лист: " & wSheet.Name Next wSheet
В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.
В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial . Этот метод передаёт аргументу Paste значение xlPasteValues .
"Копируем диапазон ячеек из листа "Лист1" другой рабочей книги (с именем Data.xlsx) "и вставляем только значения на лист "Результаты" текущей рабочей книги (с именем CurrWb.xlsm) Dim dataWb As Workbook Set dataWb = Workbooks.Open("C:\Data") "Обратите внимание, что DataWb – это активная рабочая книга. "Следовательно, следующее действие выполняется с объектом Sheets в DataWb. Sheets("Лист1").Range("A1:B10").Copy "Вставляем значения, скопированные из диапазона ячеек, на рабочий лист "Результаты" "текущей рабочей книги. Обратите внимание, что рабочая книга CurrWb.xlsm не является "активной, поэтому должна быть указана в ссылке. Workbooks("CurrWb").Sheets("Результаты").Range("A1").PasteSpecial Paste:=xlPasteValues
Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet . Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set , при помощи которого объект Range присваивается переменной Col .
Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.
"С помощью цикла просматриваем значения в столбце A на листе "Лист2", "выполняем с каждым из них арифметические операции и записываем результат "в столбец A активного рабочего листа (Лист1) Dim i As Integer Dim Col As Range Dim dVal As Double "Присваиваем переменной Col столбец A рабочего листа "Лист2" Set Col = Sheets("Лист2").Columns("A") i = 1 "Просматриваем последовательно все ячейки столбца Col до тех пор "пока не встретится пустая ячейка Do Until IsEmpty(Col.Cells(i)) "Выполняем арифметические операции со значением текущей ячейки dVal = Col.Cells(i).Value * 3 - 1 "Следующая команда записывает результат в столбец A "активного листа. Нет необходимости указывать в ссылке имя листа, "так как это активный лист рабочей книги. Cells(i, 1).Value = dVal i = i + 1 Loop
Простые и сложные переменные
По наличию внутренней структуры, переменные могут быть простыми или сложными (составными).
Простые переменные не имеют внутренней структуры, доступной для адресации. Последняя оговорка важна потому, что для компилятора или процессора переменная может быть сколь угодно сложной, но конкретная система (язык) программирования скрывает от программиста её внутреннюю структуру, позволяя адресоваться только «в целом».
Сложные переменные программист создаёт для хранения данных, имеющих внутреннюю структуру. Соответственно, есть возможность обратиться напрямую к любому элементу. Самыми характерными примерами сложных типов являются массив (все элементы однотипные) и запись (элементы могут иметь разный тип).
Следует подчеркнуть относительность такого деления: для разных программ одна и та же переменная может иметь разную структуру. Например, компилятор различает в переменной вещественного типа 4 поля: знаки мантиссы и порядка, плюс их значения, но для программиста, компилирующего свою программу, вещественная переменная - единая ячейка памяти, хранящая вещественное число.
Объектная модель MS Excel по общим принципам идентична объектной модели MS Word. Эта модель также имеет иерархическую структуру, в корне которой находится объект Application (Excel.Application), через который обеспечивается доступ к любой коллекции или внутреннему объекту приложения MS Excel или к компонентам открытых рабочих книг. Обшая структура объектной модели MS Excel представлена на рис. 1.4.
Рис. 1.4. Объектная модель MS Excel
Как уже сказано, вершиной объектной модели MS Excel является объект Application, непосредственно включающий такие объекты и коллекции, как Selection - текущий выделенный объект, WorkBooks - коллекция открытых рабочих книг, коллекции различных элементов управления, диалоговых окон и другие свойства приложения MS Excel. Объект Selection имеет свойства текущего выделенного объекта, поэтому нет смысла рассматривать здесь структуру модели этого объекта. Если выделена ячейка, то Selection = Ячейка, если диаграмма, то Selection = Диаграмма. Когда выделена область ячеек, Selection = Коллекции столбцов и строк. Коллекция рабочих книг представляет собой список открытых рабочих книг, доступ к которым осуществляется по индексу. Каждый элемент такой коллекции представляет собой объект "рабочая книга". Панели управления, меню, кнопки, диалоги и другие объекты, предназначенные для управления приложением, собраны в соответствующие коллекции элементов управления.
Основным элементом объекта "рабочая книга" является коллекция рабочих листов. Элементом коллекции является отдельный рабочий лист, который может представлять собой обычный лист (в виде таблицы) или быть в виде диаграммы. В последнем случае он ничего, кроме диаграммы, содержать не может. Рассмотрим вариант листа в виде таблицы (рис. 1.5).
Рис. 1.5. Объектная модель листа рабочей книги
Основной объект рабочего листа, с которым приходится работать, - ячейка. Ячейка как объект сама обладает множеством свойств и объектов, входящих в нее. Из них наиболее важными и часто используемыми являются: текст, шрифт, стиль текста, границы, заливка. Чтобы получить доступ к ним, необходимо получить доступ к самой ячейке, а затем изменять ее свойства. Ячейки объединены в области ячеек Range. Свойства области ячеек во многом совпадают со свойствами самой ячейки, но есть и отличия, состоящие в задании координат и размеров области. Ячейки объединены в строки и столбцы. Объединения строк и столбцов представляют собой коллекции, доступ к которым производится по числовому индексу или по буквенному обозначению столбца. На рабочем листе могуг располагаться внешние объекты: рисунки, фрагменты документов Word, звуки, видеозаписи и другие объекты, которые объединены в коллекцию внешних OLE-объектов. Прорисовка или, точнее, воспроизведение этих объектов полностью выполняется внешними программами, зарегистрированными в системе как OLE-серверы. Доступ к таким объектам производится через элементы коллекции OLEObjects, а доступ к их свойствам возможен только через эти OLE-серверы. Приложение Excel обладает большим набором собственных графических объектов, которые можно разместить на рабочем листе. Мы можем использовать рисунки, надписи, геометрические фигуры, диаграммы, которые обычно объединены в коллекции. Например, коллекция ChartObjects содержит набор диаграмм, которые располагаются на рабочем или на отдельном листе. Каждая диаграмма, в свою очередь, также содержит набор объектов и коллекций. Рассмотрение свойств, состава и содержания диаграмм - отдельная тема этой книги (см. главу 14).
Рис. 1.6. Объектные модели диалогов Найти в Excel (а) и Найти и заменить в Word (6)
Чтобы убедиться в гибкости, универсальности и больших возможностях для программирования объектов MS Office, рассмотрим еще одну коллекцию объектов, присутствующую как в Word, так и в Excel. Это коллекция диалогов (диалоговых окон), которые пользователь обычно открывает нажатием той или иной кнопки или выбором команды меню. Она принадлежит объекту Application. В объектной модели все диалоги представлены в виде элементов коллекции Dialogs, доступ к которым обеспечивается через числовой индекс. Посредством параметров метода Show элемента коллекции происходят передача параметров в диалог и его выполнение - такова модель диалогов для приложений Excel (рис. 1.6, а), для приложений Word модель диалога несколько отличается. Отличие заключается в том, что в диалог Word параметры передаются через свойства объекта-элемента коллекции (рис. 1.6, б).
У объекта Item() наряду с типичными свойствами и методами есть присущие только ему свойства и методы. Например, у диалога Найти и заменить есть свойство Find, определяющее текст для поиска - до запуска диалога.
В Excel объект коллекции Dialogs несколько отличается от диалогов Word. Здесь рассмотрим только объектную модель коллекции диалогов для Excel в целом (рис. 1.7).
Рис. 1.7. Объектная модель коллекции диалогов Excel
На этом рисунке представлена только малая часть всевозможных диалогов коллекции - показаны диалоги, наиболее часто используемые программистами и пользователями. Обычно в диалог передаются значения, которые устанавливают значения элементов управления, а возвращаются значения True или False, в зависимости от результата действия пользователя.
Можно подвести итог: приложения из состава MS Office, например Word и Excel, представляют собой взаимосвязанные объекты и коллекции объектов. Каждый объект или коллекция включает в себя множество параметров и других объектов, коллекций. В свою очередь, сами объекты и коллекции имеют хозяина (Parent), в состав которого они входят. На вершине объектной модели находятся объекты Word.Application для текстового процессора Word и Excel.Application - для табличного прцессора Excel. Через эти объекты, в основном, и осуществляется связь этих приложений с внешними программами. Как все это работает, вы узнаете из следующих глав книги.
Аннотация: Лекция посвящена описанию объектной модели MS Excel и подробному описанию методов, свойств и событий Application.
Microsoft Office Excel - это популярные электронные таблицы. Обычно, программируя для этой программы, преследуют такие цели:
В общем виде программирование в Excel похоже на работу с Microsoft Word . Однако одно из главных отличий заключается в том, что в Excel рабочая область листа разбита на ячейки, каждая из которых имеет собственное имя. Имена ячеек могут быть двух видов.
Рис.
13.1.
Для выполнения большинства операций в MS Excel применяются следующие объекты.
Давайте начнем рассмотрение объектной модели MS Excel с объекта Application .
MsgBox Excel.Application.Name Листинг 13.1. Вывести имя приложения
После выполнения программы в окне сообщения отобразится имя приложения - в данном случае - Microsoft Excel . Очевидно, что свойство Name объекта Application возвращает имя приложения.
Теперь рассмотрим наиболее важные методы и свойства Application . Некоторые из них похожи на таковые в MS Word . Например, метод Quit точно так же, как в Word , закрывает приложение , свойство Visible отвечает за видимость окна программы и т.д.
Этот метод, вызываемый для объекта Application , позволяет пересчитать все открытия книги. Его же можно вызывать для отдельных книг (объект Workbook ) листов ( Worksheet ), ячеек и их диапазонов (Range ). Например, код из листинга 13.2 . позволяет пересчитать все открытые книги.
Application.Calculate Листинг 13.2. Пересчитать все открытые книги
13-02-Excel GoTo.xlsm - пример к п. 13.3.2.
Позволяет выделить любой диапазон ячеек в любой книге, причем, если книга не активна - она будет активирована. Так же метод может запускать макросы Microsoft Excel.
Полный вызов метода выглядит так:
Goto(Reference, Scroll)
В качестве параметра Reference используется ссылка на ячейку или диапазон ячеек , которые должны быть выделены после вызова метода. Так же здесь может быть использовано имя макроса - тогда он будет запущен.
Параметр Scroll отвечает за "перемотку" листа Excel к выделенным ячейкам - так, чтобы левый верхний угол выделения совпадал бы с левым верхним углом отображаемой области листа. Если Scroll установлен в True - лист перематывается, если в False - нет.
Например, такой вызов (листинг 13.3 .) позволяет выделить ячейку H500 на активном листе.
Application.Goto _ Reference:=ActiveSheet.Range("H500"), _ Scroll:=True Листинг 13.3. Выделить ячейку H500
Как видите, обращение к активному листу очень напоминает обращение к активному документу в MS Word. Обратите внимание на то, что мы используем полное обращение к методу - Application.GoTo - как вы знаете, обычно свойства и методы объекта Application можно использовать в коде, не указывая этого объекта. Однако, если в этом случае не указать Application , то вместо метода GoTo программа попытается выполнить оператор безусловного перехода GoTo .
13-03-Excel SendKeys.xlsm - пример к п. 13.3.3.
Очень интересный метод - позволяет передавать в активное окно приложения нажатия клавиш. Полный вызов метода выглядит так:
SendKeys(Keys, Wait)
Параметр Keys позволяет задавать клавиши, нажатия которых будут переданы приложению. Метод поддерживает эмуляцию как алфавитно-цифровых, так и управляющих клавиш , для которых применяется специальная кодировка. Алфавитно-цифровые клавиши указываются при вызове в своем обычном виде Например, для передачи символа "Ф" нужно указать его при вызове метода и т.д.
Чтобы передать приложению нажатия клавиши Backspace - используйте код {BS} . Для передачи нажатия кнопки Enter используйте значок ~ (
Sub MainProcedure()
Call GetProblemSize
Sub GetProblemSize()
ReDim Visited(Ncities)
ReDim Route(Ncities + 1)
Sub Initialize()
Dim I As Integer
Route(Ncities + 1) = 1
Visited(1) = True
For I = 2 To Ncities
Visited(I) = False
Синтаксис процедуры пользователя:
Sub ИмяПроцедуры (Параметры)
<Тело процедуры>
Элементы списка параметров имеют следующий вид: ИмяЭлемента As ТипДанных
7. Типы подпрограмм и их определения: определение и виды процедур. Примеры процедур разных видов. Логические фрагменты кода, которые выполняют определенную задачу, называются подпрограммами. В вба подпрограммами называют макросы, процедуры и даже функции. Функция – это подпрограмма, которая действует в пределах своего блока и возвращает только одно значение. Функции: 1)ф. пользователя 2) ф. модулей классов. Функция имеет следующий синтаксис:
Function Имя функции(список параметров) As ТипДанных
<тело функции>
Тип данных для функции предназначен для определения типа и возвращения функцией зн-я
Чтобы создать функцию пользователя, надо: 1. если нет модуля Insert/Module 2. Insert/Procedure 3. в открывшемся окне выбрать переключатель “Function”, в поле Name ввести имя функции («Доход») 4. в этом же окне установить переключатель «общие», чтобы был тип – Public 5. Ок. Откроется окно редактора. Ввести параметры функции и код.
Function Doxod (procent as double, platezh as variant, god as variant) as double
Dim i, j, n as integer, s as double
n=platezh.rows.count
s=s+platezh(i)/(1+procent)^((god(i)-god(1))/365)
Не закрывая окна View/Object Browser. Откроется окно просмотра объектов. В левом верхнем углу выбрать VBAProject и в окне Классы отобрать элементы текущего проекта. Выбрать модуль, в котором ваша функция. Затем в окне Компоненты отобрать все элементы, в том числе и функцию Доход. Щелкнуть правой кнопкой мыши на Доход и выбрать значение Свойства. Откроется окно «Параметры компонента», в нем ввести описание(назначение) функции. Закрыть окно и после этого ваша функция попадет в библиотеку стандартных функций Excel.
8. Объявление переменных. Объявление переменных в модулях и процедурах. Область действия переменных и процедур. Пример передачи в процедуру аргументов.
Dim I As Integer, j As Integer, k As Integer
Чтобы не забывать объявить переменные Tools/Options/ вкл Editor, где установить флажок Require Variable Declaration.
Option Explicit – общая область. Этот оператор будет контролировать описание переменных. Существует очень важный тип переменных, которого в VB не было. Это переменные объекта. Dim A As Object. Частный случай: Dim A As Range. Например, мы хотим обратиться к диапазону ячеек в течение процедуры. D
Dim SRange As Range
Set SRange=ActiveWorkBook.WorkSheets(“Продажи”).
Range(“SalesRange”)
SRange.Font.Size=14
Ключевое слово Set используется только при присвоении значения объектной переменной.
Для переменных определяется область видимости. Переменные могут быть: глобальные (для объявления используется Public) и локальные (используется оператор Private, Dim). Переменная типа Public – переменная уровня модуля, Dim – уровня процедуры. Переменная, определяемая в модуле оператором Dim, может быть переопределена этим же оператором внутри процедуры, принадлежащей этому модулю.
Передача в процедуру аргументов. Можно передавать аргументы (имя, фамилию) из процедуры Main в процедуру DisplayName. Тогда процедура Main называется главной, а DisplayName – вызываемой. В этом случае переменная FirstName и LastName не объявляются переменными уровня модуля, а объявляются как локальные переменные процедуры Main.
Sub Main ()
Dim FirstName As String, LastName As String, I As Integer
FirstName=Range(“Names”).cells(i, 1)
LastName= Range(“Names”).cells(i, 2)
Call DisplayName
Sub DisplayName
Dim FulllName As String
FulllName= FirstName+ LastName
MsgBox “Полное имя сотрудника”_&_ FulllName
9. Встроенное диалоговое окно сообщения. Пример функциии оператора MsgBox . Существует несколько типов диалоговых окон, которые необходимы для поддержания в программе интерактивного режима работы конечного пользователя (вывод сообщений пользователю, прием и интерпритация указаний, введенных пользователем и т.д.). Окно сообщений обозначается MessageBox (MsgBox) и окно ввода InputBox. Их можно рассматривать как фукнции и как оператор. Окно MsgBox- окно сообщения. Оно не требует проектирования и вызывается из программы командой MsgBoх и создается с помощью функции MsgBox(). Она имеет следующий синтаксис: MsgBox (Promt [, buttons] [,title,helpFile], ), где promt - обязательный параметр, эта строка в окне сообщения. Ее максимальная длина 1024 символа. Параметр Buttons не обязателен, его значение - целое число, равное сумме значений, определяющих наличие кнопок в окне сообщения. По умолчанию равно 0. Параметр title- заголовок окна сообщений. Help file – необязательный, ссылка на файл в справочной системе и на конкретное место в этом файле. пример
В зависимости от выбора кнопок окна MsgBox диалоговое окно возвращает одно из значений.
10. Встроенное диалоговое окно ввода. Пример функцииInputBox. Существует несколько типов диалоговых окон, которые необходимы для поддержания в программе интерактивного режима работы конечного пользователя (вывод сообщений пользователю, прием и интерпритация указаний, введенных пользователем и т.д.). Окно ввода информации InputBox требуется только как функция. Часто необходимо осуществить не только набор действия, но и ввести определенную информацию, которая будет восприниматься программой. Для этого используется функция InputBox. Синтаксис: InputBox (ptomt, , , , , context])
Promt – обязательная строка, которая выдается в окне сообщения, 1024 символа Title – необязательный, заголовок окна сообщения Default - строка в текстовом поле, если его нет, то строка пуста Xpos,Ypos- позиция левого верхнего угла ввода на экран Helpfile- ссылка на файл справочной информации. В этом окне всегда стоят по умолчанию две кнопки: ОК и Саncel. Пример:
InputBox("Введите цену для сравнения", "окно для ввода критерия")
13. Основные свойства и методы объекта Range. Примерыпрограммных кодов . Объект Range одновременно является и объектом и коллекцией. Рассмотрим наиболее важные свойства и методы объекта Range: 1. Свойство Address – возвращает адрес диапазона в виде строки. «В2:Р4» А=Range(“Sales”).Address “В2:Р4”
Range (“A1:A10”).Cells(3) ‘A3
Range (“A1:D10”).Cells(3, 4) ‘D3
Range (“A1”). Offset(3, 4) – E4
4. Font – выступает как в роли объекта, так и в роли свойства. В данный момент выступает как свойство, а как объект имеет свойства: Size, Name, Bold, Italic.
5. Horizontal Alignment – горизонтальное выравнивание ячеек в диапазоне. Свойства: xlCenter – по центру; xlLigh – по правому краю; xlLeft – по левому краю.
7. Name – возвращает имя диапазона. Это свойство позволяет задать имя диапазона в программном коде.
Range (“A1:D10”).Name=”Sales”
8. Value – возвращает значение в ячейку диапазона(используется относительно только одной ячейки диапазона).
Range (“A5”). Value=«Отчет о командировке»
Методы диапазона: Clear – удаляет содержимое и форматирование диапазона ячеек. ClearContents – удаляет только содержимое. Copy – копирует содержимое одного диапазона в другой. В этом случае он используется с единственным аргументом CopyDestination. C его помощью указывается, куда вы копируете.
Range(“B4:G25”). CopyDestination:=Range(“E4:F25”). Копируются формулы и содержимое. В случае, когда надо скопировать только числовые значения, а не формулы, используется метод PastSpecial. Сначала вы копируете в буфер обмена, затем переносите в диапазон. 1)Range(“B4:G25”).Copy 2)Range(“E4:F25”).PastSpecial Paste:=xlPasteValue
Select – выделение диапазона.
Sort – Применяется для сортировки диапазона ячеек. Key1 – по какому столбцу будет сортировка в диапазоне. Order1 – порядок сортировки(по возрастанию, по убыванию). Header – «да», «нет» (Если «да», то заголовок в сортировке участия не принимает, если «нет», участвует). Range(“A1:F25”).Sort
Key1:=Range(“C2”)
Order1:=xlAscending
Правила использования объектов Excel в програмном коде. Примеры программных кодов с их использованием.
14. Способы указания диапазонов в вба. Примеры программных кодов с указанием диапазонов. В процессе составления программного кода нужно уметь правильно использовать ссылку на диапазон. Рассмотрим следующие способы: 1. Использование адреса Range(“A1:B4”). 2. Применение имени диапазона Range(“Sales”). 3. Назначение переменной с именем диапазона NSales=Range(“Информация о продажах”).Name. 4.Использование свойства Cells Range(“A1:A10”).Cells(3) – A3
Range(“A1:D10”).Cells(3, 4) – D3
5.Настройка свойства Offset. Свойство имеет 2 аргумента. Когда указывается Offset для диапазона, то в роли диапазона выступает адрес только одной ячейки. Range(“A5”).Offset(3, 4) – E8
6. Указание левого верхнего и нижнего правого угла диапазона. Range(Range(“C1”), Range(“D10”)).
7.Применение свойства End. Для выделения диапазона относительно которого известен только левый верхний угол, можно воспользоваться свойством End. Оно указывает на нижний правый угол диапазона.
With Range(“A1”)
Range(.Cells(1, 1),.End(xlRight).End(xlDown)).Select
With Range(“A1”)
Range(.Offset(1, 1),.End(xlRight)).Name=”Sales”
Range(.Offset(2, 1),.End(xlDown)).Name=”Region”
Range(.Offset(2, 2),.End(xlRight)).End(xlDown).Name=”NameSales”
Работа с массивами. Оператор Option Base. Динамическая модель индексации и оператор ReDim. Примеры использования этих операторов. Функция управления массивами Array. Пример использования.
По аналогии с работой с информацией в списках Excel, массивы тоже представляют собой списки, в которых каждый элемент имеет свой индекс. Элементы массива воспринимаются программой в виде обычных переменных, но представленных в виде индексированного списка. В вба массивы используются для обработки списков, поскольку массивами управлять в программном режиме гораздо проще, чем списками. Правила работы с массивами. Option Base – определяет нижнюю границу изменения индекса в массиве. Например, Option Base1 – нижняя граница – 1. Эту строку в программном коде пишут в общей области модуля следом за оператором Option Explicit.
В большинстве экономических задач при написании программного кода невозможно заранее узнать количество элементов. Для этого существует возможность вначале программного кода не указывать при объявлении массива точного размера. (Dim ProdCode () As Integer, NProducts As
Integer.) Затем в теле процедуры, когда уже известно необходимое количество элементов массива, можно воспользоваться оператором ReDim, который выделяет для массива строго необходимый объем памяти. With ActiveWorkBook.WorkShits(«Продажи»).Range(“A3”)
NProducts=Range(.Offset(1,0),.End(xlDown)).
ReDim ProdCode(NProducts),UnitPrise(NProducts)
For i=1 to NProducts
ProdCode(i)=.Offset(i,0)
UnitPrise(i)=.Offset(i,1)
Оператор ReDim относительно конкретного массива может использоваться в программном коде столько раз, сколько необходимо. Единственной проблемой является то, что при таком использовании массива, все, что было в массиве, теряется. Чтобы этого избежать, нужно в записи оператора записать ключевое слово Preserve. (ReDim Preserve Sum(NS)). Обычно продолжением данного программного кода является размещение данных, взятых из ст. А и В списка в массивах ProdCode и UnitPrice. Для переноса данных из столбцов списка в массив, нужно организовать цикл. Если нужно перенести данные в столбцы списка, размещенного на рабочем листе:
(NFound – количество найденных записей)
For i=1 to NFound
With Range(“E3”)
Offset(j, 0)=ProdCodeFound(j) ‘код товара
Offset(j, 1)=Quontity(j) "количество товаров
Offset(j, 2)=DollarsTotal(j) ‘стоимость продукции
Рассмотрим конструкцию Array. Рассмотрим работу этой функции на примере. Данная функция используется для заполнения массива. Ее применении е рассмотрим в программе «Ипотечный кредит».
Sub Array Function ()
Dim Days As Variant
Days=Array(«Пн», «Вт», «Ср», «Чт», «Пт», «Сб», «Вс»)
MsgBox “Первый день недели: ” & Days(1)
Ключевое слово Array, после которого указывается список в скоках, используется для заполнения значениями переменной Days. Эта переменная – обычный массив, но в операторе Dim она указывается как обычная переменная типа Variant, а VBA самостоятельно с использованием функции Array определяет эту переменную как массив.
16. Модульная структура приложения на примере программного кода задачи, определяющий оптимальный маршрут перемещения дистрибьютора фирмы. Примериспользования переменных уровня модуля . При создании больших процедур возникают такие сложности, как сложность изучения, отладки, повторного использования. Намного предпочтительнее создавать модульные приложения, которые представляют собой набор относительно небольших процедур, каждая из которых выполняет небольшую подзадачу. Создается главная процедура MainProcedure, а из нее с помощью оператора Call вызываются другие процедуры.
Sub MainProcedure()
В качестве примера рассмотрим программный код задачи, определяющий оптимальный маршрут перемещения.
Dim Ncities As Integer, Visited() As Boolean, Route() As Integer, TotDist As Integer
Sub GenDistances()
Dim I As Integer, j As Integer, Ncities As Integer
With Range("DistMatrix")
Ncities = .Rows.Count
For I = 1 To Ncities - 1
For j = I + 1 To Ncities
Cells(I, j) = Int(Rnd * 100) + 1
For I = 2 To Ncities
For j = 1 To I - 1
Cells(I, j) = .Cells(j, I)
Sub MainProcedure()
Call GetProblemSize
Call PerformHeuristic
Call DisplayResults
Sub GetProblemSize()
Ncities = Range("DistMatrix").Rows.Count
ReDim Visited(Ncities)
ReDim Route(Ncities + 1)
Sub Initialize()
Dim I As Integer
Route(Ncities + 1) = 1
Visited(1) = True
For I = 2 To Ncities
Visited(I) = False
Sub PerformHeuristic()
Dim Step As Integer, I As Integer, NowAt As Integer, NextAt As Integer, MinDist As Integer
For Step = 2 To Ncities
For I = 2 To Ncities
If I <> NowAt And Visited(I) = False Then
If Range("DistMatrix").Cells(NowAt, I) < MinDist Then
MinDist = Range("DistMatrix").Cells(NowAt, NextAt)
Route(Step) = NextAt
Visited(NextAt) = True
TotDist = TotDist + MinDist
TotDist = TotDist + Range("DistMatrix"). Cells(NowAt, 1)
Sub DisplayResults()
Dim Step As Integer
For Step = 1 To Ncities + 1
Range("B19").Offset(Step, 0) = Route(Step)
MsgBox "Общее растояние:" & TotDist, vbInformation, "общее расстояние"
Использование переменных уровня модуля. Рассмотрим программу, которая иллюстрирует использование переменных уровня модуля.
На рабочем листе Excel создадим список и дадим ему имя – Name.
Dim FirstName As String, LastName As String
Dim I As Integer
FirstName=Range(“Name”).Cells(i, 1)
LastName=Range(“Name”).Cells(i, 2)
Call DisplayName
Sub DisplayName ()
Dim FullName As String
FullName=FirstName+LastName
MsgBox “Полное имя сотрудника: ” & FullName
17. Разраотка пользовательского интерфейса: форма как пользовательское окно диалога. Разработка формы на примере формы «Сведения о товарах». Отображение пользовательской формы. Пользовательское окно диалога – это форма, содержащая элементы управления, включая командные кнопки, переключатели, текстовые поля и другие. Оно обеспечивает пользователю ввод информации, требуемой для работы приложения.Рассмотрим типичный пример формы, которая часто встречается в приложениях.
В форме присутствуют 3 обработчика событий:
1. UserForm_Initialize
2. OkButton_Click
3. CanselButton_Click
В этом приложении при открытии формы нужно добиться следующего поведения: переключатели Москва и Поезд должны быть активны, флажок Скоропортящийся установлен, а Хрупкий – сброшен, список CostumerList должен быть заполнен исходными данными. При работе со списком, его можно заполнить разными способами. Например, заполнить его данными, расположенными в ячейках рабочего листа. Для этого зоздать диапазон на рабочем листе, дать ему имя(Name - Costumer). При заполнении списка воспользоваться AddItem. Перед тем, как писать код для события Initialize, рассмотрим свойства элементов управления: пользовательская форма – Ипотека; текстовое поле – ProductBox; переключатели – MoscowOption, VoronegOption, TrainOption, TruckOption; флажки – PerishBox, FragilBox; список – CostumersList.
Dim cell As Variant
ProductBox = " "
MoscowOptions.Value = True
TrainOptions.Value = True
PerishBox = True
FragilBox = False
Существует другой способ заполнения списка. Свойство RowSource, откроется окно, из которого поочереди надо внести элементы.
Отображение формы. В отличии от VB в VBA форма в момент запуска приложения автоматически на экране не появляется. Для вызова формы на рабочий лист нужно применить метод Show. Для этого на рабочем листе создать кнопку, которая выводит форму на рабочий лист, и написать код:
Sub Кнопка1_Щелкнуть ()
Ипотека. Show
18. Обработка событий формы: создание кода обработки событий UserForm_Initialize, CanselButton_Click на примере обработки этих событий для пользовательской формы «Сведения о товарах».
1. UserForm_Initialize
2. OkButton_Click
3. CanselButton_Click
1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».
Код обработчика UserForm_Initialize.
В этом приложении при открытии формы нужно добиться следующего поведения: переключатели Москва и Поезд должны быть активны, флажок Скоропортящийся установлен, а Хрупкий – сброшен, список CostumersList должен быть заполнен исходными данными. Для заполнения списка на рабочем листе создадим диапазон и дадим ему имя Costumers. В программном коде воспользуемся методом AddItem. Перед тем, как писать код для события Initialize, рассмотрим некоторые свойства элементов управления: пользовательская форма – Ипотека; текстовое поле – ProductBox; переключатели – MoscowOption, VoronegOption, TrainOption, TruckOption; флажки – PerishBox, FragilBox; список – CostumersList.
Private Sub UserForm_Initialize()
Dim cell As Variant
ProductBox = " "
MoscowOptions.Value = True
TrainOptions.Value = True
PerishBox = True
FragilBox = False
For Each cell In Range("Customers")
CustomersList.AddItem cell.Value
Для кнопки «отмена» запишем следующий код:
Private Sub CanselButton_Click ()
Unload.Me – этот метод выгружает форму из оперативной памяти и убирает ее с экрана.
19. Обработка событий формы: создание кода обработки события OkButton_Clickна примере обработки этого события для пользовательской формы «Сведения о товарах». Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик.
Рассмотрим форму «Сведения о товарах». В ней присутствуют 3 обработчика событий:
1. UserForm_Initialize
2. OkButton_Click
3. CanselButton_Click
1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».
Код процедуры OkButton_Click. Обычно данный обработчик событий используется для сохранения данный, введенных в элементы управления. Как правило, введенная информация в элементы управления заносится в общедоступные переменные, которые определены в модуле, далее эти переменные используются в программных кодах модуля. ProductСode – код, введенный в поле; Region – пункт отправления; Shipping – транспорт; IsPerish – скоропортящийся; IsFragil – хрупкий; Customers – смисок.
Dim ProductCode As Integer, Region As String, Shipping As String, IsPerish As Boolean, IsFragile As Boolean, Customers As String
Private Sub OkButton_Click()
If .Value = " " or not IsNewmeric(.Value) Then
MsgBox "Код товара не введен или не числовой"
ProductCode=ProductBox. Value
If ProductCode < 1 Or ProductCode > 1000 Then
MsgBox "Код товара должен быть в диапазоне от 1 до 1000"
SetFocus ‘ставит курсор в поле
Exit Sub ‘выход из процедуры
If TrainOption.Value = True Then
Shipping = "Poezd"
Shipping = "Gruzovik"
If MoscowOption.Value = True Then
Region = "Moscow"
Region = "Voroneg"
IsPerish = PerishBox.Value
IsFragile = FragilBox.Value
With CostomerList ‘работа со списком
If .ListIndex <> -1 Then
Customers = CustomersList.Value
MsgBox "Элемент в списке не выбран"
20. Структура принятия решения If-Then-Else
Условный оператор If-Then-Else изменяет порядок выполнения про-граммы в зависимости от результатов проверки некоторого условия.
Sub LookUpPrice()
ReDim ProdCode(Nproducts)
ReDim UnitPrice(Nproducts)
For i = 1 To Nproducts
ProdCode(i) = .Offset(i, 0)
UnitPrice(i) = .Offset(i, 1)
ReguestedCode = InputBox("Введите код товара (большая латинская буква и 4 цифры)")
Found = False
For i = 1 To Nproducts
If ProdCode(i) = ReguestedCode Then
Found = True
ReguestedPrice = UnitPrice(i)
Exit For
If Found Then
MsgBox "Товар с кодом" & ReguestedCode & " стоит " & Format(ReguestedPrice, "0,00р."), vbInformation, "Товар не найден"
MsgBox "Товара с кодом " & ReguestedCode & " нет в списке ", vbInformation, "Товар не найден”
21. Дополнительное условие ElseIf
Средствами структуры принятия решенья If-Then-Else можно орга-низовать выполнение операторов в зависимости от соблюдения опреде-ленного условия. Рассмотрим другой вариант ее использования, на этот раз с ключевым словом ElseIf. Программа Proc31 отображает запрос на ввод пароля. Если пароль введен правильно, программа предоставляет пользо-вателю определенные возможности работы с рабочей книгой и сообщает ему об этом.
Sub Proc31_IfThenElseIf ()
Dim Password As String, ws As WorkSheet
Password = GetPassword
If Password = “level1” Then
For Each ws In ActiveWorkbook.WorkSheets
ws.Visible = True
MsgBox “Вы получили доступ ко всем листам книги.”
ElseIf Password = “level2” Then
ActiveWorkbook.Worksneets (1). Unprotect
MsgBox “Вы получили доступ только к первому листу рабочей книги.”
ElseIf Password = “level3” Then
ActiveWorkbook.Worksneets (1). Visible = True
MsgBox “Вы получили доступ только для чтения содержимого первого листа рабочей книги.”
MsgBox “Пароль не корректен. Повторите ввод пароля.”
Function GetPassword ()
В Proc31 ключевое слово Elself с последующим условием использу-ется дважды. Новое условие вносит в порядок выполнения команд допол-нительные изменения, если проверка первого условия закончилась неуда-чей. В Proc31 первое условие указано сразу за ключевым словом If. В нем проверяется равенство переменной Password строке ”levell”. Если пере-менная не равна строке, VBA переходит к первому ключевому слову Elself, где проверяет следующее условие – равенство переменной Password строке “level2”. Начиная с этого момента, программа использует только новое ус-ловие, “забывая” о старом. Если равенства опять нет, управление перехо-дит к следующему ключевому слову Elself и т. д.
В строке 3 процедуры Proc31 вызывается функция GetPassword, в ко-торой использованы новые элементы. Обратимся к ней еще раз.
Function GetPassword ()
GetPassword = Lcase (InputBox(“Enter Password:”.” Password”))
Здесь во второй строке заданы обращения к двум встроенным функ-циям VBA-Lcase и InputBox. Первая преобразует все буквы переданной в нее строки в строчные. Вторая выводит на экран диалоговое окно с запро-сом на ввод данных пользователем. У этой функции есть несколько необя-зательных аргументов, из которых нас интересуют только первые два. Один из них – строка, отображаемая в окне над полем для ввода. Второй содержит строку заголовка диалогового окна. При вызове функции Input-Box на экране появляется диалоговое окно с заданными заголовком и тек-стом приглашением, кроме того оно содержит пустое поле, в котором пользователь может ввести данные. Значение, возвращаемое InputBox, за-висит от того, какую кнопку щелкнет пользователь. Если это – ОК, воз-вращается введенная строка, если – Отмена (Cancel), то пустая. В нашем примере буквы этой строки передаются в функцию Lcase, которая преоб-разует их в строчные. В блоке, начинающемся сразу за ключевым словом If, помещен цикл For-Each-Next (он подробно описан ниже в разделе "Управляющая структура For-Each-Next”). В этом цикле перебираются все листы активной рабочей книги, с каждым из которых выполняются два действия – свойству Visible присваивается значение True и вызывается ме-тод Unprotect. Первое действие позволяет пользователю видеть лист, вто-рое – снимает с листа защиту паролем. У метода Unprotect есть один аргу-мент – строка с паролем. Но в данном случае пароль для защиты листов мы не использовали, поэтому Unprotect вызван без аргументов. В других бло-ках программы свойство Visible и метод Unprotect вызываются выборочно, в зависимости от введенного пароля.
22. Управляющая структура For-Next
Она позволяет выполнять несколько команд заданное число раз.
Sub LookUpPrice()
Dim ProdCode() As String, UnitPrice() As Currency, ReguestedPrice As Currency, Nproducts As Integer, i As Integer, Found As Boolean, ReguestedCode As String
With ActiveWorkbook.Worksheets("Коды товаров”).Range("A3")
Nproducts = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
ReDim ProdCode(Nproducts)
ReDim UnitPrice(Nproducts)
For i = 1 To Nproducts
ProdCode(i) = .Offset(i, 0)
UnitPrice(i) = .Offset(i, 1)
End With
ReguestedCode = InputBox("Введите код товара (большая латинская буква и 4 цифры)")
For i = 1 To Nproducts
If ProdCode(i) = ReguestedCode Then
ReguestedPrice = UnitPrice(i)
MsgBox "Товар с кодом" & ReguestedCode & " стоит " & Format(ReguestedPrice, "0,00р."), vbInformation, "Товар не найден"
MsgBox "Товара с кодом " & ReguestedCode & " нет в списке ", vbInformation, "Товар не найден”
23. Управляющая структура While-Wend
Действие ее подобно действию For-Next, но группа операторов вы-полняется не заданное число раз, а до соблюдения определенного условия. В программе Proc34 инструкция While-Wend использована для выделения определенного значения из последовательности случайных чисел.
Sub Proc34_WhileWend ()
Dim LotteryEntry As Integer
LotteryEntry = 0
While LotteryEntry <> 7
LotteryEntry = Int (10*Rnd ())
MsgBox “Ваше число равно “& LotteryEntry &”. Вы выиграли!!”
Программа Proc34 гарантирует, что в информационном окне всегда отображается заданное число. Цикл While-Wend выполняется, пока значе-ние переменной LotteryEntry не станет равным 7 (в условии использован оператор неравенства <>). При каждом выполнении цикла этой перемен-ной присваивается случайное значение от 1 до 9, а затем с помощью функ-ции VBA Beep, подается звуковой сигнал через внутренний динамик ком-пьютера. При запуске этой программы несколько раз, можно услышать разное количество сигналов, в зависимости от того, на каком шаге генера-тор случайных чисел вернет число 7.
Рассмотрим структуру цикла While-Wend подробнее.
While Ключевое слово, начало структуры While-Wend
LotteryEntry <> 7 Условие, определяющее, будет или нет вы-полнен цикл. Если оно соблюдено, цикл вы-полняется, если нет – управление передается оператору, стоящему перед ключевым сло-вом Wend
LotteryEntry=Int(10*Rnd()) Первый оператор тела
Beep Второй оператор тела
Wend Ключевое слово, отмечающее конец струк-туры While-Wend
Введение в систему программирования VBA. Объектная модель Excel, основные объекты Е. Краткая их характеристика.
VB и VBA не одно и то же. VB представляет собой язык для разработки программного обеспечения, которое используется отдельно от MS Office. VBA входит в состав пакета MS Office. Устанавливая его, вы получаете доступ к VBA. Он необходим для управления данными MS Office.
Язык VBA поддерживает все базовые элементы любого другого языка программ.: 1)переменные 2)массивы 3)подпрограммы 4)управляющие структуры 5)возможность создания пользоват. типов данных и т.д.
VBA явл. языком визуального и событийно управляемого программирования. В нем есть возможность создания форм с управляющими элементами, написания процедур, обрабатывающих события. VBA позволяет работать с огромным набором объектов. VBA поддерживается такими приложениями, как Access, PowerPoint, Word и др. В каждом из приложений свой набор объектов. В Access:формы, запросы, отчеты, таблицы; в Word:абзац, сноска; в PowerPoint слайд. Каждая программа поддерживает свою версию VBA.
VBA отличается от других языков программирования тем, что предоставляет возможность непосредственно работать со всеми объектами MS Office. Он имеет графическую инструментальную среду, позволяющую создать экранные формы и управляющие элементы.
Все элементы Excel – объекты, с которыми приходится работать в программе. Чаще всего используются такие объекты, как рабочие книги WorkBooks, рабочие листы WorkSheets, диапазоны Range. Объект WorkSheets имеет свойство Name, только объект Range не имеет ярко выраженной коллекции. Выступает как коллекция и как объект. Чаще исп. В программном коде, обладает огромными возможностями.
2. Объектная модель Excel: понятие объекта, метода, свойства, события.
Объектная модель Excel является основой структуры программы. Без создания иерархической структуры объектов программы, нельзя использовать VBA в Еexcel.
Все элементы Excel – объекты, с которыми приходится работать в программе. Чаще всего используются такие объекты, как рабочие книги WorkBooks, рабочие листы WorkSheets, диапазоны ячеек Range ckarts.
Свойства определяют внешний вид и состояние объекта. Для определения свойства его нужно указать справа от имени объекта через точку. Range(“A1”).Value – свойство описывает содержимое ячейки А1. Существует 2 вида опереций при работе со свойствами: 1)присваивание свойству значения. Объект.Свойство=<значение>
Range(“A1”).Value= «Отчет о командировке». 2)полученное свойство присваивается переменной. Переменная=Объект.Свойство
Var= Range(“A1”).Value
Методы объекта открывают задачи, которые может выполнять объект. Объект.Метод – когда у метода нет параметров. Range(“A1”).CleanContenst – очищает диапазон, но сохраняет форматирование. Если метод имеет аргументы, то они перечисляются через, после названия метода.
Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик. Суть программирования в VBA заключается в 2 понятиях: событие и отклик на него.
Свойства и методы могут быть связаны между собой: выполнение метода приводит к изменению свойства объекта, а изменение некоторого свойства может вызвать наступление события.
3. редактор VB. Окно проекта. Окно редактирования кода. Окно редактирования формы. Панель элементов. Окно свойств. Окно просмотра объектов. Окна Immediate, Watch. Редактор VB=Visual Basic Editor. Редактора VBA не бывает! Для запуска этого редактора нужно открыть приложение Excel и выполнить команду Сервис/Макрос/Редактор VB или Alt+F11. Чтобы вернуться в Excel, нужно повторно нажать Alt+F11. В открывшемся окне VBA: строка заголовка, меню, панели инструментов и несколько окон.
По умолчанию в верхнем левом углу находится окно проекта . Вывести его можно командой View/Project Explorer. Это окно содержит список всех открытых проектов и список всех их компонентов. В проекте всегда располагаются 3 модуля(на каждом листе) и модуль книги. Все модули делятся на: 1) стандартные – добавляются к проекту Insert/Module. Они содержат макросы, функции и процедуры. 2) модули объектов – модули, связанные с рабочими книгами, раблчими листами и формами. Проект VBA состоит из объектов, имеющих иерархическую структуру, и включает: объекты Excel, формы, стандартные модули и модули классов. Окно редактирования кода. В окне проекта выделяете объект, для которого будет введен программный код. Затем выполнить команду View/Code. Откроется окно, в которое нужно ввести программный код. Окно редактирования формы. Для вывода формы: Insert/User Form. На экране появится форма. Окно свойств. Для открытия: View/Properties Window. В левой части окна перечислены свойства объекта, в правой – значения свойств, которые можно изменять. Окно просмотра объектов. View/Object Browser или F2. В верхнем левом углу открывшегося окна – список библиотек объектной модели Excel. Оно дает спраку о всех объектах Excel, их свойствах и методах.
Панель элементов (View/Dwbug)в стандартном варианте включает различные классы управляющих элементов: CommandButton (командная кнопка), TextBox, Label и др. Существует возможность дополнить панель с помощью команды Tools/Additional Controls.
Окно Immediate – один из инструментов отладки. Предназначено для непосредственного ввода команд. Окно Watch – порядок выполнения программы и отслеживание ошибок.
4. Создание простой программы на примере приложения «Информация о продажах по регионам». Задача: создать программу, которая будет отслеживать продажи в каждом регионе. Для каждого региона отразить сообщение с количеством месяцев, когда продажи превышали заданный объем, введенный пользователем. Порядок выполнения: открыть Excel и на первом листе создать список следующего вида:
| А | В | С | … | G |
| месяц | Регион1 | Регион2 | Регион3 | |
| Янв2005 | ||||
| …. | ||||
| Дек2006 |
Отформатировать столбцы(1 – дата, остальные – формат денежный). Заполнить список. Затем выделить диапазон ячеек B2:G25 и присвоить ему имя SalesRange. Перейти в редактор вба: Alt+F11 или Сервис/Макрос/Редактор Вб. Если окна нет, выполнить команду View/Project Explorer. Далее выполнить Insort/Module, откроется окно редактора. Ввести следующий программный код:
Sub CountHighSales()
Dim i As Integer, j As Integer, ks As Integer, s As Currency
s = InputBox("Введите цену для сравнения", "окно для ввода критерия")
If Range("SalesRange").Cells(j, i) >= s Then
MsgBox "в регионе " & i & " объем продаж превышал " & s & " в " & ks & " месяцах"
Запустить программу на выполнение: F5 или Run/Run Sub. На экране появится окно InputBox. Запуск программы с помощью кнопки, расположенной на рабочем листе. Находясь на рабочем листе, выполнить команду Вид/Панели инструментов/Формы. В появившемся окне выбрать элемент Кнопка и нарисовать на рабочий лист. Откроется окно, в котором предлагается создать макрос, выбрать название CountHighSales и закрыть. Дать свое название кнопке. Для запуска программы щелкнуть на этой кнопке.
Создать программу можно и другим способом, начиная с размещения кнопки. Появляется окно «Назначить макрос объекту». Нажать на кнопку Создать. Запустится редактор VBE и откроется окно редактирования кода макроса. Ввести программный код.
5. Стандартные элементы управления VB: понятия, свойства, методы, события общие для этих элементов.Краткая характеристика элементов управления . Элементы управления - это объекты, которые можно разместить на форме. Как и все объекты, они имеют свойства, методы и события. Свойства определяют внешний вид и поведение.
Основой объектной модели Excel является объект Worksheet, представляющий один рабочий лист в файле. Каждый объект Worksheet является частью коллекции Worksheets, которая принадлежит объекту Workbook, представляющему файл рабочей книги Excel. Так как в приложении Excel одновременно может быть открыто несколько рабочих книг, существует также и коллекция Workbooks, содержащая по одному объекту Workbook на каждый открытый файл. Как и во всех приложениях пакета Office, объект Application находится в вершине иерархии объектов и доступен в качестве неявной ссылки при работе в Excel.
Существует еще один тип листов в рабочих книгах Excel - лист диаграммы (объект Chart) - особая таблица, которая содержит только диаграмму. Коллекция Charts объекта Application реализует доступ ко всем диаграммам всех открытых файлов, а коллекция charts объекта Workbook - только к диаграммам данной рабочей книги. Диаграммы также могут быть внедрены в рабочие листы вместо того, чтобы иметь собственный лист. Такие диаграммы не являются частью коллекции charts, а вместо этого доступны в составе коллекции Chartob-jects объекта Worksheet. Коллекция Sheets осуществляет доступ ко всем рабочим листам, как к таблицам, так и к диаграммам.
Большая часть того, что вы делаете в Excel, связана с объектом Range. Хотя имя этого объекта не отличается от своего двойника в приложении Word, их составы различны. Однако его функции практически те же: обрамление части документа, с которым производится работа. Диапазоном может выступать отдельная ячейка, двумерный блок ячеек на рабочем листе или трехмерный блок ячеек, охватывающий несколько рабочих листов.
Еще одной важной составной частью объектной модели Excel является объект Chart. Существует один объект chart, содержащийся в коллекции charts для каждой диаграммы в рабочей книге. Каждому объекту Chart подчинено множество объектов, представляющих составные части диаграммы, такие как оси и легенда.
Мы будем изменять наш макрос, зайдите в пункт меню "макросы", выберите наш и скажите "изменить":
Dim book As String
Dim sheet As String
Dim addr As String
book = Application.ActiveWorkbook.Name
sheet = Application.ActiveSheet.Name
Workbooks(book).Activate
Worksheets(sheet).Activate
Range("A1") = book
Range("B1") = sheet
Dim xList As Integer
xList = Application.Sheets.Count
For x = 1 To xList
s = addr + LTrim(Str(x))
Программирование на VBA можно рассматривать, как управление обьектами приложения. Вот именно обьектами и управляет наше приложение. В нашем случае, если упростить иерхическую архитектуру, то это выглядит так.
То есть главный обьект - приложение. В приложении могут быть несколько книг (Workbook), внутри которых находятся листы (Worksheets) и листы разбиты на ячейки (Cell). При работе активными могут быть только одна книга и один лист. Вот я своим макросом и пытаюсь это выяснить. А заодно сколько листов в текущей книге.
DIM - обьявляет переменную с типом string. Используя обьект Application, мы получаем имена текущих книг и листа. С помощью Range("...") можно выделить ячейку и поместить значения в неё или считать. Вообщем обьекты имеют огромное количество свойств. Задача программиста на VBA знать эти свойства и методы.
