Секреты программирования. Vba excel примеры

VBA. Чтение и запись в Excel. Оптимизация.

1. Оптимизация с помощью Range.Resize() Впервые столкнувшись с задачей программно прочитать содержимое ячеек Excel, многие используют самый очевидный способ: цикл чтений Cells(row,column).value.

Пример:

For i = 1 To 10 destination.Cells(i,1).value = source.Cells(i,1).value Next

Не трудно заметить, что в данном примере было произведено 10 чтений.

Для ускорения чтения и записи данных из Excel, через VBA, всегда используйте метод класса Range: Resize(). Метод Resize() позволяет прочитать указанный диапазон ячеек в массив, для последующей обработки.

Пример:

arSales = Array() arSales = source.Cells.Resize(10,1) В данном примере, в массив arSales помещаются 10 строк и один столбец с листа source. По сравнению с чтением в цикле, данный метод работает в разы быстрее, поскольку чтение происходит за один этап. Таким-же образом данные помещаются в место назначения.

Пример:

destination.Cells.Resize(10,1) = arSales Если имеется заданная переменная класса Range, то для получения массива можно просто использовать свойство Range.Value Пример:Set rng = Range("DATA") arSales = rng.Value 2. Отключение вычислений на время записи данных. При каждой записи ячейки Excel из кода VBA, программа автоматически пересчитывает все формулы в книге. Это вызывает серьезное замедление. Для ускорения записи имеет смысл отключить автоматические вычисления и обновление экрана.

Пример:

Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.EnableEvents = False

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

Пример:

Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.EnableEvents = True 3. Использование условного форматирования.

Иногда необходимо заполнить и форматировать(раскрасить) таблицу Excel, используя VBA. Для решения этой задачи есть два способа.Первый, "в лоб": форматировать каждую ячейку или диапазон ячеек в цикле непосредственно из программы. У этого способа два недостатка: скорость и неудобство форматирования.

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

Для реализации этого метода можно создать xls-файл, с заголовком и одной строкой, с заданным условным форматированием. Формат этой строки необходимо скопировать в строки, которые будут заполнятся. Поскольку задать формат одновременно для большого кол-ва ячеек нельзя, то делать это придется блоками.

Пример:

rowscount = 20000 ' Кол-во форматируемых строк colscount= 5 ' Кол-во форматируемых столбцов onepass = 1024 ' Кол-во строк форматируемых за один проход src_row = 1 ' Номер строки с заданным условным форматированием For i = src_row+1 To rowscount Step onepass Rows(src_row).Resize(1, colscount).Copy If i + onepass > rowscount Then: onepass = rowscount - i Rows(i).Resize(onepass, colscount).PasteSpecial _ Paste:=xlPasteFormats, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Next

Теперь можно вставлять значения. Настройки цвета и шрифта будут применяться автоматически.

procod.blogspot.com

VBA Excel | Время не ждёт

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

Опубликовано 26.10.201826.10.2018Рубрики VBA Excel

Определение количества измерений произвольного массива в коде VBA Excel с помощью пользовательской функции KolichestvoIzmereniy. Пример использования функции. ...читать далее "VBA Excel. Количество измерений массива"

Опубликовано 10.10.2018Рубрики VBA Excel

Окно Immediate используется в VBA Excel при написании и отладке процедур для тестирования отдельных строк кода, проверки текущих значений переменных и в качестве калькулятора. ...читать далее "VBA Excel. Окно Immediate (отладка кода, вычисления)"

Опубликовано 20.09.2018Рубрики VBA Excel

Число Пи как константа или функция, его возвращающая, отсутствуют в VBA, но есть функция Pi рабочего листа Excel, которой можно воспользоваться. А также есть и другие способы расчета значения этого числа. ...читать далее "VBA Excel. Число Пи (значение)"

Опубликовано 19.09.2018Рубрики VBA Excel

Генерация звукового сигнала с заданной частотой и длительностью и простых мелодий в VBA Excel с помощью функции Beep API из набора базовых функций Windows. ...читать далее "VBA Excel. Функция Beep API (звуковой сигнал, мелодия)"

Опубликовано 16.09.2018Рубрики VBA Excel

Генерация одиночного звукового сигнала в VBA с помощью оператора Beep. Оповещающий гудок при выборе определенных ячеек на рабочем листе Excel. Примеры. ...читать далее "VBA Excel. Оператор Beep (одиночный звуковой сигнал)"

Опубликовано 16.09.201818.09.2018Рубрики VBA Excel

Создание пользовательских типов данных в VBA Excel. Оператор Type, его описание и параметры. Создание массива «одномерных массивов» с пользовательскими данными. ...читать далее "VBA Excel. Пользовательские типы данных (оператор Type)"

Опубликовано 16.08.201827.10.2018Рубрики VBA Excel

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

Опубликовано 15.08.201816.08.2018Рубрики VBA Excel

Оператор Select Case, выполняющий одну или более групп операторов VBA Excel в зависимости от значения управляющего выражения. Синтаксис, компоненты, примеры. ...читать далее "VBA Excel. Оператор Select Case (синтаксис, примеры)"

Опубликовано 07.08.2018Рубрики VBA Excel

Выбор значения из заданного списка аргументов с помощью функции Choose в VBA Excel. Синтаксис и компоненты функции, примеры использования. ...читать далее "VBA Excel. Функция Choose (синтаксис, компоненты, примеры)"

Опубликовано 07.08.201807.08.2018Рубрики VBA Excel

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

Опубликовано 01.08.2018Рубрики VBA Excel

Автоматическая запись текущей даты и времени в первую ячейку новой строки базы данных на листе Excel с помощью кода VBA при выборе второй ячейки этой строки. ...читать далее "VBA Excel. Автоматическая запись текущей даты и времени"

Опубликовано 22.03.201808.07.2018Рубрики VBA Excel

Заливка ячейки цветом в VBA Excel. Фон ячейки. Свойства .Interior.Color и .Interior.ColorIndex. Цветовая модель RGB. Стандартная палитра из 56 цветов. Предопределенные константы. ...читать далее "VBA Excel. Цвет ячейки (заливка, фон)"

Опубликовано 22.03.201808.07.2018Рубрики VBA Excel

Использование функции MsgBox в VBA Excel, ее синтаксис и параметры. Значения, возвращаемые функцией MsgBox. Примеры использования. ...читать далее "VBA Excel. Функция MsgBox (синтаксис, параметры, значения)"

Опубликовано 21.03.201808.07.2018Рубрики VBA Excel

Изменение цвета текста (шрифта) в ячейке рабочего листа Excel с помощью кода VBA. Свойства ячейки (диапазона) .Font.Color, .Font.ColorIndex и .Font.TintAndShade. ...читать далее "VBA Excel. Цвет текста (шрифта) в ячейке"

Опубликовано 21.03.201808.07.2018Рубрики VBA Excel

Использование функции Join в VBA Excel, ее синтаксис и параметры. Значения, возвращаемые функцией Join. Примеры использования. ...читать далее "VBA Excel. Функция Join (синтаксис, параметры, значения)"

Опубликовано 21.03.201807.08.2018Рубрики VBA Excel

Использование функции InputBox в VBA Excel, ее синтаксис и параметры. Значения, возвращаемые функцией InputBox. Примеры использования. ...читать далее "VBA Excel. Функция InputBox (синтаксис, параметры, значения)"

Опубликовано 21.03.201808.07.2018Рубрики VBA Excel

Создание таблицы с помощью кода VBA Excel. Создание «умной» и обычной пользовательской таблицы. Указание стиля «умной» таблицы, добавление строки итогов. Примеры. ...читать далее "VBA Excel. Создание таблицы (умной, обычной)"

Опубликовано 21.03.201808.07.2018Рубрики VBA Excel

Открытие книги Excel из кода VBA. Создание новой книги, присвоение ей имени. Обращение к открытой книге и закрытие. Методы Open, Add и Close объекта Workbooks. ...читать далее "VBA Excel. Рабочая книга (открыть, создать новую, закрыть)"

Опубликовано 21.03.201802.08.2018Рубрики VBA Excel

Обращение к ячейке на листе Excel из кода VBA. Запись информации в ячейку. Чтение информации из ячейки. Очистка значения ячейки. Метод ClearContents объекта Range. ...читать далее "VBA Excel. Ячейки (обращение, запись, чтение, очистка)"

Опубликовано 20.03.201808.07.2018Рубрики VBA Excel

vremya-ne-zhdet.ru

VBA Excel. Цикл For...Next

Цикл For...Next в VBA Excel, его синтаксис и описание отдельных компонентов. Примеры использования цикла For...Next.

Цикл For...Next в VBA Excel предназначен для выполнения группы операторов необходимое количество раз, заданное управляющей переменной цикла - счетчиком. При выполнении цикла значение счетчика после каждой итерации увеличивается или уменьшается на число, указанное выражением оператора Step, или, по умолчанию, на единицу. Когда необходимо применить цикл к элементам, количество которых и индексация в группе (диапазон, массив, коллекция) неизвестны, следует использовать цикл For Each... Next.

Синтаксис цикла For...Next
Компоненты цикла For...Next
Примеры циклов For...Next

Синтаксис цикла For...Next

For counter = start To end [ Step step ] [ statements ] [ Exit For ] [ statements ] Next [ counter ] For счетчик = начало To конец [ Step шаг ] [ операторы ] [ Exit For ] [ операторы ] Next [ счетчик ]

В квадратных скобках указаны необязательные атрибуты цикла For...Next.

Компоненты цикла For...Next

Компонент	Описание
counter	Обязательный атрибут. Числовая переменная, выполняющая роль счетчика, которую еще называют управляющей переменной цикла.
start	Обязательный атрибут. Числовое выражение, задающее начальное значение счетчика.
end	Обязательный атрибут. Числовое выражение, задающее конечное значение счетчика.
Step*	Необязательный атрибут. Оператор, указывающий, что будет задан шаг цикла.
step	Необязательный атрибут. Числовое выражение, задающее шаг цикла. Может быть как положительным, так и отрицательным.
statements	Необязательный** атрибут. Операторы вашего кода.
Exit For	Необязательный атрибут. Оператор выхода из цикла до его окончания.
Next [ counter ]	Здесь counter - необязательный атрибут. Это то же самое имя управляющей переменной цикла, которое можно здесь не указывать.

*Если атрибут Step отсутствует, цикл For...Next выполняется с шагом по умолчанию, равному 1.

**Если не использовать в цикле свой код, смысл применения цикла теряется.

Примеры циклов For...Next

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

Простейший цикл

Заполняем десять первых ячеек первого столбца активного листа цифрами от 1 до 10:

Sub test1() Dim i As Long For i = 1 To 10 Cells(i, 1) = i Next End Sub

Простейший цикл с шагом

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

Sub test2() Dim i As Long For i = 1 To 10 Step 3 Cells(i, 2) = i Next End Sub

Цикл с отрицательными аргументами

Этот цикл заполняет десять первых ячеек третьего столбца в обратной последовательности:

Sub test3() Dim i As Long For i = 0 To -9 Step -1 Cells(i + 10, 3) = i + 10 Next End Sub

Увеличиваем размер шага до -3 и записываем результаты в четвертый столбец активного листа:

Sub test4() Dim i As Long For i = 0 To -9 Step -3 Cells(i + 10, 4) = i + 10 Next End Sub

Вложенный цикл

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

Sub test5() Dim i1 As Long, i2 As Long For i1 = 1 To 10 'Пятой ячейке в строке i1 присваиваем 0 Cells(i1, 5) = 0 For i2 = 1 To 4 Cells(i1, 5) = Cells(i1, 5) + Cells(i1, i2) Next Next End Sub

Выход из цикла

В шестой столбец активного листа запишем названия десяти животных, конечно же, с помощью цикла For...Next:

Sub test6() Dim i As Long For i = 1 To 10 Cells(i, 6) = Choose(i, "Медведь", "Слон", "Жираф", "Антилопа", _ "Крокодил", "Зебра", "Тигр", "Ящерица", "Лев", "Бегемот") Next End Sub

Следующий цикл будет искать в шестом столбце крокодила, который съел галоши. В ячейку седьмого столбца цикл, пока не встретит крокодила, будет записывать строку «Здесь был цикл», а когда обнаружит крокодила, запишет «Он съел галоши» и прекратит работу, выполнив команду Exit For. Это будет видно по ячейкам рядом с названиями животных ниже крокодила, в которых не будет текста «Здесь был цикл».

Sub test7() Dim i As Long For i = 1 To 10 If Cells(i, 6) = "Крокодил" Then Cells(i, 7) = "Он съел галоши" Exit For Else Cells(i, 7) = "Здесь был цикл" End If Next End Sub

Результат работы циклов For...Next из примеров:

Результат работы циклов For...Next

Такие данные на активном листе Excel вы получите, если последовательно запустите на выполнение в редакторе VBA все семь подпрограмм из примеров, демонстрирующих работу циклов For...Next.

Цикл с дробными аргументами

Атрибуты start, end и step могут быть представлены числом, переменной или числовым выражением:

For i = 1 To 20 Step 2 For i = a To b Step c For i = a - 3 To 2b + 1 Step c/2

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

'Значения атрибутов до округления For i = 1.5 To 10.5 Step 2.51 'Округленные значения атрибутов For i = 2 To 10 Step 3

Старайтесь не допускать попадания в тело цикла For...Next неокругленных значений аргументов, чтобы не получить непредсказуемые результаты его выполнения. Если без дробных чисел не обойтись, а необходимо использовать обычное округление, применяйте функцию рабочего листа WorksheetFunction.Round для округления числа перед использованием его в цикле For...Next.

vremya-ne-zhdet.ru

Основы работы с модулями классов - Модули классов - Программирование на VBA - Статьи об Excel

Создание приложения на vba путем написания процедур и функций стоит называть процедурным программированием. Написание участков кода, называемых процедурами и функциями, которые описывают какое-либо действие, и последующее последовательное исполнение этих кодов, является основным методом программирования на vba. При этом используется доступ к объектной модели Excel или других приложений. Данные и подпрограммы (функции и процедуры) функционально не связаны между собой. Это значит, что объявляя переменную «длина хвоста» на уровне модуля, нельзя задать ей различные значения для двух процедур.

При объектно-ориентированном программировании (ООП) подход иной. Данные и подпрограммы связаны между собой и описываются в классе.

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

Класс имеет следующую структуру:

Поле – элемент класса для хранения данных,

Свойство – элемент класса для хранения данных с возможностью их обработки,

Метод – аналог процедуры или функции,

Событие – сигнал при изменении состояния объекта, например исполнения метода или изменения данных.

Из всех принципов ООП в vba реализуемы только два: Абстрагирование и инкапсуляция.

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

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

Для создания класса в vba редакторе выберите в меню Insert строку Class Module. Назовите созданный класс путем переименования созданного модуля. В файле с примером он называется ExampleClass. А модуль, демонстрирующий использование этого класса называется ExClassManagement. В свойствах класса, кроме имени так же есть параметр Instancing. Указывается, будет ли виден класс из другой книги при установке ссылки на данную книгу. При установке Private (по умолчанию) класс виден только в данной книге, при установке PublicNotCreatable, класс не будет доступен из другой книги, однако экземпляр класса доступен будет, если он создан в данной книге.

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

Класс - это всего лишь описание объекта. Для использования возможностей класса, необходимо создать экземпляр класса (объект). Существует несколько способов:

Способ 1:

Private Sub TestClass() Dim cl As ExampleClass Set cl = New ExampleClass End Sub Данный способ корректен абсолютно

Способ 2:

Dim cl As ExampleClass Private Sub TestClass() Set cl = New ExampleClass End Sub Этот способ отличается от первого способа тем, что экземпляр класса объявляется вне процедуры и работать с ним можно во всех процедурах модуля. При замене Dim на Public экземпляр класса доступен во всем проекте, если объявляется вне объектного модуля.

Способ 3:

Dim cl WithEvents As ExampleClass Private Sub TestClass() Set cl = New ExampleClass End Sub

Экземпляр класса объявляется с событиями, и если в классе описаны события объекта, они будут доступны. Работает только при объявлении в объектном модуле (модуль класса, формы, листа, книги) Способ 4:

Private Sub TestClass() Dim cl As New ExampleClass End Sub Так называемый неявный метод создания экземпляра класса. В этом случае объект создается при первом обращении к переменной cl. Наверное, предпочтительнее сначала объявлять переменную (выделяется память), а затем явно создавать объект.

Уничтожение экземпляра класса

Естественно, после использования экземпляра класса, необходимо очистить память. Делается это одним способом: Set cl = Nothing

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

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

Созданный класс ExampleClass не имеет полей, свойств, методов, событий, поэтому и созданный на его основе объект (экземпляр класса) cl, так же бесполезен.

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

Создание закрытого поля:

Dim sBody As String или Private sBody As String

Создание открытого поля:

Public Head As String И теперь свойство Head доступно у экземпляра класса cl.

В него можно записать

cl.Head = "FHead" и прочитать Debug.Print cl.Head Создать поле с пользовательским типом данных не удастся.

Поля используются для хранения данных в объекте. Данные будут доступны, пока объект существует. Однако нельзя указать значение по-умолчанию для поля и нельзя сделать поле только для чтения.

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

Свойство это способ доступа к данным внутри объекта. Выглядят как поля, однако, это функции (назовем их методами). Называются они Property Get для чтения данных из объекта, и Property Let для записи данных в объект. Есть еще третье Property Set для установки ссылки на другой объект. Но это можно сделать и при помощи Property Let, поэтому Property Set вещь бесполезная.

Синтаксис Property Get [Public | Private | Friend] [Static] Property Get имя [(аргументы)] [As тип] [произвольный код] [имя=выражение] [Exit Property] [произвольный код] [имя=выражение] [End Property]

Элемент	Описание
Public	Не обязательно. Делает метод открытым во всех модулях проекта
Private	Не обязательно. Метод будет доступен только в модуле класса, то есть не будет виден как свойство
Friend	Не обязательно. Метод будет виден во всех модулях проекта, но не будет виден по ссылке на класс. то есть при конструкции Dim cl as new ExampleClass; Dim cll as object; Set cll=cl в объекте cll свойство с модификатором Friend видно не будет, а в объекте cl будет
Static	Не обязательно. Значения объявленных локальных переменных внутри метода сохраняются между обращениями. Не распространяется на переменные, объявленные на уровне модуля класса
имя	Обязательно. Имя метода будет являться и именем свойства. при этом оно должно совпадать для методов Property Let или Property Set
аргументы	Не обязательно. По сути, это аргументы функции. Имена и типы аргументов должны совпадать с аргументами метода Property Let
тип	Не обязательно. Тип данных, возвращаемый функцией может быть Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String, Object, Variant, пользовательским типом. Должен совпадать с типом данных выражения
произвольный код	Не обязательно. Любое количество строк кода, например математические операции с возвращаемым значением
выражение	Значение, возвращаемое методом Property Get. Если не указать, метод вернет пустую строку для типа String, False для Boolean, 0 для Integer и так далее

Замечания: - Элемент Exit Property означает выход из метода, и по сути аналог Exit Function. В методе их может быть несколько. - Внутри метода Property Get может быть сколько угодно процедур и функций, но сам метод не может входить в состав других процедур и функций, одноименных методов Property Get в модуле класса быть не может.

Синтаксис Property Let

[Public | Private | Friend] [Static] Property Let имя ([аргументы,] значение) [произвольный код] [Exit Property] [произвольный код] [End Property]

Элемент	Описание
Public	Не обязательно. Делает метод открытым во всех модулях проекта
Private	Не обязательно. Метод будет доступен только в модуле класса, то есть не будет виден как свойство
Friend	Не обязательно. Метод будет виден во всех модулях проекта, но не будет виден по ссылке на класс. то есть при конструкции Dim cl as new ExampleClass; Dim cll as object; Set cll=cl в объекте cll свойство с модификатором Friend видно не будет, а в объекте cl будет
Static	Не обязательно. Значения объявленных локальных переменных внутри метода сохраняются между обращениями. Не распространяется на переменные, объявленные на уровне модуля класса
аргументы	Не обязательно. По сути, это аргументы функции. Имена и типы аргументов должны совпадать с аргументами метода Property Get
значение	Не обязательно. Имя переменной метода, которой будет присваиваться значения свойства. Тип данных значения должен совпадать с типом метода Property Get
произвольный код	Не обязательно. Любое количество строк кода, например проверка указываемого значения. И конечно необходимо здесь передать значение из локальной переменной (значение) метода во внешнюю переменную модуля класса. А значение внешней переменной передать в выражение метода Property Get

Замечание: - Аналогично методу Property Get

Синтаксис Property Set

[Public | Private | Friend] [Static] Property Set имя ([аргументы,] ссылка) [произвольный код] [Exit Property] [произвольный код] [End Property]

Элемент	Описание
Public	Не обязательно. Делает метод открытым во всех модулях проекта
Private	Не обязательно. Метод будет доступен только в модуле класса, то есть не будет виден как свойство
Friend	Не обязательно. Метод будет виден во всех модулях проекта, но не будет виден по ссылке на класс. то есть при конструкции Dim cl as new ExampleClass; Dim cll as object; Set cll=cl в объекте cll свойство с модификатором Friend видно не будет, а в объекте cl будет
Static	Не обязательно. Значения объявленных локальных переменных внутри метода сохраняются между обращениями. Не распространяется на переменные, объявленные на уровне модуля класса
аргументы	Не обязательно. По сути, это аргументы функции. Имена и типы аргументов должны совпадать с аргументами метода Property Get
ссылка	Не обязательно. Имя переменной метода, которая будет ссылкой на объект. Тип данных ссылки должен быть object
произвольный код	Не обязательно. Любое количество строк кода, например работа со свойствами объекта. И конечно необходимо здесь передать значение из локальной переменной (значение) метода во внешнюю переменную модуля класса. А значение внешней переменной передать в выражение метода Property Get

Замечание: - Не забывайте, передача ссылки на объект происходит при помощи Set.

Примеры создания свойства в модуле класса Pacient:

Private sHeight As Single Public Property Get Height() As Single Height = sHeight End Property Public Property Let Height(ByVal sHeightValue As Single) sHeight = sHeightValue End Property Свойство Height доступно для чтения и записи. при этом при записи и чтении никаких модификаций с данными не производится, то есть такая конструкция аналогична открытому полю (Public Height as Single), а потому создание ее смысла не имеет. Просто лишний код.Private sHeight As Single Public Property Get Height() As Single Height = sHeight End Property Свойство доступно только для чтения. Метод Property Let не создавался. Предполагается, что данные переменной sHeight заданы в какой-либо процедуре модуля класса.Const sHeight As Single = 2 Public Property Get Height() As Single Height = sHeight End Property

Свойство доступно только для чтения и содержит константу. Единственный способ открытия константы из объектного модуля. Конструкция Public Const sHeight As Single = 2 работать не будет.

Private sHeight As Single Public Property Let Height(ByVal sHeightValue As Single) sHeight = sHeightValue End Property

Свойство только для записи. при попытке его прочитать, появится ошибка.

Private sHeight As Single Public Property Get Height() As Single Height = sHeight End Property Public Property Let Height(ByVal sHeightValue As Single) Select Case sHeightValue Case Is > 250 sHeight = 250 Case Is < 50 sHeight = 50 Case Else sHeight = sHeightValue End Select End Property Происходит проверка данных при записи значения.Private sHeight As Single Public Property Get Height() As Single Height = sHeight / 100 End Property Public Property Let Height(ByVal sHeightValue As Single) sHeight = sHeightValue End Property Пример произвольного кода в методе Property Get. В свойство Height записывается значение в сантиметрах, а читается в метрах.Private sHeight As Single Public Property Get Height(ByVal Scales As Integer) As Single Select Case Scales Case 1 Height = sHeight Case 2 Height = sHeight / 10 Case 3 Height = sHeight / 1000 End Select End Property Public Property Let Height(ByVal Scales As Integer, ByVal sHeightValue As Single) Select Case Scales Case 1 sHeight = sHeightValue Case 2 sHeight = sHeightValue * 10 Case 3 sHeight = sHeightValue * 1000 End Select End Property Свойство со аргументом. Аргумент Scales указывает, в каких единицах измерения записывается рост, а в каких читается.Private sHeight As Single Private sWeight As Single Public Property Let Height(ByVal sHeightValue As Single) sHeight = sHeightValue End Property Public Property Let Weight(ByVal sWeightValue As Single) sWeight = sWeightValue End Property Property Get IMT() As Single If sHeight <> 0 Then IMT = Round(sWeight / sHeight ^ 2, 1) End Property Свойства Height и Weight только для записи, свойство IMT только для чтения и содержит индекс массы тела, рассчитанный на основе веса и роста. Расчет происходит всякий раз при обращении к свойству IMT.

Методы класса

В созданном классе можно создавать процедуры и функции. Они будут видны в экземпляре класса как методы, если указаны как Public и не видны, если указаны как Private. Все процедуры и функции, которые не планируется использовать как методы, должны быть Private. Этого требует принцип инкапсуляции.

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

Код в классе	Вызов метод
Public Function IMT(sWeight As Single, sHeight As Single) As Single If sHeight <> 0 Then IMT = Round(sWeight / sHeight ^ 2, 1) End Function	Dim oPac As Pacient Set oPac = New Pacient Debug.Print oPac.IMT(113, 1.83) Set oPac = Nothing
Public IMT As Single Public Sub CalcIMT(sWeight As Single, sHeight As Single) If sHeight <> 0 Then IMT = Round(sWeight / sHeight ^ 2, 1) End Sub	Dim oPac As Pacient Set oPac = New Pacient oPac.CalcIMT sWeight:=113, sHeight:=1.83 Debug.Print oPac.IMT Set oPac = Nothing

В первом случае значение индекса массы тела возвращает функция, во втором открытое поле IMT.

Классический вариант:

Код в классе

Вызов метод

Private sHeight As Single Private sWeight As Single Private sIMT As Single Public Property Get Height() As Single Height = sHeight End Property Public Property Let Height(ByVal sHeightValue As Single) sHeight = sHeightValue End Property Public Property Get Weight() As Single Weight = sWeight End Property Public Property Let Weight(ByVal sWeightValue As Single) sWeight = sWeightValue End Property Public Property Get IMT() As Single IMT = sIMT End Property Public Sub CalcIMT() If sHeight <> 0 Then sIMT = Round(sWeight / sHeight ^ 2, 1) End Sub

Dim oPac As Pacient Set oPac = New Pacient oPac.Height = 1.83 oPac.Weight = 113 oPac.CalcIMT Debug.Print oPac.IMT Set oPac = Nothing

Свойства Height и Weight для чтения и записи, свойство IMT только для чтения. Индекс массы тела рассчитывается методом CalcIMT.

События класса

Созданный класс уже имеет два скрытых события: Class_Initialize - Происходит при создании экземпляра класса. В этом событии удобно указывать значения свойств и переменных по-умолчанию.Class_Terminate - Происходит при уничтожении экземпляра класса. Экземпляр класса уничтожается, когда процедура, в которой он был объявлен, завершает свою работу. Или после явной деинициализации экземпляра класса: Set oPac=Nothing

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

Private WithEvents oPac As Pacient А в самом модуле класса указывается событие:

Синтаксис:[Public] Event имя[(аргументы)]

Элемент	Описание
Public	Не обязательно. Делает событие открытым во всех объектных модулях проекта. По-умолчанию, все пользовательские события Public
имя	Имя события, которое будет видно в экземпляре класса
аргументы	Не обязательно. События могут иметь аргументы, которым можно передавать значения или ссылку на объект

Замечание:Событие должно указываться в самом начале модуля класса

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

RaiseEvent имя[(значение аргументов)]

Элемент	Описание
имя	Обязательно. Имя события, для которого указывается триггер
значение аргументов	Не обязательно. Если при указании события были указаны аргументы, здесь через запятую указываются их значения

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

Код в классе:

Public Event IMTCalculated(IMTValue As Single) Private sHeight As Single Private sWeight As Single Private sIMT As Single Public Property Get Height() As Single Height = sHeight End Property Public Property Let Height(ByVal sHeightValue As Single) sHeight = sHeightValue End Property Public Property Get Weight() As Single Weight = sWeight End Property Public Property Let Weight(ByVal sWeightValue As Single) sWeight = sWeightValue End Property Public Sub CalcIMT() If sHeight <> 0 Then sIMT = Round(sWeight / sHeight ^ 2, 1) RaiseEvent IMTCalculated(sIMT) End Sub Вызов метода в форме: Private WithEvents oPac As Pacient Private Sub CommandButton1_Click() Set oPac = New Pacient oPac.Height = 1.83 oPac.Weight = 113 oPac.CalcIMT End Sub Private Sub oPac_IMTCalculated(IMTValue As Single) Me.Label1.Caption = IMTValue End Sub Событие IMTCalculated срабатывает после расчета индекса массы тела, и в форме значение аргумента IMTValue присваивается надписи. Таким образом, отпадает необходимость в создании отдельного свойства IMT.

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

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

Задача	Пример файла
Выполнение одной процедуры несколькими контролами формы
Управление событиями на уровне приложения

Есть случаи, когда использовать пользовательские классы удобнее, чем процедуры и функции:

Задача	Пример файла
Сокрытие деталей реализации компонента для упрощения работы с ним и создание собственной библиотеки компонентов

www.excelworld.ru

Операторы условия в VBA - Microsoft Excel для начинающих

Наиболее важные операторы условия, используемые в Excel VBA – это операторы If … Then и Select Case. Оба этих выражения проверяют одно или несколько условий и, в зависимости от результата, выполнят различные действия. Далее мы поговорим об этих двух операторах условия подробнее.

Оператор «If … Then» в Visual Basic

Оператор If … Then проверяет условие и, если оно истинно (TRUE), то выполняется заданный набор действий. Также может быть определён набор действий, которые должны быть выполнены, если условие ложно (FALSE).

Синтаксис оператора If … Then вот такой:

If Условие1 Then Действия в случае, если выполняется Условие1ElseIf Условие2 Then Действия в случае, если выполняется Условие2Else Действия в случае, если не выполнено ни одно из УсловийEnd If

В этом выражении элементы ElseIf и Else оператора условия могут не использоваться, если в них нет необходимости.

Ниже приведён пример, в котором при помощи оператора If … Then цвет заливки активной ячейки изменяется в зависимости от находящегося в ней значения:

If ActiveCell.Value < 5 Then ActiveCell.Interior.Color = 65280 'Ячейка окрашивается в зелёный цвет ElseIf ActiveCell.Value < 10 Then ActiveCell.Interior.Color = 49407 'Ячейка окрашивается в оранжевый цвет Else ActiveCell.Interior.Color = 255 'Ячейка окрашивается в красный цвет End If

Обратите внимание, что как только условие становится истинным, выполнение условного оператора прерывается. Следовательно, если значение переменной ActiveCell меньше 5, то истинным становится первое условие и ячейка окрашивается в зелёный цвет. После этого выполнение оператора If … Then прерывается и остальные условия не проверяются.

Более подробно о применении в VBA условного оператора If … Then можно узнать на сайте Microsoft Developer Network.

Оператор «Select Case» в Visual Basic

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

Синтаксис оператора Select Case вот такой:

Select Case ВыражениеCase Значение1 Действия в случае, если результат Выражения соответствует Значению1Case Значение2 Действия в случае, если результат Выражения соответствует Значению2…Case Else Действия в случае, если результат Выражения не соответствует ни одному из перечисленных вариантов ЗначенияEnd Select

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

В следующем примере при помощи конструкции Select Case изменяется цвет заливки текущей ячейки в зависимости от находящегося в ней значения:

Select Case ActiveCell.Value Case Is <= 5 ActiveCell.Interior.Color = 65280 'Ячейка окрашивается в зелёный цвет Case 6, 7, 8, 9 ActiveCell.Interior.Color = 49407 'Ячейка окрашивается в оранжевый цвет Case 10 ActiveCell.Interior.Color = 65535 'Ячейка окрашивается в жёлтый цвет Case 11 To 20 ActiveCell.Interior.Color = 10498160 'Ячейка окрашивается в лиловый цвет Case Else ActiveCell.Interior.Color = 255 'Ячейка окрашивается в красный цвет End Select

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

Case Is <= 5	Таким образом при помощи ключевого слова Case Is можно проверить, удовлетворяет ли значение Выражения условию вида <=5.
Case 6, 7, 8, 9	Так можно проверить, совпадает ли значение Выражения с одним из перечисленных значений. Перечисленные значения разделяются запятыми.
Case 10	Так проверяется, совпадает ли значение Выражения с заданным значением.
Case 11 To 20	Таким образом можно записать выражение для проверки, удовлетворяет ли значение Выражения условию вида от 11 до 20 (эквивалентно неравенству «11<=значение<=20»).
Case Else	Вот так, при помощи ключевого слова Else, указываются действия для того случая, если значение Выражения не соответствует ни одному из перечисленных вариантов Case.

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

Более подробную информацию о работе VBA оператора Select Case можно найти на сайте Microsoft Developer Network.

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

office-guru.ru

excel-vba - Лучшие краткие примеры необходимости Excel VBA

Доступны ли пользователи целевой аудитории?

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

Пример 1

В соответствии с соглашением об именах существует много файлов excel:

c:\data1.xls c:\data2.xls c:\data3.xls

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

Я хотел, чтобы результат выглядел так:

id data hyperlink 1 extRefA1 c:\data1.xls 3 extRefA1 c:\data3.xls 500 extRefA1 c:\data500.xls

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

Пример 2

Это похоже на пример 1, но мне пришлось объединить разные данные диаграммы.

Данные в каждом файле excel были в столбцах:

X Y 1 5 2 10 3 5 4 60

Мне нужны объединенные данные диаграммы в строках:

1 2 3 4 data1 5 10 5 60 data3 30 60 4 2 data500 25 45 20 5

Итак, я создал VBA, который поместил массив формул, содержащий внешнюю ссылку в TRANSPOSE.

Массив формул data1 выглядел примерно так:

=TRANSPOSE('c:\[data1.xls]Sheet1'!$B$2:$B$5)

Я не знаю, как другие используют Excel и VBA, но они оказались очень полезными для меня.

Фрэнсис

qaru.site

Ускоряем работу VBA в Excel / Хабр

Предисловие

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

Перед началом работы

Перед тем, как перейти прямо к сути, я хотел бы обратить внимание на пост: Несколько советов по работе с VBA в Excel. В частности, в блоке “Ускорение работы макросов” есть полезные примеры кода, которые стоит использовать вместе с моими советами по ускорению работы, для достижения максимального результата.

Ускоряем работу макроса

Итак, к сути… Для того что бы реально ускорить работу VBA в Ecxel нужно понимать, что обращение к ячейке на листе — занимает значительно время. Если Вы хотите записать в ячейку одно значение, то это не займет значительного времени, но если Вам потребуется записать(прочитать, обратиться) к тысячам ячеек, то это потребует гораздо большего времени. Что же делать в таких случаях? На помощь приходят массивы. Массивы хранятся в памяти, а операции в памяти VBA выполняет в сотни, а то и в тысячи раз быстрее. Поэтому, если у Вас в данных тысячи, сотни тысяч значений, то время выполнения макроса может занимать от нескольких минут до нескольких часов, а если эти данные перенести в массив, то выполнение макроса может сократиться до нескольких секунд (минут).

Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.

Пример

Предположим, что у нас есть данные на “Лист1” (“Sheet1”). Данные содержаться в 50 колонках (колонки содержат названия) и 10 000 строк. К примеру, нам нужно в последнюю колонку внести значение, которое равно значению во второй колонке, деленное на значение в третьей колонке (начиная со 2-й строки, так как первая содержит заглавие). Потом мы возьмем первые 10 колонок и скопируем их на “Лист2” (“Sheet2”), для дальнейшей обработки (для других потребностей). Пусть пример и банальный, но, как мне кажется, он может отобразить всю суть данного поста.'Для явной инициализации переменных, включаем эту опцию 'Это поможет избежать многих ошибок Option Explicit Sub Test() 'К листам будем обращаться через переменные Dim Sheet1_WS, Sheet2_WS As Worksheet 'Переменная для прохождения срок на листе (в массиве) Dim i As Long 'Массив, в котором будут храниться наши данные Dim R_data As Variant 'Переменные последней строки и колонки Dim FinalRow, FinalColumn As Long 'Можно инициализировать лист не по названию, а по порядковому номеру 'Set Sheet1_WS = Application.ThisWorkbook.Worksheet("Sheet1") Set Sheet1_WS = Application.ThisWorkbook.Sheets(1) Set Sheet2_WS = Application.ThisWorkbook.Sheets(2) 'Поиск последней не пустой строки в первой колонке 'Нужно, что бы данные не были отфильтрованы, иначе последняя строка будет последней строкой в фильтре 'Также в последней строке, в первой колонке, не должно быть пустой ячейки. Конечно, если в этой строке вообще есть данные. Иначе последней строкой будет последняя не пустая ячейка. FinalRow = Sheet1_WS.Cells(Rows.Count, 1).End(xlUp).Row '=10 000 'Поиск последней не пустой колонки в первой строке FinalColumn = Sheet1_WS.Cells(1, Columns.Count).End(xlToLeft).Column '=50 'Присваиваем массиву диапазон данных на Листе 1 R_data = Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) For i = 2 To FinalRow 'Выполняем нужные нам операции с данными. 'Проверяем, что бы не было деления на ноль. 'Предполагается, что в колонке 2 и 3 стоят числовые данные 'Иначе потребуется обработка ошибок If R_data(i, 3) <> 0 Then R_data(i, FinalColumn) = R_data(i, 2) / R_data(i, 3) End If Next i 'Копируем данные из массива обратно на Лист1 'Перед этим очищаем данные на листе (если есть форматирование или формулы, то лучше Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) = R_data 'Копируем данные на Лист2, копируем первые 10 колонок. Sheet2_WS.Range(Sheet2_WS.Cells(1, 1), Sheet2_WS.Cells(FinalRow, 10)) = R_data 'Закрываем книгу и сохраняем её Workbooks(Application.ThisWorkbook.Name).Close SaveChanges:=True End Sub

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

Dim R_new() As Variant ............................................ ' Явно указываем размер массива ReDim R_new(1 To FinalRow, 1 To 50) As Variant ........................................... Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, 50)) = R_new()

Заключение

Большинство операций над данными можно выполнять в массиве, при этом, отображать на лист только результат. Иногда целесообразным бывает показать результат на лист, потом выполнить некоторые действия (например, сортировку) и снова загрузить данные в массив.

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

В дальнейшем я планирую написать советы (примеры) по быстрому поиску данных на листе, но это уже будет другой пост. Если будут вопросы, комментарии, пожалуйста, пишите.

Спасибо за внимание. Удачных разработок.

habr.com