Как правильно сохранить и применять пользовательскую функцию Excel

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

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

[expert_bq id=»1570″]На примере инструкций, которые были приведены ранее, можно сделать вывод о том, что использование VBA и макросов не предполагает больших сложностей. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.

VBA Excel: что это такое, как пользоваться объектами и применить макросы

• Если рабочих файлов много, а функция нужна везде, то и копировать код придется в каждую книгу.
• Нужно не забыть сохранить рабочую книгу в формате с поддержкой макросов (xlsm или xlsb).
• При открытии такого файла защита от макросов будет каждый раз показывать предупреждение, которое нужно подтвердить. Многие пользователи пугаются, видя предупреждение на жёлтой полосе, которое просит их включить макросы. Чтобы не видеть это сообщение, вам нужно отключить защиту Excel полностью. Это может быть не всегда правильно и безопасно.

Запись: Если вы не видите разработчик в меню Excel, затем добавьте его. Выбрать файл, Опции, Настроить лентуи выберите Все команды из выпадающего списка слева. Тогда двигайся разработчик от левой панели вправо и выберите OK, чтобы закончить.

Как на Vba Сохранить Файл Excel с Названием Взятым из Ячейки

Например, нам необходима команда, которая бы вставляла в выделенные ячейки цифру «1». Запускаем поисковик, набираем поисковую фразу, получаем результаты, начинаем просматривать, находим код примерно в таком виде:

Выделяем данный код (без нумерации строк, начиная со слова Sub) и нажимаем Ctrl+C. Переходим в свою рабочую книгу MS Excel и нажимаем сочетание клавиш Alt+F11, у вас откроется окно редактора VBA:

В левом окне «Project — VBA Project» выбираем (щелкаем мышкой) нашу рабочую книгу, в которую необходимо вставить макрос, например, «VBAProject (Книга2)»:

В левом окне «Project — VBA Project» у вас должна появиться новая папка «Modules» и в ней новый объект «Module1»:

Переходим курсором в правое «Большое» поле для ввода и нажимаем Ctrl+V, скопированный макрос вставиться в модуль:

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

Если вы работаете в MS Excel 2007, 2010 или 2013, вам необходимо будет сохранить вашу рабочую книгу как «Книга Excel с поддержкой макросов (.xlsm)»:

В MS Excel 2003 достаточно будет просто сохранить файл.

Чтобы выполнить скопированный в книгу макрос выделяем ячейки, в которые необходимо вставить «1», нажимаем в меню «Вид» кнопку «Макросы» и в выпавшем списке выбираем пункт «Макросы» или нажимаем сочетание клавиш Alt+F8:

Откроется диалоговое окно «Макрос», в списке макросов выбираем свой и нажимаем кнопку «Выполнить»:

Макрос выполнится — в выделенные ячейки вставиться «1»:

Макросы в MS Excel можно вставлять в следующие места:

• В Модуль, обычно вставляют код макросов, запуск которых будет производится по нажатию кнопки пользователем (как, например, в нашем случае) или код функций (формул);
• В Рабочий лист, обычно вставляют код макросов, запуск которых должен происходить автоматически в зависимости от действий пользователя или изменения данных в листе (поменялись данный, макрос выполнился);
• В Рабочую книгу, обычно вставляют код макросов, запуск которых должен происходить автоматически в зависимости от действий, производимых над книгой (файлом). Например, макрос, который запускается при открытии или закрытии книги, или при ее сохранении;
• Так же макросы могут быть частью пользовательской формы.

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

Чтобы вставить код в Рабочий лист, в левом окне редактора VBA выберите соответствующий лист, щелкните по нему два раза левой кнопкой мышки, переместите курсор в правое поле ввода и вставьте код.

Давайте потренируемся. Вставьте код опубликованный ниже в «Лист1».

Данный макрос выводит информационное сообщение если вы введете в любую ячейку листа «2».

Вернитесь в рабочую книгу, перейдите в «Лист1» и введите в ячейку «А1» цифру «2» и нажмите Enter, после чего у вас должно появиться следующее сообщение:

Если вы видите это сообщение, то вы все сделали правильно. Если нет, то вы вставили код куда-то не туда, повторите попытку.

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

Находим в интернете VBA код функции, которая подсчитывает, например, количество слов в ячейке:

Копируем код, нажимаем сочетание клавиш Alt+F11, откроется редактор VBA:

Добавляем новый модуль в свою книгу и в этот модуль вставляем скопированный код:

Закрываем редактор VBA и переходим в свою рабочую книгу. Создаем новый лист (необязательно) в ячейку A1 вводим текст «мама мыла раму». Встаем в ячейку, в которой хотим получить результат (количество слов), в меню «Формулы» нажимаем кнопку «Вставить функцию»:

В открывшемся окне «Вставка функции» в поле «Категория» выбираем «Определенные пользователем»

В списке доступных функций выбираем «КолСловВЯчейке», нажимаем «ОК»:

Если вы не сохраните книгу, в которую вставили макрос как «Книгу с поддержкой макросов», все модули с макросами удаляться и вам придется, потом, повторно проделывать всю эту работу.

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

Код должен будет вставиться в рабочий лист без знаков вопроса:

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

Если вы не видите в редакторе VBA окна «Project — VBA Project», перейдите во вкладку меню «View» и в списке выберите пункт «Project Explorer» или нажмите сочетание клавиш Ctrl+R:

[expert_bq id=»1570″]Отменить свойство VeryHidden можно точно так же через режим разработчика, либо используя необходимый нам макрос, о котором я напишу чуть дальше. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Стоит отметить, что у большинства пользователей запуск макросов по умолчанию отключен, именно поэтому на главном листе Main необходимо сделать что-то вроде «памятки» со справочной информацией о том, как активировать работу макросов.

Авторизация в Excel на VBA — S. H. T. E. M.

Как создать пользовательскую функцию? — В решении многих задач обычные функции Excel не всегда могут помочь. Если существующих функций недостаточно, Excel позволяет добавить новые настраиваемые пользовательские функции (UDF). Они делают вашу работу легче. Мы расскажем,…