Практикум по vba для Microsoft Excel Учебное пособие
 Скачать 2.32 Mb.
|
|
1. Задания для самостоятельной работы 2. Создать макрос, который объединяет выделенные ячейки и форматирует текст в этой объединенной ячейке по центру по горизонтали и по вертикали. 3. Создать макрос, который защищает лист с паролем. 4. Создать макрос, который снимает защиту листа с паролем. Лабораторная работа № 2 Объекты листа Cвойство объекта Range Свойство Range возвращает объект Range, определяемый аргументами. Используются два разных способа записи свойства Range. Первый способ object.Range(Cell1) Второй способ object.Range(Cell1 [,Cell2]) object - ссылка на объект, например, на рабочий лист или на интервал ячеек. Ссылка необязательна. По умолчанию используется активный лист; 12 Cell1, Cell2 - аргументы для задания интервала ячеек. Cell1 - указание обязательно при обоих способах записи свойства Range. Первый способ Аргумент Cell1 задает интервал ячеек произвольного размера. ВАЖНО Могут использоваться имена, определенные в таблице, или координаты ячеек, столбцов, строк или интервалов. Координаты задаются в стиле A1. Координаты и имена заключаются в кавычки. При задании интервалов координаты левого верхнего угла и правого нижнего угла интервала разделяются двоеточием. Для задания несмежных интервалов используется запятая. Для задания пересечения интервалов используется пробел. Примеры записи оператора Range (1 способ) Запись Возвращаемый объект ActiveSheet.Range("A1:A10") интервал ячеек A1:A10 на активном листе Range("A:B") столбцы A:B Range("налог") интервал с именем налог Range("1:3") строки с первой по третью Range("A1:C2, B10:D24") объединение двух несмежных интервалов A1:C2 и B10:D24 Range("A1:C10 B10:D24") пересечение двух интервалов A1:C10 и B10:D24, т.е. интервал B10:C10 Второй способ Аргументы задают координаты интервала: Cell1 - единственная ячейка (строка или столбец), задающая левый верхний угол интервала; Cell2 - единственная ячейка (строка или столбец), задающая правый нижний угол интервала. Необязательный аргумент. Допустимо задание аргументов переменными, выражениями, свойствами или методами, представляющими объект Range - одну ячейку, одну строку или один столбец рабочего листа. Примеры записи оператора Range (2 способ) Запись Возвращаемый объект Range("A5","D18") интервал A5:D18 Range(Columns(1), Columns(5)) интервал, содержащий первые пять столбцов рабочего листа 13 ЗАПОМНИТЕ Если свойство Range применяется к объекту Range, то ссылка на интервал ячеек считается относительной и возвращается смещенный объект Range. Например, если выделен интервал C1:D5, то запись Selection.Range("B2") возвратит ячейку D2. Свойство Cells Свойство Cells возвращает единственную ячейку рабочего листа, которая находится на пересечении строки и столбца, задаваемых целыми числами. Синтаксис object.Cells (RowIndex,ColumnIndex) object - ссылка на объект. Ссылка необязательна. По умолчанию используется активный лист; RowIndex - индекс строки; ColumnIndex - индекс столбца. ЗАМЕЧАНИЯ В свойстве Cells индекс строки является первым аргументом, а индекс столбца - вторым аргументом, тогда как при задании адреса ячейки в стиле A1 сначала указывается столбец, а затем строка. Понятие "индекс" ( Index, ColumnIndex, RowIndex ) всегда подразумевает целое число, целочисленную переменную или выражение, результат вычисления которого есть целое число или может быть преобразован в целое число. Примеры записи свойства Cells Запись Комментарий Возвращаемый объект ActiveSheet.Cells Свойство Cells без аргументов все ячейки активного рабочего листа Range("C5:C10").Cells(1,1) Свойство Cells применяется к объекту Range (относительная ссылка) ячейка C5 Range(Cells(7,3),Cells(10,4)) Свойство Cells используется в качестве аргументов свойства Range интервал ячеек C7:D10 Свойство Offset Свойство Offset позволяет задавать ячейки или интервалы при помощи числа строк и колонок, которые отделяют нужную ячейку от исходной ячейки, т.е. указывая смещение относительно выбранной ячейки. Например, Range("A5").Offset(-2,1) возвращает ячейку B3. Синтаксис object.Offset([RowOffset][,ColumnOffset]) 14 object - ссылка на объект Range. Ссылка обязательна и определяет объект, относительно которого задается смещение; RowOffset - смещение строки искомой ячейки относительно исходной ячейки; ColumnOffset - смещение столбца искомой ячейки относительно исходной ячейки. Необязательные аргументы RowOffset и ColumnOffset - числовые выражения. Если какой-то аргумент не задан, то соответствующее смещение равно нулю. Например, если выделен интервал C1:D5, то запись Selection.Offset(2,1).Select выделяет интервал D3:E7. Метод Union и свойство Areas Метод Union используется для объединения двух и более объектов Range, заданных ссылками на непересекающиеся интервалы, в один объект Range. Синтаксис Object.Union (arg1,arg2,...) object - всегда объект Application. Ссылка необязательна; arg1,arg2 - интервалы ячеек. Количество аргументов произвольно. Обязательно наличие хотя бы двух аргументов. Например, оператор Union(Range("A1:C5"),Range("B10:D12")).Select выделяет несмежные интервалы A1:C5 и B10:D12. Свойство Areas выполняет обратное действие, разделяя объединенные интервалы на несколько объектов Range. Синтаксис Object.Areas(index) object - ссылка на объект Range, состоящий из нескольких интервалов; index - номер интервала в объекте. Аргумент необязателен. Примеры Оператор Комментарий Результат p=Union (Range("A1:C5"), Range("B10:D12")).Areas(2).Count Если аргумент задан, то свойство Areas возвращает интервал - объект Range, определенный индексом интервала равен девяти, так как во втором интервале ровно 9 ячеек p=Union(Range("A1:C5"), Range("B10:D12")).Areas.Count Cвойство Areas без аргументов рассматривает каждый из несмежных интервалов как элемент коллекции объектов Range равен двум, так как объект, определенный методом Union, состоит из двух областей - коллекции из двух элементов 15 p=Range("B10:D12").Areas.Count равен единице, так как объект Range представляет один элемент коллекции Свойства Column и Row (R/O Integer) Свойства возвращают целое число, показывающее индекс первого столбца или первой строки соответственно для заданного объекта. Синтаксис свойств object.Column object Row object - обязательная ссылка на объект Range. Например, запись Range("C5").Column возвращает число 3, а запись Range("C5").Row возвращает число 5. Свойства Columns и Rows Свойство Columns (не путайте со свойством Column!) возвращает объект Range, представляющий колонку или коллекцию колонок в объекте, к которому это свойство было применено. Синтаксис Object.Columns(index) object - ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист; index - индекс колонки в объекте. Например, запись Columns(1) возвращает колонку A активного рабочего листа, а запись Range("C1:D5").Columns(1) возвращает колонку C заданного интервала, а именно, ячейки C1:C5. ВАЖНО Если не указан индекс колонки, то возвращаются все колонки объекта в виде объекта Range. Индекс колонки можно указывать числом или буквой, при этом буква заключается в кавычки. Ссылки Columns(2) и Columns("B") указывают на одну и ту же колонку B. Свойство Rows (не путайте со свойством Row!) возвращает объект Range, представляющий строку или коллекцию строк в объекте, к которому это свойство было применено. Синтаксис Object.Rows(index) object - ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист; index - индекс строки в объекте. ВАЖНО Если не указан номер строки, то возвращаются все строки объекта в виде объекта Range. 16 Например, оператор nr=Selection.Rows(Selection.Rows.Count).Row позволяет получить номер последней строки в выделенном интервале ячеек. Свойство CurrentRegion Текущий регион (CurrentRegion ) - это диапазон ячеек, ограниченный пустыми строками и колонками или сочетанием пустых строк, колонок и границ рабочего листа.Пример В процедуре сравниваются значения первой ячейки первой строки и первой ячейки каждой следующей строки заполненного данными интервала, включающего первую ячейку. Если значения совпадают, то очередная строка удаляется. Предполагается, что данные начинаются с ячейки A1 и занимают несколько строк и столбцов, при этом расположены не плотно, т.е. внутри интервала с данными могут находиться пустые строки или пустые столбцы. Анализируются только строки заполненного данными интервала ячеек вокруг ячейки A1, не содержащего пустых строк и столбцов. Cвойства, связанные с шириной и высотой ячейки Свойства Примеры и комментарии ColumnWidth (R/W Variant) Возвращает или изменяет ширину колонки в единицах, эквивалентных одному символу в стиле Обычный ( Normal ). Шрифт стиля по умолчанию Arial Cyr и размер шрифта 10. Range("A1").ColumnWidth=15 устанавливает ширину колонки A в 15 символов Width (R/O Variant) Возвращает ширину интервала ячеек в пунктах. Range("A1").Width возвращает значение 93.75, если ширина колонки 15 символов, шрифт Times New Roman, размер шрифта 12 пунктов (72 пункта равны 1 дюйму или приблизительно 2,54 см). Debug.Print Range("A1:C3").ColumnWidth распечатает значение 8.43, а оператор Debug.Print Range("A1:C3").Width распечатает значение 144, если для колонок установлена стандартная ширина, шрифт Arial Cyr и размер шрифта 10 RowHeight (R/W Variant) Возвращает или изменяет высоту строк интервала в пунктах. ActiveCell.RowHeight = 14 устанавливает высоту строки, в которой находится активная ячейка, в 14 пунктов Height (R/O Variant) Возвращает суммарную высоту интервала строк, зависящую от названия и размера шрифта. Если шрифт Arial Cyr и размер шрифта 10, то Debug.Print Range("A1").Height распечатает 12,75 и Debug.Print Range("A1:C3").Height распечатает 38,25 WrapText (R/W Boolean) Range("A1").WrapText=True Значение True разбивает текст ячейки на несколько строк, если 17 ширина столбца недостаточна для размещения текста целиком ЗАМЕЧАНИЕ Свойства Width и Height имеют статус Read-Only для объектов Range, но для других объектов, например, для объекта Window, они имеют статус Read-Write. Свойство Address Address — позволяет вернуть адрес текущего диапазона. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel. На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций Методы Select и Activate Метод Select выделяет интервал ячеек. Синтаксис object.Select(Replace) object - выделяемый объект типа Range. Ссылка на объект обязательна; Replace - для расширения выделения аргумент устанавливается в False. Если аргумент не задан или принимает значение True, то вместо старой области выделения создается новая область выделения. Необязательный параметр. Метод Activate активизирует единственную ячейку. Синтаксис object.Activate object - активизируемая ячейка. Ссылка на объект обязательна. Примеры Оператор Активная ячейка Range("C7:E9").Select C7 Range("C7:E9").Offset(1,1).Activate D8 Range("C7:E9").Activate C7 Range("C7:E9").Cells(2,1).Activate C8 ЗАМЕЧАНИЯ Активная ячейка выделяется фоном среди всех выделенных ячеек. Метод Select выделяет интервал ячеек, тогда как метод Activate активизирует только одну ячейку. При использовании метода Select первая ячейка интервала становится активной. 18 Если выделена только одна ячейка, то она является активной и свойства ActiveCell и Selection возвращают одну и ту же ячейку (объект Range ). Метод Clear Очищает интервал ячеек, изменяя, таким образом, свойство Value каждой ячейки интервала. Задание 1 Создать 2 макроса. 1. Макрос оформление. Выполнить обрамление для созданной таблицы. Залить первую строку и задать для нее жирный шрифт. 2. Макрос ряд. Для выделенного диапазона (столбца) получить натуральный ряд. Первоначальный вариант макроса оформление. Sub оформление() Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlDouble .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeTop) .LineStyle = xlDouble .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeRight) .LineStyle = xlDouble .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin 19 End With Range("E6:H6").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With Selection.Font.Bold = True End Sub Заливка строки в данной макросе будет выполняться не правильно для произвольной таблицы, т.к. выделение привязывается к конкретному диапазону. (выделенная строка) Для решения этой задачи надо запомнить первоначально выделенный диапазон в объектной переменной в начале макроса. Dim r As Range Set r = Selection Перед заливкой надо выделить первую строку этого диапазона, а затем выполнить заливку. Для этого надо вместо выделенной строки в тексте макроса выполнить команду: r.rows(1).Select Окончательный вариант макроса оформление. Sub оформление() Dim r As Range Set r = Selection Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlDouble .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeTop) .LineStyle = xlDouble .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlEdgeRight) .LineStyle = xlDouble .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThick End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous 20 .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With r.Rows(1).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With Selection.Font.Bold = True End Sub Для получения макроса ряд натуральные числа следует получить через команду прогрессия. Первоначальный вариант макроса таблица Sub ряд() ActiveCell.FormulaR1C1 = "1" Range("E6:E24").Select Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False End Sub Для создания макроса для произвольного диапазона следует исключить конкретные адреса. Окончательный вариант макроса ряд Sub ряд() Dim r As Range Set r = Selection „Сохранение выделенного диапазона n = r.Row 'Номер верхнего ряда m = r.Column 'Номер левого столбца k = r.Rows.Count 'Количество выделенных рядов Cells(n, m).Select ActiveCell.FormulaR1C1 = "1" Range(Cells(n, m), Cells(n + k, m)).Select Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False End Sub Задание 2 В некоторых командах надо указывать конкретный адрес диапазона. Для этого используется свойства Address. Создать макрос сумма, который суммирует числа в выделенном диапазоне и помещает результат под этим диапазоном в последний столбец. Заполним диапазон какими-нибудь числами. В макросе вызовем функцию суммирования и укажем диапазон суммирования. 21 В результате макрос будет иметь вид: Sub Сумма () Range("K20").Select ActiveCell.FormulaR1C1 = "=SUM(R[-14]C[-5]:R[-1]C[-1])" Range("K21").Select End Sub Из текста макроса видно, что преобразовать данный макрос к нужной форме достаточно сложно. Для создания данного макроса надо использовать функцию СУММ(диапазон суммирования). Для определения диапазона суммирования необходимо выделить этот диапазон перед началом записи макроса, а затем в макросе определить адрес этого диапазона, его местоположение и размеры, для определения местоположения ячейки, где будет находится сумма. Макрос будет иметь вид: Sub Сумма () s = Selection.Address „определение адреса диапазона n = Selection.Row „номер ряда начальной ячейки диапазона m = Selection.Column „номер колонки начальной ячейки диапазона a = Selection.Rows.Count „количество рядов диапазоне b = Selection.Columns.Count „количество столбцов диапазоне Cells(n + a, m + b).FormulaLocal = "=СУММ(" + s + ")" End Sub В макросе используется свойство FormulaLocal, потому, что используется функция на русском языке. Еще один пример использования свойства Address. Создать макрос |