Как в excel сделать макрос: примеры и как сделать / Skillbox Media

Содержание

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

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

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

Допустим в ячейке A1 у нас будет число 2, в ячейке B1 тоже будет число 2 и далее разберём как в ячейку C1 получить сумму чисел с помощью макроса.

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

Будем использовать ее для второго примера, для второго макроса, переименуем ее, кнопка два.

Для написания кода макроса перейдём в редактор Visual Basic, добавим модуль напишем первую подпрограмму test1.

Объявим несколько переменных для значений ячеек — a, b тип Integer, цельно численное значение.

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

a = cells(1,1)

.

Всплывающая подсказка показывает, что это у нас row – строка, а это у нас колонка – столбец, допишем value — значение.

Для пометки, в комментарий напишем, что у нас в каком порядке идет – row, col, на русском напишем — строка, столбец.

Для переменной

b = Cells(1 , 2). Value

– первая строка, второй столбец.

Можно объявить здесь выше переменную C, далее пишем

c = a + b

.

Выведем результат —

Cells(1 , 3). Value

– первая строка, третий столбец равно c.


Sub test1()
Dim a, b As Integer

a = Cells(1, 1).Value ' row, col
b = Cells(1, 2). Value ' row, col
c = a + b
Cells(1, 3).Value = c ' row, col

End Sub

Вот теперь мы присвоим для первой кнопки макрос test1 и посмотрим, что получится.

Что же нам сделать, если у нас нужно будет данные получать с разных листов или передавать полученный результат в другой лист?

Для примера скопирую подпрограмму test1, вставлю ниже и переименую в test2.


Sub test2()
Dim a, b As Integer

a = Sheets("Лист1").Cells(1,1).Value
b = Sheets("Лист1").Cells(1,2).Value
c = a + b

Sheets("Лист2").Cells(1,3).Value = c

End Sub

Для доступа к ячейкам на других листах перед ячейкой Cells укажем имя листа, напишем Sheets в круглых скобках имя листа в двойных кавычках.

В скобочках мы пишем, что данные мы берём лист один, если кнопка и данные на том же листе, получаемую информацию можно так не оформлять.

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

Теперь назначим макрос test2 для Кнопки2, давайте изменю значения ячеек, два-три для примера. При нажатии на первую кнопку результат равен пяти, при нажатии на вторую кнопку на Лист2 тоже результат равен пяти.

Если Вам не нравится вариант записи Cells(1,1) и сложно запомнить где что – где строки, а где столбцы – Вы можете обращаться к ячейкам по их именам A1, B1, C1 через Range – это диапазон или адрес ячейки.

Изменим Cells на Range, в круглых скобках в двойных кавычках вам нужно будет указать имя ячейки на английском А1.


Sub test2()
Dim a, b As Integer

a = Sheets("Лист1").Range("A1").Value
b = Sheets("Лист1").Range("B1").Value
c = a + b

Sheets("Лист2").Range("C1").Value = c

End Sub

Соответственно здесь у нас будет Range("B1"), ну и здесь у нас будет Range("C1"), результат не изменится, запустим test2 и проверим.

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

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

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

Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!

vba — Как автоматически при открытии книги запустить макрос в excel?



Как автоматически при открытии книги запустить макрос в excel?

  • vba
  • excel






1

Вставляете это в код:

Private Sub Workbook_Open() 
'Вместо EntryPoint вставьте название Вашего главного метода'
    Call EntryPoint  
End Sub

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

Код для автозагрузки при открытии Word:

Private Sub Document_Open()
'Вместо EntryPoint вставьте название Вашего главного метода'
     Call EntryPoint 
End Sub

Добавлю к ответу igumnov.

Код необходимо вставить в модуль ЭтаКнига (Для Word — ThisDocument)

Alt+F11 — открываем проект VBA.
Слева — окно проекта, модуль находится там, в папке Microsoft Excel Objects

Исполняемый макрос записывается в общий модуль (в папке Modules).

Если модуля нет, его нужно создать: в редакторе VBA вкладка Insert-Module

Если окна проекта нет, открыть — меню Viev-Project Explorer (Ctrl+R)

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

Поменять уровень безопасности вручную:

Для Excel-2003: меню Сервис-Макрос-Безопасность

Для Excel-2010 глубже: закладка ленты Файл-Параметры-Центр_управления_безопасностью-Параметры_центра_управления_безопасностью-Параметры_макросов

