Использование макросов в excel

 

Цель работы: ознакомиться с понятиями объекта и переменной в Visual Basic for Application, получить практические навыки по созданию макросов в Excel с использованием процедур и функций.

 

Теоретические сведения

 

Объекты и методы

 

Объект ‑ это то, чем Вы управляете с помощью программы на языке Visual Basic, например кнопка, рабочий лист, рабочая книга, диаграмма, ячейка таблицы и т.д. Объект — это базовый элемент для построения любого приложения для Excel. Visual Basic for Application предлагает большой набор объектов, с помощью которых Вы можете быстро создать сложную программу.

Каждый объект обладает определенными свойствами. Например, диалог в данный момент на экране может быть видимым или невидимым. Вы можете узнать текущее состояние диалога с помощью свойства Visible (Видимый). Еще один пример свойства объекта ‑ это шрифт, используемый для отображения информации в ячейке рабочего листа. Изменяя свойства, Вы можете изменять характеристики объекта. Не все свойства могут быть установлены, некоторые носят информационный характер и могут только читаться. Синтаксис установки значения свойства объекта выглядит следующим образом:

Объект Свойство Выражение

Следующий пример демонстрирует изменение параметров шрифта для выделенного участка рабочего листа:

 

Sub ChangeFont()

‘ Установка размера шрифта

Selection.Font.Size=16

‘ Установка полужирного шрифта

Selection.Font.Bold=true

End Sub

 

Строка, в начале которой стоит символ ‘, не обрабатывается редактором. Это удобно использовать для комментариев и пояснений к создаваемым макросам.

Методы – это команды, с помощью которых выполняется некоторое преобразование объектов, например, отображение на экране диалога. Вы можете сделать это с помощью метода Show (Показать). Следующий пример выбирает диапазон ячеек В3:F5 в текущем рабочем листе с помощью метода Select объекта Range:

 

Range(“B3:F5”).Select

 

Объект самого высокого уровня — это объект Application (Приложение). Если Вы изменяете свойства этого объекта или вызываете его методы, то результат применяется к текущей работе Excel. Например, Вы можете завершить работу с Excel с помощью метода Quit (Выход) объекта Application (Приложение):

 

Application.Quit

 

Следующий пример закрывает все рабочие книги в текущем приложении, при этом используется метод Close (Закрыть) объекта Workbooks (Рабочие книги), который входит в объект Application (Приложение):

 

Application.Workbooks.Close

 

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

 

Переменные и их типы

 

Все переменные в языке Visual Basic имеют тип. Тип указывает, что может хранить переменная: целое число, строку, дату и т.п.

Для объявления типа переменной необходимо написать:

Dim perem As Type

Вместо Dim можно использовать Public и Private. Переменная, объявленная с помощью Public, может использоваться макросами из других модулей и форм, а переменная, объявленная как Private или Dim, может быть использована только в том модуле, где была объявлена.

При составлении программы на языке Visual Basic for Application совсем не обязательно объявлять переменные до начала их использования в программе. Как только в программе встретится присвоение новой переменной, Visual Basic сразу создаст переменную, тип которой будет совместим с типом присваиваемого выражения. По умолчанию, если не указан тип переменной, то будет использоваться тип Variant (Вариант). Переменные этого типа могут хранить все, что в них поместят, т.е. их тип изменяется в зависимости от последнего присвоения.

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

 

Таблица 15.1 ‑ Основные типы переменных

Тип данных	Диапазон принимаемых значений
Byte	Байт	0 ÷ 255
Boolean	Логическая величина	True (Истина) или False (Ложь)
Integer	Целое	-32768 ÷ 32767
Long	Длинное целое	-2147483648 ÷ 2147483647
Single	Вещественное с обычной точностью	-3.402823E38 ÷ -1.401298E-45 и 1.401298E-45 ÷ 3.402823E38
Double	Вещественное с двойной точностью	-1.79769313486231E308 ÷ -4.94065645841247E-324 и 4.94065645841247E-324 ÷ 1.79769313486232E308
String	Строка	0 ÷ 2Е9
Variant	Вариант	Double / String

 

Тип String (Строка). По умолчанию строковая или текстовая переменная является массивом переменной длины, которая содержит символы.

