Excel примеры макрос: Макросы для Excel с исходными кодами и описанием функций

Содержание

Макросы Excel — пользовательские формы

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

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

Создание пользовательской формы

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

  • Нажмите вкладку РАЗРАБОТЧИК на ленте.
  • Нажмите Visual Basic. Откроется окно Visual Basic для книги.
  • Нажмите Вставить,
  • Выберите UserForm из выпадающего списка.

Пользовательская форма появляется в правой части окна.

Понимание пользовательской формы

Разверните окно UserForm.xlsx – UserForm1.

Вы находитесь в режиме дизайна сейчас. Вы можете вставить элементы управления в пользовательскую форму и написать код для соответствующих действий. Элементы управления доступны в панели инструментов. Свойства UserForm находятся в окне свойств. UserForm1 (подпись UserForm) указывается в разделе «Формы» в проводнике проектов.

  • Измените заголовок UserForm на Project Report – Daily в окне свойств.
  • Измените имя пользовательской формы на ProjectReport.

Изменения отражаются в пользовательской форме, свойствах и проводнике проекта.

Элементы управления в панели инструментов

Пользовательская форма будет иметь разные компоненты. Когда вы нажимаете на любой из компонентов, вам либо будут предоставлены инструкции о том, что и как должна быть предоставлена ​​информация, либо вам будут предоставлены варианты (варианты) для выбора. Все это обеспечивается с помощью элементов управления ActiveX в панели инструментов пользовательской формы.

Excel предоставляет два типа элементов управления – элементы управления формы и элементы управления ActiveX. Вы должны понимать разницу между этими двумя типами элементов управления.

Элементы управления формой

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

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

Элементы управления ActiveX

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

У вас есть следующие элементы управления ActiveX в UserForm ToolBox –

  • Указатель
  • этикетка
  • Текстовое окно
  • Поле со списком
  • ListBox
  • CheckBox
  • OptionButton
  • Рамка
  • Кнопка-переключатель
  • CommandButton
  • TabStrip
  • MultiPage
  • Полоса прокрутки
  • в полях ввода
  • Образ

В дополнение к этим элементам управления Visual Basic предоставляет функцию MsgBox, которую можно использовать для отображения сообщений и / или запроса пользователя о действии.

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

этикетка

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

пример

Текстовое окно

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

пример

Список

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

  • Вставьте ListBox в пользовательскую форму.
  • Нажмите на список.
  • Введите ProjectCodes для Name в окне свойств ListBox.

Есть три типа списков –

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

  • Список выбора множественного выбора – Список списка множественного выбора включает либо один выбор, либо смежные (смежные) варианты.

  • Поле со списком расширенного выбора – Поле со списком расширенного выбора позволяет выбрать один, непрерывный и несмежный (или несвязанный) выбор.

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

Список выбора множественного выбора – Список списка множественного выбора включает либо один выбор, либо смежные (смежные) варианты.

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

Вы можете выбрать один из этих типов списков в окне свойств.

  • Щелкните правой кнопкой мыши на пользовательской форме.
  • Выберите View Code из выпадающего списка. Откроется окно кода UserForm.
  • Нажмите Инициализировать в правом верхнем углу окна кода.
  • Введите следующее в Private Sub UserForm_Initialize ().
ProjectCodes.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5")
  • Перейдите на вкладку «Выполнить» на ленте.
  • Выберите Run Sub / UserForm из выпадающего списка.

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

Поле со списком

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

  • Вставьте ComboBox в пользовательскую форму.
  • Нажмите ComboBox.
  • Введите ProjectCodes2 для имени в окне свойств ComboBox.
  • Щелкните правой кнопкой мыши на пользовательской форме.
  • Выберите View Code из выпадающего списка.
  • Откроется окно кода UserForm.

Введите следующее, как показано ниже.

ProjectCodes2.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5")
  • Перейдите на вкладку «Выполнить» на ленте.
  • Выберите Run Sub / UserForm из выпадающего списка.

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

Нажмите на нужный элемент, скажем, Project2016-5. Выбранная опция будет отображаться в поле со списком.

CheckBox

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

Флажок может иметь два состояния:

  • Выбрано (включено), помечено галочкой в ​​поле
  • Очищено (выключено), обозначено прозрачной рамкой

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

  • Смешанный, означающий комбинацию включенных и выключенных состояний, обозначенную черной точкой в ​​поле. Это будет отображаться, чтобы указать несколько вариантов выбора в комбинированном окне с флажками.

  • Установите флажки в пользовательской форме, как показано ниже.

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

