Excel. Трюки и эффекты Гладкий Алексей

Создание цветной границы диапазона

Для каждого диапазона рабочего листа можно создать индивидуальную границу: например, верхняя часть границы выделяется одним цветом, а нижняя – другим. В приведенном в листинге 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 будет залит красным цветом с синим сетчатым узором.

Ввод строго ограниченных значений в указанный диапазон

В процессе эксплуатации программы иногда возникает необходимость сделать так, чтобы вводимые пользователем данные не выходили за рамки определенного интервала. В этом подразделе мы рассмотрим два трюка, которые позволяют решить эту задачу: с использованием диалогового окна и путем непосредственного ввода данных в диапазон. Применение подобных трюков позволяет контролировать корректность вводимых пользователями данных.

Ввод данных с помощью диалогового окна

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

Листинг 2.35. Настройка ввода данных в диалоговом окне

Sub DialogInputData()

Dim intMin As Integer, intMax As Integer ' Диапазон значений

Dim strInput As String ' Введенная пользователем строка

Dim strMessage As String

Dim intValue As Integer

intMin = 1 ' Минимальное значение

intMax = 50 ' Максимальное значение

strMessage = "Введите значение от " & intMin & " до " & intMax

' Ввод значения (цикл завершается, когда пользователь вводит _

значение из заданного диапазона или отменяет ввод)

Do

strInput = InputBox(strMessage)

If strInput = "" Then Exit Sub ' Отмена ввода

' Проверка, содержит ли введенная пользователем строка число

If IsNumeric(strInput) Then

intValue = CInt(strInput)

' Проверка, удовлетворяет ли значение диапазону

If intValue >= intMin And intValue <= intMax Then

' Все условия выполнены

Exit Do

End If

End If

' Формирование сообщения с текстом ошибки

strMessage = «Вы ввели некорректное значение.» & vbNewLine & _

"Введите число от " & intMin & " до " & intMax

Loop

' Внесение данных в ячейку

ActiveSheet.Range(«A1»).Value = strInput

End Sub

После написания данного кода в окне выбора макросов станет доступен макрос DialoglnputData. Для его вызова лучше создать специальную кнопку. После нажатия данной кнопки откроется диалоговое окно с предложением ввести значение от 1 до 50 (интервал значений можно изменять по своему усмотрению – для этого достаточно внести соответствующие изменения в код макроса). При попытке ввода значения, которое выходит за рамки указанного интервала, появится окно с соответствующим предупреждением и повторным предложением ввести корректное значение. Введенное значение будет помещено в ячейку А1 – это указано в строке кода ActiveSheet.Range («Al»). Value = strlnput. Если в данной строке вместо А1 указать, например, В1: Е5, то введенное значение будет помещено во все ячейки указанного интервала.

Непосредственный ввод данных

Если ввод данных с использованием диалогового окна по каким-либо причинам нецелесообразен, то можно вводить их непосредственно в диапазон. При этом программа будет контролировать вводимые данные (чтобы они не выходили за рамки указанного интервала).

Выделим на рабочем листе какой-либо диапазон (например, А1:Е10) и назовем его InputRange. Теперь в редакторе VBA в модуле рабочего листа напишем код, представленный в листинге 2.36.

Листинг 2.36. Ограничение возможных значений диапазона

Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rgInputRange As Range

Dim cell As Range

Dim strMessage As String

Dim varResult As Variant

' Диапазон, в котором контролируется ввод

Set rgInputRange = Range(«A1:E10»)

' Просмотр всех измененных ячеек и контроль ввода в тех,

которые _

принадлежат заданному диапазону

For Each cell In Target

' Проверка принадлежности диапазону

If Union(cell, rgInputRange).Address =

rgInputRange.Address Then

' Контроль правильности ввода

varResult = IsCellDataValid(cell)

If varResult = True Then

' Введено корректное значение

Exit Sub

Else

' Формирование и вывод сообщения об ошибке

strMessage = "Ячейка " & cell.Address(False, False) &

