Как создать макрос в excel
Перейти к содержимому

Как создать макрос в excel

  • автор:

Автоматизация рутины в Microsoft Excel при помощи VBA

В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.

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

Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.

Поэтому, увы, будем учить Visual Basic.

Чуть-чуть подготовки и постановка задачи

Итак, поехали. Открываем Excel.

Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.

Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):

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

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

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

Кодим

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

И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».

Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:

Напишем Hello World:

Sub FormatPrice()
MsgBox «Hello World!»
End Sub

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

Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

‘ Процедура. Ничего не возвращает
‘ Перегрузка в VBA отсутствует
Sub foo(a As String , b As String )
‘ Exit Sub ‘ Это значит «выйти из процедуры»
MsgBox a + «;» + b
End Sub

‘ Функция. Вовращает Integer
Function LengthSqr(x As Integer , y As Integer ) As Integer
‘ Exit Function
LengthSqr = x * x + y * y
End Function

Sub FormatPrice()
Dim s1 As String , s2 As String
s1 = «str1»
s2 = «str2»
If s1 <> s2 Then
foo «123» , «456» ‘ Скобки при вызове процедур запрещены
End If

Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i

Dim x As Double
x = Val( «1.234» ) ‘ Парсинг чисел
x = x + 10
MsgBox x

On Error Resume Next ‘ Обработка ошибок — игнорировать все ошибки
x = 5 / 0
MsgBox x

On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox «OK!»
GoTo ne

ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок

‘ Циклы бывает, какие захотите
Do While True
Exit Do

Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( «abc» ), Val( «4» ))
Case 24
MsgBox «0»
Case 25
MsgBox «1»
Case 26
MsgBox «2»
End Select

‘ Двухмерный массив.
‘ Можно также менять размеры командой ReDim (Preserve) — см. google
Dim arr(1 to 10, 5 to 6) As Integer
arr(1, 6) = 8

Dim coll As New Collection
Dim coll2 As Collection
coll.Add «item» , «key»
Set coll2 = coll ‘ Все присваивания объектов должны производится командой Set
MsgBox coll2( «key» )
Set coll2 = New Collection
MsgBox coll2.Count
End Sub

Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.

Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.

Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.

Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.

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

Кодим много и под Excel

В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.

Sub FormatPrice()
Sheets( «result» ).Cells.Clear
Sheets( «data» ).Activate
End Sub

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets( «result» ).Activate
Dim r As Range
Set r = Range( «A1» )
r.Value = «123»
Set r = Range( «A3,A5» )
r.Font.Color = vbRed
r.Value = «456»
Set r = Range( «A6:A7» )
r.Value = «=A1+A3»

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

  1. Считали группы из очередной строки.
  2. Пробегаемся по всем группам в порядке приоритета (вначале более крупные)
    1. Если текущая группа не совпадает, вызываем процедуру AddGroup(i, name), где i — номер группы (от номера текущей до максимума), name — её имя. Несколько вызовов необходимы, чтобы создать не только наш заголовок, но и всё более мелкие.

    Для упрощения работы рекомендую определить следующие функции-сокращения:

    Function GetCol(Col As Integer ) As String
    GetCol = Chr(Asc( «A» ) + Col)
    End Function

    Function GetCellS(Sheet As String , Col As Integer , Row As Integer ) As Range
    Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row))
    End Function

    Function GetCell(Col As Integer , Row As Integer ) As Range
    Set GetCell = Range(GetCol(Col) + CStr(Row))
    End Function

    Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

    Глобальные переменные

    Option Explicit ‘ про эту строчку я уже рассказывал
    Dim CurRow As Integer
    Const GroupsCount As Integer = 2
    Const DataCount As Integer = 3

    FormatPrice

    Sub FormatPrice()
    Dim I As Integer ‘ строка в data
    CurRow = 1
    Dim Groups(1 To GroupsCount) As String
    Dim PrGroups(1 To GroupsCount) As String

    Sheets( «data» ).Activate
    I = 2
    Do While True
    If GetCell(0, I).Value = «» Then Exit Do
    ‘ .
    I = I + 1
    Loop
    End Sub

    Теперь надо заполнить массив Groups:

    На месте многоточия

    Dim I2 As Integer
    For I2 = 1 To GroupsCount
    Groups(I2) = GetCell(I2, I)
    Next I2
    ‘ .
    For I2 = 1 To GroupsCount ‘ VB не умеет копировать массивы
    PrGroups(I2) = Groups(I2)
    Next I2
    I = I + 1

    И создать заголовки:

    На месте многоточия в предыдущем куске

    For I2 = 1 To GroupsCount
    If Groups(I2) <> PrGroups(I2) Then
    Dim I3 As Integer
    For I3 = I2 To GroupsCount
    AddHeader I3, Groups(I3)
    Next I3
    Exit For
    End If
    Next I2

    Не забудем про процедуру AddHeader:

    Перед FormatPrice

    Sub AddHeader(Ty As Integer , Name As String )
    GetCellS( «result» , 1, CurRow).Value = Name
    CurRow = CurRow + 1
    End Sub

    Теперь надо перенести всякую информацию в result

    For I2 = 0 To DataCount — 1
    GetCellS( «result» , I2, CurRow).Value = GetCell(I2, I)
    Next I2

    Подогнать столбцы по ширине и выбрать лист result для показа результата

    После цикла в конце FormatPrice

    Sheets( «Result» ).Activate
    Columns.AutoFit

    Всё. Можно любоваться первой версией.

    Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

    Sub AddHeader(Ty As Integer , Name As String )
    Sheets( «result» ).Range( «A» + CStr(CurRow) + «:C» + CStr(CurRow)).Merge
    ‘ Чтобы не заводить переменную и не писать каждый раз длинный вызов
    ‘ можно воспользоваться блоком With
    With GetCellS( «result» , 0, CurRow)
    .Value = Name
    .Font.Italic = True
    .Font.Name = «Cambria»
    Select Case Ty
    Case 1 ‘ Тип
    .Font.Bold = True
    .Font.Size = 16
    Case 2 ‘ Производитель
    .Font.Size = 12
    End Select
    .HorizontalAlignment = xlCenter
    End With
    CurRow = CurRow + 1
    End Sub

    Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

    Поэтому чуть-чуть меняем код с добавлением стиля границ:

    Sub AddHeader(Ty As Integer , Name As String )
    With Sheets( «result» ).Range( «A» + CStr(CurRow) + «:C» + CStr(CurRow))
    .Merge
    .Value = Name
    .Font.Italic = True
    .Font.Name = «Cambria»
    .HorizontalAlignment = xlCenter

    Select Case Ty
    Case 1 ‘ Тип
    .Font.Bold = True
    .Font.Size = 16
    .Borders(xlTop).Weight = xlThick
    Case 2 ‘ Производитель
    .Font.Size = 12
    .Borders(xlTop).Weight = xlMedium
    End Select
    .Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
    End With
    CurRow = CurRow + 1
    End Sub

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

    В начале FormatPrice

    Dim I As Integer ‘ строка в data
    CurRow = 0 ‘ чтобы не было пропуска в самом начале
    Dim Groups(1 To GroupsCount) As String

    В цикле расстановки заголовков

    If Groups(I2) <> PrGroups(I2) Then
    CurRow = CurRow + 1
    Dim I3 As Integer

    В точности то, что и хотели.

    Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.

    Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.

    Спасибо за внимание.

    Буду рад конструктивной критике в комментариях.

    UPD: Перезалил пример на Dropbox и min.us.

    UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

    Как записывать макросы в Microsoft Excel (руководство по автоматизации)

    Andrew Childress

    https://amdy.su/wp-admin/options-general.php?page=ad-inserter.php#tab-8

    Andrew Childress Last updated Dec 14, 2018

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

    Record and use Macros in ExcelRecord and use Macros in Excel Record and use Macros in Excel

    Итак, что, если вы работаете с таблицами изо дня в день, но проводите слишком много времени делая что-то снова и снова? Ответом будет – создание Microsoft Excel макроса, записать набор шагов, которые можно воспроизвести. Записать макрос, а затем применить его к новым данным, и вы увидите, как они меняются с этим набором записанных действий.

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

    Что такое макросы в Excel и для чего они?

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

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

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

    Плюсы от создания макросов включают:

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

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

    Как сделать макрос в Excel (смотрим и учимся)

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

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

    Как создать макрос в Excel

    Следуйте инструкции ниже, чтобы создать макрос в Excel:

    1. Откройте вкладку разработчика

    Чтобы создать макрос, найдите вкладку Разработчик на ленте Excel и нажмите на неё. Если вы не видите меню Разработчик, вам нужно его включить в Файл > Параметры > Настроить ленту. Посмотрите отмечен ли флажок напротив Разработчик, если нет, то поставьте его:

    Customize Ribbon with Developer tabCustomize Ribbon with Developer tab Customize Ribbon with Developer tabЧтобы видеть вкладку Разработчик на ленте, нужно включить её в настройках.

    Теперь, нажмите на вкладку Разработчик. Слева вы видите несколько кнопок, относящихся к макросам в Microsoft Excel, которые мы будем использовать в этом уроке. Посмотрите есть ли у вас кнопки Макросы, Запись макроса и Относительные ссылки:

    Macros button on Developer tabMacros button on Developer tab Macros button on Developer tabВ этом уроке вы будете работать с левой стороной панели Разработчика, с параметрами Макросы и Записать макрос.

    2. Запишите ваш первый макрос

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

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

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

    Я думаю, что эта настройка говорит Excel к чему применять макрос. Если вы это не включите, макрос будет очень специфично осуществлять преобразования. Например, если вы запишите макрос, когда эта опция выключена и измените ячейки A1, A2, и A3, то макрос всегда будет изменять только те ячейки.

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

    Теперь, нажмите на Запись макроса. Вы увидите новое окно, где вы можете указать название макроса. Придумайте хорошее название, чтобы, когда у вас будет много макросов, вы помнили, что каждый из них делает с данными. А ещё установите клавиатурное сокращение:

    Save a Macro in ExcelSave a Macro in Excel Save a Macro in ExcelКогда начнёте записывать маркос в Excel, вам понадобится задать ему название и установить клавиатурное сокращение.

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

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

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

    Highlighted rows in ExcelHighlighted rows in Excel Highlighted rows in ExcelПри записи макроса я каждую ненужную строку, чтобы включить их в мой макрос «удаление других строк».

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

    Как только выделите достаточное количество строк, правый клик и удалите строки. Затем нажмите Остановить запись на ленте. Макрос закончит записывать ваши шаги.

    Вот и всё! Мы готовы воспроизвести макрос и удалить строки в новом наборе данных.

    3. Воспроизведение макроса в Excel

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

    Важное примечание об использовании макросов Microsoft Excel: они начинают воспроизводиться с того места, куда вы навели курсор мыши. В макросе Delete Every Other Row (Удалить каждую ненужную строку) он начнёт удаление строк, с той, на которой мы находимся. Помните об этом, чтобы макрос запускался и влиял на набор данных, который вы подразумеваете.

    Теперь нажмите на меню Макросы. Откроются параметры и список всех макросов, которые вы записали для использования с вашими данными:

    Play back Macros Menu in ExcelPlay back Macros Menu in Excel Play back Macros Menu in ExcelВыберите макрос во всплывающем окне, затем нажмите Выполнить, чтобы воспроизвести его и применить преобразования к данным.

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

    Deleted every other row in ExcelDeleted every other row in Excel Deleted every other row in ExcelПосле воспроизведения макроса, все другие строки данных будут удалены.

    Лучшее в макросах Excel – они применяются последовательно. Это поможет избежать ошибок в данных. К сожалению, это так же может быть и проблемой. Если ваши исходные данные существенно изменяются, макрос может не знать, как обработать данные и изменит данные некорректно.

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

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

    Узнайте больше о Excel

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

    Excel — это мощный инструмент, который имеет множество применений. Ознакомьтесь с уроками, приведёнными ниже, чтобы узнать больше вариантов работы с данными в Excel и приручить электронные таблицы:

    Какие ваши любимые инструменты для экономии времени в Excel? Используете ли вы макросы или другие инструменты для работы с данными, чтобы ваша работа с электронными таблицами была выполнимой? Дайте мне знать в разделе комментариев ниже и поделитесь любимым советом с вашим приятелем, читателем Envato Tuts+.

    Essential VBA coding every Excel user should know

    Ivan Cuenca

    Excel users don’t program, and programmers don’t care about Excel so the potential of VBA remains a wasteland between these two worlds for a lot of users.

    By the end of this article you’ll be able to write your own simple programs and customized Excel functions regardless of your current level.

    I’am writing this with a really straight forward approach, because although there are a lot of great VBA blogs out there, they fail at breaking the barrier for most non-programers to start with it.

    My goal is to help as much people as possible improving their work with some VBA skills.

    If you already know what VBA is, and you have some basic programming skills I suggest you skip this introduction and go straight to the “Ok, let’s get serious” title below. If this is not your case, then let me explain what this is about.

    VBA stands for Visual Basic for Applications. It’s a programming language included in the MS Office package.

    VBA is perfect to perform repetitive tasks which doesn’t add value. For example, the classical update, copy, paste, repeate type of workbook. Analizing the reports adds value, but building them is a waste of time, son instead of doing it yourself let the computer work for you (I talked about this on How to become a better controller).

    With a handful of VBA programs you can save huge amounts of time.

    Non the less, this VBA scripts are contained in the Excel files, so another person without any technical knowledge can benefit on the code you write.

    You can use it also to perform difficult tasks which would be very tedious, or simply impossible to do with Excel’s built-in functions alone. Like performing an action depending on the background color of a cell for an instance.

    But before we begin, there is some general misunderstanding about what is a Macro and what is a VBA program, so let’s keep it clear before we begin.

    Are VBA and Macros the same?

    Macros are peaces of VBA code generated automatically via Excel. It is really easy to create a macro, you just need to click on Developer/Record Macro and do whatever you want it to repeat later.

    When using macros you don’t need to see the code, much less editing it, but at the end this is exactly what Excel is doing. In the GIF below you can see how the code is generated while recording a macro:

    Even for experienced VBA users it’s useful to record a macro every now and then because it gives you a hint of how to write an specific code without spending an afternoon searching on google. The downside is that this macro’s code usually isn’t the best option.

    For example, in the GIF above Excel writes 7 lines to change the color of a cell but, a single line with the code ActiveCell.Interior.Color=65535 would be more than enought.

    Forget about macros for now and let’s focus on pure VBA programming.

    Part 1: Writing a program

    As I said before my goal is for you to be able to write simple vba programs, so forgive me if I go straight to the point and skip some technical details.

    Also, if you have problems following any step just leave a comment, I’ll be really happy to help you.

    First of all, we are going to create a module, you can imagine it as a notebook where you write VBA code. On any Excel worksheet, press Alt+F11 or go to Developer/Visual Basic, this will open the VBA editor window.

    At the left you should see a frame named Project -VBA project containing a treelike structure with the name of the open workbooks on your computer, their sheets, and some other stuff which we’ll talk about later. If you can’t see this go to View/Project Explorer.

    Now right-click on any sheet and press Insert/Module (see GIF below)

    Hello World

    If you have ever programmed a Hello World function then you might want to skip this part, but if not this is a fun way to write your first program.

    Copy this in your module, or better type it yourself, and press F5 or Run/Run Sub.

    You should see something like this:

    Tada. Congratulations for your first working program. Let’s take a look at it step by step. First the sub block, this is telling your Excel Here you have a VBA program called ‘HelloWorld’. The End Sub points the last line of the program.
    You will write the commands between these two lines

    Next we summon the msgbox() function. This function can do a lot more than this, but sometimes it’s ok to keep it simple.

    Note that we must write the text quoted, if it’s not quoted the editor will handle it like a variable and pop an error.

    Coding Hint 1: you can leave comments within your code with an apostrophe. In your VBA editor you will see them green colored.

    Codign Hint 2: You can also use tabs, spaces and empty lines to make it more readable, they don’t affect the program either.

    Ok, now let’s get serious

    How about counting the number of colored cells in a range for example? As the exercise above, this has a pedagogical purpose, but try to think about the kind of tasks you could apply it for.

    First of all, I have a sheet that looks like this:

    As I did before, I’ll show you the program finished, so that you can just copy&paste and see it working on your computer. Then I’ll explain it line by line.

    Once you run it you should get a message box like this:

    It will work for any color and any number of colored cells within the range A1:H15, but don’t believe me, try coloring different cells with different colors and see it for yourself.

    How does it work? Let’s go step by step:

    We declared two variables, a Range and an Integer. You can imagine a variable like a container for an specific type of data. For instance, we created an integer, which is just a whole number, and a Range. Ranges are really interesting in VBA for Excel. Once you assign a range of cells to a range type variable you gain access to those cells properties, like their value, their background color, format, etc. We’ll get to that later.

    By the way, it’s a healthy habit to declare the needed variables at the beginning.

    Next step, assign values.

    Notice that in order to fill a Range you need to use the Set function while with simpler variables like integers it’s not necessary.

    Acting on each cell of a given range

    To perform this we’ll use a for statement. In case you’re not familiar with programing, a for function runs through a set of elements, performing an action in each one of them.

    In its simplest expression, it just repeats something over.
    For example, if you try this:

    Something like this will happen:

    Got it? Now look at the code we used before:

    In this snippet, instead of going from 1 to 10 we do something more useful in Excel, going through each cell of a given range and performing an action in each one of them.

    Now let’s take a look at what’s inside the for statement.

    If function in VBA

    As an Excel user, you may be familiar with the IF function. In case you’re not, it works this way:

    The condition is a boolean, this means that its value can only be True or False.

    The equivalent to this Excel function in VBA would be:

    In our example, we are only interested in the true part. If the cell is colored then we count it, if not we don’t care. When you’re only interested in the true part you can write it as a single line

    Going back to our code:

    The condition is (Not cell.Interior.ColorIndex = xlNone), in natural language it would be something like (this is not an colorless cell). So if the cell has any color the program will adds 1 to the variable Count. Finally, when the for statement has already run over all cells in range R it added as many +1 as colored cells. That’s how we count them.

    Cells properties in VBA

    I don’t want to get technical, but you should know that VBA is an object oriented program. VBA objects have properties and methods. A property is something like color, size, column, etc. A method is an operation like copy, save, etc. Each type of object is called a Class, and each Class has different properties and methods. For instance, it makes sense for a Workbook class object to have a save method, but it doesn’t for a cell class object as there is no way to save a single cell.

    When you type a dot next to a variable the VBA Editor will show you all the available properties and methods for its class (see GIF below).

    In the color counting example we used the property .Interior.ColorIndex. An easy way to get the name of a given property is to change it while recording a macro.

    Cell Class has dozens of properties. Some of the most useful and easy to understand are those:

    • .Row: row number
    • .Column: column number. I insist, not letter, but number. Column A is 1, B is 2 and so on.
    • .Value: its value, being the output of a formula or just a raw value (usually a number, a string or a boolean).

    And there is also a property called .Offset( ) which is particularly useful.

    This one has two arguments .Offset(rows,columns). For example, Range(“A1”).offset(1,2) is C2, as it’s one row below and two columns to the right. With this property you can act over a cell depending of its position relative to an other cell which you have declared in your code.

    Look at this example. Take a moment to read and understand the code and then pay attention to what is actually doing.

    See how you can combine different properties like Offset and Value?

    By the way, to run a program line by line as I did above just press F8.

    Stop condition

    While running a for statement, sometimes you don’t need to go through all the cells. For instance, imagine that we want to know if there is any colored cell. As you don’t care about how many are in there you can stop when you find the first one. To do this use the Exit For statement. See example below:

    You can copy this on you VBA editor and run it step by step just pressing F8 repeatedly. Put the windows in a way that you can see the worksheet, I entered the snippet cell.select so you can see which cell it’s checking at a time.

    Part 2: Different ways to run a program

    Pressing play (F5) in the VBA Editor is the simplest way to execute a program, but you need to have at least some knowledge of VBA to do it.
    What if your program is for someone else who doesn’t know anything about VBA? Or what if you want it to work under certain circumstances? like when you open a certain file, for example.

    Running a program on demand

    The easiest way to do this is to assign the program you have just created to a shape. This is really easy, just right click on any shape and choose Assign Macro. After this you’ll run the program just by clicking (left click) on this shape.

    A rather more professional way to do this is with Active X controls. There are different types of Active X controls, but in this article we’ll see only buttons.
    Go to Developer/Insert/Active X controls to add a new button (see GIF)

    By right clicking and choosing View Code you can write exactly what the button is supposed to do. Note that this code is created in the Sheet code, not in a module.

    You can also summon a sub from another sheet or module with the function call.

    There are some other ways to run a VBA program on demand, I’ll write more about this topic in the future, but for now understanding this two ways should be more than enough.

    Running by events

    In this context, an event is an action. In the previous example, you saw a Button_Click event. In this case the event is clicking that particular button, not very original. But there are a lot of different events you can use. Let’s see a few examples:

    1. By activating a certain sheet

    To program an event on a given sheet just open its code with a double click and choose between the available events in the choose boxes above. You can also write the code straight away if you know how does it goes.

    This would be an example of what you can do.

    It will do something like this:

    Remember that you can also call some function from another module with the Call Statement.

    2. By opening a Workbook

    Same as with sheets, there are some events regarding whole workbooks. To program those events double click on ThisWorkbook in the project explorer menu to open its code.

    Imagine you have a lot of pivot tables in the same document, and you have to make sure you don’t forget to update them. Take it easy, let the computer work for you. You can automate it with just 3 lines of code:

    Play with the chooseboxes to find more events, the more you play the more you learn.

    Part 3: Customized functions

    Would you like to create your own functions, not VBA functions, but functions you can use in a spreadsheet like =SUM() or =VLOOKUP(), then I have good news for you, this is way easier than it looks like.

    For example, did you ever have a sheet with a lot of ratios like A/B, sometimes it’s really annoying when B equals 0 and it start giving #DIV0 errors.
    I know, you can just do something like:

    But it would be easier if you just have a formula that does exactly that without having to type it each time, wouldn’t it.

    Create a new module and try this one:

    That’s it! You don’t need to do anything else the new function is available. See how it works:

    The difference between functions and sub, like the MutiplyByTwo we did before, is that a sub will be calculate only when the program runs, but a function written in a cell will update like any other Excel Built-in function as SUM( ) or COUNT( ).

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

    Макрос – это программа, записанная на языке VBA(VisualBasicforApplications– специальная версия языкаVisualBasic), и предназначенная для автоматизации вычислений и действий в офисных приложениях фирмы Microsoft(Word,Excel,PowerPointи других).

    Макросы часто используются:

    для ускорения часто выполняемых операций редактирования или форматирования;

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

    для упрощения доступа к параметрам в диалоговых окнах;

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

    Создать макрос можно двумя способами: с помощью средства для записи макросов или с помощью редактора Visual Basic.

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

    Средство записи макросов (Сервис→Макрос→Начать запись) позволяет быстро создавать макросы с минимальными усилиями (нет необходимости знать VBA). При этом все действия, производимые пользователем, автоматически записываются в виде последовательности инструкций на языке VBA.

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

    Изменить, просмотреть или удалить макрос можно с помощью диалогового окна Макрос (Сервис→Макрос→Макросы).

    Для быстрого запуска макроса можно назначить ему:

    кнопку из панели инструментов Формы,

    графический объект на листе.

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

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

    Применение элементов управления в Excel

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

    В Excel имеются два типа элементов управления:

    элементы ActiveX панели инструментов Элементы управления. Макросы для управления их работой обычно создаются в редакторе VBA как программы на языке VBA;

    элементы управления панели Формы. Макросы для этих элементов создаются с помощью средства записи макросов и не требуют знания VBA.

    Перечень основных элементов управления представлен в таблице «Элементы управления Excel».

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

    Название элемента

    Текст, который можно вставить в любое место на листе.

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

    Кнопка, запускающая макрос

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

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

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

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

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

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

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

    Свойства большинства элементов можно изменять.

    Панель инструментов Формы

    Действия, производимые с помощью объекта управления, задаются в диалоговом окне Формат элемента управления на вкладке Элемент управления (это окно вызывается командой Формат объекта из контекстного меню). У элементов управления Кнопка и Надпись можно изменять только свойства, касающиеся формата.

    С помощью элементов форм в Excelможно не просто заносить данные в анкеты и бланки, но и производить анализ введенных данных.

    Каждый элемент формы (кроме элементов КнопкаиНадпись) связан с ячейкой. Значение связанной ячейки изменяется в зависимости от состояния элемента или действия с элементом. Для работы элементовСписок,Поле со списком,Полоса прокруткииСчетчикзадаются диапазоны ячеек.

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

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

    Описания для работы элемента (связанные ячейки, диапазоны ячеек, название и т. п.) задаются в окне Свойства. Действия, выполняемые элементами (например, кнопкой), задаются в виде программы на языкеVBA.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *