Редактор Visual Basic

Редактор Visual Basic дозволяет записывать, сохранять и видоизменять программные модули, делать и отлаживать процедуры.

В среду редактора VBA можно перейти

• командой Сервис-Макрос-Редактор Visual Basic,
• нажав клавишу на панели инструментов Visual Basic
• при помощи многофункциональные кнопкой Alt+F11.

Возврат в рабочую книжку MS Excel из редактора VBA производится

• командой Microsoft Excel — крайней командой меню Вид,
• нажатием на клавишу обычной панели инструментов Visual Basic
• кнопками Alt+F11 либо Alt+Q.

• Документ MS Excel представляет собой рабочую книжку, содержащую рабочие листы с данными разных типов и формулами, листы диаграмм и программки для обработки данных.
• Программные составляющие документа (модули, процедуры, формы) соединяются воединыжды в проект, который сохраняется на диске совместно с документом.
• Проект на VBA недозволено сделать независимо от документа. Каждой рабочей книжке соответствует проект с именованием, состоящим из 2-ух частей: VBAProject и наименования книжки в скобках. Такое имя присваивается проекту по дефлоту

Окна редактора VB

Project Window (окно проекта)

Структура проекта видна в окне проекта редактора Visual Basic ( рис. 2.1). Основное назначение окна проекта — обеспечение навигации в проекте.

Структура проекта имеет вид дерева. Элементы структуры могут быть свернуты либо расширены нажатием на пиктограммы «+» либо «-«, расположенные слева от папок, составляющих проект.

Показать окно проекта можно с помощью:

• нажатия на клавишу панели инструментов пиктографического меню
• команды Project Explorer меню View
• кнопкой Ctrl+R.

Сначало проект содержит лишь одну папку с объектами документа — Microsoft Excel Objects.

По мере разработки пользовательского приложения добавляются

• папка Формы (Forms), содержащая строчки для каждой формы, сделанной юзером . Это диалоговые окна юзера — объекты Userform.
• папка Модули (Modules) со обычными пользовательскими модулями, не связанными с определенными объектами либо формами.
• папка Модули класса (Classes) с модулями, определяющими пользовательские классы.
• папка Ссылки (References) со ссылками на остальные проекты.

Объекты, надлежащие рабочей книжке, ее листам и пользовательским формам, состоят из 2-ух компонент — видимая часть (к примеру, конкретно рабочий лист ) и кодовая часть (процедурный лист).

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

Переход на процедурный лист хоть какого объекта проекта делается последующими методами:

• двойным щелчком на избранный объект проекта
• нажатием клавиши F7 на выделенный объект проекта
• выбором команды Code из меню View либо из контекстного меню для выделенного объекта проекта.

Обычные модули содержат:

• процедуры общего типа,
• процедуры-функции, разработанные юзером,
• процедуры, записанные макрорекордером .

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

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

Процедура — это последовательность установок (операторов языка), начинающаяся с оператора Sub и заканчивающаяся оператором End Sub.

Все операторы, которые заключены меж этими 2-мя операторами, составляют тело процедуры .

Опосля записи оператора начала процедуры вручную и нажатия клавиши Enter конец процедуры записывается автоматом.

Для вставки процедуры установите точку вставки в тело модуля и сделайте команду Procedure (Процедура) из меню Insert (Вставка).

• Всякую функцию, расположенную в обычном модуле, можно запускать из диалогового окна Макрос, содержащего список доступных для пуска процедур (см. «Внедрение макрорекордера. Абсолютные и относительные ссылки»рис. 1.5).
• Процедуры, расположенные на процедурных листах, связанных с объектами рабочей книжки либо пользовательскими формами, не доступны в этом окне.

Properties Window (Окно параметров)

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

Если выделен обычный модуль либо проект ( рис. 2.2), то высвечивается его единственное свойство — Name (имя). Если же выделен, к примеру, объект Userform, то в окне параметров показана таблица с списком его параметров.

Изменение характеристики Name приводит к изменению имени объекта, под которым этот объект известен всем процедурам проекта.

Характеристики объектов можно изменять в режиме конструктора ( Design time ) либо в режиме выполнения процедуры ( Run time ). Некие характеристики объектов можно изменять в любом режиме, а отдельные характеристики подлежат изменению лишь в режиме конструктора либо лишь в режиме выполнения процедуры. Если характеристики объектов меняются в режиме выполнения процедуры, то они никогда не заметны в окне параметров объекта.

