Подготовьте отчет о выполненной лабораторной работе. Он должен содержать титульный лист и описание последовательности ваших действий с отладчиком, а также результаты выполнения программы. Дайте письменные ответы на контрольные вопросы. Сформулируйте выводы, которые можно сделать по результатам выполненной работы.
5. Контрольные вопросы
1. Каково назначение окна локальных переменных?
2. Каково назначение окна редактора кодов?
3. Как выглядит маркер отладчика?
4. Каково назначение окна проектов?
5. Как выполнить программу по шагам?
6. Какие команды существуют для выполнения программы и в чем их отличие?
7. Что такое точка останова?
8. Как при выполнении программы по шагам можно автоматически выполнить ее определенную часть?
9. Как запустить программу на выполнение из Excel?
10. Как можно наблюдать результаты выполнения программы в пошаговом режиме в таблице Excel?
Лабораторная работа №3. Обмен данными между Excel и VBA
Методические указания
Процессор ЭВМ манипулирует с данными, имеющимися в памяти машины. В зависимости от конкретной задачи эти данные могут принимать самые разнообразные значения, но они должны быть занесены в стандартные физические ячейки памяти, размер которых определяется конструкцией конкретного устройства. Поскольку для хранения различных данных может потребоваться различный объем памяти, используется метод последовательного размещения их в памяти. В этом случае одна единица данных может занимать одну или несколько последовательных физических ячеек памяти машины. Адресация к данным производится по адресу первой ячейки, но при этом общее число используемых ячеек должно быть точно известно. Так как только программист в состоянии предусмотреть возможные значения данных, используемых в программах, вопрос о распределении памяти для хранения информации ложится на его плечи. Конкретная организация памяти в задаче осуществляется за счет выбора программистом конкретного типа данных для хранения его информации.
Тип данных‑ способ внутреннего представления данных в памяти машины, учитывающий метод их кодирования в одной или нескольких ячейках памяти и предусматривающий возможности их расшифровки или преобразования.
Первые языки программирования содержали всего два типа данных – целые (Integer) и дробные (Real или Float или Single). С развитием языков программирования и расширением круга решаемых задач число используемых типов данных непрерывно росло. Так, для обеспечения требуемой точности и диапазона вычислений были введены соответственно для целых и дробных чисел типы Long и Double. Потребность в хранении текстовой информации привела к появлению типа данных Byte (в терминологии VBA), позволяющего наиболее экономно расходовать память ЭВМ (современные таблицы кодировки символов используют диапазон кодов от 0 до 255). Задачи, связанные с анализом и составлением текстовых сообщений, стали поддерживаться типом данных String. Для обеспечения возможностей ссылки на различные участки памяти был предложен специальный тип данных, называемый указателем (Object). В случае использования указателя в памяти хранится адрес ячейки памяти, содержащей интересующие нас данные или коды программы. Кроме этого, программисту предоставили возможность самому создавать интересующий его тип данных. Необходимость выполнения вычислений с датами и временем породила свой специальный тип данных Date. Особые условия выполнения вычислений с деньгами заставили добавить в перечень специальный тип Currency. Наконец, для упрощения начального ввода данных в клетки электронных таблиц Excel был разработан специальный тип данных Variant, позволяющий автоматически распознавать и обрабатывать числа и строки.
Готовясь к написанию программы, программист обязан задуматься над вопросом: какие значения могут принимать данные его программы? Ответив на этот вопрос, программист подбирает удобный ему тип данных из числа стандартных или создает свой. При этом приходится принимать во внимание следующее обстоятельство: использование стандартных типов данных существенно упрощает процесс создания программы, поскольку в языке программирования заложены возможности действий с этими данными и их преобразование из типа в тип. Мы предполагаем, что вы знакомы с типами данных Excel, задаваемыми ячейкам командой Формат, Ячейки вкладка Число окно Числовые форматы. В языке VBA существуют типы данных, приведенные в табл. 2
Пример 2. В программе, предназначенной для расчета начисления заработной платы рис. 1. для хранения номера в списке (если он будет добавлен в таблицу) можно выбрать тип данных Integer, для хранения фамилий сотрудников тип данных String. Ставка заработной платы и величина начисленного налога может быть описана типом данных Currency, а ставка налога типом данных Single. Кроме этого, например можно создать свой тип данных (Type), в который входят фамилия, начисленная сумма, сумма уплачиваемого налога и сумма к выдаче как самостоятельная единица хранимых в памяти данных.
Поскольку физически данные программы оказываются содержимым конкретных ячеек памяти машины, для их отыскания достаточно знать адрес первой ячейки, связанной с данными, и по типу данных определить общее число используемых для хранения элементарных ячеек. Такой подход имел место на самой ранней стадии программирования и оказался крайне неудобным из-за отсутствия наглядности в записи программы. Действительно, если память современной машины содержит несколько десятков, а то и сотен миллионов ячеек памяти, то обращение к ним по номерам было бы крайне неразумным. Уже первые трансляторы использовали прием, основанный на использовании так называемых идентификаторов.
Идентификаторомназывается символическое имя ячейки памяти. Каждый язык программирования содержит свои правила составления таких имен, общим является то, что программист вправе сам придумать имя, что позволяет ему сохранить в нем смысловое значение. В языке VBA имеются следующие ограничения на имена:
· Длина имени не должна превышать 255 символов.
· Имя должно начинаться с буквы.
· Имя не может содержать точек и символов %, &,!, #, @, $.
· Буквы рассматриваются инвариантно по отношению к регистру, то есть имя Aa и aA есть одно и то же имя.
· Совпадения имен идентификаторов с так называемыми ключевыми словами не допускается.
Ключевые слова- набор специальных слов, написанных символами латыни и имеющих определенный смысл с тоски зрения конструкций языка программирования. Ключевыми словами обозначаются, в частности, операторы языка и встроенные функции языка.
Пример 3. Возможные варианты идентификаторов языка VBA: I, j, Name, Переменная, Результат_вычислений. Еще варианты записи идентификаторов: A%, B&, C!, D#, E@, F$. В этом случае символы %, &,!, #, @, $ не входят в состав идентификатора и используются в качестве специального признака типа данных (смотри табл. 2).
Программист может вводить переменные в текст программы на VBA по мере их необходимости, применяя явное или неявное (по умолчанию) их объявление. В последнем случае переменная просто начинает использоваться в тексте. При первом ее появлении компилятор (интерпретатор) заносит новое имя в таблицу и закрепляет за ним определенный адрес и тип данных (в VBA – Variant).
Хотя возможность объявления переменных по умолчанию предусмотрена разработчиками языка, она представляется крайне нежелательной. Текст программы сам по себе представляет документ, в котором содержится исчерпывающая информация о ее работе, в том числе и о типах используемых данных. Введение переменных по умолчанию приводит к затруднениям при изучении программы и, как следствие, к ошибкам. Поэтому рекомендуется всегда явно определять переменные с помощью оператора Dimс указанием типа и задавать специальный режим принудительного объявления переменных программы помещенной в начале текста модуля инструкцией Option Explicit.
Пример 4. Явное объявление переменной:
Dim I As Integer, Name, j As Integer, Переменная As Integer, GGG As Integer
Обратите внимание на то, что если вы не указываете явно тип переменной, то по умолчанию она имеет тип Variant. Так, в рассмотренном выше примере такой тип имеет переменная Name.
Примечание. Интегрированная среда разработки VBA в окне редактора кодов предлагает в качестве сервиса возможность выбора одного из существующих типов данных из автоматически раскрывающегося списка. Так, после набора ключевого слова Dim, указания идентификатора переменной и набора ключевого слова As автоматически открывается список возможных значений (в данном случае типов данных). Перемещение по списку может осуществляться с помощью маркеров или путем ввода символов с клавиатуры. После того, как требуемое значение в списке установлено, оно может быть перенесено в текст программы клавишей Tab или в результате двойного клика клавишей мышки. Этой возможностью удобно пользоваться для избежания грамматических ошибок при наборе текста программы.
Рассмотренные выше примеры объявления переменных предусматривали создание одиночных констант или переменных, обращение к которым осуществляется только по имени. Практика программирования широко использует переменные, обращение к которым ведется как по имени, так и по номеру. В этом случае можно говорить о создании переменных табличного типа, когда обращение к данным ведется по имени и номеру (индексу) внутри этого имени. Такие переменные обычно называются массивами. Массив‑ последовательно упорядоченные в памяти данные одного типа.
Таблица 2.Типы данных языка VBA
| Тип данных | Размер (байт) | Служебный символ | Диапазон значений |
| Byte (байт) | От 0 до 255 | ||
| Boolean (логический) | True или False | ||
| Integer (целые) | % | От -32768 до 32767 | |
| Long (длинное целое) | & | От -2147483648 до 2147483647 | |
| Single (плавающее обычной точности) | ! | От -3,402823E38 до -1,401298Е-45 и от 1,401298Е-45 до 3,402823E38 | |
| Double (плавающее двойной точности) | # | От -1,79769313486231Е308 до -4,94065645841247Е-324 и от 4,94065645841247Е-324 до 1,79769313486231Е308 | |
| Currency (денежный) | @ | От -922337203685477,5808 до 922337203685477,5808 | |
| Decimal (масштабируемое целое) | +/-7922816251426433759353950335 и 28 знаков после запятой. Минимальное отличное от нуля значение имеет вид +/-0,0000000000000000000000000001 | ||
| Date (время и дата) | От 1 января 100 г. до 31 декабря 9999 г. | ||
| Object (объект) | Любой указатель объекта | ||
| String (строка переменной длины) | 10+ длина строки | От 0 до приблизительно двух миллиардов | |
| String (строка постоянной длины) | Длина строки | $ | От 1 до 65400 |
| Variant (числовые подтипы) | От -1,79769313486232Е308 до -4,94065645841247Е-324 и от 4,94065645841247Е-324 до 1,79769313486232Е308 | ||
| Variant (строковые подтипы) | 22+ длина строки | От 0 до приблизительно двух миллиардов | |
| Type (определяемый пользователем) | Определяется элементами типа | Диапазон каждого элемента определяется его типом данных |
Если мы хотим воспользоваться массивом, то мы должны его явно объявить. В дополнение к предыдущему объявление предусматривает еще и задание диапазона изменения номера элемента массива. По умолчанию считается, что минимальное значение номера (базовый индекс) равно нулю, а максимальное задается при объявлении. При необходимости (например, из соображений совместимости со старыми версиями языка) значение базового индекса 0 может быть изменено на 1 инструкцией Option Base 1. Другим способом изменения базового индекса конкретного массива является использование в объявлении ключевого слова To. Минимальное значение индекса указывается до слова To, а максимальное после. В некоторых случаях, например для хранения таблиц, оказывается удобным использовать двумерные массивы. В этом случае при объявлении через запятую описываются оба индекса. Возможно и применение массивов и большей размерности.
Пример 5. Объявления массивов:
Dim YY(25)
Объявляется одномерный массив из 26 элементов. Начальный (базовый) индекс принят по умолчанию равным 0.
Dim ZZ(3,10) As Single
Объявляется двумерный массив ZZ типа Single, первый индекс которого меняется в диапазоне от 0 до 3, а второй в диапазоне от 0 до 10.
Dim SS(-3 To 3,1 To 10) As Integer
Переопределение базовых индексов с помощью явного указания нижних и верхних границ номеров элементов массива с использованием ключевого слова To.
Для обращения к ячейке памяти или элементу массива достаточно в тексте программы использовать соответствующий идентификатор (в случае массива с номером элемента, указанным в скобках). Важной особенностью систем программирования является то обстоятельство, что в качестве номера элемента массива может выступать не только константа, но и другая переменная, заданная своим идентификатором. Заметим, что недостатком рассмотренного приема является относительно высокая вероятность возникновения ошибки программирования связанной с выходом индекса (номера элемента) за границы массива. Программная среда VBA автоматически локализует такую ситуацию, выдавая соответствующее диагностическое сообщение.
Пример 6. Обращение к элементу массива в тексте программы с явным указанием номеров элементов: SS(-2,5).
Если переменная Name содержит число –2, а ячейка Переменная число 5, то обращение SS(Name, Переменная) полностью эквивалентно предыдущему.
Если в процессе предыдущих вычислений переменная Name примет значение –4, а мы попытаемся выполнить SS(Name, Переменная), то произойдет обращение к несуществующему элементу массива и возникнет ошибка выхода индекса за границы массива.
Массивыудобно использовать при программировании однотипных действий с ячейками памяти. В качестве примера рассмотрим задачу расчета начисления заработной платы (рис. 1). Поскольку исходные данные и результаты промежуточных вычислений должны храниться в памяти ЭВМ, в процессе программирования решения задачи на VBA приходится использовать идентификаторы. Заметим, что обычный идентификатор в этом случае не очень удобен. Действительно, хотя возможно введение в текст программы обычной переменной вида Налог_Трофимова_Л_А, создаваемая программа может быть в этом случае использована только для расчетов налога, уплачиваемого именно Л.А. Трофимовой. Если мы хотим запрограммировать вычисления для другого лица, то нам придется вводить другой идентификатор. Подобные действия ведут к изменению текста исходной программы и крайне нежелательны на практике. Конечно, мы можем ввести идентификаторы обычных переменных вида Налог_запись_2, однако и в этом случае мы должны будем индивидуально описать последовательность манипуляций с ячейками памяти для каждого сотрудника, включенного в список. Для нашего примера это вполне возможно, но реальный список может состоять, например, из 100 фамилий.
Кроме всего прочего, каждый раз при изменении количества сотрудников мы должны корректировать объявления переменных и, возможно, делать добавления в текст программы. Программирование существенно упростится, если ввести в рассмотрение массивы данных, имеющие смысл Начислено(1 To 4), Налог(1 To 4), К_выдаче(1 To 4) и рассматривать их элементы с одинаковыми номерами как записи, относящиеся к сотруднику, имеющему соответствующий идентификационный номер. На первый взгляд этот способ ничем существенным не отличается от использования идентификаторов одиночных переменных с номерами, однако если вспомнить, что существует возможность обращения к элементу массива с использованием идентификатора другой переменной, то можно рассматриваемую задачу попытаться описать и в общем виде.
Пример 7. В общем виде выражение для вычисления величины суммы к выдаче для каждого сотрудника может быть записано как:
К_выдаче(i) = Начислено(i) – Налог(i)
Здесь символом = обозначена операция присваивания результата вычислений в правой части оператора ячейке, указанной в левой части. Во время выполнения этой операции старое содержимое ячейки К_выдаче(i) теряется и она получает новое значение. В то же время символ – есть символ операции вычитания.
Если организовать повторения вычислений по этой формуле столько раз, сколько сотрудников имеется в списке для последовательно изменяющихся значений индекса i, то рассматриваемая задача может быть решена заметно проще, чем в случае объявления одиночных переменных.
Иногда приходится создавать массивы, размер которых невозможно определить на этапе компиляции программы. В нашем примере нам может быть неизвестно общее число сотрудников, для которых должна быть начислена зарплата. Конечно, можно объявить массивы с запасом, так, чтобы номер максимального элемента массива был заведомо большим максимально возможного числа сотрудников, допустим 100 человек. Однако такой прием приводит к нерациональному распределению памяти. Альтернативой является метод динамического объявления размера массива. В этом случае конкретный размер массива вычисляется в процессе выполнения программы и память для хранения данных отводится тоже во время выполнения. Чтобы воспользоваться этим методом, необходимо первоначально объявить массив без указания его размеров, а затем воспользоваться инструкцией ReDim. Менять границы изменения индекса массива можно сколь угодно много раз. Если массив больше не требуется в программе, память, занимаемая им, может быть освобождена с помощью инструкции Erase Начислено.
Пример 8.
Dim Начислено() As Currency, i As Integer
i = 10
ReDim Начислено(1 To i)
МассивНачислено() первоначально был объявлен как массив неопределенной длины. Инструкция ReDim изменила массив, причем память под него была отведена в момент выполнения программы.
Очень часто при программировании возникает необходимость создания новых типов данных, вид которых определяется конкретной задачей. Так, например, программируя задачу, представленную на рис. 1, обратим внимание на то обстоятельство, что информация, размещенная в этой таблице, имеет одинаковую структуру по строкам. Более того, даже программируя соответствующую колонку таблицы в виде массива, программист обязан следить за тем, чтобы номера элементов разных массивов, относящихся к одному сотруднику, не отличались бы один от другого. Из соображений надежности программирования оказывается удобным рассматривать все, относящееся к одному сотруднику, в виде целой неделимой записи, содержащей соответственно фамилию, начисленную сумму, рассчитанный налог и сумму к выдаче. На самом деле речь идет о создании нового типа данных, определенного пользователем и включающего в себя относящиеся к записи поля. Структура данных‑ объединение под одним именем различных компонентов с индивидуальными именами и типами, называемых членами структуры.
Признаком структурыданных, как правило, является символ точки в ее идентификаторе, причем имя структуры записывается до точки, а имя ее компонента (члена) после точки. В языке VBA структуры данных можно создавать на основе типов данных, определяемым пользователем. Задание типа данных только описывает структуру, информация о которой размещается в общей области программы VBA. Для ее непосредственного объявления и резервирования ячеек памяти под хранение данных требуется явно объявить переменную в конкретном модуле.
Пример 9. Создание пользовательского типа данных, представляющего собой одну строку записи рис. 1.
Type Запись_Ведомости
Фамилия_И_О As String
Начислено_Ведомость As Currency
Налог_Ведомость As Currency
К_выдаче_Ведомость As Currency
End Type
Объявление переменной:
Dim Запись1 As Запись_Ведомости
Запись значений в элементы структуры с использованием оператора присваивания:
Запись1.Фамилия_И_О = "Иванов В.Н."
Запись1.Начислено_Ведомость = 1234
Запись1.Налог_Ведомость = Запись1.Начислено_Ведомость * 0.12
Запись1.К_выдаче_Ведомость=Запись1.Начислено_Ведомость-Запись1.Налог_Ведомость
Здесь символом * обозначена операция умножения.
Объявление массива структур:
Dim Ведомость(1 To 4) As Запись_Ведомости
Соответствующие обращения к элементам массива и членам структуры будут иметь вид:
Ведомость(1).Фамилия_И_О = "Иванов В.Н."
Ведомость(1).Начислено_Ведомость = 1234
Ведомость(2).Фамилия_И_О = "Трофимова Л.А.
Ведомость(2).Начислено_Ведомость = 1234
Примечание. Интегрированная среда разработки VBA в окне редактора кодов предлагает в качестве сервиса возможность конкретного выбора типа данных, определенных пользователем, из автоматически раскрывающегося списка. Если структура данных ранее была объявлена и выполнена компиляция проекта, после набора символа точки автоматически открывается список возможных имен полей структуры. Этой возможностью удобно пользоваться для избежания синтаксических ошибок при наборе текста программы.
Отдельную проблему представляет прямая и обратная передача данных из таблицы Excel в ячейки памяти, объявленные в программе, написанной на VBA. Автоматически созданный макроснепосредственно манипулирует с ячейками таблицы, используя стили ссылки на ячейки в Excel: A1 и R1C1. Конечно, такой прием может быть использован и в рабочей программе, однако в этом случае ее модификация и использование существенно затруднены. Гораздо предпочтительнее использовать свойство Cells() стандартного объекта Excel Range. Сам объект представляет собой ячейку, столбец, строку или выделенный диапазон листа Excel. Свойство Cells() позволяет непосредственно обратиться к объекту Excel по номеру строки и колонки. Поскольку это свойство установлено по умолчанию для рабочего листа Excel, то его можно использовать без дополнительных указаний.
Свойство Cells()позволяет обратиться к ячейке рабочего листа задав номер строки и колонки. Если запись свойства стоит слева от символа равенства (оператор присваивания), то производится запись данных в ячейку таблицы, если справа, то считывание значения из ячейки таблицы. Кроме собственно записи данных свойство Cells() в сочетании со свойствами других объектов (Font, Color и т.п.) позволяет задавать параметры шрифта, его цвет, фон и так далее. Для изучения этих возможностей целесообразно ознакомиться с описанием соответствующих свойств и объектов в литературе, воспользоваться Help-системой или, что вероятно проще всего, запустить режим записи макроса в Ехсеl, выполнить, например, установку цвета и изучить текст полученного макроса.
Пример 10. Использование свойства Cells() для считывания данных в переменную VBA и возврата значения в Excel и установки нового цвета шрифта. Используется тот факт, что положение и количество ячеек в таблице рис. 1. известно. Дополнительно в программе используется символ комментария ' и комбинация символов «пробел»_ (_) для обозначения продолжения длинной строки
Sub Расчет_заработной_платы2()
Dim Начислено(1 To 4) As Currency, Налог(1 To 4) As Currency, _
К_Выдаче(1 To 4) As Currency, i As Integer
i = 1
Начислено(i) = Cells(i + 1, 2) 'В первую ячейке массива Начислено записывается содержимое
'второй строки и второй колонки исходной таблицы Excel
Cells(i + 1, 2).Font.ColorIndex = 7 'В ячейке устанавливается новый цвет шрифта
Налог(i) = Начислено(i) * 0.12 'Рассчитывается значение налога и запоминается
'в соответствующей ячейке
Cells(i + 1, 3) = Налог(i) 'Значение налога возвращается в таблицу Excel
К_Выдаче(i) = Начислено(i) - Налог(i) 'Рассчитывается значение к выдаче
'и запоминается в соответствующей ячейке
Cells(i + 1, 4) = К_Выдаче(i) 'Значение к выдаче возвращается в таблицу Excel
i = i + 1 'Переход к следующей записи
Начислено(i) = Cells(i + 1, 2)
Cells(i + 1, 2).Font.ColorIndex = 7
Налог(i) = Начислено(i) * 0.12
Cells(i + 1, 3) = Налог(i)
К_Выдаче(i) = Начислено(i) - Налог(i)
Cells(i + 1, 4) = К_Выдаче(i)
i = i + 1 'Переход к следующей записи
Начислено(i) = Cells(i + 1, 2)
Cells(i + 1, 2).Font.ColorIndex = 7
Налог(i) = Начислено(i) * 0.12
Cells(i + 1, 3) = Налог(i)
К_Выдаче(i) = Начислено(i) - Налог(i)
Cells(i + 1, 4) = К_Выдаче(i)
i = i + 1 'Переход к следующей записи
Начислено(i) = Cells(i + 1, 2)
Cells(i + 1, 2).Font.ColorIndex = 7
Налог(i) = Начислено(i) * 0.12
Cells(i + 1, 3) = Налог(i)
К_Выдаче(i) = Начислено(i) - Налог(i)
Cells(i + 1, 4) = К_Выдаче(i)
End Sub
Задание
Используйте согласованный с преподавателем вариант задания (табл. 1), выполненную на его основе таблицу Excel и созданный вами макрос. Модифицируйте созданный вами макрос и напишите новую программу так, чтобы ее основные вычисления производились с переменными VBA. При этом исходные данные первоначально должны быть считаны из таблицы, а результаты вычислений возвращены в нее.