Чтобы запретить использование переменных без предварительного объявления, необходимо поместить в начале программы оператор Option Explicit. После этого, если в тексте программы встретится переменная без предварительного объявления, на экран будет выведено сообщение об ошибке: "Variable not defined" (Переменная не определена). Это поможет избежать самого распространенного типа ошибок, связанного с неправильным описанием имен переменных.

 

Процедуры и функции

 

Функция представляет собой программу, которая получает информацию из другой программы, преобразует эту информацию и возвращает определенное значение той же программе. Описание функции начинается с заголовка Function, за которым следуют имя функции и список параметров, заканчивается описание оператором End Function. Для передачи значения из функции в основную программу следует присвоить имени функции результат вычислений в теле самой функции.

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

 

Sub Main()

Dim a As Integer

Dim c As Integer

Dim n As Integer

c = InputBox("Введите число:")

n = InputBox("Введите степень:")

‘ Переменной а присваивается значение функции stepen

a = stepen (c, n)

MsgBox a

End Sub

Function stepen (f As Integer, e As Integer) As Integer

stepen = f ^ e

End Function

 

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

Описание процедуры начинается с заголовка Sub, за которым следуют имя процедуры и список параметров, а заканчивается оператором End Sub.

Приведем пример использования процедуры для возведения числа в степень.

 

Sub Main()

Dim a As Integer

Dim c As Integer

Dim n As Integer

c = InputBox("Введите число:")

n = InputBox("Введите степень:")

‘ Вызов процедуры stepen и передача переменных a, c, n

stepen a, c, n

MsgBox a

End Sub

Sub stepen (d As Integer, f As Integer, e As Integer)

d = f ^ e

End Sub

 

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

 

Порядок выполнения работы

1. Изучите теоретические сведения.

2. Создайте макрос для изменения свойства ячейки (шрифт, цвет).

3. Создайте макрос для определения силы тока по заданному напряжению и сопротивлению элемента. Попробуйте по-разному задать типы переменных, используемых в программе.

4. Создайте макрос для заданного преподавателем варианта.

5. Сохраните файл с созданными макросами.

5. Перепишите текст макросов в отчет.

6. Закройте Visual Basic и Microsoft Excel.

7. Ответьте на контрольные вопросы по указанию преподавателя.

 

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

 

1. Что входит в понятие объект?

2. Чем отличаются типы переменных?

3. Что означает тип переменной Variant?

4. Что входит в понятие функция?

5. Что входит в понятие процедура?

6. Чем отличаются процедуры от функций?

7. Как осуществляется вызов процедур и функций?

 

15.4 Задания для самостоятельной работы

 

1. Создайте программу с использованием функции для расчета напряжений короткого замыкания лучей схемы замещения трехобмоточного трансформатора по формулам

U_кВ = 0,5(U_кВН + U_кBC – U_кCH);

U_кC = 0,5(U_кВC + U_кCH – U_кBH);

U_кH = 0,5(U_кВH + U_кCH – U_кBC).

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

 

2. Создайте программу с использованием процедуры для расчета потерь короткого замыкания лучей схемы замещения трехобмоточного трансформатора по формулам

DР_кВ = 0,5(DР_кВН + DР_кBC – DР_кCH);

DР_кC = 0,5(DР_кВC + DР_кCH – DР_кBH);

DР_кH = 0,5(DР_кВH + DР_кCH – DР_кBC).

Предусмотреть ввод значений потерь короткого замыкания для пар обмоток трехобмоточного трансформатора DР_кВН, DР_кBC и DР_кCH с клавиатуры.

 

3. Создайте макрос для переименования рабочего листа. Имя изменяемого листа и его новое название должны вводиться с клавиатуры

 

4. Создайте макросы для перехода к первому и ко второму рабочим листам. Просмотрите текст макросов для переходов к первому и ко второму рабочим листам и постарайтесь изменить его в редакторе Visual Basic так, чтобы получился один макрос, который, если активным является первый лист, переключается на второй лист, иначе ‑ на первый лист.

 

5. В редакторе Visual Basic составьте макрос, заполняющий диагональ выделенного квадрата черным цветом. Определить число строк и столбцов выделенной области можно с помощью Selection.Rows.Count и Selection.Columns.Cout соответственно. Переход с одной ячейки на другую по диагонали можно осуществить с помощью ActiveCell.Offset (rowoffset:=1, columnoffset:=1).Activate.

ЛАБОРАТОРНАЯ РАБОТА № 16