Code (окно программки)

Окно раскрывается при выполнении команды Code (Программка) из меню View либо при нажатии клавиши F7. Можно надавить клавишу View Code ( рис. 2.2) либо создать двойной щелчок на имени модуля, объекта рабочей книжки либо формы.

Меню и панели инструментов Visual Basic

При переходе в редактор VB изменяется меню и возникает обычная панель инструментов Visual Basic.

Пиктограммы отладки программ размещены на отдельной панели инструментов, которая высвечивается командой View-Toolbars-Debug.

Команды vba excel справочник

Немногие знают, что 1-ая версия пользующегося популярностью продукта Microsoft Excel возникла в 1985 году. С того времени он пережил несколько модификаций и нужен у миллионов юзеров по всему миру. При всем этом почти все работают только с малой толикой способностей этого табличного микропроцессора и даже не догадываются, как им могло бы облегчить жизнь умение программирования в Excel.

Что такое VBA

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

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

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

Объекты, коллекции, характеристики и способы

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

Основным из их является Application, соответственный самой программке Excel. Потом следуют Workbooks, Worksheets, также Range. К примеру, для воззвания к ячейке A1 на определенном листе следует указать путь с учетом иерархии.

Что касается понятия «коллекция», то это группа объектов такого же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

Последующее понятие — характеристики. Они являются нужной чертой хоть какого объекта. К примеру, для Range — это Value либо Formula.

Способы — это команды, показывающие, что требуется создать. При написании кода в VBA их нужно отделять от объекта точкой. К примеру, как будет показано в предстоящем, весьма нередко при программировании в «Эксель» употребляют команду Cells(1,1).Select. Она значит, что нужно избрать ячейку с координатами (1,1) т.е. A1.

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

Как начать

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

Потом нужно перейти в приложение VB, для чего же довольно пользоваться композицией кнопок «Alt» и «F11». Дальше:

• в строке меню, расположенном в высшей части окна, жмут на иконку рядом с иконкой Excel;
• выбирают команду Mudule;
• сохраняют, нажав на иконку с изображением floppy disk;
• пишут, выскажемся так, рисунок кода.

Он смотрится последующим образом:

Направьте внимание, что строчка «’Наш код» будет выделена остальным цветом (зеленоватым). Причина в апострофе, поставленном сначала строчки, который обозначает, что дальше следует комментарий.

Сейчас вы сможете написать хоть какой код и сделать себе новейший инструмент в VBA Excel (примеры программ см. дальше). Естественно, тем, кто знаком с азами Visual Basic, будет намного проще. Но даже те, кто их не имеет, при желании сумеют акклиматизироваться довольно стремительно.

Макросы в Excel

За таковым заглавием скрываются программки, написанные на языке Visual Basic for Application. Таковым образом, программирование в Excel — это создание макросов с необходимым кодом. Благодаря данной для нас способности табличный микропроцессор Microsoft саморазвивается, подстраиваясь под требования определенного юзера. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению определенных примеров программ VBA Excel. Идеальнее всего начать с самых простых кодов.

Пример 1

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

• открывают вкладку «Вид»;
• перебегают на пиктограмму «Макросы»;
• нажимают на «Запись макроса»;
• заполняют открывшуюся форму.

Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание кнопок» вставляют, к примеру, hh (это означает, что запустить программу можно будет блиц-командой «Ctrl+h»). Жмут Enter.

Сейчас, когда уже запущена запись макроса, создают копирование содержимого какой-нибудь ячейки в другую. Ворачиваются на начальную пиктограмму. Жмут на «Запись макроса». Это действие значит окончание программы.

• вновь перебегают на строчку «Макросы»;
• в перечне выбирают «Макрос 1»;
• жмут «Выполнить» (то же действие запускается начатием сочетания кнопок «Ctrl+hh»).

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

Имеет смысл узреть, как смотрится код. Для этого вновь перебегают на строчку «Макросы» и жмут «Поменять» либо «Войти». В итоге оказываются в среде VBA. Фактически, сам код макроса находится меж строчками Sub Макрос1() и End Sub.

Если копирование было выполнено, к примеру, из ячейки А1 в ячейку C1, то одна из строк кода будет смотреться, как Range(“C1”).Select. В переводе это смотрится, как «Спектр(“C1”).Выделить», другими словами производит переход в VBA Excel, в ячейку С1.

