Программирование в эксель примеры: Excel примеры vba

alexxlab / / Разное

Содержание

Программирование в Excel — voxt.ru

Как программировать в Excel?

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

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

Сначала включить вкладку разработчика

Доступ к программированию на VBA можно получить, включив вкладку РАЗРАБОТЧИК в Excel. По умолчанию в MS Excel он отключен; нам нужно включить его. Ниже приведен скриншот того же.

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

Шаг 1: Перейти в ФАЙЛ.

Шаг 2: В разделе ФАЙЛ перейдите к ОПЦИИ.

Шаг 3: Вы увидите все окно «Параметры Excel». Выберите Настроить ленту в Excel.

Шаг 4: Установите флажок РАЗРАБОТЧИК, чтобы включить его. Щелкните ОК.

Шаг 5: Вы будете теперь иметь возможность видеть то Вкладка разработчика.

Как записывать макросы в Excel?

Лучший способ начать программирование в Excel — это записать макрос.

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

Вы можете скачать этот шаблон Excel для программирования здесь — Шаблон для программирования Excel

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

Шаг 1: На вкладке «Разработчик» нажмите «Записать макрос».

Шаг 2: Назовите макрос.

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

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

Шаг 3: Назовите окно макроса

Нам нужно дать собственное имя окну макроса. В окне ниже я дал имя «Recording_Macro». Как мы видим, здесь нет специального символа или пробела.

Шаг 4: Щелкните ОК, чтобы начать запись.

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

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

Первое, что я сделаю в Excel, это выделю ячейку A1. Это первое действие на этом листе.

После выбора ячейки наберу «Добро пожаловать в VBA» в этой камере. Это второе действие в Excel.

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

Шаг 6: Нажмите на Остановить запись на вкладке Разработчик, чтобы остановить запись макроса.

После этих трех действий я выберу опцию остановки записи на вкладке разработчика.

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

Теперь нам нужно пройти через то, что было записано, чтобы понять, как именно программирование работает с Excel. Чтобы увидеть этот код на вкладке «Разработчик», нажмите «Visual Basic» или нажмите комбинацию клавиш «ALT + F11».

Сочетание клавиш Excel для нажатия «Visual Basic».

После нажатия «Visual Basic» или сочетания клавиш, мы можем увидеть окно под окном.

Дважды щелкните модули.

Теперь все записи, которые мы сделали до сих пор, появятся справа.

Как видим, первое слово — «SUB. » Все имена макросов начинаются с «Sub», «Function», «Private» или «Public».

Каждый макрос состоит из двух частей; один — «Голова», а второй — «Хвост». Голова — это имя макроса, а хвост — это конец макроса.

Между этим заголовком и концом макроса excel VBA записал все действия, которые мы сделали. Теперь давайте посмотрим на код построчно.

Просто запомните, каким было наше первое действие после того, как мы начали запись макроса, то есть мы выбрали ячейку A1, поэтому для этого в коде программирования VBA используется диапазон («A1»). Выбрать

После выбора ячейки A11 мы вошли в тест как «Добро пожаловать в VBA», поэтому в коде программирования VBA

ActiveCell. FormulaR1C1 = “Welcome to VBA”

Заметка: R1C1 — это строка 1, столбец 1.

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

Range (“A2”). Select

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

Теперь этот код можно выполнить в любое время; он повторяет одно и то же действие снова и снова. Теперь удалите слово.

«Добро пожаловать в VBA» в ячейке A1.

После удаления слова перейдите в Visual Basic. Поместите курсор внутри кода и нажмите кнопку RUN. Теперь мы снова получаем текст «Добро пожаловать в VBA» в ячейке A1.

Заметка: Сочетание клавиш для запуска кода — F5.

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

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

Весь код макросов, содержащийся в книгах, должен быть сохранен в специальных версиях. При сохранении книги нам нужно выбрать «Сохранить как тип» в качестве «Книги Excel с поддержкой макросов».

То, что нужно запомнить

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

УЗНАТЬ БОЛЬШЕ >>

Post Views: 795

Похожие записи

Прокрутить вверх

Примеры макросов Excel — ОфисГуру

Главная » Уроки MS Excel » Макросы (VBA)

Автор Антон Андронов На чтение 6 мин Опубликовано

12.2015

Следующие простые примеры макросов Excel иллюстрируют некоторые возможности и приёмы, описанные в самоучителе по Excel VBA.

