Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как запустить макрос при изменении значения ячейки в Excel?

Как на VBA сохранить файл Excel с названием, взятым из ячейки?

Здесь мы рассмотрим сценарий, в котором вам нужно создать имя файла в Excel, сформированное из значения ячейки или даже нескольких ячеек. В этом посте показан простой метод реализации этой задачи.

Исходные данные

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

Примечание! Я использую Excel 2013.

Скриншот 2

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

Начнем с самой простой ситуации, когда ячейка, из которой будет сгенерировано имя файла, известна заранее, и ее адрес не меняется.

Сохранение файла Excel с названием из ячейки — с привязкой к этой ячейке

Имея данные, нам нужно создать процедуру в VBA (макрос), которая берет значение из определенной ячейки, в данном случае B14, и присваивает его названию файла.

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

Если вам нужно записывать макрос в каждый файл, т.е. в макросовместимые файлы (расширение.xlsm), то при записи достаточно указать другой тип файла, т.е. xlOpenXMLWorkbookMacroEnabled, я это указываю в процедурах в комментариях.

Если вы работаете с Excel, вы можете вставить следующий код процедуры в исходный код этой книги (This Book, дважды щелкните, чтобы открыть) или в модуль, который вы должны создать заранее.

Примечание: Чтобы открыть редактор Visual Basic в Excel, перейдите на вкладку «Разработчик» и нажмите на «Visual Basic». Файл Excel, содержащий код процедуры, должен быть сохранен как «Макросовместимая рабочая книга Excel».

Код процедуры

Запуск макроса после сохранения файла (Макросы -> Запуск -> Сохранить файл) — это все, что необходимо.

В результате процедура прошла успешно в моем случае, файл был сохранен с именем «Car make 1», это значение взято из ячейки B14, как указано в сообщении в конце процедуры. Файл сохраняется в той же папке, что и исходный файл (все приведенные ниже примеры имеют одинаковое имя, т.е. сохраняются рядом с исходным файлом, но вы можете это изменить).

Скриншот 3

Добавление кнопки в Excel для запуска макроса

Не очень удобно каждый раз открывать окно макросов и выбирать нужный макрос, поэтому можно просто добавить кнопку где-нибудь рядом с данными и просто нажать ее. Это делается следующим образом: «вкладка Разработчик -> Вставка -> Кнопка (элемент управления формой)».

Скриншот 4

Затем выберите место, куда вы хотите вставить кнопку, и щелкните по нему. Это вызовет окно назначения действия, т.е. выберите макрос, который будет запускаться при нажатии этой кнопки, выберите наш макрос, т.е. SaveFile, и нажмите OK.

Скриншот 5

В результате появится кнопка с именем «Кнопка», которую вы, возможно, захотите переименовать в «Сохранить файл». Для этого щелкните правой кнопкой мыши на кнопке и выберите настройку «Изменить текст». Соответственно, вы должны получить что-то вроде этого.

Скриншот 6

Сохранение файла Excel с названием из ячейки — без привязки к ячейке

Теперь представим, что мы не можем заранее определить, какая ячейка будет формировать имя файла (может быть B14, а может и нет), поэтому мы можем немного подкорректировать алгоритм так, чтобы он брал значение из той ячейки, которая активна, но в этом случае вы, конечно, должны заранее ее выделить (т.е. встать на нее).

Замените код процедуры на следующий код, очень незначительно измененный.

Читайте так же:
Как легко скопировать и вставить диапазон или диаграмму как изображение в Excel?

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

Скриншот 7

Как видите, все прошло успешно.

Сохранение файла Excel с названием, которое сформировано из значений двух ячеек

Теперь представим, что нам нужно сформировать файл с именем из значений двух ячеек. На примере ниже разделителем может быть «Марка автомобиля — VIN-номер», в качестве разделителя я указал символ — (дефис), но можно использовать любой символ, на ваш выбор.

Этот пример иллюстрирует возможность его реализации с привязкой к конкретным клеткам, в нашем случае B14 и D14.

В этом случае код процедуры будет выглядеть следующим образом

Скриншот 8

ОК, файл создан.

