Автоматизация рутины в Microsoft Excel при помощи VBA. Примеры vba excel

VBA Excel. Свойство Range.Resize (синтаксис, примеры)

Свойство Resize объекта Range позволяет в коде VBA Excel изменять размер указанного диапазона. Синтаксис свойства Range.Resize, его параметры и примеры использования.

Описание свойства Range.Resize
Примеры использования в VBA Excel

Описание свойства Range.Resize

Свойство Range.Resize устанавливает новый размер исходного диапазона по указанному количеству строк и столбцов.

Синтаксис свойства Range.Resize

Expression.Resize(RowSize, ColumnSize)

Конструкция, представляющая синтаксис свойства, не может использоваться отдельно сама по себе, иначе VBA Excel сгенерирует ошибку. Поскольку свойство Range.Resize возвращает диапазон нового размера, его можно присвоить переменной или применить к нему какой-либо метод.

Параметры свойства Range.Resize

Параметр	Описание
Expression	Выражение, возвращающее исходный диапазон, которому требуется изменить размер. Тип данных параметра - Range.
RowSize	Число строк или выражение, возвращающее количество строк нового диапазона. Тип данных параметра - Variant. Если этот аргумент пропущен, число строк в диапазоне останется прежним.
ColumnSize	Число столбцов или выражение, возвращающее количество столбцов нового диапазона. Тип данных параметра - Variant. Если этот аргумент пропущен, число столбцов в диапазоне останется прежним.

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

Примеры использования в VBA Excel

Пример 1

Sub Primer1() Dim a As Range Set a = Range("C3:E5") Set a = a.Resize(5, 5) MsgBox a.Address End Sub

В первом примере мы присваиваем переменной a диапазон из трех столбцов и трех строк, а затем преобразуем его с помощью кода VBA Excel в диапазон из пяти столбцов и пяти строк и перезаписываем его в ту же переменную a. Обратите внимание, что диапазон расширяется вправо (столбцы) и вниз (строки). При уменьшении диапазона, «лишние столбцы и строки» будут исключены из него, соответственно, справа и снизу.

Пример 2

Sub Primer2() Dim a As Range Set a = Range("A1:E5") a.Resize(2, 2).Select MsgBox a.Address End Sub

Во втором примере мы уменьшили размер исходного диапазона и применили к нему метод Select. Обратите внимание, что уменьшенный диапазон выбран с помощью метода Select на активном листе рабочей книги Excel, а в переменной a диапазон остался прежним, что и покажет информационное окно MsgBox.

Пример 3

Sub Primer3() Dim a As Range Set a = Union(Range("A1:A5"), Range("B1:B5")).Resize(4, 4) MsgBox a.Address End Sub

В третьем примере кода VBA в качестве Expression свойства Range.Resize используется выражение с методом Union.

vremya-ne-zhdet.ru

Основные операторы vba – Условные операторы

VBA

(Visual Basic for Application)

Лекция 1

Basic- язык программирования высокого уровня (интерпретатор)

Visual- содержит наглядные средства по разработке программ (кодов), упрощающие работу пользователя, позволяет записывать коды с помощью макрорекодера.

Application- приложение к программной системеMSOfficeнаряду с приложениямиWord,Access,PowerPoint.

Зачем нужен VBA?

Объединяет (интегрирует) приложения, позволяет управлять работой других приложений не выходя из Excel, внедрять объекты из других приложений;
Действия доступные пользователю на рабочем листе составляют 10% от всех возможностей приложения Excel,VBAпозволяет автоматизировать работу вашего проекта.

VBAотносится к числу объектно-ориентированных языков, то есть при разработке проектов используются объекты.Определение: объединение данных и кода в единое целоею Например, элемент «Кнопка» и соответствующий код, связанный с этой кнопкой обеспечат переход на другой лист рабочей книги.

Основные объекты VBA:

Application (само приложение Excel)

WorkBook(рабочая книга – ваш файл)

WorkSheetFunction (мастер функций)

WorkSheet (рабочий лист)

Range (диапазон)

Chart (диаграмма)

Style (стиль)

Border (границы)

Interior (цвет фона)

Font (шрифт)

Множество некоторых объектов составляют семейства–WorkBooks,WorkSheets,Charts.

Объекты обладают свойствами (действия над объектами) и методами (дейстия самих объектов).

Начнем знакомиться со средствами визуализации разработки проектов в VBA. Таковым является Интегрированная Среда Разработки Приложений. Чтобы попасть в эту среду надо выбрать в пункте меню СервисМакросРедакторVBAили нажать одновременно клавишиALTиF11.

На экране появятся компоненты редактора VBA:

- окно проекта Project – VBA Project

-окно свойствPropeties

- окно кода

- окно форм UserForm

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

Окно проекта Project–VBAProject(рис. 1) показывает структуру вашего проекта (файла). Это окно активизируется в редактореVBAвыбором командыViewProjectExplorerили кнопкой «ProjectExplorer» или нажатием клавишCtrl+R

Рис. 1 Окно проекта.

Рис.2 Интегрированная среда разработки приложений

Окно кода предназначено для хранения кода, связанного с объектом. У каждого объекта свое окно, так каждый рабочий лист (WorkSheet) имеет свое окно кода, рабочая книга (WorkBook) – свое окно.

Создание пользовательских функций

Пользовательские функции добавляются к стандартному списку мастера функций (WorkSheetFunction). Эти функции создаются в специальном модуле, сопровождающем объектWorkSheetFunction. Этот модуль добавляется к проекту с помощью командыInsertModule(ВставитьМодуль), в окне проекта он отобразится на уровне вашего приложения. Все коды, написанные в этом модуле, появятся в категории «Функции определенные пользователем» мастера функций.

Итак, добавляем модуль в наш проект (InsertModule) и в окне кода этого модуля пишем текст программы:

Function y(x)

y = Cos((x + 2) / 2) ^ 2 + Exp(-2 * x) / (x ^ 2 + 1) ^ 0.5

End Function

Затем переходим на рабочий лист «1 график», в ячейке с2 выполним обращение к новой функции, добавленной в мастер функций - y(x). Работа с этой функцией ничем не отличается от работы с любой другой функцией. На первом шаге надо выбратьy(x) в категории «Функции определенные пользователем», на втором шаге в качестве аргументаxуказать ячейкуA2. В результате в ячейке С2 будет записана формула =y(A2). Эту формулу протащить на весь диапазон А2:А17 как показано на рис. 3. Разумеется, результат должен совпасть с тем, что вы получили, вычисляя эту функцию обычными средствами рабочего листа.

Рис 3. Функция y(x), рассчитанная обычным образом и с помощью пользовательской функции.

Условные операторы имеют 2 формы записи:

1) В одну строку

IF< условие>THEN<оператор 1> [ELSE<оператор 2>]

IF,THEN,ELSE–служебные неизменяемые слова, в угловых скобках < >текст пользователя, это то, что вы пишете в соответствии с заданием, в квадратных скобках [ ] необязательная часть, текст может отсутствовать. Такая форма обычно используется в случае простых действий, например, при вычислении модуля числаy=х=abs(s) можно использовать следующий оператор:

IF x > 0 THEN y = x ELSE y = -x

2) В несколько строк. В этом случае условный оператор обязательно заканчивается утверждением “ENDIF”

IF <условие> THEN

<оператор 1>

<оператор 2>

…

ELSE

<оператор 3>

<оператор 4>

…

END IF

Такая форма используется при сложных вычислениях, например, при вычислении корней квадратного уравнения. Пусть надо найти корни уравнения a*x2+b*x+c= 0. Как известно в случае еслиb2-4*a*c≥ 0, то корни вычисляются по формуле, еслиb2-4*a*c≤ 0, то корней в области действительных чисел нет. Условный оператор, реализующий этот алгоритм, выглядит следующим образом:

IF b^2 -4*a*c>= 0 THEN

X1 = (-b + (b^2 - 4*a*c)^(1/2)) / (2*a)

X2 = (-b + (b^2 + 4*a*c)^(1/2)) / (2*a)

ELSE

X1 = “ РЕШЕНИЯ НЕТ”

X2 = “ РЕШЕНИЯ НЕТ”

END IF

Примеры написания пользовательских функций в vba

Пример 1.

Function y(x)

y = Cos((x + 2) / 2) ^ 2 + Exp(-2 * x) / (x ^ 2 + 1) ^ 0.5

End Function

Пример 2

Function z(x)

If x < 0 Then

z = (1 + x + x ^ 2) / (1 + x ^ 2)

Else

If x < 1 Then

z = (1 + 2 * x / (1 + x ^ 2)) ^ (1 / 2)

Else

z = 2 * Abs(0.5 + Sin(x))

End If

End Function

Лекция 2

- Свойства, методы и события объектов VBA. Пример процедуры с использованием отдельных свойств объектаApplication.
- Элементы управления
- Кнопки перехода с листа на лист, об авторе, о памяти ПК
- Типы переменных в VBA
- операторы цикла
- пример программы табулирования функции

Свойства, методы и события объектов VBA. Пример процедуры с использованием отдельных свойств объектаApplication.

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

Полная ссылка на объект состоит из перечисления вложенных подчиненных объектов, разделенных точкой. Например, полная ссылка на ячейку А1 листа “Содержание”будет выглядеть так:

Application.WorkBooks(“Графики”).Worksheets(“Содержание”).Range(“A1”).

Если рабочая книга (файл) “Графики” является активной, то достаточно указать

Worksheets(“Содержание”).Range(“A1”).

Если же вы работаете на листе “Содержание” , то ссылка будет выглядеть Range(“A1”).

Все объекты имеют свойства, методы и события.

Свойствоэто некоторая характеристика объекта ( цвет, форма, наименование, расположение, видимость и т.д.) Устанавливается значение объекта так:

Объект.Свойство = значение свойства

Методэто действие, выполняемое над объектом (открыть, закрыть, удалить). Правило записи метода:

Объект. Метод

Событиеэто действие, распознаваемое объектом ( щелчок мышью, двойной щелчок, нажатие клавиши).

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

Приведем некоторые свойства, методы и события основных объектов.

Свойство	Метод	Событие
Объект Application
Caption (заголовок объекта)	Quit (выход изExcel	NewWorkBook(создание новой рабочей книги)
AutoREcover (автосохранение)	Save(сохранение)	SheetActivate(переход на рабочий лист)
ReferenceStyle(стиль ссылок)	Run( выполнение макроса)	WorkBookOpen(открытие рабочей книги)
MemoryFree(информация о свободной оперативной памяти)	Volatile(перевычисление при изменении в ячейках рабочего листа)	WorkBookBeforeClose(закрытие рабочей книги)
MemoryTotal(информация об общей оперативной памяти)	Calculate(вычисление во всех открытых книгах)	SheetBeforeDubleClick( двойной щелчок)
MemoryUsed(информация о занятой оперативной памяти)	IpputBox(ввод данных)	SheetBeforeRightClick(щелчок правой кнопкой)
CellDragAndDrop(управление перетаскиванием формул в ячейках)	Msgbox(вывод сообщений)
ActiveCell, ActiveSheet (активная ячейка, лист)
Cells(диапазон ячеек)
DisplayFormulaBar(отображение строки формул)
DisplayScrollBar(отображение полос прокрутки)
DisplayStatusBar(отображение строки состояния)

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

Данные процедуры записываются в модуле «Эта книга» и выполняются при открытии и закрытии рабочей книги, то есть при выполнении событий OpenиBeforeClose:

Private Sub workbook_open()

' Заголовок рабочей книги

Application.Caption = "Киса и Ося были здесь"

' Цвет фона диапазона A1:D1 -Красный

Sheets("Содержание").Range("a1:d1").Interior.Color = RGB(255, 0, 0)

'Границы диапазона A1:D1 - пунктир

Sheets("Содержание").Range("a1:d1").Borders.LineStyle = 3

'отменяется перетаскивание ячеек CellDragAndDrops

Application.CellDragAndDrop = Falsе

‘ убирается строка формул

Application.DisplayFormulaBar = False

‘убираются полосы прокрутки

Application.DisplayScrollBars = False

“устанавливается стиль ссылок R1C1

Application.ReferenceStyle = xlR1C1

End Sub

'Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Восстанавливается перетаскивание ячеек CellDragAndDrops

Application.CellDragAndDrop = True

‘Восстанавливается строка формул

Application.DisplayFormulaBar = True

‘Восстанавливаются полосы прокрутки

Application.DisplayScrollBars = True

‘Восстанавливается стиль ссылок А1

Application.ReferenceStyle = xlA1

'End Sub

studfiles.net

Переменные и константы в VBA

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

Например, константа Pi хранит значение 3,14159265… Число «Пи» не будет изменяться в ходе выполнения программы, но все же хранить такое значение удобнее как константу.

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

Типы данных

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

Тип данных Размер Описание Диапазон значений

Byte	1 байт	Положительные целые числа; часто используется для двоичных данных	от 0 до 255
Boolean	2 байта	Может принимать значения либо True, либо False	True или False
Integer	2 байта	Целые числа (нет дробной части)	от -32 768 до +32 767
Long	4 байта	Большие целые числа (нет дробной части)	от -2 147 483 648 до +2 147 483 647
Single	4 байта	Число с плавающей точкой одинарной точности	от -3.4e38 до +3.4e38
Double	8 байт	Число с плавающей точкой двойной точности	от -1.8e308 до +1.8e308
Currency	8 байт	Число с плавающей точкой, с фиксированным количеством десятичных разрядов	от -922 337 203 685 477.5808 до +922 337 203 685 477.5807
Date	8 байт	Дата и время – данные типа Date представлены числом с плавающей точкой. Целая часть этого числа выражает дату, а дробная часть – время	от 1 Января 100 до 31 Декабря 9999
Object	4 байта	Ссылка на объект	Любая ссылка на объект
String	изменяется	Набор символов. Тип String может иметь фиксированную или изменяющуюся длину. Чаще используется с изменяющейся длиной	Фиксированной длины — приблизительно до 65 500 символов. Переменной длины — приблизительно до 2 миллиардов символов
Variant	изменяется	Может содержать дату, число с плавающей точкой или строку символов. Этот тип используют в тех случаях, когда заранее не известно, какой именно тип данных будет введён	Число – Double, строка – String

Очевидно, что пользуясь приведённой выше таблицей и правильно выбирая тип данных, можно использовать память более экономно (например, выбрать тип данных Integer вместо Long или Single вместо Double). Однако, используя более компактные типы данных, нужно внимательно следить за тем, чтобы в коде не было попыток уместить в них не соразмерно большие значения.

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

Примечание переводчика: Говоря о переменных в VBA, стоит упомянуть ещё один очень важный момент. Если мы объявляем переменную, но не присваиваем ей какое-либо значение, то она инициализируется значением по умолчанию: • текстовые строки — инициализируются пустыми строками; • числа — значением 0; • переменные типа Boolean — False; • даты — 30 декабря 1899.

Прежде чем использовать переменную или константу, её нужно объявить. Для этого в макрос добавляют вот такую простую строку кода:

Dim Имя_Переменной As Тип_Данных

В показанной выше строке кода Имя_Переменной – это имя переменной, которая будет использована в коде, а Тип_Данных – это один из типов данных из таблицы, приведённой чуть ранее в этой статье. Например:

Dim sVAT_Rate As Single Dim i As Integer

Аналогично объявляются константы, но при объявлении констант обязательно сразу указывается их значение. Например, вот так:

Const iMaxCount = 5000 Const iMaxScore = 100

Объявлять переменные в Excel не обязательно. По умолчанию все введённые, но не объявленные переменные в Excel будут иметь тип Variant и смогут принять как числовое, так и текстовое значение.

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

Использование памяти и скорость вычислений. Если не объявлять переменную с указанием типа данных, то по умолчанию для неё будет установлен тип Variant. Этот тип данных использует больше памяти, чем другие типы данных.Казалось бы, несколько лишних байт на каждую переменную – не так уж много, но на практике в создаваемых программах могут быть тысячи переменных (особенно при работе с массивами). Поэтому излишняя память, используемая переменными типа Variant, по сравнению с переменными типа Integer или Single, может сложится в значительную сумму.К тому же, операции с переменными типа Variant выполняются гораздо медленнее, чем с переменными других типов, соответственно лишняя тысяча переменных типа Variant может значительно замедлить вычисления.
Профилактика опечаток в именах переменных. Если все переменные объявляются, то можно использовать оператор VBA — Option Explicit (о нём расскажем далее), чтобы выявить все не объявленные переменные.Таким образом исключается появление в программе ошибки в результате не верно записанного имени переменной. Например, используя в коде переменную с именем sVAT_Rate, можно допустить опечатку и, присваивая значение этой переменной, записать: «VATRate = 0,175». Ожидается, что с этого момента, переменная sVAT_Rate должна содержать значение 0,175 – но, конечно же, этого не происходит. Если же включен режим обязательного объявления всех используемых переменных, то компилятор VBA сразу же укажет на ошибку, так как не найдёт переменную VATRate среди объявленных.
Выделение значений, не соответствующих объявленному типу переменной. Если объявить переменную определённого типа и попытаться присвоить ей данные другого типа, то появится ошибка, не исправив которую, можно получить сбой в работе программы.На первый взгляд, это может показаться хорошей причиной, чтобы не объявлять переменные, но на самом деле, чем раньше выяснится, что одна из переменных получила не те данные, которые должна была получить – тем лучше! Иначе, если программа продолжит работу, результаты могут оказаться неверными и неожиданными, а найти причину ошибок будет гораздо сложнее.Возможно также, что макрос будет «успешно» выполнен. В результате ошибка останется незамеченной и работа продолжится с неверными данными!

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

Option Explicit

Оператор Option Explicit заставляет объявлять все переменные, которые будут использованы в коде VBA, и при компиляции выделяет все не объявленные переменные как ошибки (прежде чем будет запущено выполнение кода). Применить этот оператор не сложно – просто запишите в самом верху файла VBA такую строку:

Option Explicit

Если хотите всегда вставлять Option Explicit в начало каждого нового созданного модуля VBA, то это можно делать автоматически. Для этого необходимо включить параметр Require Variable Declaration в настройках редактора VBA.

Это делается так:

В меню редактора Visual Basic нажмите Tools > Options
В появившемся диалоговом окне откройте вкладку Editor
Отметьте галочкой параметр Require Variable Declaration и нажмите ОК

При включенном параметре строка Option Explicit будет автоматически вставляться в начало каждого нового созданного модуля.

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

Каждая объявленная переменная или константа имеет свою ограниченную область действия, то есть ограниченную часть программы, в которой эта переменная существует. Область действия зависит от того, где было сделано объявление переменной или константы. Возьмём, к примеру, переменную sVAT_Rate, которая используется в функции Total_Cost. В следующей таблице рассмотрены два варианта области действия переменной sVAT_Rate, объявленной в двух различных позициях в модуле:

Option Explicit Dim sVAT_Rate As Single Function Total_Cost() As Double ... End Function

Если переменная sVAT_Rate объявлена в самом начале модуля, то областью действия этой переменной будет весь модуль (т.е. переменная sVAT_Rate будет распознаваться всеми процедурами в этом модуле).

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

Однако, если будет вызвана какая-то функция, расположенная в другом модуле, то для неё переменная sVAT_Rate будет не известна.

Option Explicit Function Total_Cost() As Double Dim sVAT_Rate As Single ... End Function

Если переменная sVAT_Rate объявлена в начале функции Total_Cost, то её область действия будет ограничена только этой функцией (т.е. в пределах функции Total_Cost, можно будет использовать переменную sVAT_Rate, а за её пределами – нет).

При попытке использовать sVAT_Rate в другой процедуре, компилятор VBA сообщит об ошибке, так как эта переменная не была объявлена за пределами функции Total_Cost (при условии, что использован оператор Option Explicit).

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

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

Для объявления констант также можно использовать ключевые слова Public и Private, но не вместо ключевого слова Const, а вместе с ним.

В следующих примерах показано использование ключевых слов Public и Private в применении к переменным и к константам.

Option Explicit Public sVAT_Rate As Single Public Const iMax_Count = 5000 ...

В этом примере ключевое слово Public использовано для объявления переменной sVAT_Rate и константы iMax_Count. Областью действия объявленных таким образом элементов будет весь текущий проект.

Это значит, что sVAT_Rate и iMax_Count будут доступны в любом модуле проекта.

Option Explicit Private sVAT_Rate As Single Private Const iMax_Count = 5000 ...

В этом примере для объявления переменной sVAT_Rate и константы iMax_Count использовано ключевое слово Private. Областью действия этих элементов является текущий модуль.

Это значит, что sVAT_Rate и iMax_Count будут доступны во всех процедурах текущего модуля, но не будут доступны для процедур, находящихся в других модулях.

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

office-guru.ru

Использование функций листов в коде VBA в Excel 2010

06/24/2015
Время чтения: 9 мин

В этой статье

Сводка. Ознакомьтесь с использованием методов объекта WorksheetFunction в Visual Basic для приложений (VBA), с помощью которых ваш код получит доступ к функциям листов в Microsoft Excel 2010.

Дата последнего изменения: 5 июня 2011 г.

Применимо к: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

В этой статьеОбзор использования методов VBA для доступа к функциям ExcelНазначение следующего доступного идентификатора с помощью метода MaxОтображение сводных данных для выбранного диапазонаСоздание таблицы ежемесячных платежей по кредитуОтображение суммы на основании множества критериевУпорядочивание данных с помощью методов Proper и TrimЗаключениеДополнительные ресурсы

Дата публикации: июнь 2011 г.

Авторы: доктор Джерард М. Вершурен (Gerard M. Verschuuren), Mr. Excel | Марк Робертс (Mark Roberts), корпорация Майкрософт

Содержание

Обзор использования методов VBA для доступа к функциям Excel
Назначение следующего доступного идентификатора с помощью метода Max
Отображение сводных данных для выбранного диапазона
Создание таблицы ежемесячных платежей по кредиту
Отображение суммы на основании множества критериев
Упорядочивание данных с помощью методов Proper и Trim
Заключение
Дополнительные ресурсы

Пример книги Excel: использование функций листа с помощью кода VBA (WorksheetFunction.xlsm)

Обзор использования методов VBA для доступа к функциям Excel

Благодаря объекту WorksheetFunction вы получаете доступ к большинству встроенных функций листа Microsoft Excel 2010, представленных в виде методов Visual Basic для приложений (VBA), что экономит ваше время и силы. Кроме того, вы можете получить доступ к объекту WorksheetFunction через объект Application, используя свойство WorksheetFunction. В случае кода VBA, написанного в книге Excel, это действие необязательно. Объект WorksheetFunction можно использовать в качестве объекта верхнего уровня и получить прямой доступ к его методам, как показано в примере простого кода ниже.

Dim mySum As Integer mySum = WorksheetFunction.Sum(100,200)

Объект WorksheetFunction удобно использовать напрямую, поэтому наличие доступа к нему еще и с помощью объекта Application может показаться нелогичным. Причина в том, что порою нужно использовать функции листа Excel в коде из других приложений. Например, если добавить три элемента управления содержимым в документ Microsoft Word, открыть редактор кода VBA и добавить ссылку на библиотеку объектов Microsoft Excel 14.0, можно написать код, подобный приведенному ниже. В этом примере суммируются значения, введенные в первые два элемента управления, и результат добавляется в оставшийся третий.

Dim xlApp As Excel.Application Dim x, y As Integer Set xlApp = New Excel.Application x = ActiveDocument.ContentControls(1).Range.Text y = ActiveDocument.ContentControls(2).Range.Text ActiveDocument.ContentControls(3).Range.Text = _ xlApp.WorksheetFunction.Sum(x, y)

В отличие от функции листа в пользовательском интерфейсе Excel у параметров метода объекта WorksheetFunction отсутствуют описательные названия. Например, при использовании функции СУММЕСЛИ непосредственно в книге Excel отображается СУММЕСЛИ(диапазон;критерий;диапазон_суммирования). Тем не менее, когда соответствующий метод SumIf используется в коде VBA, технология IntelliSense отображает его параметры так: SumIf(Arg1 As Range, Arg2, [Arg3]) As Double. Если нужна дополнительная информация о значениях того или иного параметра, щелкните имя функции в редакторе кода, а затем нажмите клавишу F1, чтобы открыть соответствующий раздел справки с описанием каждого параметра.

Чтобы выполнить код для образца данных, описанный далее в этой статье, скачайте пример книги — WorksheetFunction.

Назначение следующего доступного идентификатора с помощью метода Max

Макрос NewRecord в первом модуле (Module1) книги WorksheetFunction добавляет новую запись на листе Employees (Сотрудники) и присваивает следующий доступный идентификатор, используя метод Max, который обеспечивает тот же результат, что и функция MAX.

После активации листа Employees (Сотрудники) в первой строке кода используются свойства End и Offset для выбора первой пустой ячейки, следующей за последней ячейкой в столбце A.

Range("A1").End(xlDown).Offset(1, 0).Select

Затем последняя строка кода вызывает метод Max, увеличивает значение на 1 и присваивает это значение текущей ячейке.

ActiveCell = WorksheetFunction.Max(ActiveCell.EntireColumn) + 1

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

Ниже приводится полный код макроса NewRecord.

Sub NewRecord() ' Add new record and increment the value in the first column. Sheets("Employees").Activate ' Select the first cell after the last filled cell in column A. Range("A1").End(xlDown).Offset(1, 0).Select ' Determine the maximum value in the column and add 1. ActiveCell = WorksheetFunction.Max(ActiveCell.EntireColumn) + 1 End Sub

Отображение сводных данных для выбранного диапазона

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

Начальные строки макроса Summary выбирают лист Employees (Сотрудники), а затем предлагают пользователю выбрать диапазон данных в таблице. Если указать значение Selection.Address для параметра Default метода InputBox и задать значение 8 для параметра Type, будет создан объект Range, свойство Address которого ссылается на выбранный пользователем диапазон. Строка objSelect.Select в конце этого фрагмента кода гарантирует, что диапазон будет выбран, если пользователь его введет в поле ввода.

Sheets("Employees").Activate Set objSelect = Application.InputBox(Prompt:="Select range of values to summarize", _ Default:=Selection.Address, _ Type:=8) objSelect.Select

Строки кода ниже передают выбранный диапазон методам Count, Sum и Average.

iCount = WorksheetFunction.Count(objSelect) pSum = WorksheetFunction.Sum(objSelect) pAvg = WorksheetFunction.Average(objSelect)

Последняя строка кода отображает значения в окне сообщения.

MsgBox "Count: " & iCount & vbCr & _ "Sum: " & FormatNumber(pSum, 2) & vbCr & _ "Avg: " & FormatNumber(pAvg, 2)

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

Ниже полностью приведен код макроса Summary.

Sub Summary() ' Displays the sum, average, and count of the selected range. Dim objSelect As Range Dim iCount As Long, pSum As Double, pAvg As Double Sheets("Employees").Activate ' Prompt user for range to summarize. Set objSelect = Application.InputBox(Prompt:="Select range of values to summarize", _ Default:=Selection.Address, _ Type:=8) objSelect.Select ' Use Count, Sum, and Average worksheet functions ' on the selected range. iCount = WorksheetFunction.Count(objSelect) pSum = WorksheetFunction.Sum(objSelect) pAvg = WorksheetFunction.Average(objSelect) ' Display values. MsgBox "Count: " & iCount & vbCr & _ "Sum: " & FormatNumber(pSum, 2) & vbCr & _ "Avg: " & FormatNumber(pAvg, 2) End Sub

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

Макрос PMT_Table вставляет новый лист, а затем создает таблицу ежемесячных платежей для различных сумм кредита с учетом годовых процентных ставок (APR). В Excel это можно сделать с помощью функции ПЛТ, которую вызывает код VBA с помощью соответствующего метода Pmt.

Первая строка кода вызывает макрос InsertSheet, который добавляет новый лист и предлагает пользователю назвать его. Следующие две строки предлагают задать сумму кредита и размер годовой процентной ставки (APR).

Затем вложенные циклы For/Next, показанные в примере кода ниже, создают десять заголовков столбцов, в которых исходная годовая процентная ставка увеличивается на 5 %, а также десять меток строки, в которых увеличена исходная сумма кредита на 10 %.

For i = 0 To 9 For j = 0 To 9 Cells(1, j + 2) = FormatPercent(fAPR + 0.0005 * j) Next j Cells(i + 2, 1) = FormatCurrency(curAmount + (curAmount / 100) * i) Next i

После создания меток строк и столбцов другой набор вложенных циклов For/Next, приведенный в фрагменте кода ниже, подставляет значения в таблицу платежей с помощью метода Pmt. Так как в таблице должны быть представлены ежемесячные платежи, аргумент Arg1, соответствующий rate (ставке), делится на 12. Расчет ежемесячных платежей производится для кредита, выданного сроком на 30 лет, поэтому для аргумента Arg2, соответствующего nper (числу платежей), задается значение 360 (30 лет x 12 месяцев). Значение аргумента Arg3, соответствующего pv (текущему значению, то есть сумме кредита в нашем случае), берется из первой ячейки заполняемой строки.

For i = 2 To 11 For j = 2 To 11 curPMT = WorksheetFunction.Pmt(Arg1:=Cells(1, j) / 12, Arg2:=360, Arg3:=Cells(i, 1)) Cells(i, j) = FormatCurrency(curPMT) Next j Next i

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

Ниже полностью приводится код макроса PMT_Table.

Sub PMT_Table() ' Creates a table of mortgage payments for specified loan amount and APR. Dim i As Integer, j As Integer, curAmount As Currency, fAPR As Single Dim objCell As Range, curPMT As Currency ' Call InsertSheet Sub procedure. InsertSheet ' Prompt user for loan amount and APR. curAmount = InputBox(Prompt:="Loan amount?", _ Default:=60000) fAPR = InputBox(Prompt:="APR?", _ Default:=0.06) ' Create APR column headings, and loan amount row labels. For i = 0 To 9 For j = 0 To 9 Cells(1, j + 2) = FormatPercent(fAPR + 0.0005 * j) Next j Cells(i + 2, 1) = FormatCurrency(curAmount + (curAmount / 100) * i) Next i ' Fill in payment table values. For i = 2 To 11 For j = 2 To 11 curPMT = WorksheetFunction.Pmt(Cells(1, j) / 12, 360, Cells(i, 1)) Cells(i, j) = FormatCurrency(curPMT) Next j Next i ' Format cells as bold, and autofit columns. Cells.Font.Bold = True Cells.EntireColumn.AutoFit End Sub

Отображение суммы на основании множества критериев

С помощью макроса CalcSalaries суммируются заработные платы сотрудников по отделам и расположению. Для создания подобного макроса потребуется много циклов. Избежать их использования помогает метод SumIfs, который впервые был представлен в Excel 2007.

После активации листа Employees (Сотрудники) приведенный ниже код задает три переменных объекта Range для столбцов DEPT (Отдел), LOCATION (Расположение) и SALARY (Заработная плата).

With ActiveCell.CurrentRegion ' Set ranges for the department, location, and salary columns. Set objDept = .Columns(4) Set objLoc = .Columns(5) Set objSal = .Columns(6)

Примечание

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

Далее код предложит пользователю указать отдел и расположение. Выражения с оператором If позволяют оставить поле ввода пустым или отменить его заполнение, если пользователь не планирует применять отдельный фильтр. Символ "звездочка" (*) выступает в роли подстановочного знака.

strDept = InputBox(Prompt:="Which department (blank or cancel for all departments)?", _ Default:="Finance") If strDept = "" Then strDept = "*" strLoc = InputBox(Prompt:="Which location (blank or cancel for all locations)?", _ Default:="Boston") If strLoc = "" Then strLoc = "*"

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

curSum = WorksheetFunction.SumIfs(objSal, objDept, strDept, objLoc, strLoc) MsgBox "The total for " & strDept & " in " & strLoc & " is: " & FormatCurrency(curSum)

Ниже полностью приведен код макроса CalcSalaries.

Sub CalcSalaries() ' Calculates the sum of saleries for the specified department and location. Dim objDept As Range, objLoc As Range, objSal As Range Dim strDept As String, strLoc As String, curSum As Currency Sheets("Employees").Activate ' This With statement returns a Range object that represents the range ' that surrounds the active cell. With ActiveCell.CurrentRegion ' Set ranges for the department, location, and salary columns. Set objDept = .Columns(4) Set objLoc = .Columns(5) Set objSal = .Columns(6) ' Prompt for department and location. strDept = InputBox(Prompt:="Which department (cancel or blank for all departments)?", _ Default:="Finance") If strDept = "" Then strDept = "*" strLoc = InputBox(Prompt:="Which location (cancel or blank for all locations)?", _ Default:="Boston") If strLoc = "" Then strLoc = "*" ' Calculate and display sum of specified salaries. curSum = WorksheetFunction.SumIfs(objSal, objDept, strDept, objLoc, strLoc) MsgBox "The total for " & strDept & " in " & strLoc & " is: " & FormatCurrency(curSum) End With End Sub

Упорядочивание данных с помощью методов Proper и Trim

Макрос CleanUpData в книге WorksheetFunction — это простой макрос, который упорядочивает данные в таблице, применяя правильное написание элементов прописными буквами и удаляя пробелы в начале и в конце каждой ячейки. Для этого код проходит циклом по каждой ячейке в текущей области и вызывает метод Trim, чтобы обрезать лишние пробелы в начале и в конце строки, а также метод Proper, чтобы исправить проблемы в использовании прописной буквы.

Ниже полностью приводится код макроса CleanUpData.

Sub CleanUpData() ' Trims irregular spacing, and corrects capitalization. Dim objCell As Range For Each objCell In ActiveCell.CurrentRegion objCell = WorksheetFunction.Trim(objCell) objCell = WorksheetFunction.Proper(objCell) Next objCell End Sub

Заключение

В этой статье и книге WorksheetFunction есть примеры использования функций листа Excel в коде VBA. Чтобы ознакомиться со всеми методами объекта WorksheetFunction, которые сопоставляются с функциями листа, используемыми в коде VBA в Excel 2010, см. статью WorksheetFunction Members (Excel). Дополнительные сведения о функциях листа Excel, включая примеры с фиктивными данными, см. в статье Функции Excel (по алфавиту).

Дополнительные ресурсы

msdn.microsoft.com

Оформление кода VBA - Microsoft Excel для начинающих

Начиная практиковаться в написании кода VBA, очень важно с самого начала выработать хорошие привычки в оформлении кода, чтобы в дальнейшем написанный код было легко читать и понимать, как он работает.

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

Эта статья посвящена комментариям, отступам в коде и переносам строк – элементам, которые делают код аккуратным и понятным.

Комментарии в VBA

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

Комментарии не участвуют в процессе выполнения программы и не влияют на результат работы макроса. Каждая строка, начинающаяся апострофом (‘), будет считаться в VBA комментарием. Редактор VBA в Excel выделит такую строку зелёным цветом шрифта, чтобы с первого взгляда было понятно, что это комментарий, который не будет выполняться.

Ниже продемонстрировано, как при помощи комментариев поясняется работа простой процедуры Sub:

' процедура 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 ' найдено совпадение с переданной строкой ' сохраняем номер текущей строки и выходим из цикла iRowNumber = i Exit For End If Next i ' сообщение во всплывающем окне сообщает пользователю, ' найдена ли строка, и если найдена – сообщает номер строки If iRowNumber = 0 Then MsgBox "Строка " & sFindText & " не найдена" Else MsgBox "Строка " & sFindText & " найдена в ячейке A" & iRowNumber End If End Sub

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

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

Отступы в коде VBA

Другой приём, делающий написанный код более читаемым – правильно расставлять отступы. В приведённом выше примере видно, что отступ сделан для кода внутри главной процедуры Sub и далее отступ увеличивается для каждого вложенного блока кода. Такие увеличенные отступы помогают понять, где каждый отдельный блок кода начинается и заканчивается.

Переносы строк в VBA

Ещё один способ сделать код более читаемым и облегчить работу с ним – делать переносы и разбивать одну длинную строку кода на несколько коротких. В VBA, чтобы разбить строку, нужно вставить символы » _» (пробел+подчёркивание) непосредственно перед переносом строки. Это сообщает компилятору VBA, что текущая строка кода продолжается на следующей строке.

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

Посмотрите на этот оператор If:

If (index = 1 And sColor1 = "красный") Or (index = 2 And sColor1 = "синий") Or (index = 3 And sColor1 = "зеленый") Or (index = 4 And sColor1 = "коричневый") Then

При помощи переносов строк тот же оператор If может быть записан вот так:

If (index = 1 And sColor1 = "красный") Or _ (index = 2 And sColor1 = "синий") Or _ (index = 3 And sColor1 = "зеленый") Or _ (index = 4 And sColor1 = "коричневый") Then

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

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

office-guru.ru

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

Приветствую всех.

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

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

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

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

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

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

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

Появилась вкладка.

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

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

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

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

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

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

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

Sub FormatPrice()

End Sub

Напишем Hello World:

Sub FormatPrice() MsgBox "Hello World!"End Sub

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

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

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

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

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

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

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

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

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

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

Err: MsgBox "Err!"

ne: On Error GoTo 0 ' Отключаем обработку ошибок ' Циклы бывает, какие захотите Do While True Exit Do

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

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

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

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

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

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

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

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

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

Sub FormatPrice() Sheets("result").Cells.Clear Sheets("data").ActivateEnd Sub

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

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

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

Sheets("result").ActivateDim r As RangeSet r = Range("A1") r.Value = "123"Set r = Range("A3,A5") r.Font.Color = vbRed r.Value = "456"Set r = Range("A6:A7") r.Value = "=A1+A3"

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

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

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

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

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

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

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

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

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

FormatPrice

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

Sheets("data").Activate I = 2 Do While True If GetCell(0, I).Value = "" Then Exit Do ' ... I = I + 1 LoopEnd Sub

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

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

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

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

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

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

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

Перед FormatPrice

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

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

For I2 = 0 To DataCount - 1 GetCellS("result", I2, CurRow).Value = GetCell(I2, I)Next I2

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

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

Sheets("Result").Activate Columns.AutoFit

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

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

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

Уже лучше:

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

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

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

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

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

В начале FormatPrice

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

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

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

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

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

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

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

habr.com

VBA Excel. Оператор If...Then...Else и функция IIf

Однострочная и многострочная конструкции оператора If...Then...Else и функция IIf, используемые в коде VBA Excel - синтаксис, компоненты, примеры.

Оператор If...Then...Else
Функция IIf

Оператор If...Then...Else

Оператор If...Then...Else предназначен для передачи управления одному из блоков операторов в зависимости от результатов проверяемых условий.

Однострочная конструкция

Оператор If...Then...Else может использоваться в однострочной конструкции без ключевых слов Else, End If.

Синтаксис однострочной конструкции If...Then...

If [условие] Then [операторы]

Компоненты однострочной конструкции If...Then...

условие - числовое или строковое выражение, возвращающее логическое значение True или False;
операторы - блок операторов кода VBA Excel, который выполняется, если компонент условие возвращает значение True;

Если компонент условие возвращает значение False, блок операторов конструкции If...Then... пропускается и управление программой передается следующей строке кода.

Пример 1

Sub primer1() Dim d As Integer, a As String d = InputBox("Введите число от 1 до 20", "Пример 1", 1) If d > 10 Then a = "Число " & d & " больше 10" MsgBox a End Sub

Многострочная конструкция

Синтаксис многострочной конструкции If...Then...Else

If [условие] Then [операторы] ElseIf [условие] Then [операторы] ---------------- Else [операторы] End If

Компоненты многострочной конструкции If...Then...Else:

условие - числовое или строковое выражение, следующее за ключевым словом If или ElseIf и возвращающее логическое значение True или False;
операторы - блок операторов кода VBA Excel, который выполняется, если компонент условие возвращает значение True.
пунктирная линия обозначает дополнительные структурные блоки ElseIf [условие] Then + [операторы];
блок операторов после ключевого слова Else выполняется в любом случае, но структурный блок кода Else + [операторы] не является обязательным и может быть пропущен.

Если компонент условие возвращает значение False, следующий за ним блок операторов конструкции If...Then...Else пропускается и управление программой передается следующей строке кода.

Самый простой вариант многострочной конструкции If...Then...Else:

If [условие] Then [операторы] Else [операторы] End If

Пример 2

Sub primer2() Dim d As Integer, a As String d = InputBox("Введите число от 1 до 40", "Пример 2", 1) If d < 11 Then a = "Число " & d & " входит в первую десятку" ElseIf d > 10 And d < 21 Then a = "Число " & d & " входит во вторую десятку" ElseIf d > 20 And d < 31 Then a = "Число " & d & " входит в третью десятку" Else a = "Число " & d & " входит в четвертую десятку" End If MsgBox a End Sub

Функция IIf

Функция IIf проверяет заданное условие и возвращает значение в зависимости от результата проверки.

Синтаксис функции

IIf([условие], [если True], [если False])

Компоненты функции IIf

условие - числовое или строковое выражение, возвращающее логическое значение True или False;
если True - значение, которое возвращает функция IIf, если условие возвратило значение True;
если False - значение, которое возвращает функция IIf, если условие возвратило значение False.

Пример 3

Sub primer3() Dim d As Integer, a As String d = InputBox("Введите число от 1 до 20", "Пример 3", 1) a = IIf(d < 10, d & " - число однозначное", _ d & " - число двузначное") MsgBox a End Sub

При нажатии кнопки «Cancel» или закрытии крестиком диалогового окна InputBox из примеров, генерируется ошибка, так как в этих случаях функция InputBox возвращает пустую строку. Присвоение пустой строки переменной d типа Integer вызывает ошибку. При нажатии кнопки «OK» диалогового окна, числа, вписанные в поле ввода в текстовом формате, VBA Excel автоматически преобразует в числовой формат переменной d.

vremya-ne-zhdet.ru