Vba excel счетчик элемент управления

Обновлено: 07.01.2025

Выше мы говорили лишь о кнопке и надписи. Но VBA позволяет работать с огромнейшим количеством элементов управления.

На панели инструментов Toolbox в редакторе VBA отображается лишь их малая часть. Для того чтобы просмотреть установленные в системе элементы и вывести кнопки для их вызова на панель инструментов , щелкните правой кнопкой мыши по панели и в появившемся меню выберите пункт Additional Control (Дополнительные элементы управления ).

Даже если вы специально не устанавливали пакеты элементов – вы увидите большой список (рис. 4.1.). Мы нашли в этом окне Календарь 12.0. Выделим его (для этого надо включить флажок напротив названия) и нажмем OK — на панели инструментов VBA появится новый элемент управления.

Рис. 4.1. Просмотр дополнительных элементов управления

Теперь рассмотрим наиболее часто используемые элементы управления и особенности их применения.

4.2. Form

Вы уже знакомы с формами. Они содержат в себе другие элементы управления .

Среди событий форм можно отметить следующие.

Initialize ( Инициализация ) — возникает перед появлением формы. На этом этапе форма готовится к открытию и в обработчик этого события обычно включают операции для настройки элементов управления, открытия внешних файлов.

Error (Ошибка) — возникает при появлении ошибки в форме.

Terminate (Завершение) — возникает при обычном завершении работы формы. В обработчик этого события можно добавить команды записи протоколов работы приложений, удаления временных файлов, в которых нуждалось приложение .

Если в вашем проекте имеется несколько форм, вам понадобится работать с ними, показывая и скрывая их при необходимости. Чтобы отобразить форму, воспользуйтесь методом Show (Показать). Для скрытия формы используйте метод Hide (Скрыть).

Выполним небольшое упражнение. Создадим несколько форм и настроим навигацию между ними. Одна из форм будет служить главной — из нее вызываются остальные формы.

Создайте новый документ Microsoft Word , откройте редактор кода и добавьте в проект три формы. Настройте их свойства следующим образом (табл. 4.1.).

Добавьте на форму frm_Main две кнопки. Одну из них назовите cmd_Call_F1 и подпишите ее "Вызвать первую форму", вторую – cmd_Call_F2 с подписью "Вызвать вторую форму". Эти кнопки должны скрывать форму frm_Main , и, соответственно, вызывать frm_First и frm_Second .

На форму frm_First добавьте кнопку cmd_First с надписью "На главную". Нажатие на эту кнопку должно скрывать frm_First и отображать frm_Main . Аналогичную кнопку (только с именем cmd_Second ) добавьте на frm_Second .

Вот как (рис. 4.2.) выглядит окно проекта.

На рис. 4.3. вы можете видеть окно кода формы frm_Main после создания необходимых обработчиков событий.

Обработчик события Click для кнопки cmd_First формы frm_First выглядит так (листинг 4.1, комментарии опущены).

Обработчик события Click кнопки cmd_Second формы frm_Second выглядит аналогично — главное отличие — он скрывает frm_Second

Теперь вставить элемент управления можно через меню: Разработчик/ Элементы управления/ Вставить .

Обратите внимание, что в этом меню можно также вставить Элементы ActiveX, которые расположены ниже интересующих нас Элементов управления формы. У обоих типов есть одни и те же элементы Кнопка, Список, Флажок и т.п. Разница между ними следующая: чтобы использовать Элементы ActiveX необходимо использовать VBA, а Элементы управления формы можно напрямую привязать к ячейке на листе.

Счетчик ( Spin button, Spinner ) как, впрочем и все другие Элементы управления формы, возвращает только 1 числовое значение. См. файл примера лист Счетчик .

Обзорную статью обо всех элементах управления формы можно прочитать здесь .

Вставка Счетчика

Через меню Разработчик/ Элементы управления/ Вставить выберем левой клавишей мыши элемент Счетчик.

После этого выпадающее меню закроется, а курсор вместо обычного толстого крестика

превратится в тонкий крестик.

Кликнув левой клавишей мыши в нужное место на листе, элемент Счетчик будет помещен на лист.

Выделение Счетчика

После вставки Счетчика он становится выделенным. Если кликнуть в любом другом месте листа, то Счетчик перестанет быть выделенным. Чтобы снова его выделить нужно кликнуть его ПРАВОЙ клавишей мыши (клик ЛЕВОЙ клавиши увеличивает или уменьшает значение в связанной ячейке (см. ниже)). После клика правой кнопкой также появляется контекстное меню, чтобы его убрать можно нажать ESC или кликнуть левой клавишей по Счетчику .

Перемещение Счетчика и изменение его размеров

Если навести курсор на выделенный Счетчик (курсор примет форму 4-х направленных в разные стороны стрелок), затем нажать и удерживать левую кнопку мыши, то можно переместить Счетчик . Удерживая клавишу ALT можно выровнять Счетчик по границам ячеек. Выделенный Счетчик также можно перемещать стрелками с клавиатуры.

Если навести курсор на углы прямоугольника или на маленькие квадратики на границе, то можно изменить его размер.

Связываем Счетчик с ячейкой

Как было сказано выше, все Элементы управления формы возвращают значение. Это значение помещается в ячейку определенную пользователем. Чтобы связать Элемент управления с ячейкой, кликните на него ПРАВОЙ клавишей мыши, в появившемся контекстном меню выберите Формат объекта. Появится диалоговое окно, выберите вкладку Элемент управления (если такая вкладка отсутствует, то Вы вставили Элемент ActiveX, а не Элемент управления формы, об этом см. выше).

В поле Связь с ячейкой нужно ввести ссылку на ячейку. Свяжем наш Счетчик с ячейкой А1 .

Также установим минимальное значение =1, максимальное =101, шаг изменения =2.

Убедитесь, что Счетчик не выделен. Пощелкайте левой клавишей мыши по верхней кнопке Счетчика . В ячейке А1 значение будет увеличиваться в указанном диапазоне, причем с шагом 2 (1, 3, 5, . ), т.е. в ячейку будут вводиться только нечетные числа. При щелчке по нижней кнопке, значения будут уменьшаться.

Существует и другой способ связать Элемент управления и ячейку: Выделите правой клавишей мыши Элемент управления, в Строке формул введите =, затем кликните левой клавишей мыши на нужную ячейку, нажмите клавишу ENTER . Чтобы изменить ячейку, с которой связан Элемент управления, достаточно перетащить эту ячейку, взяв за ее границу, в нужное место.

Одну ячейку можно связать с несколькими элементами управления, но имеет ли это смысл? Решать Вам.

Примечание . Можно принудительно ввести в ячейку текстовое значение, но оно будет заменено при следующем нажатии Счетчика . Проведем эксперимент. Пусть в ячейке А1 введено число 5. Даже если Вы введете в ячейку А1 текст " строка ", то при следующем нажатии Счетчика , например вверх, в ячейке появится число 7 (если шаг =2), т.е. Счетчик хранит текущее значение не в ячейке, а где-то в себе.

Если, в нашем примере, Вы введете четное значение, то Счетчик не сбросит его, а будет прибавлять 2 и Вы получите четную последовательность 2, 4, 6, . Но, при достижении верхней границы его поведение изменится 96, 98, 100, 101, т.к. максимальное значение установлено нами =101. Теперь при движении вниз Счетчик будет воспроизводить последовательность нечетных чисел! Тоже справедливо и для нижней границы: 6, 4, 2, 1, т.к. минимальное значение установлено =1. Поэтому, следите, чтобы граничные значения (при шаге отличным от 1), содержались в требуемой последовательности, иначе при движении вверх и вниз Вы можете получить разные последовательности. Например, для последовательности 1, 4, 7, 10 (шаг 3) правильно установить границы 1 и 10. Если Вы установите границы 1 и 9, то при движении от 1 Вы получите последовательность 1, 4, 7, 9, затем при движении от 9 - получите 9, 6, 3, 1, т.е. 2 разные последовательности!

Использование Счетчика

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

Приведем 2 примера использования Счетчика.

1. График функции с параметром

Построим график функции y=5*x+а , где а - это параметр, который мы будем изменять с помощью Счетчика . При изменении параметра а график (прямая) будет смещаться вверх или вниз (см. файл примера лист Счетчик ).

При нажатии на Счетчик (верхняя клавиша), значение в связанной ячейке В7 будет увеличиваться, следовательно все значения в столбце у также будут увеличиваться, сдвигая график вверх.

2. Формирование отчета

Пусть имеется таблица с данными об успеваемости группы. Необходимо распечатать карточки по каждому студенту. В карточке ( Отчете ) должна отображаться фамилия студента, количество баллов, оценка и информация о зачете.

Здесь Счетчик будем использовать, чтобы последовательно выбирать студентов. Строка с выбранным студентом подсвечивается Условным форматированием . Данные из таблицы в отчет будут попадать с помощью формулы =ВПР(D26;A29:B37;2)

Нажимая на Счетчик , отчет будет обновляться, а выбранный студент выделяться в таблице.

Имя Элемента управления

У каждого Элемента управления есть имя. Чтобы его узнать нужно выделить Счетчик , в Поле имя будет отображено его имя. Чтобы изменить имя Счетчика - в Поле имя введите новое имя и нажмите клавишу ENTER . Также имя можно изменить в Области выделения ( Главная / Редактирование/ Найти и выделить/ Область выделения ).

Зачем нам знать его имя? Если Вы не планируете управлять Счетчиком из программы VBA, то имя может потребоваться только для настройки его отображения на листе. Об этом читайте ниже.

Прячем Счетчик на листе

Включите Область выделения ( Главная / Редактирование/ Найти и выделить )

В Области выделения можно управлять отображением не только Элементов управления, но и других объектов на листе, например рисунков.

Нажмите на изображение глаза напротив имени объекта и объект исчезнет/ появится.

Расширяем возможности Счетчика

Диапазон изменения значений Счетчика может содержать только положительные значения, шаг - только целые и положительные значения. Этого не всегда достаточно. Научимся использовать формулы, чтобы расширить возможности Счетчика (см. файл примера ).

Чтобы иметь возможность изменять значение в ячейке с шагом 0,1 используйте формулу =A42/10 ( Счетчик связан с ячейкой А42 ).

Чтобы изменять значение в ячейке от -101 до -1, используйте формулу =-102+A50 (границы Счетчика установлены от 1 до 101, Счетчик связан с ячейкой А50 )

Шаг изменения счетчика можно сделать переменным, например, используя квадратичную зависимость (1, 4, 9, 16, . ) с помощью формулы =A58*A58 ( Счетчик связан с ячейкой А58 ).

Шаг изменения счетчика можно сделать различным в зависимости от диапазона, например, (в диапазоне от 1 до 11 шаг=1, от 11 до 101, шаг=10) с помощью формулы =ЕСЛИ(A66 (границы Счетчика установлены от 1 до 20, Счетчик связан с ячейкой А66 ).

Элемент управления SpinButton предназначен в VBA Excel для ввода пользователем числовых данных, которые ограничены минимальным и максимальным значениями. Увеличение или уменьшение числового значения счетчика при однократном нажатии кнопки происходит с указанным шагом.

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

Обычно счетчик используется в паре с элементом управления TextBox или Label. Вспомогательный элемент необходим, чтобы отобразить значение счетчика на пользовательской форме.

Свойства элемента SpinButton

Свойство	Описание
BackColor	Цвет кнопок.
Delay*	Время между последовательными событиями при удержании кнопки.
ControlTipText	Текст всплывающей подсказки при наведении курсора на счетчик.
Enabled	Возможность взаимодействия пользователя с элементом управления. True – взаимодействие включено, False – отключено (цвет стрелок становится серым).
Height	Высота элемента управления.
Left	Расстояние от левого края внутренней границы пользовательской формы до левого края элемента управления.
Max	Максимальное значение свойства Value.
Min	Минимальное значение свойства Value.
Orientation**	Задает горизонтальную или вертикальную ориентацию элемента управления SpinButton.
SmallChange	Шаг изменения значения свойства Value.
TabIndex	Определяет позицию элемента управления в очереди на получение фокуса при табуляции, вызываемой нажатием клавиш «Tab», «Enter». Отсчет начинается с 0.
Top	Расстояние от верхнего края внутренней границы пользовательской формы до верхнего края элемента управления.
Visible	Видимость элемента SpinButton. True – элемент отображается на пользовательской форме, False – скрыт.
Width	Ширина элемента управления.

* По умолчанию свойство Delay равно 50 миллисекундам. Это означает, что первое событие (SpinUp, SpinDown, Change) происходит через 250 миллисекунд после нажатия кнопки, а каждое последующее событие – через каждые 50 миллисекунд (и так до отпускания кнопки).

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

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

Элемент управления VBA SpinButton добавляет на поверхность формы счетчик, как и для ScrollBar тут можно задавать как минимальное так и максимальное значение. Объект SpinButton эффективно использовать при работе с небольшими значениями. Он представляет из себя ту же полосу прокрутки с двумя кнопками, но без полосы прокрутки.

Как и с ScrollBar, мы будет использовать компонент VBA SpinButton для вычисления суммы чисел. Хотя более эффективно он может использоваться для вывода определенных данных. Например, объект vba будет хранить пять значений, при нажатии по кнопкам компонента в текстовом поле будет выводиться заданное значение.

Как и для ScrollBar, для SpinButton VBA свойства практически те же.

Базовые свойства класса SpinButton VBA

Max и Min – тут мы задаем целые значения, максимально и минимальное. Значения могут находиться в диапазоне от −32 767 до +32 767. Задавать числовые значения можно как в прямом так и в обратном порядке, например, Min=1, а Max=100 или наоборот, Min=100, а Max=1. Хотя, так как тут лучше использовать небольшие диапазоны, то порядок перебора будет не сильно актуален.

SmallChange – определяет шаг при нажатии на управляющие кнопки, по умолчанию данное свойство содержит числовое значение 1, вы можете задать собственный шаг в диапазоне от −32 767 до +32 767. В принципе, увеличивать значение шага актуально при математических вычислениях, так как в иных случаях, нам просто надо выбрать значение из счетчика – vba SpinButton.

Orientation – свойство позволяет задать ориентацию объекта класса VBA SpinButton, то есть, горизонтальное или вертикальное положение. Тут можно задавать три варианта значений: 1 – стоит по умолчанию и определяет положение согласно параметров формы, -1 – задает горизонтальную ориентацию и 0 – вертикальное положение. Для формы мы будем использовать вертикальное размещение.

Visible – не только для текущего элемента управления, но и для других, свойство задает видимость. То есть, при значении false мы можем спрятать объект, но в памяти он будет хранится, что бы вновь его показать, значение свойства меняем на true.

Value – хранит выбранное в текущий момент значение, значения выбираются из диапазона, заданного свойствами Min и Max.

При изменении данных для счетчика возникает событие Change , оно является ключевым для SpinButton VBA компонента.

Теперь напишем пример и создадим форму.

Добавьте в окно проектов форму с именем SpinForm и модуль с именем SpinModule, за добавление отвечает меню Insert. Что бы быстро открыть редактор VisualBasic нажмите комбинацию Alt + F11.

Пропишите в редакторе кода для формы (просто выберите текущую форму в окне Проектов и нажмите кнопку View Code, расположенную вверху слева) следующий код:

Данный код отвечает за вывод формы при запуске макроса, тут процедура SpinModule содержит вызов метода Show для объекта SpinForm класса UserForm.

На поверхности формы расположите компоненты:

Label1 – будет содержать информативную надпись о том, что мы работаем с компонентом “Счетчик”

Label2 – будет содержать результат суммирования чисел от нуля до того значения, которое выбрано в компоненте VBA SpinButton

SpinButton1 – собственно, наш счетчик, который будет содержать диапазон от 0 до 10.

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

В редакторе кода для формы нам надо прописать следующие процедуры:

SpinButton1_Change – обработка ключевого события. При нажатии на управляющие кнопки будет происходить обработка цикла for, в котором будет происходить вычисление суммы чисел от 0 до выбранного значения в данный момент. За выбранное значение отвечает свойство Value, а точнее, SpinButton1.Value. После вычисления суммы в свойство Caption второй метки будет записана информирующая строка со значением полученной суммы.

UserForm_Initialize – собственно, тут происходит определение свойств компонентов на форме при инициализации формы UserForm.

Ну что же, пример довольно простой да и статья не тянет на что-то гениальное. Мы просто рассмотрели объект VBA класса SpinButton, который позволяет добавлять счетчик на поверхность формы.

Спасибо за внимание. Автор блога Владимир Баталий

Существуют различные способы подсчета в MS Excel. Мы можем использовать встроенные функции, такие как Count, CountIf, CountA, CountBlank, которые есть в Excel. Но эти функции не будут работать должным образом в VBA и также имеют ограничения. Чтобы преодолеть это, у нас есть много функций и команд, а также процесс, с помощью которого мы можем выполнить VBA Counter. Счетчик VBA используется для подсчета ячеек, подсчета содержимого ячеек, а также используется в качестве таймера обратного отсчета, когда мы можем реализовать расписание для измерения любой задачи.

С помощью счетчика VBA мы можем также подсчитать ячейки с их цветовыми показателями. Этот индекс цвета может также использовать цвет шрифта или фона ячейки, а также полужирные значения.

Как создать счетчик в Excel VBA?

Ниже приведены различные примеры создания счетчика в Excel с использованием кода VBA.

Вы можете скачать этот шаблон VBA Counter Excel здесь - Шаблон VBA Counter Excel

Счетчик VBA - Пример № 1

У нас есть некоторые данные случайных чисел в столбце А. Все упомянутые значения являются случайными и не имеют связи, такой как в последовательности или серии. Теперь с помощью процесса счетчика VBA мы посчитаем верхнее и нижнее значения и получим выходные данные в ячейках D2 и D3.

Мы посчитаем число и одновременно раскрасим их согласно цвету текста в ячейках C2 и C3.

Для этого выполните следующие шаги:

Шаг 1. Создайте кнопку «Command», которая доступна на вкладке « Разработчик » в Active X Control меню «Вставка», как показано ниже.

Шаг 2: Теперь создайте кнопку, как показано ниже.

Шаг 3: Щелкните правой кнопкой мыши на созданной кнопке и перейдите к опции Свойства .

Шаг 4: Измените заголовок кнопки, которая по умолчанию называется CommandButton2, на любое желаемое имя. Здесь мы изменили это на « Подсчет клеток по значению ».

Шаг 5: Сделав это, щелкните правой кнопкой мыши на созданной кнопке и выберите опцию View Code, чтобы открыть окно VBA.

Редактор Visual Basic открыт сейчас, и по умолчанию подпрограмма для командной кнопки уже создана.

Код:

Шаг 6: Теперь нам нужно объявить 3 переменные. Объявите первую переменную, в которой мы будем хранить числа, и вторую переменную, в которой мы будем использовать счетчик в качестве целых чисел, как показано ниже. И 3- я переменная будет длинной, чтобы сосчитать числа, хранящиеся в каждой строке.

Код:

Шаг 7: Теперь мы выберем первую ячейку листа, которая является A1 в качестве нашего диапазона. Отсюда мы будем выбирать ячейки, пока в них не появятся номера.

Код:

Шаг 8: Теперь мы откроем и запишем условие в цикле For, если ячейка имеет значение больше 10, тогда цвет шрифта ячейки будет изменен на Желтый, а для значения меньше 10 цвет шрифта ячейки будет изменен на Синий с индексами цвета 44 и 55 соответственно.

Код:

Шаг 9: Теперь, чтобы увидеть вывод значений из ячейки A1 до конца, мы выберем ячейки, как описано выше с помощью приведенного ниже кода.

Код:

Шаг 10: Теперь запустите код, нажав кнопку.

Мы увидим, что ячейки с номерами меньше 10 выделены синим цветом. И тот, что больше 10, выделен желтым цветом.

Счетчик VBA - Пример № 2

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

Для этого выполните следующие шаги:

Шаг 1: Создайте 2 вкладки любой фигуры в меню «Вставка» «Фигуры», как показано ниже. Мы выбираем Rectangle: Rounder Corners .

Шаг 2: Создайте 2 кнопки и назовите их «Пуск» и «Сброс», с помощью которых мы запустим таймер и очистим время.

Шаг 3: Теперь перейдите в VBA и откройте модуль из меню «Вставка».

Шаг 4: В этом напишите подкатегорию «Таймер запуска», как показано ниже.

Код:

Шаг 5: Рассмотрим слово для строки Next, например NextRow, где мы выберем рабочий лист, в котором мы хотим реализовать код. Здесь наш лист Sheet2 . И здесь мы посчитаем строки из ячейки +1, которая является ячейкой A2, пока у нас не будет курсора значение файла.

Код:

Шаг 6: Теперь, чтобы перейти к следующей строке, мы будем использовать формат вершины, где 1 в скобках для оси Y.

Код:

Шаг 7: Напишите код для сброса ниже того же кода запуска.

Код:

Шаг 8: Теперь для сброса мы сохраним тот же код, что и Start, но ключевое слово будет изменено на LastRow, и мы удалим +1 только для того, чтобы нам не пришлось попадать в другую строку.

Код:

Шаг 9: Теперь, чтобы очистить данные в последней ячейке столбца A до первой, сначала выберите диапазон ячеек с функцией ClearContents .

Код:

Теперь закройте окно VBA.

Шаг 10: Щелкните правой кнопкой мыши на вкладке « Пуск » и выберите « Назначить макрос» .

Шаг 11: Выберите Начальное субкодирование, которое мы делали выше.

Шаг 12: То же самое можно сделать и для кнопки сброса. Присвойте субкод « Сброс» кнопке « Сброс», нажмите «ОК» и выйдите из режима разработки, чтобы выполнить код.

Шаг 13: Теперь сначала нажмите кнопку « Пуск» . Время начала мы получим как 3:51:33.

Шаг 14: Теперь снова нажмите кнопку «Пуск» 2-3 раза, чтобы получить многократный промежуток времени. Мы увидим, что в каждой итерации есть промежуток в несколько секунд.

Шаг 15: Теперь после этого мы протестируем кнопку « Сброс» . Нажмите на Сброс. Мы увидим, что данные очищены и готовы к следующему кругу отсчета времени.

Плюсы Excel VBA Counter

Реализовать счетчик отрезков времени очень просто, как мы видели в примере 2.
VBA Counter имеет много типов реализации, это не ограничивается только примерами, которые мы видели.

То, что нужно запомнить

Всегда лучше назначать код для любой кнопки, когда мы выполнили 2 или более типов кода в одном процессе.
Выйдите из режима разработки после назначения и выполнения кода на вкладке «Разработчик».
Мы также можем создать счетчик времени в реальном времени, где мы сможем увидеть обратный отсчет времени или посмотреть время в реальном времени.
Сохраните макрос в Macro, включите формат файла Excel, чтобы мы не потеряли наш код.