Если вам вдруг понадобится реализовать без привязки к конкретным ячейкам, например, значения хранятся в определенных столбцах, но конкретная строка заранее неизвестна. Например, у меня есть несколько строк со значениями, и конкретные значения, которые будут взяты за основу имени файла, я хочу указать сам, непосредственно перед сохранением, но без редактирования кода процедуры.

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

Код процедуры

Запустите макрос в любой ячейке столбца B, содержащей значение.

Как запустить макрос при изменении значения ячейки в Excel?

Цель этого шага — изучить некоторые особенности работы с кодом VBA.

Журнал представляет собой довольно простую структуру, однако наличие в нем формул может привести к его поломке при случайном изменении или удалении, в результате чего список будет вести себя странно. Кроме того, пользователь должен постоянно вводить дату операции. Давайте избавимся от этих недостатков.

Для этого создайте последовательность ввода данных. В приведенном ниже примере объясняется, как заполнить строку 16 для ввода информации о номере расходного ордера. 4.

Главное условие — автоматизация процесса ввода данных должна снизить трудоемкость и уменьшить вероятность внесения ошибок. Далее мы предполагаем, что заполнение журнала и формирование заказов происходит не задним числом, а на ежедневной основе.

Приемы автоматизации заполнения журнала кассовых документов

  • запись макросов ввода отдельных формул и последующая замена формул на определенные ими значения;
  • создание кода VBA поиска первой пустой строки в списке и перемещения по этой строке;
  • соединение всего кода VBA созданных макросов в одну подпрограмму, выполняющую всю последовательность действий в автоматическом режиме.

Изменение параметров Excel перед записью макросов

Если вы не вводите данные в ячейки для редактирования, вы можете нажимать клавиши для перемещения на одну ячейку (вниз, вверх, влево, вправо), Home, End, Page Up или Page Down. Чаще всего ввод данных в Excel осуществляется нажатием клавиши Enter, после чего табличный курсор обычно перемещается в ячейку ниже. И это довольно удобно. Большинство пользователей Excel полагают, что это свойство является неизменным. На самом деле, этот параметр устанавливается при установке Excel по умолчанию и может быть изменен при необходимости.

Табличный курсор может переместиться в ячейку ниже после фиксации клавиши ввода Enter при работе непосредственно на рабочем листе, но если вы записываете макрос, это проблематично.

Недостатком является то, что перед остановкой записи макроса или любой другой операции после ввода данных в ячейку (диапазон ячеек) адрес ячейки, в которой появится табличный курсор, будет определен отдельной строкой кода VBA (см. Рисунок 4).

Впоследствии, при выполнении написанного макроса, эта операция будет выполняться как один из переходов к фиксированной ячейке. Кроме того, это увеличивает время выполнения макроса и «утяжеляет» файл Excel количеством символов, содержащихся в этой строке. Кроме того, удаление этих строк кода займет время в редакторе Microsoft Visual Basic Editor.

Чтобы избежать написания макросов, связанных с операциями ввода данных, откройте диалоговое окно Параметры с помощью меню Сервис и снимите флажок Переместить в другую ячейку после ввода на вкладке Правка (Рисунок 1).

Рисунок 1. Диалоговое окно Параметры, вкладка Правка с открытым выпадающим списком В направлении с активированной опцией Переместить в другую ячейку после ввода

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

Запись кода VBA при вводе формул в процессе записи макроса

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

Рис.2. Последовательность предстоящих операций, подлежащих автоматизации при формировании записи о кассовом чеке в журнале

Автоматизация ввода текущей даты

Согласно правилам заполнения журнала, первым действием должен быть ввод даты этой операции. Если операция заполнения журнала выполняется в день ее выполнения, то для автоматического ввода даты достаточно ввести в ячейку F16 функцию ввода текущей даты. Это можно сделать с помощью кнопки Мастер функций на стандартной панели инструментов или путем ручного ввода строки с клавиатуры с помощью функции TODAY (Рисунок 3).

Рис.3. Функциональная панель TODAY

В функции TODAY, которая возвращает текущую дату, аргументы отсутствуют.

  • введите в ячейку В16 функцию СЕГОДНЯ и не перемещайте табличный курсор;
  • выполните процедуру начала записи макроса, которому присвойте имя РасходныйОрдер и при необходимости введите его описание;
  • запись макроса заключается в последовательном нажатии на клавишу F2 (редактирование содержимого ячейки) и клавишу Enter ;
  • произведите остановку записи макроса.

