Практическая работа №13 Тема: Основы языка VBA. Создание макросов.

Практическая работа №13.

Тема: Основы языка VBA. Создание макросов.

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

Задание 1.

Выделение ячеек и диапазонов.

Прежде чем произвести какие-нибудь действия с ячейками, их необходимо выделить. Можно сделать это вручную, а можно с использованием специальных программ – макросов. Ниже приведен пример программного кода макроса, действие которого состоит в выделении диапазона ячеек A1:C6 активного рабочего листа:

Листинг 1: Программный код макроса RangeSelect ( )

Sub RangeSelect ( )

Range (“A1:C6”).Select

End Sub

Для создания макроса необходимо запустить редактор VBA.

Для этого:

1) Перейдите на вкладку Разработчик. Далее выберите раздел Код и щелкните на пиктограмме Visual Basic.

2) Перед вами откроется окно редактора VBA. В открывшемся окне выберите команду Insert Module (см. рис.4).

 

 

Рис. 4. Вставка нового модуля.

В результате в проект VBA добавляется новый модуль, что на практике выглядит как появление пустой рабочей области (см. рис.5). Именно туда и следует ввести программный код.

Примечание 1: если окно записи кода макроса не видно после выполнения команды Insert Module, то нужно щелкнуть
на пиктограмме View Code (крайний слева значок в строке под словами Project-VBAProject):

 

 

Рис. 5. Появление пустой рабочей области.

Что касается непосредственно кода, то он состоит всего из одной команды (если не считать обязательных инструкций начала и конца макроса). Ячейки А1:С6 выделяются с помощью метода Select. О том, что следует выделять именно ячейки А1:С6, указывает инструкция Range (“A1:C6”) (объект диапазон ячеек). Вся команда выглядит как Range (“A1:C6”).Select. Ключевое слово Sub в начале макроса является зарезервированным, после него указывается имя макроса (в данном случае RangeSelect ( ) – имя выбирается пользователем). Также зарезервированной является инструкция окончания тела макроса End Sub.

3) В пустой рабочей области введите код макроса RangeSelect ( ), приведенный в листинге 1, см. рис.6:

 

Рис. 6. Окно редактора VBA с программным кодом макроса.

Сохраните записанный вами макрос с помощью команды Saveменю окна MS Visual Basic. И далее, каждый раз после того, как вы записали новый макрос, перед тем как запустить его на выполнение, его необходимо сохранить.

4) Запустите макрос RangeSelect ( ), с помощью команды Разработчик → Код → Макросы. Откроется диалоговое окно
с названием Макрос. В этом окне выберите RangeSelect ( ) – имя, запускаемого вами на выполнение макроса. Результат выполнения макроса представлен на рис. 7:

 

Рис. 7. Результат выполнения макроса RangeSelect ( ), задание 1, п. 1 - 4.

5) Метод Select используется для выделения ячеек в активных листах. Поэтому сначала нужно активизировать нужный лист с помощью команды Worksheets (“Лист2”).Select). После этого выделяют нужный диапазон ячеек с помощью команды Range (“A1:C6”).Select. Также для данного случая при выделении рабочего листа можно использовать метод Activate. Ниже в листинге приведен код макроса RangeSelect2 ( ), который активизирует Лист 2 и выделяет на нем диапазон ячеек А1:С6:

Листинг 2: Программный код макроса RangeSelect2 ( )

Sub RangeSelect2 ( )

Worksheets (“Лист 2”).Select

Range (“A1:C6”).Select

End Sub

Выполните команду Insert Module, и во вновь открывшемся окне введите код макроса RangeSelect2 ( ). Далее выполните запуск этого макроса с помощью команды: Разработчик → Код → Макросы аналогично п. 4.
Результат выполнения макроса RangeSelect2 ( ) представлен на рис. 8:

 

 

Рис. 8. Результат выполнения макроса RangeSelect2 ( ), задание 1, п. 5.

6) Достаточно легко выделяются области, состоящие из нескольких диапазонов. В этом случае аргументом свойства Range указываются разделенные запятыми выделяемые диапазоны. Код макроса RangeSelect3 ( ) приведен в листинге 3:

Листинг 3: Программный код макроса RangeSelect3 ( )

Sub RangeSelect3 ( )

Range (“A1:C6, E4:G8”).Select

End Sub

 

Рис. 9. Результат выполнения макроса RangeSelect3 ( ), задание 1, п. 6.

В результате выполнения команды Range (“A1:C6, E4:G8”).Select в коде макроса выделяются сразу два диапазона ячеек: А1:С6 и Е4:G8.

7) Кроме того, можно выделять целые строки или столбцы. Пример макроса, который выполняет данные действие, приведен в листинге 4:

Листинг 4: Программный код макроса RangeSelect4 ( )

Sub RangeSelect4 ( )

Range (“3:5, C:G”).Select

End Sub

Выполните макрос RangeSelect4 ( ), приведенный в листинге 4. Обратите внимание, что аргументами свойства Range указаны диапазоны 3:5 (строки с 3-й по 5-ю) и C:G и (столбцы в диапазоне букв от C до G включительно).
После выполнения макроса получится результат как на рис. 10:

 

Рис. 11. Результат выполнения макроса RangeSelect4 ( ), задание 1, п. 7.

Задание 2.

Изменение значений ячеек.

Изменение значений ячеек в любом документе программными методами является эффективным и элегантным инструментом работы с Excel. Доступ к значению ячейки или диапазона может быть получен через свойство Value.

1) Для начала рассмотри простой макрос, работа которого будет состоять в том, что значение активной на момент запуска макроса ячейки будет устанавливаться равным 1 (единице). Программный код макроса SetValue ( ) приведен в листинге 5:

Листинг 5: Программный код макроса SetValue ( )

Public Sub SetValue( )

ActiveCell.Value=1

End Sub

Код макроса состоит всего из одной команды ActiveCell.Value=1, с помощью которой свойству Value активной ячейки ActiveCell присваивается значение 1, см. рис. 12:

 

Рис. 12. Результат выполнения макроса SetValue( ), задание 2, п. 1.

2) Чтобы присвоить значение 1, выделенному на момент запуска макроса диапазону ячеек используют ссылку SelectionValue. В листинге 6 приводится код макроса SetValue2( ), которым единичное значение присваивается всем ячейкам выделенного диапазона:

Листинг 6: Программный код макроса SetValue2 ( )

Public Sub SetValue2 ( )

Selection.Value=1

End Sub

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

Попробуйте самостоятельно выделить любой диапазон ячеек (можно вручную или с помощью запуска рассмотренных выше макросов RangeSelect1-4 ( ), а после выделения на экране соответствующего диапазона ячеек запустить макрос SetValue2 ( ). Примерный вариант выполнения задания приведен на рис. 13:

 

Рис. 13. Результат выполнения задания 2, п. 1 – 2.

Примечание 2: также можно использовать один макрос, который и выделяет указанные ячейки и заполняет их единицами. В приведенном на рис. 13 примере использован макрос:

Public Sub SetValue2( )

Range("A1:C4,E4:G8,A9:C12,E10:G13").Select

Selection.Value = 1

End Sub

3) Теперь заполните ячейкиС3:D5 значениями на 1 большими, чем значение в ячейке A1. Код данного макроса приведен в листинге 7:

Листинг 7: Программный код макроса SetValue3 ( )

Public Sub SetValue3 ( )

Range (“C3:D5”).Value = Range (“A1”).Value+1

End Sub

Для этого введите в ячейку A1 значение 3, выделите ячейку А6 и запустите макрос SetValue3 ( ) на выполнение. Диапазон ячеек С3:D5 заполнится значениями 4, при этом ячейка A6 остается активной, см. рис. 14:

 

 

 

Рис. 14. Результат выполнения макроса SetValue3 ( ), задание 2, п. 3.

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

4) Совсем не обязательно, чтобы значениями ячеек были числа. Можно в рамках макроса присвоить ячейке или ячейкам текстовые значения. В листинге 8 приведен код макроса SetValue4 ( ), который производит эту операцию. Выполните самостоятельно запуск макроса SetValue4 ( ). Результат выполнения задания представлен на рис. 15.

Листинг 8: Программный код макроса SetValue4 ( )

Public Sub SetValue4 ( )

ActiveCell.Offset (1,0).Value = ”Юг”

ActiveCell.Offset (0,1).Value = ”Восток”

ActiveCell.Offset (-1,0).Value = ”Север”

Рис. 15. Результат выполнения макроса SetValue4 ( ), задание 2, п. 4.

ActiveCell.Offset (0,-1).Value = ”Запад”

End Sub

 

У данного макроса две особенности: 1- ячейкам присваиваются текстовые значения и 2 – использовано свойство Offset ( ). Свойству передаются два аргумента: отступ вдоль вертикали и отступ вдоль горизонтали. Эти целые числа указывают, на сколько ячеек следует сделать отступ от активной ячейки (или указанной через инструкцию Range)
по строкам и столбцам соответственно.

Задание 3.

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

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

1) Для начала рассмотрим исключительно простой макрос, с помощью которого в качестве значения активной ячейке присваивается название шрифта для отображения данных в ячейке. Программный код макроса Formats ( ) приведен
в листинге 9:

Листинг 9: Программный код макроса Formats ( )

Public Sub Formats ( )

ActiveCell.Value = ActiveCell.Font.Name

End Sub

Данный макрос свойству активной ячейки Value присваивает значение свойства Font.Name (название шрифта ячейки). Установите шрифт Arial для ячейки B3 (при этом ячейка B3 остается активной) и далее запустите макрос
на выполнение. В результате значением ячейки B3станет название шрифта Arial (см. рис. 16):

 

 

Рис. 16. Результат выполнения макроса Formats ( ), задание 3, п. 1

2) Несколько измените макрос и сохраните под новым именем Formats2 ( ). Видоизмененный макрос приводится
в листинге 10:

Листинг 10: Программный код макроса Formats2 ( )

Public Sub Formats2 ( )

With Selection

.Value = .Font.Name

.Font.Bold = True

End With

End Sub

Данный макрос отличается от предыдущего макроса Formats ( ) тем, что значения ячеек устанавливаются
в соответствии с названием шрифта ля выделенного диапазона ячеек, а для шрифта устанавливается полужирное начертание (свойству шрифта Bold присваивается значение True).

Для удобства в теле макроса использована конструкция With … End With. После ключевого слова With указана ссылка на объект Selection. Это позволяет не использовать данную ссылку в последующих инструкциях блока.
Т.е. внутри блока всё, что начинается с точки, интерпретируется как ссылка, начинающаяся с инструкции Selection.

Для того чтобы проверить работу макроса, установите в ячейках диапазона B3:D5 шрифт Arial для отображения данных. Диапазон ячеек B3:D5 перед запуском макроса остается выделенным. Далее запустите на выполнение макрос Formats2 ( ). Результат выполнения макроса Formats2 ( ) представлен на рис. 17:

 

Рис. 17. Результат выполнения макроса Formats2 ( ), задание 3, п.2.

3) Следующий макрос Formats3 ( ) (см. листинг 11) имеет более сложный программный код, нежели Formats2.

Листинг 11: Программный код макроса Formats3 ( )

Public Sub Formats3 ( )

With Selection

.ColumnWidth = . ColumnWidth*1.5

.RowHeight = .RowHeight + 2

End With

With Selection.Font

.Color = RGB (0,0,255)

.FontStyle = “Bold Italic”

Selection.Value = .Name & “ “ & .Size

End With

End Sub

Во-первых, данным макросом изменяются ширина и высота ячеек выделенного диапазона. Ширина ячеек увеличивается в 1,5 раза с помощью команды .ColumnWidth = . ColumnWidth*1.5 (внутри блока With Selection). Высота ячеек увеличивается на 2 позиции (команда .RowHeight = .RowHeight + 2 внутри того же блока With Selection). Во-вторых, в блоке With Selection.Font задаются некоторые настройки шрифта. В частности, использованием функции RGB ( ) задается синий цвет шрифта. Функция RGB ( ) генерирует цвет в формате RGB (RGB – сокращение от Red – Green – Blue, т.е. красный – синий - зеленый). Установлен стиль шрифта полужирный курсив, с помощью команды .FontStyle = “Bold Italic”. Значения ячеек формируются объединением названия шрифта (свойство Name), пробела и размера шрифта (свойство Size). Используется оператор объединения текстовых строк &. Пробел, заключенный в кавычки, необходим для того, чтобы между названием шрифта и размером оставался отступ.

Для проверки правильности работы макроса выделите диапазон ячеек B3:D5, присвойте им шрифт Arial, размер шрифта 11 пт. и запустите макрос на выполнение. Результат выполнения работы макроса Formats3 ( ) представлен
на рис. 18:

 

Рис. 18. Результат выполнения макроса Formats3 ( ), задание 3, п.3.

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

Задание 4.

Ввод программными методами формул в ячейки.

1) Часто возникает ситуация, когда необходимо внести в ячейку документа формулу, а не просто некоторое значение.
В Excel это довольно простая задача. Её решение подразумевает обращение к свойству Formula объекта Range. Например, для ввода в ячейку B3 формулы = А3^2, используем в программном коде инструкцию Range (“B3”). Formula = “=A3^2”. Эта команда формирует программный код макроса CellFormula ( ), представленного в листинге 12:

Листинг 12: Программный код макроса CellFormula ( )

Public Sub CellFormula ( )

Range (“B3”).Formula = “=A3^2”

End Sub

Для проверки работы макроса введите в ячейку А3 значение 5, после чего запустите макрос CellFormula ( ). Результат выполнения макроса представлен на рис. 19:

 

 

Рис. 19. Результат выполнения макроса CellFormula ( ). задание 4, п.1.

2) Аналогично ячейки заполняются формулами с встроенными функциями Excel. Например, командой Range (“B4”).Formula = “=SIN(A4)” в ячейку B4 вводится формула =SIN(A4). Код соответствующего макроса приводится
в листинге 13:

Листинг 13: Программный код макроса CellFormula2 ( )

Public Sub CellFormula2 ( )

Range (“B4”).Formula = “=SIN(A4)”

End Sub

Выполните самостоятельно макрос CellFormula2 ( ). Результат выполнения макроса приведен на рис. 20:

 

 

Рис. 20. Результат выполнения макроса CellFormula2 ( ). задание 4, п.2.

3) Проблемы возникают, когда приходится вводить формулы с русскоязычным синтаксисом. С первого раза такие формулы программными методами не обрабатываются корректно. Поэтому приходится выделять ячейку и нажимать клавишу Enter. Проблема решается, если вместо свойства Formula воспользоваться свойством FormulaLocal. Пример макроса, в котором реализуется данный принцип, приведен в листинге 14:

Листинг 14: Программный код макроса CellFormula3 ( )

Public Sub CellFormula2 ( )

Range (“B5”).FormulaLocal = “=СУММ(A3:B4)”

End Sub

Данный макрос вставляет функцию вычисления суммы ячеек A3:B4. Проверьте самостоятельно работу данного макроса. Результат выполнения макроса приведен на рис. 21:

 

 

Рис. 21. Результат выполнения макроса CellFormula3 ( ). задание 4, п.3.

 

4) Существует возможность вводить формулы массива. Для этого используется свойство FormulaArray. Этому свойству
в качестве значения присваивается формула, однако вводится она системой в формате формулы массива. Пример простого макроса для ввода формулы массива приводится в листинге 15:

Листинг 15: Программный код макроса CellFormula4 ( )

Public Sub CellFormula4 ( )

Range (“C3:C5”).FormulaArray = “=TAN(B3:B5)”

End Sub

Выполните данный макрос. Результат выполнения макроса представлен на рис. 22:

 

Рис. 22. Результат выполнения макроса CellFormula4 ( ). задание 4, п.4.

5) Во всех предыдущих случаях использовались абсолютные ссылки. На практике это не всегда бывает удобно. Формула с относительными ссылками вводится через свойство FormulaR1C1. В листинге 16 приведен код макроса, которым
в активную ячейку вводится формула, согласно которой значение ячейки вычисляется как единица, деленная на сумму единицы и значения соседней ячейки слева вверху от активной.

Листинг 16: Программный код макроса CellFormula5 ( )

Public Sub CellFormula5 ( )

ActiveCell.FormulaR1C1 = “=1/(1+R[-1]C[-1])”

End Sub

Выполните самостоятельно данный макрос. Результат выполнения макроса CellFormula5 представлен на рис. 23:

 

Рис. 23. Результат выполнения макроса CellFormula5 ( ). задание 4, п.5.

Относительная ссылка на ячейку вводится в формате R1C1. Перед запуском активной была ячейка С6. После выполнения макроса в эту ячейку введена формула = 1/(1+B5). Для ввода формул с относительными ссылками
с русскоязычным синтаксисом используют свойство FormulaR1C1Local.

Задание 5.

Использование встроенных функций Excel.

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

1) Создайте макрос, который вычисляет сумму значений ячеек диапазона, выделенного на момент запуска макроса.
Для вычисления суммы значений ячеек воспользуйтесь встроенной функцией Excel СУММ ( ) (англоязычный вариант - функция SUM ( )). Назовем этот макрос ExcelFunc ( ). Программный код макроса приведен в листинге 17:

Листинг 17: Программный код макроса ExcelFunc ( )

Public Sub ExcelFunc ( )

Dim S As Range

Dim i As Integer

Set S = Selection

i = S.Rows.Count

Selection.Offset(i,0).Range(“A1”).Value = Application.Sum(S)

End Sub

 

В макросе имеются две переменные: переменная i целочисленного типа Integer и переменная S объектного типа Range для записи в неё объекта выделенный диапазон. Именно это делается с помощью команды Set S = Selection.

Свойством Selection возвращается выделенный диапазон ячеек или активная ячейка. Инструкцией S.Rows.Count возвращается количество строк в выделенном диапазоне, и это число присваивается в качестве переменной i. Для выделения ячейки, расположенной под выделенным диапазоном, используется свойство Offset(i,0), где по строкам отступ равен i строкам от верхней левой ячейки диапазона, а по столбцам отступ равен 0. Свойством Range(“A1”) задаются через аргумент A1 размеры диапазона (одна ячейка), а значение (свойство Value) устанавливается равным сумме ячеек диапазона, записанного в переменную S. Сумма вычисляется с помощью встроенной функции Excel. Для доступа к ней указана инструкция Application.Sum(S), а аргументом функции указана переменная S (т.е. диапазон, для которого вычисляется сумма ячеек).

Для выполнения задания заполните числовыми значениями диапазон ячеек B3:C5. На рис. 24 показан документ перед запуском макроса. После запуска макроса получается результат как на рис. 25. Причем сумма вычислена корректно и размещается в ячейке под выделенным диапазоном. При этом выделение с диапазона не снимается, а в ячейку вводится число, а не формула.

 

 

 

	Рис. 24. Вид документа перед выполнением макроса ExcelFunc ( ).		Рис. 25. Вид документа после выполнения макроса ExcelFunc ( ).

 

Задание 6.

Использование условных операторов и операторы цикла при вычислении чисел Фибоначчи.

Любая более или менее серьёзная программа подразумевает использование управляющих инструкций, среди которых особое место занимают условные операторы и операторы циклов. Рассмотрим особенности применения таких операторов.

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

Оператор цикла позволяет выполнять последовательности однотипных действий.

1) Создадим с помощью оператора цикла FOR макрос, который заполняет ячейки документа числами Фибоначчи
(по имени средневекового математика Леонардо Пизанского, известного как Фибоначчи):

1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, ...

Числами Фибоначчи называют последовательность натуральных чисел, первые два из которых равны 1, а каждое последующее равно сумме двух предыдущих.