Активную часть кода завершает команда ActiveSheet.Paste. Она значит запись содержания выделенной ячейки (в этом случае А1) в выделенную ячейку С1.

Пример 2

Циклы VBA помогают создавать разные макросы в Excel.

Циклы VBA помогают создавать разные макросы. Представим, что имеется функция y=x + x 2 + 3x 3 – cos(x). Требуется сделать макрос для получения ее графика. Создать это можно лишь, используя циклы VBA.

За изначальное и конечное значение аргумента функции берут x1=0 и x2=10. Не считая того, нужно ввести константу — значение для шага конфигурации аргумента и изначальное значение для счетчика.

Все примеры макросов VBA Excel создаются по той же процедуре, которая представлена выше. В данном определенном случае код смотрится, как:

Do While x1 0 Then Cells(1, 1).Value = 1

Open Notes

Обо всём, что мне любопытно

Полезные команды VBA

Пополняемый перечень нужных отрывков кода VBA для выполнения нередко нужных действий в MS Excel.

Служебные команды для убыстрения скорости выполнения макроса:

‘Отключение отображения выполняемых действий Application.ScreenUpdating = False ‘Предотвращение возникновения предупреждающих сообщений Application.DisplayAlerts = False ‘Предотвращение возникновения предупреждения о обновлении связей данных Application.AskToUpdateLinks = False ‘Чистка буфера обмена Application.CutCopyMode = False

Проверка имени юзера, запустившего макрос:

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

If Application.UserName = «Имя_автора_документа» Then .

Поиск крайней строчки таблицы:

Set myWSheet = ThisWorkbook.Sheets(«Имя_листа») With myWSheet ‘Определение индекса крайней строчки таблицы lastRow = .Cells(Rows.Count, 1).End(xlUp).Row ‘Определение значения в ячейки крайней строке столбца A lastARow = .Range(«A» & lastRow).Value End With

Подмена формулы на значение:

Добавление новейшего листа с именованием опосля всех имеющихся:

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = «Имя_листа»

Как выяснить крайний денек предшествующего месяца:

LastMonthDay = DateAdd(«d», -1, DateSerial(Year(dtDate), Month(dtDate), 1))

Определение оставшихся дней месяца:

dToEndOfMonth = DateDiff(«d», dFrom, DateAdd(«d», -1, _ DateSerial(Year(dFrom), Month(dFrom) + 1, 1)))

Номер текущего денька в недельке (воскресенье — 1-ый денек):

DayOfWeek = DatePart(«w», dToday)

Создание новейшего файла из текущего:

pathNewBook = «C:Temp» nameNewBook = «Имя_нового_файла.xls» Workbooks.Add ActiveWorkbook.SaveAs Filename:=pathNewBook & nameNewBook ActiveWorkbook.Close True

Сохранить текущий файл в формате CSV

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

ActiveWorkbook.SaveAs FileName:=»Name.csv», FileFormat:=xlCSV, _ CreateBackup:=False, Local:=True ActiveWorkbook.Saved = True ActiveWorkbook.Close True

Копирование данных из 1-го файла в иной:

wbPath = «C:Temp» wbName = «Имя_файла_откуда_копируем.xls» Workbooks.Open (wbPath & wbName) Set WB = Workbooks(wbName) WB.Sheets(«Лист 1»).Range(«A1:С10»).Copy Sheet(«Лист_в_текущем_файле»).Range(«A2»).PasteSpecial xlPasteValues

Чтоб открыть файл лишь для чтения, следует употреблять:

Workbooks.Open (Filename:=wbPath & wbName, ReadOnly:=True)

Предотвращение ошибки при неудачном поиске значения в таблице:

Set DateRowObj = WB.Sheets(«Имя_листа»).Range(«A:A»)._ Find(What:=dtToAsDate, LookIn:=xlFormulas) If (DateRowObj Is Nothing) Then WB.Close False MsgBox «Данные не найдены.» Else DateRow = DateRowObj.Row ‘Номер строчки с разыскиваемым значением End If

Как получить имя активной книжки Excel без его расширения (без .xls или без .xlsx):

wbName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, «.») — 1)

Проверка существования файла:

fPath = «C:Temp» fName = «Файл.txt» If Dir(fPath & fName) = «» Then MsgBox «Файл не найден:» & Chr(13) & fPath & fName Exit Sub End If

Клавиша, скрывающая/разворачивающая часть таблицы:

Private Sub tbV ).H >

Обновление сводной таблицы:

currPath = ThisWorkbook.Path currWBName = ThisWorkbook.Name ListName.PivotTables(«СводнаяТаблица1»).ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=currPath & «[» & _ currWBName & «]Лист1!R1C1:R10C5»)

Воззвание к элементам Frame:

Подмена #ДЕЛ/0! в спектре:

Selection.Replace What:=»#DIV/0!», Replacement:=»», LookAt:=xlPart,_ SearchOrder:=xlByRows, MatchCase:=False,_ SearchFormat:=False, ReplaceFormat:=False

Количество строк в отфильтрованной таблице:

Стремительно убрать излишние пробелы в спектре:

Программно снять защиту с листа:

Работа с спектром

Помножить спектр на число:

ThisWorkbook.Sheets(1).Range(«A1:A10») = _ ThisWorkbook.Sheets(1).Evaluate(«A1:A10» & «*80»)

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

Сортировка избранного столбца в сводной таблице

Col = Selection.Column ‘Номер избранного столбца ColMax = ActiveSheet.PivotTables(«СводнаяТаблица»).PivotColumnAxis. _ PivotLines.Count If Col — 1

Поиск файлов в папке

Dim strDirPath, strMaskSearch, strFileName as String strDirPath = «C:/test/» ‘Папка поиска strMaskSearch = «*.xls*» ‘Маска поиска ‘Получаем 1-ый файл соответственный шаблону strFileName = Dir(strDirPath & strMaskSearch) Do While strFileName <> «» ‘До того времени пока файлы «не завершатся» MsgBox strFileName strFileName = Dir ‘Последующий файл Loop

Окно сообщения в VBA Excel — MsgBox

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

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

Синтаксис

Параметр Описание

• Запрос — неотклонимый параметр. Строчка, отображаемая в виде сообщения в диалоговом окне. Наибольшая длина приглашения — около 1024 знаков. Если сообщение распространяется наиболее чем на одну строчку, то строчки могут быть разбиты с внедрением знака возврата каретки (Chr (13)) либо знака перевода строчки (Chr (10)) меж каждой строчкой.
• Клавиши — необязательный параметр. Числовое выражение, которое показывает тип отображаемых клавиш, стиль значка для использования, идентификатор клавиши по дефлоту и модальность окна сообщения. Если бросить пустым, значение по дефлоту для клавиш равно 0.
• Заголовок — необязательный параметр. Строковое выражение отображается в строке заголовка диалогового окна. Если заголовок оставлен пустым, имя приложения помещается в строчку заголовка.
• Файл справки — необязательный параметр. Выражение String, которое идентифицирует файл справки, который будет употребляться для предоставления контекстно-зависимой справки для диалогового окна.
• Контекст — необязательный параметр. Числовое выражение, которое идентифицирует номер контекста справки, присвоенный создателю справки, в соответственный раздел справки. Если контекст предоставлен, также должен быть предоставлен справочный файл.

Параметр Buttons может принимать хоть какое из последующих значений:

• 0 vbOKOnly — показывает лишь клавишу OK.
• 1 vbOKCancel — показывает клавиши «ОК» и «Отмена».
• 2 vbAbortRetryIgnore — показывает клавиши отмены, повтора и игнорирования.
• 3 vbYesNoCancel — показывает клавиши «Да», «Нет» и «Отмена».
• 4 vbYesNo — показывает клавиши «Да» и «Нет».
• 5 vbRetryCancel — показывает клавиши повтора и отмены.
• 16 vbCritical — показывает значок критичного сообщения.
• 32 vbQuestion — показывает значок предупреждения.
• 48 vbExclamation — показывает значок предупреждающего сообщения.
• 64 vbИнформация. Показывает значок информационного сообщения.
• 0 vbDefaultButton1 — 1-ая клавиша по дефлоту.
• 256 vbDefaultButton2 — 2-ая клавиша по дефлоту.
• 512 vbDefaultButton3 — 3-я клавиша по дефлоту.
• 768 vbDefaultButton4 — 4-ая клавиша по дефлоту.
• 0 vbApplicationModal Application modal — текущее приложение не будет работать, пока юзер не ответит на сообщение.
• 4096 vbSystemModal System modal — все приложения не будут работать, пока юзер не ответит на сообщение.

Вышеуказанные значения логически разбиты на четыре группы: 1-ая группа (от 0 до 5) показывает клавиши, которые будут отображаться в окне сообщения. 2-ая группа (16, 32, 48, 64) обрисовывает стиль значка , который будет отображаться, в третью группу (0, 256, 512, 768) показывает на то, какая клавиша обязана быть по дефлоту, а четвертую группу (0, 4096 ) описывает модальность окна сообщения.

Возвращаемые значения

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

• 1 — нажата клавиша vbOK — OK.
• 2 — vbCancel — нажата клавиша Отмена
• 3 — vbAbort — нажата клавиша Abort
• 4 — vbRetry — нажата клавиша Retry
• 5 — vbIgnore — нажата клавиша Ignore
• 6 — vbYes — нажата клавиша «Да»
• 7 — vbNo — Нет щелчка

MsgBox по шагам

Шаг 1 — Вышеуказанная функция быть может выполнена или нажатием клавиши «Выполнить» в окне VBA, или нажатием на клавишу, которую Вы сами добавили на лист Excel.

Шаг 2 — Обычный текстовый ящик отображается с сообщением «Добро пожаловать» и клавишей «ОК»

Шаг 3 — Опосля нажатия клавиши «ОК» отображается очередное диалоговое окно с сообщением совместно с клавишами «да, нет и отмена».

Шаг 4 — Опосля нажатия клавиши «Нет» значение данной для нас клавиши (7) сохраняется как целое число и отображается как окно сообщения юзеру, как показано на последующем скриншоте. Используя это значение, можно осознать, какую клавишу щелкнул юзер.

VBA GoTo Statement

VBA GoTo statement is used when an error occurs while running the code rather than displaying error to resume the next line of code by ignoring the error message. There are two kinds of GOTO statements one is to select any range of the worksheet in the specified workbook and another one is error handler.

To overcome the anticipated errors in VBA, we have a function called “GOTO.” We will see both kinds of GoTo statements in this article.

You are free to use this image on your website, templates etc, Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For eg:
Source: VBA GoTo Statement (wallstreetmojo.com)

2 Ways to use GoTo Statement in VBA Code

#1 – Application.GoTo Method

If you want to go to the specific workbook or specific worksheet in all the opened workbooks, we can use the statement Application.GoTo method.

Let’s look at the syntax of the Application.GoTo method

• [Reference]: This is nothing but a specified cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more . If the reference is not provided by default, it will take you to the last used cell range.
• [Scroll]: This a logical statement of TRUE or FALSE. If the value is TRUE, it will scroll through the window. If the value if FALSE, it will not scroll through the window.

Example

If you want to go to a specific cell in the specific worksheet, we can use the Goto method. I have 3 sheets named Jan, Feb, and Mar.

If I want to go to cell C5 in the Jan sheet, I will use the below set of codes.

Code:

Step 2: Start the method “Application.GoTo“

Code:

Step 3: In the reference argument, we need to specify the worksheet name, and in that worksheet, we need to mention the specific cell.

Code:

Step 4: Mention the scroll as TRUE.

Code:

Step 5: Now run this code using the F5 key, or you can also run this code manually, it will take you to the specified sheet and specified cell.

Now I will change the scroll argument to FALSE and see the change it will encounter.

If you want to go to a specific workbook, then you need to mention the workbook name as well before the workbook name.

#2 – Error Handler Method

When the particular line of code encounters an error, then VBA stops executing the rest of the code and shows the error message.

For example, look at the below line of code.

The above code says to delete the sheet in April and add a new sheet. In the active workbook, if there is any sheet name called April, it will delete, or else it will show the below error message dialogue box.

When I run this code, my workbook didn’t have a sheet called April, so the VBA code VBA Code VBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. read more couldn’t find the sheet name and throws the error. Sometimes we need to ignore this error because oftentimes, if there is no sheet name called April, then we need to go on with the other lines of code.

To eliminate this error, we can use the GoTo method as an error handler.

Now I will modify the code as On error goes to the next line.

If you run this, it will add the new sheet even though there is no sheet name called April.

The statement “On Error GoTo NextLine” understands that if any error occurs, it will jump to the next line, and in the next line, VBA code is to add a new sheet.