Установите флажки в пользовательской форме, как показано ниже.

  • Перейдите на вкладку «Выполнить» на ленте.
  • Выберите Run Sub / UserForm из выпадающего списка.
  • Нажмите на поля для выбранных вами вариантов.

OptionButton

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

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

  • Выбран (включен), обозначен точкой в ​​круге
  • Сброс (выключен), обозначается пробелом

Рамка

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

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

Вставьте рамку с надписью «Выбор».

Вставьте две кнопки с надписями «Да» и «Нет» в элементе управления кадром. Опции Да и Нет являются взаимоисключающими.

  • Перейдите на вкладку «Выполнить» на ленте.
  • Выберите Run Sub / UserForm из выпадающего списка.
  • Нажмите на выбранный вами вариант.

Кнопка-переключатель

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

Вставьте кнопку переключения на пользовательскую форму, как показано ниже –

Перейдите на вкладку «Выполнить» на ленте.

Выберите Run Sub / UserForm из выпадающего списка. По умолчанию кнопка переключения будет в активированном состоянии.

Нажмите кнопку переключения. Кнопка переключения будет отключена.

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

CommandButton

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

Командная кнопка также называется кнопкой. Вставьте командную кнопку в пользовательскую форму, как показано ниже –

  • Щелкните правой кнопкой мыши на кнопке.
  • Введите следующий код в sub Commandbutton1_click ().
ProjectCodes2.DropDown
  • Перейдите на вкладку «Выполнить» на ленте.
  • Выберите Run Sub / UserForm из выпадающего списка.

Нажмите кнопку команды. Откроется выпадающий список со списком, так как это действие, которое вы написали в коде.

TabStrip

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

Полоса прокрутки

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

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

  • Щелкните правой кнопкой мыши по полосе прокрутки.
  • Выберите View Code из выпадающего списка. Откроется окно кода.
  • Добавьте следующую строку под sub ScrollBar1_Scroll ().
TextBox2.Text = "Scrolling Values"
  • Перейдите на вкладку «Выполнить» на ленте.
  • Выберите Run Sub / UserForm из выпадающего списка.

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

MsgBox ()

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

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

Отображается значок окна сообщения

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

  • Введите следующий код в ScrollBar1_scroll.
MsgBox "Select Ok or Cancel", vbOKCancel, "OK  - Cancel Message" 
MsgBox "It's an Error!", vbCritical, "Run time result" 
MsgBox "Why this value", vbQuestion, "Run time result" 
MsgBox "Value Been for a Long Time", vbInformation, "Run time result" 
MsgBox "Oh Is it so", vbExclamation, "Run time result"
  • Перейдите на вкладку «Выполнить» на ленте.
  • Выберите Run Sub / UserForm из выпадающего списка.
  • Перетащите поле прокрутки.

Вы получите следующие сообщения последовательно.

Разработка UserForm

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

Обратитесь к учебнику VBA в этой библиотеке учебников для примера UserForm.

Как защитить персональные данные клиентов

Сообщения о том, что очередная компания потеряла персональные данные клиентов, появляются с пугающей регулярностью. Только за неполный июль стало известно о трех крупных «сливах» — злоумышленники украли и выставили на продажу данные клиентов «Острова чистоты», «Буслика» и «Юркас». Что делать, чтобы ваша компания не стала жертвой злоумышленников? Как защитить персональные данные ваших клиентов? «Про бизнес» узнал об этом у директора по информационной безопасности ActiveCloud Антона Грецкого.

— Почему защита данных для компаний сейчас особенно актуальна?

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

— Кто в Беларуси контролирует эту сферу?

— 15 ноября 2021 года у нас появился Национальный центр по защите персональных данных. Это оперативно-аналитический центр при Президенте Республики Беларусь. Он и занимается регулированием, учетом и контролем подобных инцидентов.

— Почему бизнесу важно защищать информацию?

— Можно выделить 3 основные причины, почему этому надо уделить особое внимание:

1. Ответственность — в случае крупных инцидентов Национальный центр по защите персональных данных может приостановить ваш бизнес.

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

3. Репутационные риски. Не все можно посчитать в деньгах, но утеря данных также отражается на репутации бизнеса.

Директор по информационной безопасности ActiveCloud Антон Грецкий. Фото из личного архива героя.

— Какие примеры утечек данных вы можете привести и чем они грозят бизнесу?

