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

Содержание

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

Обращение к конкретной ячейке

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

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

С помощью Range
С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

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

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Range(«A1») Cells(1, 1)

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

With Workbooks(«Книга1»).Sheets(«Лист2») ‘ Вывести значение ячейки A1, которая находится на Листе2 MsgBox .Range(«A1») ‘ Вывести значение ячейки B1, которая находится на Листе2 MsgBox .Range(«B1») End With

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

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Application.ActiveCell ‘ полная запись ActiveCell ‘ краткая запись

Чтение значения из ячейки

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

Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

MsgBox Cells(1, 1) ‘ выведет 01.03.2018 MsgBox Cells(1, 1).Value ‘ выведет 01.03.2018 MsgBox Cells(1, 1).Value2 ‘ выведет 43160 MsgBox Cells(1, 1).Text ‘ выведет 01 марта 2018 г. Dim d As Date d = Cells(1, 1).Value2 ‘ числовое представление даты преобразуется в тип Date MsgBox d ‘ выведет 01.03.2018

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

MsgBox Range(«C1») ‘ выведет 123,4568 MsgBox Range(«C1»).Value ‘ выведет 123,4568 MsgBox Range(«C1»).Value2 ‘ выведет 123,456789 MsgBox Range(«C1»).Text ‘ выведет 123,457р. Dim c As Currency c = Range(«C1»).Value2 ‘ значение преобразуется в тип Currency MsgBox c ‘ выведет 123,4568 Dim d As Double d = Range(«C1»).Value2 ‘ значение преобразуется в тип Double MsgBox d ‘ выведет 123,456789

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Dim s As String Dim i As Integer s = Range(«B1»).Value2 ‘ успех i = Range(«B1»).Value2 ‘ ошибка

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Как написать макрос в Excel на языке программирования VBA

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

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

Создание макросов

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

Создать макрос в Excel с помощью макрорекордера

Регистратор макросов может записывать только то, к чему он может прикоснуться, что означает, что он может записывать только действия, когда используются кнопки, значки, команды меню и все в этом духе, такие параметры, как сортировка по цвету, для него недоступны;
Если в период регистрации была допущена ошибка, это также будет зарегистрировано. Но вы можете использовать кнопку, чтобы отменить последнее действие, удалить последнюю команду, которую вы неправильно написали в VBA;
Запись в макрорекордере осуществляется только в пределах окна MS Excel, и если вы закроете программу или включите другую, запись будет остановлена и перестанет работать.

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

в версиях Excel, начиная с 2007 г., необходимо нажать кнопку «Записать макрос» на вкладке «Разработчик»>;>
в версиях Excel 2003 и более ранних (они все еще очень часто используются) нужно выбрать пункт «Макрос» в меню «Инструменты» и нажать кнопку «Начать запись».

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

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

Написание макросов в Excel

Код макроса Excel написан на Visual Basic для приложений (VBA) и выполняется инструментом приложения, с которым он связан. Большинство этих инструментов недоступны на уровне окна Excel. Как написать макрос.

Теперь мы продемонстрируем на примере информацию о том, как писать, редактировать и запускать код макроса.

Чтобы написать макрос:

SubMyMakros()
ДимпользовательAs String
Dimdata_segodnyaAs Данные
пользователь = Application.UserName
data_segodnya = Сейчас
MsgBox «Пользователь запустил макрос:» vbNewLine
вы можете выбрать сочетание клавиш, которое будет запускать запись макроса при нажатии;

Выберите место для сохранения:

Принцип работы, создание и конструирование Пользовательских Форм — UserForms (2)

Ввод данных в таблицу Excel с помощью формы VBA

— сохраненный в «Этой книге» макрос будет работать только в текущем документе;

— после сохранения в «Личной книге» макрос будет работать во всех документах на вашем компьютере.

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

Щелкните «ОК».

Если вы не указали сочетание клавиш, запись начнется сразу после нажатия кнопки «Ок».

В процессе регистрации вам необходимо выполнить необходимую последовательность действий.

Когда закончите, нажмите кнопку «Остановить запись.

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

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

Макросы в книге можно редактировать. Для этого выберите макрос и нажмите кнопку «Изменить». При нажатии кнопки «Изменить» откроется редактор макросов со сценарием, написанным на VBA.

Отображение вкладки “Разработчик” в ленте меню

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

Щелкните правой кнопкой мыши одну из существующих вкладок на ленте и выберите «Настроить ленту». Откроется диалоговое окно «Параметры Excel».

В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». В правой части панели «Основные вкладки» установите флажок «Разработчик».

Нажмите ОК».

В результате в строке меню появится вкладка «Разработчик

Абсолютная и относительная запись макроса

Вы уже знаете об абсолютных и относительных ссылках в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выбираете ячейку A2 и вводите текст «Excel», каждый раз, независимо от того, где вы находитесь на листе и какую ячейку вы выбираете, ваш код будет вставлять текст «Excel» в ячейку A2.

Если вы используете параметр относительной привязки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет «двигаться» относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1 и начали запись макроса в режиме относительной ссылки. Теперь выберите ячейку A2, введите текст Excel и нажмите Enter.

Теперь, если вы запустите этот макрос, он не вернется к ячейке A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она перейдет в B4, напишет текст «Excel», а затем перейдет в ячейку K5.

Теперь напишем макрос в режиме относительной ссылки:

Выберите ячейку A1.

Переходим во вкладку «Разработчик”.

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

Нажмите кнопку «Записать макрос”.

В диалоговом окне «Запись макроса» введите имя макроса. Например, название «RelativeRefs”.

В опции «Сохранить в» выберите «Эта книга”.

Щелкните «ОК”.

Выберите ячейку A2.

Введите текст «Excel» (или как хотите).

Нажмите клавишу Enter. Курсор переместится в ячейку A3.

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

Макрос будет сохранен в режиме относительной ссылки.

А теперь сделайте следующее.

Выделите любую ячейку (кроме A1).

Переходим во вкладку «Разработчик”.

В группе «Код» нажмите кнопку «Макрос”.

В диалоговом окне «Макросы» щелкните сохраненный макрос «Относительные ссылки”.

Нажмите кнопку «Выполнить”.

Как вы заметили, макрос не записывал текст «Excel» в ячейки A2. Это произошло из-за того, что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она перейдет в текст Excel — ячейку B4 и в конечном итоге выберет ячейку B5.

Вот код, который написал макрос:

Sub RelativeRefs () ‘Макрос RelativeRefs’ ActiveCell.Offset (1, 0) .Range («A1»). Выберите ActiveCell.FormulaR1C1 = «Excel» ActiveCell.Offset (1, 0) .Range («A1»). Выберите Готово ниже

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение от этой ячейки.

Игнорируйте часть кода Range («A1»). Это один из тех случаев, когда средство записи макросов добавляет ненужный код, который не имеет смысла и может быть удален. Код будет нормально работать и без него.

Расширение файлов Excel, которые содержат макросы

Что нельзя сделать с помощью макрорекодера?

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

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

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

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

Условия не могут быть проанализированы — условия в коде можно проверить с помощью средства записи макросов. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и выполнения кода, если оно истинно (или другого кода, если оно ложно).

Редактор Visual Basic

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

Запускаем выполнение макроса

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

На этой же вкладке («Разработчик») и в группе «Код» нажмите кнопку «Макрос» (также можно использовать сочетание клавиш Alt + F8).

В появившемся окне выбираем наш макрос и нажимаем на команду «Выполнить”.
Примечание. Есть более простой способ запустить выполнение макроса: использовать сочетание клавиш, которое мы указали при создании макроса.

Результатом проверки будет повторение ранее выполненных действий (записано.

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

Редактор Visual Basic

В редакторе VB можно записать любой оператор языка.

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

Для добавления процедуры используется команда Insert — Procedure. Этой же командой можно создать функцию.

Рис. 2.8. Добавление процедуры

В примере добавляется процедура MyProc ( рис. 2.8), что приводит к появлению двух строк

Рис. 2.9. Две строки процедуры

Для переименования процедуры достаточно изменить ее идентификатор в операторе объявления процедуры Sub.

Процедуры записываются в стандартных модулях и на процедурных листах. Новый модуль добавляется в проект выбором пункта Module (Модуль) из меню Insert.

В процедурах записываются операторы и комментарии:

операторы представляют собой команды обработки объектов приложения и команды языка Visual Basic ;

комментарии — некоторый свободный текст, который вводится в программу в качестве пояснений. Текст комментария начинается с одинарной кавычки (‘).

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

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

Операторы не привязаны к определенной позиции строки, но считается хорошим тоном записывать операторы друг под другом в соответствии с уровнем вложенности операторов ( запись тела цикла , условных операторов и т.п.). Стандартный сдвиг в четыре символа производится при нажатии на клавишу Tab. Автоматически в следующей строке делается такой же отступ , как и в предыдущей. Изменить размер стандартного сдвига можно в настройках редактора (см. ниже рис. 2.10, поле Tab Width ).

Рекомендуется отделять смысловые части программы пустыми строками и комментариями.

Допускается перенос длинного оператора с одной строки на другую. Cимвол продолжения строки — символ подчеркивания (_).

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

Перед символом продолжения строки должен находиться пробел. После символа продолжения строки не должно быть никаких символов.

Для ускорения набора ключевых слов используйте команду Complete Word (Завершить слово) из меню Edit (Правка) или клавиши Ctrl+пробел.

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

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

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

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

Настройка редактора выполняется на вкладке Editor Format команды Tools-Options (Сервис-Параметры).

Рис. 2.10. Диалоговое окно настройки редактора VB

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

Можно импортировать ( команда Import из меню File ) текст модуля из файлов с расширениями .frm(формы), .bas(модули), .cls(классы). Также можно экспортировать ( команда Export из меню File ) текст модуля в файлы с расширениями .frm, .bas, .cls. Процедуры экспорта и импорта позволяют легко переносить приложение на VBA из одного документа в другой.

В окне программы отображаются тексты процедур объекта, выделенного в окне проекта.

В окне программы может отображаться текст одной процедуры ( procedure view ) или текст модуля целиком ( full-module view ). В последнем случае процедуры разделяются горизонтальными линейками. Выбрать вид представления кода можно, нажав соответствующую кнопку в левом нижнем углу окна с текстом процедур (см. рис. 2.11).

Вверху окна программы расположены два списка: список объектов (левый) и список процедур (правый). Содержание списков зависит от типа объекта, для которого высвечено окно программы.

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

На рис. 2.11 из списка процедур выбрана строка b_p. Это действие высветит текст процедуры b_p.

строки для каждого объекта, расположенного внутри выбранного объекта;

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

Если в списке объектов выделен объект, то второй список — перечень событий, связанных с выделенным объектом. Если в списке объектов выбрана строка General, то во втором списке доступна строка Declarations и перечень общих процедур.

Рис. 2.11. Окно стандартного модуля

На рис. 2.12 в окне программы, открытом для рабочей книги , в списке объектов выделен объект Workbook и для него открыт перечень событий.

Рис. 2.12. Окно программы объекта Эта книга

Режимы работы с программой

В VBA возможны четыре режима работы с программой.

Режим конструктора. В режиме конструктора можно создавать и редактировать процедуры, создавать и модифицировать формы.

Режим выполнения программы. Для запуска процедуры используются клавиша F5 или кнопка Run на стандартной панели инструментов. Предварительно необходимо установить точку вставки внутрь выбранной процедуры. В этом режиме в заголовке окна VB после имени файла указывается [running].

Режим прерывания программы. Программа автоматически переходит в режим прерывания при возникновении некоторой ошибки во время выполнения программы.

Рис. 2.13. Прерывание программы

Высвечивается диалог, в котором можно выбрать продолжение ( Continue ) или завершение работы программы ( End ), получение справки относительно возникшей ошибки ( Help ) или переход в режим отладки ( Debug ).

Важно:

При возникновении ошибки на этапе выполнения ( Run-time error) VBA выводит сообщение, о том, какая именно ошибка произошла (например, Object required ) и позволяет получить более подробный Help об этом типе ошибок. Внимательное чтение сообщений об ошибках и соотвествующих разделов Help значительно упрощает отладку.

Если программа зависает или необходимо прервать выполнение программы по какой-то причине, используйте клавиши Ctrl-Break или кнопкупри нажатии которых программа переходит в режим отладки .

при достижении контрольной точки;

программа выполнила команду Stop (пауза в выполнении программы);

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