Vba - сохраните с другим именем, если файл уже существует в каталоге

Сохранение пользовательских функций в файле надстройки

Как создать собственную надстройку? Рассмотрим пошагово.

Шаг 1. Создаем файл надстройки.

Открываем Microsoft Excel, создаем новую книгу и сохраняем ее под любым подходящим именем (например My_Functions) в формате надстройки. Для этого используем меню Файл – Сохранить как или клавишу . Обязательно указываем тип файла Надстройка Excel:

Ваша надстройка будет иметь расширение .xlam.

Обратите внимание, что стандартно Excel хранит надстройки в папке C:\Users\\AppData\Roaming\Microsoft\AddIns. Советую принять расположение по умолчанию

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

Шаг 2. Подключаем файл надстройки.

Теперь созданную нами надстройку My_Functions надо подключить к Excel. Тогда она будет загружаться автоматически при старте программы. Для этого используем меню Файл — Параметры — Надстройки. Убедитесь, что внизу в поле Управление выбраны Надстройки Excel. Жмем на кнопку Перейти в нижней части окна. В появившемся окне отмечаем нашу надстройку My_Functions. Если вы не видите ее в списке, нажмите кнопку Просмотр и укажите расположение вашего файла надстройки вручную.

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

Шаг 3. Добавляем в надстройку пользовательские функции и макросы.

Наша надстройка подключена к Excel, но в ней пока нет ничего. Чтобы добавить в нее новые пользовательские функции, откройте редактор Visual Basic при помощи сочетания клавиш . После этого вы можете добавлять новые модули с кодом VBA, как описано .

Выберите в окне VBAProject ваш файл надстройки (My Finctions.xlam). Используйте меню Insert – Module, чтобы добавить пользовательский модуль. В него нужно записывать пользовательские функции.

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

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

Работа с отдельными ячейками

Синтаксическая форма	Комментарии по использованию
(«D5») или	Ячейка D5 текущего листа. Полная и краткая формы. Тут применим только синтаксис типа A1, но не R1C1. То есть такая конструкция («R1C2») — вызовет ошибку, даже если в книге Excel включен режим формул R1C1. Разумеется после этой формы вы можете обратиться к свойствам соответствующей ячейки. Например, («D5»).
или	Ячейка D5 текущего листа через свойство . 5 — строка (row), 4 — столбец (column). Допустимость второй формы мало кому известна.
	Ячейку D5 можно адресовать и через указание только одного параметра свойсва . При этом нумерация идёт слева направо, потом сверху вниз. То есть сначала нумеруется вся строка (2^14=16384 колонок) и только потом идёт переход на следующую строку. То есть вернёт вам ячейку A2, а D5 будет . Пока данный способ выглядит не очень удобным.

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

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

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

Код процедуры в этом случае будет выглядеть так.

Sub SaveFile () ‘Объявление переменной Dim CellValue As String Dim Path As String Dim FinalFileName As String’ Временно отключить отображение вспомогательных сообщений Application.DisplayAlerts = False ‘Установить каталог для сохранения файла (в данном случае текущий каталог) Path = ThisWorkbook.Path & «\» ‘Проверка значения ячеек B14 и D14, если диапазон («B14»). Значение = «» Или диапазон («D14»). Value = «» Then MsgBox «В ячейке нет значения», vbCritical, «Error!» Exit Sub End If ‘Добавить значения из двух ячеек B14 и D14 CellValue = Range («B14»). Значение & «-» и диапазон («D14»). Значение ‘Сформировать окончательный путь и имя файла FinalFileName = Path & CellValue’ Сохранить файл ActiveWorkbook.SaveAs FileName: = FinalFileName, _ FileFormat: = xlOpenXMLWorkbook ‘FileFormat: = xlOpenXMLWorkbookMacroEnabled’ Чтобы сохранить файл с макросом ‘Включить вывод сообщений Приложение. DisplayAlerts = True MsgBox «Результат был успешно сохранен с именем» & CellValue «End Sub

Запустим макрос.

Все хорошо, файл создан.

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

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

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