— Компания оказывала услуги по сбору персональных данных для крупных медцентров. Допустим, вам нужно было записать к врачу, и вы заходили на сайт медцентра, выбирали врача, вносили свои данные через этот сервис. Полгода назад у них произошла утечка персональных данных клиентов. Началась проверка, им выдвинули n-е количество обвинений. Оказалось, что у сервиса не было ни аттестации, ни каких-либо законных мер по защите данных. В итоге Национальный центр защиты персональных данных остановил деятельность компании, ей выписаны штрафы, и ее ждут суды.

В июле 2023 года произошло сразу 3 крупных потери данных:

  1. У ЧТУП «ЗападХимТорг» (бренд «Остров чистоты») база данных «утекла» не в первый раз. В июле 2023 года стало известно, что в результате несанкционированного доступа к информационной системе произошла очередная утечка информации и злоумышленники выставили на продажу базу данных, в которой содержатся персональные данные более 730 тысяч пользователей ресурса. Национальный центр защиты персональных данных уже проводил внеплановую проверку ЧТУП «ЗападХимТорг» в связи с предыдущей утечкой данных. Однако, похоже, выводов компания не сделала, что привело к повторному инциденту.

  2. 10 июля 2023 года в результате несанкционированного доступа к информационной системе крупного белорусский производитель дверей «Юркас» также произошла утечка персональных данных. В сеть попали ФИО, адреса электронной почты, номера телефонов клиентов и сотрудников предприятия. Утечка затронула данные не более 5000 человек.

  3. 5 июля официальный Telegram-канал Национального центра защиты персональных данных сообщил о несанкционированном доступе к информационной системе ООО «ДПМ», в результате которого были получены персональные данные пользователей интернет-магазина buslik.by, который занимается реализацией детских товаров. На продажу выставили данные 220 тысяч пользователей.

— В чем заключается риск для пользователя, данные которого «утекли»?

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

Опять же — на вас могут взять кредит или оформить поручительство. Это в Беларуси для этого нужно прийти в банк. А в некоторых странах (допустим, Сербии, Албании) достаточно заполнить набор персональных данных какого-то человека, чтобы «повесить» на него кредит или оплату за какие-то услуги.

— Каких киберугроз нужно опасаться компаниям?

— Самая популярная киберугроза в отношении компании — это фишинг.

Фишинг (англ. phishing от fishing «рыбная ловля, выуживание») — вид интернет-мошенничества, целью которого является получение доступа к конфиденциальным данным пользователей — логинам и паролям. Это достигается путем проведения массовых рассылок электронных писем от имени популярных брендов, а также личных сообщений внутри различных сервисов, например, от имени банков или внутри социальных сетей. В письме часто содержится прямая ссылка на сайт, внешне неотличимый от настоящего, либо на сайт с редиректом.

Идея фишинга — заслать работнику компании (желательно далекому от IT —

бухгалтеру, экономисту, юристу) письмо такого содержания, которое он, скорее всего, откроет. Например, письмо с темой: «Сокращение премий за июль».

В письме будет приложение в виде Excel-файла, который наивная жертва откроет прочитать. А там лежит макрос — специально исполняемый код, который прописывается и что-то делает в вашем компьютере, пока вы работаете, — ворует трафик, копирует данные, предоставляет доступы.

Есть cross site script-мошенничество — когда вас перекидывает с правильного сайта на фальшивый. Бывают различного рода зловредные вирусы, которые могут попадать в компанию по почте с какими-то тестовыми версиями. Часто их доставляют в компанию сами работники — многие любят на работе что-то скачивать с непонятных сайтов, например, фильм на выходной.

Есть хакерские атаки, связанные с набором пароля. Они до сих пор эффективны, потому что многие все еще ставят пароли «чтобы не забыть», например 1−2−3−4−5−6 или дату своего рождения. Естественно, эти пароли очень быстро подбираются, даже взламывать ничего не надо.

— С чем связано то, что злоумышленники получают доступ к клиентской информации?

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

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

У многих компаний потеря данных происходит также из-за отсутствия или устаревания инструментария для защиты.

— Какие именно данные и почему становятся мишенью для мошенников? Как это предотвратить?

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

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

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

— Какие новые тренды киберугроз возникли в последнее время? К чему это приводит?

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

Конечно, и для удаленной работы компания может сделать безопасное соединение. Но это стоит денег. Вот и получается, что покупаются 1−2 лицензии или несколько каналов, на которых «сидят» 20 человек. Это медленно и неудобно, в итоге люди начинают подключаться по незащищенным каналам — и результат предсказуем. По большому счету, утеря данных в этом случае происходит из-за желания компании сэкономить. Конечно, разумный баланс должен быть. Но нужно осознавать, что будет, если вопросам безопасности не будут уделяться особое внимание.

