wwwoldi.ru

About Me

Dashy

Hello word I am dashy Guy

Разное

Примеры vba excel: 18 готовых макросов VBA Excel

alexxlab
No Comments

Содержание

Примеры макросов Excel — Microsoft Excel для начинающих

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

Макрос 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

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

Готовые программы Vba. Примеры, задачи.

Готовые программы Vba. Примеры, задачи.

  1. Главная
  2. Готовые программы Vba
  • Раскрасить ячейки по диапазону значений после их рандомного заполнения
  • Матрицы, функции, подпрограммы и прочее
  • Все слова, длиной более пяти символов, начинать с прописной буквы
  • Выяснить, какое число встречается раньше — положительное или отрицательное
  • Ошибка «несовпадение типа» при вызове процедуры
  • Найти значение функции
  • Как открыть ссылку, находящуюся в ячейке листа Ексель, через send в НТТР запросе?
  • Сделать прозрачным ряд данных на графике
  • Excel macro Снять фильтры со всех листов в куче файлов
  • Вычислить число π пользуясь рядом Грегори
  • Excel (VBA). Посчитать значение функции sin(x)*cos(x)
  • Как задать размер массива через переменную?
  • Не обновляется usedrange листа
  • Создание кроссворда на VBA
  • Сколькими способами можно разложить на равные кучки 36 орехов?
  • Спойлер в Ворде
  • Запуск процедуры SQL Server через VBA
  • VBA-Матрица,диагональ(главная,побочная)
  • Удалить кавычки в строке
  • Создание макроса
  • Среднее арифметическое в строке с отрицательным элементом
  • Выборка уникальных дат из строк листа «l1» и помещение их в лист «l3», Не получается. На листе «l3» вставляет
  • MS Word(2010) Сохранить файл FSO
  • Макрос на поиск и замены значения
  • В listbox.multiselect отзывается лишь последний выбранный элемент
  • слова заканчиваются на заданую букву
  • Вывод сообщений на VBA в консольное окно
  • Как привязать к «выделенной ячейке» закомментированный код?
  • Нахождение двузначных чисел
  • Поиск файла в папке
  • Обработка части текста из ячейки ворда
  • VBA парсинг и импорт в столбец excel
  • Через Ексель Открыть файл ВОРД и сохранить файл под определенным именем
  • Уровнение с экспонентой
  • Макрос на кнопке по .OnAction
  • VBA Access 2016. Работа с Recordset
  • Макрос подсчета объединенных ячеек
  • Объединить 2 макроса
  • Что не так с кодом?
  • Макрос выделения диапазона ячеек-объединение их в одну-переход на след.строку-повтор пред.действия
  • Макрос который создает папку!
  • Не работает цикл для вывода записей из Access в Excel
  • VBA EXCEL: Собрать кучу файлов в один
  • Subscript out of range
  • Создание и проверка даты рождения
  • Ускорение макроса удаления ячеек
  • Сокрытие MediaPlayer на листе
  • Разбиение одномерного массива на двумерный
  • Удаление лишних символов
  • (VBA Excel) Цвет текста примечания

VBA Excel. Содержание рубрики

Содержание рубрики VBA Excel на сайте «Время не ждёт». Систематизация статей по тематическим группам для ускорения поиска нужной информации по заданной теме. Ссылки открываются в новом окне.

Знакомство с VBA Excel
Методы VBA Excel
Объект Range в VBA Excel
Объекты VBA Excel
Объект Collection (создание, методы, примеры)
Объект Dictionary (свойства, методы, примеры)
Объект FileSystemObject
Объект TextStream (свойства и методы)
Рабочая книга (открыть, создать новую, закрыть)
Рабочий лист (обращение, переименование, скрытие)
Рабочий лист (создание, копирование, удаление)
Регулярные выражения (объекты, свойства, методы)
Операторы в VBA Excel
Переменные в VBA Excel
Примеры кода VBA Excel
Прочее в VBA Excel
Работа с Word из кода VBA Excel
Редактор VBA Excel
События VBA Excel
Функции в VBA Excel
Изменение значений других ячеек из функции
Пользовательская функция (синтаксис, компоненты)
Удаление лишних пробелов (LTrim, RTrim, Trim)
Функции Left, Mid, Right (вырезать часть строки)
Функции Space, String и StrReverse
Функция Beep API (звуковой сигнал, мелодия)
Функция Choose (синтаксис, компоненты, примеры)
Функция Format (синтаксис, параметры, примеры)
Функция FreeFile
Функция InputBox (синтаксис, параметры, значения)
Функция InStr (синтаксис, параметры, примеры)
Функция Join (синтаксис, параметры, значения)
Функция MsgBox (синтаксис, параметры, значения)
Функция Replace (замена подстроки)
Функция Split (синтаксис, параметры, значения)
Функция StrConv (смена регистра букв)
Функция Switch (синтаксис, примеры)
Функция Timer (примеры)
Циклы в VBA Excel
Элементы управления в VBA Excel

Excel примеры vba Excelka.ru — все про Ексель

Макросы VBA Excel

Получение списка файлов в папке и подпапках средствами VBA

Функция VBA для получения списка файлов из папки,
с учётом выбранной глубины поиска в подпапках

Пример в файле FilenamesCollection.xls выводит список файлов на чистый лист новой книги (формируя заголовки)

Пример в файле FilenamesCollectionEx.xls более функционален — он, помимо списка файлов из папки, отображает размер файла, и дату его создания, а также формирует в ячейках гиперссылки на найденные файлы.

Вывод списка производится на лист запуска, параметры поиска файлов задаются в ячейках листа (см. скриншот)

  • 158 комментариев
  • Читать далее
  • 234694 просмотра
  • 2 прикреплённых файла

Выпадающий календарь в ячейке (выбор даты на листе Excel)

Надстройка samradDatePicker (русифицированная) для облегчения ввода даты в ячейки листа Excel.

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

Поместите файл надстройки из вложения в папку автозагрузки Excel (C:Program FilesMicrosoft OfficeOFFICExxXLSTART).

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

VBA Excel: примеры программ. Макросы в Excel

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

Что такое VBA

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

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

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

Объекты, коллекции, свойства и методы

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

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

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

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

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

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

Как начать

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

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

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

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

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

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

Макросы в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

VBA Excel: примеры программ. Макросы в Excel

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

VBA Excel: примеры программ. Макросы в Excel

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

Что такоe VBA

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

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

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

Объeкты, коллeкции, свойства и мeтоды

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

Главным из них являeтся Application, соотвeтствующий самой программe Excel. Затeм слeдуют Workbooks, Worksheets, а такжe Range. Напримeр, для обращeния к ячeйкe A1 на конкрeтном листe слeдуeт указать путь с учeтом иeрархии.

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

Слeдующee понятиe — свойства. Они являются нeобходимой характeристикой любого объeкта. Напримeр, для Range — это Value или Formula.

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

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

Как начать

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

Затeм нeобходимо пeрeйти в приложeниe VB, для чeго достаточно воспользоваться комбинациeй клавиш «Alt» и «F11». Далee:

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

Он выглядит слeдующим образом:

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

Тeпeрь вы можeтe написать любой код и создать для сeбя новый инструмeнт в VBA Excel (примeры программ см. далee). Конeчно, тeм, кто знаком с азами Visual Basic, будeт намного прощe. Однако дажe тe, кто их нe имeeт, при жeлании смогут освоиться достаточно быстро.

Макросы в Excel

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

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

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

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

Тeпeрь, когда ужe запущeна запись макроса, производят копированиe содeржимого какой-либо ячeйки в другую. Возвращаются на исходную пиктограмму. Нажимают на «Запись макроса». Это дeйствиe означаeт завeршeниe программки.

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

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

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

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

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

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

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

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

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

VBA-Урок 1. Что такое VBA. Основные понятия.

В данном разделе мы рассмотрим что такое VBA, посколько именно на нем и пишутся макросы.

Что такое VBA?

VBA — язык (расшифровывается как Visual Basic for Application) был разработан компанией Microsoft. Данный язык не является самостоятельным, а предназначен для автоматизации процессов в пакете MS Office. VBA широко используется в Excel, а также в Access, Word и других программах пакета.

VBA — простой язык программирования, которому может научиться любой желающий. Изучив его, вы сможет предоставлять команды Excel, что делать с колонками, строками, значениями в ячейках, перемещать/добавлять/сортировать листы, выводить заранее запрограммированные сообщения, писать свои формулы и функции и т.д. Суть языка заключается в оперировании объектами (что относит его к объектно-ориентированному программированию).

Чтобы работать с VBA кодом, нам нужен редактор, который уже установлен по умолчанию. Вы можете открыть его, нажав комбинацию клавиш » ALT + F11 «.

Объекты (Objects)

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

Главный объект это Application, что соответствует самой программе Excel. Далее следует Workbooks (книга), Worksheets (лист), Range (диапазон, или отдельная ячейка). Например, чтобы обратиться к ячейке «A1» на листе нам нужно будет прописать следующий путь с учетом иерархии:

Application.Workbooks(«Архив»).Worksheets(«Аркуш1»).Range(«A1»).

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

Коллекции (Collections)

В свою очередь объекты имеют «коллекции». Коллекция — это группа объектов одинакового класса. Отдельные элеметы коллекции являются также объектами. Так, объекты Worksheets являются элементами коллекции объекта Worksheet, который содержит также и другие коллекции и объекты:

  • ChartObjects (элемент коллекции объекта ChartObject)
  • Range
  • PageSetup
  • PivotTables (элемент коллекции объекта PivotTable).

Свойства (Properties)

Каждый объект имеет свойства . Например, объект Range имеет свойство Value или Formula.

Worksheets(“Sheet1”).Range(“A1”).Value або Worksheets(“Sheet1”).Range(“A1”).Formula

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

Также, через свойство Formula можно не только получить формулу, но и записать ее:

MsgBox Range(“A1”).Formula — получим сообщение с формулой в ячейке «А1«;

Range(“B12”).Formula = “=2+6*100” — вписываем формулу =2+6*100 в ячейку B12.

Методи (Methods)

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

Range(«A1»).Select или Cells(1, 1).Select

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

Selection.ClearContents

Здесь программа «берет» то, что мы выделили (Selection) и удаляет его содержимое (ClearContents ).

Сборник готовых макросов VBA

Представляю Вашему вниманию огромный сборник макросов и функций, все макросы сгруппированы по главам, для удобства добавил оглавление с гиперссылками.

P.S.: Где скачал не помню.

Запуск макроса с поиском ячейки
Запуск макроса при открытии книги
Запуск макроса при вводе в ячейку «2»
Запуск макроса при нажатии «Ентер»
Добавить в панель свою вкладку «Надстройки» (Формат ячейки)

Проверка наличия файла по указанному пути_1
Проверка наличия файла по указанному пути_2
Проверка наличия файла по указанному пути_3
Поиск нужного файла_1
Поиск нужного файла_2
Поиск нужного файла_3
Поиск нужного файла_4
Автоматизация удаления файлов
Произвольный текст в строке состояния
Восстановление строки состояния
Бегущая строка в строке состояния
Быстрое изменение заголовка окна
Быстрое изменение заголовка окна_2
Изменение заголовка окна (со скрытием названия файла)
Возврат к первоначальному заголовку
Что открыто в данный момент
Работа с текстовыми файлами
Запись и чтение текстового файла
Обработка нескольких текстовых файлов
Определение конца строки текстового файла
Копирование из текстового файла в эксель
Копирование содержимого в текстовый файл_1
Копирование содержимого в текстовый файл_2
Экспорт данных в HТМL
Создание резервных копий ценных файлов
Подсчет количества открытий файла
Вывод пути к файлу в активную ячейку
Копирование содержимого файла RTF в эксель
Копирование данных из закрытой книги
Извлечение данных из закрытого файла
Поиск слова в файлах
Создание текстового файла и ввод текста в файл
Создание текстового файла и ввод текста (определение конца файла)
Создание документов Word на основе таблицы Excel
Команды создания и удаления каталогов
Получение текущего каталога
Посмотреть все файлы в каталоге_1
Посмотреть все файлы в каталоге_2
Посмотреть все файлы в каталоге_3

Количество имен рабочей книги
Защита рабочей книги
Запрет печати книги
Открытие книги (или текстовых файлов)
Открытие книги и добавление в ячейку А1 текста
Сколько книг открыто
Закрытие всех книг
Закрытие рабочей книги только при выполнении условия
Сохранение рабочей книги с именем, представляющим собой текущую дату
Сохранена ли рабочая книга
Создать книгу с одним листом
Удаление ненужных имен
Быстрое размножение рабочей книги
Сортировка листов
Поиск максимального значения на всех листах книги
Проверка наличия защиты рабочего листа
Список отсортированных листов
Создать новый лист_1
Копирование листа в книге
Копирование листа в новую книгу (создается)
Перемещение листа в книге
Перемещение нескольких листов в новую книгу
Заменить существующий файл
Вставка колонтитула с именем книги, листа и текущей датой
Существует ли лист
Существует ли лист_2
Вывод количества листов в активной книге
Вывод количества листов в активной книге в виде гиперссылок
Вывод имен активных листов по очереди
Вывод имени и номеров листов текущей книги
Сделать лист невидимым
Сколько страниц на всех листах?
Копирование строк на другой лист
Копирование столбцов на другой лист
Подсчет количества ячеек, содержащих указанные значения_1
Подсчет количества ячеек в диапазоне, содержащих указанные значения_2
Подсчет количества видимых ячеек в диапазоне
Определение количества ячеек в диапазоне и суммы их значений
Подсчет количества ячеек
Автоматический пересчет данных таблицы при изменении ее значений
Ввод данных в ячейки
Ввод данных с использованием формул
Ввод текстоввых данных в ячейки
Вывод в ячейки названия книги, листа и количества листов
Удаление пустых строк_1
Удаление пустых строк_2
Удаление пустых строк_3
Удаление строки по условию
Удаление используемых скрытых строк или строк с нулевой высотой
Удаление дубликатов по маске
Выделение диапазона над текущей ячейкой
Выделение диапазона над текущей ячейкой_2
Выделение отрицательных значений
Выделение диапазона и использование абсолютных адресов
Выделение ячеек через интервал_2
Движение по ячейкам
Поиск ближайшей пустой ячейки столбца
Поиск максимального значения
Поиск и замена по шаблону
Поиск значения с отображением результата в отдельном окне
Поиск с выделением найденных данных_1
Поиск с выделением найденных данных_2
Поиск по условию в диапазоне
Поиск последней непустой ячейки диапазона
Поиск последней непустой ячейки столбца
Поиск последней непустой ячейки строки
Поиск ячейки синего цвета в диапазоне
Поиск наличия значения в столбце
Поиск совпадений в диапазоне
Поиск ячейки в диапазоне_1
Поиск ячейки в диапазоне_2
Поиск приближенного значения в диапазоне
Поиск начала и окончания диапазона, содержащего данные
Автоматическая замена значений
Быстрое заполнение диапазона (массив)
Заполнение через интервал(массив)
Заполнение указанного диапазона(массив)
Заполнение диапазона(массив)
Расчет суммы первых значений диапазона
Размещение в ячейке электронных часов
«Будильник»
Адрес активной ячейки
Координаты активной ячейки
Формула активной ячейки
Получение из ячейки формулы
Тип данных ячейки
Вывод адреса конца диапазона
Получение информации о выделенном диапазоне
Создание изменяемого списка (таблица)
Умножение выделенного диапазона на 2
Одновременное умножение всех данных диапазона
Деление диапазона на 100
Суммирование данных только видимых ячеек
Сумма ячеек с числовыми значениями
При суммировании — курсор внутри диапазона
Начисление процентов в зависимости от суммы_1
Начисление процентов в зависимости от суммы_2
Начисление процентов в зависимости от суммы_3
Сводный пример расчета комиссионного вознаграждения
Движение по диапазону
Сдвиг от выделенной ячейки
Создание заливки диапазона
Подбор параметра ячейки
Разбиение диапазона
Объединение данных диапазона
Объединение данных диапазона_2
Узнать максимальную колонку или строку.
Ограничение возможных значений диапазона
Тестирование скорости чтения и записи диапазонов
Открыть MsgBox при выборе ячейки
Скрытие строки
Скрытие нескольких строк
Скрытие столбца
Скрытие нескольких столбцов
Скрытие строки по имени ячейки
Скрытие нескольких строк по адресам ячеек
Скрытие столбца по имени ячейки
Скрытие нескольких столбцов по адресам ячеек
Мигание ячейки

Вывод на экран всех примечаний рабочего листа
Функция извлечения комментария
Список примечаний защищенных листов
Перечень примечаний в отдельном списке_1
Перечень примечаний в отдельном списке_2
Перечень примечаний в отдельном списке_3
Подсчет количества примечаний_1
Подсчет примечаний_3
Выделение ячеек с примечаниями
Отображение всех примечаний
Изменение цвета примечаний
Добавление примечаний
Добавление примечаний в диапазон по условию
Перенос комментария в ячейку и обратно
Перенос значений из ячейки в комментарий_1
Перенос значений из ячейки в комментарий_2

Дополнение панели инструментов
Добавление кнопки на панель инструментов
Панель с одной кнопкой
Панель с двумя кнопками
Создание панели справа
Вызов предварительного просмотра
Создание пользовательского меню (вариант 1)
Создание пользовательского меню (вариант 2)
Создание пользовательского меню (вариант 3)
Создание пользовательского меню (вариант 4)
Создание пользовательского меню (вариант 5)
Создание списка пунктов главного меню Excel
Создание списка пунктов контекстных меню
Отображение панели инструментов при определенном условии
Скрытие и отображение панелей инструментов
Создать подсказку к моим кнопкам
Создание меню на основе данных рабочего листа
Создание контекстного меню
Блокировка контекстного меню
Добавление команды в меню Сервис
Добавление команды в меню Вид
Создание панели со списком
Мультфильм с помощником в главной роли
Дополнение помощника текстом, заголовком, кнопкой и значком
Новые параметры помощника
Использование помощника для выбора цвета заливки

Функция INPUTBOX (через ввод значения)
Настройка ввода данных в диалоговом окне
Открытие диалогового окна (“Открыть файл”)_1
Вызов броузера из Экселя
Диалоговое окно ввода данных
Значения по умолчанию

Вывод списка доступных шрифтов
Выбор из текста всех чисел
Прописная буква только в начале текста
Подсчет количества повторов искомого текста
Выделение из текста произвольного элемента
Отображение текста «задом наперед»
Запуск таблицы символов из Excel

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

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

Программа для составления кроссвордов
Игра «Минное поле»
Игра «Угадай животное»
Расчет на основании ячеек определенного цвета

Вызов функциональных клавиш
Расчет среднего арифметического значения
Перевод чисел в «деньги»
Поиск ближайшего понедельника
Подсчет количества полных лет
Расчет средневзвешенного значения
Преобразование номера месяца в его название
Использование относительных ссылок
Преобразование таблицы Excel в HТМL-формат
Генератор случайных чисел
Случайные числа — на основании диапазона
Применение функции без ввода ее в ячейку
Подсчет именованных объектов
Включение автофильтра с помощью макроса
Создание бегущей строки
Создание бегущей картинки
Вращающиеся автофигуры
Вызов таблицы цветов
Создание калькулятора
Склонение фамилии, имени и отчества

Вывод даты и времени_1
Вывод даты и времени_2
Получение системной даты
Извлечение даты и часов
Функция ДатаПолная

К сообщению приложен файл: macros.rar(83Kb)

ГЛАВА 1. МАКРОСЫ

Запуск макроса с поиском ячейки

Запуск макроса при открытии книги

Запуск макроса при вводе в ячейку «2»

Запуск макроса при нажатии «Ентер»

Добавить в панель свою вкладку «Надстройки» (Формат ячейки)

ГЛАВА 2. РАБОТА С ФАЙЛАМИ (Т.Е.ОБМЕН ДАННЫМИ С ТХТ, RTF, XLS И Т.Д.)

Проверка наличия файла по указанному пути_1

Проверка наличия файла по указанному пути_2

Проверка наличия файла по указанному пути_3

Поиск нужного файла_1

Поиск нужного файла_2

Поиск нужного файла_3

Поиск нужного файла_4

Автоматизация удаления файлов

Произвольный текст в строке состояния

Восстановление строки состояния

Бегущая строка в строке состояния

Быстрое изменение заголовка окна

Быстрое изменение заголовка окна_2

Изменение заголовка окна (со скрытием названия файла)

Возврат к первоначальному заголовку

Что открыто в данный момент

Работа с текстовыми файлами

Запись и чтение текстового файла

Обработка нескольких текстовых файлов

Определение конца строки текстового файла

Копирование из текстового файла в эксель

Копирование содержимого в текстовый файл_1

Копирование содержимого в текстовый файл_2

Экспорт данных в HТМL

Создание резервных копий ценных файлов

Подсчет количества открытий файла

Вывод пути к файлу в активную ячейку

Копирование содержимого файла RTF в эксель

Копирование данных из закрытой книги

Извлечение данных из закрытого файла

Поиск слова в файлах

Создание текстового файла и ввод текста в файл

Создание текстового файла и ввод текста (определение конца файла)

Создание документов Word на основе таблицы Excel

Команды создания и удаления каталогов

Получение  текущего каталога

Посмотреть все файлы в каталоге_1

Посмотреть все файлы в каталоге_2

Посмотреть все файлы в каталоге_3

ГЛАВА 3. РАБОЧАЯ ОБЛАСТЬ MICROSOFT EXCEL

Количество имен рабочей книги

Защита рабочей книги

Запрет печати книги

Открытие книги (или текстовых файлов)

Открытие книги и добавление в ячейку А1 текста

Сколько книг открыто

Закрытие всех книг

Закрытие рабочей книги только при выполнении условия

Сохранение рабочей книги с именем, представляющим собой текущую дату

Сохранена ли рабочая книга

Создать книгу с одним листом

Удаление ненужных имен

Быстрое размножение рабочей книги

Сортировка листов

Поиск максимального значения на всех листах книги

Проверка наличия защиты рабочего листа

Список отсортированных листов

Создать новый лист_1

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

Копирование листа в новую книгу (создается)

Перемещение листа в книге

Перемещение нескольких листов в новую книгу

Заменить существующий файл

Вставка колонтитула с именем книги, листа и текущей датой

Существует ли лист

Существует ли лист_2

Вывод количества листов в активной книге

Вывод количества листов в активной книге в виде гиперссылок

Вывод имен активных листов по очереди

Вывод имени и номеров листов текущей книги

Сделать лист невидимым

Сколько страниц на всех листах?

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

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

Подсчет количества ячеек, содержащих указанные значения_1

Подсчет количества ячеек в диапазоне, содержащих указанные значения_2

Подсчет количества видимых ячеек в диапазоне

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

Подсчет количества ячеек

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

Ввод данных в ячейки

Ввод данных с использованием формул

Ввод текстоввых данных в ячейки

Вывод в ячейки названия книги, листа и количества листов

Удаление пустых строк_1

Удаление пустых строк_2

Удаление пустых строк_3

Удаление строки по условию

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

Удаление дубликатов по маске

Выделение диапазона над текущей ячейкой

Выделение диапазона над текущей ячейкой_2

Выделение отрицательных значений

Выделение диапазона и использование абсолютных адресов

Выделение ячеек через интервал_2

Движение по ячейкам

Поиск ближайшей пустой ячейки столбца

Поиск максимального значения

Поиск и замена по шаблону

Поиск значения с отображением результата в отдельном окне

Поиск с выделением найденных данных_1

Поиск с выделением найденных данных_2

Поиск по условию в диапазоне

Поиск последней непустой ячейки диапазона

Поиск последней непустой ячейки столбца

Поиск последней непустой ячейки строки

Поиск ячейки синего цвета в диапазоне

Поиск наличия значения в столбце

Поиск совпадений в диапазоне

Поиск ячейки в диапазоне_1

Поиск  ячейки в диапазоне_2

Поиск приближенного значения в диапазоне

Поиск начала и окончания диапазона, содержащего данные

Автоматическая замена значений

Быстрое заполнение диапазона (массив)

Заполнение через интервал(массив)

Заполнение указанного диапазона(массив)

Заполнение диапазона(массив)

Расчет суммы первых значений диапазона

Размещение в ячейке электронных часов

«Будильник»

Адрес активной ячейки

Координаты активной ячейки

Формула активной ячейки

Получение из ячейки формулы

Тип данных ячейки

Вывод адреса конца диапазона

Получение информации о выделенном диапазоне

Создание изменяемого списка (таблица)

Умножение выделенного диапазона на 2

Одновременное умножение всех данных диапазона

Деление диапазона на 100

Суммирование данных только видимых ячеек

Сумма ячеек с числовыми значениями

При суммировании — курсор внутри диапазона

Начисление процентов в зависимости от суммы_1

Начисление процентов в зависимости от суммы_2

Начисление процентов в зависимости от суммы_3

Сводный пример расчета комиссионного вознаграждения

Движение по диапазону

Сдвиг от выделенной ячейки

Создание заливки диапазона

Подбор параметра ячейки

Разбиение диапазона

Объединение данных диапазона

Объединение данных диапазона_2

Узнать максимальную колонку или строку.

Ограничение возможных значений диапазона

Тестирование скорости чтения и записи диапазонов

Открыть MsgBox при выборе ячейки

Скрытие строки

Скрытие нескольких строк

Скрытие столбца

Скрытие нескольких столбцов

Скрытие строки по имени ячейки

Скрытие нескольких строк по адресам ячеек

Скрытие столбца по имени ячейки

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

Мигание ячейки

ГЛАВА 4. РАБОТА С ПРИМЕЧАНИЯМИ

Вывод на экран всех примечаний рабочего листа

Функция извлечения комментария

Список примечаний защищенных листов

Перечень примечаний в отдельном списке_1

Перечень примечаний в отдельном списке_2

Перечень примечаний в отдельном списке_3

Подсчет количества примечаний_1

Подсчет примечаний_3

Выделение ячеек с примечаниями

Отображение всех примечаний

Изменение цвета примечаний

Добавление примечаний

Добавление примечаний в диапазон по условию

Перенос комментария в ячейку и обратно

Перенос значений из ячейки в комментарий_1

Перенос значений из ячейки в комментарий_2

ГЛАВА 5 . ПОЛЬЗОВАТЕЛЬСКИЕ ВКЛАДКИ НА ЛЕНТЕ

Дополнение панели инструментов

Добавление кнопки на панель инструментов

Панель с одной кнопкой

Панель с двумя кнопками

Создание панели справа

Вызов предварительного просмотра

Создание пользовательского меню (вариант 1)

Создание пользовательского меню (вариант 2)

Создание пользовательского меню (вариант 3)

Создание пользовательского меню (вариант 4)

Создание пользовательского меню (вариант 5)

Создание списка пунктов главного меню Excel

Создание списка пунктов контекстных меню

Отображение панели инструментов при определенном условии

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

Создать подсказку к моим кнопкам

Создание меню на основе данных рабочего листа

Создание контекстного меню

Блокировка контекстного меню

Добавление команды в меню Сервис

Добавление команды в меню Вид

Создание панели со списком

Мультфильм с помощником в главной роли

Дополнение помощника текстом, заголовком, кнопкой и значком

Новые параметры помощника

Использование помощника для выбора цвета заливки

ГЛАВА 6. ДИАЛОГОВЫЕ ОКНА

Функция INPUTBOX (через ввод значения)

Настройка ввода данных в диалоговом окне

Открытие диалогового окна (“Открыть файл”)_1

Вызов броузера из Экселя

Диалоговое окно ввода данных

Значения по умолчанию

ГЛАВА 7.ФОРМАТИРОВАНИЕ ТЕКСТА. ТАБЛИЦЫ. ГРАНИЦЫ И ЗАЛИВКА.

Вывод списка доступных шрифтов

Выбор из текста всех чисел

Прописная буква только в начале текста

Подсчет количества повторов искомого текста

Выделение из текста произвольного элемента

Отображение текста «задом наперед»

Запуск таблицы символов из Excel

ГЛАВА 8 ИНФОРМАЦИЯ О ПОЛЬЗОВАТЕЛЕ, КОМПЬЮТЕРЕ, ПРИНТЕРЕ И Т.Д.

Получить имя пользователя

Вывод разрешения монитора

Получение информации об используемом принтере

Просмотр информации о дисках компьютера

ГЛАВА 9. ДИАГРАММЫ

Построение диаграммы с помощью макроса

Сохранение диаграммы в отдельном файле

Построение и удаление диаграммы нажатием одной кнопки

Применение случайной цветовой палитры

Эффект прозрачности диаграммы

Построение диаграммы на основе данных нескольких рабочих листов

Создание подписей к данным диаграммы

ГЛАВА 10. РАЗНЫЕ ПРОГРАММЫ.

Программа для составления кроссвордов

Игра «Минное поле»

Игра «Угадай животное»

Расчет на основании ячеек определенного цвета

ГЛАВА 11. ДРУГИЕ ФУНКЦИИ И МАКРОСЫ

Вызов функциональных клавиш

Расчет среднего арифметического значения

Перевод чисел в «деньги»

Поиск ближайшего понедельника

Подсчет количества полных лет

Расчет средневзвешенного значения

Преобразование номера месяца в его название

Использование относительных ссылок

Преобразование таблицы Excel в HТМL-формат

Генератор случайных чисел

Случайные числа — на основании диапазона

Применение функции без ввода ее в ячейку

Подсчет именованных объектов

Включение автофильтра с помощью макроса

Создание бегущей строки

Создание бегущей картинки

Вращающиеся автофигуры

Вызов таблицы цветов

Создание калькулятора

Склонение фамилии, имени и отчества

ГЛАВА 12. ДАТА И ВРЕМЯ

Вывод даты и времени_1

Вывод даты и времени_2

Получение системной даты

Извлечение даты и часов

Функция ДатаПолная

 

ГЛАВА 1. МАКРОСЫ

Запуск макроса с поиском ячейки

‘ Sub  GotoFixedCell:

‘ Делает активной ячейку, содержащую значение vVariant на

‘ рабочем листе sSheetName в активной рабочей книге.

‘

‘ Note: Содержимое ячеек интерпретируется как ‘значение’!

‘

Public Sub GotoFixedCell(vValue As Variant, sSheetName As String)

  Dim c As Range, cStart As Range, cForFind As Range

  Dim i As Integer

 

  On Error GoTo errhandle:

 

  Set cForFind = Worksheets(sSheetName).Cells   ‘ Диапазон поиска

     With cForFind

       Set c = .Find(What:=vValue, After:=ActiveCell, LookIn:=xlValues, _

                LookAt:= xlРart, SearchOrder:=xlByRows,_

                SearchDirection:=xlNext, MatchCase:=False)

       Set cStart = c

       While Not c Is Nothing

         Set c = .FindNext(c)

         If c.Address = cStart.Address Then

           c.Select

           Exit Sub

         End If

       Wend

     End With

  Exit Sub

  errНandle:

    MsgBox Err.Descriрtion, vbExclamation, «Error #» & Err.Number

End Sub

Запуск макроса при открытии книги

Sub Auto_Oрen()

Запуск макроса при вводе в ячейку «2»

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim w As Object

    ‘On Error Resume Next

    If Range(«A1»).Value = 2 Then

        MsgBox «Ох! Значение ячейки стало равным 2-м!»

        MsgBox «Я попробую сейчас открыть модуль с процедурой, которая все это делает!»

        Application.VBE.MainWindow.SetFocus

        Application.VBE.Windows(1).SetFocus

        SendKeys «{F7}», True

    End If

End Sub

Запуск макроса при нажатии «Ентер»

в модуле листа

Private Sub Worksheet_Selectiоnchange(ByVal Target As Range)

Application.OnKey «{~}», «StartEnter»

End Sub

 

в модуле книги

Sub StartEnter()

MsgBox («sadfsdfsf»)

End Sub

Добавить в панель свою вкладку «Надстройки» (Формат ячейки)

Код в модуле рабочего листа

Sub Worksheet_Change(ByVal Target As Excel.Range)

   Call updаtеToolbar

End Sub

 

Sub Worksheet_Selectiоnchange(ByVal Target As Excel.Range)

   Call updаtеToolbar

End Sub

Листинг 2.43. Код в стандартном модуле

Sub FastChangeNumberFormat()

   Dim bar As CommandBar

   Dim button As CommandBarButton

 

   ‘ Удаление существующей панели инструментов (если она есть)

   On Error Resume Next

   CommandBars(«Числовой формат»).Delete

   On Error GoTo 0

 

   ‘ Формирование новой панели

   Set bar = CommandBars.Add

   With bar

      .Name = «Числовой формат»

      .Visible = True

   End With

   ‘ Создание кнопки

   Set button = CommandBars(«Числовой формат»).Controls.Add _

    (Type:=msoControlButton)

   With button

      .Caption = «»

      .OnAction = «ChangeNumFormat»

      .TooltipText = «Щелкните для изменения числового формата»

      .Style = msoButtonCaption

   End With

   ‘ Обновление созданной панели инструментов

   Call updаtеToolbar

End Sub

 

Sub updаtеToolbar()

   ‘ Обновление панели инструментов (если она создана)

   On Error Resume Next

   ‘ Изменение заголовка кнопки (на название формата выделенной ячейки)

   CommandBars(«Числовой формат»).Controls(1).Caption = _

    ActiveCell.NumberFormat

End Sub

 

Sub ChangeNumFormat()

   ‘ Отображение диалогового окна изменения формата ячейки

   Application.Dialogs(xlDialogFormatNumber).Show

   Call updаtеToolbar

End Sub

 

ГЛАВА 2. РАБОТА С ФАЙЛАМИ (Т.Е.ОБМЕН ДАННЫМИ С ТХТ, RTF, XLS И Т.Д.)

Проверка наличия файла по указанному пути_1

Sub VerifyFileLocation()

   Dim strFileName As String

   Dim strFileTitle As String

   ‘ Имя и путь искомого файла

   strFileTitle = «primer.xls»

   strFileName = «C:\Документы\primer.xls»

   ‘ Проверка наличия файла (функция Dir возвращает пустую _

    строку, если по указанному пути файл обнаружить не удалось)

   If Dir(strFileName) <> «» Then

      MsgBox «Файл » & strFileTitle & » найден»

   Else

      MsgBox «Файл » & strFileTitle & » не найден»

   End If

End Sub

Проверка наличия файла по указанному пути_2

Sub VerifyFileLocation1()

   Dim strFileName As String

   ‘ Имя искомого файла

   strFileName = «C:\Документы\primer.xls»

   ‘ Проверка наличия файла (функция Dir возвращает пустую _

    строку, если по указанному пути файл обнаружить не удалось)

   If Dir(strFileName) <> «» Then

      MsgBox «Файл » & strFileName & » найден»

   Else

      MsgBox «Файл » & strFileName & » не найден»

   End If

End Sub

Проверка наличия файла по указанному пути_3

Sub Check_Disk()

On Error Resume Next

If Dir(«\\192.168.1.200\c\», vbSystem) <> «» Then

If Err = 52 Then

Err.Clear

MsgBox «Диска нет!», 48, «Ошибка»

Exit Sub

End If

If Err <> 0 Then

MsgBox «Произошло ошибка!», 48, «Ошибка»

Exit Sub

Else

On Error GoTo 0

MsgBox «Диск есть!», 64, «»

End If

End If

End Sub

 

Поиск нужного файла_1

Sub FileSearch()

   Dim strFileName As String

   Dim strFolder As String

   Dim strFullPath As String

 

   ‘ Задание имени папки для поиска

   strFolder = InputBox(«Определите папку:»)

   If strFolder = «» Then Exit Sub

   ‘ Задание имени файла для поиска

   strFileName = Application.InputBox(«Введите имя файла:»)

   If strFileName = «» Then Exit Sub

   ‘ При необходимости дополняем имя папки «\»

   If Right(strFolder, 1) <> «\» Then strFolder = strFolder & «\»

 

   ‘ Полный путь файла

   strFullPath = strFolder & strFileName

 

   ‘ Вывод окна с отчетом о поиске средствами VBA

   MsgBox «Использование команды VBA…» & vbCrLf & vbCrLf & _

    dhSearchVBA(strFullPath), vbInformation, strFullPath

   ‘ Вывод окна с отчетом о поиске средствами объекта FileSearch

   MsgBox «Использование объекта FileSearch…» & vbCrLf & _

    vbCrLf & dhSearchFileSearch(strFolder, strFileName), vbInformation, _

    strFullPath

   ‘ Вывод окна с отчетом о поиске средствами объекта _

    FileSystemObject

   MsgBox «Использование объекта FileSystemObject…» & vbCrLf & _

    vbCrLf & dhSearchFileSystemObject(strFullPath), vbInformation, _

    strFullPath

End Sub

Поиск нужного файла_2

 

Function dhSearchVBA(varFullPath As Variant) As Boolean

   ‘ Использование команды VBA

   dhSearchVBA = Dir(varFullPath) <> «»

End Function

Поиск нужного файла_3

 

Function dhSearchFileSearch(varFolder As Variant, varFileName _

 As Variant) As Boolean

   ‘ Использование объекта FileSearch

   With Application.FileSearch

      ‘ Создание нового поиска

      .NewSearch

      ‘ Имя для поиска

      .FileName = varFileName

      ‘ Папка поиска

      .LookIn = varFolder

      ‘ Собственно поиск

      .Execute

      dhSearchFileSearch = .FoundFiles.Count <> 0

   End With

End Function

Поиск нужного файла_4

 

Function dhSearchFileSystemObject(varFullPath As Variant) As Boolean

   Dim objFSObject As Object

   ‘ Использование объекта FileSystemObject

   Set objFSObject = CreateObject(«sсriрting.FileSystemObject»)

   dhSearchFileSystemObject = objFSObject.FileExists(varFullPath)

End Function

Автоматизация удаления файлов

Листинг 3.51. Удаление файла

Sub DeleteFile()

   Kill «C:\Документы\primer.xls»

End Sub

Листинг 3.52. Удаление группы файлов

Sub DeleteFiles()

   ‘ Удаление всех файлов с расширением XLS из заданной папки

   Kill «C:\Документы» & «*.xls»

End Sub

 

Произвольный текст в строке состояния

Sub ChangeStatusBarText()

   Application.StatusBar = «Как надоело работать!!!»

End Sub

Восстановление строки состояния

Sub ReturnStatusBarText()

   Application.StatusBar = False

End Sub

Бегущая строка в строке состояния

Sub MovingTextInStatusBar()

   Dim intSpaces As Integer

   ‘ Изменение количества пробелов в начале строки (от 20 до 0) — _

    строка бежит (скорее, ползет) влево

   For intSpaces = 20 To 0 Step -1

      ‘ Запись текста в строку состояния

      Application.StatusBar = Space(intSpaces) & «Как надоело работать!!!»

      ‘ Выдерживаем паузу

      Application.Wait Now + TimeValue(«00:00:01»)

      ‘ Дадим Excel обработать пользовательский ввод

      DoEvents

   Next

   Application.StatusBar = False

End Sub

Быстрое изменение заголовка окна

Sub NewTitle()

   Application.Caption = «Какая хорошая погода»

End Sub

Быстрое изменение заголовка окна_2

VBA Excel. Правильные имена переменных и процедур

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

Правильные имена в VBA Excel

Правила наименования переменных, констант, процедур и аргументов:

  • Имя может состоять из букв, цифр и знака подчеркивания (_). Пробел и другие специальные символы не допускаются, кроме шести символов-суффиксов для переменных.
  • Первым знаком имени должна быть буква. Длина имени ограничена 255 знаками.
  • Не следует использовать имена, совпадающие с именами встроенных функций, операторов, методов, свойств и констант VBA Excel.
  • Нельзя допускать повторения имен внутри одной области видимости (действия) переменных, например, внутри одной процедуры.

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

Примеры имен

Переменные: x, y, z, a1, a2, a3, summa, myRange, my_Currency;

Подпрограммы: Primer1(), Raschet_Stazha(), SozdaniyeTablitsy();

Функции с аргументом: СуммаПрописью(x As Double) As String, ПоследнийДеньМесяца(x As Date) As Date.

Области видимости переменных

ПеременнаяОбласть действияСпособ объявления
Локальнаяотдельная процедуравнутри процедуры с помощью ключевого слова Dim или Static
Модульнаяотдельный модульперед первой процедурой в модуле с помощью ключевого слова Dim или Private
Глобальнаявсе стандартные модулиперед первой процедурой в стандартном модуле с помощью ключевого слова Public

Суффиксы в именах переменных

Переменные в VBA Excel могут быть объявлены с одним из следующих типов данных: Byte, Boolean, Integer, Long, Single, Double, Currency, Date, String (для строк переменной или фиксированной длины), Object или Variant. Если при объявлении переменной тип данных не указан, по умолчанию присваивается тип Variant.

Шесть типов данных имеют соответствующие им символы, которые можно использовать при объявлении переменных, добавляя их в конце имени как суффиксы. Переменная будет объявлена с соответствующим символу типом данных.

Символы, назначенные типам данных:

  • % – Integer;
  • & – Long;
  • ! – Single;
  • # – Double;
  • @ – Currency;
  • $ – String.

Примеры объявления переменных

Пример 1

‘Объявление переменных без суффикса

Dim myInteger as Integer, mySingle as Single, myCurrency as Currency

‘Объявление переменных с суффиксом

Dim myInteger%, mySingle!, myCurrency@

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

Пример 2

Если в модуле не используются операторы Option Explicit и Dim (Static, Public), то суффиксы являются единственным способом назначить переменной тип данных. То, что суффиксы работают и в этом случае, проверим на следующей процедуре:

Sub Primer()

a$ = «привет»

a = StrConv(a, 1)

End Sub

Указываем с помощью суффикса $, что переменная a является строковой, и ставим паузу на операторе End Sub. Запускаем процедуру:

На изображении видно, что переменной a присвоен тип данных String (окно Locals). Теперь запускаем ту же процедуру, но с переменной без суффикса:

Как и ожидалось, переменной a присвоен тип данных по умолчанию – Variant. Через косую черту указан тип данных, который VBA Excel идентифицировал у содержимого переменной a.

VBA Excel. Объект Collection (создание, методы, примеры)

Создание объекта Collection с помощью кода VBA Excel. Методы коллекции и синтаксис выражений с ними. Свойство Count и примеры кода.

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

Создание объекта Collection

Создать новый экземпляр Collection в коде VBA Excel можно двумя строками:

Dim myColl As Object

Set myColl = New Collection

или одной строкой:

Dim myColl As New Collection

Лист автоматической вставки объектов, методов и свойств (лист подсказок) предоставит при написании кода VBA Excel простой доступ к методам Add, Item, Remove и свойству Count объекта Collection:

Лист подсказок отображается автоматически после ввода точки или, в иных случаях, вызывается сочетанием клавиш «Ctrl+Пробел».

Методы и свойство коллекции

Метод Add

Метод Add добавляет новый элемент в объект Collection.

Синтаксис метода Add:

Collection.Add Элемент, [Ключ], [До], [После]

Компоненты метода Add:

  1. Collection – обязательный компонент, представляющий выражение (переменную), возвращающее объект Collection.
  2. Элемент – обязательный аргумент, представляющий выражение любого типа, возвращающее элемент, который необходимо добавить в коллекцию.
  3. Ключ – необязательный аргумент, представляющий строковое выражение, задающее уникальный ключ, который может использоваться вместо индекса позиции для доступа к элементу коллекции.
  4. До* – необязательный аргумент, указывающий на позицию существующего элемента в коллекции, перед которым будет добавлен новый элемент.
  5. После* – необязательный аргумент, указывающий на позицию существующего элемента в коллекции, после которого будет добавлен новый элемент.

* Аргументы «До» и «После» не могут применяться одновременно. Если аргументу «До» или «После» присвоено числовое значение, оно должно быть в пределах диапазона от 1 до значения свойства Collection.Count. Если это строка, она должна соответствовать одному из ключей существующих в коллекции элементов.

Метод Item

Метод Item возвращает элемент объекта Collection по индексу позиции или по ключу.

Синтаксис метода Item объекта Collection:

Компоненты метода Item:

  • Collection – обязательный компонент, представляющий выражение (переменную), возвращающее объект Collection.
  • Index – обязательный аргумент, представляющий выражение, возвращающее номер (индекс) позиции элемента коллекции или его уникальный ключ.

Метод Remove

Метод Remove удаляет элемент из объекта Collection по индексу позиции или по ключу.

Синтаксис метода Remove объекта Collection:

Collection.Remove (Index)

Компоненты метода Remove:

  • Collection – обязательный компонент, представляющий выражение (переменную), возвращающее объект Collection.
  • Index – обязательный аргумент, представляющий выражение, возвращающее номер (индекс) позиции элемента коллекции или его уникальный ключ.

Свойство Collection.Count

Свойство Count объекта Collection возвращает количество элементов в коллекции.

Примеры кода с объектом Collection

Пример 1
Создание нового экземпляра объекта Collection, добавление в коллекцию трех элементов, определение количества элементов в коллекции, извлечение одного и того же элемента по индексу и по ключу:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

Sub Primer1()

‘Создаем новый экземпляр

‘объекта Collection

Dim myColl As New Collection

‘Добавляем три элемента

myColl.Add «Первый элемент»

myColl.Add «Второй элемент», «Ключ 2»

myColl.Add «Третий элемент»

‘Определение количества элементов

MsgBox myColl.Count

‘Извлечение одного и того же элемента

‘коллекции по индексу и по ключу

MsgBox myColl.Item(2) & _

» (извлечен по индексу)»

MsgBox myColl.Item(«Ключ 2») & _

» (извлечен по ключу)»

End Sub

Пример 2
Добавление элементов в коллекцию с использованием аргументов «До» и «После»:

Sub Primer2()

Dim myColl As New Collection

‘Добавляем три первых элемента

myColl.Add «Второй элемент»

myColl.Add «Третий элемент»

myColl.Add «Пятый элемент»

‘Следующий элемент вставляем перед первым

myColl.Add «Первый элемент», , 1

‘Следующий элемент вставляем после третьего

‘с учетом добавленного предыдущей строкой

myColl.Add «Четвертый элемент», , , 3

‘Извлекаем первый и четвертый элементы

MsgBox myColl.Item(1)

MsgBox myColl.Item(4)

End Sub

Пример 3
Генерация ошибки при добавлении в коллекцию нового элемента с неуникальным ключом:

Sub Primer3()

Dim myColl As New Collection

On Error GoTo Instr

myColl.Add «Первый элемент», «Ключ»

myColl.Add «Второй элемент», «Ключ»

Instr:

MsgBox «Произошла ошибка: » & Err.Description

End Sub

Эта особенность объекта Collection используется для отбора уникальных значений из списка элементов.

300 примеров Excel — Easy Excel Tutorial

1 Создание макроса: поменять местами значения | Запускать код из модуля | Регистратор макросов | Использовать относительные ссылки | FormulaR1C1 | Добавить макрос на панель инструментов | Макро-безопасность | Защитить макрос

2 MsgBox: Функция MsgBox | Функция InputBox

3 Рабочая книга и объект рабочего листа: путь и полное имя | Закрыть и открыть | Просматривайте книги и листы | Калькулятор продаж | Файлы в каталоге | Таблицы импорта | Таблицы программирования

4 Range Объект: CurrentRegion | Динамический диапазон | Изменить размер | Целые строки и столбцы | Смещение | От активной ячейки до последнего входа | Союз и Интерсект | Проверить выбор | Возможные футбольные матчи | Шрифт | Цвета фона | Коллекция областей | Сравнить диапазоны

5 переменных: Явная опция | Переменная область действия | Жизнь переменных

6 Оператор If Then: логические операторы | Выбрать дело | Налоговые ставки | Оператор модов | Проверка простых чисел | Найдите второе по величине значение | Сумма по цвету | Удалить пустые ячейки

7 Loop: Прохождение через определенный диапазон | Перебрать весь столбец | До цикла | Step Keyword | Создать узор | Сортировка номеров | Произвольная сортировка данных | Удалить дубликаты | Комплексные расчеты | Рюкзак Задача

8 Макро-ошибок: отладка | Обработка ошибок | Err Object | Прервать макрос | Комментарии макроса

9 Манипуляции со строками: отдельные строки | Обратные струны | Преобразовать в правильный регистр | Подсчитать слов

10 Дата и время: сравните дату и время | Функция DateDiff | Будни | Отложить макрос | Год появления | Задачи по расписанию | Сортировать дни рождения

11 событий: событие BeforeDoubleClick | Выделите активную ячейку | Создайте нижний колонтитул перед печатью | Счета и монеты | Таблица скользящих средних

12 Массив: динамический массив | Функция массива | Названия месяцев | Размер массива

13 Функция и подпрограмма: функция, определяемая пользователем | Пользовательская функция среднего значения | Летучие функции | ByRef и ByVal

14 Объект приложения: StatusBar | Чтение данных из текстового файла | Запись данных в текстовый файл

15 элементов управления ActiveX: текстовое поле | Список | Поле со списком | Флажок | Кнопки выбора | Кнопка вращения | Кредитный калькулятор

16 Пользовательская форма: пользовательская форма и диапазоны | Конвертер валют | Индикатор прогресса | Выбор нескольких окон списка | Многоколоночное поле со списком | Зависимые поля со списком | Прокручивать элементы управления | Коллекция элементов управления | Пользовательская форма с несколькими страницами | Интерактивная форма пользователя

.

Примеры Excel VBA, Примеры макросов Excel и бесплатные загрузки

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

АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЫЭЮЯ

3

Танцующие маятники 3D

А

Автоматизация повторяющихся задач в VBA

К

Преобразование формул с использованием таймера обратного отсчета VBA
в VBA

Создание презентаций PowerPoint с помощью Excel
Создание облака тегов в Excel
Консолидация данных из разных файлов
Часы и таймер с помощью Excel

Д

Динамические диаграммы в базе данных Excel VBA
с использованием примеров приборной панели Excel
— Конкурс на обследование заработной платы Excel

E

Пример панели управления Excel № 1
Пример панели управления Excel № 2
Пример панели управления Excel № 3
Принтер отчета Excel
Извлечение комментариев к ячейкам
Электронная почта с использованием Excel
Извлечение чисел из Excel с помощью VBA

Факс

Получение котировок акций в Excel

Форматирование комментариев к ячейкам

г

Получение более трех условных форматов
Игра — препятствия в Excel

H

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

Я

Введение в VBA и макросы Excel
Интерактивные информационные панели — обзор техники Excel и VBA

м

Объединить ячейки без потери данных
Переместить данные с одного листа на несколько листов

О

Оптимизация кода VBA

П

Шаблон календаря с изображениями
Печать отчетов Excel через Word

Р

Обновить сводные таблицы
Удаление строк разрыва страницы

S

Диаграммы анализа продаж с использованием VBA и Excel

Решение судоку
Разделить файл Excel на множество
Разделить текст с помощью макросов

т

Приложение списка Todo

Твитов из Excel

U

Обновление фильтров отчета с использованием VBA
Использование объектов в VBA
Использование переменных, условий и циклов

В

Демонстрационное приложение VBA для запуска магазина
Диаграммы Венна

в Excel

Подробнее о Excel VBA

Пожалуйста, перейдите на эти страницы, чтобы узнать больше о Excel VBA

  • Домашняя страница Excel VBA
  • Примеры Excel VBA
  • Видеоуроки по Excel VBA, макросы
  • Советы по Excel VBA
  • Пользовательские формы и элементы управления в VBA
  • Книги в формате Excel VBA
  • Ссылки на Excel VBA
  • Обучение работе с Excel VBA
  • Присоединяйтесь к нашему информационному бюллетеню Excel

.

Примеры Excel VBA: вводные примеры событий

  2. Программное обеспечение
  3. Microsoft Office
  4. Excel
  5. Excel VBA Примеры: вводные примеры событий

Автор Джон Уокенбах

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

Пример Excel VBA: событие Open для книги

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

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

  1. Откройте книгу Excel.
    Подойдет любая книга Excel.
  2. Нажмите Alt + F11, чтобы активировать VBE.
  3. Найдите книгу в окне проекта.
  4. При необходимости дважды щелкните имя проекта, чтобы отобразить его элементы.
  5. Дважды щелкните элемент ThisWorkbook.
    VBE отображает пустое окно кода для объекта ThisWorkbook.
  6. В окне «Код» выберите «Рабочая книга» в раскрывающемся списке «Объект» (слева).
    VBE вводит начальные и конечные операторы для процедуры Workbook_Open.
  7. Введите следующие операторы, чтобы полная процедура события выглядела так:
 Частная вспомогательная книга_Открыть ()
  Dim Msg As String
  Если будний день (сейчас) = 6, то
    Msg = "Сегодня пятница. Не забудьте"
    Msg = Msg & "отправить отчет TPS!"
    MsgBox Msg
  Конец, если
Концевой переводник

Окно кода должно выглядеть так.

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

Workbook_Open выполняется автоматически при открытии книги.Он использует функцию WeekDay языка VBA для определения дня недели. Если сегодня пятница (день 6), появится окно с напоминанием пользователю о необходимости отправить отчет. Если сегодня не пятница, ничего не происходит.

Если сегодня не пятница, возможно, вам будет сложно проверить эту процедуру. Вы можете просто изменить число 6, чтобы оно соответствовало текущему номеру дня.

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

Процедура Workbook_Open может делать почти все. Эти обработчики событий часто используются для следующих целей:

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

Вот последний пример процедуры Workbook_Open в Excel VBA, в которой используются функции GetSetting и SaveSetting, чтобы отслеживать, сколько раз открывалась книга.Функция SaveSetting записывает значение в реестр Windows, а функция GetSetting извлекает это значение (подробности см. В справочной системе). Следующий пример Excel VBA извлекает счетчик из реестра, увеличивает его, а затем сохраняет обратно в реестр. Он также сообщает пользователю значение Cnt, которое соответствует количеству открытий книги.

 Private Sub Workbook_Open ()
  Dim Cnt As Long
  Cnt = GetSetting («Мое приложение», «Настройки», «Открыть», 0)
  Cnt = Cnt + 1
  SaveSetting «Мое приложение», «Настройки», «Открыть», Cnt
  MsgBox "Эта книга открывалась" & Cnt & "раз."
Концевой переводник

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

Excel VBA, пример: событие BeforeClose для книги

Вот пример процедуры обработчика событий Excel VBA Workbook_BeforeClose, которая выполняется автоматически непосредственно перед закрытием книги. Эта процедура находится в окне кода для объекта ThisWorkbook:

 Private Sub Workbook_BeforeClose (Отменить как логическое)
  Dim Msg As String
  Dim Ans As Long
  Dim FName As String
  Msg = "Хотите сделать резервную копию этого файла?"
  Ans = MsgBox (Msg, vbYesNo)
  Если Ans = vb Да Тогда
    FName = "F: \ BACKUP \" и ThisWorkbook.имя
    ThisWorkbook.SaveCopyAs FName
  Конец, если
Концевой переводник

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

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

При использовании события Workbook_BeforeClose помните: если вы закроете Excel и какой-либо открытый файл был изменен с момента последнего сохранения, Excel покажет обычное окно сообщения «Вы хотите сохранить изменения?». Нажатие кнопки «Отмена» отменяет весь процесс закрытия. Но событие Workbook_BeforeClose все равно будет выполнено.

Excel VBA, пример: событие BeforeSave для книги

Событие BeforeSave, как следует из его названия, запускается перед сохранением книги.Это событие происходит, когда вы выбираете Файл → Сохранить или Файл → Сохранить как.

Следующая процедура, которая помещается в окно кода для объекта ThisWorkbook, демонстрирует событие BeforeSave. Подпрограмма обновляет значение в ячейке (ячейка A1 на Sheet1) каждый раз при сохранении книги. Другими словами, ячейка A1 служит счетчиком для отслеживания количества сохранений файла.

 Private Sub Workbook_BeforeSave (ByVal SaveAsUI _
  Как логическое, отменить как логическое)
  Тусклый счетчик как диапазон
  Установите Counter = Sheets ("Sheet1").Диапазон ("A1")
  Counter.Value = Counter.Value + 1
Концевой переводник

Обратите внимание, что процедура Workbook_BeforeSave имеет два аргумента: SaveAsUI и Cancel. Чтобы продемонстрировать, как работают эти аргументы, изучите следующий макрос, который выполняется перед сохранением книги. Эта процедура пытается помешать пользователю сохранить книгу под другим именем. Если пользователь выбирает File → Save As, аргумент SaveAsUI имеет значение True.

Когда код выполняется, он проверяет значение SaveAsUI.Если эта переменная имеет значение True, процедура отображает сообщение и устанавливает для параметра Cancel значение True, что отменяет операцию сохранения.

 Private Sub Workbook_BeforeSave (ByVal SaveAsUI _
  Как логическое, отменить как логическое)
  Если SaveAsUI, то
    MsgBox "Вы не можете сохранить копию этой книги!"
  Отмена = True
  Конец, если
Концевой переводник

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

.Учебное пособие по функциям

Excel VBA: возврат, вызов, примеры

  • Home

  • Testing

      • Back
      • Agile Testing
      • BugZilla
      • Cucumber
      • Database Testing
      • Database Testing
      • Назад
      • JUnit
      • LoadRunner
      • Ручное тестирование
      • Мобильное тестирование
      • Mantis
      • Почтальон
      • QTP
      • Назад
      • Центр качества SAP
      • SoapUI
      • Управление тестированием
      • TestLink

  • SAP

      • Назад
      • ABAP
      • APO
      • Начинающий
      • Basis
      • BODS
      • BI
      • BPC
      • CO
      • Назад
      • CRM
      • Crystal Reports
      • Crystal Reports
      • FICO3
      • Заработная плата
      • Назад
      • PI / PO
      • PP
      • SD
      • SAPUI5
      • Безопасность
      • Менеджер решений
      • Successfactors
      • SAP Tutorials

      4

    • Web
    • Apache
    • AngularJS
    • ASP.Net
    • C
    • C #
    • C ++
    • CodeIgniter
    • СУБД
    • JavaScript
    • Назад
    • Java
    • JSP
    • Kotlin
    • Linux
    • Linux
    • Kotlin
    • Linux

      • js

    • Perl
    • Назад
    • PHP
    • PL / SQL
    • PostgreSQL
    • Python
    • ReactJS
    • Ruby & Rails
    • Scala
    • SQL

      • 000

    • SQL

      • 000

      0003 SQL

      000

      0003 SQL

      000

    • UML
    • VB.Net
    • VBScript
    • Веб-службы
    • WPF

  • Обязательно учите!

      • Назад
      • Бухгалтерский учет
      • Алгоритмы
      • Android
      • Блокчейн
      • Business Analyst
      • Создание веб-сайта
      • CCNA
      • Облачные вычисления
      • 00030003 COBOL 9000 Compiler
          9000 Встроенные системы

        • 00030002 9000 Compiler 9000
        • Ethical Hacking
        • Учебники по Excel
        • Программирование на Go
        • IoT
        • ITIL
        • Jenkins
        • MIS
        • Сеть
        • Операционная система
        • Назад
        • Управление проектами Обзоры
        • Salesforce
        • SEO
        • Разработка программного обеспечения
        • VB A

    • Big Data

        • Назад
        • AWS
        • BigData
        • Cassandra
        • Cognos
        • Хранилище данных

          • 0003

        • HBOps

          • 0003

        • HBOps

          • 0003

        • MicroStrategy
        • MongoDB

    .

    No related posts.

    • Share :

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

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