Основные объекты excel используемые при создании процедур - Word и Excel

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

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

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

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

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

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

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

Workbooks("Книга1").Worksheets("Лист1").Range("A1:B10")

Содержание

Присваивание объекта переменной
Активный объект
Смена активного объекта
Свойства объектов
Методы объектов
Рассмотрим несколько примеров
Пример 1
Пример 2
Пример 3

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

В 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 на активном рабочем листе активной книги, можно записать просто:

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

Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы 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 и ещё множество других.

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

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

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

ActiveWorkbook.SaveAs "Книга2", xlCSV

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

ActiveWorkbook.SaveAs Filename:="Книга2", [FileFormat]:=xlCSV

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

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

Пример 1

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

'Пролистываем поочерёдно все рабочие листы активной рабочей книги
'и выводим окно сообщения с именем каждого рабочего листа

Dim wSheet As Worksheet

For Each wSheet in Worksheets
   MsgBox "Найден рабочий лист: " & wSheet.Name
Next wSheet

Пример 2

В этом примере кода 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

Пример 3

Следующий отрывок кода 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

Оцените качество статьи. Нам важно ваше мнение:

Источник

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
является основой структуры программы.
Без создания иерархической структуры
объектов программы, нельзя использовать
VBA
в Еexcel.

Все элементы Excel
– объекты, с которыми приходится работать
в программе. Чаще всего используются
такие объекты, как рабочие книги
WorkBooks,
рабочие листы WorkSheets,
диапазоны Range.
Объект WorkSheets
имеет свойство Name,
только объект Range
не имеет ярко выраженной коллекции.
Выступает как коллекция и как объект.
Чаще исп. В программном коде, обладает
огромными возможностями.

2. Объектная модель Excel: понятие объекта, метода, свойства, события.

Свойства определяют
внешний вид и состояние объекта. Для
определения свойства его нужно указать
справа от имени объекта через точку.
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(«Введите
цену для сравнения», «окно для ввода
критерия»)

For
i = 1 To 6

ks
= 0

For
j = 1 To 24

If
Range(«SalesRange»).Cells(j, i) >= s Then

ks
= ks + 1

End
If

Next j

MsgBox «в регионе
» & i & » объем продаж превышал
» & s & » в » & ks & » месяцах»

Next i

End Sub

Запустить программу
на выполнение: F5
или Run/Run
Sub.
На экране появится окно InputBox. Запуск
программы с помощью кнопки, расположенной
на рабочем листе. Находясь
на рабочем листе, выполнить команду
Вид/Панели инструментов/Формы. В
появившемся окне выбрать элемент Кнопка
и нарисовать на рабочий лист. Откроется
окно, в котором предлагается создать
макрос, выбрать название CountHighSales
и закрыть. Дать свое название кнопке.
Для запуска программы щелкнуть на этой
кнопке.

Создать программу
можно и другим способом, начиная с
размещения кнопки. Появляется окно
«Назначить макрос объекту». Нажать на
кнопку Создать. Запустится редактор
VBE
и откроется окно редактирования кода
макроса. Ввести программный код.

5. Стандартные
элементы управления VB:
понятия, свойства, методы, события общие
для этих элементов.
Краткая
характеристика элементов управления.
Элементы управления — это объекты,
которые можно разместить на форме. Как
и все объекты, они имеют свойства, методы
и события. Свойства определяют внешний
вид и поведение. Изменять свойства можно
как во время проектирования (через окно
свойств), так и в программном коде. Общие
свойства: name
– имя, которое используется для ссылок
на элементы управления в программном
коде. Left,
Top
– левый верхний угол элемента. Width,
height
– ширина, высота элемента. Visible
– будет ли виден на экране. Методы: Move
– перемещение элемента на форме. Drag
– перетаскивание. События: Set
Focus,
click,
dblclick.
Элемент управления Командная кнопка
(CommandButton).
Свойства: Caption
— текст на кнопке Picture-
определяет рисунок на кнопке. Picture
Position
— определяет позицию рисунка на кнопке.
Событие: Click
— возникает при нажатии пользователем
кнопки Мышью, DblClick-
двойной щелчок, когда возникает нажатие.
Элемент управления счетчик SpinButton
— позволяет уменьшать или увеличивать
числовое значение в результате щелчка
стрелки. Элемент управления полоса
прокрутки ScrollBar.
Позволяет выбрать значение из заданного
диапазона с помощью мыли. Щелчком мыши
на концах полосы прокрутки или на самой
полосе путем перетаскивания бегунка.
Важнейшие свойства SpinButton
и ScrollBar:
max-
максимальное значение, выдаваемое
полосой прокрутки или счетчиком (всегда
положителен) min-
минимальное значение выдаваемое этими
элементами. CmalChange-
отрицательное или положительное целое
число равное шагу изменения значения
этих элементов в тот момент,
когдыапроизводится щелчок. событие
Change
происходит при смене значений этих
элементов. С помощью свойства Value
в некоторой переменной можно запомнить
текущее значение этого счетчика. Элемент
управления рамка (Frame)
Она является контейнером и служит для
объединения других элементов в группу.
После чего помещенными в нее объектами
можно управлять как единым целым (либо
переключатели, либо флажки обычно в нее
помещаются) Свойства: Caption-
задает текст, который выводится в левом
верхнем углу рамки; Appearance-
2 значения: 0- Flat
(простая рамка), 1- 3D
объемная. Элемент управления текстовое
поле. Он
обеспечивает возможность ввода текста
пользователем во время работы приложения
или отображение информации, задаваемой
свойством Text.
Свойства: text-
текстовое поле, характеризуется еще
свойствами, позволяющими использовать
этот элемент при создании полей,
предназначенных для ввода пароля.
PasswordChar
задает символ отображаемый в поле вместо
возможных символов. Maxlength-
максимальное количество символов,
вводимых в поле. Элемент управления
Метка (надпись) он используется в форме
для размещения статичного текста.
Свойства: Caption-
текст который размещается на этом
элементе, размер не более 1024 байт. Font-
внешний вид шрифта.

6. Типы подпрограмм
и их определения: определение и виды
процедур. Примеры процедур разных видов.
Логические
фрагменты кода, которые выполняют
определенную задачу, называются
подпрограммами. В вба подпрограммами
называют макросы, процедуры и даже
функции.

Можно создать
основную процедуру и вызвать из нее
остальные процедуры. В этом случае набор
совместно работающих процедур наз.
приложением. Существуют следующие виды
процедур: 1)обработки событий 2) макросов
3) пользователей. Процедура обработки
события всегда связана с каким-либо
объектом.

Private
Sub
ИмяОбъекта_Событие()

<тело
процедуры обрабатываемого события>

End
Sub

Пример:
Private Sub CommandButton_Click()

Ипотека.Show

End
Sub

Процедура
обработки макроса имеет следующий
синтаксис:

Sub
ИмяМакроса ()

<Тело
макроса>

End
Sub

Для вызова макроса
из главной процедуры используется
оператор Call
ИмяМакроса. При условии, что все процедуры,
в том числе и главная, расположены в
одном модуле.

Пример:

Sub
MainProcedure()

Call
GetProblemSize

Call
Initialize

Exit
Sub

End Sub

Sub
GetProblemSize()

Ncities =
Range(«DistMatrix»).Rows.Count

ReDim
Visited(Ncities)

ReDim
Route(Ncities + 1)

End Sub

Sub
Initialize()

Dim I As
Integer

Route(1) = 1

Route(Ncities + 1) = 1

Visited(1) = True

For
I = 2 To Ncities

Visited(I) = False

TotDist = 0