Программно менять уровень безопасности можно, но перед этим нужно установить на компьютер скрипт, который «умеет» менять уровень безопасности. Такие программы есть в свободном доступе.







Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации



Почта

Необходима, но никому не показывается




By clicking “Отправить ответ”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.


Как запустить макрос в Excel

Хотя запустить макрос в Excel несложно, существует множество способов его запуска. Некоторые способы предназначены для упрощения использования макросов, в то время как другие способы могут полностью изменить способ взаимодействия пользователя с вашей книгой. В этой статье мы рассмотрим некоторые очевидные способы запуска макросов, такие как элементы управления «Список макросов» и «Кнопка», а также некоторые более неясные способы, такие как редактор VB и события.

Первое: убедитесь, что макросы включены

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

Если вы не можете запускать макросы, попробуйте выполнить следующие инструкции:

  • Сохранить книгу
  • Закройте книгу, а затем снова откройте ее
  • Когда рабочая книга загрузится, вы увидите сообщение Security Warning (на фото ниже). Щелкните Включить содержимое .

Запуск макроса из списка макросов

Доступ к списку макросов можно получить на вкладке «Вид». Чтобы просмотреть этот список:

  • Выберите Вид вкладка
  • Нажмите кнопку с надписью Макросы , чтобы открыть список макросов
  • Выберите из списка макрос, который вы хотите запустить, затем нажмите кнопку Выполнить

Вы также можете отобразить список макросов в любое время, нажав ALT+F8 .

Запуск макроса с помощью сочетания клавиш

Вы можете назначить сочетание клавиш для часто используемого макроса в виде CTRL+<буква> или CTRL+SHIFT+<буква>. Для этого:

  • Вызов списка макросов ( View > Macros , или ALT+F8)
  • Выберите макрос, к которому вы хотите применить ярлык
  • Щелкните Параметры… , чтобы открыть поддиалоговое окно Параметры макроса
  • В текстовом поле под Горячая клавиша введите одну букву и нажмите OK . Если вы удерживаете клавишу SHIFT при вводе буквы, метка рядом с полем будет отображать SHIFT как часть ярлыка

ПРИМЕЧАНИЕ : настоятельно рекомендуется использовать SHIFT при создании сочетания клавиш! Excel использует для себя сочетания клавиш CTRL+, например. CTRL+C для копирования или CTRL+V для сохранения. Если вы создаете ярлык макроса, используя эти или другие ярлыки Excel, вы перезапишете ярлык Excel, пока не переназначите ярлык макроса.

Запуск макроса из редактора VB

Макросы также можно запускать из VB Editor. Редактор VB позволяет просматривать код макроса и вносить любые необходимые изменения перед его запуском.

Чтобы запустить макрос в редакторе VB:

  • Откройте редактор VB (вкладка «Разработчик» > Visual Basic или ALT+F11)
  • В окне Project дважды щелкните модуль, содержащий макрос, который вы хотите протестировать
  • В окне кода модуля поместите курсор в любое место кода макроса между «Sub» и «End Sub»
  • Нажмите кнопку Run на панели инструментов или нажмите сочетание клавиш F5

Запуск макроса с помощью кнопки или фигуры

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

Информацию о том, как создать интерактивную кнопку или фигуру, можно найти здесь: Добавление кнопки и назначение макроса в Excel.

Автоматический запуск макроса с использованием событий в VBA

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

Чтобы написать код для события, вам нужно использовать VB Editor. Например, чтобы просмотреть события для книги:

  • Открыть редактор VB (ALT+F11)
  • Дважды щелкните объект ThisWorkbook в редакторе VB 9.0020 Окно проекта
  • Выберите «Рабочая книга» в раскрывающемся списке в верхнем левом углу окна кода
  • Щелкните раскрывающийся список справа, чтобы просмотреть список событий

Ниже приведен небольшой, но полезный пример событий Excel, из которых можно запускать макросы.

Workbook_Open()

Событие Workbook_Open() возникает при открытии книги. Если вы получаете предупреждение о безопасности после открытия книги, это событие срабатывает после нажатия «Включить содержимое».

 Частная подпрограмма Workbook_Open()

    MsgBox "Книга открыта!"

Конец суб

Workbook_BeforeClose (Отмена как логическое значение)