Рис.4. Окно программы с макрокодом ExpenditureOrder

При написании макроса ExpendOrder режим спуска ячеек не был отключен (Рисунок 1). Поэтому на рисунке 4 вы видите вторую строку кода:

Запись кода формул определения порядкового номера и замены строки с формулами на значения

Макросы2 и Макрос3 пишутся одинаково — сначала вы вводите формулы в ячейку A16 следующим образом:

Рисунок 5: Пример окна программы с кодом для Macros2, Macros3 и Macros4.

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

  • установите курсор в любую пустую ячейку, и выполните команду Правка | Копировать (или комбинация клавиш Ctrl+С );
  • выполните команду Правка | Специальная вставка и в появившемся диалоговом окне Специальная вставка активизируйте переключатель Значения , после чего нажмите кнопку ОК ;
  • остановите запись макроса.

Метод Специальная вставка

Используя диалоговое окно «Специальная вставка», рассмотрите следующий код VBA, записанный при вставке из буфера обмена:

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

  • xlAll — все;
  • xlFormulas — формулы;
  • xlValues — значения;
  • xlFormats — форматы.

  • xlNone — нет;
  • xlAdd — сложить;
  • xlSubtract — вычесть;
  • xlMultiply — умножить;
  • xlDivide — разделить.

Есть два аргумента SkipBlanks и Transpose. Они могут быть установлены в True или False.

Поскольку задача Macros4 создает только вставленные значения, удалите ненужный код VBA (рисунок 8).

Соединение макросов

На рисунке 6 показана последовательность действий при вводе даты, порядковых номеров и подстановки формул со значениями. Но прежде чем заставить макросы выполнять все записанные операции последовательно, их нужно соединить друг с другом.

Рис.6. Последовательность действий полного макроса expenditureOrder

  • перенос написанных макросов Макрос2 , Макрос3 и Макрос4 в один макрос РасходныйОрдер в той последовательности, в которой они записывались;
  • редактирование полученного макроса РасходныйОрдер и добавления процедур с целью последовательного выполнения операций показанных на рисунке 6;
  • ввод примечаний.

Скопируйте два макроса вместе, чтобы получился один. На рисунке 7 показано, как это сделать с помощью окна Visual Basic Editor. Выберите область кода, начинающуюся с конца последнего символа, включая первый символ апострофа, как показано на рисунке.

Рис. 7. Выбор фрагмента макроса для копирования и вставки в другой макрос

На рисунке 8 вы видите макрос, созданный после объединения всех макросов.

Рисунок 8: Макрос, полученный в результате комбинирования четырех макросов

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

Абсолютная и относительная ссылки при выполнении макроса

Excel при механической записи макросов выполняет абсолютную запись, т.е. сохраняет точные адреса ячеек при их активации. «Точный» адрес ячейки определяется из левого верхнего угла рабочего листа. Например, в ячейке B3 адрес равен (3,2) или пересечению третьей строки и второго столбца.

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

После запуска макроса необходимо переместить курсор таблицы в ячейку E16, чтобы выполнить макрос, записанный в Macro2. Активной ячейкой Макроса1 является F16, на которую устанавливается табличный курсор перед выполнением макроса. Этого можно добиться, поместив на эту ячейку относительную ссылку. Ввод метода Select означает перемещение от активной ячейки на 1 столбец влево и 0 строк вниз (вверх) и выделение (активизацию). Код операции в VBA записывается следующим образом:

Для выполнения фрагмента Macros3 необходимо сдвинуть 4 колонки влево:

Macros4 требует добавления относительного перехода, а также установки диапазона перед выполнением фрагмента:

Если макрос записан в относительном режиме, то предполагается, что ячейка, в которой находится курсор таблицы, имеет адрес A1 , а адрес ячейки (или диапазона), указанный после относительного перехода, берется относительно этой ячейки A1 , поэтому если мы укажем адрес A1:F1 , то справа будут назначены шесть ячеек, включая активную ячейку, в которой находился курсор таблицы. Или, в нашем примере, ячейки A16:F16 .