End Sub

Синтаксис процедуры
пользователя:

[Private/Public]
Sub ИмяПроцедуры
(Параметры)

<Тело процедуры>

End
Sub

Элементы списка
параметров имеют следующий вид:
ИмяЭлемента As
ТипДанных

7. Типы подпрограмм
и их определения: определение и виды
процедур. Примеры процедур разных видов.
Логические
фрагменты кода, которые выполняют
определенную задачу, называются
подпрограммами. В вба подпрограммами
называют макросы, процедуры и даже
функции. Функция – это подпрограмма,
которая действует в пределах своего
блока и возвращает только одно значение.
Функции: 1)ф. пользователя 2) ф. модулей
классов. Функция имеет следующий
синтаксис:

[Private/Public]
Function
Имя функции(список параметров) As
ТипДанных

<тело функции>

End
Function

Тип данных для
функции предназначен для определения
типа и возвращения функцией зн-я

Чтобы создать
функцию пользователя, надо: 1. если нет
модуля Insert/Module
2. Insert/Procedure
3. в открывшемся окне выбрать переключатель
“Function”,
в поле Name
ввести имя функции («Доход») 4. в этом же
окне установить переключатель «общие»,
чтобы был тип – Public
5. Ок. Откроется окно редактора. Ввести
параметры функции и код.

Option
explicit

Option
Base 1

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=0

for
i=1 to n

s=s+platezh(i)/(1+procent)^((god(i)-god(1))/365)

next
i

doxod=s

end
fuction

Не закрывая окна
View/Object
Browser.
Откроется окно просмотра объектов. В
левом верхнем углу выбрать VBAProject
и в окне Классы отобрать элементы
текущего проекта. Выбрать модуль, в
котором ваша функция. Затем в окне
Компоненты отобрать все элементы, в том
числе и функцию Доход. Щелкнуть правой
кнопкой мыши на Доход и выбрать значение
Свойства. Откроется окно «Параметры
компонента», в нем ввести описание(назначение)
функции. Закрыть окно и после этого ваша
функция попадет в библиотеку стандартных
функций Excel.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник

2006 г.

Объекты Excel
Лекция из курса «Основы офисного программирования и документы Excel»

Биллиг Владимир Арнольдович
Интернет-Университет Информационных Технологий, INTUIT.ru

Программист, работающий в Excel, должен свободно ориентироваться в мире его объектов. Мощь офисного программирования определяется тем, что изначально в распоряжении программиста находится большое число уже готовых объектов. Чтобы с толком распорядиться предоставляемыми возможностями, объекты нужно знать.

Разговор об объектах Excel целесообразно начать с рассмотрения каркаса документа Excel. Многочисленные библиотеки объектов Office 2000, совокупность которых для программиста и представляют Office 2000, задают каркас всех документов, которые можно построить в этой среде. Когда создается новый документ, например, рабочая книга Excel, то по умолчанию из всей совокупности библиотек выбирается несколько, объекты которых и составляют каркас документа. Эти объекты доступны программисту, без каких либо дополнительных усилий. Центральную роль в каркасе документов Excel играют, конечно же, объекты библиотеки Excel. Но знание и всех других объектов, входящих в каркас, необходимо. Например, при программном создании интерфейса необходимо знание общих объектов библиотеки Office. Отмечу еще, что при желании программист всегда может расширить каркас документа, добавив в него те или иные библиотеки. Каркас, создаваемый по умолчанию в тот момент, когда открывается новая рабочая книга, состоит из объектов, входящих в состав следующих библиотек:

Excel — библиотека, задающая основу документов Excel. Здесь хранится класс, задающий корневой объект Excel.Application, и все классы объектов, вложенных в корневой объект.
Office — библиотека объектов, общих для всех приложений Office 2000. Здесь находятся классы, определяющие инструментальные панели — CommandBar и классы других общих объектов. Здесь же находятся классы, задающие Помощника (объект Assistant и все классы, связанные с ним). В частности, появился новый объект, которого не было в предыдущей версии — Мастер Ответов (Answer Wizard).
Stdole — библиотека классов, позволяющая работать с OLE — объектами и реализовать Автоматизацию.
VBA — библиотека классов, связанных с языком VBA. Здесь хранятся все стандартные функции и константы, встроенные в язык, классы Collection и ErrObject.
VBAProject — проект по умолчанию, связанный с документом. Классы, которые могут программистом создаваться в этом проекте, методы, свойства, — все это доступно для просмотра, так же, как и объекты классов, встроенных в стандартные библиотеки.

Если сравнить каркас рабочей книги Excel, например, с каркасом документа Word, то они отличаются тем, что в основе одного лежит библиотека Excel, в основе другого — библиотека Word. Эти библиотеки содержат специфические для данных приложений объекты. Что же касается интерфейсных объектов, объектов определяющих среду редактора VBA, автоматизацию, то здесь используются общие объекты. Библиотеки Office, Stdole, VBA — это общие для всех приложений Office 2000 библиотеки.

Замечу, что хотя каркас документа Excel не изменился в Office 2000 в сравнении с предыдущей версией, вместе с тем в объектной модели произошли довольно существенные изменения, появились новые объекты, новые свойства и методы у ранее существовавших объектов.

Объектная модель Excel

Прежде всего, несколько слов о том, как устроена объектная модель Excel и других приложений Office 2000. В этой модели объекты связаны между собой отношением встраивания. На нулевом уровне иерархии существует некоторый центральный объект, в который встроены другие объекты, составляющие первый уровень иерархии. В каждый из объектов первого и последующих уровней могут быть встроены объекты следующего уровня. Так это процесс продолжается. Таким образом, объекты в этой модели «толстые», поскольку в них встроено большое число других объектов. В особенности это касается объектов, стоящих на верхних уровнях иерархии.

Формально встраивание реализуется с помощью свойств объектов. Свойства могут быть как терминальными, не являющимися объектами, и так называемыми свойствами — участниками, которые возвращают объекты при их вызове.

Давайте перейдем к рассмотрению библиотеки объектов Excel 9.0 и начнем с центрального объекта этой библиотеки — Excel.Application.

Объект Excel Application

Объект Excel.Application задает приложение Excel. А посему свойства, методы и события этого объекта должны характеризовать приложение в целом. Понятно, что у этого объекта должно быть свойство Workbooks, возвращающее все открытые в приложении рабочие книги, свойство Windows, возвращающее открытые окна, свойства, такие как CommandBars, возвращающие объекты интерфейса, и другие подобные свойства. Методов и событий, характерных для всего приложения в целом, по-видимому, не так уж и много. Так что, казалось бы, структура этого объекта должна быть достаточно простой. Однако реально это не так, — у объекта Excel.Application очень большое число свойств, методов и событий, что не позволяет мне описать их полностью, да и нет в этом особого смысла. Объект Excel.Application, на мой взгляд, явно перегружен, многие его свойства и методы без всякого ущерба можно было бы исключить, поскольку они оперируют с объектами, стоящими на более низких уровнях иерархии и не имеют прямого отношения ко всему приложению в целом. Приведу лишь один пример. Первое по алфавиту свойство ActiveCell возвращает объект, задающий активную ячейку. Понятно, что речь идет об активной ячейке активной страницы активной рабочей книги. Непонятно только, зачем нужно было добавлять это свойство самому приложению. Вполне достаточно, чтобы им обладал объект WorkSheet, задающий страницу книги. Более того, если в момент вызова свойства ActiveCell нет активной страницы с ячейками, то возникнет ошибка, чего не происходит, если активную ячейку вызывает объект WorkSheet. Примеров подобной перегруженности объекта Application можно привести много. Я в своем описании объектов верхнего уровня не всегда буду упоминать такие свойства, полагая, что лучше рассказать о них там, где они необходимы по существу.