Содержание

  1. Макрос Excel: пример 1
  2. Макрос Excel: пример 2
  3. Макрос Excel: пример 3
  4. Макрос Excel: пример 4
  5. Макрос Excel: пример 5
  6. Макрос Excel: пример 6

Макрос Excel: пример 1

Первоначально эта процедура Sub была приведена, как пример использования комментариев в коде VBA. Однако, здесь также можно увидеть, как объявляются переменные, как работают ссылки на ячейки Excel, использование цикла For, условного оператора If и вывод на экран окна сообщения.

'Процедура Sub выполняет поиск ячейки, содержащей заданную строку
'в диапазоне ячеек A1:A100 активного листа

Sub Find_String(sFindText As String)

   Dim i As Integer 'Целое число типа Integer, используется в цикле For
   Dim iRowNumber As Integer 'Целое число типа Integer для хранения результата

   iRowNumber = 0

   'Просматривает одну за другой ячейки A1:A100 до тех пор, пока не будет найдена строка sFindText

   For i = 1 To 100

      If Cells(i, 1).
  Value = sFindText Then

         'Если совпадение с заданной строкой найдено
         'сохраняем номер текущей строки и выходим из цикла For
         iRowNumber = i
         Exit For

      End If
   Next i

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

   If iRowNumber = 0 Then
      MsgBox "Строка " & sFindText & " не найдена"
   Else
      MsgBox "Строка " & sFindText & " найдена в ячейке A" & iRowNumber
   End If

End Sub

Макрос Excel: пример 2

Следующая процедура Sub – пример использования цикла Do While. Здесь также можно увидеть, как объявляются переменные, работу со ссылками на ячейки Excel и применение условного оператора If.

'Процедура Sub выводит числа Фибоначчи, не превышающие 1000

Sub Fibonacci()

   Dim i As Integer 'Счётчик для обозначения позиции элемента в последовательности
   Dim iFib As Integer 'Хранит текущее значение последовательности
   Dim iFib_Next As Integer 'Хранит следующее значение последовательности
   Dim iStep As Integer 'Хранит размер следующего приращения

   'Инициализируем переменные i и iFib_Next
   i = 1
   iFib_Next = 0

   'Цикл Do While будет выполняться до тех пор, пока значение
   'текущего числа Фибоначчи не превысит 1000

   Do While iFib_Next < 1000

      If i = 1 Then
         'Особый случай для первого элемента последовательности
         iStep = 1
         iFib = 0
      Else
         'Сохраняем размер следующего приращения перед тем, как перезаписать
         'текущее значение последовательности
         iStep = iFib
         iFib = iFib_Next
      End If

      'Выводим текущее число Фибоначчи в столбце A активного рабочего листа
      'в строке с индексом i
      Cells(i, 1).
  Value = iFib

      'Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1
      iFib_Next = iFib + iStep
      i = i + 1
   Loop

End Sub

Макрос Excel: пример 3

Эта процедура Sub просматривает ячейки столбца

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

'Процедура Sub сохраняет значения ячеек столбца A активного листа в массиве

Sub GetCellValues()

   Dim iRow As Integer 'Хранит номер текущей строки
   Dim dCellValues() As Double 'Массив для хранения значений ячеек

   iRow = 1
   ReDim dCellValues(1 To 10)

   'Цикл Do Until перебирает последовательно ячейки столбца A активного листа
   'и извлекает их значения в массив до тех пор, пока не встретится пустая ячейка

   Do Until IsEmpty(Cells(iRow, 1))

      'Проверяем, что массив dCellValues имеет достаточный размер
      'Если нет – увеличиваем размер массива на 10 при помощи ReDim
      If UBound(dCellValues) < iRow Then
         ReDim Preserve dCellValues(1 To iRow + 9)
      End If

      'Сохраняем значение текущей ячейки в массиве dCellValues
      dCellValues(iRow) = Cells(iRow, 1). Value

      iRow = iRow + 1
   Loop

End Sub

Макрос Excel: пример 4

В этом примере процедура Sub считывает значения из столбца A рабочего листа Лист2 и выполняет с ними арифметические операции. Результаты заносятся в ячейки столбца A на активном рабочем листе. В этом макросе продемонстрировано использование объектов Excel. В частности, производится обращение процедурой Sub к объекту Columns, и показано, как доступ к этому объекту осуществляется через объект Worksheet. Показано так же, что при обращении к ячейке или диапазону ячеек на активном листе, имя этого листа при записи ссылки указывать не обязательно.

'Процедура Sub при помощи цикла считывает значения в столбце A рабочего листа Лист2,
'выполняет с каждым значением арифметические операции и записывает результат в
'столбец A активного рабочего листа (Лист1)

Sub Transfer_ColA()

   Dim i As Integer
   Dim Col As Range
   Dim dVal As Double

   'Присваиваем переменной Col столбец A рабочего листа Лист 2

   Set Col = Sheets("Лист2"). Columns("A")
   i = 1

   'При помощи цикла считываем значения ячеек столбца Col до тех пор,
   'пока не встретится пустая ячейка

   Do Until IsEmpty(Col.Cells(i))

      'Выполняем арифметические операции над значением текущей ячейки

      dVal = Col.Cells(i).Value * 3 - 1

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

      Cells(i, 1) = dVal
      i = i + 1

   Loop

End Sub

Макрос Excel: пример 5

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

'Данный код показывает окно с сообщением, если на текущем рабочем листе
'выбрана ячейка B1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   'Проверяем выбрана ли ячейка B1
   If Target. Count = 1 And Target.Row = 1 And Target.Column = 2 Then

      'Если ячейка B1 выбрана, выполняем необходимое действие
      MsgBox "Вы выбрали ячейку B1"

   End If

End Sub

Макрос Excel: пример 6

На примере этой процедуры показано использование операторов On Error и Resume для обработки ошибок. В данном коде также показан пример открытия и чтения данных из файла.

'Процедура Sub присваивает аргументам Val1 и Val2 значения ячеек A1 и B1
'из рабочей книги Data.xlsx, находящейся в папке C:\Documents and Settings

Sub Set_Values(Val1 As Double, Val2 As Double)

   Dim DataWorkbook As Workbook

   On Error GoTo ErrorHandling

      'Открываем рабочую книгу с данными

      Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data")

      'Присваиваем переменным Val1 и Val2 значения из заданной рабочей книги

      Val1 = Sheets("Лист1"). Cells(1, 1)
      Val2 = Sheets("Лист1").Cells(1, 2)

      DataWorkbook.Close

   Exit Sub

ErrorHandling:

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

   MsgBox "Файл Data.xlsx не найден! " & _
      "Пожалуйста добавьте рабочую книгу в папку C:\Documents and Settings и нажмите OK"
   Resume

End Sub

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excelfunctions.net/Excel-Macro-Example.html
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Примеры кода VBA для Excel

Примеры макросов Excel

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

Копирование и вставка строки с одного листа на другой

Этот очень простой макрос копирует строку с одного листа на другой.

Sub Paste_OneRow()
'Копировать и вставить строку
Листы("лист1").Диапазон("1:1").Копировать Листы("лист2").Диапазон("1:1")
Application.CutCopyMode = Ложь
Конец сабвуфера
Отправить письмо

Этот полезный макрос запускает Outlook, создает черновик сообщения электронной почты и прикрепляет ActiveWorkbook.

Sub Send_Mail ()
    Dim OutApp как объект
    Затемнить OutMail как объект
    Установите OutApp = CreateObject("Outlook.Application")
    Установить OutMail = OutApp.CreateItem(0)
    С исходящей почтой
        .to = "test@test.com"
        .Subject = "Тестовое письмо"
        .Body = "Тело сообщения"
        .Attachments.Add ActiveWorkbook.FullName
        .Отображать
    Конец с
    Установите OutMail = Ничего
    Установить OutApp = Ничего
Конец сабвуфера
Список всех листов в книге

Этот макрос выведет список всех листов в книге.

Подлисты списка()
    
    Dim ws As рабочий лист
    Dim x как целое число
    
    х = 1
    
    ActiveSheet. Range("A:A").Очистить
    
    Для каждого ws в рабочих листах
        ActiveSheet.Cells(x, 1) = ws.Name
        х = х + 1
    Следующий мс
    
Конец сабвуфера
Показать все рабочие листы

Этот макрос отобразит все рабочие листы.

' Показать все рабочие листы
Sub Показать все рабочие листы ()
    Dim ws As рабочий лист
    
    Для каждого ws в ActiveWorkbook.Worksheets
        ws.Visible = кслшитвисибле
    Следующий мс
    
Конец сабвуфера
Скрыть все рабочие листы, кроме активного

Этот макрос скроет все рабочие листы, кроме активного рабочего листа.

' Скрыть все листы, кроме активного листа
Sub HideAllExceptActiveSheet()
    Dim ws As рабочий лист
    
    Для каждого ws в ThisWorkbook.Worksheets
        Если ws.Name <> ActiveSheet.Name, тогда ws.Visible = xlSheetHidden
    Следующий мс
    
Конец сабвуфера
Снять защиту со всех листов

Этот пример макроса снимает защиту со всех листов в книге.

' Снять защиту со всех рабочих листов
Sub UnProtectAllSheets ()
    Dim ws As рабочий лист
    
    Для каждого ws в рабочих листах
        ws.Unprotect "пароль"
    Следующий мс
    
Конец сабвуфера
Защитить все листы

Этот макрос защитит все листы в книге.

'Защитить все рабочие листы
Sub ProtectAllSheets ()
    Dim ws As рабочий лист
    
    Для каждого ws в рабочих листах
        ws.protect "пароль"
    Следующий мс
    
Конец сабвуфера
Удалить все фигуры

Этот макрос удалит все фигуры на листе.

Sub Удалить все фигуры ()
Dim GetShape как форма
Для каждого GetShape в ActiveSheet.Shapes
  GetShape.Удалить
Следующий
Конец сабвуфера
Удалить все пустые строки в рабочем листе

Этот пример макроса удалит все пустые строки в рабочем листе.

Sub DeleteBlankRows()
Тусклый х как долго
С ActiveSheet
    Для x = . Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Шаг -1
        Если WorksheetFunction.CountA(.Rows(x)) = 0 Тогда
            ActiveSheet.Rows(x).Удалить
        Конец, если
    Следующий
Конец с
Конец сабвуфера
Выделение повторяющихся значений в выделенном фрагменте

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

' Выделить повторяющиеся значения в выборе
Sub HighlightDuplicateValues ​​()
    Dim myRange As Range
    Затемнить ячейку как диапазон
    
    Установите myRange = Выбор
    
    Для каждой ячейки в моем диапазоне
        Если WorksheetFunction.CountIf(myRange, cell.Value) > 1 Тогда
            ячейка.Interior.ColorIndex = 36
        Конец, если
    Следующая ячейка
Конец сабвуфера
Подсветка отрицательных чисел

Этот макрос автоматизирует задачу выделения отрицательных чисел.

' Выделить отрицательные числа
Sub HighlightNegativeNumbers()
    Dim myRange As Range
    Затемнить ячейку как диапазон
    
    Установите myRange = Выбор
    
    Для каждой ячейки в моем диапазоне
        Если ячейка. Значение < 0 Тогда
            ячейка.Interior.ColorIndex = 36
        Конец, если
    Следующая ячейка
Конец сабвуфера
Подсветка альтернативных строк

Этот макрос полезен для выделения альтернативных строк.

' Выделить альтернативные строки
ПодсветкаAlternateRows()
    Затемнить ячейку как диапазон
    Dim myRange As Range
    
    мой диапазон = выбор
    
    Для каждой ячейки в myRange.Rows
        Если Не Application.CheckSpelling(Word:=cell.Text), то
            ячейка.Interior.ColorIndex = 36
        Конец, если
    Следующая ячейка
Конец сабвуфера
Выделение пустых ячеек в выделенном фрагменте

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

' Выделить все пустые ячейки в выделении
Подсветка пустых ячеек ()
    Dim rng As Range
    
    Установить rng = Выбор
    rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbCyan
    
Конец сабвуфера

Примеры макросов Excel VBA — скачать бесплатно

Мы создали бесплатную надстройку VBA (Macros) Code Examples. Надстройка содержит более 100 готовых к использованию примеров макросов, включая приведенные выше примеры макросов!

Часто задаваемые вопросы о макросах Excel/VBA

Как писать код VBA (макросы) в Excel?

Чтобы написать код VBA в Excel, откройте редактор VBA (ALT + F11). Введите «Sub HelloWorld», нажмите Enter, и вы создали макрос! ИЛИ Скопируйте и вставьте одну из процедур, перечисленных на этой странице, в окно кода.

Что такое Excel VBA?

VBA — это язык программирования, используемый для автоматизации Excel.

Как использовать VBA для автоматизации Excel?

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

Практика VBA

Вы можете попрактиковаться в VBA с помощью нашего интерактивного учебного пособия по VBA.

Шпаргалки по VBA — списки команд и синтаксиса