Как применяется VBA в Excel

»  » 

Картинка к странице

Содержание

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

В Excel наиболее важным является объект Application. Объект Application (приложение) является главным в иерархии объектов Excel и представляет само приложение Excel. Он имеет более 120 свойств и 40 методов. Эти свойства и методы предназначены для установки общих параметров приложения Excel. В иерархии Excel объект Workbook (рабочая книга) идет сразу после объекта Application и представляет файл рабочей книги. Рабочая книга хранится либо в файлах формата XLS (стандартная рабочая книга), либо XLA (полностью откомпилированное приложение). Свойства и методы рабочей книги позволяют работать с файлами. Однако наиболее «употребляемым» на практике является объект Range, который наилучшим образом отображает возможности использования VBA в Excel (о свойствах объекта Range см. таблицу «Свойства объекта Range», о методах - таблицу «Методы объекта Range»).

В иерархии Excel объект Range (диапазон) идет сразу после объекта worksheet. Объект Range является одним из ключевых объектов VBA. Объект selection (выбор) возникает в VBA двояко - либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range, и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и Selection является то, что они не являются элементами никакого семейства объектов.

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

Расширенные примеры по VBA

Расширенные примеры применения Visual Basic в Excel смотрите здесь:

Далее об основах.

Задание групп строк и столбцов с помощью объекта Range

Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («а:с») задает диапазон, состоящий из столбцов а, в и с, а Range(«2:2») - из второй строки. Другим способом работы со строками и столбцами являются методы Rows (строки) и columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом а является columns (1), а второй строкой - Rows (2).

Связь объекта Range и свойства Cells

Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект Cells (ячейки) - это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или Cells (1, 2). В свою очередь, объект cells, вкладываясь в Range, также позволяет записывать диапазон в альтернативном виде, который иногда удобен для работы, а именно Range(«А2:СЗ») и Range(Cells(1.2), Cells(3.3)) определяют один и тот же диапазон.

Свойства объекта Range

Свойство Действие
Value

Возвращает значение из ячейки или в ячейки диапазона. В данном примере переменной х присваивается значение из ячейки c1:

х=Range("C1").Value;

В следующем примере в диапазон а1:в2 введена 1:

Range("A1:B2").Value=1

Name

Возвращает имя диапазона. В данном примере диапазону а1:в2 присваивается имя «итоги»:

Range("Al:B2").Name="Итоги"

Count

Возвращает число объектов в наборе. В данном примере переменной х присваивается значение, равное числу строк диапазона а1:в2:

х=Range("Al:B2").Rows.Count

ColumnWidth, RowHeight Возвращает ширину столбцов и высоту строк диапазона
CurrentRegion

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

у=Range("A1").CurrentRegion.Rows.Count

WrapText

Позволяет переносить текст при вводе в диапазон. Допустимые значения True и False. В следующем примере в ячейку в2 вводится текст «длинный текст», и в этой ячейке устанавливается режим ввода текста с переносом:

With Range("B2").Value="Длинный текст".WrapText=True End With

EntireColumn, EntireRow

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

ActiveCell.EntireRow.Clear

ActiveCell. EntireColumn.Select

Comment

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

Объект comment является элементом семейства comments. Метод AddComment, примененный к диапазону, создает новое примечание.

Среди методов объекта Comment отметим только метод Text, который задает текст, выводимый в примечании. Синтаксис:

Text(Text, Start, Overwrite)

Здесь Text - строка, выводимая в качестве примечания; Start - с какого символа вводится текст в уже существующее примечание. Если аргумент опущен, то из примечания удаляется весь ранее введенный текст; overwrite - допустимые значения: True (вводимый текст записывается поверх уже существующего) и False (вводимый текст вставляется в уже существующий). Среди свойств объекта comment отметим только свойство visible, устанавливающее отображение примечания при активизации диапазона, имеющего определенное примечание

Vertical Alignment

Вертикальное выравнивание. Допустимые значения: xlBottom (выравнивание по нижнему краю);

xlCenter (выравнивание по центру);

xlJustify (выравнивание по высоте);

xlTop (выравнивание по верхнему краю)

Orientation Ориентация. Допускается либо угол поворота текста в градусах от -90° до 90°
ShrinkToFit Допустимые значения: True (автоматическое изменение шрифта так, чтобы текст помещался в ячейку) и False (в противном случае)
Font

Возвращает объект Font (шрифт). Объект Font имеет следующие свойства:

Name - строка, указывающая имя шрифта, например «Arial Суr»;

FontStyle - стиль, возможен Regular (обычный), Bold (полужирный), italic (курсив), Bold italic (полужирный курсив);

size - размер;

strikethrough - допустимы два значения: True (буквы имеют линию по центру, как будто они перечеркнуты) и False (не имеют линии по центру);

superscript - допустимы два значения: True (текст используется как верхний индекс) и False (не используется как верхний индекс);

Subscript - допустимы два значения: True (текст используется как нижний индекс) и False (не используется как нижний индекс);

underline - устанавливает тип подчеркивания, допустимыми являются значения: xlNone (нет подчеркивания), xlSingle (одинарное по значению)

Horizontal Alignment

Горизонтальное выравнивание. Допустимые значения:

xlGeneral (обычное выравнивание, зависящее от типа

вводимых значений);

xlCenter (выравнивание по центру);

xlRight (выравнивание по правому краю);

xlLeft (выравнивание по левому краю);

xlJustify (выравнивание по ширине);

xlCenterAcrossSelection (выравнивание по центру

в выделенном диапазоне);

xlFill (выравнивание по ширине)

Методы объекта Range

Метод Действие
AutoFit Автоматически настраивает ширину столбца и высоту строки
Clear, ClearComments, ClearContents, ClearFormats

Метод clear очищает диапазон. В следующем примере очищается диапазон A1:G37.

Range("А1:G37").Clear

Методы ClearComments, ClearContents, ClearFormats и ClearNotes очищают в указанном диапазоне

Insert

Вставка ячейки или диапазона ячеек. В следующем примере вставляется новая строка перед четвертой строкой рабочего листа Лист1:

Worksheets('Лист1").Rows(4).Insert

Select Выделение диапазона
ClearNotes Комментарии, содержание, форматы и примечания соответственно
Copy

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

обмена. Синтаксис:

Copy(destination)

Аргумент destination определяет диапазон, куда копируется данный диапазон. Если аргумент destination опущен, то копирование происходит в буфер обмена. В данном примере диапазон a1:D4 рабочего листа копируется в диапазон Е5 листа 2:

Worksheets("Лист1").Range("А1:D4").Сору destination:=Worksheets("Лист2").Range("E5")

Delete Удаляет диапазон
AddComment

Добавляет примечание к диапазону. Синтаксис: AddComment (Text)

Text - строковое выражение, добавляемое в качестве примечания

Address

Возвращает адрес ячейки. Синтаксис: Address(rowAbsolute, columnAbsolute, referenceStyle, external, relativeTo)

Аргументы:

rowAbsolute - допустимы два значения True и False, если используется значение True или аргумент опущен, то возвращается абсолютная ссылка на строку; columnAbsolute - допустимы два значения True и False, если используется значение True или аргумент опущен, то возвращается абсолютная ссылка на столбец;

referenceStyle - допустимы два значения xiA1 и xlRld, если используется значение xiA1 или аргумент опущен, то возвращается ссылка в виде формата А1; external - допустимы два значения True и False, если используется значение False или аргумент опущен, то возвращается относительная ссылка. Следующий пример показывает различные результаты адресации.

Msg Box Cells(l, l).Address

В диалоговом окне отображается адрес $А$1.

MsgBox Cells(l, 1).Address(rowAbsolute:=False

В диалоговом окне отображается адрес $А1.

MsgBox Cells(l, 1).Address(referenceStyle:=x1R1С1

В диалоговом окне отображается адрес R1C1

Cut

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

или в буфер обмена.

Синтаксис:

Cut(destination)

Аргумент destination определяет диапазон, который копируется в данный диапазон. Если аргумент destination опущен, то диапазон копируется в буфер обмена

Columns, Rows

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

i=Selection.Columns.Count

j == Selection.Rows.Count

Методы объекта Range, использующие команды Excel

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

Метод AutoFill

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

Синтаксис:

объект. AutoFill(диапазон, тип)

Аргументы:

Диапазон Диапазон, с которого начинается заполнение тип Допустимые значения: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault

Метод AutoFilter

Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, осуществляется вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (All), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NonBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).

При применении метода AutoFilter допустимы два синтаксиса.

Синтаксис 1:

Объект. AutoFilter

В этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект.

Синтаксис 2:

Объект. AutoFilter (field, criteria1, operator, criteria2)

В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе.

Аргументы:

field - Целое, указывающее поле, в котором производится фильтрация данных;

criteria1 - Задают два возможных условия фильтрации и criteria2 поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, <, >=, <=, =, <>;

operator - Допустимые значения: X1And (логическое объединение первого и второго критериев); X1or (логическое сложение первого и второго критериев)

При работе с фильтрами полезны метод showAllData и свойства FilterMode и AutoFilterMode

Метод ShowAllData показывает все отфильтрованные и неотфильтрованные строки рабочего листа.

Свойство FilterMode. Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)

Свойство AutoFilterMode. Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае).

Метод GoalSeek

Метод GoalSeek (подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения. Вручную метод GoalSeek выполняется с помощью команды Сервис, Подбор параметра (Tools, Goal Seek). Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения.

Синтаксис:

Объект. GoalSeek(Goal, ChangingCell)

Аргументы:

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

Goal. Значение левой части решаемого уравнения, не содержащей параметра.

ChangingCell. Ссылка на ячейку, отведенную под параметр (неизвестную величину). Значение, введенное в данную ячейку до активизации метода Goalseek, рассматривается как начальное приближение к искомому корню

Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами Maxchange и Maxiterations объекта Application. Например, определение корня с точностью до 0.0001 максимум за 1000 итераций устанавливается инструкцией:

With Application

Maxiterations=1000

MaxChange=0.0001

End With

Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options), вызываемого командой Сервис, Параметры (Tools, Options).

Метод Sort

Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные, Сортировка (Data, Sort).

Синтаксис:

Объект. Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)

Аргументы:

Объект Диапазон, который будет сортироваться.

Key1 Ссылка на первое упорядочиваемое поле.

Order1. Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок).

key2. Ссылка на второе упорядочиваемое поле.

order2. Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок).

Header. Допустимые значения: xlYes (первая строка диапазона содержит заголовок, который не сортируется); xlNo (первая строка диапазона не содержит заголовка, по умолчанию считается данное значение); xlGuess (Excel решает, имеется ли заголовок).

orderCustom. Пользовательский порядок сортировки. По умолчанию используется Normal.

matchCase. Допустимые значения: True (учитываются регистры) и False (регистры не учитываются).

Orientation. Допустимые значения: xlTopToBottom (сортировка осуществляется сверху вниз, то есть по строкам); xlLeftToRight (слева направо, то есть по столбцам).

Например, диапазон А1:С20 рабочего листа лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная - по второму:

Worksheets(«Лист»).Range(«A1: C20»).Sort _

key1:=Worksheets(«Sheet1»).Range(«A1»), _

key2:=Worksheets («Sheet1»).Range («B1»)

Округление чисел

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

1 способ

Функция Round

Пример:

X= round(2.505, 2)

Значение х будет 2.5, а не 2.51.

Поэтому часто не используется.

2 способ

Функция Format

Пример:

sngОкругление=Format(SngНеокругленное, "#, 0.00")

3 способ

Функция FormatNumber

SngОкругление= FormatNumber(sbgНеокругленное, 2)

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

Примечание.

Переменная, в которую помещается округленное значение, должна иметь тип string, single, double, decimal, currency или variant, но не тип integer или long.

Приведение данных

Для приведения введенных данных к нужному типу в VBA включен обширный набор функций, одна из которых - CDBL. Синтаксис:

CDbl(выражение)

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

А=Cdbl(textBoxN.text)

После чего с данной переменной можно работать.

Для выведения значений непосредственно в ячейки книги Excel удобно использовать объект Range:

range(«A5»).value=a

Функцией, обратной по действию к CDbl, является функция CStr - она переводит числа в строки и удобна для вывода результата либо в ячейку на лист, либо в то или иное текстовое окно.

TextBoxN.text=CStr(.Range(«A8»).value) - считывание значения с ячейки и вывод его в текстовое окно.

Функция Trim (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.

Создание VBA программ

Использование метода GoalSeek

Пример.

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

Разработанная форма примера в рабочем состоянии
Разработанная форма примера в рабочем состоянии

Технология выполнения

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

Кнопка Вычислить

Private Sub CommandButton1_Click()

Dim a, b, c As Double

a=CDbl(TextBox1.Text)

b=CDbl(TextBox2.Text)

c=CDbl(TextBox3.Text)

With ActiveSheet

Range(«b3»).Value=a

Range(«b4»).Value=b

Range(«b5»).Value=c

Range(«b6»).FormulaLocal=«=b3*b7^3+b4*sin(b7)»

Range(«b6»).GoalSeek Goal:=c, changingCell:=Range(«b7»)

TextBox4.Text=CStr(.Range(«b7»).Value)

TextBox4.Text=FormatNumber(TextBox4.Text, 2)

End With

End Sub

Кнопка Закрыть

Private Sub CommandButton2_Click()

UserForm1.Hide

End Sub

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

Private Sub UserForm_initialize()

Worksheets(1).Visible=False

End Sub

Использование методов AutoFill при заполнении таблиц

Пример.

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

Технология выполнения

  1. Запустите приложение Excel, сохраните документ.
  2. Перейдите в редактор VBA. Создайте форму.
Разработанная форма примера в режиме конструктора
Разработанная форма примера в режиме конструктора

На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации.

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

Обработайте кнопки

Кнопка Создать таблицу

Const strNomer=3 количество строк для заголовка

Dim strName1 As String 'строка для адресации ячеек

Dim strName2 As String

Dim nomer As Long номер очередной строки таблицы

Private Sub CommandButton1_Click()

ActiveWorkbook.SaveAs («работа с базой данных. xls»)

nomer=1 End Sub

Кнопка Добавить строку

Private Sub CommandButton2_Click()

strName1=Trim(Str(strNomer + nomer))

With ActiveSheet 'ввод данных для новой отчетной таблицы

Range("A" + strName1).Value=nomer

Range("B" + strName1).Value=TextBox1.Text

Range("C" + strName1).Value=TextBox2.Text

Range("D" + strName1).Value=TextBox3.Text

// автозаполнение с текущей строки таблицы

strName2=Trim(Str(strNomer + nomer + 1))

Set range1 =.Range("A" + strName1 +":D" + strName1)

Set range2 =.Range("A" + strName1 +":D" + strName2)

range1.AutoFill Destination:=range2

Range("A" + strName2 +":D" + strName2).Clear

End With

// очистка полей формы для ввода очередной записи

TextBox1.Text=""

TextBox2.Text=""

TextBox3.Text=""

TextBox1.SetFocus

nomer=nomer + 1

End Sub

Кнопка Закончить таблицу

Private Sub CommandButton3_Click()

// закрытие формы подведение итогов и вывод фамилии преподавателя

UserForm1.Hide

With ActiveSheet

strName2=Trim(Str(strNomer + nomer + 2))

Range("A" + strName2).Value=«классный руководитель»

Range("D" + strName2).Value=TextBox4.Text

End With

End Sub

Откомпилируйте программу и запустите на выполнение.

Пример.

Разработать программу, которая по введенным переменным в соответствующие поля формы решает простейшее линейное уравнение y=a*x + b*x, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.

Пример.

Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y=a*x^3 + 3b*sinx, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.

Пример.

Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y=5a*x^1/3 + 3b*tg4x, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.

Пример.

Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y=ln(a*x^3) + 3b*cos(e^x), находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.

Есть вопросы, замечания, дополнения? Пишите в комментариях.
excel функции vba

Страница: Как применяется VBA в Excel

Дата публикации: 2010.12.01 04:10.Обновление: 2014.02.28 01:00

Следующая статья:

Полезные темы:

Как применяется VBA для построения диаграмм в Excel
Как применяется VBA для построения диаграмм в Excel
В статье описывается применение программирования VBA для построения диаграмм в Excel
Как поднять ТИЦ?
Как поднять ТИЦ?
Описание основных принципов поднятия ТИЦ бесплатно, а также общие рекомендации по улучшению поисковых запросов
Как понимать ответы
Как понимать ответы
Часто профессионалы отвечают довольно замысловато, давайте рассмотрим как эти ответы понимать
Как работает PayPal
Как работает PayPal
Статья об устройстве западной платежной системы PayPal, какие типы счетов можно использовать в платежной системе и другие аспекты учитывающиеся при работе
Как прочитать информацию из Excel
Как прочитать информацию из Excel
Инструкция с примерами, кКак следует правильно прочитать из Excel информацию средствами PHP
наверх