Общие объекты и Excel.Application

Давайте начнем рассмотрение со свойств объекта Excel.Application , возвращающих уже знакомые нам общие объекты:

Таблица 3.1. Общие объекты, доступные в Excel.Application

Свойство, возвращающее объект	Назначение объекта	Библиотека
`Assistant`	Помощник, позволяющий организовать собственную диалоговую систему.	Office
`Answer Wizard`	Мастер Ответов, стоящий за спиной Помощника. Может использоваться при создании собственной справочной системы.	Office
`Com AddIns`	Коллекция компонент, общих для приложений Office 2000.	Office
`CommandBars`	Коллекция инструментальных панелей, без работы с которой не обойтись при создании собственного интерфейса документа Excel.	Office
`FileSearch`	Объект, используемый при поиске файлов.	Office
`Language Settings`	Объект, задающий языковые предпочтения, общие для приложений Office 2000.	Office
`Debug`	Объект, используемый при отладке программных проектов.	VBA
`VBE`	Корневой объект при работе с программными проектами.	VBA

Все объекты, приведенные в этой таблице, играют важную роль при программной работе с документами Excel, как, впрочем, и с другими документами Office 2000.

Свойства — участники объекта

Рассмотрим теперь свойства — участники объекта Excel.Application, возвращающие объекты, специфические для Excel, Как я и предупреждал, я рассмотрю лишь основные свойства, которые действительно необходимы при работе с объектом Excel.Application.

Таблица 3.2. Основные свойства — участники

Свойство, возвращающее объект	Назначение объекта
`WorkBooks`	Коллекция открытых в Excel документов — рабочих книг. Основной объект, благодаря которому можно получить доступ к любому документу Excel и далее работать с объектами этой рабочей книги.
`Windows`	Коллекция открытых окон во всех рабочих книгах. Дело в том, что одну и ту же рабочую книгу часто полезно открывать в нескольких окнах, что позволяет видеть разные участки рабочей книги. Коллекция Windows позволяет получить доступ к каждому такому окну. Чаще всего, свойство `Windows` используется при работе с объектом `WorkBook`, для объекта `Application` это один из примеров той перегрузки, о которой я упоминал выше.
`WorkSheetFunction`	Объект — контейнер, в котором находятся многочисленные функции Excel, начиная от обычных математических функций и кончая функциями, применяемыми для решения задач статистики, прогноза, работы с датами и прочими.
`AddIns`	Коллекция компонент, расширяющих возможности решения специальных задач в Excel.
`AutoCorrect`	Знакомый по приложению Word объект, позволяющий задавать автоматическую корректировку набираемых текстов в ячейках Excel.
`DefaultWebOptions`	Объект, позволяющий устанавливать параметры для документов Excel, сохраненных в виде Web-страниц. Схож с аналогичным объектом `Word.Application`, но имеет свою специфику.
`Dialogs`	Объект Dialogs также как и три предыдущих объекта — `AddIns, AutoCorrect, DefaultWebOptions` относится к группе схожих объектов, встречающихся в каждом из приложений Office 2000, имеющих много общего, но имеющих и отличия, связанные со спецификой приложения. Также как и в Word, объект `Dialogs` задает коллекцию стандартных диалоговых окон, которые могут открываться в Excel, позволяя организовать диалог с пользователем.
`Names`	Одно из перегруженных свойств, возвращающее коллекцию всех имен, используемых для отдельных ячеек и областей всех открытых документов Excel. Чаще всего, это свойство используется при работе с отдельной рабочей книгой или отдельной страницей.
`ODBCErrors`	Коллекция объектов класса `ODBCError`. Элементы этой коллекции создаются автоматически источником ODBC-данных, если при выполнении запроса на получение данных возникли ошибки. Если ошибок не было, то и коллекция будет пустой.
`OLEDBErrors`	Коллекция объектов класса `OLEDBError`. Аналогично предыдущей коллекции, ее элементы появляются при наличии ошибок в процессе работы с базой данных, когда используется интерфейс OLE DB.
`RecentFiles`	Объект, относящийся к группе схожих объектов семейства Office 2000. Он задает коллекцию файлов, хранящих документы Excel последнего использования.

Основное содержание этой главы будет связано с рассмотрением коллекции Workbooks, а точнее с объектом Workbook и вложенными в него объектами. Но прежде чем двинуться далее, приведу все-таки краткий обзор тех вложенных в Excel.Application объектов, доступных на этом уровне, по сути, относящихся к нижним уровням иерархии объектной модели Excel:

Группа активных объектов — ActiveWorkbook, ActiveWindow, ActiveSheet, ActiveChart, ActiveCell, ActivePrinter, — возвращающих активную рабочую книгу, окно, активную рабочую страницу, диаграмму или ячейку, если таковые существуют в момент вызова соответствующего свойства. При отсутствии запрашиваемого активного объекта возникнет ошибка. Все эти объекты будут подробно рассмотрены, но чуть позже, когда мы спустимся вниз по иерархии объектов. Особняком стоит свойство, возвращающее активный принтер. Это свойство действительно имеет смысл связать с приложением. Заметьте, что объекты, стоящие на нижних уровнях иерархии, например, Workbook, этим свойством не обладают, так что добраться до принтера можно только через объект Application.
Группа коллекций и объектов Range, входящих в состав соответствующего активного объекта — Sheets, Charts, Rows, Columns, Cells, Range — возвращающие соответственно коллекции рабочих страниц, страниц диаграмм активной рабочей книги, объект Range, содержащий все строки, столбцы, ячейки или заданную область активной рабочей страницы. Также как и в случае вызова объектов предыдущей группы, при вызове этих свойств следует быть осторожным, поскольку возникает ошибка, если нет соответствующего активного объекта.
Свойство Selection возвращает выделенный объект в активном окне. Тип возвращаемого объекта зависит, от текущего выделения. Возвращается Nothing, если в активном окне нет выделенного объекта.
Свойство ThisWorkbook возвращает текущую рабочую книгу, содержащую выполняемый макрос, один из операторов которого и вызвал это свойство. Это свойство представляет единственный способ добраться до рабочей книги, содержащей компонент AddIn, изнутри макросов, составляющих этот компонент.

Терминальные свойства

Терминальных свойств много, и понятно почему. Приложение Excel, как и другие приложения Office 2000, могут быть настроены пользователем по своему усмотрению. Эту настройку можно выполнять вручную, а можно и программно. Настройка вручную большей частью проводится из меню Сервис | Параметры, используя возможности, предоставляемые различными вкладками в открывающемся окне параметров. Для программной настройки используются терминальные свойства, — в этом их основное назначение. Естественно, я не буду останавливаться на всех свойствах, — они просты. В ниже приведенном обзоре представлено выборочное описание некоторых групп терминальных свойств:

Группа свойств, задающих свойства приложения по умолчанию, — DefaultFilePath, DefaultSaveFormat, DefaultSheetDirection, — путь по умолчанию, формат по умолчанию, направление просмотра текста (слева направо или справа налево), задаваемое для некоторых языков. К этим же свойствам примыкает и ранее упоминавшееся свойство DefaultWebOptions.
Группа булевых свойств, позволяющих включить или выключить отображение на экране тех или иных элементов приложения — DisplayAlerts, DisplayCommentIndicator, DisplayFormulaBar, DisplayStatusBar и другие Display-свойства. Первое из этих свойств позволяет управлять выдачей на экран некоторых сообщений в процессе работы макросов, второе — отображать специальный индикатор при показе комментариев. Более часто приходится использовать управление показом панелей формул и статуса. Особенно часто приходится использовать эти свойства, когда документ Excel используется в специальных целях, например, при отображении различных бланков, когда внешний вид документа ничем не напоминает привычную электронную таблицу. Замечу, что используемое в этих случаях свойство DisplayGridLines, позволяющее отключать сетку, принадлежит объекту Windows, а не объекту Application.
Группа булевых свойств, позволяющих включить или выключить те или иные свойства — EnableAnimations, EnableAutoComplete, EnableCancelKey, EnableEvents, EnableSound. Первое из этих свойств позволяет управлять анимацией при добавлении или удалении строк и столбцов рабочего листа, второе — автозаполнением ячеек таблицы. Свойство EnableCancelKey не является булевым, оно принимает значения, заданные соответствующим перечислением, и позволяет управлять процессом прерывания программы при нажатии комбинации клавиш Ctrl+Break. Значение xlInterrupt, принятое по умолчанию, позволяет прервать выполнение макроса и перейти в режим отладки, где возможно пошаговое выполнение. Однако с помощью этого свойства можно задать разные режимы, как, например, передачу управления обработчику ошибок в момент прерывания. Пользоваться этим свойством следует осторожно, поскольку при зацикливании может возникнуть ситуация, когда нельзя будет прервать программу, не применяя грубых способов. Свойство EnableEvents позвол яет управлять включением событий объекта Application, а свойство EnableSound управляет включением звука в процессе работы приложений Office 2000.
Группа свойств, управляющих размерами главного окна приложения Excel — Height, Width, Left, Top, задающие высоту, ширину окна и координаты верхнего левого угла окна.
Многие другие свойства, позволяющие управлять курсором, скроллингом, характеристиками пользователя и многими другими параметрами так или иначе, характеризующими приложение Excel.

Методы объекта Application

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

Метод ActivateMicrosoftApp(Index As xlMSApplication) позволяет активировать приложение Microsoft, заданное соответствующей константой в аргументе метода. Если приложение уже выполняется, то активируется текущий вариант. В противном случае открывается экземпляр приложения, и затем приложение активируется. Константы позволяют задать все основные приложения Office 2000, а также FoxPro, Project и некоторые другие приложения Microsoft.
Группа методов — DeleteCustomList, DeleteChartAutoFormat, AddCustomList, AddChartAutoFormat — позволяет удалять и добавлять пользовательские списки и пользовательские форматы к тем спискам и форматам, которые используются в самом приложении Excel.
Группа из пяти DDE-методов позволяет обеспечить динамический обмен данными между приложениями в соответствии со стандартом DDE. Сохранена для поддержки совместимости с предыдущими версиями Excel.
Методы, запускающие вычисления — Calculate, CalculateFull, приводят к перевычислению рабочих страниц всех рабочих книг. Метод CheckSpelling запускает проверку орфографии во всех рабочих книгах. Метод Evaluate(Name) преобразует имя объекта в сам объект. Эти методы объединяет то, что все они, по существу, являются методами объектов более низкого уровня иерархии — объектов Workbook, WorkSheet, Chat. Объект Application «наследует» эти методы у своих потомков, что позволяет распространять действие метода на все открытые рабочие книги. Следует понимать, что пользоваться вызовом этих методов объектом Application стоит в очень редких случаях. Опять-таки, можно говорить о некоторой излишней перегрузке объекта Application.
Группа Get-методов — GetCustomListContents, GetCustomListNum, позволяет вернуть содержимое пользовательского списка, получить его номер. Методы GetOpenFileName, GetSaveAsFileName позволяют получить имя файла, выбранное пользователем, открывая по ходу дела соответствующее диалоговое окно.
Группа On-методов, позволяющих запустить на выполнение некоторый макрос. Метод OnKey(Key, Procedure) позволяет запустить макрос, заданный вторым параметром метода, при нажатии пользователем комбинации клавиш, заданной первым параметром метода. Метод OnTime(EarliestTime, Procedure As String, [LatestTime], [Schedule]) позволяет запустить макрос, заданный вторым параметром метода, в указанное время. О схожем методе OnTime рассказывалось при описании методов объекта Word.Application. Методы OnRepeat(Text As String, Procedure As String) и OnUndo(Text As String, Procedure As String) позволяют указать макросы и текст, который будет появляться в пунктах «Повторить Выполнение» и «Отменить Выполнение» из меню Правка. Когда пользователь выберет соответствующий пункт меню, то запускается макрос, указанный втор ым параметром метода. Вот простой пример на применение этих методов:

Public Sub RepeatAndUndo()
   'Создание пунктов Повторить и Отменить в меню Правка 
   Call Application.OnRepeat("Hello", "Test")
   Call Application.OnUndo("7 to A1", "Write7")

End Sub

Public Sub Test()
   MsgBox ("Hi!")
End Sub

Public Sub Write7()
 Range("A1") = 7
End Sub

Процедура RepeatAndUndo создает соответствующие пункты меню Правка, а процедуры Test и Write7 будут вызываться при выборе пользователем этих пунктов меню. Замечу, что реально особой пользы от применения этих методов не вижу, так как при любых действиях пользователя произойдет обновление этих пунктов меню.

Методы Repeat и Undo близки по духу к рассмотренным только что методам. Они позволяют повторить или отменить последнее действие пользователя при его работе вручную.
Еще одним важным методом, позволяющим запускать макрос на выполнение, является метод Run(Macro, Arg1, Arg2, …). Метод Run позволяет выполнить макрос (процедуру или функцию) проекта рабочей книги или функцию из DLL или XLL. Макрос, запускаемый на выполнение, может находиться в той же рабочей книге, что и макрос, вызвавший метод Run, но может принадлежать и другой рабочей книге. В этом случае, естественно, проекты должны быть связаны по ссылке и в проекте, который вызывает макрос другого проекта, должна быть установлена ссылка на вызываемый проект. При вызове макросу могут быть передано произвольное число аргументов, все они передаются по значению, так что, заметьте, нельзя передать макросу сам объект, а только его значение, задаваемое свойством Value. Метод Run в свою очередь возвращает значение, являющееся результатом выполнения макроса. Приведу простой пример, демонстрирующий все особенности вызова метода Run:

Проекту документа BookOne я дал имя BookOneProject. В этом проекте объявлена глобальная переменная

Option Explicit
Public GlobalZ As Variant

В модуль с именем ModuleOne этого проекта я поместил описание процедуры PlusXY и функции Plus1. Они выполняют простые и понятные без комментариев действия.

Public Function Plus1(ByVal X As Integer) As Integer
   Plus1 = X + 1
End Function

Public Sub PlusXY(ByVal X As Integer, Y As Integer)
   GlobalZ = X + Y
End Sub

В этом же модуле находится и процедура testrun, демонстрирующая вызовы метода Run.

Public Sub testrun()
   'Запуск на выполнение функции и процедуры,
   'находящихся в том же проекте
Dim z As Integer
   z = Application.Run("Plus1", 7)
   Debug.Print "z = ", z
   z = Application.Run("PlusXY", 5, 7)
   Debug.Print "GlobalZ = ", GlobalZ, "z = ", z
End Sub

Вот результаты ее выполнения:

z = 8 
GlobalZ = 12   z = 0

В проекте другой рабочей книги Excel с именем BookTwo я установил ссылку на проект BookOneProject и в один из модулей поместил процедуру testrun1, вызывающую макросы проекта BookOneProject:

Public Sub testrun1()
   'Запуск на выполнение функции и процедуры,
   'находящихся в другом проекте BookOneProject,
   'на который установлена ссылка.
   Dim z As Integer
   z = Application.Run("BookOneProject.Module1.plus1", 7)
   MsgBox ("z= " & z)
   Call Application.Run("BookOneProject.Module1.plusXY", 5, 7)
   MsgBox ("GlobalZ = " & BookOneProject.GlobalZ)
End Sub

И в этом варианте метод Run успешно справляется с вызовом макросов другого проекта. Конечно, в данном примере вместо того, чтобы применять метод Run, можно было бы непосредственно вызвать ту же функцию Plus1. Но, надеюсь, Вы понимаете, что истинная ценность метода Run в том, что имя выполняемого макроса может быть передано ему в качестве параметра, так что в зависимости от ситуации он может запускать разные макросы. Но давайте закончим с примером и вернемся к рассмотрению других методов объекта Excel.Application.

Метод Goto([Reference], [Scroll]) не выполняя макроса, позволяет перейти к его рассмотрению. Другое, может быть, основное назначение метода состоит в том, чтобы перейти в заданную точку рабочей книги Excel. Чтобы перейти к рассмотрению макроса, параметр Reference должен быть строкой, задающей имя макроса. Для перехода в заданную область документа параметр Reference задается объектом Range. Булев параметр Scroll, имеющий значение true, обеспечивает прокрутку области так, чтобы заданная точка находилась в левом верхнем углу области просмотра. Главное, на что стоит обратить внимание, — метод Goto позволяет осуществлять переходы между документами. Вот пример макросов из документа BookTwo, осуществляющих соответственно переходы к заданной области и макросу документа BookOne.
```
Public Sub GotoRange()
   'Переход к заданной области другого документа
   Application.Goto Workbooks("BookOne.xls").Worksheets("Лист1").Range("A20"), True
End Sub

Public Sub GotoMacro()
   'Переход к заданному макросу в другом проекте
   Application.Goto "BookOneProject.Module1.testrun"
End Sub	
```
Метод MacroOptions ([Macro], [Description], [HasMenu], [MenuText], [HasShortcutKey], [ShortcutKey], [Category], [StatusBar], [HelpContextID], [HelpFile]) — это еще один метод, связанный с макросами. Он позволяет задать для макроса, указанного первым параметром, различные характеристики — описание, горячие клавиши, раздел справки, связанный с данным макросом, и другие свойства.
Метод RecordMacro([BasicCode], [XlmCode]) — также предназначен для работы с макросами. Он позволяет добавить некоторый программный код в макрос, создаваемый инструментом MacroRecorder. В момент вызова метода MacroRecorder должен быть включен и записывать макрос в модуль, не являющийся активным, другими словами, нельзя произвести запись в тот модуль, макрос которого вызвал метод RecordMacro.
Метод Wait(Time) As Boolean — это последний из описываемых мной методов объекта Excel.Application, входящих в большую группу методов, предназначенных для работы с макросами. Он позволяет организовать задержку вычислений на заданное время, указанное параметром метода. В приведенном ниже примере метод используется, чтобы открыть и показать пользователю некоторую форму, а затем закрыть ее по истечении заданного времени. Этот прием можно использовать в играх, целью которых является проверка внимательности. Вот текст соответствующего макроса:
```
Public Sub WaitSomeTime()
   'Открывает форму на ограниченное время
   MsgBox ("Форма будет показана на 10 секунд!")
   FlyForm.Show
   Application.Wait (Now + TimeValue("0:00:10"))
   FlyForm.Hide
End Sub	
```

Взгляните, как выглядит сама форма.

Рис. 3.1. Форма FlyForm, открытая на «мгновение»

Привожу рисунок этой формы только для того, чтобы пояснить, какая цель преследуется в этом примере. Я предполагал, что при открытии формы пользователь должен успеть в предоставленное ему время ввести два числа в поля X и Y , нажать кнопку, производящую вычисления и запомнить полученный результат. Однако мои намерения не осуществились, и вот по каким причинам. Если форма имеет статус модальной формы, то выполнение макроса приостанавливается до той поры, пока пользователь не закроет форму. Так что в этом случае у пользователя время на работу с формой не ограничено. Это я понимал. Если же форма имеет статус немодальной формы (свойство ShowModal = False), то форма действительно будет открыта в течение 10 секунд. Но в этом случае пользователь не сможет работать с этой формой, вводить значения в поля ввода и нажимать командную кнопку. Хуже всего то, что при попытке ввода значений в поля формы они фактически будут попадать в произвольное место программного текста и порти ть сам проект. Так что следует быть осторожным в подобной ситуации.

Метод Help([HelpFile], [HelpContextID]) позволяет вызвать справочное руководство, указав при необходимости и соответствующий раздел в этом руководстве. Можно вызывать как стандартную справочную систему, — в этом случае не нужно задавать аргументы при вызове метода, либо, что чаще бывает, собственную справочную систему. Первый параметр метода задает имя файла, хранящего справочное руководство. Этот файл может иметь уточнение «chm» , если руководство подготовлено с помощью инструментария HTML Help Workshop, или иметь уточнение «htm», если справочная система создана с помощью инструментария Microsoft WinHelp.
Методы Intersect(Arg1 As Range, Arg2 As Range, …)As Range и Union(Arg1 As Range, Arg2 As Range, …)As Range возвращают в качестве результата объект Range, задающий прямоугольную область, представляющую соответственно пересечение или объединение областей аргументов, которых должно быть не менее двух и не более 30.
Метод InputBox, по существу, эквивалентен одноименной функции из библиотеки VBA и позволяет организовать диалог с пользователем и принять введенное им значение. Функция InputBox является одной из наиболее широко применяемых функций, и примеров ее вызова приводилось достаточно много. Не обойтись без нее и в примерах этой книги. Что вызывать метод InputBox объекта Application или функцию InputBox библиотеки VBA — дело вкуса.
Метод Volatile([Volatile]) позволяет включить или выключить принудительное вычисление для функций, вызываемых в формулах рабочего листа. Метод вызывается непосредственно в функции, которую предполагается пометить. Булев параметр Volatile помечает функцию, как принудительно вычисляемую, если он имеет значение true. Это значение является значением параметра по умолчанию.

Я рассмотрел большую часть методов объекта Application. Замечу, что в предыдущей версии этих методов было значительно больше, поскольку многие функции Excel — математические и прочие были доступны на этом уровне. Теперь, как и положено, все они находятся в специальном контейнере WorkSheetFunction.

Оглавление Вперёд

Бесплатный конструктор сайтов и Landing Page

Хостинг с DDoS защитой от 2.5$ + Бесплатный SSL и Домен

SSD VPS в Нидерландах под различные задачи от 2.6$

ATLEX

Выделенные серверы: в Европе / в России.

Виртуальные серверы: в Европе / в России.

Партнерская программа

VPS в 21 локации

От 104 рублей в месяц

Безлимитный трафик. Защита от ДДоС.

Виртуальные серверы VPS/VDS в России, Европе и США!

Промокод citforum — скидка 10% на заказ сервера!

Новости мира IT:

14.04 — Компания AMD представила открытый проект openSIL для разработки прошивок
14.04 — «Роскосмос» создаст систему связи с прямым подключением смартфонов к спутникам
14.04 — Представлен новый суперкомпьютер Gaea C5 производительностью более 10 Пфлопс для исследования климата
14.04 — После обновления сети курс Ethereum взлетел до максимума за 11 месяцев — биткоин тоже вырос
14.04 — Представлен первый в мире полностью перерабатываемый нетоксичный транзистор
14.04 — Европейский совет по защите данных проведёт расследование работы ChatGPT
14.04 — Роскомнадзор заблокировал 135 млн мошеннических звонков
14.04 — Google представила нейросеть Med-PaLM 2 для помощи медработникам в постановке диагноза
13.04 — Android или iOS? А может, Flutter? Всё и сразу на Mobius 2023 Spring!
13.04 — В России создали первую отечественную базовую станцию стандарта 5G — до 1,4 Гбит/с
13.04 — Intel продала свой бизнес по выпуску серверов
13.04 — Совкомбанк и Фонд «Сколково» проведут командный онлайн-хакатон по разработке HR-платформы
13.04 — Обанкротившаяся криптобиржа FTX восстановила $7,3 млрд активов и планирует перезапуск
13.04 — Apple перейдёт на использование только переработанного кобальта во всех батареях к 2025 году
11.04 — Состоялся релиз Firefox 112
11.04 — Вышел релиз FreeBSD 13.2 с поддержкой Netlink и WireGuard
11.04 — В WhatsApp можно будет войти в аккаунт на четырёх устройствах одновременно
11.04 — Google исправила более 60 уязвимостей Android
11.04 — Google оштрафовали на $32 млн за недобросовестную конкуренцию на рынке мобильных игр в Южной Корее
11.04 — Российский рынок ЦОД продолжает расти, но темпы развития замедлились

Архив новостей

Корпоративная мобильная связь от Телфин
HOSTKEY: серверы и облачные решения для вашего бизнеса

Источник

Время на прочтение
7 мин

Количество просмотров 312K

Приветствую всех.

В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.

VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.

Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.

Поэтому, увы, будем учить Visual Basic.

Чуть-чуть подготовки и постановка задачи

Итак, поехали. Открываем Excel.

Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.

Появилась вкладка.

Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):

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

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

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

Кодим

Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».

И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».

Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:

Sub FormatPrice()End Sub

Напишем Hello World:

Sub FormatPrice() MsgBox "Hello World!" End Sub

И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.

Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

' Процедура. Ничего не возвращает ' Перегрузка в VBA отсутствует Sub foo(a As String, b As String) ' Exit Sub ' Это значит "выйти из процедуры" MsgBox a + ";" + b End Sub' Функция. Вовращает Integer Function LengthSqr(x As Integer, y As Integer) As Integer ' Exit Function LengthSqr = x * x + y * y End FunctionSub FormatPrice() Dim s1 As String, s2 As String s1 = "str1" s2 = "str2" If s1 <> s2 Then foo "123", "456" ' Скобки при вызове процедур запрещены End IfDim res As sTRING ' Регистр в VB не важен. Впрочем, редактор Вас поправит Dim i As Integer ' Цикл всегда состоит из нескольких строк For i = 1 To 10 res = res + CStr(i) ' Конвертация чего угодно в String If i = 5 Then Exit For Next iDim x As Double x = Val("1.234") ' Парсинг чисел x = x + 10 MsgBox xOn Error Resume Next ' Обработка ошибок - игнорировать все ошибки x = 5 / 0 MsgBox xOn Error GoTo Err ' При ошибке перейти к метке Err x = 5 / 0 MsgBox "OK!" GoTo ne


Err:

    MsgBox 
"Err!"
ne:

On Error GoTo 0 ' Отключаем обработку ошибок ' Циклы бывает, какие захотите Do While True Exit DoLoop 'While True Do 'Until False Exit Do Loop Until False ' А вот при вызове функций, от которых хотим получить значение, скобки нужны. ' Val также умеет возвращать Integer Select Case LengthSqr(Len("abc"), Val("4")) Case 24 MsgBox "0" Case 25 MsgBox "1" Case 26 MsgBox "2" End Select' Двухмерный массив. ' Можно также менять размеры командой ReDim (Preserve) - см. google Dim arr(1 to 10, 5 to 6) As Integer arr(1, 6) = 8Dim coll As New Collection Dim coll2 As Collection coll.Add "item", "key" Set coll2 = coll ' Все присваивания объектов должны производится командой Set MsgBox coll2("key") Set coll2 = New Collection MsgBox coll2.Count End Sub

Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.

Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.

Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.

Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.

Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое-нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями.

Кодим много и под Excel

В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.

Sub FormatPrice() Sheets("result").Cells.Clear Sheets("data").Activate End Sub

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets("result").Activate Dim r As Range Set r = Range("A1") r.Value = "123" Set r = Range("A3,A5") r.Font.Color = vbRed r.Value = "456" Set r = Range("A6:A7") r.Value = "=A1+A3"

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

Считали группы из очередной строки.
Пробегаемся по всем группам в порядке приоритета (вначале более крупные)
1. Если текущая группа не совпадает, вызываем процедуру AddGroup(i, name), где i — номер группы (от номера текущей до максимума), name — её имя. Несколько вызовов необходимы, чтобы создать не только наш заголовок, но и всё более мелкие.
После отрисовки всех необходимых заголовков делаем еще одну строку и заполняем её данными.

Для упрощения работы рекомендую определить следующие функции-сокращения:

Function GetCol(Col As Integer) As String GetCol = Chr(Asc("A") + Col) End FunctionFunction GetCellS(Sheet As String, Col As Integer, Row As Integer) As Range Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row)) End FunctionFunction GetCell(Col As Integer, Row As Integer) As Range Set GetCell = Range(GetCol(Col) + CStr(Row)) End Function

Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

Глобальные переменные

Option Explicit ' про эту строчку я уже рассказывал Dim CurRow As Integer Const GroupsCount As Integer = 2 Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice() Dim I As Integer ' строка в data CurRow = 1 Dim Groups(1 To GroupsCount) As String Dim PrGroups(1 To GroupsCount) As String


    Sheets(

"data").Activate I = 2 Do While True If GetCell(0, I).Value = "" Then Exit Do ' ... I = I + 1 Loop End Sub

Теперь надо заполнить массив Groups:

На месте многоточия

Dim I2 As Integer For I2 = 1 To GroupsCount Groups(I2) = GetCell(I2, I) Next I2 ' ... For I2 = 1 To GroupsCount ' VB не умеет копировать массивы PrGroups(I2) = Groups(I2) Next I2 I = I + 1

И создать заголовки:

На месте многоточия в предыдущем куске

For I2 = 1 To GroupsCount If Groups(I2) <> PrGroups(I2) Then Dim I3 As Integer For I3 = I2 To GroupsCount AddHeader I3, Groups(I3) Next I3 Exit For End If Next I2

Не забудем про процедуру AddHeader:

Перед FormatPrice

Sub AddHeader(Ty As Integer, Name As String) GetCellS("result", 1, CurRow).Value = Name CurRow = CurRow + 1 End Sub

Теперь надо перенести всякую информацию в result

For I2 = 0 To DataCount - 1 GetCellS("result", I2, CurRow).Value = GetCell(I2, I) Next I2

Подогнать столбцы по ширине и выбрать лист result для показа результата

После цикла в конце FormatPrice

Sheets("Result").Activate Columns.AutoFit

Всё. Можно любоваться первой версией.

Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

Sub AddHeader(Ty As Integer, Name As String) Sheets("result").Range("A" + CStr(CurRow) + ":C" + CStr(CurRow)).Merge ' Чтобы не заводить переменную и не писать каждый раз длинный вызов ' можно воспользоваться блоком With With GetCellS("result", 0, CurRow) .Value = Name .Font.Italic = True .Font.Name = "Cambria" Select Case Ty Case 1 ' Тип .Font.Bold = True .Font.Size = 16 Case 2 ' Производитель .Font.Size = 12 End Select .HorizontalAlignment = xlCenter End With CurRow = CurRow + 1 End Sub

Уже лучше:

Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

Поэтому чуть-чуть меняем код с добавлением стиля границ:

Sub AddHeader(Ty As Integer, Name As String) With Sheets("result").Range("A" + CStr(CurRow) + ":C" + CStr(CurRow)) .Merge .Value = Name .Font.Italic = True .Font.Name = "Cambria" .HorizontalAlignment = xlCenterSelect Case Ty Case 1 ' Тип .Font.Bold = True .Font.Size = 16 .Borders(xlTop).Weight = xlThick Case 2 ' Производитель .Font.Size = 12 .Borders(xlTop).Weight = xlMedium End Select .Borders(xlBottom).Weight = xlMedium ' По убыванию: xlThick, xlMedium, xlThin, xlHairline End With CurRow = CurRow + 1 End Sub

Осталось лишь добится пропусков перед началом новой группы. Это легко:

В начале FormatPrice

Dim I As Integer ' строка в data CurRow = 0 ' чтобы не было пропуска в самом начале Dim Groups(1 To GroupsCount) As String

В цикле расстановки заголовков

If Groups(I2) <> PrGroups(I2) Then CurRow = CurRow + 1 Dim I3 As Integer

В точности то, что и хотели.

Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: ~~CurRow = 0~~ CurRow = 1.

Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. ~~Если кто-нибудь подскажет человеческих файлохостинг, залью туда.~~

Спасибо за внимание.

Буду рад конструктивной критике в комментариях.

UPD: Перезалил пример на Dropbox и min.us.

UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

Источник

Introduction

This is a tutorial about writing code in Excel spreadsheets using Visual Basic for Applications (VBA).

Excel is one of Microsoft’s most popular products. In 2016, the CEO of Microsoft said «Think about a world without Excel. That’s just impossible for me.” Well, maybe the world can’t think without Excel.

In 1996, there were over 30 million users of Microsoft Excel (source).
Today, there are an estimated 750 million users of Microsoft Excel. That’s a little more than the population of Europe and 25x more users than there were in 1996.

We’re one big happy family!

In this tutorial, you’ll learn about VBA and how to write code in an Excel spreadsheet using Visual Basic.

Prerequisites

You don’t need any prior programming experience to understand this tutorial. However, you will need:

Basic to intermediate familiarity with Microsoft Excel
If you want to follow along with the VBA examples in this article, you will need access to Microsoft Excel, preferably the latest version (2019) but Excel 2016 and Excel 2013 will work just fine.
A willingness to try new things

Learning Objectives

Over the course of this article, you will learn:

What VBA is
Why you would use VBA
How to get set up in Excel to write VBA
How to solve some real-world problems with VBA

Important Concepts

Here are some important concepts that you should be familiar with to fully understand this tutorial.

Objects: Excel is object-oriented, which means everything is an object — the Excel window, the workbook, a sheet, a chart, a cell. VBA allows users to manipulate and perform actions with objects in Excel.

If you don’t have any experience with object-oriented programming and this is a brand new concept, take a second to let that sink in!

Procedures: a procedure is a chunk of VBA code, written in the Visual Basic Editor, that accomplishes a task. Sometimes, this is also referred to as a macro (more on macros below). There are two types of procedures:

Subroutines: a group of VBA statements that performs one or more actions
Functions: a group of VBA statements that performs one or more actions and returns one or more values

Note: you can have functions operating inside of subroutines. You’ll see later.

Macros: If you’ve spent any time learning more advanced Excel functionality, you’ve probably encountered the concept of a “macro.” Excel users can record macros, consisting of user commands/keystrokes/clicks, and play them back at lightning speed to accomplish repetitive tasks. Recorded macros generate VBA code, which you can then examine. It’s actually quite fun to record a simple macro and then look at the VBA code.

Please keep in mind that sometimes it may be easier and faster to record a macro rather than hand-code a VBA procedure.

For example, maybe you work in project management. Once a week, you have to turn a raw exported report from your project management system into a beautifully formatted, clean report for leadership. You need to format the names of the over-budget projects in bold red text. You could record the formatting changes as a macro and run that whenever you need to make the change.

What is VBA?

Visual Basic for Applications is a programming language developed by Microsoft. Each software program in the Microsoft Office suite is bundled with the VBA language at no extra cost. VBA allows Microsoft Office users to create small programs that operate within Microsoft Office software programs.

Think of VBA like a pizza oven within a restaurant. Excel is the restaurant. The kitchen comes with standard commercial appliances, like large refrigerators, stoves, and regular ole’ ovens — those are all of Excel’s standard features.

But what if you want to make wood-fired pizza? Can’t do that in a standard commercial baking oven. VBA is the pizza oven.

Yum.

Why use VBA in Excel?

Because wood-fired pizza is the best!

But seriously.

A lot of people spend a lot of time in Excel as a part of their jobs. Time in Excel moves differently, too. Depending on the circumstances, 10 minutes in Excel can feel like eternity if you’re not able to do what you need, or 10 hours can go by very quickly if everything is going great. Which is when you should ask yourself, why on earth am I spending 10 hours in Excel?

Sometimes, those days are inevitable. But if you’re spending 8-10 hours everyday in Excel doing repetitive tasks, repeating a lot of the same processes, trying to clean up after other users of the file, or even updating other files after changes are made to the Excel file, a VBA procedure just might be the solution for you.

You should consider using VBA if you need to:

Automate repetitive tasks
Create easy ways for users to interact with your spreadsheets
Manipulate large amounts of data

Getting Set Up to Write VBA in Excel

Developer Tab

To write VBA, you’ll need to add the Developer tab to the ribbon, so you’ll see the ribbon like this.

To add the Developer tab to the ribbon:

On the File tab, go to Options > Customize Ribbon.
Under Customize the Ribbon and under Main Tabs, select the Developer check box.

After you show the tab, the Developer tab stays visible, unless you clear the check box or have to reinstall Excel. For more information, see Microsoft help documentation.

VBA Editor

Navigate to the Developer Tab, and click the Visual Basic button. A new window will pop up — this is the Visual Basic Editor. For the purposes of this tutorial, you just need to be familiar with the Project Explorer pane and the Property Properties pane.

Excel VBA Examples

First, let’s create a file for us to play around in.

Open a new Excel file
Save it as a macro-enabled workbook (. xlsm)
Select the Developer tab
Open the VBA Editor

Let’s rock and roll with some easy examples to get you writing code in a spreadsheet using Visual Basic.

Example #1: Display a Message when Users Open the Excel Workbook

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub Auto_Open()
MsgBox («Welcome to the XYZ Workbook.»)
End Sub

Save, close the workbook, and reopen the workbook. This dialog should display.

Ta da!

How is it doing that?

Depending on your familiarity with programming, you may have some guesses. It’s not particularly complex, but there’s quite a lot going on:

Sub (short for “Subroutine): remember from the beginning, “a group of VBA statements that performs one or more actions.”
Auto_Open: this is the specific subroutine. It automatically runs your code when the Excel file opens — this is the event that triggers the procedure. Auto_Open will only run when the workbook is opened manually; it will not run if the workbook is opened via code from another workbook (Workbook_Open will do that, learn more about the difference between the two).
By default, a subroutine’s access is public. This means any other module can use this subroutine. All examples in this tutorial will be public subroutines. If needed, you can declare subroutines as private. This may be needed in some situations. Learn more about subroutine access modifiers.
msgBox: this is a function — a group of VBA statements that performs one or more actions and returns a value. The returned value is the message “Welcome to the XYZ Workbook.”