Последний элемент подпрограммы — переход в ячейку G16 для ввода суммы почтового перевода. Поэтому подайте команду на перемещение от активной клетки на 6 клеток вправо:

Методы ввода кода VBA

Код на VBA можно писать либо непосредственно с клавиатуры, либо из диалогового окна Object View (рис. 9), которое активизируется командой View | Object View, а также нажатием клавиши F2.

Рис.9. Редактор Microsoft Visual Basic с открытым диалоговым окном View Objects в верхнем правом углу

Используйте окно Object Viewer для просмотра объектов, классов, методов, свойств, событий, констант и функций в библиотеках объектов. Это диалоговое окно упрощает использование справочной системы Microsoft Visual Basic. Для этого выделите интересующий вас объект и нажмите F1.

  • в раскрывающемся списке Проект | Библиотека выберите библиотеку Excel ;
  • в окне Компонент выделите объект ActiveCell и скопируйте в буфер обмена;
  • перейдите в окно программы и, установив курсор в теле подпрограммы, произведите вставку скопированного;
  • после ввода команды Точка появится список свойств и методов, которые могут быть использованы для дальнейшего написания кода для этого объекта. С помощью полосы прокрутки найдите нужное свойство или метод. Поиск можно ускорить при вводе после точки первых символов кода. Для ввода названия свойства или метода в подпрограмму дважды щелкните по нему правой кнопкой мыши и т.д.

  • при выполнении команды Правка | Список свойств/методов ;
  • при помощи контекстного меню;
  • комбинации клавиш Ctrl+J ;
  • нажатии на кнопку Список свойств/методов на панели инструментов Правка (рисунок 10).

Рисунок 10: Панель инструментов для редактирования

  • нажатии на кнопку Завершить слово на панели инструментов Правка (рисунок 10);
  • выполнении команды Правка | Завершить слово ;
  • используя контекстное меню;
  • комбинации клавиш Ctrl+Space .

Примечания

Последним элементом редактирования подпрограммы является добавление примечаний для описания действий, выполняемых подпрограммой. Примечание не является командой и игнорируется во время выполнения макроса.

  • поместите курсор в начало строки и введите знак апострофа с клавиатуры;
  • поместите курсор на строку и выделите блок строк, после чего нажмите на кнопку Закомментировать блок на панели инструментов Правка (рисунок 10).

Чтобы удалить знак комментария, удалите знак апострофа с помощью клавиш Delete или Backspace или, выделив строку (блок строк), нажмите кнопку Uncomment block на панели инструментов Edit (Редактирование).

11.6 Объект Range, его свойства и методы

Диапазон — это, пожалуй, самый популярный объект в иерархии объектной модели Excel. Вы можете использовать этот объект для представления одной ячейки, нескольких ячеек (включая несмежные ячейки или группы несмежных ячеек) или всего листа. Для ввода данных в Word можно использовать как объект Range, так и объект Selection, но в Excel все дело в объекте Range:

  • Если вам нужно ввести данные в ячейку или отформатировать ее, вам нужно получить объект Range, представляющий эту ячейку;
  • Если вы хотите что-то сделать с выделенными ячейками, вам нужно получить объект Range, представляющий выделение;
  • Если вы просто хотите что-то сделать с группой ячеек, вашим первым действием, опять же, будет получение объекта Range, представляющего группу ячеек.

В этой статье из базы знаний Microsoft описано 22 способа получения объекта Range в Excel. Скорее всего, вы не будете использовать все эти методы. Давайте рассмотрим наиболее распространенные:

  • Самый простой и очевидный способ — использовать свойство Range. Это свойство доступно для объектов приложений, объектов электронных таблиц и самого объекта Range (если вы решили создать новый диапазон на основе существующего диапазона). Например, если вы хотите получить ссылку на объект Range, представляющий ячейку A1, вы можете сделать следующее:

Dim oRange As Range

А для диапазона ячеек от A1 до D10 — вот так

Dim oRange как диапазон

Нужно быть очень осторожным при применении свойства Range к самому объекту Range. Идея заключается в том, что Excel создает виртуальный лист на основе объекта Range со своей собственной нумерацией. Таким образом, вы должны закодировать его следующим образом:

Set oRange1 = Worksheets("Лист1").Range("C1")

Запишет значение 20 не в ячейку B1, как предполагает код, а в ячейку D1 (то есть B1 относительно виртуального листа, начиная с C1).

  • Второй способ — использовать свойство Cells. У этого свойства меньше возможностей — мы можем вернуть диапазон, состоящий из одной ячейки. Но мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода к любому количеству ячеек и т.д.) Например, чтобы получить ссылку на ячейку D1, мы можем использовать код типа

Dim oRange как Диапазон

Set oRange = Worksheets("Лист1").Cells(1, 4)

Чтобы получить диапазон, состоящий из нескольких ячеек, удобно использовать свойства Range и Cells совместно:

Set oRange = Range(Cells(1, 1), Cells(5, 3))

  • Третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменять текущий диапазон или создавать из него новый диапазон. Эти свойства будут рассмотрены позже.

Как только вы найдете нужную ячейку, необходимо записать в нее что-нибудь. Для выполнения этой задачи можно использовать свойство Value, например:

Для эффективной работы в Excel необходимо знать все свойства и методы объекта Range. Ниже приведены некоторые из наиболее распространенных свойств:

  • Адрес — позволяет вернуть адрес текущего поля, например, в предыдущем примере будет возвращен адрес $A$1:$C$5. Этому свойству можно передать множество параметров — указать стиль ссылки, абсолютный или относительный адрес для столбцов и строк, к чему должен быть относительный адрес и т.д. Это свойство доступно только для чтения. AddressLocal — то же самое, но с учетом специфики локализованных версий Excel.

На практике часто требуется разобрать адрес ячейки и вернуть в качестве результата имя столбца или номер строки. Благодаря знакам доллара это очень легко сделать с помощью строковых функций. Если объект oRange представляет одну ячейку, то имя столбца можно вернуть следующим образом:

sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))

sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))

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

  • AllowEdit — это свойство, доступное только для чтения, позволяет определить, сможет ли пользователь править данную ячейку (набор ячеек) на защищенном листе. Используется для проверок.
  • Areas — свойство исключительно важное. Дело в том, что, как уже говорилось, объект Range может состоять из несмежных наборов ячеек. Многие методы применительно к таким диапазонам ведут себя совершенно непредсказуемо или просто возвращают ошибки. Свойство Areas позволяет разбить подобные нестандартные диапазоны на набор стандартных. Созданные таким образом объекты Range будут помещены в коллекцию Areas. Это свойство можно использовать и для проверки "нестандартности" диапазона:

Hvis Выбор.Области.Число > 1 Derefter

Debug.Print "Диапазон с несмежными областями"

  • Borders — возможность получить ссылку на коллекцию Borders, с помощью которой мы можем управлять рамками в нашем ассортименте.
  • Cells — это свойство также доступно для объекта Range. Он работает точно так же, за исключением того, что он снова использует свою собственную виртуальную адресацию, основанную на диапазоне :

Dim oRange, oRange2 As Range

Set oRange = Range(Cells(2, 2), Cells(5, 3))

Set oRange2 = oRange.Cells(1, 1) ‘Вместо A1 получаем ссылку на B2

Debug.Print oRange2.Address ‘Так оно и есть

Свойства Row и Rows, Column и Columns имеют абсолютно те же особенности.

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

Dim oRange As Range

oRange.Characters(1, 1).Font.Color = vbRed

Как и в третьей строке примера, если вы хотите изменить только значение, то используйте свойство Value.

  • Count — возвращает количество ячеек в диапазоне. Его можно использовать для проверок.
  • CurrentRegion — очень полезное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (изначально мы не знаем, когда эти данные будут). Он возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (т.е. непустой областью, включающей исходный диапазон/ячейку). Например, чтобы выделить всю непустую область вокруг активной ячейки, вы можете использовать следующий код
  • Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
  • Worksheets("Лист1").Activate
  • ActiveCell.Dependents.Select

Precedents позволяет визуализировать обратные зависимости. Свойства DirectDependents и DirectPrecedents можно использовать для просмотра первого уровня зависимостей.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector