АРХИВ СТАТЕЙ ЖУРНАЛА «МОЙ КОМПЬЮТЕР» ЗА 2002 ГОД

Меню для проверки

Все журналы \ Номер 23/194 `2002 от 03.06.2002 \ Меню для проверки

Геннадий ТИХОМИРОВ telewons@ambernet.kiev.ua

Зачем писать приложение «с нуля»? Ведь можно воспользоваться пакетом MS Office! Но всегда ли такое приложение полностью может удовлетворить пользователя? В данной статье предлагается рассмотреть возможности расширения стандартных контекстных меню и средств проверки вводимых данных в Excel.

В своих статьях мной неоднократно затрагивались проблемы создания панелей инструментов в MS Office, в частности в Word’е. Но если в последнем заполнение списков панелей инструментов и пользовательских форм возможно путем применения метода AddItem, обращающегося либо к информации, содержащейся в тексте программы, либо, например, к текстовому файлу, выполняющему роль базы данных, то для Excel решение во многом упрощается, т. к. электронные таблицы одновременно могут выполнять роль базы данных, пусть бы и плоской. Использование же таких мощных средств программы, как автофильтр, сводные таблицы или расчет промежуточных итогов, делают Excel’евские документы великолепными инструментами анализа данных.

Для заполнения списков ListBox и ComboBox пользовательских форм данными из таблиц совсем необязательно использование метода AddItem — достаточно на этапе разработки формы указать параметры RowSource /строки-ресурсы/ и ColumnCount /количество колонок/ для контрола (см. Рис. 1), или же прописать эти же значения в процедуре, например:

Где слово 'Значения' — имя листа рабочей книги, в которой находится диапазон данных a2:e45.

Использование средства «Проверка данных» (меню Данные > Проверка) позволяет задать для выделенного диапазона, в данном случае А2:А10, набор контрольных значений, диапазон которых, выделенный пользователем на рабочем листе, заносится в поле «Источник» (см. Рис. 2).

В конечном итоге, при активизации любой ячейки в диапазоне А2:А10 у правой ее границы появится кнопка выпадающего списка, содержащего значения диапазона-источника (см. Рис. 3).

Обратите внимание, контрольные значения (источник) находятся на том же рабочем листе, что и область заполнения. Они здесь явно ни к чему. Лучше перенести контрольные значения на другой лист рабочей книги Excel, а удаление ненужных значений производить, выделяя строку кликом по ее номеру (см. Рис. 4) и выбирая из контекстного меню «Удалить», причем удаление в этом случае не будет сопровождаться выбросом диалогового окна «Удаление ячеек».

Заметим, что при обработке данных (например, при фильтрации или выборке по запросу Microsoft Query) значения «Васильев» и «Васильєв», или «Сидоров», набранный полностью кириллицей, и тот же «Сидоров», имеющий заглавную «С» латинскую, будут восприниматься по-разному, а следовательно, неминуемо возникнут итоговые ошибки. Чтобы этого не произошло, можно в поле имени, слева от строки формул, задать имя диапазону контрольных значений, независимо от того, на каком листе данный диапазон находится (выделите ячейку или диапазон, введите имя в этом поле и затем нажмите клавишу ENTER), а в поле «Источник» прописать формулу = ИмяДиапазона. Но в процессе работы список контрольных значений может увеличиваться, а задавать для запаса пустые ячейки — значит позволить пользователю в формируемую базу заносить… пустоту.

Для начала переименуем листы в рабочей книге на «Значения» и «Партнеры», а контрольные значения перенесем в соответствующий лист, в ячейку А1. В дальнейшем лист «Значения» можно скрыть, т. к. обращаться к нему будут не так часто (Формат > Лист > Скрыть).

Можно создать панель инструментов или пользовательскую форму с контрольными данными, получаемыми из другого листа, но возможности, предоставляемые контекстными меню, позволяют уменьшить время редактирования документов. Поэтому предлагаю поместить в контекстное меню список значений для занесения в активную ячейку. Но если Word еще как-то позволяет редактировать контекстные меню вручную в режиме «Настройка» (см. Рис. 5), то в Excel редактировать контекстного меню можно только процедурно.

Помещенная в стандартный модуль процедура создания контекстного меню может быть следующей:

Sub KontextMnu()
' Определяем контрол — объектный тип CommandBarComboBox
Dim myControl As CommandBarComboBox
' Объявление переменной для счетчика цикла заполнения списка
Dim n As Long
n = 0
' Делаем контекстное меню видимым
    Application.CommandBars("cell").Enabled = True
' Метод Reset — переустанавливаем или перезагружаем (кому как будет удобней перевести) меню, в результате чего оно будет содержать набор стандартных команд, предусмотренных Excel. Обратите внимание, что CommandBar, над которой производятся операции, имеет специфическое имя — "cell", т. е. "ячейка"
    Application.CommandBars("cell").Reset
' В контекстное меню добавляем контрол, т. е. кнопку, предназначение которой — вносить текущую дату в активизируемую ячейку; впрочем, вы можете создать кнопку с любым нужным вам свойством — "OnAction"
      With Application.CommandBars("cell").Controls
       With .Add(Type:=msoControlButton, before:=1, temporary:=True)
        .Caption = "Дата"
        .OnAction = "ToDay"
        .FaceId = 125
        .BeginGroup = True
       End With
'Набор инструкций для создания нового объекта - раскрывающегося списка
Set myControl = Application.CommandBars("cell").Controls.Add(Type:=msoControlComboBox, before:=2, temporary:=True)
       With myControl
'Цикл заполнения раскрывающегося списка — выполняется до тех пор, пока ячейка в списке контрольных значений на соответствующем листе не будет пуста. Функция Trim удаляет начальные и хвостовые символы пробелов 
Do
      n = n + 1
      If Sheets("Значения").Cells(n, 1).Offset(1, 0).Value = "" Then Exit Do
      .AddItem Trim(Sheets("Значения").Cells(n, 1).Offset(1, 0).Value)
  Loop
' Определение параметров раскрывающегося списка — его ширины и выполняемой им команды
    .DropDownWidth = 100
    .OnAction = "ValueToCell"
    End With
   End With
End Sub
'Определяем командные процедуры для только что созданных контролов
Sub ValueToCell()
ActiveCell.Value = Application.CommandBars("cell").Controls(2).Text
End Sub
Sub ToDay()
ActiveCell.Value = Format(Date, "dd.mm.yyyy")
End Sub

Теперь почти все готово. Единственное, в чем необходимо определиться, — когда осуществлять заполнение меню новыми контролами. Это имеет смысл в следующих случаях:

• в момент открытия рабочей книги;

• активизации листа «Партнеры».

Помещаем (см. Рис. 6) в объекты ThisWorkbook и Лист1(Партнеры) вызов процедуры KontextMnu.

Что произойдет? При открытии книги контекстное меню получит новые объекты, но в случае редактирования контрольных значений придется все равно осуществлять перезагрузку контекстного меню. Поэтому при переходе с листа «Значения» на лист «Партнеры» последний будет активизирован, и раскрывающийся список обновится (см. Рис. 7).

В принципе, можно остановиться и на этом. Но в интересах эргономики контекстное меню листа «Значения» должно иметь стандартный вид. Для этого в объекте Лист2(Значения) прописываем процедуру перезагрузки контекстного меню:

а в Лист1(Партнеры) добавляем процедуру реакции на райт-клик мыши:

Далее предлагаю процедуру проверки уже введенных данных, в которой может возникнуть необходимость после корректировки контрольных значений или при наличии ошибок во время ввода данных в колонку А:А, допустим, с клавиатуры. «Прицепить» ее можно, например, к кнопке вашей панели инструментов (см. Рис. 8), созданной как вручную, так и программно. Но все же рекомендую панели инструментов создавать последним способом — информация о кнопках, созданных вручную, сохраняется в системных файлах Excel, а не в рабочих книгах, и, следовательно, кнопка будет потеряна при переносе книги на другой ПК. Плюс еще одно неудобство — панель, созданная для конкретного файла Excel, будет постоянно появляться при работе с другими книгами. Панель же, создаваемую из кода, можно запрограммировать на появление и скрытие при работе с конкретными диапазонами данных.

Sub Check_MyList()
'Переменные, используемые в циклах проверки, назначение которых проясняется при детальном рассмотрении этих самых циклов
Dim Count As Integer
Dim x As Long
Dim y As Long
' Переменная для статусбара — пользователь должен ощущать работу приложения, а не думать, что оно у него "зависло"
Dim Percents As Byte
' Переходим на лист "Партнеры"
Sheets("Партнеры").Select
' Статусбар делаем видимым
Application.DisplayStatusBar = True
' Свойство Enabled раскрывающегося списка устанавливаем в True, так как данные в листе будут сравниваться с данными раскрывающегося списка 
CommandBars("cell").Controls(2).Enabled = True
' Снимаем возможность обновления экрана с единственной целью — повысить скорость обработки (зачем тратить попусту время и ресурсы?)
Application.ScreenUpdating = False
' Определяем номер последней строки, заполненной данными
Range("A1").End(xlDown).Select
 y = ActiveCell.Row
' Цикл проверки со второй строки до последней в списке
For x = 2 To y
With CommandBars("cell").Controls(2)
' Вложенный цикл сравнения значения в ячейке с каждым значением списка. Если значения совпадут, устанавливаем цвет проверяемой ячейки в xlNone(т. е. нет заливки) и выходим из цикла; в противном случае ячейка с ошибочными данными будет красной (не нравится красный (3) — покрась в синий (5) :-) )
For Count = 1 To .ListCount
  If Trim(Cells(x, 1).Value) = .List(Count) Then
      Cells(x, 1).Interior.ColorIndex = xlNone
        Exit For
       Else: Cells(x, 1).Interior.ColorIndex = 3
       End If
 Next
  End With
'Вычисляем примерный процент уже проверенных данных (большая точность вряд ли понадобится)
Percents = (x / y) * 100
'Индикация процентов (см. Рис. 8)
Application.StatusBar = "Проверено " & Percents & "%"
  Next
'Сигнал пользователю об окончании проверки
  Beep
'В результате приведения статусбара в состояние False появится сообщение "Готово" или, для нелокализованной версии, "Ready"
Application.StatusBar = False
' Обновляем экран
  Application.ScreenUpdating = True
End Sub

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

И напоследок, совет для пользователей Excel 2000 — если вас раздражает нагромождение кнопок в каждом экземпляре создаваемых новых рабочих книг, снимите флажок в окне «Параметры» (Options) (см. Рис. 9) или пропишите простенькую процедуру:

На сегодня все, желаю удачи.

Рекомендуем ещё прочитать:

Мышление в стиле Visual Basic. (№3/174 `2002) Андрей Гончаров
Мышление в стиле Visual Basic. (№6/177 `2002) Андрей Гончаров
Я твой слуга, я твой работник. (№11/182 `2002) Дмитрий Сахань
Открытый ключ к закрытой информации. (№12/183 `2002) Роман Бречко
Уроки ASP-технологии. (№12/183 `2002) Нет Автора
Выживет сильнейший. (№15/186 `2002) Нет Автора
OpenGL и Delphi - 2. (№36/207 `2002) Руслан Ризванов
Говорим на Flash Action Script. (№49/220 `2002) Кирилл Коваленко

Предыдущая статья Назад К списку журналов Следующая статья