Методы объекта Range

Объект Range имеет около 80 различных методов. Есть ли пересечение с методами объекта Worksheet? Есть, но оно незначительно. Общих методов примерно 10%. К таким методам относятся методы общего назначения: Activate, Calculate, CheckSpelling, Copy, Delete, PasteSpecial, PrintOut, Select. Замечу, что объект Range имеет общие методы не только с объектом Worksheet, но и со старшим в иерархии объектом Workbook. Так описанный ранее метод Run, позволяющий запускать макросы, есть и у объекта Range. Более 20 методов общего назначения входят в следующие группы, которые я лишь назову, не приводя подробного описания:

• Clear – 7 методов этой группы проводят разнообразную чистку содержания, комментариев и прочих деталей в области объекта Range.

• Copy, Fill – 9 методов этих близких групп выполняют копирование объекта Range и заполнения некоторого диапазона результатами копирования.

• Find - 3 метода позволяют проводить разнообразный поиск в области объекта Range.

• Show - 4 метода этой группы предназначены для отображения данных на экране дисплея.

• Sort - 2 метода выполняют сортировку данных в области объекта Range.

Остается еще более 40 методов, которые я не стану сейчас описывать, надеясь, что большинство из них, по крайней мере, самые важные в работе программиста появятся при рассмотрении большого числа задач в последующих главах. В качестве примере дам краткую характеристику лишь трех из них:

• Sub AutoFill(Destination As Range, [Type As XlAutoFillType = xlFillDefault]) — метод из группы Fill позволяет заполнить диапазон, заданный параметром Destination, используя значения объекта Range и тип заполнения, определенный параметром Type. Диапазон назначения Destination должен включать в себя исходный объект Range, вызвавший метод.

• Sub AutoFormat([Format As XlRangeAutoFormat = xlRangeAutoFormatClassic1], [Number], [Font], [Alignment], [Border], [Pattern], [Width]) — метод из группы методов форматирования включает автоматическое форматирование диапазона. Тип форматирования определяет первый параметр, остальные параметры имеют булевы и позволяют включать или выключать те или иные возможности форматирования. По умолчанию они включены. Если исходный диапазон представляет одну ячейку, то форматируется весь текущий регион, содержащий эту ячейку.

• Sub DataSeries([Rowcol], [Type As XlDataSeriesType = xlDataSeriesLinear], [Date As XlDataSeriesDate = xlDay], [Step], [Stop], [Trend]) — метод из группы методов, предназначенных для работы с данными. Позволяет создать автоматически ряд данных в указанном диапазоне, удовлетворяющий определенному закону построения. Первый параметр имеет два возможных значения: xlRows и xlColumns, указывающих, как будут заполняться данные — по строкам или столбцам. Второй параметр задает тип заполнения ряда данных. Остальные параметры позволяют управлять процессом заполнения данных. Вот небольшой пример на применение этого метода:

Public Sub WorkWD() 'Работа с методом DataSeries Dim Sh As Worksheet Dim myr As Range Set Sh = ThisWorkbook.Worksheets(1) Set myr = Sh.Range("C21:C32") myr.Cells(1, 1) = "31-Jan-2001" myr.DataSeries Type:=xlChronological, Date:=xlMonth Set myr = Sh.Range("D21:D32") myr.Cells(1, 1) = 320 myr.DataSeries Type:=xlDataSeriesLinear, Step:=320 End Sub

Взгляните на два ряда данных, полученных в результате выполнения данной процедуры:

Рис. 22 Автоматическое построение рядов данных

Пример создания базы данных с контролем вводимой информации.

Процедуры работают с именованной областью База, которую создают вначале работы и расширяют по мере изменения списка.

Для ввода и редактирования используется одно и то же диалоговое окно, заголовок которого меняется по признаку глобальной логической переменной red, принимающей значение False, если вводим новые данные или True, когда редактируем имеющиеся.

Для редактирования списка можно установить курсор в строку редактирования или указать диапазон или ячейку, по адресу которой будет найдена строка списка (используется элемент управления формы RefEdit1.Text = Selection.Address).

Для копирования информации можно выделить требуемый диапазон или указать область, используя аналогичный элемент управления.

