Главная страница
Навигация по странице:

  • Лабораторная работа № 2 Объекты листа Cвойство объекта Range

  • Первый способ Аргумент Cell1 задает интервал ячеек произвольного размера. ВАЖНО

  • C1:D5

  • Свойства Column и Row (R/O Integer)

  • Свойства Columns и Rows

  • Методы Select и Activate

  • Метод Clear Очищает интервал ячеек, изменяя, таким образом, свойство Value каждой ячейки интервала. Задание 1 Создать 2 макроса. 1. Макрос оформление

  • Задание 2 В некоторых командах надо указывать конкретный адрес диапазона. Для этого используется свойства Address. Создать макрос сумма

  • Практикум по vba для Microsoft Excel Учебное пособие


    Скачать 2.32 Mb.
    НазваниеПрактикум по vba для Microsoft Excel Учебное пособие
    АнкорExcel
    Дата03.10.2022
    Размер2.32 Mb.
    Формат файлаpdf
    Имя файлаShireva_VBA.pdf
    ТипПрактикум
    #710617
    страница2 из 10
    1   2   3   4   5   6   7   8   9   10
    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.
    Создать макрос
    1   2   3   4   5   6   7   8   9   10


    написать администратору сайта