In short, this is a simple subroutine that contains a function.

When could I use this?

Maybe you have a very important file that is accessed infrequently (say, once a quarter), but automatically updated daily by another VBA procedure. When it is accessed, it’s by many people in multiple departments, all across the company.

Problem: Most of the time when users access the file, they are confused about the purpose of this file (why it exists), how it is updated so often, who maintains it, and how they should interact with it. New hires always have tons of questions, and you have to field these questions over and over and over again.
Solution: create a user message that contains a concise answer to each of these frequently answered questions.

Real World Examples

Use the MsgBox function to display a message when there is any event: user closes an Excel workbook, user prints, a new sheet is added to the workbook, etc.
Use the MsgBox function to display a message when a user needs to fulfill a condition before closing an Excel workbook
Use the InputBox function to get information from the user

Example #2: Allow User to Execute another Procedure

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub UserReportQuery()
Dim UserInput As Long
Dim Answer As Integer
UserInput = vbYesNo
Answer = MsgBox(«Process the XYZ Report?», UserInput)
If Answer = vbYes Then ProcessReport
End Sub

Sub ProcessReport()
MsgBox («Thanks for processing the XYZ Report.»)
End Sub

Save and navigate back to the Developer tab of Excel and select the “Button” option. Click on a cell and assign the UserReportQuery macro to the button.

Now click the button. This message should display:

Click “yes” or hit Enter.

Once again, tada!

Please note that the secondary subroutine, ProcessReport, could be anything. I’ll demonstrate more possibilities in example #3. But first…

How is it doing that?

This example builds on the previous example and has quite a few new elements. Let’s go over the new stuff:

Dim UserInput As Long: Dim is short for “dimension” and allows you to declare variable names. In this case, UserInput is the variable name and Long is the data type. In plain English, this line means “Here’s a variable called “UserInput”, and it’s a Long variable type.”
Dim Answer As Integer: declares another variable called “Answer,” with a data type of Integer. Learn more about data types here.
UserInput = vbYesNo: assigns a value to the variable. In this case, vbYesNo, which displays Yes and No buttons. There are many button types, learn more here.
Answer = MsgBox(“Process the XYZ Report?”, UserInput): assigns the value of the variable Answer to be a MsgBox function and the UserInput variable. Yes, a variable within a variable.
If Answer = vbYes Then ProcessReport: this is an “If statement,” a conditional statement, which allows us to say if x is true, then do y. In this case, if the user has selected “Yes,” then execute the ProcessReport subroutine.

When could I use this?

This could be used in many, many ways. The value and versatility of this functionality is more so defined by what the secondary subroutine does.

For example, maybe you have a file that is used to generate 3 different weekly reports. These reports are formatted in dramatically different ways.

Problem: Each time one of these reports needs to be generated, a user opens the file and changes formatting and charts; so on and so forth. This file is being edited extensively at least 3 times per week, and it takes at least 30 minutes each time it’s edited.
Solution: create 1 button per report type, which automatically reformats the necessary components of the reports and generates the necessary charts.

Real World Examples

Create a dialog box for user to automatically populate certain information across multiple sheets
Use the InputBox function to get information from the user, which is then populated across multiple sheets

Example #3: Add Numbers to a Range with a For-Next Loop

For loops are very useful if you need to perform repetitive tasks on a specific range of values — arrays or cell ranges. In plain English, a loop says “for each x, do y.”

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub LoopExample()
Dim X As Integer
For X = 1 To 100
Range(«A» & X).Value = X
Next X
End Sub

Save and navigate back to the Developer tab of Excel and select the Macros button. Run the LoopExample macro.

This should happen:

Etc, until the 100th row.

How is it doing that?

Dim X As Integer: declares the variable X as a data type of Integer.
For X = 1 To 100: this is the start of the For loop. Simply put, it tells the loop to keep repeating until X = 100. X is the counter. The loop will keep executing until X = 100, execute one last time, and then stop.
Range(«A» & X).Value = X: this declares the range of the loop and what to put in that range. Since X = 1 initially, the first cell will be A1, at which point the loop will put X into that cell.
Next X: this tells the loop to run again

When could I use this?

The For-Next loop is one of the most powerful functionalities of VBA; there are numerous potential use cases. This is a more complex example that would require multiple layers of logic, but it communicates the world of possibilities in For-Next loops.

Maybe you have a list of all products sold at your bakery in Column A, the type of product in Column B (cakes, donuts, or muffins), the cost of ingredients in Column C, and the market average cost of each product type in another sheet.

You need to figure out what should be the retail price of each product. You’re thinking it should be the cost of ingredients plus 20%, but also 1.2% under market average if possible. A For-Next loop would allow you to do this type of calculation.

Real World Examples

Use a loop with a nested if statement to add specific values to a separate array only if they meet certain conditions
Perform mathematical calculations on each value in a range, e.g. calculate additional charges and add them to the value
Loop through each character in a string and extract all numbers
Randomly select a number of values from an array

Conclusion

Now that we’ve talked about pizza and muffins and oh-yeah, how to write VBA code in Excel spreadsheets, let’s do a learning check. See if you can answer these questions.

What is VBA?
How do I get set up to start using VBA in Excel?
Why and when would you use VBA?
What are some problems I could solve with VBA?

If you have a fair idea of how to you could answer these questions, then this was successful.

Whether you’re an occasional user or a power user, I hope this tutorial provided useful information about what can be accomplished with just a bit of code in your Excel spreadsheets.

Happy coding!

Learning Resources

Excel VBA Programming for Dummies, John Walkenbach
Get Started with VBA, Microsoft Documentation
Learning VBA in Excel, Lynda

A bit about me

I’m Chloe Tucker, an artist and developer in Portland, Oregon. As a former educator, I’m continuously searching for the intersection of learning and teaching, or technology and art. Reach out to me on Twitter @_chloetucker and check out my website at chloe.dev.

Learn to code for free. freeCodeCamp’s open source curriculum has helped more than 40,000 people get jobs as developers. Get started

Источник

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

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

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

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

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

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

Пример 1

Пример 2

Пример 3

2. Объектная модель Excel: понятие объекта, метода, свойства, события.

Объекты Excel Лекция из курса «Основы офисного программирования и документы Excel»

Оглавление

Объектная модель Excel

Объект Excel Application

Общие объекты и Excel.Application

Свойства — участники объекта

Терминальные свойства

Методы объекта Application

Чуть-чуть подготовки и постановка задачи

Кодим

Примеры синтаксиса

Кодим много и под Excel

Работа с диапазонами ячеек

Примеры работы с Range

Глобальные переменные

FormatPrice

На месте многоточия

На месте многоточия в предыдущем куске

Перед FormatPrice

После цикла в конце FormatPrice

В начале FormatPrice

В цикле расстановки заголовков

Introduction

Prerequisites

Learning Objectives

Important Concepts

What is VBA?

Why use VBA in Excel?

Getting Set Up to Write VBA in Excel

Developer Tab

VBA Editor

Excel VBA Examples

Example #1: Display a Message when Users Open the Excel Workbook

How is it doing that?

When could I use this?

Real World Examples

Example #2: Allow User to Execute another Procedure

How is it doing that?

When could I use this?

Real World Examples

Example #3: Add Numbers to a Range with a For-Next Loop

How is it doing that?

When could I use this?

Real World Examples

Conclusion

Learning Resources

A bit about me

А вот еще интересные статьи:

Объекты Excel
Лекция из курса «Основы офисного программирования и документы Excel»