Sub SaveFile () ‘Объявление переменной Dim CellValue As String Dim Path As String Dim FinalFileName As String’ Временно отключить отображение вспомогательных сообщений Application.DisplayAlerts = False ‘Установить каталог для сохранения файла (в данном случае текущий каталог) Path = ThisWorkbook.Path & «\» ‘Проверка номера столбца, если ActiveCell.Column 2 Then MsgBox «Указан недопустимый столбец», vbCritical, «Ошибка!» Выход из подпрограммы End If’ Проверить значение ячейки If ActiveCell.Value = «» Then MsgBox » Ячейка не содержит значения «, vbCritical,» Ошибка! » Exit Sub End If ‘Получить значение активной ячейки CellValue = ActiveCell.Value’ Переместить 2 столбца относительно активной ячейки ActiveCell.Offset (0, 2). Выберите ‘Добавить значения из двух ячеек CellValue = CellValue & «- «& ActiveCell .Value ‘Формируем окончательный путь и имя файла FinalFileName = Path & CellValue’ Сохраняем файл ActiveWorkbook.SaveAs FileName: = FinalFileName, _ FileFormat: = xlOpenXMLWorkbook ‘FileFormat: = xlOpenXMLWorkbookMacroEnabled’ Чтобы сохранить файл с макросом файл был успешно сохранен с именем — «& CellValue, vbInformation,» Result «End Sub

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

Скрытие / отображение персональной макрокоманды

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

Нажмите вкладку VIEW на ленте.
Нажмите Показать в группе окон.

Нажмите вкладку VIEW на ленте.

Нажмите Показать в группе окон.

Откроется диалоговое окно «Показать».

PERSONAL.XLSB появится в поле «Показать книгу» и нажмите «ОК».

Теперь вы можете просматривать макросы, сохраненные в личной книге макросов.

Чтобы скрыть личную книгу макросов, выполните следующие действия:

Нажмите на личную книгу макросов.
Нажмите вкладку VIEW на ленте.
Нажмите Скрыть на ленте.

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

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

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

Код процедуры в данном случае будет выглядеть следующим образом.

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

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

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

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

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

У меня на этом все, надеюсь, материал был Вам полезен, пока!

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

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

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

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

Как видим, все отработало.

Перемещение указателя внутри файла

Каждый открытый файл поддерживает указатель на текущую позицию в файле. Для перемещения и манипулирования позицией указателя внутри файла используются следующие функции и процедуры:

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

Запишем строку в файл в бинарном режиме, а затем переместим указатель в начало файла и считаем содержимое файла посимвольно в строку (листинг 12.1).

Листинг 12.1. Перемещение указателя внутри файла

Объекты Range

Range это диапазон ячеек. Минимум — одна ячейка, максимум — весь лист, теоретически насчитывающий более 17 миллиардов ячеек (строки 2^20 * столбцы 2^14 = 2^34).
В Excel объявлены глобально и всегда готовы к использованию несколько коллекций, имеющий членами объекты типа , либо свойства это же типа.
Коллекции глобального объекта : , , , а также свойства , , , . — активная ячейка текущего листа, — если вы написали пользовательскую функцию рабочего листа, то через это свойство вы можете определить какая конкретно ячейка в данный момент пересчитывает вашу функцию. Об остальных перечисленных объектов речь пойдёт ниже.

Пример Example

В этом примере показано, как сохранить активную книгу. This example saves the active workbook.

В этом примере показано, как сохранить все открытые книги, а затем закрыть Microsoft Excel. This example saves all open workbooks and then closes Microsoft Excel.

В этом примере показано использование события BeforeSave для проверки того, что определенные ячейки содержат данные, прежде чем можно будет сохранить книгу. This example uses the BeforeSave event to verify that certain cells contain data before the workbook can be saved. Книга не может быть сохранена до тех пор, пока не будут указаны данные в каждой из следующих ячеек: D5, D7, D9, D11, D13 и D15. The workbook cannot be saved until there is data in each of the following cells: D5, D7, D9, D11, D13, and D15.

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

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

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

Код процедуры в данном случае будет выглядеть следующим образом.

   
   Sub SaveFile()

   'Объявление переменных
   Dim CellValue As String
   Dim Path As String
   Dim FinalFileName As String

   'Временно отключаем показ вспомогательных сообщений
   Application.DisplayAlerts = False

   'Задаём каталог сохранения файла (в данном случае текущий каталог)
   Path = ThisWorkbook.Path & "\"

   'Проверка значения ячеек B14 и D14
   If Range("B14").Value = "" Or Range("D14").Value = "" Then
     MsgBox "В ячейке отсутствует значение", vbCritical, "Ошибка!"
     Exit Sub
   End If

   'Складываем значения из двух ячеек B14 и D14
   CellValue = Range("B14").Value & " - " & Range("D14").Value

   'Формируем итоговый путь и название файла
   FinalFileName = Path & CellValue

   'Сохраняем файл
   ActiveWorkbook.SaveAs FileName:=FinalFileName, _
                      FileFormat:=xlOpenXMLWorkbook
                      'FileFormat:=xlOpenXMLWorkbookMacroEnabled 'Для сохранения файла с макросом

   'Включаем вывод сообщений
   Application.DisplayAlerts = True

   MsgBox "Файл успешно сохранен с названием - " & CellValue, vbInformation, "Результат"

   End Sub

Запускаем макрос.

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

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

   
   Sub SaveFile()

   'Объявление переменных
   Dim CellValue As String
   Dim Path As String
   Dim FinalFileName As String

   'Временно отключаем показ вспомогательных сообщений
   Application.DisplayAlerts = False

   'Задаём каталог сохранения файла (в данном случае текущий каталог)
   Path = ThisWorkbook.Path & "\"

   'Проверка номера столбца
   If ActiveCell.Column <> 2 Then
     MsgBox "Указан некорректный столбец", vbCritical, "Ошибка!"
     Exit Sub
   End If

  'Проверка значения ячейки
   If ActiveCell.Value = "" Then
     MsgBox "В ячейке отсутствует значение", vbCritical, "Ошибка!"
     Exit Sub
   End If

   'Получаем значение активной ячейки
   CellValue = ActiveCell.Value

   'Смещаемся на 2 столбца, относительно активной ячейки
   ActiveCell.Offset(0, 2).Select

   'Складываем значения из двух ячеек
   CellValue = CellValue & " - " & ActiveCell.Value

   'Формируем итоговый путь и название файла
   FinalFileName = Path & CellValue

   'Сохраняем файл
   ActiveWorkbook.SaveAs FileName:=FinalFileName, _
                      FileFormat:=xlOpenXMLWorkbook
                      'FileFormat:=xlOpenXMLWorkbookMacroEnabled 'Для сохранения файла с макросом

   'Включаем вывод сообщений
   Application.DisplayAlerts = True

   MsgBox "Файл успешно сохранен с названием - " & CellValue, vbInformation, "Результат"

   End Sub

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

У меня на этом все, надеюсь, материал был Вам полезен, пока!

Нравится25Не нравится

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

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

Как видим, все отработало.

Что такое VBA

Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.

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

Недостатком программы являются проблемы, связанные с совместимостью различных версий. Они обусловлены тем, что код программы VBA обращается к функциональным возможностям, которые присутствуют в новой версии продукта, но отсутствуют в старой. Также к минусам относят и чрезмерно высокую открытость кода для изменения посторонним лицом. Тем не менее Microsoft Office, а также IBM Lotus Symphony позволяют пользователю применять шифрование начального кода и установку пароля для его просмотра.

Применение в других функциях и процедурах VBA

Пользовательские функции также можно использовать в макросах VBA.

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

Код макроса содержит пользовательскую функцию GetMaxBetween(.Cells, 10, 50). Она находит нужное максимальное значение в активном столбце. Затем это значение будет выделено цветом.

Результат работы макроса вы видите на скриншоте ниже.

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

Применив вместе уже знакомую нам GetMaxBetween и SpellNumber, мы можем получить максимальное значение из диапазона и сразу записать его в виде текста.

Для этого создадим новую пользовательскую функцию, в которой используем уже знакомые нам функции GetMaxBetween и SpellNumber.

Как видите, функция GetMaxBetween является аргументом другой пользовательской функции – SpellNumber. Сначала она определяет максимальное значение, как мы уже неоднократно делали ранее. Затем это число преобразуется в текст.

На скриншоте выше вы видите, как функция SpellGetMaxBetween находит максимальное число в диапазоне от 5000 до 7000, а затем конвертирует его в текст.

Как сохранить программный код макроса? Что такое модули?

Одним из способов установки (сохранения) макросов на своем компьютере является копирование программного кода и его вставка с последующим сохранением в модуль проекта редактора Visual Basic.

Напомню, что язык Visual Basic for Applications (VBA) – полнофункциональный язык программирования, встроенный в различные приложения компании Microsoft, а также других разработчиков программного обеспечения. Код VBA создается в редакторе Visual Basic Editor. Макросы VBA сохраняются в файлах документов в Word и в файлах рабочих книг в Excel. Макросы сохраняются в специальной части файла, которая называется модулем. Модуль – это элемент проекта, в котором хранится программный код. Каждый документ Word, каждая рабочая книга Excel может содержать один или несколько модулей, а может и не содержать ни одного модуля. Модули, сохраняемые в документе или книге имеют общее название — Project (проект).

Модули делятся на два типа: стандартные модули и модули класса. Основное содержание модулей — это макросы и процедуры написанные на языке VBA. Процедура — совокупность команд или инструкций в модуле, выполняемых как одна программа. В VBA существуют процедуры-подпрограммы Subprogram и процедуры- функции Function. Процедура от макроса отличается наличием в программном коде переменных.

Стандартные модули содержат процедуры, которые не связаны с конкретным объектом. Стандартный модуль — это модуль, в который помещают процедуры Sub и Function, которые должны быть доступны для всех процедур в данном приложении.

Модуль класса отличается от стандартного модуля тем, что, кроме процедур содержит еще и описание объекта и используется для создания классов (объектов).

Перед тем как копировать и вставлять программный код макроса в модуль проекта, откроем редактор Visual Basic. Чтобы запустить этот редактор, выберите команду Сервис/Макрос/Редактор Visual Basic, если Вы используете MS Office 2003, либо сочетанием клавиш Alt+F11 (универсальный способ вызова редактора для любых версий). Редактор открывается в новом окне, содержащем несколько панелей, расположением которых можно управлять. На рисунке ниже обозначены три стандартные панели.

1. Основное место занимает самая большая из них — область разработки. Эта панель используется для написания кода программ. При наличии готовых макросов их код отображается в области разработки.

2. На панели проектов представлен список всех составляющих объектов проекта, по умолчанию это три листа (электронные таблицы) и рабочая книга.

3. На панели свойств отображаются все свойства, ассоциированные с выбранным объектом. Изменения, которые вносятся на панель свойств, влияют на свойства объекта. Кроме того, изменения в области разработки отражаются в значениях величин, которыми определяются свойства объекта.

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

Если в списке проекта отсутствует модуль Module1, то перед записью макроса его нужно добавить. Кроме того, можно добавлять объекты формы, создавая в программе графические интерфейсы пользователя.

Для вставки модуля в проект, выберите в меню редактора VBA команду Insert/Modul, либо воспользуйтесь контекстным меню, щелкнув правой кнопкой мыши в области панели проектов и выбрав пункт меню, как показано на картинке ниже. В список проекта будет добавлен модуль с именем Module1.

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

Таким образом сохранение макросов на своем компьютере можно описать следующей последовательностью шагов:

Находите и копируете в буфер обмена код макроса;
Открываете на своем компьютере редактор Visual Basic (Alt+F11), выбираете существующий (или при необходимости добавляете новый) модуль;
Вставляете в свой модуль данные из буфера обмена;
Сохраняете внесенные изменения.
Вызываете список своих макросов сочетанием клавиш Alt+F8, выбираете и запускаете нужный Вам макрос.

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

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

Код процедуры в данном случае будет выглядеть следующим образом.

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

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

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

У меня на этом все, надеюсь, материал был Вам полезен, пока!

Сохранение листа в из книги как отдельный файл.xls

Макрос сохранение активного листа книги в отдельный файлКод сохраняет файл, но с пустым содержанием. Подскажите, в чем может быть проблема? With.

Сохранение листа книги в отдельный файл в альбомном видеДобрый день! Есть данный макрос, он сохраняет лист из книги в отдельный файл, но не совсем так как.

Сохранение книги как отдельный файлКоллеги, доброго дня. Подскажите советом — нахожусь на развилке между удобством и автоматизацией.

Сохранение текущего листа Excel в отдельный файлДоброго всем времени суток! Помогите справиться с небольшой проблемкой. Есть файл Excel с.

Сохранение листа книги в файле — проблема с защитой листа и привязкой макросовС толкнулся с такой проблемой при сохранении листа в файле вот код который сохраняет лист в.

Klim_ul, давай для начала с разделом определимся — это VB6 или VBA(Excel)?

Где такая кнопка есть — на форме, на листе, в тулбаре?

Отдельно от самой книги. например открыта Книга.xls состоящая из 3-х листов, далее на каждом из листов ввели какую либо информацию. Мне нужно сохранить только лишь Лист3 из этой книги, как отдельный файл.xls название которого будет текущая дата.

Добавлено через 2 минуты Это VBA(Excel)! А кнопку создаем сами CommandButton и привязываем Макрос!

Добавлено через 40 секунд Кнопка на листе!

Добавлено через 43 секунды Апострофф, Буду оч. благодарен за помощь!

Огромное Всем спасибо.

Добавлено через 15 минут И еще один вопрос. Где указать путь куда будет сохраняться файл. В данных примерах у меня сохраняет в текущую директорию, а мне нужно указать другой путь!

Плохо, когда ноги с головой не дружат:D

Спасибо! Это мой первый опыт в VBA. Не судите строго! Не подскажите, сложно ли сделать так, чтобы все выражения в скопированных листах были сохранены как значения?

Добавлено через 49 минут В коде ниже сохранять как значение, если использовать массивы не получается:

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

Добавлено через 2 часа 30 минут DanAttess, А можно увидеть файл с результатом сохранений?

Для этого потребовалось написать 2 коротеньких алгоритма (макроса):

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

Sub noll() On Error Resume Next ActiveSheet.Cells(2, 2).Value = «» End Sub

Этот макрос следует записать в поле раздела «module» — в «контейнер» модуля.

Выглядит код вызывающий (запускающий ) макрос по клику на ячейку, следующим образом:

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Dim u As String On Error Resume Next If Not Intersect(Target, Range(«B1»)) Is Nothing Then Call noll End If End Sub

Этот код нужно записывать не в контейнер «module», а непосредтвенно в лист. Для этого следует в меню разработчика в просмотре кода:

Кликнуть двойным щелчком по названию листа.
После двойного клика будет открыто поле для внесения кода аналогичное полю модуля (module).

Код стал выглядеть следующим образом:

Private Sub Worksheet_Change(ByVal Target As Range) Dim u As String On Error Resume Next If Not Intersect(Target, Range(«B1»)) Is Nothing Then Call noll End If End Sub

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

голоса

Рейтинг статьи

Создание макроса – практический пример

Для примера возьмём самый обычный файл CSV. Это простая таблица 10х20, заполненная числами от 0 до 100 с заголовками для столбцов и строк. Наша задача превратить этот набор данных в презентабельно отформатированную таблицу и сформировать итоги в каждой строке.

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

Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)

Дайте своему макросу имя (без пробелов) и нажмите ОК.

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

Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).

Во-вторых, в нижнем левом углу окна Excel. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.

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

Далее, введите в ячейки формулы в соответствии с названиями заголовков (даны варианты формул для англоязычной и русифицированной версии Excel, адреса ячеек – всегда латинские буквы и цифры):

=SUM(B2:K2) или =СУММ(B2:K2)
=AVERAGE(B2:K2) или =СРЗНАЧ(B2:K2)
=MIN(B2:K2) или =МИН(B2:K2)
=MAX(B2:K2) или =МАКС(B2:K2)
=MEDIAN(B2:K2) или =МЕДИАНА(B2:K2)

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

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

Далее, мы подведем итоги для всей таблицы, для этого делаем ещё несколько математических действий:

Соответственно:

=SUM(L2:L21) или =СУММ(L2:L21)
=AVERAGE(B2:K21) или =СРЗНАЧ(B2:K21) – для расчёта этого значения необходимо взять именно исходные данные таблицы. Если взять среднее значение из средних по отдельным строкам, то результат будет другим.
=MIN(N2:N21) или =МИН(N2:N21)
=MAX(O2:O21) или =МАКС(O2:O21)
=MEDIAN(B2:K21) или =МЕДИАНА(B2:K21) – считаем, используя исходные данные таблицы, по причине указанной выше.

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A, либо щелкните по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).

Далее, изменим внешний вид заголовков столбцов и строк:

Жирное начертание шрифта.
Выравнивание по центру.
Заливка цветом.

И, наконец, настроим формат итоговых значений.

Вот так это должно выглядеть в итоге:

Если Вас все устраивает, остановите запись макроса.

Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.

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

Чтобы очистить все ячейки от данных, щёлкните правой кнопкой мыши по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов, и из контекстного меню выберите пункт Delete (Удалить).

Теперь наш лист полностью очищен от всех данных, при этом макрос остался записан. Нам нужно сохранить книгу, как шаблон Excel с поддержкой макросов, который имеет расширение XLTM.

Важный момент! Если Вы сохраните файл с расширением XLTX, то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT, он тоже поддерживает макросы.

Когда шаблон сохранён, можно спокойно закрыть Excel.

Создание Личной книги макросов

Изначально Личная книга макросов в программе Excel отсутствует. Чтобы проверить, есть ли она в вашей программе (не создал ли ее кто-то ранее), откройте любую рабочую книгу и перейдите в редактор VBA, нажав сочетание клавиш «Alt+F11» (в этом сочетании используется левая клавиша Alt). Открыть редактор VBA можно и пройдя по пунктам меню:

Excel 2007-2016: «Разработчик» — «Visual Basic» (смотрите, как отобразить вкладку «Разработчик», которая изначально скрыта);

Excel 2000-2003: «Сервис» — «Макрос» — «Редактор Visual Basic».

Откроется редактор VBA:

Проводник в редакторе VBA

Если в проводнике сверху вы видите строку «VBAProject (PERSONAL.XLSB)», значит Личная книга макросов на вашем компьютере уже есть.

Если же ее нет, необходимо записать какой-нибудь простенький макрос, выбрав при его создании Сохранить в: Личная книга макросов.

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