Понимание ячеек Excel и функций диапазона в VBA
Excel мощный Если вы часто его используете, вы, вероятно, уже знаете много хитростей, используя формулы или автоформатирование, но используя ячейки а также Спектр Функции в VBA, вы можете поднять свою аналитику Excel на совершенно новый уровень.
Проблема с использованием функций Cells и Range в VBA состоит в том, что на продвинутых уровнях большинству людей трудно понять, как эти функции действительно работают. Использование их может очень запутать. Вот как вы можете использовать их так, как вы, вероятно, никогда не предполагали.
Сапер на VBA — S. H. T. E. M.
- Применить план вокруг группы
- Проверьте правильность написания текста внутри диапазона ячеек
- Очистить, скопировать или вырезать ячейки
- Поиск по диапазону с помощью метода «Найти»
- Намного больше
Во многих отношениях функция Range намного мощнее, чем использование ячеек, поскольку она позволяет вам ссылаться либо на одну ячейку, либо на определенный диапазон ячеек одновременно. Вы не собираетесь зацикливаться на функции Range, потому что ссылки на ячейки не являются числами (если только вы не внедрили в них функцию Cells).
Vba Excel Событие Пересчета Формулы в Ячейке
Шаг 239.
VBA в MSExcel. Автоматизация рабочего процесса по формированию и учету кассовых документов. Дальнейшая автоматизация заполнения журнала
На этом шаге мы рассмотрим некоторые особенности работы с кодом VBA .
Несмотря на то, что журнал формируется относительно просто, наличие в нем формул может вызвать его «сбой» при случайном изменении или удалении одной из формул, сортировке списка. Кроме того, пользователю необходимо постоянно вводить дату проведения операции. Устраним эти недостатки.
Для этого разработайте последовательность ввода данных. Пример будет описан на примере заполнения строки 16 по вводу данных расходного ордера №4.
Главное условие — автоматизация этого процесса должна уменьшить трудоемкость операции ввода данных и уменьшить возможность ввода ошибки. Далее предполагаем, что заполнение журнала и формирование ордеров происходит не задним числом, а день в день.
Приемы автоматизации заполнения журнала кассовых документов
- запись макросов ввода отдельных формул и последующая замена формул на определенные ими значения;
- создание кода VBA поиска первой пустой строки в списке и перемещения по этой строке;
- соединение всего кода VBA созданных макросов в одну подпрограмму, выполняющую всю последовательность действий в автоматическом режиме.
Изменение параметров Excel перед записью макросов
И если при работе по вводу данных непосредственно на рабочем листе, перемещение табличного курсора на ячейку ниже после фиксации ввода клавишей Enter — удобство, то при записи макроса — недостаток.
Недостаток заключается в том, что либо перед остановкой записи макроса или при проведении каких-либо других операций после ввода данных в ячейку (диапазон ячеек), адрес ячейки, в которой окажется табличный курсор, будет зафиксирован отдельной строкой кода VBA (см. рисунок 4).
В последующем, при выполнении записанного макроса, эта операция будет выполняться, как один из переходов на зафиксированную ячейку. Это увеличивает продолжительность выполнения макроса и «утяжеляет» файл Excel на количество символов этой строки. А при редактировании кода макроса в Редакторе Microsoft Visual Basic будет потрачено время на удаление этих строк.
Поэтому, прежде чем записывать макросы, связанные с операциями ввода данных, с помощью команды Сервис | Параметры откройте диалоговое окно Параметры и на вкладке Правка (рисунок 1) удалите флажок с опции Переход к другой ячейке после ввода .
Рис.1. Диалоговое окно Параметры , вкладка Правка с открытым раскрывающимся списком В направлении при активизированной опции Переход к другой ячейке после ввода
Иначе при записи макроса, перемещение табличного курсора после нажатия на клавишу Enter на ячейку, по заданному этой опцией в направлении, выбранном в раскрывающемся списке В направлении , будет отражено в сгенерированном коде макроса.
Запись кода VBA при вводе формул в процессе записи макроса
Автоматизация процесса будет основана на написании четырех макросов и последующем их объединении с последовательностью выполнения операций, приведенных на рисунке 2.
Рис.2. Последовательность предстоящих операций, подлежащих автоматизации при формировании записи расходного кассового ордера в журнале
Автоматизация ввода текущей даты
Функция СЕГОДНЯ не имеет аргументов и возвращает текущую дату.
- введите в ячейку В16 функцию СЕГОДНЯ и не перемещайте табличный курсор;
- выполните процедуру начала записи макроса, которому присвойте имя РасходныйОрдер и при необходимости введите его описание;
- запись макроса заключается в последовательном нажатии на клавишу F2 (редактирование содержимого ячейки) и клавишу Enter ;
- произведите остановку записи макроса.
Рис.4. Окно программы с кодом макроса РасходныйОрдер
При записи макроса РасходныйОрдер режим перехода на ячейку вниз отключен не был (рисунок 1). Поэтому на рисунке 4 вы видите вторую строку кода:
которую удалите при редактировании. В этой строке находится объект 4-го уровня иерархии — Range . Этот объект используется для ссылок на ячейку или диапазон ячеек.
смысл которой заключается в том, что в активную ячейку (в которой находился до начала записи макроса табличный курсор) ввести формулу ввода текущей даты. Следует заметить, что все функции в коде VBA записываются на английском языке, а вводимые формулы заключаются в кавычки.
Запись кода формул определения порядкового номера и замены строки с формулами на значения
Запись макросов Макрос2 и Макрос3 осуществляется аналогично — сначала вводятся формулы в ячейку А16 :
Рис.5. Окно программы с кодом макросов Макрос2 , Макрос3 и Макрос4
Следующий, четвертый макрос, предназначен для замены формул, вычисленными значениями.
- установите курсор в любую пустую ячейку, и выполните команду Правка | Копировать (или комбинация клавиш Ctrl+С );
- выполните команду Правка | Специальная вставка и в появившемся диалоговом окне Специальная вставка активизируйте переключатель Значения , после чего нажмите кнопку ОК ;
- остановите запись макроса.
Метод Специальная вставка
Проанализируем записанный код VBA при вставке из буфера обмена скопированной области через диалоговое окно Специальная вставка :
Для того чтобы узнать его свойства и присваиваемые ему аргументы, запишите макрос последовательно производя вставку через диалоговое окно Специальная вставка и активизируя различные переключатели и опции. Ниже рассмотрены те из них, которые, возможно, будут применяться при разработке приложений.
Аргументам SkipBlanks ( Пропускать пустые ячейки ) и Transpose ( Транспонировать ) могут присваиваться два значения False ( Ложь ) и True ( Истина ) .
В связи с тем, что задача Макрос4 производит только вставку значений, удалите ненужный код VBA (рисунок 8).
Соединение макросов
Последовательность процесса выполнения операции ввода даты, порядковых номеров и замены формул значениями приведена на рисунке 6. Но прежде чем заставить макросы выполнять последовательно все записанные операции их необходимо соединить друг с другом.
Рис.6. Последовательность действий полного макроса РасходныйОрдер
- перенос написанных макросов Макрос2 , Макрос3 и Макрос4 в один макрос РасходныйОрдер в той последовательности, в которой они записывались;
- редактирование полученного макроса РасходныйОрдер и добавления процедур с целью последовательного выполнения операций показанных на рисунке 6;
- ввод примечаний.
Для соединения макросов в один примените метод копирования. Для этого в окне Редактора Visual Basic выделите область от конца последнего символа вверх кода VBA , включая первый встречающийся знак апострофа, как это показано на рисунке 7.
Рис.7. Выделение фрагмента макроса для копирования и вставки в другой макрос
После соединения всех макросов получился макрос, показанный на рисунке 8.
Рис.8. Макрос, полученный в результате соединения четырех макросов
Но данный макрос «работать» не будет, потому что он произведет вставку всех формул в одной и той же выделенной ячейке, которая перед выполнением макроса была активна.
Абсолютная и относительная ссылки при выполнении макроса
Как правило, при механической записи макросов, Excel выполняет абсолютную запись, то есть сохраняет точные адреса ячеек при их активизации. В свою очередь «точный» адрес ячейки ведет свой отсчет от левого верхнего угла рабочего листа. Например, адрес ячейки В3 — (3,2) или пересечение третьей строки и второго столбца.
При относительном режиме записи адрес ячейки будет зависеть от местонахождения активной ячейки на рабочем листе в данный момент времени, то есть отсчет ведется относительно адреса активной ячейки.
в которой знак минус указывает, что необходимо передвинуться на один столбец влево, а значение 0 — передвинуться на ноль значений вниз и считать эту ячейку активной (ActiveCell) и выделенной (Select) . Эту строку введите перед фрагментом, скопированным из Макрос2 .
Для выполнения фрагмента из Макрос3 необходимо передвинуться на 4 столбца влево:
Перед выполнением фрагмента из Макрос4 необходимо не только добавить относительный переход, но и выделить диапазон следующим образом:
И последний элемент выполнения подпрограммы — переход на ячейку G16 для ввода суммы приходного ордера. Следовательно, надо задать команду перехода от активной ячейки на 6 ячеек вправо:
Методы ввода кода VBA
Запись кода VBA можно производить только с клавиатуры, а можно и с помощью диалогового окна Просмотр объектов (рисунок 9), которое активизируется командой Вид | Просмотр объектов или нажатием на клавишу F2 .
Рис.9. Приложение Редактор Microsoft Visual Basic с открытым диалоговым окном Просмотр объектов в правом верхнем углу
При помощи окна Просмотр объектов можно просматривать в объектных библиотеках объекты, классы, методы, свойства, события, константы и функции. При помощи этого диалогового окна удобно использовать справочную систему Microsoft Visual Basic . Для этого выделите интересующий объект и нажмите на клавишу F1 .
- в раскрывающемся списке Проект | Библиотека выберите библиотеку Excel ;
- в окне Компонент выделите объект ActiveCell и скопируйте в буфер обмена;
- перейдите в окно программы и, установив курсор в теле подпрограммы, произведите вставку скопированного;
- после ввода команды Точка появится список свойств и методов, которые могут быть использованы для дальнейшего написания кода для этого объекта. С помощью полосы прокрутки найдите нужное свойство или метод. Поиск можно ускорить при вводе после точки первых символов кода. Для ввода названия свойства или метода в подпрограмму дважды щелкните по нему правой кнопкой мыши и т.д.
- при выполнении команды Правка | Список свойств/методов ;
- при помощи контекстного меню;
- комбинации клавиш Ctrl+J ;
- нажатии на кнопку Список свойств/методов на панели инструментов Правка (рисунок 10).
- нажатии на кнопку Завершить слово на панели инструментов Правка (рисунок 10);
- выполнении команды Правка | Завершить слово ;
- используя контекстное меню;
- комбинации клавиш Ctrl+Space .
Примечания
И последний элемент редактирования подпрограммы — добавление примечаний для описания действий выполняемых подпрограммой. Примечания не являются командами и при выполнении макроса игнорируются.
- поместите курсор в начало строки и введите знак апострофа с клавиатуры;
- поместите курсор на строку и выделите блок строк, после чего нажмите на кнопку Закомментировать блок на панели инструментов Правка (рисунок 10).
Для того чтобы убрать знак комментария, удалите знак апострофа, используя клавиши Delete или Backspace , или выделив строку (блок строк) нажмите кнопку Раскомментировать блок на панели инструментов Правка .
Понимание ячеек Excel и функций диапазона в VBA
АргументPrompt определяет сообщение, которое появится в диалоговом окне. Наберите текст (в кавычках). Используйте переменную типа string или объединенные строковые переменные и строки со значком &, например:
Вычисление
Создаете вы сложный отчет или простую таблицу в программе, функции вычисления одинаково необходимы в обоих случаях.
С помощью горячих функций можно проводить все расчеты в несколько раз быстрее и эффективнее.
Прописав любую формулу, пользователь самостоятельно определяет порядок действий, которые будут произведены над ячейкой.
Операторы – это символьные или условные обозначения действий, которые будут выполнены в ячейке.
Список горячих клавиш и операторов, которые они вызывают:
| Комбинация | Описание | Excel 2003 и старше | Excel 2007 и 2010 |
| SHIF+F3 | Данная комбинация вызывает режим мастера функций | Вставка → Функция | Формулы → Вставить функцию |
| F4 | Переключение между ссылками документа | ||
| CTRL+ |
Как обновить формулы в excel быстрой клавишей
- Введите формулу на чистый лист (чтобы можно было проверить как работает данный пример).
- Выберите инструмент: «Формулы»-«Параметры вычислений»-«Вручную».
- Убедитесь, что теперь после ввода данных в ячейку (например, число 7 вместо 1 в ячейке A2 как на рисунке), формула не пересчитывает результат автоматически. Пока пользователь не нажмет клавишу F9 (или SFIFT+F9).
Свойства объектов, значения ячеек и значения переменных — все это примеры ввода. Данные ввода можно получить во время выполнения процедуры от пользователя. Это называется пользовательским вводом.
| Комбинация | Описание | Excel 2003 и старше | Excel 2007 и 2010 |
| SHIF+F3 | Данная комбинация вызывает режим мастера функций | Вставка → Функция | Формулы → Вставить функцию |
| F4 | Переключение между ссылками документа | ||
| CTRL+ |
Формула специальной вставки в VBA
Ниже приведена формула для специальной вставки в VBA.
Тип пасты: После копирования данных, как вы хотите вставить. Если вы хотите вставить значения, формулы, форматы, проверку и т. д. Ниже приведен полный список параметров, доступных в разделе Тип вставки.
Вставить специальную операцию: Во время вставки вы хотите выполнять какие-либо операции, такие как сложение, вычитание, деление, умножение или ничего.
Функции даты-времени в VBA. Работа с датой и временем
Даже если вы знакомы с Microsoft Excel, вы можете быть удивлены количеством и разнообразием сочетаний клавиш, которые вы можете использовать для ускорения вашей работы и, как правило, сделать вещи более удобными.
DateSerial
Функция, которая возвращает дату для заданных параметров дня, месяца и года.
Синтаксис
Параметр/Описание
- Год — требуемый параметр. Число от 100 до 9999 или числовое выражение. Значения от 0 до 99 интерпретируются как годы с 1900 по 1999 год. Для всех аргументов за другой год используйте полный четырехзначный год.
- Месяц — требуемый параметр. Он также может быть в форме выражения, которое должно варьироваться от 1 до 12.
- День — требуемый параметр. Он также может быть в форме выражения, которое должно варьироваться от 1 до 31.
пример
Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.
Функция, которая возвращает действительную дату для данного года, месяца и даты.
Ввод-вывод данных на лист Excel с помощью метода Cells. Диалоговый ввод — вывод в VBA Vba msgbox вывод значения переменной
-
Дата — требуемый параметр. День недели вернет указанную дату. Firstdayofweek — необязательный параметр. Задает первый день недели. Он может принимать следующие значения. 0 = vbUseSystemDayOfWeek — настройка API поддержки национальных языков (NLS) 1 = vbSunday — воскресенье 2 = vbMonday — понедельник 3 = vbВперед — вторник 4 = vbWednesday — среда 5 = vbThursday — четверг 6 = vbFriday — пятница 7 = vbSaturday — суббота
Если вы хотите рассчитать бонус на первом листе, используя значения из двух листов, есть несколько способов сделать это. Вы можете написать формулу в первой ячейке, которая выполняет вычисления, используя данные на двух листах, и перетащить ее вниз. Это сработает.
Сапер на VBA
Однажды, мне захотелось попробовать сделать элементарную игру «Сапер» в Excel на VBA.
Игра завершается тогда, когда открыты все ячейки, кроме тех, в которых содержатся мины — то есть в нашем случае (20х20-40) = 360 ячеек.
Для реализации этой задумки сначала был составлен алгоритм:
- Создать поле из ячеек
- Расположить мины на поле
- Расположить цифры на поле, показывающее количество мин вокруг
- Скрыть все поле другими цветами
- Создать события по нажатию на ячейку: если пустая, с цифрой, с миной
Также был составлен список проблем или недоработок, которые были найдены уже после написания кода:
- Установка флажка на ячейку, по нажатию правой кнопки отсутствует (некоторые люди ставят флажки на тех местах, где по их мнению может быть мина, но они не уверены на 100%).
- В оригинальной игре «Сапер» на Windows, при первом открытии ячейки нельзя попасть на мину: сначала игрок нажимает на ячейку, а затем генерируется поле, где нажатая ячейка НЕ является миной, а потом уже идет полноценная игра. На момент написания кода я этого не учел, поэтому с некоторой вероятностью первым кликом можно попасть на мину. Пока писал данный пункт — понял, как это реализовать, но решил, что переделывать уже не буду.
- Нет уведомлении об успешном «разминировании» всего поля. Добавлю это спустя некоторое время: в теории это не очень сложно, но, вероятно, это немного замедлит скорость выполнения всех макросов.
- Также в оригинальной игре есть возможность открыть все скрытые ячейки вокруг цифры, при условии, что все мины вокруг нее уже открыты — нужно было нажать на цифру левой кнопкой при зажатой правой. Функция довольно полезная, но на VBA реализовать ее мне не удалось.
Итак, «Сапер»:
Наш первый пункт — создать поле из ячеек. В Excel с этим не может возникнуть никаких проблем, ведь лист Excel по своей сути и есть набор ячеек. Единственное, что нам нужно сделать — определиться, какого размера будет наш «игровой квадрат», нарисовать границы и сделать ширину ячеек равной ее высоте. В конечном итоге, наше поле будет выглядеть так (поле 20х20):
Пункт второй: необходимо в нашей игре «Сапер» расположить на поле все мины. С этим пунктом также не должно возникнуть никаких проблем — все просто и элементарно. Нужно в случайных местах нашего поля расставить необходимое нам количество мин. Для этого пишем нужный нам код:
Пункт третий: необходимо в ячейках разместить цифры, которые будут показывать количество мин вокруг. Ниже представлен код для выполнения данного пункта:
В этом макросе мы проверяем каждую ячейку нашего минного поля, перебирая двумя циклами строки и столбцы. Мы считаем, сколько мин вокруг ячейки и вписываем в нее их количество. Если же мин вокруг ячейки нет — оставляем ее пустой.
Пункт четвертый: скрыть все мины и цифры. Это максимально просто: закрашиваем весь фон игрового диапазона новым цветом и точно таким же цветом окрашиваем шрифт мин и цифр, тем самым маскируя все поле:
Все, поле игры «Сапер» на листе Excel готово. Теперь приступаем к самому главному!
Пункт пятый: события по нажатию на ячейку. Возможно, для кого-то данный пункт покажется сложным для восприятия, но я постараюсь рассказать все максимально просто.
- Отследить «выделение ячейки» и запустить определенный код в зависимости от ее содержимого
- Если ячейка равна «Б» — вывести сообщение о проигрыше и открыть все поле
- Если ячейка равна цифре — отобразить данную цифру, изменив ее цвет на черный
- Если ячейка пустая — запустить код по открытию прилежащей пустой области, ограниченной цифрами с количеством мин
- Если ячейка не относится к игровому полю — не делать ничего
- Первая — количество выделенных ячеек. По правилам игры, мы должны щелкнуть на одну ячейку, потом на другую, третью и так далее. Мы не можем нажать сразу на несколько. Поэтому и в Excel, чтобы «случайно» не нажать на несколько ячеек, т.е. в нашем случае, чтобы случайно их не выделить — добавляем ограничение: если количество выделенных ячеек больше 1 — ничего не выполняем, выходим из процедуры.
- Вторая — проверяем принадлежность выделенной ячейки нашему игровому полю. Если ячейка не имеет никакого отношения к нему — не делаем ничего, в противном случае запускаем обработку ячейки с помощью макроса table_click (target), где target — наша выделенная ячейка. Код макроса table_click:
Если содержимое ячейки равно «Б» — фон ячейки становится белым, шрифт черным. Выводим сообщение о проигрыше и фон всего поля окрашиваем белым (то есть все поле открывается).
Если содержимое равно 1 или больше — фон ячейки также становится белым, а шрифт черным, то есть мы просто «открываем» ячейку с миной.
Если содержимое ячейки равно «», то есть в ней нет ничего, запускаем целый набор макросов:
Включение кода оптимизации — данный код взят отсюда. При запуске деактивируются некоторые опции Excel, которые негативно влияют на быстродействие.
Успешно используется мной во всех проектах и значительно сокращает время работы макросов при работе с большими объемами данных — рекомендую использовать данный код почаще, но старайтесь не забывать его «деактивировать».
Запуск рекурсии по раскрытию пустых ячеек вокруг. Рекурсия — это такое явление в программировании, когда функция/макрос/процедура запускает сама себя. И запускать себя она будет до тех пор, пока не встретится ограничивающее условие (в противном случае она может зациклиться и не закончиться никогда).
И если мы нажали на пустую ячейку — после выполнения данного макроса откроется вся пустая область вокруг нее.
Выключение кода оптимизации. Активируем опции Excel, которые мы выключили перед выполнением предыдущих макросов:
В моем примере также добавлена кнопка, которая запускает макрос full_game(), который полностью формирует поле для игры — ставит мины и цифры и закрашивает их шрифт и фон одинаковыми цветами. Также в ней запускается вышеупомянутый макрос оптимизации, для сокращения времени работы макросов.
При добавлении каких-либо доработок типа «исключение взрыва при открытии первой ячейки» или «уведомление о победе» будет сообщено дополнительно.
Пример
Функция InputBox() немного сложнее, чем MsgBox(), и позволяет ввести реальное значение или текстовую строку. Поскольку InputBox() получает значение, её бессмысленно применять, не используя введенное значение. Поэтому эта функция всегда записывается с круглыми скобками.
