Основы программирования VBA и создание макросов в приложениях Mіcrosoft Offіce. Примеры задач Visual Basic for ApplicationVBA Помощь репетитора

Для начала в качестве примера рассмотрим простую процедуру VBA типа Sub. Она хранится в модуле VBA и вычисляет сумму первых ста положительных целых чисел. По окончании вычислений процедура отображает сообщение с результатом.

Sub VBA_Demo()
" Пример простой процедуры VBA
Dim Total As Long, i As Long
Total = 0
For i = 1 To 100
Total = Total + i
Next i
MsgBox Total
End Sub

Скачать заметку в формате или

В этой процедуре применяются некоторые популярные элементы языка:

• комментарий (строка, начинающаяся апострофом);
• оператор объявления переменной (строка, начинающаяся ключевым словом Dim);
• две переменные (Total и i);
• два оператора присваивания (Total = 0 и Total = Total + i);
• циклическая структура (For–Next);
• функция VBA (MsgBox).

Комментарии

Вы можете использовать для комментария новую строку либо вставить комментарий после инструкции в той же строке.

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

Переменные, типы данных и константы

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

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

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

Рекомендуется выбирать тот тип данных, в котором используется минимальное количество байтов для хранения значений. Для проведения математических вычислений в рабочих листах Excel использует тип данных Double. Его рекомендуется применять и в процессе обработки чисел в VBA для обеспечения той же точности вычислений.

Существуют три типы областей действия переменных:

Работа с константами

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

Для объявления констант используется оператор Const. Например,

Const NumQuarters as Integer = 4

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

Sub SetToLandscape ()
ActiveSheet.PageSetup.Orientation = xlLandscape
End Sub

Фактическое значение переменной xlLandscape равно 2. Окно Object Browser содержит список всех констант Excel и VBA. Чтобы открыть Object Browser в VBE, нажмите клавишу .

В VBA дата и время определяются как значения, заключенные между знаками #

Const FirstDay As Date = #1/1/2007#
Const Noon = #12:00:00#

Даты всегда определяются в формате " месяц/день/год " , даже если система настроена на отображение данных в другом формате.

Операторы присваивания

Оператор присваивания - это инструкция VBA, выполняющая математическое вычисление и присваивающая результат переменной или объекту. В справочной системе Excel выражение определяется как комбинация ключевых слов, операторов, переменных и констант. Эта комбинация возвращает в результате строку, число или объект. Выражение может выполнять вычисление, обрабатывать символы или тестировать данные.

Большое количество операций, выполняемых в VBA, связано с разработкой (и отладкой) выражений. Если вы знаете, как создавать формулы в Excel, то у вас не будет возникать проблем с созданием выражений в VBA. В формуле рабочего листа Excel результат отображается в ячейке. С другой стороны, выражение VBA может присваивать значение переменной или использоваться как значение свойства. В VBA оператором присваивания выступает знак равенства (=).

Массивы

Массив - это группа элементов одного типа, которые имеют общее имя; на конкретный элемент массива ссылаются, используя имя массива и индекс. Например, можно определить массив из 12 строк так, чтобы каждая переменная соответствовала названию месяца. Если вы назовете массив MonthNames, то можете обратиться к первому элементу массива как MonthNames (0), ко второму - как MonthNames (1) и так до MonthNames (11).

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

Dim MyArray(1 То 100) As Integer

По умолчанию в массивах VBA в качестве первого элемента используется нуль. Если вы хотите, чтобы в качестве первого индекса всех массивов использовалась единица, то перед первой процедурой модуля нужно сделать следующее объявление: Option Base 1

Динамический массив не имеет предопределенного количества элементов. Он объявляется с незаполненными значениями в скобках: Dim MyArray() As Integer. Тем не менее, прежде чем динамический массив можно будет использовать в программе, необходимо обратиться к оператору ReDim, указывающему VBA, сколько элементов находится в массиве. Для этого часто применяется переменная, значение которой неизвестно до тех пор, пока процедура не будет запущена на выполнение. Например, если переменной х присвоено число, размер массива определяется с помощью следующего оператора: ReDim MyArray (1 to х).

Объектные переменные

Объектая переменная - это переменная, представляющая целый объект, например, диапазон или рабочий лист: Dim InputArea As Range. Для присваивания объекта переменной воспользуйтесь ключевым словом Set: Set InputArea = Range(«С16:Е16»).

Встроенные функции

В VBA есть ряд встроенных функций, упрощающих вычисления и операции. Например, функция VBA UCase, преобразующая строку в верхний регистр, эквивалентна функции Excel ПРОПИСН. Чтобы использовать функцию Excel в операторе VBA, перед названием функции введите следующее выражение:

Application.WorksheetFunction

Важно понимать, что вы не можете использовать функции Excel, для которых в VBA представлены эквивалентные функции. Например, VBA не позволяет получить доступ к функции Excel КОРЕНЬ (SQRT), так как в VBA имеется собственная версия этой функции: Sqr. Таким образом, следующий оператор выдает ошибку:

MsgBox Application.WorksheetFunction.Sqrt(123)

Функция MsgBox - одна из самых полезных в VBA. Кроме прочего, это превосходный инструмент отладки, поскольку вы можете в любое время вставить функцию MsgBox, чтобы приостановить программу и отобразить результат вычисления или присваивания. Функция MsgBox не только возвращает значение, но и отображает диалоговое окно, в котором пользователь может выполнить определенные действия. Значение, возвращаемое функцией MsgBox, является ответом пользователя на отображенный запрос. Функция MsgBox может применяться даже в том случае, когда ответ пользователя не требуется, а нужно отобразить сообщение. Синтаксис функции MsgBox:

MsgBox(сообщение[, кнопки] [, заголовок] [, файл_справки, контекст])

• Сообщение (обязательный аргумент) - сообщение, которое отображается в диалоговом окне.
• Кнопки (необязательный аргумент) - значение, определяющее, какие кнопки и пиктограммы (если нужно) отображаются в окне сообщения. Применяйте встроенные константы (например, vbYesNo).
• Заголовок (необязательный аргумент) - текст, который отображается в строке заголовка окна сообщения. По умолчанию отображается текст Microsoft Excel.
• Файл_справки (необязательный аргумент) - название файла справки, соответствующего окну сообщения.
• Контекст (необязательный аргумент) - контекстный идентификатор раздела справки. Представляет конкретный раздел справки для отображения. Если используется аргумент контекст, следует также задействовать аргумент файл_справки.

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

Ans = MsgBox(" Продолжить? " , vbYesNo + vbQuestion, " Сообщи ")
If Ans = vbNo Then Exit Sub

Обратите внимание, что в качестве значения аргумента кнопки используется сумма двух встроенных констант (vbYesNo + vbQuestion). Благодаря константе vbYesNo в окне сообщения отображаются две кнопки: одна с меткой Yes, а вторая - с меткой No. Добавление vbQuestion в состав аргумента также приведет к отображению значка вопроса. Как только будет выполнен первый оператор, переменная Ans получит одно из двух значений, представленных константами vbYes и vbNo. В этом примере процедура завершает свою работу после щелчка на кнопке No.

Управление объектами и коллекциями

VBA предлагает две конструкции, которые помогут вам упростить управление объектами и коллекциями. Конструкция With — End With позволяет выполнять несколько операций над одним объектом. Чтобы понять, как она работает, проанализируйте следующую процедуру, которая изменяет шесть свойств выделенного объекта (подразумевается, что выделен объект Range).

Sub ChangeFontl()
Selection.Font.Name = " Cambria "
Selection.Font.Bold = True Selection.Font.Italic = True
Selection.Font.Size = 12
Selection.Font.Underline = xlUnderlineStyleSingle
Selection.Font.ThemeColor = xlThemeColorAccentl
End Sub

Эту процедуру можно переписать с помощью конструкции With — End With. Процедура, показанная ниже, работает точно так же, как и предыдущая.

Sub ChangeFont2 ()
With Selection.Font
.Name = " Cambria "
.Bold = True
.Italic = True
.Size = 12
.Underline = xlUnderlineStyleSingle
.ThemeColor = xlThemeColorAccentl
End With
End Sub

Предположим, вы решили выполнить действие над всеми объектами коллекции или вам необходимо оценить все объекты коллекции и совершить действие при выполнении определенных условий. Это идеальная ситуация для применения конструкции For Each — Next. Синтаксис конструкции:

For Each элемент In коллекция
[инструкции ]
[инструкции ]
Next [элемент ]

Например:

Sub CountSheets()
Dim Item as Worksheet
For Each Item In ActiveWorkbook.Worksheets
MsgBox Item.Name
Next Item
End Sub

В следующем примере закрываются все окна, за исключением активного:

Sub Closelnactive()
Dim Book as Workbook
For Each Book In Workbooks
If Book.Name <> ActiveWorkbook.Name Then Book.Close
Next Book
End Sub

Контроль за выполнением кода

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

Оператор GoTo перенаправляет ход выполнения программы на новую инструкцию, которая помечена специальным образом (текстовая строка, заканчивающаяся двоеточием, или число, заканчивающееся пробелом, указанные перед инструкцией). В приведенной ниже процедуре применена функция VBA InputBox для получения имени пользователя. Если имя пользователя отличается от Ховард, то процедура переходит к метке WrongName, на чем заканчивает свою работу. В противном случае процедура выполняет дополнительные операции. Оператор Exit Sub заканчивает выполнение процедуры.

Sub GoToDemo()
UserName = InputBox(" Введите свое имя: ")
If UserName <> " Ховард " Then GoTo WrongName
MsgBox (" Привет, Ховард… ")
" — [Здесь вводится дополнительный код] —
Exit Sub
WrongName:
MsgBox " Извините, эту процедуру может запускать только Ховард. "
End Sub

В действительности оператор GoTo необходим только для перехват ошибок (код выше является неудачным примером, который не следует использовать).

Вероятно, конструкция If-Then чаще остальных используется для группирования инструкций VBA:

If условие Then инструкции_истина

Например

Sub GreetMe()
If Time < 0.5 Then
MsgBox " Доброе утро "
Elself Time >= 0.5 And Time < 0.75 Then
MsgBox " Добрый день "
Else
MsgBox " Добрый вечер "
End If
End Sub

VBA использует систему дат и времени, похожую на задействованную в Excel. Время дня выражается дробным числом, например, полдень представлен как 0.5. Значение 0.75 представляет время 18:00 - три четверти суток и тот момент, когда день переходит в вечер. Вложенные структуры If-Then достаточно громоздкие. Поэтому рекомендуется использовать их только для принятия простых бинарных решений. Если же необходимо выбрать между тремя и более вариантами, то целесообразно обратиться к конструкции Select Case.

В следующей процедуре используется функция VBA WeekDay, с помощью которой определяется, является ли текущий день субботой либо воскресеньем (функция Weekday возвращает значение 1 либо 7). Затем отображается соответствующее сообщение.

Sub GreetUserlO
Select Case Weekday(Now)
Case 1, 7
MsgBox " Это выходные "
Case Else
MsgBox " Это не выходные "
End Select
End Sub

Интерпретатор VBA осуществляет выход из конструкции Select Case, как только найдено условие True. Следовательно, для максимальной эффективности, в первую очередь, следует выполнить проверку наиболее вероятного случая.

Цикл - это процесс повторения набора инструкций. Возможно, вы заранее знаете, сколько раз должен повториться цикл, или это значение определяется переменными в программе. Простейший пример хорошего цикла - For-Next:

For счетчик = начало То конец
[инструкции ]
[инструкции ]
Next [счетчик ]

Следующая процедура суммирует квадратные корни первых 100 целых чисел:

Sub SumSquareRoots()
Dim Sum As Double
Dim Count As Integer
Sum = 0
For Count = 1 To 100
Sum = Sum + Sqr(Count)
Next Count
MsgBox Sum
End Sub

Значение переменной Step в цикле For-Next может быть отрицательным. Приведенная ниже процедура удаляет строки 2, 4, 6, 8 и 10 в активном листе:

Sub DeleteRows ()
Dim RowNum As Long
For RowNum = 10 To 2 Step -2
Rows(RowNum).Delete
Next RowNum
End Sub

Циклы For-Next могут также содержать один или более операторов Exit For. Когда программа встречает этот оператор, то сразу же выходит из цикла:

Sub ExitForDemo()
Dim MaxVal As Double
Dim Row As Long
MaxVal = Application.WorksheetFunction.Max(Range(" A:A "))
For Row = 1 To 1048576
If Cells(Row, 1).Value = MaxVal Then
Exit For
End If
Next Row
MsgBox " Максимальное значение в строке " & Row
Cells(Row, 1).Activate
End Sub

Максимальное значение в столбце вычисляется с помощью функции Excel МАХ. Затем это значение присваивается переменной MaxVal. Цикл For-Next проверяет каждую ячейку в столбце. Если определенная ячейка равна MaxVal, оператор Exit For заканчивает процедуру. Однако перед выходом из цикла процедура сообщает пользователю о расположении искомой ячейки и активизирует ее.

Цикл Do While выполняется до тех пор, пока удовлетворяется заданное условие. Цикл Do While может иметь один из двух представленных ниже синтаксисов.

Do
[инструкции ]
[инструкции ]
Loop

Do
[инструкции ]
[инструкции ]
Loop

Процедура EnterDates1 вводит даты текущего месяца в столбец рабочего листа, начиная с активной ячейки:

Sub EnterDatesl ()
" цикл Do While, условие проверяется в начале
Dim TheDate As Date
TheDate = DateSerial(Year(Date), Month(Date), 1)
Do While Month(TheDate) = Month(Date)
ActiveCell = TheDate
TheDate = TheDate + 1
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

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

Циклы Do While также могут включать один или более операторов Exit Do. По достижении оператора Exit Do цикл завершается, а управление передается оператору, следующему за оператором Loop.

Структура цикла Do Until имеет много общего с конструкцией Do While. Разница заключается лишь в том, как проверяется условие цикла. В варианте Do While цикл выполняется до тех пор, пока выполняется условие. В цикле Do Until цикл выполняется, пока условие не станет выполняться. Структура Do Until также может быть представлена двумя видами синтаксиса.

По материалам книги . – М: Диалектика, 2013. – С. 211–251.

(Visual Basic for Application)

Basic- язык программирования высокого уровня (интерпретатор)

Visual- содержит наглядные средства по разработке программ (кодов), упрощающие работу пользователя, позволяет записывать коды с помощью макрорекодера.

Application- приложение к программной системеMSOfficeнаряду с приложениямиWord,Access,PowerPoint.

Зачем нужен VBA?

Объединяет (интегрирует) приложения, позволяет управлять работой других приложений не выходя из Excel, внедрять объекты из других приложений;

Действия доступные пользователю на рабочем листе составляют 10% от всех возможностей приложения Excel,VBAпозволяет автоматизировать работу вашего проекта.

VBAотносится к числу объектно-ориентированных языков, то есть при разработке проектов используются объекты.Определение: объединение данных и кода в единое целоею Например, элемент «Кнопка» и соответствующий код, связанный с этой кнопкой обеспечат переход на другой лист рабочей книги.

Основные объекты VBA:

Application (само приложение Excel)

WorkBook(рабочая книга – ваш файл)

WorkSheetFunction (мастер функций)

WorkSheet (рабочий лист)

Range (диапазон)

Chart (диаграмма)

Style (стиль)

Border (границы)

Interior (цвет фона)

Font (шрифт)

Множество некоторых объектов составляют семейства –WorkBooks,WorkSheets,Charts.

Объекты обладают свойствами (действия над объектами) и методами (дейстия самих объектов).

Начнем знакомиться со средствами визуализации разработки проектов в VBA. Таковым является Интегрированная Среда Разработки Приложений. Чтобы попасть в эту среду надо выбрать в пункте меню СервисМакросРедакторVBAили нажать одновременно клавишиALTиF11.

На экране появятся компоненты редактора VBA:

Окно проекта Project – VBA Project

Окно свойствPropeties

Окно кода

Окно форм UserForm

Панели инструментов

Окно проекта Project–VBAProject(рис. 1) показывает структуру вашего проекта (файла). Это окно активизируется в редактореVBAвыбором командыViewProjectExplorerили кнопкой «ProjectExplorer» или нажатием клавишCtrl+R

Рис. 1 Окно проекта.

Рис.2 Интегрированная среда разработки приложений

Окно кода предназначено для хранения кода, связанного с объектом. У каждого объекта свое окно, так каждый рабочий лист (WorkSheet) имеет свое окно кода, рабочая книга (WorkBook) – свое окно.

Создание пользовательских функций

Пользовательские функции добавляются к стандартному списку мастера функций (WorkSheetFunction). Эти функции создаются в специальном модуле, сопровождающем объектWorkSheetFunction. Этот модуль добавляется к проекту с помощью командыInsertModule(ВставитьМодуль), в окне проекта он отобразится на уровне вашего приложения. Все коды, написанные в этом модуле, появятся в категории «Функции определенные пользователем» мастера функций.

Итак, добавляем модуль в наш проект (InsertModule) и в окне кода этого модуля пишем текст программы:

y = Cos((x + 2) / 2) ^ 2 + Exp(-2 * x) / (x ^ 2 + 1) ^ 0.5

Затем переходим на рабочий лист «1 график», в ячейке с2 выполним обращение к новой функции, добавленной в мастер функций - y(x). Работа с этой функцией ничем не отличается от работы с любой другой функцией. На первом шаге надо выбратьy(x) в категории «Функции определенные пользователем», на втором шаге в качестве аргументаxуказать ячейкуA2. В результате в ячейке С2 будет записана формула =y(A2). Эту формулу протащить на весь диапазон А2:А17 как показано на рис. 3. Разумеется, результат должен совпасть с тем, что вы получили, вычисляя эту функцию обычными средствами рабочего листа.

Рис 3. Функция y(x), рассчитанная обычным образом и с помощью пользовательской функции.

Основные операторы vba – Условные операторы

Условные операторы имеют 2 формы записи:

1) В одну строку

IF< условие>THEN<оператор 1>

IF,THEN,ELSE–служебные неизменяемые слова, в угловых скобках < >текст пользователя, это то, что вы пишете в соответствии с заданием, в квадратных скобках необязательная часть, текст может отсутствовать. Такая форма обычно используется в случае простых действий, например, при вычислении модуля числаy=х=abs(s) можно использовать следующий оператор:

IF x > 0 THEN y = x ELSE y = -x

2) В несколько строк. В этом случае условный оператор обязательно заканчивается утверждением “ENDIF”

IF <условие> THEN

<оператор 1>

<оператор 2>

<оператор 3>

<оператор 4>

Такая форма используется при сложных вычислениях, например, при вычислении корней квадратного уравнения. Пусть надо найти корни уравнения a*x 2 +b*x+c= 0. Как известно в случае еслиb 2 -4*a*c≥ 0, то корни вычисляются по формуле
, еслиb 2 -4*a*c≤ 0, то корней в области действительных чисел нет. Условный оператор, реализующий этот алгоритм, выглядит следующим образом:

IF b^2 -4*a*c>= 0 THEN

X1 = (-b + (b^2 - 4*a*c)^(1/2)) / (2*a)

X2 = (-b + (b^2 + 4*a*c)^(1/2)) / (2*a)

X1 = “ РЕШЕНИЯ НЕТ”

X2 = “ РЕШЕНИЯ НЕТ”

Примеры написания пользовательских функций в vba

Пример 1.

F
unction y(x)

y = Cos((x + 2) / 2) ^ 2 + Exp(-2 * x) / (x ^ 2 + 1) ^ 0.5

End Function

Пример 2

Function z(x)

If x < 0 Then

z = (1 + x + x ^ 2) / (1 + x ^ 2)

If x < 1 Then

z = (1 + 2 * x / (1 + x ^ 2)) ^ (1 / 2)

z = 2 * Abs(0.5 + Sin(x))

End Function

Лекция 2

Свойства, методы и события объектов VBA. Пример процедуры с использованием отдельных свойств объектаApplication.

Элементы управления

Типы переменных в VBA

Операторы цикла

Пример программы табулирования функции

Свойства, методы и события объектов VBA. Пример процедуры с использованием отдельных свойств объектаApplication.

Продолжим знакомство с объектной моделью VBA. Все объекты организованы в иерархическую структуру, подчиненную объекту верхнего уровня «Application».

Application. WorkBooks(“Графики”).

Если рабочая книга (файл) “Графики” является активной, то достаточно указать

Worksheets(“Содержание”).Range(“A1”).

Если же вы работаете на листе “Содержание” , то ссылка будет выглядеть Range(“A1”).

Все объекты имеют свойства, методы и события.

Свойство это некоторая характеристика объекта (цвет, форма, наименование, расположение, видимость и т.д.) Устанавливается значение объекта так:

Объект.Свойство = значение свойства

Метод это действие, выполняемое над объектом (открыть, закрыть, удалить). Правило записи метода:

Объект. Метод

Событие это действие, распознаваемое объектом (щелчок мышью, двойной щелчок, нажатие клавиши).

Суть программирования в VBAсостоит в двух понятиях – событии и отклике на него. Если пользователь производит воздействие на систему (нажатие кнопки), что является событием, то средствамиVBAможно запрограммировать отклик – ответное действие.

Приведем некоторые свойства, методы и события основных объектов.

Свойства метода Applicationпозволяют программно установить значения многих опций окна Сервис/Параметры. Например, с помощью показанных ниже процедур можно изменить стандартный вид окнаExcel, изменить заголовок, отключить действие перетаскивания формул.

Данные процедуры записываются в модуле «Эта книга» и выполняются при открытии и закрытии рабочей книги, то есть при выполнении событий OpenиBeforeClose:

Private Sub workbook_open()

" Заголовок рабочей книги

Application.Caption = "Киса и Ося были здесь"

" Цвет фона диапазона A1:D1 -Красный

"Границы диапазона A1:D1 - пунктир

"отменяется перетаскивание ячеек CellDragAndDrops

Application.CellDragAndDrop = Falsе

‘ убирается строка формул

Application.DisplayFormulaBar = False

‘убираются полосы прокрутки

Application.DisplayScrollBars = False

“устанавливается стиль ссылок R1C1

Application.ReferenceStyle = xlR1C1

"Private Sub Workbook_BeforeClose(Cancel As Boolean)

"Восстанавливается перетаскивание ячеек CellDragAndDrops

Application.CellDragAndDrop = True

‘Восстанавливается строка формул

Application.DisplayFormulaBar = True

‘Восстанавливаются полосы прокрутки

Application.DisplayScrollBars = True

‘Восстанавливается стиль ссылок А1

Application.ReferenceStyle = xlA1

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

Что такое VBA

Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.

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

Недостатком программы являются проблемы, связанные с совместимостью различных версий. Они обусловлены тем, что код программы VBA обращается к функциональным возможностям, которые присутствуют в новой версии продукта, но отсутствуют в старой. Также к минусам относят и чрезмерно высокую открытость кода для изменения посторонним лицом. Тем не менее Microsoft Office, а также IBM Lotus Symphony позволяют пользователю применять шифрование начального кода и установку пароля для его просмотра.

Объекты, коллекции, свойства и методы

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

Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.

Что касается понятия "коллекция", то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.

Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в "Эксель" используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами

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

Как начать

Затем необходимо перейти в приложение VB, для чего достаточно воспользоваться комбинацией клавиш «Alt» и «F11». Далее:

• в строке меню, расположенном в верхней части окна, нажимают на иконку рядом с иконкой Excel;
• выбирают команду Mudule;
• сохраняют, нажав на иконку с изображением ;
• пишут, скажем так, набросок кода.

Он выглядит следующим образом:

Sub program ()

"Наш код

Обратите внимание, что строка «"Наш код» будет выделена другим цветом (зеленым). Причина в апострофе, поставленном в начале строки, который обозначает, что далее следует комментарий.

Теперь вы можете написать любой код и создать для себя новый инструмент в VBA Excel (примеры программ см. далее). Конечно, тем, кто знаком с азами Visual Basic, будет намного проще. Однако даже те, кто их не имеет, при желании смогут освоиться достаточно быстро.

Макросы в Excel

За таким названием скрываются программы, написанные на языке Visual Basic for Application. Таким образом, программирование в Excel — это создание макросов с нужным кодом. Благодаря этой возможности табличный процессор Microsoft саморазвивается, подстраиваясь под требования конкретного пользователя. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению конкретных примеров программ VBA Excel. Лучше всего начать с самых элементарных кодов.

Пример 1

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

Для этого:

• открывают вкладку «Вид»;
• переходят на пиктограмму «Макросы»;
• жмут на «Запись макроса»;
• заполняют открывшуюся форму.

Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.

Теперь, когда уже запущена запись макроса, производят копирование содержимого какой-либо ячейки в другую. Возвращаются на исходную пиктограмму. Нажимают на «Запись макроса». Это действие означает завершение программки.

• вновь переходят на строку «Макросы»;
• в списке выбирают «Макрос 1»;
• нажимают «Выполнить» (то же действие запускается начатием сочетания клавиш «Ctrl+hh»).

В результате происходит действие, которое было осуществлено в процессе записи макроса.

Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.

Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.

Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.

Пример 2

Циклы VBA помогают создавать различные макросы в Excel.

Циклы VBA помогают создавать различные макросы. Предположим, что имеется функция y=x + x 2 + 3x 3 - cos(x). Требуется создать макрос для получения ее графика. Сделать это можно только, используя циклы VBA.

За начальное и конечное значение аргумента функции берут x1=0 и x2=10. Кроме того, необходимо ввести константу — значение для шага изменения аргумента и начальное значение для счетчика.

Все примеры макросов VBA Excel создаются по той же процедуре, которая представлена выше. В данном конкретном случае код выглядит, как:

Sub programm()

shag = 0.1

Do While x1 < x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Cells(i, 1).Value = x1 (значение x1 записывается в ячейку с координатами (i,1))

Cells(i, 2).Value = y (значение y записывается в ячейку с координатами (i,2))

i = i + 1 (действует счетчик);

x1 = x1 + shag (аргумент изменяется на величину шага);

End Sub.

В результате запуска данного макроса в "Эксель" получаем два столбца, в первом из которых записаны значения для x, а во втором — для y.

Затем по ним строится график способом, стандартным для "Эксель".

Пример 3

Для реализации циклов в VBA Excel 2010, как и в других версиях, наряду с уже приведенной конструкцией Do While используется For.

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

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

For i = 1 to 10 Next

Команда переводится на «человеческий» язык, как «Повторять от 1 до 10 с шагом один».

Если ставится задача получить столбец с квадратами, например, всех нечетных чисел из диапазона от 1 до 11, то пишем:

For i = 1 to 10 step 1 Next.

Здесь step — шаг. В данном случае он равен двум. По умолчанию отсутствие этого слова в цикле означает, что шаг единичный.

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

В целом код будет выглядеть, как:

Sub program()

For i = 1 To 10 Step 1 (можно записать просто For i = 1 To 10)

Cells(i, 1).Value = i ^ 2 (т.е. в ячейку (i,1) записывается значение квадрата i)

Next (в некотором смысле играет роль счетчика и означает еще один запуск цикла)

End Sub.

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

Пример 4

В повседневной жизни сплошь и рядом возникает необходимость принять то или иное решение в зависимости от какого-то условия. Не обойтись без них и в VBA Excel. Примеры программ, где дальнейший ход выполнения алгоритма выбирается, а не предопределен изначально, чаще всего используют конструкцию If …Then (для сложных случаев) If …Then …END If.

Рассмотрим конкретный случай. Предположим, необходимо создать макрос для "Эксель", чтобы в ячейку с координатами (1,1) было записано:

1, если аргумент положительный;

0, если аргумент нулевой;

-1, если аргумент отрицательный.

Создание такого макроса для "Эксель" начинается стандартным способом, через использование «горячих» клавиш Alt и F11. Далее записывается следующий код:

Sub program()

x= Cells(1, 1).Value (эта команда присваивает x значение содержимого ячейки с координатами (1, 1))

If x>0 Then Cells(1, 1).Value = 1

If x=0 Then Cells(1, 1).Value = 0

If x<0 Then Cells(1, 1).Value = -1

End Sub.

Остается запустить макрос и получить в "Эксель" нужное значение для аргумента.

Функции VBA

Как вы уже могли заметить, программировать в самом известном табличном процессоре Microsoft не так уж сложно. Особенно, если научиться применять функции VBA. Всего в этом языке программирования, созданном специально для написания приложений в "Эксель" и Word, около 160 функций. Их можно разделить на несколько больших групп. Это:

• Математические функции. Применив их к аргументу, получают значение косинуса, натурального логарифма, целой части и пр.
• Финансовые функции. Благодаря их наличию и используя программирование в Excel, можно получать эффективные инструменты для ведения бухгалтерского учета и осуществления финансовых расчетов.
• Функции обработки массивов. К ним относятся Array, IsArray; LBound; UBound.
• Функции VBA Excel для строки. Это достаточно многочисленная группа. В нее входят, например, функции Space для создания строки с числом пробелов, равных целочисленному аргументу, или Asc для перевода символов в код ANSI. Все они имеют широкое применение и позволяют работать со строками в "Эксель", создавая приложения, значительно облегчающие работу с этими таблицами.
• Функции преобразования типа данных. Например, CVar возвращает значение аргумента Expression, преобразовав его в тип данных Variant.
• Функции работы с датами. Они значительно расширяют стандартные Так, функция WeekdayName возвращает название (полное или частичное) дня недели по его номеру. Еще более полезной является Timer. Он выдает число секунд, которые прошли с полуночи до конкретного момента дня.
• Функции для преобразования числового аргумента в разные системы счисления. Например, Oct выдает в восьмеричное представление числа.
• Функции форматирования. Важнейшей из них является Format. Она возвращает значение типа Variant с выражением, отформатированным согласно инструкциям, которые заданы в описании формата.
• и пр.

Изучение свойств этих функций и их применение позволит значительно расширить сферу применения "Эксель".

Пример 5

Попробуем перейти к решению более сложных задач. Например:

Дан бумажный документ отчета фактического уровня издержек предприятия. Требуется:

• разработать его шаблонную часть посредством табличного процессора "Эксель";
• составить программу VBA, которая будет запрашивать исходные данные для ее заполнения, осуществлять необходимые расчеты и заполнять ими соответствующие ячейки шаблона.

Рассмотрим один из вариантов решения.

Создание шаблона

Все действия осуществляются на стандартном листе в Excel. Резервируются свободные ячейки для внесения данных по названию компании-потребителя, сумме издержек, их уровня, товарооборота. Так как количество компаний (обществ), относительно которых составляется отчет, не зафиксировано, ячейки для внесения значений по итогам и ФИО специалиста заранее не резервируют. Рабочему листу присваивается новое название. Например, "Օтчет".

Переменные

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

• NN- номер текущей строки таблицы;
• TP и TF - планируемый и фактический товарооборот;
• SF и SP - фактическая и планируемая сумма издержек;
• IP и IF - планируемый и фактически уровень издержек.

Обозначим теми же буквами, но с «приставкой» Itog накопление итога по данному столбцу. Например, ItogTP - касается столбца таблицы, озаглавленного, как «планируемый товарооборот».

Решение задачи с использованием программирования на VBA

Используя введенные обозначения, получаем формулы для отклонений. Если требуется осуществить расчет в % имеем (F - P) / P * 100, а в сумме — (F - P).

Результаты этих вычислений можно лучше всего сразу внести в соответствующие ячейки таблицы "Эксель".

Для итогов по факту и прогнозу получают по формулам ItogP=ItogP + P и ItogF=ItogF+ F.

Для отклонений используют = (ItogF - ItogP) / ItogP * 100, если расчет ведется в процентах, а в случае суммарной величины — (ItogF - ItogP).

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

Перед запуском созданной программы, требуется сохранить рабочую книгу, например, под названием "Отчет1.xls".

Клавишу «Создать отчетную таблицу» требуется нажать всего 1 раз после ввода заголовочной информации. Следует знать и другие правила. В частности, кнопка «Добавить строку» должна нажиматься каждый раз после ввода в таблицу значений по каждому виду деятельности. После занесения всех данных требуется нажать кнопку «Закончить» и затем переключиться в окно "Эксель".

Теперь вы знаете, как решать задачи для Excel с помощью макросов. Умение применять vba excel (примеры программ см. выше) может понадобиться и для работы в среде самого популярного на данный момент текстового редактора "Ворд". В частности, можно путем записи, как показано в самом начале статьи, или через написание кода создавать кнопки меню, благодаря которым многие операции над текстом можно будет осуществлять нажатием дежурных клавиш или через вкладку "Вид" и пиктограмму "Макросы".

Мы вкратце познакомились с записью макроса при помощи рекордера Excel. Сейчас мы углубимся в эти дебри и напишем для примера пару простейших макросов. В одном из примеров по традиции продемонстрируем работу с диалоговым окном типа «Сообщение», в другом расширим наше диалоговое окно.

Пример 1. Простое диалоговое сообщение msgBox в VBA

Не будем отступать от традиций начала всех примеров программирования. Напишем макрос, который при запуске выдаст нам окно сообщения с надписью «Hello World». Заодно рассмотрим работу с пользовательскими диалогами.

Теперь писать макросы будем только в ручном режиме, никаких рекордеров!

Итак, для того чтоб создать макрос, Вам необходимо открыть окно всеми любимого редактора Visual Basic (VB). Для этого выполняем следующие действия: Сервис-Макрос-Редактор Visual Basic (Alt+F11).

Откроется окно редактора MS Visual Basic.

Если у Вас отсутствуют левые окна, то их необходимо включить. Для этого нажмите F4 – Открывает окно свойств Properties Window, и сочетание клавиш Ctrl+R – открывает окно Project Explorer. Без этих окон в дальнейшем затруднительно работать. Все! Сделали.

Что мы видим в окне Project ? В данном окне отражается как раз таки структура нашей книги. Объекты книги – Лист1, 2, 3, Эта книга. Более подробно изучим данные объекты в последующих статьях, а пока возвращаюсь к примеру.

Что необходимо сделать для того чтоб начать писать код? Необходимо создать модуль. Примечание: Вообще, в дальнейшем рекомендую разделять код обработчиков на разные модули. Это облегчит понимание кода и создаст порядок в структуре.

Создаем модуль: Insert – Module

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

Переходим в окно редактирования кода и пишем следующее:

Готово! Открываем окно рабочей книги Excel, жмем Alt+F8 и видим наш макрос «Hello»

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

Пример 2. Расширенное диалоговое сообщение msgBox в VBA

Рассмотрим еще один вид диалоговых сообщений, которые содержат дополнительные кнопки «Да», «Нет», «Отмена»

Переходим к коду нашего макроса «Hello» и дописываем к команде msgbox следующее:

MsgBox "Hello, World!", vbYesNoCancel, "Мой макрос"

Обратите внимание, когда вы поставите запятую после «Hello, World!», у Вас должен появиться список всех доступных типов диалоговых окон

Поэкспериментируйте с каждым для понимания.

Запустим наш макрос

Теперь у нас открылась совсем иная форма сообщения.

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

Видео: Пример работы msgbox в vba

Тема 2.3. Программные средства презентаций и основы офисного программирования

Тема 2.4. Системы управления базами данных и экспертные системы

2.4.11. Учебная база данных с главной кнопочной формой "Training_students" - Скачать

Программирования VBA и макросы

2.3. Программные средства презентаций и основы офисного программирования

2.3.7. Основы офисного программирования

Визуальный язык программирования Бейсик для приложений: Visual Basic for Applications (VBA)

VBA – это подмножество визуального языка программирования Visual Basic (VB), которое включает почти все средства создания приложений VB.

VBA отличается от языка программирования VB тем, что система VBA предназначена для непосредственной работы с объектами Office, в ней нельзя создавать проект независимо от приложений Office. Таким образом, в VBA языком программирования является VB, а инструментальная среда программирования реализована в виде редактора VB, который может активизироваться из любого приложения MS Office.

Например, для того, чтобы открыть редактор VBA из приложения PowerPoint необходимо выполнить команду Сервис / Макрос / Редактор VBA. Вернуться из редактора в приложение можно, выбрав команду Microsoft PowerPoint в меню Вид или комбинацией клавиш Alt + F11.

С помощью встроенного в редактор VBA набора элементов управления и редактора форм пользователь может создать пользовательский интерфейс для разрабатываемого проекта с экранной формой. Элементы управления являются объектами, а для каждого объекта определен ряд возможных событий (например, щелчок или двойной щелчок мыши, нажатие клавиши, перетаскивание объекта и т.д.).

