VBA Excel. Пользовательская функция (синтаксис, компоненты). Вызов процедуры «Sub» в Excel VBA. Вызов VBA процедуры «Function» из другой процедуры

В это статье мы кратко познакомимся с языком VBA, который позволяет создавать макросы в программных продуктах Microsoft Office. Макросы – некий программный код, который позволяет реализовывать за вас поставленную задачу. В отличии от прикладных программ, макросы VBA как таковые не могут выполняться самостоятельно. Язык VBA – Visual Basic for Applications является родственником таких языков как VBScript, Visual Basic или простой Basic. Синтаксис и общие правила программирования практически идентичны.

По сути, зная язык сервера Windows Script Host, вы с легкостью освоите все премудрости VBA.

Отличия VBA от VBScript

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

Внешние объекты – оба языка позволяют подключать функциональные возможности внешних объектов. Объект – в данном контексте это внешний библиотечный файл с набором методов (функций) и свойств (переменных). Однако, если вы создаете макрос в Word, то объект Word.Application будет доступен автоматически, если макрос работает в документе Excel, то автоматически доступен компонент Excel.Application. В сценариях эти объекты нужно подключать самостоятельно (сделать ссылку).

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

Практическое применение VBA

И так, открыв любой документ Microsoft Office, например, Word, мы может вызвать встроенный редактор Visual Basic (комбинация клавиш Alt + F11) и приступить к созданию макроса. Как упоминалось выше, в макросах можно подключать функциональные возможности внешних объектов, тем самым, получать доступ к элементам операционной системы, например, файловая система, реестр и так далее. Желательно, что бы в настройках был отключен автоматический запуск макроса при открытии документа, дабы дырявый или зловредный код не натворил лиха.

Однако, на сегодняшний момент, макросы и язык VBA как элементы администрирования теряют свое преимущество. Тот же Microsoft Access, который предназначен для работы с базами данных также практически не используется. Более актуальным является пакет 1С.

Общие синтаксические принципы языка VBA

• Регистр не играет роли – вы можете прописать оператор как в верхнем, так и в нижнем регистре, или вообще в вперемешку, например, dim, DIM, Dim, разницы нет.
• Комментарии – для комментирования строки, перед ней прописывается одинарная кавычка или ключевое слово REM.
• Максимальная длина любого имени (будь то , или ) составляет 255 знаков.
• В отличии от JavaScript, или С языков, в VBA в конце строки с операторами не нужно ставить запятой.
• Двоеточие – чтобы не прописывать каждый оператор в отдельной строке, их можно с помощью двоеточия прописать в одну строку, например: MsgBox "Строка1" : MsgBox "Строка2"
• Перенос операторов – если строка с операторами слишком длинная, то ее можно разделить на несколько при помощи символа подчеркивания и знака &, например:
  MsgBox "Это простая" _
  & "строка"

Для демонстрации работы с макросами напишем такой пример. Откройте документ Microsoft Word и запустите редактор Visual Basic. Вам нужно создать новую форму и новый модуль (макрос). Цель – решение квадратного уравнения с изменяемыми значениями.

На поверхность формы надо добавить три полосы прокрутки и две текстовые метки. В редакторе кода для формы пропишите:

Option Explicit Sub Get_Result() Dim MyA As Byte , MyB As Byte , MyC As Byte Dim D, x, x1, x2, msg MyA = ScrollBar1.Value: MyB = ScrollBar2.Value: MyC = ScrollBar3.Value Label1.Caption = MyA & "x*x + " _ & MyB & "x + " _ & MyC & " = 0" " находим дискриминант D = MyB ^ 2 - 4 * MyA * MyC "Если дискриминант равен нулую - одно решение If D = 0 Then x = MyB / (2 * MyA) msg = "Уравнение имеет одно решение, x ровно: " & x "Если дискриминант больше нуля - два решения ElseIf D > 0 Then x1 = (-MyB + Sqr(D)) / (2 * MyA) x2 = (-MyB - Sqr(D)) / (2 * MyA) msg = "Уравнение имеет два решения" & vbCrLf & "x1 ровно: " & x1 & vbCrLf & "x2 ровно: " & x2 "Если дискриминант меньше нуля - нет решения Else msg = "Нет решения (комплексные числа)" End If Label2.Caption = msg End Sub Private Sub ScrollBar1_Change() Call Get_Result End Sub Private Sub ScrollBar2_Change() Call Get_Result End Sub Private Sub ScrollBar3_Change() Call Get_Result End Sub Private Sub UserForm_Initialize() Dim MyA As Byte , MyB As Byte , MyC As Byte " параметры полос прокрутки ScrollBar1.Min = 1 ScrollBar1.Max = 20 ScrollBar2.Min = 1 ScrollBar2.Max = 30 ScrollBar3.Min = 1 ScrollBar3.Max = 40 " параметры первого текстового поля Label1.FontSize = 15 Label1.ForeColor = &HFF0000 Label1.TextAlign = fmTextAlignCenter " параметры второго текстового поля Label2.FontSize = 15 Label2.ForeColor = &H6400 MyA = ScrollBar1.Value: MyB = ScrollBar2.Value: MyC = ScrollBar3.Value Label1.Caption = MyA & "x*x + " _ & MyB & "x + " _ & MyC & " = 0" End Sub

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