Примечание 3: Оказывается, эта последовательность имеет множество интересных с точки зрения математики свойств. Вот пример: вы можете разделить линию на два сегмента, так что соотношение между большим и меньшим сегментом будет пропорционально соотношению между всей линией и большим сегментом. Этот коэффицент пропорциональности, приблизительно равный 1,618, известен как золотое сечение. В эпоху Возрождения считалось, что именно эта пропорция, соблюденная в архитектурных сооружениях, больше всего радует глаз. Если вы возьмете последовательные пары из ряда Фибоначчи и будете делить большее число из каждой пары на меньшее, ваш результат будет постепенно приближаться к золотому сечению. Другую интересную информацию о числах Фибоначчи можно найти в Интернете: http://elementy.ru/trefil/21136; http://ru.wikipedia.org и др..

Также, в оператор For добавим условный оператор, с помощью которого обеспечим необходимость того, чтобы числами ячейки заполнялись только в том случае, если в активную ячейку введено число большее 2. В VBA существует условный оператор If…. Then….Else, который реализует означенный алгоритм. Программный код данного макроса приведен в листинге 18:

Листинг 18: Программный код макроса Fibon ( )

Public Sub Fibon ( )

Dim N As Integer

Dim i As Integer

Dim a As Integer

Dim b As Integer

N = ActiveCell.Value

If N<2 Then

MsgBox Prompt: = “Указано неверное значение!”, Title: = “Ошибка!”

Else

a = 1

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

ActiveCell.Offset(1,0).Value = a

b = 1

For i = 2 To N

ActiveCell.Offset(i,-1).Value = i

ActiveCell.Offset(i,0).Value = b

b = b + a

a = b – a

Next i

End If

End Sub

 

Рис. 26. Результат выполнения задания 6, п. 1.

В ячейку А4 введите слова: «Числа Фибоначчи в количестве N = », в ячейку В4 введите число 12 (сколько чисел Фибоначчи будет выведено с помощью макроса), далее выделите ячейку B4 со значением 12 (ячейка B4 станет активной) и далее запустите макрос на выполнение. Результат выполнения макроса будет выглядеть как на рис. 27. Внизу, под ячейкой B4 (в ячейках B5:В16) будут выведены 12 чисел Фибоначчи. Поэкспериментируйте сами, с выполнением данного макроса, задавая для вычисления разное число чисел Фибоначчи в ячейке B4.

Если в ячейку В4 будет введено число меньшее 2, то результат будет иным, см. рис. 27:

 

Рис. 27.Результат работы макроса при неверно указанном значении ячейки.

Данный макрос Fibon имеет недостатки, в частности не может вывести одно число Фибоначчи, когда значение
в активной ячейке равно 1, а также не может обработать случай, когда в ячейка В4 содержит нечисловое значение.

Приведённый ниже макрос Fibon2 решает эти проблемы (см. листинг 19):

Листинг 19: Программный код макроса Fibon2 ( )

Public Sub Fibon2()

Dim N As Integer

Dim i As Integer

Dim a As Integer

Dim b As Integer

N = ActiveCell.Value

If Not IsNumeric(ActiveCell.Value) Then

MsgBox Prompt:=” Должно быть указано число!", Buttons:=vbCritical, Title:="Внимание!"

Exit Sub

End If

N = ActiveCell.Value

Select Case N

Case Is < 1

MsgBox Prompt:=" Указано неверное число!", Buttons:=vbInformation, Title:="Внимание!"

Case Is = 1

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

ActiveCell.Offset(1, 0).Value = 1

Case Else

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

ActiveCell.Offset(1, 0).Value = 1

a = 1

b = 1

i = 2

Do While i <= N

ActiveCell.Offset(i, -1).Value = i

ActiveCell.Offset(i, 0).Value = b

b = b + a

a = b - a

i = i + 1

Loop

End Select

End Sub

Как работает этот макрос? Сначала выполняется условный оператор, который проверяет условие того, что значение в ячейке является числовым. Для этого используется функция IsNumeric( ). Если выражение, указанное аргументом функции, является числовым, то возвращается значение true (в противном случае результатом функции будет false). Поскольку перед функцией указана инструкция отрицания Not, блок условного оператора выполняется при нечисловом значении ячейки. Блок состоит из двух операторов. Первый выводит сообщение о том, что надо ввести числовое значение в активную ячейку. В функции MsgBox использован аргумент Buttons:=vbCritical, которым устанавливается тип окна сообщения с пиктограммой ошибки. Вторая команда – завершает процедуру Exit Sub. Альтернативная ветка Else в этом случае не используется.

Оператор Select Case проверяет значения переменной N (значение активной ячейки). Для случая, если это значение меньше 1 (условие Is < 1), выводится специальное информационное сообщение (используется аргумент Buttons:=vbInformation для функции MsgBox). Для случая, если значение активной ячейки равно 1 (условие Is=1), выводится одно число Фибоначчи. В прочих случаях выполняется блок команд Case Else. В этом блоке оператор цикла For заменен на оператор цикла Do While….Loop. Команды, размещенные в этом операторе, выполняются до тех пор, пока выполнено условие, указанное после ключевого словаWhile.

Результат вызова макроса Fibon2 при числовом, но некорректном значении в активной ячейке, показан на рис. 28:

 

Рис. 28. Попытка вычисления чисел Фибоначчи при неверно указанном значении в активной ячейке.

Если ввести единицу и запустить макрос, то будет выведено первое число Фибоначчи, см. рис. 29:

 

Рис. 29. Результат вывода первого числа Фибоначчи.

Значения чисел Фибоначчи достаточно быстро растут с увеличением их порядкового номера. Поэтому созданные макросы могут использоваться для заполнения ячеек числами из первого десятка. Если указать слишком большое число
в ячейке, то произойдет ошибка, связанная с выходом за пределы допустимого диапазона целочисленных значений
в VBA. Избежать этой ошибки можно при использовании более подходящего типа данных, например Double.

 

Контрольные вопросы:

1) Введите в ячейку A1 число 6. Как будет выглядеть результат выполнения макроса SetValue3 ( ), задание 2, п. 3.
если код макроса SetValue3 ( )будет иметь вид как в листинге 7*?

Листинг 7*: Программный код макроса SetValue3 ( )

Public Sub SetValue3 ( )

Range (“B3:D8”).Value = Range (“A1”).Value+3

End Sub

2) Выделите активную ячейку D10. Используя макрос SetValue4 ( ) измените его код так, чтобы отступ от активной ячейки по вертикали и по горизонтали составлял 2.

3) Установите в ячейках С4:D10 шрифт: Times, размер: 14 пт.. Далее, используя макрос Formats2 ( ), измените его код так, чтобы ячейкам С4:D10 присваивалось значение шрифта Times, начертание: Bold.

4) Установите в ячейках С4:D10 шрифт: Times, размер: 14 пт.. Далее, используя макрос Formats3 ( ), измените его код так, чтобы ячейки С4:D10 увеличились в 2 раза по ширине, высота ячеек увеличилась на 4 позиции, цвет шрифта: Green, стиль шрифта: Bold.

5) Введите в ячейку А3 значение 0,5. Используя макрос CellFormula ( ), вычислите значение ячейки B3 по формуле: SIN(A3^2).

6) Введите в ячейку А1 число 2, а в ячейку В1 число 3. Используя макрос CellFormula5, вычислите значение ячейки А2(А2 считается активной) по формуле, согласно которой значение ячейки вычисляется как сумма 1, и значения соседней ячейки, отстоящей справа вверху от активной (ответ:4).

7) Введите в ячейку А1 число 2, в ячейку A2 число 4. Используя макрос CellFormula5, вычислите значение ячейки B1
(B1 считается активной) по формуле, согласно которой значение ячейки вычисляется как сумма 1, и квадрата значения соседней ячейки, отстоящей слева внизу от активной (ответ:17).

8) При выполнении макроса Fibon2 введите в активную ячейку количество чисел Фибоначчи, которое вы хотите вывести на экран. Сначала число 20, затем число 50 и запустите макрос на выполнение. Проверьте результат выполнения макроса. В каком случае он не работает и почему?