В се процедуры запускаются по кнопкам, добавленным в созданную пользователем панель инструментов.

Эта программа задает начало диапазона для формирования списка База

'==================================================

Public Sub Creatnamerange()

Worksheets(“Список”).Select

Names.Add Name:=“База”, RefersToR1C1:="=Список!" & "r1c1" & ":" & "r2c3"

Range(“База”).Select

End Sub

‘Диапазон с именем База после добавления записи (строки) в список расширяется.

Диалоговое окно UserForm1 обеспечивает ввод (red=false) и редактирование (red=true) данных.

Public red As Boolean ‘ признак ввода / редактирования данных

Обработчик нажатия кнопки CommandButton1 контролирует дублирование информации и расширяет именованную область База.

Этапы разработки

• Создать заголовки списка (фамилия, длжность, телефон) на листе с именем Список

• В модуле проекта VBA описать переменные и ввести процедуры Public red As Boolean ‘ признак ввода / редактирования данных Public Sub Creatnamerange() Worksheets(“Список”).Select

Names.Add Name:=“База”, RefersToR1C1:="=Список!" & "r1c1" & ":" & "r2c3"

Range(“База”).Select

End Sub Public Sub MoveToBaza() UserForm1.Caption = “Добавление элементов списка” red = False UserForm1.Show End Sub

• Разработать форму для ввода / редактирования данных и ввести код обработчика события нажатия кнопки и процедуру контроля вводимой информации на повтор (см. след. слайд).

Private Sub CommandButton1_Click()

‘вносим данные из формы в лист

Dim i As Integer

Dim dbnewrow As Integer

Dim newrange As String

With Range(“База")

If red = True Then

' редактирование данных

dbnewrow = ActiveCell.Row

Else

‘переход на новую строку

dbnewrow = .Rows.Count + .Row

End If

' проверка на дублирование

If dublir Then Exit Sub

‘ ввод данных из формы в ячейки новой строки

For i = 1 To .Columns.Count

Cells(dbnewrow, (i + .Column) - 1).Value = _

UserForm1.Controls("txt" & CStr(i)).Text

Next i

If red = False Then

‘ расширяем диапазон База

newrange = "=" & .Parent.Name & "!"

newrange = newrange & Cells(.Row, .Column).Address & ":"

newrange = newrange & _

Cells(dbnewrow, (.Column + .Columns.Count) - 1).Address

Names(“База").RefersTo = newrange

End If

End With

End Sub

' проверка на повтор вводимой информации

Public Function dublir() As Boolean

dublir = False

With Range(“База”)

For i = 1 To .Rows.Count

If (Cells(i, 1).Value = _ UserForm1.Controls("txt1").Text _ And Cells(i, 2).Value = _ UserForm1.Controls("txt2").Text _ And Cells(i, 3).Value = _ CStr(UserForm1.Controls("txt3").Text)) Then

MsgBox (“Повтор информации при вводе")

dublir = True

Exit For

End If

Next i

End With

End Function

Разработать форму UserForm2 для выделения диапазона или ячейки с элементом управления RefEdit1. Эта форма может использоваться и для копирования данных на другой лист.

Sub CommandButton1_Click()

Me.Hide

Me.Tag = vbOK

End Sub

В модуле создать процедуру editdata() для редактирования данных списка.

Public Sub editdata()

Dim i As Integer

Dim curr As Integer

Dim lb As Integer

Dim kol As Integer

' выбор элемента

With UserForm2

.Caption = “Выбор строки для редактирования"

.RefEdit1.Text = Selection.Address

.Show

If .Tag = vbOK Then

Range(UserForm2.RefEdit1.Text).Select

End If

End With

‘ текущая строка

curr = ActiveCell.Row

‘ получаем количество столбцов в базе

kol = Range("База").Columns.Count

‘ получаем самый левый столбец

lb = Range("База").Column

‘загружаем данные записи в окно UserForm1

For i = 1 To kol

UserForm1.Controls("txt" & CStr(i)).Text = _

Cells(curr, (i + lb) - 1).Value

Next i

UserForm1.Caption = “Редактирование элемента списка"

red = True

UserForm1.Show

End Sub

В модуле создать процедуру для копирования выбранного диапазона на другой лист, например Public Sub copyDiapazon()

52