Каждое событие проявляется в определенных действиях программы (откликах, реакции). Пользовательская форма позволяет создавать окна диалога приложений. Язык программирования VBA служит для написания кода программы, например для создания функций пользователя в Excel.

Тот факт, что система программирования VBA предназначена для работы с объектами Office, позволяет эффективно ее применять для автоматизации деятельности, связанной с разработкой различных типов документов.

Рассмотрим алгоритм создания функций пользователя в VBA:

1. Вызвать окно редактора кода VBA, выполнив команду Сервис / Макрос / Редактор Visual Basic или нажать Alt+F11.

2. Выполнить пункт меню редактора Insert / Module (Вставка / Модуля).

3. Далее выполнить Insert / Procedure (Вставка /Процедура). В открывшемся диалогом окне Add Procedure ввести имя функции (например, СУММА5) и установить переключатели: Type (тип) – в положение Function (функция); Scope (Область определения) - в положение Public (Общая) и щелкнуть ОК.

Рис. 1.

4. В окне редактора для программирования VBA появится заготовка функции: заголовок - Public Function СУММА5() и окончание - End Function, между которыми надо поместить код тела функции.

Рис. 2.

5. Далее вводим список параметров функции, например, в скобках указываем (x, y, z, i, j), тип данных (для точных расчетов) и тип возвращаемого функцией значения (в данном примере не будем вводить). Кроме того, вводим тело функции, например, СУММА5 = x + y + z + i + j. В итоге получим следующий текст программы:

Public Function СУММА5(x, y, z, i, j)
СУММА5 = x + y + z + i + j
End Function

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

7. Выполняем "Вставка/Функция" и в открывшемся окне мастера функций выбираем категорию «Определенные пользователем», а в окне «Выберите функцию» выделяем СУММА5 и щелкаем ОК.

Рис. 3.

Рис. 4.

Макросы

Создание макроса

Создадим макрос для автоматического суммирования двух чисел в ячейках A1, B1 и размещения результата в ячейке C1, а также заливки ячейки C1 бирюзовым цветом.

Алгоритм создания макроса для поставленной задачи:

1. Выберите Сервис/Макрос, Начать запись.

2. В поле Имя макроса введите имя для макроса. Первым символом имени макроса должна быть буква. В имени макроса не допускаются пробелы; в качестве разделителей слов можно использовать знаки подчеркивания.

3. Для того чтобы запускать макрос с помощью сочетания клавиш, введите букву в поле Сочетание клавиш. Допускается использование сочетаний CTRL+ буква (для строчных букв) или CTRL+SHIFT+ буква (для прописных букв), где буква - любая буквенная клавиша на клавиатуре. Не выбирайте стандартного сочетания клавиш, так как выбранное сочетание клавиш подавляет стандартные сочетания клавиш Microsoft Excel на то время с данной книгой.

4. В поле "Сохранить" выберите книгу, в которой требуется сохранить макрос. Сохраните макрос в «Эта книга». Для создания краткого описания макроса, введите необходимый текст в поле Описание. На скриншоте представлен пример заполнения окна диалога «Запись макроса»

Рис. 5.

5. Нажмите кнопку OK.

6. Выполните макрокоманды, которые нужно записать.

Рис. 6.

7. Нажмите кнопку Остановить запись на плавающей панели инструментов «Ос» (Остановить запись) или Сервис / Макрос / Остановить макрос.

По окончании записи макроса он появляется в списке макросов под своим именем.

Редактор VBA автоматически записал последовательность макрокоманд или программу по выполненным действиям (Рис. 7.).

Рис. 7.

Для вызова макроса необходимо выполнить команду Сервис / Макрос / Макросы. После этого в диалоговом окне со списком макросов его можно найти по имени и нажать кнопку Выполнить.

Назначение кнопки панели инструментов для запуска макроса

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

1. Выбрать пункт Настройка в меню Сервис.
2. В окне диалога Настройка выбрать вкладку Команды и выбрать параметр Макросы в списке Категории, в списке Команды выделить «Настраиваемая кнопка».
3. Из списка Команды перетащить с помощью мыши настраиваемую кнопку на панель инструментов.
4. Щелкнуть эту кнопку правой кнопкой мыши и выбрать команду Назначить макрос в контекстном меню.
5. Ввести имя макроса в поле Имя макроса.

Назначение области графического объекта для запуска макроса:

1. Создайте графический объект.
2. К выделенному графическому объекту, примените контекстное меню.
3. Выберите в контекстном меню команду Назначить макрос.
4. В появившемся окне диалога "Назначить макрос объекту", введите название макроса в поле Имя макроса, затем нажмите кнопку OK.

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

1. Выбрать команду Сервис / Макрос / Макросы.
2. Выбрать имя макроса, который следует изменить, в списке Имя.
3. Нажать кнопку Изменить, будет открыто окно Visual Basic, в котором возможно редактирование команд выбранного макроса, записанных на языке Visual Basic.

Удаление макроса:

1. В меню Сервис выберите пункт Макрос, а затем - команду Макросы.
2. В списке макросов текущей рабочей книге необходимо выбрать макрос, который предполагается удалить и нажать кнопку Удалить.

Переименование макроса

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