Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
|
Лабораторная работа № 7 |
1. Цель работ
Изучить методы создания, использования и вызова процедур, методы создания объекта Range, его терминальные своиства и методы. Исследовать использование процедуры для заполнения объекта Range значениями при вычислении значений функций.
2. Введение
Для создания приложений, работающих в среде Excel, необходимо воспользоваться встроенным редактором Visual Basic для создания функции или процедуры пользователя.
Процедуры
Процедура - ряд инструкций Visual Basic, заключенных между оператором Sub и оператором End Sub, которые выполняют действия, но не возвращают значение. Процедура Sub может получать параметры типа констант, переменных, или выражений, которые переданы процедурой вызова. Если процедура Sub не имеет никаких параметров, оператор Sub все равно должен включить пустые круглые скобки.
Примеры:
' Объявляется процедура с именем GetInfo
' Эта процедура не имеет аргументов
Sub GetInfo()
' Объявляется переменная типа string с именем answer
Dim answer As String
' Переменная answer получает значение, возвращаемое функцией InputBox
answer = InputBox(Prompt:="Как вас зовут?")
If answer = Empty Then
' Вызывается функция MsgBox
MsgBox Prompt:="Вы не ввели свое имя."
Else
' К функции MsgBox добавляется переменная answer
MsgBox Prompt:="Ваше имя" & answer
End If
'Конец процедуры
End Sub
Оператор Sub объявляет имя, параметры и код, который формируют тело процедуры Sub.Синтаксис оператора:
[Private | Public | Friend] [Static] Sub name [(arglist)]
[Инструкции]
[Exit Sub]
[Инструкции]
End Sub
Оператор Sub имеет следующие параметры:
|
Параметр |
Описание |
|
Public |
Факультативный. Указывает, что процедура Sub доступна для всех других процедур во всех модулях. Если используется в модуле, который содержит инструкцию Option Private, процедура не доступна вне проекта. |
|
Private |
Факультативный. Указывает, что процедура Sub доступна только другим процедурам в модуле, где она объявлена. |
|
Friend |
Факультативный. Используется только в модуле класса. |
|
Static |
Факультативный. Указывает, что локальные переменные процедуры сохраняются между обращениями к ней. Атрибут Static не воздействует на переменные, которые объявлены вне процедуры, даже если они используются в процедуре. |
|
name |
Обязательный. Имя процедуры. |
|
arglist |
Факультативный список аргументов. Список параметров переменных, которые переданы к процедуре Sub, когда она вызвана. Переменные отделяются запятыми. |
|
Инструкции |
Факультативный. Любая группа операторов, которые будут выполнены внутри процедуры Sub. |
Список аргументов имеет следующий синтаксис и параметры:
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]
|
Параметр |
Описание |
|
Optional |
Факультативный. Ключевое слово, указывающее, что параметр не обязателен, и его можно опустить при вызове процедуры. Если Optional используется, все необязательные параметры в списке аргументов должны размещаться в конце списка аргументов. Optional не может использоваться, если используется массив аргументов ParamArray. |
|
ByVal |
Факультативный. Указывает, что параметр передан по значению. |
|
ByRef |
Факультативный. Указывает, что параметр передан по ссылке. В Visual Basic ByRef - значение по умолчанию. |
|
ParamArray |
Факультативный. Используется только как последний параметр в списке аргументов, чтобы указать, что заключительный параметр является необязательным массивом различных аргументов. Ключевое слово ParamArray позволяет обеспечивать произвольное число параметров. ParamArray не может использоваться с ByVal, ByRef, или Optional. |
|
varname |
Требуемый. Имя переменной. |
|
type |
Факультативный. Тип данных параметра, переданного процедуре; может быть Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (в настоящее время не поддерживается), Date, String, Object, Variant или специфический объектный тип. Если параметр не Optional, тип может также быть определен пользователем. |
|
defaultvalue |
Факультативный. Любая константа или постоянное выражение. Имеет силу только для параметров Optional. Если тип - Object, явное значение по умолчанию может быть только Nothing. |
Замечания. Если процедура явно не определенно как Public, Private, или Friend, по умолчанию процедура Sub принимается Public. Если параметр Static не используется, значение локальных переменных не сохраняется между обращениями к процедуре.
Предостережение. Процедуры Sub могут быть рекурсивными; то есть они могут вызывать сами себя. Однако использование рекурсии может привести к переполнению стека. Ключевое слово Static обычно не используется с рекурсивными процедурами Sub.
Весь выполняемый код должен быть в процедурах. Нельзя определять процедуру Sub внутри другой процедуры, функции или свойствах.
Ключевые слова Exit Sub вызывают непосредственный выход из процедуры. Выполнение программы продолжается с инструкции, следующей за инструкцией вызова процедуры Sub.
Подобно функции Function, процедура Sub - отдельная процедура, которая может получать параметры, выполнять ряд инструкций и изменять значение параметров. Однако, в отличие от Function, которая возвращает значение, процедура Sub не может использоваться в выражении.
Переменные, используемые в процедурах Sub, относятся к двум категориям: явно объявленные внутри процедуры, и нет. Переменные, которые явно объявлены в процедуре (с использованием Dim) всегда локальны для процедуры. Переменные, которые используются, но не объявлены явно в процедуре, также локальны, если они не объявлены явно на более высоком уровне вне процедуры.
Примечание. Вы не можете использовать GoSub, GoTo, или Return, чтобы войти или выйти из процедуры Sub.
Вызов процедур и функций
Чтобы вызывать процедуру Sub из другой процедуры, введите имя процедуры, и укажите значения для требуемых параметров. Инструкции Call не требуется, но если она используется, нужно добавить все параметры в круглые скобки.
Вы можете использовать процедуру Sub, чтобы создать другие процедуры, более простые для понимания и отладки.
Примеры:
В следующем примере, процедура Main вызывает процедуру MultiBeep, передавая ей значение параметра 56. После того, как процедура MultiBeep выполнится, управление возвращается процедуре Main, и она называет процедуру Message. Процедура Message отображает окно сообщений; когда пользователь нажимает кнопку OK, управление возвращается процедуре Main, и она заканчивается.
Sub Main()
MultiBeep 56
Message
End Sub
Sub MultiBeep(numbeeps)
For counter = 1 To numbeeps
Beep
Next counter
End Sub
Sub Message()
MsgBox "Time to take a break!"
End Sub
В следующем примере показано два способа вызвать процедуру Sub с числом параметров больше чем одним. При втором вызове процедуры HouseCalc требуются круглые скобки вокруг параметров, потому что используется инструкция Call.
Sub Main()
HouseCalc 99800, 43100
Call HouseCalc(380950, 49500)
End Sub
Sub HouseCalc(price As Single, wage As Single)
If 2.5 * wage <= 0.8 * price Then
MsgBox "You cannot afford this house."
Else
MsgBox "This house is affordable."
End If
End Sub
Объекты Range и Selection
Все объекты Excel имеют только свойство Range. Это свойство имеют и верхний в иерархии объект Application и Worksheet и сам объект Range, представляющий объекты нижнего уровня вплоть до ячейки. Синтаксис этого свойства следующий:
Property Range (Cell1 [, Сеll2]) As Range
С объектом Selection дело обстоит не так просто, поскольку в Excel нет класса объектов Selection. Объект Selection возникает двояко - либо в результате работы метода Select, либо при вызове свойства Selection. Тип полученного объекта может быть различным и определяется типом выделенного объекта. Чаще всего, объект Selection принадлежит классу Range и при работе с ним можно использовать свойства и методы объектов класса Range.
Вернемся к объекту Range, создаваемого, чаще всего, при вызове свойства Range тех или иных объектов Excel. Что может быть задано в качестве параметров Call1 и Call2? Рассмотрим этот вопрос подробнее. Прежде всего, нужно понимать, что Range уникальный объект - он может представлять как единственную ячейку таблицы, так и столбец или строку, некоторую связную и не связную прямоугольную область, а также объединение всех подобных элементов. Это же касается и объекта Selection. Начнем с простых примеров:
Примеры:
Workbooks("BookOne").Activate
Worksheets("Sheet2").Activate
Range("A1") = 5
Range("A2") = "=A1+2"
Range("A3:A4") = "A1+A2"
Обратите внимание на три момента:
По-видимому, лучше писать подобные присваивания в таком виде:
Примеры:
Range("B1").Value = 7
Range("B2").Formula = "=B1+2"
Range("B3:B4").Formula = "=B1+B2"
Следующий пример демонстрирует важную для понимания относительность ссылок, задаваемых параметром Cell. Этот параметр задает ссылки относительно своего начала.
Примеры:
Dim myRange As Range
Set myRange = Range("C1:C4")
myRange.Range("A1") = 7
myRange.Range("A2") = "=A1+2"
myRange.Range("A3:A4") = "=A1+A2"
Вначале создается объект myRange, заданный диапазоном "С1:С4". Вызов myRange.Range("A1") создает новый объект из одной ячейки А1 объекта myRange. Такая ячейка у него есть, эта ссылка задает ячейку С1 в абсолютных адресах. Заметьте, что при попытке обратиться к ячейке, например В1, объекта myRange возникнет ошибка, так как наш объект имеет только четыре ячейки в первом столбце. Остается еще заметить, что если в левой части операторов присваивания А1 и А2- это ссылки относительно объекта myRange, то в формулах правой части А1 и А2 привязаны к абсолютным адресам.
Следующий пример демонстрирует работу с объектом Selection:
Примеры:
Range("D1:D4").Select
Selection.Range( "А1") = 7
Selection.Range("A2") = "=A1+2"
Selection.Range("A3:A4") = "=A1+A2"
Рассмотрим теперь вызов Range с двумя параметрами:
Примеры:
Dim myRange1 As Range
Set myRange1 = Range("E1", "E6")
Debug.Print myRange1.Count
myRangel.Range( "А1") = 7
myRangel.Range( "А2") = "=A1+2"
myRangel.Range("A3:A6") = "=A1+A2"
Здесь в роли параметров метода Range выступают ячейки, первая из них определяет левый верхний, а вторая - правый нижний элемент диапазона объекта Range. Далее мы с этим объектом работаем также как и в предыдущем примере. В качестве параметров Cell1 и Се112 могут выступать не только ячейки, но и объекты Range, что и демонстрирует следующий пример. Возвращаемый объект Range в этом случае представляет объединение областей, охватывающее область первого и второго объектов.
Примеры:
Dim myRange2 As Range
myRange2 = Range(myRange, myRange1)
Debug.Print myRange2.Count,
myRange2.Select
В предыдущем примере мы показали , как можно построить объединение диапазонов, дающее в результате новый непрерывный диапазон, включающий области обоих объектов. Покажем теперь, как построить "настоящее" объединение и пересечение диапазонов. В настоящем объединении могут быть разные диапазоны. Оно включает те и только те ячейки, которые принадлежат объединяемым объектам. Чтобы задать объединение или пересечение диапазонов, нужно вызвать Range с одним параметром. Этот параметр в этом случае представляет список элементов, разделенный знаком объединения - "," (запятая) или знаком пересечения " " (пробел). Каждый элемент списка представляет диапазон, возможно, ячейку.
Примеры:
Dim myRange3 As Range
'Пример пересечения
Set myRange3 = Range("A6:E6 Е1:Е6")
'Пересечение состоит из одного элемента E6
Debug.Print myRange3. Count myRange3.Select
'Пример объединения
Dim myRange4 As Range
Set myRange4 = Range("A6:E6, Е1:Е6")
'Объединение содержит 11 (!) элементов
Debug.Print myRange4.Count
Range4.Select
Следует обратить внимание на несколько моментов. Во-первых, выделяется хотя и связная, но не прямоугольная область - такой уголок, состоящий из строки и столбца. Во-вторых, наше объединение не совсем "настоящее". В математике общие элементы присутствуют в одном экземпляре. Здесь же объединяются списки элементов без всякого их выбрасывания, так что "угол" E6 будет присутствовать дважды. В третьих, заметьте, при построении пересечения и объединения нельзя использовать переменные - диапазоны должны быть заданы константами. Следующий пример приводит к ошибке, если, конечно, убрать знаки комментария.
Примеры:
Dim myRange5 As Range
Set myRange5 = Range("myRange1, myRange2")
Наконец, покажем, что в одном Range можно строить сколь угодно сложное объединение и пересечение элементов. Приведем для экзотики такой пример:
Примеры:
Dim myRange5 As Range
Set myRange5 = Range("A6:E6, Е1:Е6, C1:C6 B5:D5")
Print myRange5.Count
MyRange5.Select
Адресация ячеек
До сих пор в примерах мы использовали так называемую относительную адресацию ячеек таблицы в формате А1. Имя ячейки в этом формате строится из имени столбца (их 256 - A, B,...Z, АВ, ...AZ,....HZ, 1А, ...IV) и номера строки (1.. 16384). Адреса ячеек в этом формате, как мы видели при рассмотрении свойства (метода) Range, являются относительными. В этом случае начало координат, задающее нумерацию строк и столбцов, связывается с объектом, вызвавшим Range. Относительные ссылки вещь весьма полезная. Но иногда наряду с относительными ссылками требуются и абсолютные ссылки Excel достаточно гибок в этом вопросе и предоставляет возможность независимых абсолютных ссылок на строку и столбец. Признаком абсолютной ссылки является знак "$", предшествующий имени строки и (или) столбца. Так что адреса: Z10, Z$10, $Z10, $Z$10 в зависимости от контекста могут именовать одну и ту же или четыре разные ячейки. Адрес ячейки на рабочем листе является лишь частью полного адреса, который, в общем случае, включает адрес листа и адрес книги. При задании полного адреса имя листа сопровождается знаком "!". Имя книги должно заключаться в квадратные скобки. Эта книга должна быть открыта.
Примеры:
Debug.Print Range("$A$1").Value
Debug.Print Range("Sheet2!$A$1").Value
Debug.Print Range("[BookOne.xls]Sheet2!$A$1").Value
А вот результаты отладочной печати:
5
5
5
В первой строке приведена абсолютная ссылка на ячейку А1 текущего рабочего листа (Sheet2), во второй - ссылка на эту же ячейку, но имя включает и имя листа, в третьей - дано полное имя, включающее имя книги.
Смещение и свойство Offset
Мы только что сказали, что при создании объектов Range нельзя пользоваться смещением - доступен только формат А1. Тем не менее, можно использовать смещение, чтобы переходить от одного объекта Range к другому, например от одной ячейки к другой, отстоящей от первой на определенном расстоянии. Достигается это благодаря свойству Offset объекта Range. Это свойство, или если хотите метод, имеет два параметра: RowOffset и ColumnOffset - смещение по строкам и столбцам, и возвращает новый объект Range, отстоящий от прежнего на заданное расстояние. Вот пример создания нового объекта смещенного относительно исходного:
Примеры:
Dim myRange As Range, myRange1 As Range
Set myRange = Range( "А1 :A4")
Set myRange1 = myRange.0ffset(2, 3)
myRange1.Select
Приведем еще один пример, когда смещение используется при работе с ячейками. Заодно продемонстрируем ряд полезных функций, позволяющих проанализировать тип значения, хранящегося в ячейках таблицы:
Примеры:
For Each currcell In Range("E1:E6").Cells
If Application.WorksheetFunctlon.IsText(currcell.Value) Then
curcell.0ffset(0, 1).Formula = "Text"
ElseIf Application. WorksheetFunction.IsNumber(currcell.Value) Then
currcell.0ffset(0, 1).Formula = "Number"
ElseIf Application. WorksheetFunction.IsLogicaKcurrcell.Value) Then
currcell.0ffset(0, 1).Formula = "Logical"
ElseIf Application. WorksheetFunction.IsError(currcell.Value) Then
currcell.0ffset(0, 1).Formula = "Error"
ElseIf currcell.Formula = "" Then
currcell.0ffset(0, 1).Formula = "Пусто"
End If
Next currcell
Свойства и методы
Об объекте Range можно говорить сколь угодно долго - это основа Excel. Вы будете возвращаться к нему неоднократно во время выполнения курсовой работы. Приведем в заключение краткую характеристику некоторых его свойств и методов. Начнем с таблицы свойств:
|
Свойства |
Описание |
|
Address |
Возвращает строку, задающую ссылку на Range объект. Эту ссылку можно выдавать в формате А1 или R1C1, как абсолютную или относительную. Вид возвращаемого значения определяют параметры этого свойства (метода). |
|
Ce11s, |
Возвращаются коллекции ячеек, столбцов или строк, входящие в объект Range. Возвращаемые объекты также являются Range объектами. Последние два свойства возвращают один или несколько столбцов или строк, охватывающих объект Range. |
|
Text, |
Свойство Text возвращает строку текста, связанного с Range объектом (ячейкой). Имеет статус только для чтения. Если нужно изменить весь текст или его часть, то можно использовать свойство (метод) Characters, два параметра которого: Start и Length позволяют выделить требуемую подстроку текста. |
|
Column, |
Возвращают соответственно номер первого столбца или первойстроки в области объекта Range. |
|
CarrentArray, |
Первое из этих свойств возвращает весь массив, частью которого является объект Range (ячейка). Второе - возвращает текущий регион, то есть минимальную прямоугольную область, содержащую элементы из Range и окаймленную пустыми строками и столбцами или границами таблицы. Если Range представляет связную область, то текущий регион охватывает Range, для несвязной области он выделяет лишь некоторую его часть. |
|
Precedents, |
В первых двух случаях возвращается объект Range, содержащий все ячейки, зависимые или предшествующие ячейкам исходного объекта. Результат обычно представляет несвязную область. В двух последних случаях возвращаются только непосредственно зависимые и непосредственно предшествующие ячейки. Возможно, стоит определить понятия зависимых и предшествующих ячеек. Если формула в ячейке Y содержит ссылку на ячейку X, то говорят, что Y непосредственно зависит от X, а Х непосредственно предшествует Y. Обобщая понятие непосредственной зависимости, мы говорим: Y зависит от X, а Х предшествует Y, если существует цепочка ячеек Z1, Z2, ...ZK, начинающаяся с Х и заканчивающаяся Y, такая, что каждые два соседние элемента цепочки связаны отношением непосредственной зависимости (непосредственного предшествования). |
|
Formula, |
Первое из них позволяет прочесть или задать формулу в формате А1, второе - в формате R1C1. |
|
NumberFormat |
Позволяет выяснить или установить код формата данных. |
|
PivotTable |
Возвращает сводную таблицу - объект PivotTable, если таковой содержится в Range. |
|
Value |
Значение указанной ячейки. Если она пуста, то возвращается значение Empty, что можно проверить, вызвав функцию IsEmpty. Если объект Range содержит более одной ячейки, то возвращается массив значений, что можно проверить, вызвав функцию IsArray. |
Дадим теперь краткую характеристику некоторых методов:
3. Индивидуальное задание
4. Содержание отчета