Практикум по vba для Microsoft Excel Учебное пособие
 Скачать 2.32 Mb.
|
|
диаграмма, в котором построить диаграмму по выделенному диапазону и разместить эту диаграмму на отдельном листе. Создаем диапазон с числовыми данными. До записи макроса выделяем диапазон. В макросе указываем тип диаграммы (объемная гистограмма), убираем легенду и размещаем диаграмму на отдельном листе. Записанный макрос имеет вид: Sub Диаграмма() ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range("'Лист1'!$A$1:$A$7") ActiveChart.ChartType = xl3DColumnClustered ActiveChart.SetElement (msoElementLegendNone) ActiveChart.Location Where:=xlLocationAsNewSheet End Sub В данном макросе конкретный адрес диапазона данных находится в выделенной строке. Поэтому надо определить адрес выделенного диапазона и подставить его в команду. Sub Диаграмма() s = Selection.Address ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range(s) ActiveChart.ChartType = xl3DColumnClustered ActiveChart.SetElement (msoElementLegendNone) ActiveChart.Location Where:=xlLocationAsNewSheet End Sub 22 Задания для самостоятельной работы 1. Создать макрос итог, к тором для созданной таблицы выполнить форматирование: 1. Для столбцов выполнить автоподбор ширины. 2. Выполнить обрамление таблицы. 3. Для шапки выполнить заливку и задать полужирный шрифт. 4. для строки над таблицей задать объединение ячеек и центрирование по вертикали и по горизонтали. 5. Для последнего столбца задать денежный формат. 6. Под последней колонкой поместить функцию суммирования и выделить ее жирным шрифтом.. 7. Объединить ячейки перед суммой на написать туда слово ИТОГО жирным шрифтом. 2. Создать макрос, который строит график функции по заданным данным. На графике изобразить линии вертикальной и горизонтальной сетки. Убрать легенду. 23 Лабораторная работа № 3 Функции При записи строковой константы ее надо заключать в кавычки. В выражениях, наряду с константами могут встречаться и функции. Функция (function) — это встроенная формула, выполняющая действия над выражениями и генерирующая значение. Функция всегда возвращает значение, которое VBA вставляет в программу в том месте, где появляется имя функции. Функции VBA делятся на несколько групп в зависимости от типа операции или вычисления, которое они выполняют. Чтобы использовать функцию, надо просто ввести имя функции в оператор VBA вместе с любыми аргументами, которые требуются для этой функции, в том месте в операторе, где необходимо использовать результат функции. [Помещение имени функции в оператор VBA для активизации функции называют вызовом (calling) функции.] При использовании функций в выражениях существуют следующие правила: Можно использовать результат функции как часть выражения. Можно присваивать результат функции какой-либо переменной. Можно использовать результат функции для предоставления значения в список аргументов другой процедуры или функции. Функции имеют списки аргументов, заключенные в круглые скобки. В основном функцию можно использовать для предоставления значения в любом месте в любом операторе VBA, где может быть оправданно использование значения константы или переменной. Тип данных значения, возвращаемого функцией, зависит от этой конкретной функции. Большинство функций возвращают значения типа Variant, хотя некото- рые функции возвращают данные определенных типов, таких как String, Double и Integer. VBA во многих случаях может автоматически преобразовывать результат какой-либо функции в данные типа, совместимого с другими типами значений в выражении, содержащем эту функцию, точно, как VBA преобразует типы данных в присваиваниях переменных и вычислении выражений. Встроенные функции VBA делятся на несколько категорий на основе общего назначения функций (математические, преобразования данных, даты и времени, строковые и работы с диском). Далее обсуждаются категории функций и описываются их действия. Большинство функций VBA, такие как математические функции, являются довольно ясными из их названия и не требуют подробного объяснения. Другие функции, такие как функции преобразования типа данных и обработки строк, описаны более подробно. VBA-функции обработки строк имеют важное значение, поэтому рассмотрены способы их использования. 24 Математические функции VBA предоставляет стандартный набор математических функций. В табл. 2 приведены математические функции, имеющиеся в VBA. В этой таблице N означает любое численное выражение; все аргументы функций являются обязательными, если только не указано иначе. Таблица 2 Функции (аргументы) Возвращает /действие Abs(N) Возвращает абсолютное значение N. Atn(N) Возвращает арктангенс N как угол в радианах. Cos(N) Косинус угла N, где N — это угол, измеренный в радианах. Exp(N) Возвращает константу е, возведенную в степень N. (е — это ос- нование натуральных логарифмов и она (приблизительно) равна 2,718282). Fix(N) Возвращает целую часть N. Fix не округляет число, а отбрасы- вает любую дробную часть. Если N является отрицательным, Fix возвращает ближайшее отрицательное целое большее, чем или равное N. Int(N) Возвращает целую часть N. Int не округляет число, а отбрасы- вает любую дробную часть. Если N является отрицательным, Int возвращает ближайшее отрицательное целое меньшее, чем или равное N. Log(N) Возвращает натуральный логарифм N. Rnd(N) Возвращает случайное число; аргумент является необязатель- ным. Используйте функцию Rnd только после инициализации VBA-генератора случайных чисел оператором Randomize. Sgn(N) Возвращает знак числа: -1, если N — отрицательное; 1, если N — положительное; 0, если N равно 0. Sin(N) Возвращает синус угла; N — это угол, измеренный в радианах. Sqr(N) Возвращает корень квадратный из N. VBA отображает ошибку времени исполнения, если N — отрицательное. Tan(N) Возвращает тангенс угла; N — угол в радианах. Функции Fix и Int укорачивают целые, то есть они отбрасывают дробную часть числа без округления. Единственное различие между функциями Fix и Int — это то, как они обрабатывают отрицательные числа. Дополнительные тригонометрические функции можно выводить из базовых математических функций VBA. Например, если необходимо вычислить котангенс угла, для его нахождения можно использовать формулу 1/Таn(х). Примеры использования математических функций: gipot=Sqr(kat2^2+kat2^2) chislo=Int(Rnd(1)*100) 25 Функции преобразования данных Visual Basic предоставляет несколько функций для преобразования одного типа данных в другой. Надо использовать эти функции для устранения ошибок несовпадения типов и обеспечения явного контроля за типами данных в выражениях. Например, при получении сообщения об ошибке несовпадения типов в определенном выражении можно преобразовать значения в выражении в типы, совместимые друг с другом, используя функции преобразования. Или же можно сохранять результат выражения в диапазоне численного типа Single (большинство численных выражений имеют результатом значение типа Double); в таком случае следует использовать функцию CSng для преобразования результата выражения в число типа Single. В табл. 3 приведены функции преобразования данных в VBA. В этой таблице N — это любое численное, S — любое строковое, а Е — выражение любого типа. Аргументы каждой функции являются обязательными, если не указано иначе. Таблица 3 Функция (аргументы) Возвращает/действие Asc(S) Возвращает число кода символа, соответствующее первой букве строки S. Буква "А", например, имеет код символа 65. Chr(N) Возвращает строку из одного символа, соответствующего коду символа N, который должен быть числом между 0 и 255, включительно. Код символа 65, например, возвращает букву "А". Format(E, S) Возвращает строку, содержащую значение, представленное выражением Е, в формате в соответствии с инструкциями, содержащимися в S. Hex(N) Возвращает строку, содержащую шестнадцатиричное представление N. Oct(N) Возвращает строку, содержащую восьмиричное представление N. RGB(N, N, N) Возвращает целое типа Long, представляющее значение основных цветов изображения. N в каждом аргументе должно быть целым в диапазоне 0 — 255, включительно. Аргументы (слева направо) — это значения для красного, зеленого и синего цвета. Str(N) Возвращает строку, эквивалентную численному выражению N. Val(S) Возвращает численное значение, соответствующее числу, представленному строкой S, которая должна содержать только цифры и одну десятичную точку, иначе VBA не может преобразовать ее в число. Если VBA не может 26 преобразовать строку в S, то функция Val возвращает 0. CBool(N) Возвращает Boolean-эквивалент.численного выражения N. CByte(E) Возвращает численное значение типа Byte (от 0 до 255); Е — любое допустимое численное или строковое выражение, которое может быть преобразовано в число. CCur(E) Возвращает численное значение типа Currency; E — любое допустимое численное или строковое выражение, которое может быть преобразовано в число. CDate(E) Возвращает значение типа Date. E может быть любым допус- тимым выражением (строкой или числом), представляющим дату в диапазоне 1/1/100 — 12/31/9999, включительно. CDbl(E) Возвращает численное значение типа Double; E — любое до- пустимое численное или строковое выражение, которое может быть преобразовано в число. CInt(E) Возвращает численное значение типа Integer; E — любое до- пустимое численное или строковое выражение, которое может быть преобразовано в число. CLng(E) Возвращает численное значение типа Long; E — любое допустимое численное или строковое выражение, которое может быть преобразовано в число. CSng(E) Возвращает численное значение типа Single; E — любое допустимое численное или строковое выражение, которое может быть преобразовано в число. CStr(E) Возвращает значение типа String; E — любое допустимое численное или строковое выражение. CVar(E) Возвращает значение типа Variant; E — любое допустимое численное или строковое выражение. Наиболее часто используемые функции — это функции (объединенные в конце табл. 3 в группу), начинающиеся с буквы С (от слова conversion), за которыми следует сокращение имени типа: CStr, CSng, CDbl и так далее. Примеры использования функций преобразования данных: d=CDat(“1.9.2006”) text=CStr(36*3) Функции даты и времени Язык VBA содержит специальный тип данных Date, предназначенный для представления значений даты и времени. Внутри программ значения типа Date представлены как числа с плавающей запятой: целая часть значения отображает дату как количество дней, прошедшее с 30 декабря 1899 года; 27 дробная часть значения представляет время в виде доли 24-часового дня (например, .25 — это 6 часов утра, 5 — это полдень и т.д.); дробное число без целой части отображает время без даты. Для пользователя или программиста представленные таким образом значения даты и времени являются бесполезными. К счастью, в языке VBA предусмотрены инструменты, позволяющие преобразовывать эти значения к более знакомому пользователям виду. Исходное "сырое" представление значений даты и времени в виде чисел с плавающей запятой скрыто от посторонних глаз и используется программой только в процессе проведения внутренних вычислений. Язык VBA способен распознавать значения даты и времени, представленные практически в любом стандартном формате. Чтобы указать на текстовые константы как на значения даты/времени, необходимо заключить их в символы #. Используйте обычный оператор присваивания, чтобы присвоить переменной типа Date значение даты/времени: dt = #01.05.2003# VBA-функции даты и времени обычно используются для получения текущей даты и времени, разбиения значения даты на ее составляющие части или для преобразования строк и чисел в значения типа Date. В таблице 4 приведены VBA-функции даты и времени и их действие. В этой таблице N — любое допустимое численное выражение, a D — любое допустимое выражения типа Date (включая значения типа Date, числа или строки, которые VBA может преобразовать в дату). Все аргументы функций в этой таблице являются обязательными, если не указано иначе. Таблица 4 Функции(аргументы) Возвращает/действие Date Возвращает системную дату. Можно также использовать эту функцию как процедуру для установки системных часов компьютера. Более подробно можно узнать из справочной системы VBA. Time Возвращает системное время компьютера как значение типа Date. Можно также использовать эту функцию как процедуру для установки системных часов. Более подробно можно узнать из справочной системы VBA. Now Возвращает системную дату и время. Year(D) Возвращает целое, являющееся частью выражения типа Date и содержащее год. Год возвращается как число между 100 и 9999. Month(D) Возвращает целое, являющееся частью выражения типа Date, содержащее месяц. Месяц возвращается как число между 1 и 12, включительно. 28 Day(D) Возвращает целое, являющееся частью выражения типа Date и содержащее день. День возвращается как число между 1 и 31, включительно. Weekday(D) Возвращает целое, содержащее день недели для выражения типа Date. День недели возвращается как число между 1 и 7, включительно; 1 — это воскресенье, 2 — понедельник и так далее. WeekdayName(N1,B, N) Возвращает строку с наименованием дня недели, номер которого задается параметром N1. Hour(D) Возвращает целое, содержащее часы как часть времени, содержащегося в выражении типа Date. Часы возвращаются как число между 0 и 23, включительно. Если выражение D не содержит значения времени, то Hour возвращает 0. Minute(D) Возвращает целое, содержащее минуты как часть времени в выражении типа Date. Минуты возвращаются как число между 0 и 59, включительно. Если выражение D не содержит значения времени, Minute возвращает 0. Second(D) Возвращает целое, содержащее секунды как часть времени в выражении типа Date. Секунды возвращаются как число между 0 и 59, включительно. Если выражение D не содержит значения времени, Second возвращает 0. DateAdd(S, N, D) Возвращает значение [тип Variant (Date)], содержащее дату, к которой добавлен заданный интервал времени. DateDiff(S, D1, D2[, N1 [, N2 ]]) Возвращает значение [тип Variant (Long)] числа временных интервалов между двумя определенными датами. DatePart(S, D,[,N1 [, N2]]) Возвращает определенную часть [тип Variant (Integer)] заданной даты. DateSerial(N, N,N) Возвращает значение последовательной даты для заданной даты. Слева направо аргументы представляют год, месяц и день. Аргумент года должен быть целым числом между 100 и 9999, месяца — между 1 и 12, дня — между 1 и 31 (все диапазоны являются включающими). TimeSerial(N, N,N) Возвращает значение последовательного времени. Слева направо аргументы представляют часы, минуты и секунды. Аргумент часов должен быть целым числом между 0 и 23, аргументы минут и секунд должны оба быть числами 0 и 59 (все диапазоны являются включающими). 29 DateValue(E) Возвращает значение типа Date, эквивалентное дате, заданной аргументом Е, который должен быть строкой, числом или константой, представляющей дату. TimeValue(E) Возвращает значение типа Date, содержащее время, заданное аргументом Е, который может быть строкой, числом или константой, представляющей время. Timer Возвращает число, представляющее количество секунд от полуночи в соответствии с системным временем компьютера. Примеры использования функций даты и времени: god=year(Date()) nomer_den=WeekDay(#25/12/2006#)-1 Строковые функции Строковые функции VBA часто применяются для нахождения заданных строк внутри других строк, для сравнения одной строки с другой и копирования выбранных частей строк. Строковые функции VBA используются довольно часто, потому что строковые данные очень важны и встречаются в каждом приложении VBA — Word, Excel, Access или другом host-приложении VBA. Часто необходимо манипулировать строковыми данными, полученными как пользовательский ввод функцией InputBox. В других случаях строковые данные появляются в коде VBA как имена файлов для документов Word, рабочих книг Excel, баз данных Access и других типов данных, сохраняемых в файлах на дисках. В Word важность манипулирования строковыми данными очевидна: данные, сохраняемые в документах Word, в основном являются ничем иным, как строковыми данными. Строковые данные также важны в Excel в качестве имен рабочих листов, именованных диапазонов данных и так далее. В этом разделе приводятся имеющиеся в VBA строковые функции; в последующем разделе более подробно описывается, как использовать наиболее важные и полезные строковые функции. В табл. 5, где приведены основные строковые функции VBA, N — это любое допустимое численное выражение, a S — это любое допустимое строковое выражение. Все аргументы функций являются обязательными, если не указано иначе. Таблица 6 Функция(аргумент) Возвращает/действие InStr([Nl,] S1, S2[, N2]) Возвращает положение S2 в S1. N1 — начальное положение для поиска; N2 определяет тип сравнения. N1 и N2 необязательны. Если N2 опускается, то для поиска используется текущая установка Option Compare. 30 InStrRev(Sl, S2 [, Nl[, N2]]) Возвращает позицию появления строки S2 внутри S1, в направлении от конца (или N1) к началу строки. N2 определяет тип сравнения. Если N2 опускается, то для поиска используется текущая установка Option Compare. LCase(S) Возвращает строку (тип String), содержащую копию S со всеми символами верхнего регистра, преобразованными в символы нижнего регистра. Left(S, N) Возвращает строку; копирует N символов из S, начиная с левого крайнего символа S. Len(S) Возвращает число символов в S, включая начальные и конечные пробелы. LTrim(S) Возвращает копию строки S после удаления символов пробела из левой части строки (начальные пробелы). Mid(S, N1, N2) Возвращает строку; копирует N2 символов из S, начиная с позиции символа в S, заданной аргументом N1. N2 является необязательным; если N2 опущен, то |