Разное (39)
Баги и глюки Excel (3)

Как обратиться к диапазону из VBA

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

Если необходимо обратиться к именованному диапазону :

Cells(1, 1).Value = "Привет"

Синтаксис объекта Range:
Range(Cell1 , Cell2 )

• Cell1 - первая ячейка диапазона. Может быть ссылкой на ячейку или диапазон ячеек, текстовым представлением адреса или имени диапазона/ячейки. Допускается указание несвязанных диапазонов(A1,B10), пересечений(A1 B10).
• Cell2 - последняя ячейка диапазона. Необязательна к указанию. Допускается указание ссылки на ячейку, столбец или строку.

Синтаксис объекта Cells:
Cells(Rowindex , Columnindex )

• Rowindex - номер строки
• Columnindex - номер столбца

Исходя из этого несложно предположить, что к диапазону можно обратиться, используя Cells и Range:

"выделяем диапазон "A1:B10" на активном листе Range(Cells(1,1), Cells(10,2)).Select

и для чего? Ведь можно гораздо короче:

Range("D5:F56").Cells(3, 2).Select

Согласитесь, это гораздо удобнее, чем отсчитывать каждый раз. Особенно, если придется оперировать смещением не на 2-3 ячейки, а на 20 и более. Конечно, можно было бы применить Offset. Но данное свойство именно смещает диапазон на указанное количество строк и столбцов и придется уменьшать на 1 смещение каждого параметра для получения нужной ячейки. Да и смещает на указанное количество строк и столбцов весь диапазон, а не одну ячейку. Это, конечно, тоже не проблема - можно вдобавок к этому использовать метод Resize - но запись получится несколько длиннее и менее наглядной:

Dim rR as Range Set rR = Range("D5")

если оператор Set не применять, то в лучшем случае получите ошибку, а в худшем(он возможен, если переменной rR не назначать тип) переменной будет назначено значение Null или значение ячейки по умолчанию. Почему это хуже? Потому что в таком случае код продолжит выполняться, но логика кода будет неверной, т.к. эта самая переменная будет содержать значение неверного типа и применение её в коде в дальнейшем все равно приведет к ошибке. Только ошибку эту отловить будет уже сложнее.
Использовать же такую переменную в дальнейшем можно так же, как и прямое обращение к диапазону:

"так выглядит запись слова Test в ячейку А1 Range("A1").Select Selection.Value = "Test"

Но как правило выделение - действие лишнее. Можно записать значение и без него:

Теперь чуть подробнее разберем, как обратиться к диапазону не выделяя его и при этом сделать все правильно. Диапазон и ячейка - это объекты листа. У каждого объекта есть родитель - грубо говоря это другой объект, который является управляющим для дочернего объекта. Для ячейки родительский объект - Лист, для Листа - Книга, для Книги - Приложение Excel. Если смотреть на иерархию зависимости объектов, то от старшего к младшему получится так:
Applicaton => Workbooks => Sheets => Range
По умолчанию для всех диапазонов и ячеек родительским объектом является текущий(активный) лист. Т.е. если для диапазона(ячейки) не указать явно лист, к которому он относится, в качестве родительского листа для него будет использован текущий - ActiveSheet:

"запишем слово Test в ячейку A1 на активном листе Range("A1").Value = "Test"

Т.е. если в данный момент активен Лист1 - то слово Test будет записано в ячейку А1 Лист1. Если активен Лист3 - в А1 Лист3. Иначе говоря такая запись равносильна записи:

"активируем Лист2 Worksheets("Лист2").Select "записываем слово Test в ячейку A1 Range("A1").Value = "Test"

Чтобы не активируя другой лист записать в него данные, необходимо явно указать принадлежность объекта Range именно этому листу:

"запишем слово Test в ячейку A1 на Лист2 независимо от того, какой лист активен Worksheets("Лист2").Range("A1").Value = "Test"

Таким же образом происходит считывание данных с ячеек - если не указывать лист, данные ячеек которого необходимо считать - считаны будут данные с ячейки активного листа. Чтобы считать данные с Лист2 независимо от того, какой лист активен применяется такой код:

"считываем значение ячейки A1 с Лист2 независимо от того, какой лист активен MsgBox Worksheets("Лист2").Range("A1").Value

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

"запишем слово Test в ячейку A1 на Лист2 книги Книга2.xlsx независимо от того, какая книга и какой лист активен Workbooks("Книга2.xlsx").Worksheets("Лист2").Range("A1").Value = "Test" "считываем значение ячейки A1 с Лист2 книги Книга3.xlsx независимо от того, какой лист активен MsgBox Workbooks("Книга3.xlsx").Worksheets("Лист2").Range("A1").Value

Важный момент: лучше всегда указать имя книги вместе с расширением(.xlsx, xlsm, .xls и т.д.). Если в настройках ОС Windows(Панель управления -Параметры папок -вкладка Вид -Скрывать расширения для зарегистрированных типов файлов ) указано скрывать расширения - то указывать расширение не обязательно - Workbooks("Книга2"). Но и ошибки не будет, если его указать. Однако, если пункт "Скрывать расширения для зарегистрированных типов файлов" отключен, то указание Workbooks("Книга2") обязательно приведет к ошибке.

Очень часто ошибки обращения к ячейкам листов и книг делают начинающие, особенно в циклах по листам. Вот пример неправильного цикла:

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets Range("A1").Value = wsSh.Name "записываем в ячейку А1 имя листа MsgBox Range("A1").Value "проверяем, то ли имя записалось Next wsSh

MsgBox будет выдавать правильные значения, но сами имена листов будут записываться не на каждый лист, а в последовательно в ячейку активного листа. Поэтому на активном листе в ячейке А1 будет имя последнего листа.
А вот так выглядит правильный цикл:
Вариант 1 - активация листа (медленный)

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets wsSh.Activate "активируем каждый лист Range("A1").Value = wsSh.Name "записываем в ячейку А1 имя листа MsgBox Range("A1").Value "проверяем, то ли имя записалось Next wsSh

Вариант 2 - без активации листа (быстрый и более правильный)

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets wsSh.Range("A1").Value = wsSh.Name "записываем в ячейку А1 имя листа MsgBox wsSh.Range("A1").Value "проверяем, то ли имя записалось Next wsSh

Важно: если код записан в модуле листа(правая кнопка мыши на листе-Исходный текст ) и для объекта Range или Cells родитель явно не указан(т.е. нет имени листа и книги) - тогда в качестве родителя будет использован именно тот лист, в котором записан код, независимо от того какой лист активный. Иными словами - если в модуле листа записать обращение вроде Range("A1").Value = "привет" , то слово привет всегда будет записывать в ячейку A1 именно того листа, в котором записан сам код. Это следует учитывать, когда располагаете свои коды внутри модулей листов.

В конструкциях типа Range(Cells(,),Cells(,)) Range является контейнером, в котором указываются ссылки на объекты, из которых и будет создана ссылка на непосредственно конечный объект.
Предположим, что активен "Лист1" , а код запущен с листа "Итог" .
Если запись будет вида

Sheets("Итог").Range(Cells(1, 1), Sheets("Итог").Cells(10, 1)) "запись ниже так же неверна Range(Cells(1, 1), Sheets("Итог").Cells(10, 1))

Sheets("Итог" ).Range(Sheets("Итог" ).Cells(1, 1), Sheets("Итог" ).Cells(10, 1)) Range(Sheets("Итог" ).Cells(1, 1), Sheets("Итог" ).Cells(10, 1))

Sheets("Итог").Range(Sheets("Итог").Cells(1, 1), Sheets("Итог").Cells(10, 1)) Range(Sheets("Итог").Cells(1, 1), Sheets("Итог").Cells(10, 1))

Вторая запись не содержит ссылки на родителя для Range, но ошибки это в большинстве случаев не вызовет - т.к. если для контейнера ссылка не указана, а для двух объектов внутри контейнера родитель один - он будет применен и для самого контейнера. Однако лучше делать как в первой строке - т.е. с обязательным указанием родителя для контейнера и для его составляющих. Т.к. при определенных обстоятельствах(например, если в момент обращения к диапазону активной является книга, открытая в режиме защищенного просмотра) обращение к Range без родителя может вызывать ошибку выполнения.
Если запись будет вида Range("A1" , "A10") , то указывать ссылку на родителя внутри Range не обязательно - достаточно будет указать эту ссылку перед самим Range - Sheets("Итог").Range("A1" , "A10") , т.к. текстовое представление адреса внутри Range обязывает создать ссылку именно на родителя контейнера.

Разберем пример, приближенный к жизненной ситуации. Необходимо на лист Итог занести формулу вычитания, начиная с ячейки А2 и до последней заполненной. На момент записи активен Лист1. Очень часто начинающие записывают так:

Sheets("Итог" ).Range("A2:A" & Cells(Rows.Count, 1).End (xlUp).Row) _ .FormulaR1C1 = "=RC2-RC11"

Sheets("Итог").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) _ .FormulaR1C1 = "=RC2-RC11"

Запись смешанная - и текстовое представление адреса ячейки("A2:A") и ссылка на объект Cells . В данном случае явную ошибку код не вызовет, но и работать будет не всегда так, как хотелось бы. А это самое плохое, что может случиться при разработке.
Sheets("Итог").Range("A2:A" - создается ссылка на столбец " A " листа Итог . Но далее идет первого столбца. И вот как раз это вычисление происходит на основе объекта Cells , который не содержит в себе ссылки на родительский объект. А значит он будет вычислять последнюю строку исключительно для текущего листа(если код записан в стандартном модуле, а не модуле листа) - т.е. для Лист1. Правильно было бы записать так:

lLastRow = Workbooks("Книга3.xls").Sheets("Лист1").Cells(Rows.Count, 1).End(xlUp).Row

с виду все нормально, но есть нюанс. Rows.Count по умолчанию будет относится к активной книге, если записано в стандартном модуле. Приведенный выше код должен работать с книгой формата 97-2003 и вычислить последнюю заполненную ячейку на листе1. В книгах формата Excel 97-2003(.xls) всего 65536 строк. Если в момент выполнения приведенной строки активна книга формата 2007 и выше(форматы.xlsx, .xlsm, .xlsb и пр) - то Rows.Count вернет 1048576 , т.к. именно такое количество строк в листах книг версий Excel, начиная с 2007. И т.к. в книге, в которой мы пытаемся вычислить последнюю строку всего 65536 строк - получим ошибку 1004, т.к. не может быть номера строки 1048576 на листе с количеством строк 65536. Поэтому имеет смысл указывать явно откуда считывать Rows.Count:

With Workbooks("Книга3.xls").Sheets("Лист1") lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row End With

Также не мешало бы упомянуть возможность выделения несмежного диапазона(часто его называют "рваным"). Это диапазон, который обычно привыкли выделять на листе при помощи зажатой клавиши Ctrl. Что это дает? Это дает возможность выделить одновременно ячейки A1 и B10 и записать значения только в них. Для этого есть несколько способов. Самый очевидный и описанный в справке - метод Union :

Union(Range("A1" ), Range("B10" )).Value = "Привет"

Union(Range("A1"), Range("B10")).Value = "Привет"

Однако существует и другой метод:

Range("A1,B10" ).Value = "Привет"

Range("A1,B10").Value = "Привет"

В чем отличие(я бы даже сказал преимущество) Union : можно применять в цикле по условию. Например, выделить в диапазоне A1:F50 только те ячейки, значение которых больше 10 и меньше 20:

Sub SelOne() Dim rCell As Range, rSel As Range For Each rCell In Range("A1:F50" ) If rCell.Value > 10 And rCell.Value < 20 Then If rSel Is Nothing Then Set rSel = rCell Else Set rSel = Union(rSel, rCell) End If End If Next rCell If Not rSel Is Nothing Then rSel.Select End Sub

Sub SelOne() Dim rCell As Range, rSel As Range For Each rCell In Range("A1:F50") If rCell.Value > 10 And rCell.Value < 20 Then If rSel Is Nothing Then Set rSel = rCell Else Set rSel = Union(rSel, rCell) End If End If Next rCell If Not rSel Is Nothing Then rSel.Select End Sub

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

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

Также см.:
[[Как определить последнюю ячейку на листе через VBA?]]
[[Как определить первую заполненную ячейку на листе?]]

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

Определение VBA, преимущества, возможности применения

VBA (Visual Basic for Applications) - это диалект языка Visual Basic, расширяющий возможности Visual Basic и предназначенный для работы с приложениями Microsoft Office и другими приложениями от Microsoft и третьих фирм.

В принципе, как это не удивительно, при программировании в Office можно вполне обойтись без языка VBA. Подойдет любой COM-совместимый язык, например, обычный Visual Basic, VBScript, JScript, C++, Delphi, Java и т.п. Можно использовать и.NET-совместимые языки программирования - VB.NET, C# и т.п. Все возможности объектных моделей приложений Office вполне можно будет использовать. Например, если сохранить следующий код в файле с расширением *.vbs и запустить его на выполнение, то будет запущен Word, в нем открыт новый документ и впечатан текст:

Set oWord = CreateObject("Word.Application")

oWord.Visible = true

oWord.Documents.Add

oWord.Selection.TypeText ("Привет от VBScript")

Однако VBA обычно - самый удобный язык для работы с приложениями Office.

Главная причина проста - язык VBA встроен в приложения Office (и не только), и код на языке VBA можно хранить внутри документов приложений Office - документах Word, книгах Excel, презентациях PowerPoint и т.п. Конечно же, этот код можно запускать оттуда на выполнение, поскольку среда выполнения кода VBA (на программистском сленге - хост) встроена внутрь этих приложений.

В настоящее время VBA встроен:

• во все главные приложения MS Office - Word, Excel, Access, PowerPoint, Outlook, FrontPage, InfoPath;
• в другие приложения Microsoft, например, Visio и M icrosoft Project;
• в более чем 100 приложений третьих фирм, например, CorelDraw и CorelWordPerfect Office 2000, AutoCAD и т.п.

У VBA есть также множество других преимуществ:

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

• создавать полноценные приложения на Visual Basic (поскольку эти языки - близкие родственники);
• использовать все возможности языка VBScript (это - вообще урезанный VBA). В результате в вашем распоряжении будут универсальные средства для создания скриптов администрирования Windows (об этом - в конце курса), для создания Web-страниц (VBScript в Internet Explorer), для создания Web-приложений ASP, для применения в пакетах DTS и заданиях на MS SQL Server, для создания серверных скриптов Exchange Server и многое-многое другое.

VBA изначально был ориентирован на пользователей, а не на профессиональных программистов (хотя профессионалы пользуются им очень активно), поэтому создавать программы на нем можно очень быстро и легко. Кроме того, в Office встроены мощные средства, облегчающие работу пользователя: подсказки по объектам и по синтаксису, макрорекордер и т.п.

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

Несмотря на то, что часто приложения VBA выполняются медленнее, чем бы вам хотелось, они нересурсоемки и очень хорошо работают, например, на сервере терминалов. Но, как правило, для программ на VBA особых требований про производительности и нет: для написания игр, драйверов, серверных продуктов они не используется. По моему опыту, чаще всего проблемы с производительностью VBA-приложений - это не проблемы VBA, а проблемы баз данных, к которым они обращаются. Если проблемы действительно в VBA (обычно тогда, когда вам требуется сложная математика), то всегда есть возможность написать важный код на C++ и обращаться к нему как к обычной библиотеке DLL или встраиваемому приложению (Add-In) для Word, Excel, Access и т.п.

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

В среде программистов-профессионалов считается, что самый короткий путь "с нуля" и программ типа "Hello, World" до профессиональных программ, которые делаются под заказ - именно через связку Office- VBA (а конечно, не через C++, Java или Delphi).

VBA в Excel

Программирование в Excel с помощью VBA.

Язык VBA позволяет писать макросы в Excel. Как это делать рассматривается ниже.

Application

Application - это объект, занимающий вершину иерархии объектов в Excel. Application – это и есть Excel. Вот примеры использования Application:

окно нормальных размеров

Application.WindowState = xlNormal

окно максимальных размеров

Application.WindowState = xlMaximized

Workbooks

Workbooks - это коллекция рабочих книг, которые открыты. Каждая рабочая книга представлена объектом Workbook. Узнать сколько рабочих книг сейчас в коллекции можно так:

Dim bookCount As Integer
bookCount = Workbooks.Count
MsgBox ("Opened books = " & bookCount)

Обатиться к нужной рабочей книге в коллекции Workbooks можно обратиться по его номеру или по имени:

Workbooks.Item(2).Activate

здесь мы активизировали второй элемент коллекции Workbooks.

Другие примеры мы увидим ниже.

Workbook

Workbooks.Item(1).Save

И где же в этом примере рабочая книга Workbook? Здесь: Workbooks.Item(1), это выражение возвращает первый элемент коллекции Workbooks, а это и есть Workbook.

Закрыть рабочую книгу:

Workbooks.Item(1).Close

Sheets

Sheets - это все листы рабочей книги. Листы рабочей книги представлены двумя типами: рабочие листы - это обычные листы Excel и второй тип - это листы диаграмм. Те и другие и составляют коллекцию Sheets.

Charts

Charts - это только диаграммы рабочей книги.

Chart

Chart - это объект, представляющий одину диаграмму. Если диаграмма содержится в обычном рабочем листе, то она объектом Chart не является.

Worksheets

Worksheets - это только рабочие листы рабочей книги.

Добавим рабочий лист в коллекцию Worksheets:

Worksheet

Worksheet - это один лист рабочей книги Excel.

Все ячейки рабочего листа Worksheet:

Worksheets("Лист1").Cells

Range. Работа с ячейками в Excel

Range - это одна ячейка листа или несколько ячеек. Этот объект рассмотрим подробнее. Установим кнопку на рабочий лист Excel. Откройте панель инструментов «Элементы управления» (правой кнопкой по свободному полю панели инструментов и выбираем из выпавшего меню). В этой панели выбираем кнопку и устанавливаем её на свободное место рабочего листа, в его правой части. Правой кнопкой мыши по нашей кнопке - > Свойства. Установите значение свойства Name - CommandButton, а свойства Caption - Range Test.

Получаем:

Закройте окно свойств кнопки, и кликаем по нашей кнопке правой кнопкой мыши, из выпавшего меню выбираем «Исходный текст». Открывается окно редактора VBA, оно называется Microsoft Visual Basic. В нём уже есть заготовка обработчика нажатия на кнопку CommandButton:

Кстати, помотрите на эту заготовку, ключевое слово Sub говорит, что это процедура, а слово Private указывает, что эта процедура видна лишь в данном модуле.

Всё у нас готово для начала изучения работы с ячейками рабочего листа Excel.

Как задать активную ячейку?

Сделаем активной ячейку A2:A2:

Private Sub CommandButton_Click()
Range("A2:A2").Activate
End Sub

Написание кода Ваших процедур и редактирование макросов производится в редакторе Visual Basic, который доступен с вкладки «Разработчик». Данная вкладка по умолчанию скрыта. Для ее отображения необходимо выполнить следующие действия:

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

Для Excel 2010 и 2013 . В параметрах Excel нужно выбрать пункт «Настроить ленту» и в категории основные вкладки установить галочку для вкладки «Разработчик».

Знакомство с редактором Visual Basic в Excel

Чтобы попасть в редактор кода, кликните на вкладке разработчика в области «Код» по кнопке «Visual Basic».

После этого на экране появится новое окно редактора VBA. Рассмотрим ниже некоторые его элементы, которые могут понадобиться в самом начале.

№2 на изображении . Кнопки запуска, остановки и прерывания выполнения кода. Во время запуска начинает выполняться код той процедуры, на которой размещен курсор. Данные кнопки дублируются вкладкой «Run» основного меню редактора.

№3 на изображении . Окно управления проектами VBA открытых книг (Project Explorer) и кнопка, отображающая это окно.

№4 на изображении . Кнопка, отображающая окно свойств объектов (Properties Window), выбранных в окне №3, и элементов пользовательских форм (работа с объектами, их свойствами и методами будет рассмотрена в отдельной статье этой категории).

№5 на изображении . Кнопка вызова окна объектов (Object Browser). В нем описаны все доступные для работы объекты, их свойства и методы.

Кнопки №3, №4 и №5 так же доступны на вкладке «View» главного меню редактора. Там же можно вызвать другие полезные для работы окна, которые не будут рассмотрены в этом материале.

Написание простой процедуры на Visual Basic for Application

Код любой процедуры (подпрограммы) располагается в модуле, поэтому необходимо его добавить, чтобы приступить к программированию. Выберите пункт «Module» на вкладке «Insert» основного меню редактора VBA. В основной области редактора (на изображении имеет серый фон) должно появиться новое окно кода – Имя_книги – имя_модуля (Code), а в окне управления проектами (№3) к дереву выбранного проекта добавится вновь созданный модуль.

Добавьте в модуль следующий код:

Sub Моя_процедура() MsgBox "Привет пользователь!" End Sub

Данная процедура выводит на экран диалоговое окно с сообщением «Привет пользователь!». Протестируйте ее работу, нажав кнопку Rub Sub (№2 на изображении) или вызвав как обычный макрос.

Теперь более подробно разберем приведенный код.

Любая процедура начинается со строки объявления, состоящей из оператора Sub (сокращение от Subprogram), после которого следует имя подпрограммы. За именем следует перечень аргументов, заключенный в скобки.

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

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

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

Последняя строка содержит оператор End с последующим ключевым словом Sub и сообщает о завершении процедуры.

Учимся пользоваться Object Browser

Выше была упомянута функция MsgBox , но ее возможности полностью не рассмотрены. Это хороший случай, чтобы научиться использовать браузер объектов (№5 на изображении окна редактора VBA).

Нажмите кнопку на панели или клавишу F2, чтобы отобразить Object Browser:

Выпадающий список №1 содержит перечень всех подключенных библиотек. Используйте его только в том случае, если точно знаете, к какой библиотеке относится изучаемый класс, функция и т.д. (подключить или отключить любую из доступных библиотек можно, выбрав пункт «References» на вкладке «Tools» главного меню редактора VBA).

Примечание: Библиотеки предоставляют стандартные и дополнительные возможности работы с объектами Excel (ячейки, листы, книги и т.д.), файловой системы, изображениями, базами данных и другими.

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

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

Поле №4 содержит перечень всех классов, выбранных в поле №1 библиотек.

В области №5 содержится список всех членов класса, выбранного в поле №4.

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

Примечание: библиотеки, классы, функции и процедуры (методы), свойства и т.п. в Object Browser имеют различные обозначения, представленные набором пиктограмм. Краткая справка (поле №6) всегда указывает какой тип элемента выделен. Обращайте на это внимание, чтобы в дальнейшем лучше ориентироваться в работе с объектами.

Примечание: Понятие объекта не относится к теме данной статьи и будет рассмотрено в следующих материалах.

Теперь найдем функцию MsgBox с помощью браузера объектов. В поле поиска (№2) впишите ее название и кликните по кнопке с изображением бинокля. В поле результатов поиска выделите строку со значением MsgBox в столбце «Member». В поле №6 появилась краткая информация, сообщающая нам, что выбранный член является функцией, имеет перечень аргументов, возвращает результат типа VbMsgBoxResult, относиться в классу Interaction библиотеки VBA.

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

Изучите самостоятельно возможности функции MsgBox.

Ссылка на процедуру VBA

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

Для лучшего понимая рассмотрим пример (в примере используется псевдокод):

Начало_Процедуры Любой код Первая строка дублирующего кода Любой код Последняя строка дублирующего кода Любой код Первая строка дублирующего кода Любой код Последняя строка дублирующего кода Любой код Конец_Процедуру

Выносите дублирующие участки кода в отдельные процедуры и указывайте ссылки на них из главной процедуры:

Начало_Главной_Процедуры Любой код Ссылка на Процедуру_дублирующегося_кода Любой код Ссылка на Процедуру_дублирующегося_кода Любой код Конец_Главной_Процедуру Начало_Процедуры_дублирующегося_кода Любой код Конец_Процедуры_дублирующегося_кода

Call Макрос1 "Следующая строка идентична предыдущей Макрос1

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

У Вас недостаточно прав для комментирования.