Первый способ
Предположим, что нам необходимо заполнить какой-нибудь диапазон последовательностью чисел от 1 до 100. Для решения этой задачи удобно воспользоваться макросом, код которого приведен в листинге 2.22 (этот код следует набрать в стандартном модуле редактора VBA).
Листинг 2.22. Быстрое заполнение диапазона
Sub FillCells()
Dim intStartVal As Integer ' Начальное значение
Dim intStep As Integer ' Шаг при изменении значения
Dim intEndVal As Integer ' Конечное значение
Dim intVal As Integer ' Текущее значение
Dim intCellOffset As Integer ' Смещение от начальной ячейки
' Установка параметров заполнения
intStartVal = 1
intStep = 1
intEndVal = 100
' Заполнение ячеек текущего столбца значениями от 1 до 100
For intVal = intStartVal To intEndVal Step intStep
ActiveCell.Offset(intCellOffset, 0).Value = intVal
intCellOffset = intCellOffset + 1
Next intVal
End Sub
Сразу после выполнения макроса диапазон, расположенный ниже текущей ячейки (в том же столбце), будет заполнен числами от 1 до 100 (заполнение начнется с активной ячейки).
Приведенный выше код можно изменять по своему усмотрению и таким образом корректировать длину последовательности, шаг и иные параметры. Например, можно применить такой код (листинг 2.23).
Листинг 2.23. Заполнение через интервал
Sub FillCells()
Dim intStartVal As Integer ' Начальное значение
Dim intStep As Integer ' Шаг при изменении значения
Dim intEndVal As Integer ' Конечное значение
Dim intVal As Integer ' Текущее значение
Dim intCellOffset As Integer ' Смещение от начальной ячейки
Dim intCellStep As Integer ' Шаг при перемещении между _
заполняемыми ячейками
' Установка параметров заполнения
intStartVal = 3
intStep = 3
intEndVal = 30
intCellStep = 3
' Заполнение ячеек текущего столбца значениями от 3 до 30
For intVal = intStartVal To intEndVal Step intStep
ActiveCell.Offset(intCellOffset, 0).Value = intVal
intCellOffset = intCellOffset + intCellStep
Next intVal
End Sub
После выполнения данного макроса последовательность будет заполнена числами с 3 до 30, причем числа будут расположены в каждой третьей ячейке следующим образом: 3 (активная ячейка), 6, 9, 12…., 27, 30.
Второй способ
Допустим, что нам нужно быстро заполнить диапазон, состоящий из 10 ячеек в высоту и 5 ячеек в ширину, последовательностью чисел, расположенных по порядку от 1 до 50. Для решения данной задачи напишем в стандартном модуле редактора VBA представленный ниже код (листинг 2.24).
Листинг 2.24. Заполнение указанного диапазона
Sub FillCellRect()
Dim lngRows As Long, intCols As Integer ' Количество ячеек по _
горизонтали и вертикали
Dim lngRows As Long, intCol As Integer ' Координаты текущей
ячейки
Dim lngStep As Long, lngVal As Long
' Установка начального значения и шага заполнения
lngVal = 1
lngStep = 1
' Ввод количества ячеек по горизонтали и вертикали, которое _
необходимо заполнить
lngRows = Val(InputBox("Количество ячеек в высоту"))
intCols = Val(InputBox("Количество ячеек в ширину"))
' Отключение обновления экрана
Application.ScreenUpdating = False
' Заполнение ячеек значениями
For lngRow = 0 To lngRows – 1
For intCol = 0 To intCols – 1
ActiveCell.Offset(lngRow, intCol).Value = lngVal
lngVal = lngVal + lngStep
Next intCol
Next lngRow
' Включение обновления экрана
Application.ScreenUpdating = True
End Sub
В результате написания кода будет создан макрос FillCellRect. После его запуска откроется окно, в котором с клавиатуры нужно ввести количество ячеек в высоту (в нашем примере нужно указать значение 10). После нажатия в данном окне кнопки О К откроется аналогичное окно, в котором точно так же нужно ввести количество ячеек в ширину (в нашем примере нужно задать 5) и нажать ОК. После этого в соответствии с выполненными настройками будет автоматически заполнен диапазон, начиная с ячейки, которая была выделена (данная ячейка будет являться левой верхней в заполненном диапазоне). Изменив соответствующим образом код макроса, можно корректировать шаг последовательности, ее первое число (в строке IngVal = 1, которая входит в состав приведенного выше кода, указано, что первое число последовательности – 1) и другие параметры.
Третий способ
Данный способ заполнения диапазона также предусматривает написание макроса. Его преимущество состоит в том, что он быстрее способа, о котором рассказано в предыдущем примере. Код макроса (он должен быть написан в стандартном модуле редактора VBA) выглядит следующим образом (листинг 2.25).
Листинг 2.25. Заполнение диапазона
Sub FillCellRect1()
Dim lngRows As Long, intCols As Integer
Dim lngRow As Long, intCol As Integer
Dim lngStep As Long, lngVal As Long
Dim alngValues() As Long
Dim rgRange As Range
' Установка начального значения и шага заполнения
lngVal = 1
lngStep = 1
' Ввод количества ячеек по горизонтали и вертикали, которое _
необходимо заполнить
lngRows = Val(InputBox("Количество ячеек в высоту"))
intCols = Val(InputBox("Количество ячеек в ширину"))
ReDim alngValues(1 To lngRows, 1 To intCols)
Set rgRange = ActiveCell.Range(Cells(1, 1), _
Cells(lngRows, intCols))
' Заполнение массива alngValues значениями
For lngRow = 1 To lngRows
For intCol = 1 To intCols
alngValues(lngRow, intCol) = lngVal
lngVal = lngVal + lngStep
Next intCol
Next lngRow
' Перенос значений из массива в таблицу
rgRange.Value = alngValues
End Sub
Порядок заполнения диапазона такой же, как и в предыдущем примере, – после запуска макроса нужно последовательно указать количество ячеек в высоту и ширину. Результатом работы макроса будет заполненный диапазон (начиная с активной ячейки). Так же, как и в предыдущем примере, для изменения параметров заполнения диапазона (начальное число последовательности, шаг и др.) можно внести соответствующие изменения в код макроса.
Более высокая скорость работы данного алгоритма достигается благодаря тому, что сначала формируется двухмерный массив со значениями. Этот массив целиком передается объекту Range. Тем самым мы избегаем множества обращений к таблице, заменяя их одним, но эффективным.
Гиперссылки – в виде обычного текста
В Microsoft Excel, начиная с версии 2000, осуществляется автоматическая замена текста гиперссылками, если он содержит следующие наборы символов:
• http://;
• www.;
• ftp://;
• mailto:;
• file://;
• news:;
• mail@pochta.
Однако такая автозамена не всегда удобна. Чтобы ее отменить, достаточно в качестве первого символа, вводимого в ячейку, использовать апостроф (). После нажатия Enter этот символ исчезнет и останется только введенный текст, причем он не будет преобразован в гиперссылку. На печать апостроф также не выводится.
Помещение в ячейку электронных часов
В любую ячейку рабочего листа при необходимости можно поместить электронные часы. В листинге 2.26 приведен код макроса, который позволяет решить эту задачу.
Листинг 2.26. Размещение в ячейке электронных часов
Sub UpdateTime()
Dim varNextCall As Variant
' Записываем в ячейку текущее время
Cells(1, 1).Value = Now
' Записываем в varNextCall время, когда вызвать этот макрос _
в следующий раз (через 1 секунду)
varNextCall = TimeSerial(Hour(Now), Minute(Now), Second(Now)
+ 1)
' Уведомляем Excel в необходимости вызова макроса
Application.OnTime varNextCall, "UpdateTime"
End Sub
После выполнения данного макроса электронные часы будут помещены в ячейку А1. Если в эту ячейку поместить курсор, то часы отобразятся также и в строке формул.
"Будильник"
Помимо электронных часов, можно настроить "будильник". Смысл данного трюка заключается в том, что в определенное время на экране отобразится окно с указанным ранее информационным сообщением (а при наличии колонок и иных сопутствующих устройств – вместе со звуковым сигналом). В листинге 2.27 приведен код макроса, который позволяет решить эту задачу.
Листинг 2.27. "Будильник"
Sub Clock()
' Уведомляем Excel, что процедуру Alarm нужно вызвать в 20:55
Application.OnTime TimeValue("20:55:00"), "Alarm"
End Sub
Sub Alarm()
MsgBox "Пора ужинать!!!"
End Sub
После выполнения макроса Clock на экране в назначенное время (20 часов 55 минут) появится окно с сообщением Пора ужинать!!!. Следует учитывать, что для каждого последующего появления в назначенное время такого окна необходимо отдельно запускать макрос.
Поиск данных в диапазоне
Используя средства языка VBA, можно по-разному искать требуемые данные в указанном диапазоне. Рассмотрим несколько популярных способов поиска данных.
Поиск в диапазоне значения по шаблону
Предположим, возникла необходимость просмотреть большой отчет и заменить во всех его местах слово Доход словом Выручка, выделив при этом цветом ячейки, в которых выполнялась такая замена. Решить эту задачу нам поможет следующий макрос (листинг 2.28).
Листинг 2.28. Поиск и замена по шаблону
Sub ReplaceCellsData()
Dim cell As Range
' Просмотр всех ячеек диапазона G1:K20 и замена искомого
текста
For Each cell In [G1:K20]
If cell.Value Like "*Доход*" Then
cell.Value = "Выручка"
cell.Interior.Color = RGB(255, 255, 0)
Else
cell.Interior.Color = RGB(255, 255, 255)
End If
Next
End Sub
После выполнения данного макроса слово Доход, встречающееся в диапазоне G1:K20, заменяется словом Выручка, а соответствующие ячейки выделяются желтым цветом. Вся остальная часть диапазона заливается белым цветом (при этом содержимое всех ячеек по-прежнему отображается, а сетка исчезает).
Поиск значения с выводом результата
Можно настроить параметры поиска требуемого значения таким образом, что его результат будет выведен в отдельном информационном окне. Вот пример макроса (листинг 2.29), который позволяет решить эту задачу.
Листинг 2.29. Поиск значения с отображением результата в отдельном окне
Sub Search()
Dim rgResult As Range
' Поиск заданного значения в диапазоне B1:B20 и вывод результата
Set rgResult = Range("B1:B20").Find(9999, , xlValues)
If rgResult Is Nothing Then
MsgBox "Поиск не дал результатов"
Else
MsgBox rgResult.Address
End If
End Sub
С помощью данного макроса обрабатывается диапазон В1:В20, в котором ведется поиск значения 9 99 9. При обнаружении данного значения появляется окно с указанием адреса соответствующей ячейки. Если же указанное значение не обнаружено, то в данном окне отображается сообщение Поиск не дал результатов.
Поиск с выделением найденных данных
Достаточно удобным является вид поиска, в котором используется выделение найденных ячеек. Применение данного способа весьма целесообразно при работе с большими объемами данных. Для настройки поиска можно использовать, например, такой макрос (листинг 2.30).
Листинг 2.30. Выделение найденных данных
Sub FindAndSelect()
Dim strStartAddr As String ' Хранит координаты первого найденного _ значения
Dim rgResult As Range
' Поиск первого входжения искомого слова
Set rgResult = Range("B1:B10").Find("Прибыль", , xlValues)
If Not rgResult Is Nothing Then
' Сохраним адрес найденной ячейки (чтобы контролировать _
зацикливание поиска)
strStartAddr = rgResult.Address
End If
Do While Not rgResult Is Nothing
' Обработка результата поиска
rgResult.Interior.Color = RGB(255, 255, 0)
' Новый поиск
Set rgResult = Range("B1:B10").FindNext(rgResult)
If rgResult.Address = strStartAddr Then
' Поиск завершен
Exit Do
End If
Loop
End Sub
Этот макрос обрабатывает диапазон В1:В10 и ищет в нем значение Прибыль. Все ячейки, в которых обнаружено данное значение, будут выделены желтым цветом (строка rgResult. Interior. Color = RGB (255, 255, 0)). С помощью подобных макросов можно обрабатывать любые диапазоны и находить в них любые значения.
Создание цветной границы диапазона
Для каждого диапазона рабочего листа можно создать индивидуальную границу: например, верхняя часть границы выделяется одним цветом, а нижняя – другим. В приведенном в листинге 2.31 макросе верхняя граница диапазона будет начертана толстой синей линией, а нижняя – розовой пунктирной обычной толщины.
Листинг 2.31. Оформление верхней и нижней границ диапазона
Sub RangeBorder()
Dim rgRange As Range
Set rgRange = Range("B2:D5")
' Оформление верхней границы диапазона
With rgRange.Borders(xlEdgeTop)
.Weight = xlThick
.LineStyle = xlContinuous
.Color = RGB(0, 0, 255)
End With
' Оформление нижней границы диапазона
With rgRange.Borders(xlEdgeBottom)
.Weight = xlMedium
.LineStyle = xlDash
.Color = RGB(255, 0, 255)
End With
End Sub
Очевидно, что цвета и толщину линии можно изменять по своему усмотрению.
Автоматическое определение адреса ячейки
Можно настроить программу таким образом, что при щелчке кнопкой мыши на любом месте рабочего листа на экране будет появляться окно с информацией об адресе текущей ячейки, причем в разных форматах. Код макроса, который позволяет решить эту задачу, приведен в листинге 2.32 (следует учитывать, что данный код должен быть помещен в модуль рабочего листа).
Листинг 2.32. Информация об адресе активной ячейки
Sub Worksheet_SelectionChange(ByVal Target As Range)
' Вывод адреса ячейки в различных форматах
MsgBox Target.Address() & vbCr & _
Target.Address(RowAbsolute:=False) & vbCr & _
Target.Address(ReferenceStyle:=xlR1C1) & vbCr & _
Target.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, ColumnAbsolute:=False, _
RelativeTo:=Worksheets(1).Cells(2, 2))
End Sub
Теперь при щелчке кнопкой мыши на ячейке, например, Е9 на экране отобразится окно со следующим сообщением:
$E$9
$E9
R9C5
R[7]C[3]
Адрес ячейки представлен в нескольких форматах, в том числе и относительно другой ячейки. В качестве ячейки, относительно которой определяется адресация, в данном случае используется ячейка В2 (см. в коде значение параметра RelativeTo).
Автоматизация добавления примечаний в указанном диапазоне
Трюк, который мы сейчас рассмотрим, позволяет быстро вставить примечание в ячейки определенного диапазона, соответствующие указанным требованиям.
Для достижения такого эффекта можно воспользоваться макросом, код которого приведен в листинге 2.33.
Листинг 2.33. Добавление примечаний в диапазон
Sub CreateComments()
Dim cell As Range
' Производим поиск по всем ячейкам диапазона и добавляем
примечания _
ко всем ячейкам, содержащим слово "Выручка"
For Each cell In Range("B1:B100")
If cell.Value Like "*Выручка*" Then
cell.ClearComments
cell.AddComment "Неучтенная наличка"
End If
Next
End Sub
С помощью данного макроса обрабатывается диапазон В1:В100. После применения макроса ко всем ячейкам, содержащим текст Выручка, будет добавлено примечание Неучтенная наличка. При этом в примечании будет отсутствовать имя пользователя, его создавшего. Если в указанных ячейках содержалось другое примечание, то оно будет удалено и заменено тем, которое указано в макросе. Очевидно, что с помощью подобных макросов можно создавать произвольные примечания к любым ячейкам указанного диапазона.
Заливка диапазона
Выполнив несложный трюк, можно быстро залить любой диапазон ячеек требуемым цветом (или комбинацией разных цветов). Для этого следует использовать, например, такой макрос (листинг 2.34).
Листинг 2.34. Создание заливки диапазона
Sub FillRange()
' Заливка диапазона
With Range("B1:E10")
' Задаем узор – сетчатый
.Interior.Pattern = xlPatternChecker
' Цвет узора – синий
.Interior.PatternColor = RGB(0, 0, 255)
' Цвет ячейки – красный
.Interior.Color = RGB(255, 0, 0)
End With
End Sub
В результате применения данного макроса диапазон В1:Е10 будет залит красным цветом с синим сетчатым узором.
Ввод строго ограниченных значений в указанный диапазон
В процессе эксплуатации программы иногда возникает необходимость сделать так, чтобы вводимые пользователем данные не выходили за рамки определенного интервала. В этом подразделе мы рассмотрим два трюка, которые позволяют решить эту задачу: с использованием диалогового окна и путем непосредственного ввода данных в диапазон. Применение подобных трюков позволяет контролировать корректность вводимых пользователями данных.