Workbook_BeforeClose() срабатывает, когда пользователь пытается закрыть книгу. Это происходит до того, как будут выполнены какие-либо проверки, чтобы увидеть, нужно ли сохранять книгу.

Для параметра Cancel можно установить значение True, чтобы остановить закрытие рабочей книги.

 Private Sub Workbook_BeforeClose (Отмена как логическое значение)

    Если MsgBox("Вы уверены?", vbYesNo + vbQuestion, "Закрыть") = vbNo Тогда
        
        Отмена = Истина
        
    Конец, если

Конец суб

Worksheet_Change (ByVal Target As Range)

Worksheet_Change() срабатывает при изменении значения ячейки — будь то макрос, операция копирования/вставки или внешняя ссылка. Однако он не срабатывает, когда значение пересчитывается по формуле.

Параметр Target представляет ячейки, значение которых изменилось.

Если вы измените значение других ячеек внутри этого события, событие сработает снова. Это может вызвать бесконечный цикл. Если вам нужно изменить значения ячеек без запуска этого события, рассмотрите возможность установки Application.EnableEvents Сначала от до False , а затем снова установите значение True в конце процедуры обработки события.

 Private Sub Worksheet_Change (ByVal Target As Range)

    MsgBox "Ячейки изменены: " & Target.Address
    
    Приложение.EnableEvents = Ложь
    Диапазон ("A2"). Значение = Диапазон ("A2"). Значение + Target.Cells.Count
    Приложение.EnableEvents = Истина

Конец суб

Worksheet_SelectionChange (Цель ByVal как диапазон)

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

Вы также можете вызвать это событие с помощью кода, например, «Диапазон («A1»). Выберите». Как и в случае с Worksheet_Change(), вы должны быть осторожны при выборе других ячеек внутри этого события, так как вы можете вызвать бесконечный цикл. Используйте Application.EnableEvents.

 Private Sub Worksheet_SelectionChange (Цель ByVal как диапазон)
    
    Если Target.Address = "$A$1" Тогда
        
        MsgBox "Курсор в исходном положении."
        
    Конец, если
    
Конец суб

Остановка работающего макроса

Работающий макрос можно прервать, нажав ESC или CTRL+BREAK . По умолчанию прерванный макрос показывает следующий диалог:

Нажатие Конец остановит макрос, а Продолжить возобновит его. Нажатие Debug откроет макрос в редакторе VB и сосредоточит внимание на строке кода, выполнение которой было приостановлено. (В редакторе VB вы можете остановить или возобновить выполнение макроса с помощью кнопок «Выполнить» или «Конец» на панели инструментов.)

Приложение.EnableCancelKey

Вы можете отключить возможность остановки макроса с помощью ESC или CTRL+BREAK, установив свойство Application.EnableCancelKey . Это свойство имеет три возможных значения:

.

  • xlInterrupt — это значение по умолчанию, которое заставляет Excel показывать диалог выше
  • xlDisabled — убирает возможность остановки запущенного макроса
  • xlErrorHandler — при попытке прерывания выдается ошибка, которую можно обработать в коде

Всякий раз, когда выполнение кода останавливается, Excel всегда сбрасывает значение Application.EnableCancelKey обратно на xlInterrupt .

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

 Sub UpdateBaseData (ByVal NewData As Range, ByVal Target As Range)
    
    Приложение.EnableCancelKey = кслдисаблед
    
    Target.Worksheet.Unprotect "MyPassword"
    Цель NewData.Copy
    Target.Worksheet.Protect "MyPassword"
    
    Приложение.EnableCancelKey = xlInterrupt
    
Конец суб

Принудительное закрытие Excel с помощью диспетчера задач Windows

Если макрос «завис» или Excel стал слишком занят, чтобы подтвердить попытку прерывания, вам может потребоваться принудительно закрыть сам Excel с помощью Диспетчер задач Windows . (ПРИМЕЧАНИЕ: если вы сделаете это, вы можете потерять всю несохраненную работу, и вам придется полагаться на версию книги с автоматическим восстановлением.)

  • Откройте Диспетчер задач напрямую, используя CTRL+SHIFT+ESC
  • Выберите вкладку « Процессы »
  • Разверните элемент «Microsoft Excel», чтобы отобразить все книги
  • Выберите книгу, которую хотите закрыть, затем нажмите Завершить задачу в правом нижнем углу

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

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

Уровень навыков: Средний

Видеоурок

Смотрите на YouTube и подписывайтесь на наш канал заново учусь.

Создание удобных кнопок макросов в электронных таблицах

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

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

По этой причине Кристен, участница нашего курса VBA Pro, любит добавлять кнопки в свои электронные таблицы.

Щелкните здесь, чтобы послушать историю Кристен об изучении VBA

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

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

Итак, давайте посмотрим, как создать кнопку и назначить ей макрос.

3 типа макрокнопок для рабочих листов

В этом посте я объясню три различных типа кнопок, которые вы можете разместить на рабочем листе.

  1. Формы
  2. Кнопки управления формой
  3. Кнопки управления ActiveX

Начнем с моего любимого…

1. Использование фигур для создания макрокнопок

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

Ниже мы создадим кнопку на рабочем листе и назначим ей макрос. Вот шаги для создания кнопки макроса:

1. Нарисуйте фигуру на листе (вкладка «Вставка» > раскрывающийся список «Фигуры» > «Прямоугольник»).

2. Добавьте текст к фигуре (щелкните правой кнопкой мыши > Изменить текст | или дважды щелкните фигуру).

3. Назначить макрос (Щелкните правой кнопкой мыши границу фигуры > Назначить макрос…)

4. Выберите макрос из списка. Важно отметить, что макрос должен быть выбран из списка This Workbook , чтобы при совместном использовании книги с другими пользователями макрос также был доступен.

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

5. Нажмите OK .

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

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

Изменение кнопки

Если вам нужно выбрать фигуру, чтобы изменить ее внешний вид или переместить, удерживайте клавишу Ctrl , щелкая фигуру. Это предотвратит запуск макроса и позволит вам изменить форму. Excel предлагает множество параметров на вкладке Format для изменения стиля формы, эффектов формы, цвета или размера шрифта и т. д.

Предотвращение изменения размера кнопки при изменении ячейки

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

Сначала щелкните правой кнопкой мыши на кнопке и выберите Размер и свойства…

Откроется панель Форма формы . Есть варианты на выбор в Свойства , в зависимости от того, хотите ли вы, чтобы кнопка перемещалась или изменяла размер при внесении изменений в ваш лист.

2. Использование элементов управления формы для создания кнопок макросов

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

Разница между элементами управления и формами заключается в стиле.

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

Мы также не можем изменить цвет кнопки. Единственные параметры форматирования относятся к типу, размеру и цвету шрифта .

Вставьте кнопку управления формой

Для добавьте кнопку управления формой , просто перейдите на вкладку Разработчик и нажмите кнопку Вставить . В раскрывающемся списке представлены параметры Form Controls .

3. Использование элементов управления ActiveX для кнопок макросов

Последний вариант — кнопки управления ActiveX.

Кнопки управления ActiveX имеют больше параметров форматирования, и вы можете изменить цвет кнопок.

Они также требуют, чтобы вы добавили макрос события в модуль листа, который будет запускаться при нажатии кнопки. Это можно сделать, щелкнув правой кнопкой мыши кнопку и выбрав View Code .

Вы можете добавить другие триггеры событий, такие как двойной щелчок. Это может выполнять другое действие/макрос при двойном щелчке кнопки.

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

Размещение кнопок на ленте или панели инструментов

Другой вариант — разместить кнопки макросов на любой из вкладок ленты Excel или панели быстрого доступа .

Это видео № 3 в моей серии из 4 видео о Личной книге макросов. Этот метод лучше всего подходит для макросов, которые вы будете запускать только на своем компьютере, поскольку настройки кнопок ленты не переносятся вместе с книгой.

Если вы хотите, чтобы кнопки ленты перемещались вместе с книгой, вы можете использовать код XML для настройки ленты. В будущем я напишу об этом статью, и я подробно рассказываю о ней в своем курсе VBA Pro, а также о том, как создавать надстройки.

Учебники по теме

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

  • Как создать кнопки макроса VBA для фильтров в Excel
  • Кнопки макросов для добавления полей в сводные таблицы
  • Как добавить всплывающее окно сообщения «Да Нет» в макрос перед его запуском
  • Как включить вкладку «Разработчик» в Excel

Заключение

Моя общая рекомендация — не усложнять и придерживаться форм для кнопок макросов.