Код программы в excel

• Понятие макроса.
• Запись простых макросов.
• Выполнение макросов.
• Редактирование макросов.
• Назначение макроса графическим изображениям.
• Удаление макросов из списка макросов.
• Ограниченность макросов.

Понятие макроса Прежде чем приступить к написанию программ на VBA, воспользуемся простой возможностью создания программы (макроса) на языке VBA с использованием MacroRecorder. MacroRecorder — это стандартное средство записи макросов в Excel, посредством которого можно записывать последовательность действий пользователя и получать соответствующий код (программу) на VBA. Программа, созданная с помощью MacroRecorder, называется макросом. Основное назначение макросов — автоматизация работы пользователя. Кроме этого, созданный код макроса может служить основой для дальнейших разработок. При записи макроса запоминаются все действия пользователя, будь то нажатие клавиши или выбор определенной команды меню, которые автоматически преобразуются в программный код на языке VBA. Каждому макросу дается имя, а для быстрого запуска макроса можно создать или присвоить ему “горячую” клавишу (клавишу, по нажатию на которую будет производиться запуск макроса). После запуска макрос будет автоматически выполнен тем приложением, в котором он создан и запущен. При выполнении макроса компьютер воспроизведет все действия пользователя. Макрос — это именованная последовательность заданных пользователем команд и действий, хранящаяся в форме программы на языке VBA.Запись макросов в приложении Excel Для работы с макросами в приложении Excel имеется специальная панель. Рис. 1. Панель для работы с макросами Для вызова этой панели необходимо выполнить последовательно команды меню: “Вид | Панель инструментов | Visual Basic”. Для записи макроса необходимо: 1. Нажать на кнопку “Запись макроса” на панели Visual Basic. 2. В диалоговом окне “Запись макроса” присвоить имя макросу. (В имени макроса первым символом должна быть буква, не допускается использование пробелов.) 3. Выполнить действия, которые нужно записать. 4. Нажать кнопку “Остановить запись”. Пример Рассмотрим процедуру записи последовательности действий на следующем примере: определим максимальное значение в выделенном диапазоне ячеек. В таблице представлена информация о самых ценных алмазах в мире. Нам требуется создать макрос для нахождения алмаза с максимальной массой в граммах. Это действие в Excel можно выполнить с помощью стандартной функции МАКС, но мы продемонстрируем на этом примере, как сохранить последовательность действий пользователя и на их основе создать макрос. Выполните следующие действия: 1. Откройте новую книгу. 2. Создайте таблицу по приведенному образцу. 3. В ячейке D10 наберите “Максимальная масса в граммах”. Курсор после набора текста может находиться в любой ячейке, кроме ячейки, в которой мы хотим получить результат. На панели Visual Basic нажмите на кнопку “Запись макроса”. Появится диалоговое окно “Запись макроса”:

Рис. 2. Окно “Запись макроса” Введите вместо названия “Макрос1” название макроса “Алмаз” и нажмите “ОК”. На экране появится панель инструментов “Остановить запись”. Теперь все производимые нами действия будут записываться до тех пор, пока не будет нажата эта кнопка. Не выполняйте никаких лишних действий. Все ваши действия фиксируются и записываются. Установите курсор в ячейку E10. В этой ячейке должен появиться результат. 4. Выполните команду “Вставка | Функция”. Появится диалоговое окно “Мастер функций”. В окне “Категории функций” выберите “Статистические”. Затем выберите функцию МАКС. Появится диалоговое окно, в первой строке которого необходимо указать диапазон, в котором мы будем искать максимальное значение, — E2:E9. Нажмите “ОК”. 5. Щелкните на кнопке “Остановить запись” на панели Visual Basic. Запись макроса завершена. Выполнение макросов При выполнении макроса Excel повторяет те же действия, которые пользователь выполнял в процессе его создания. Продолжим работу с созданным макросом. Чтобы проверить, как работает созданный нами макрос, прежде всего надо удалить из ячейки E10 полученный результат. Рис. 3. Диалоговое окно функции МАКС Затем: 1. Установите курсор в любую ячейку листа. 2. Выполните команду “Сервис | Макрос | Макросы”. Появится диалоговое окно, показанное на рис. 4: Рис. 4. Диалоговое окно “Макрос” 3. Выделите макрос “Алмаз” и щелкните по кнопке “Выполнить”. В ячейке E10 появится результат. Редактирование макросов Во время записи макроса Excel запоминает ваши действия и преобразует их в код VBA. Можно просмотреть полученный код и отредактировать его, если в этом есть необходимость. Для просмотра созданного макроса выполните команду “Сервис | Макрос | Макросы”. Появится уже знакомое диалоговое окно “Макрос”. Выделим макрос “Алмаз” и щелкнем по кнопке “Изменить”. Откроется окно редактора. Рис. 5. Использование редактора Visual Basic для просмотра и редактирования кода VBA При записи макроса выполнялись всего два действия. Сначала мы установили курсор в ячейку E10. На языке VBA этому действию соответствует строка: Range(«E10»).Select Затем мы вызвали функцию МАКС и в качестве аргумента указали диапазон ячеек E2:E9: ActiveCell.FormulaR1C1 = «=MAX(R[-8]C:R[-1]C)» Полученный код можно редактировать непосредственно в редакторе Visual Basic. Изменим размер шрифта в ячейке E10 на 16: Range(«E10»).Font.Size = 16 Можно также изменить цвет шрифта. Например, изменим цвет текста в ячейке на красный: Range(«E10»).Font.ColorIndex = 3. Значения, которые может принять свойство Font.ColorIndex, изменяются от 1 до 56. После внесенных изменений текст макроса стал следующим: Sub Алмаз() ‘ Алмаз Макрос Range(«E10»).Select ActiveCell.FormulaR1C1 = «=MAX(R[-8]C:R[-1]C)» Range(«E10»).Font.Size = 16 Range(«E10»).Font.ColorIndex = 3 End Sub Закройте окно редактора, вернитесь на лист Excel, удалите содержимое ячейки E10 и запустите макрос на выполнение. Назначение макроса графическим изображениям Разрабатывая приложение, надо думать о том, чтобы создать легкий и удобный интерфейс для выполнения задач автоматизации. Запуск макросов на выполнение посредством команд меню или кнопок на панели Visual Basic — не слишком удобный механизм для пользователей. Для запуска макроса можно использовать любой элемент, находящийся на рабочем листе. Например, можно использовать для запуска любое графическое изображение. Рассмотрим соответствующий пример. Используя панель “Рисование”, нарисуем на листе любую автофигуру. Щелкнем правой кнопкой мыши на изображении и в открывшемся контекстном меню выберем команду “Назначить макрос”. Отобразится диалоговое окно “Назначить макрос объекту”. Затем необходимо выбрать макрос “Алмаз” и щелкнуть вне графического изображения, чтобы снять выделение с объекта. Теперь макрос “Алмаз” можно запустить посредством щелчка на автофигуре. Рис. 6. Автофигура и кнопка для запуска макроса Запустить макрос на выполнение можно и другим способом, использовав командную кнопку. Вставить командую кнопку непосредственно на рабочий лист Excel можно с помощью панели инструментов “Формы”. Затем необходимо назначить ей наш макрос. Удаление макросов из списка макросов Для того чтобы удалить макрос, надо: 1. Выполнить команду “Сервис | Макрос | Макросы”. Появится уже знакомое диалоговое окно. 2. Выделить макрос, подлежащий удалению, и щелкнуть по кнопке “Удалить”. 3. Подтвердить выполнение операции в специальном окне, которое появится. Ограниченность макросов Множество операций Excel можно автоматизировать посредством макросов. Но макросы имеют и ограничения. С помощью макросов нельзя выполнить различные действия в зависимости от содержимого ячейки, нельзя вызвать некоторые диалоговые окна Excel, например, сохранить документ, нельзя отобразить и использовать пользовательские формы ввода данных. Эти ограничения приводят к необходимости создания программ на VBA.

Штрих сканер и EXCEL для инвентаризации товара и имущества.

Импорт данных множества интернет-страниц в Excel с помощью Power Query

Источник: studfile.net

Как создать макрос или скрипт VBA в Excel

Microsoft Excel позволяет пользователям автоматизировать функции и команды с помощью макросов и сценариев Visual Basic для приложений (VBA).

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

На вкладке «Разработчик» на ленте в Excel пользователи могут записывать щелчки мыши и нажатия клавиш (макросы). Однако некоторые функции требуют более глубокого написания сценариев, чем могут обеспечить макросы. Здесь сценарии VBA становятся огромным преимуществом. Это позволяет пользователям создавать более сложные сценарии.

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

В этой статье мы объясним следующее:

• Включение скриптов и макросов
• Как создать макрос в Excel
• Конкретный пример макроса
• Узнайте больше о VBA
• Создать кнопку для начала работы с VBA
• Добавить код для придания кнопкам функциональности
• Это сработало?

Включение скриптов и макросов

Прежде чем вы сможете создавать макросы или сценарии VBA в Excel, необходимо включить вкладку «Разработчик» в меню ленты. Вкладка «Разработчик» не включена по умолчанию. Чтобы включить это:

• Откройте лист Excel.
• Нажмите Файл> Параметры> Настроить ленту.

• Поставьте галочку в поле рядом с Developer.

• Нажмите на вкладку Разработчик в меню ленты.

• Затем нажмите «Безопасность макросов» и установите флажок «Включить все макросы» (не рекомендуется; может выполняться потенциально опасный код).
• Затем нажмите ОК.

Макросы по умолчанию не включены и сопровождаются предупреждением, потому что они представляют собой компьютерный код, который может содержать вредоносное ПО.

Убедитесь, что документ из надежного источника, если вы работаете над общим проектом в Excel и других программах Microsoft.

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

Создать макрос в Excel

Все действия, которые вы выполняете в Excel во время записи макроса, добавляются к нему.

• На вкладке «Разработчик» нажмите «Запись макроса».

• Введите имя макроса, сочетание клавиш и описание. Имена макросов должны начинаться с буквы и не должны содержать пробелов. Сочетание клавиш должно быть буквой.

Решите, где вы хотите сохранить макрос из следующих опций:

• Персональная книга макросов: это создаст скрытый документ Excel с сохраненными макросами для использования с любыми документами Excel.
• Новая рабочая книга: создаст новый документ Excel для хранения созданных макросов.
• Эта рабочая тетрадь: она будет применяться только к документу, который вы в настоящее время редактируете.

Когда закончите, нажмите ОК.

• Выполните действия, которые вы хотите автоматизировать. Когда вы закончите, нажмите «Остановить запись».
• Если вы хотите получить доступ к вашему макросу, используйте сочетание клавиш, которое вы ему дали.

Конкретный пример макроса

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

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

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

• Нажмите на запись макроса. Давайте назовем его Format_Customer_Data и нажмите ОК.
• Чтобы получить желаемое форматирование, мы изменим имя первого столбца на Имя.
• Затем вставьте столбец рядом с A и назовите его Фамилия.
• Выделите все имена в первом столбце (которые по-прежнему включают имя и фамилию) и нажмите «Данные» на ленте навигации.
• Нажмите на текст в столбцы.

• Отметьте разделителем> Далее> Разделить пробелом> Далее> Готово. Смотрите скриншот ниже и как имена и фамилии были разделены вышеописанным процессом.

• Чтобы отформатировать поле «Сальдо», выделите суммы. Нажмите Главная> Условное форматирование> Выделить правила ячейки> Больше, чем> 0.

Это будет выделять клетки, которые имеют баланс из-за. Мы добавили несколько клиентов без остатка из-за дальнейшей иллюстрации форматирования.

• Вернитесь к разработчику и нажмите «Остановить запись».

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

Когда вы запускаете макрос, все форматирование выполняется за вас. Этот макрос, который мы только что создали, хранится в редакторе Visual Basic.

Пользователи могут запускать макросы несколькими различными способами. Читать Запустить макрос Узнать больше.

Узнайте больше о VBA

Чтобы узнать о VBA, нажмите Макрос на вкладке Разработчик. Найдите созданный вами файл и нажмите «Изменить».

Код, который вы видите в поле выше, создан при записи макроса.

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

Создать кнопку, чтобы начать работу с VBA

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

• Чтобы вставить элемент кнопки, перейдите на вкладку «Разработчик».
• Выберите ActiveX Command Button в раскрывающемся списке рядом с «Вставить» в разделе «Элементы управления».

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

• Чтобы прикрепить код, щелкните правой кнопкой мыши на кнопке и выберите «Свойства». Мы будем сохранять имя как CommandButton и заголовок для преобразования (это текст кнопки).

Добавить код для придания кнопке функциональности

Кодирование VBA не выполняется в интерфейсе Excel. Это делается в отдельной среде.

• Перейдите на вкладку «Разработчик» и убедитесь, что режим «Дизайн» активен.

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

• Глядя на код на скриншоте ниже, обратите внимание, что начало (Private Sub) и конец (End Sub) кода уже есть.

• Код ниже будет управлять процедурой конвертации валюты.

ActiveCell.Value = (ActiveCell * 1.28)

Наша цель в этом разделе — конвертировать валюту в нашей таблице. Сценарий выше отражает обменный курс от GBP к USD. Новое значение ячейки будет то, что в настоящее время там умножено на 1,28.

На приведенном ниже снимке экрана показано, как выглядит код в окне VBA после его вставки.

• Перейдите в «Файл» в верхней части навигации и нажмите «Закрыть» и «Вернуться в Microsoft Excel», чтобы вернуться в основной интерфейс Excel.

Это сработало?

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

• Введите любое число в таблицу и нажмите кнопку «Преобразовать». Если ценность вашего номера увеличивается примерно на четверть, это сработало.

Для этого примера я положил число 4 в клетку. После нажатия кнопки «Преобразовать» число изменилось на 5.12. Так как 4 раза 1,28 является 5,12, код был выполнен правильно.

Теперь, когда вы понимаете, как создать макрос или скрипт в Excel, вы можете использовать их для автоматизации множества действий в Excel.

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Источник: mexn.ru

Как записать макрос в Excel: инструкция с примерами

В этой статье расскажу, что такое макрос, как включить режим разработчика, записать макрос и назначить его на кнопку.

Здравствуйте, друзья! Думаю, многие из вас (как и я в свое время), кто работает в Microsoft Excel , задавались вопросом автоматизации некоторых задач. Предположим, вам необходимо по щелчку кнопки мгновенно перемещаться между листами книги или распечатать область на листе в определенном количестве копий. В этом и многом другом нам помогут макросы.

Макрос — это действие (или последовательность действий), записанное на языке программирования Visual Basic for Applications (VBA) , которое можно выполнить сколько угодно раз.

Для работы с макросами необходимо открыть специальное окно – редактор программ на VBA, встроенный в Microsoft Excel, который доступен при включенном режиме Разработчика. Чтобы включить режим разработчика, нужно перейти по пути: Файл – Параметры – Настроить ленту и поставить в правой части окна флажок Разработчик.

В ленте появится вкладка «Разработчик», в которой есть кнопка для запуска редактора Visual Basic.

Макрос можно записать двумя способами

1. Вручную в редакторе (для сложных макросов и процедур);
2. С помощью функции «Запись макроса», которая находится во вкладке Разработчик.

Для начала, разберем первый способ – запишем макрос вручную. Например, у нас есть код макроса для печати активного листа:

Sub Print_1()
ActiveSheet.UsedRange.PrintOut
End Sub

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

– Каждый макрос заканчивается оператором End Sub.

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

Макросы хранятся в модулях. В любой книге Excel мы можем создать любое количество модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна VBA Project в левом верхнем углу редактора

Чтобы создать модуль, нужно открыть во вкладке «Разработчик» редактор Visual Basic, в окне VBA Project щелкнуть правой кнопкой мыши и выбрать Insert – Module.

Появится вкладка «Modules», в которой находится наш модуль «Module1». Копируем код макроса и вставляем его в наш модуль.

Готово, макрос записан. Теперь нужно определиться с запуском макроса. Макрос можно запустить несколькими способами:

– Во вкладке «Разработчик» нажать кнопку «Макросы», в появившемся окне выбрать наш макрос и нажать «Выполнить»;

– С помощью горячих клавиш, сочетание которых можно установить в том же окне, нажав кнопку «Параметры»;

– Назначить макрос на кнопку, размещенную на листе. Этот способ рассмотрим более подробно.

Для начала, добавим кнопку на лист, который нужно распечатать. Для этого откроем вкладку «Вставка» и выберем Иллюстрации-Фигуры.

Выбираем любую фигуру (я выбрал прямоугольник), затем щелкаем левой кнопкой мыши на листе и растягиваем фигуру. Получилась кнопка, которую можно отформатировать (поменять цвет, вставить надпись и т.д.). Щелкаем по кнопке правой кнопкой мыши, выбираем «Назначить макрос», выбираем наш макрос Print1 из списка, жмем «Ок».

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

Второй способ: запишем макрос при помощи макрорекордера. Например, у нас есть книга с двумя листами (Лист1 и Лист2). Нам нужно, чтобы по нажатию кнопки на Листе1 мы переходили на Лист2. Открываем Лист1, создаем на нем кнопку. Переходим во вкладку «Разработчик» и жмем кнопку «Запись макроса».

Задаем имя макроса без пробелов (в моем случае это ПереходЛист2), по желанию задаем сочетание клавиш и жмем «Ок». Запись началась. Открываем Лист2 и жмем во вкладке «Разработчик» кнопку «Остановить запись». Готово, макрос записан. Чтобы посмотреть код, заходим в редактор Visual Basic.

Макрос записался в Module2.

Назначаем макрос на кнопку, которую мы предварительно создали на Листе1. Готово, теперь при нажатии на кнопку мы переместимся на Лист2.

После того, как макрос записан, необходимо сохранить нашу книгу с поддержкой макросов. Жмем Файл – Сохранить как, выбираем папку и в списке «Тип файла» выбираем «Книга excel с поддержкой макросов».

Источник: excel-sclad-pro.ru