— Антон, а как искусственный интеллект может помогать злоумышленникам?

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

— Так что бы вы посоветовали сделать компаниям для защиты данных?

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

Отчет после качественного аудита обычно содержит подробную «дорожную карту» того, что нужно сделать. Скорее всего, там будут задачи обеспечить информационную безопасность, конфиденциальность, целостность и доступность информации.

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

— В чем преимущества «облачных» решений для защиты?

— Когда вы пытается все это организовать на своих ресурсах, вы должны быть готовы к тому, что это достаточно затратно и по деньгам, и по времени. К тому же часть европейских брендов перекрыли продажи своих товаров в Беларусь и РФ. Итого проект по информационной безопасности в отдельно взятой компании может стоить около $ 300 тысяч, а по сроку развертывания занять около года.

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

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

Ответственность — вы и только вы будете виноваты, если что-то случится с вашими данными.

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

Учебное пособие

— Создание макросов для потоковой передачи данных — Excel Data Streamer

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

  • Статья

В этом руководстве вы узнаете, как:

  • Создавать модули для организации макросов
  • Объявление эталонных функций
  • Создать макрос для сохранения испытаний

Предпосылки

  • Данные датчика, ранее переданные в потоковом режиме на странице Data In Excel
  • Таблица пробных данных создана и структурирована
  • Вкладка ленты разработчика включена

Создание модулей в VBA

  1. Выберите Developer > Visual Basic , чтобы открыть Visual Basic для Excel
  2. Выберите Вставить > Модуль , чтобы добавить модуль для хранения макросов
  3. На вкладке Properties переименуйте Module1 по желанию. Это сохранит ваши ссылки.
  4. Повторите шаги 2 и 3, также переименовав по желанию. Это сохранит ваши активные макросы.

Объявить эталонные функции

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

  2. В окне кода начните с объявления Option Explicit .

  3. Ниже укажите имя листа, на котором хранится ваша таблица.

  4. Повторите шаг 3 для имени вашей таблицы.

     Явный параметр
 Public Const имя_листа = "Бэкенд"
 Общедоступная константа tblname = "Tbl_Backend"
 Сенсорное количество общедоступных констант = 2

  5. Используйте команду «Функция», чтобы создать сокращенную ссылку для вашей таблицы.

     Функция BT_tbl() как ListObject
        Установите BT_tbl = Листы (имя листа).ListObjects (имя таблицы)
Конечная функция

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

     Функция Live_CH(snum как целое число) как диапазон
        Установить Live_CH = BT_tbl.ListColumns("CH" и snum).DataBodyRange
Конечная функция

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

     Функция Trial_CH(snum как целое число, tnum как целое число) как диапазон
        Установить Trial_CH = BT_tbl.ListColumns("CH" и snum & "_T" & tnum).DataBodyRange
Конечная функция

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

Создать макрос для сохранения испытаний

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

  2. В окне кода начните с объявления имени пробного макроса сохранения, используя sub.vba

     Sub SaveTrial()
Конец сабвуфера

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

    .

     Sub SaveTrial()
        Dim tnum As Integer
        tnum = Right(ActiveSheet.Shapes(Application.Caller).Name,1)
Конец сабвуфера

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

     Sub SaveTrial()
        Dim Num как целое число
        Num = Right(ActiveSheet.Shapes(Application.Caller).Имя,1)
        References.BT_Ch2_Trial(Num).Value = References.BT_Ch2.Value
        References.BT_Ch3_Trial(Num).Value = References.BT_Ch3.Value
Конец сабвуфера

После написания этого макроса пользователь теперь может назначать макросы объектам.

  1. Перейдите в книгу Excel, на лист, содержащий вашу таблицу и именованные объекты.
  2. Выберите все пробные кнопки, удерживая SHIFT и выбирая каждый объект.
  3. Откройте контекстное меню этих объектов и выберите Назначить макрос…
  4. Выберите макрос SaveTrial .

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

Узнайте, как использовать макросы Excel для автоматизации утомительных задач

Быстрые ссылки

  • Что такое макрос?
  • Создание макроса: объяснение на примере
  • Использование макроса Excel
  • Заглянем внутрь: что заставляет макрос работать
    900 07 Используя наш пример, сделайте еще один шаг вперед. ..
  • Загрузите этот шаблон

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

Если вам интересно, что такое макросы или как их создать, нет проблем — мы проведем вас через весь процесс.

Примечание. Тот же процесс должен работать в большинстве версий Microsoft Office. Скриншоты могут немного отличаться.

Что такое макрос?

Макрос Microsoft Office (поскольку эта функция применима к нескольким приложениям MS Office) — это просто код Visual Basic для приложений (VBA), сохраненный внутри документа. Для сопоставимой аналогии подумайте о документе как о HTML, а о макросе как о Javascript. Во многом так же, как Javascript может манипулировать HTML на веб-странице, макрос может манипулировать документом.

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

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

Создание макроса: объяснение на примере

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

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

Чтобы создать макрос, выберите «Просмотр» > «Макросы» > «Запись макроса».

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

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

Есть несколько мест, которые указывают, что Excel находится в режиме записи. Один из них — просмотреть меню «Макрос» и отметить, что функция «Остановить запись» заменила параметр «Запись макроса».

Другой в правом нижнем углу. Значок «стоп» указывает, что он находится в режиме макросов, и нажатие здесь остановит запись (аналогично, когда не в режиме записи, этот значок будет кнопкой «Запись макроса», которую вы можете использовать вместо перехода в меню «Макросы»).

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

Далее применим соответствующие формулы (соответственно):

  • =СУММ(B2:K2)
  • =СРЗНАЧ(B2:K2)
  • =MIN(B2:K2)
  • =MAX(B2:K2)
  • =MEDIAN(B2:K2)

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

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

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

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

  • =СУММ(L2:L21)
  • =СРЗНАЧ(B2:K21) равно среднему значению всех значений.
  • =MIN(N2:N21)
  • =MAX(O2:O21)
  • =MEDIAN(B2:K21) *Рассчитано для всех данных по той же причине, что и выше.

 

Теперь, когда вычисления завершены, мы применим стиль и форматирование. Сначала примените общее числовое форматирование ко всем ячейкам, выбрав «Выбрать все» (либо Ctrl + A, либо щелкните ячейку между заголовками строки и столбца) и выберите значок «Стиль запятой» в главном меню.

Затем примените визуальное форматирование к заголовкам строк и столбцов:

  • Жирный.
  • По центру.
  • Цвет заливки фона.

И, наконец, примените стиль к итоговым значениям.

Когда все будет готово, вот как выглядит наш лист данных:

 

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

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

 

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

Для этого выделите все ячейки и удалите их.

Теперь, когда данные очищены (но макросы все еще включены в файл Excel), мы хотим сохранить файл как файл шаблона с поддержкой макросов (XLTM). Важно отметить, что если вы сохраните это как файл стандартного шаблона (XLTX), то из него можно будет запускать макросы , а не . Кроме того, вы можете сохранить файл как файл устаревшего шаблона (XLT), что позволит запускать макросы.

После сохранения файла в качестве шаблона закройте Excel.

 

Использование макроса Excel

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

  • Макросы могут быть вредоносными.
  • См. пункт выше.

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

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

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

Чтобы завершить импорт CSV-файла, вам может потребоваться установить несколько параметров, чтобы Excel правильно его интерпретировал (например, разделитель, наличие заголовков и т.  д.).

 

После импорта данных просто перейдите в меню «Макросы» (на вкладке «Вид») и выберите «Просмотреть макросы».

В появившемся диалоговом окне мы видим записанный выше макрос FormatData. Выберите его и нажмите «Выполнить».

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

 

 

Заглянем внутрь: что заставляет макрос работать

Как мы уже несколько раз упоминали, макрос управляется кодом Visual Basic для приложений (VBA). Когда вы «записываете» макрос, Excel фактически переводит все, что вы делаете, в соответствующие инструкции VBA. Проще говоря, вам не нужно писать код, потому что Excel пишет код за вас.

Чтобы просмотреть код, который запускает наш макрос, в диалоговом окне «Макросы» нажмите кнопку «Изменить».

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

 

Еще один шаг вперед в нашем примере…

Предположим, что наш исходный файл данных, data.csv, создается автоматическим процессом, который всегда сохраняет файл в одном и том же месте (например, C:\Data\data.csv всегда является самыми последними данными). Процесс открытия этого файла и его импорта также можно легко превратить в макрос:

  1. Откройте файл шаблона Excel, содержащий наш макрос «FormatData».
  2. Запишите новый макрос с именем «LoadData».
  3. При записи макроса импортируйте файл данных, как обычно.
  4. После импорта данных остановите запись макроса.