":" _

& vbCrLf & vbCrLf & varResult

MsgBox strMessage, vbCritical, «Неправильное значение»

' Очистка ввода

Application.EnableEvents = False

cell.ClearContents

cell.Activate

Application.EnableEvents = True

End If

End If

Next cell

End Sub

Function IsCellDataValid(cell As Range) As Variant

' Возвращает True, если в ячейку вводится целое число _

в диапазоне от 1 до 12. В противном случае выдается _

соответствующее сообщение

' Проверка, является ли содержимое ячейки числом

If Not WorksheetFunction.IsNumber(cell.Value) Then

IsCellDataValid = «Нечисловое значение»

Exit Function

End If

' Проверка, является ли введенное число целым

If Int(cell.Value) <> cell.Value Then

IsCellDataValid = «Введите целое число»

Exit Function

End If

' Проверка соответствия числа диапазону

If cell.Value < 1 Or cell.Value > 12 Then

IsCellDataValid = «Значение должно быть от 1 до 12»

Exit Function

End If

' В ячейку введено допустимое значение

IsCellDataValid = True

End Function

После написания данного кода в диапазон А1:Е10 можно будет вводить только целые числовые значения, попадающие в интервал от 1 до 12. При попытке ввода нечислового значения (например, текста) программа не позволит этого сделать – на экране отобразится окно с сообщением Нечисловое значение. Ввод дробного числа также будет невозможен – появится сообщение Введите целое число. Если же попытаться ввести значение, выходящее за рамки интервала от 1 до 12, то это также окажется невозможным и будет выдано сообщение Значение должно быть от 1 до 12.

Последовательный ввод данных

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

Смысл операции заключается в том, что необходимые данные будут вводиться в диалоговом окне и лишь после нажатия ОК они займут свое место в таблице. Сразу после этого в диалоговом окне можно будет вводить уже следующие данные и т. д. И все это – независимо от расположения курсора. Реализацию данной возможности рассмотрим на конкретном примере.

Предположим, что в ячейки столбца А необходимо последовательно ввести перечень дат, а в ячейки столбца В – торговую выручку, соответствующую каждой дате столбца А. Решить эту задачу можно с помощью макроса, код которого (он должен быть помещен в стандартный модуль) приведен в листинге 2.37.

Листинг 2.37. Последовательный ввод данных

Sub StreamInput()

Dim strDate As String

Dim strSum As String

Dim lngRow As Long

' Ввод данных в цикле (повторяется до тех пор, пока пользователь _

не введет пустую строку или не нажмет «Отмена» в окне ввода)

Do

lngRow = Range(«A65536»).End(xlUp).Row + 1

' Ввод даты

strDate = InputBox(«Вводим дату»)

If strDate = "" Then Exit Sub

' Ввод выручки

strSum = InputBox(«Вводим выручку»)

If strSum = "" Then Exit Sub

' Запись данных в ячейки

Cells(lngRow, 1) = strDate

Cells(lngRow, 2) = strSum

Loop

End Sub

После написания кода макрос Streamlnput будет доступен в окне выбора макросов. Для удобства поместите в любое удобное место интерфейса кнопку и привяжите к ней данный макрос – и можно приступать к последовательному вводу данных.

Введем в ячейки А1 и В1 названия соответствующих столбцов таблицы (например, Дата и Выручка) и нажмем кнопку вызова макроса. В результате откроется диалоговое окно, в котором с клавиатуры сначала вводится дата (в поле Вводим дату), а после нажатия кнопки ОК – сумма выручки (в поле Вводим выручку). После еще одного нажатия кнопки ОК введенные данные отобразятся в ячейках А2 и В2 соответственно, а в диалоговом окне можно вводить следующие данные (которые, в свою очередь, будут помещены в ячейки A3 и ВЗ) и т. д. Для выхода из цикла следует нажать в диалоговом окне кнопку Cancel

Быстрое выделение ячеек с отрицательными значениями

Страницы: «« « ... 678910111213 » ... »»