Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel collection

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

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

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

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

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

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

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

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

Метод Add

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

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

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

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

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

Метод Item

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

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

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

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

Метод Remove

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

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

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

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

Свойство Collection.Count

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

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

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

Объект Collection(VBA) как альтернатива динамическому массиву

При формировании перечня элементов(или значений) объект Collection может быть с успехом применен вместо динамического массива. В семейсто можно добавить(метод Add) или удалить(метод Remove) любое количество элементов. Причем, вставить новый элемент можно перед(после) существующим, т.е. можно получить ОТСОРТИРОВАННЫЙ перечень. Кроме того, на большом объеме данных есть существенное преимущество в скорострельности перед методом ReDim Preserve для динамического списка. Индексы в семействе начинаются с 1, а количество элементов определяется свойством Count.

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

Объест Collection объявляется как Dim col As New Collection.
Использование New в данном случае обязательно.

В вызывающей процедуре можно применить 2 варианта:
Dim col As New Collection Здесь col — пустое семейство.
Set col = GetFilesList(. )
или
Dim col As Collection Здесь col — пустая ссылка на семейство.
Set col = GetFilesList(. )
Во втором варианте переменная может быть в состоянии Nothing, и обращение к свойству Count или любому методу семейства сгенерирует ошибку.
Специалисты настоятельно советуют использовать первый вариант всегда.
Другие возможности Collection:
Можно использовать конструкцию For Each . Next.
При добавлении нового элемента можно указать уникальный строковый ключ и затем использовать его при обращении к элементу. Например:
col.Add 100, «New»
MsgBox col(«New») ( или col!New )
col.Remove «New»
К сожалению, прочитать значение ключа нельзя.

Читать еще:  После открытия файла excel

Function GetFilesList(Optional PathName As String, _
Optional FoldersOnly As Boolean) As Collection
‘ Функция возвращает отсортированное семейство имен файлов или
‘вложенных папок (если установлен FoldersOnly).
‘ Аргумент PathName может принимать значение, распознаваемое
‘функцией Dir().
On Error GoTo GetFilesList_err
Dim col As New Collection
Dim strFileName As String, strCompareFileName As String, _
i As Integer, j As Integer, MidPos As Integer

If FoldersOnly Then
strFileName = Dir$(PathName, vbDirectory)
Else
strFileName = Dir$(PathName)
End If

Do Until Len(strFileName) = 0
‘ В режиме поиска вложенных папок игнорирует папки «.»,»..» и

‘файлы.
If FoldersOnly Then
‘Если первый символ — «.»(код 46), игнорируется.
If Asc(strFileName) = 46 Then GoTo NextFile
‘Если отсутствует аттрибут vbDirectory, игнорируется.
If Not (GetAttr(PathName & strFileName) And vbDirectory) = _
vbDirectory Then GoTo NextFile
End If

i = 1
j = col.Count
‘Если коллекция пуста — добавляет значение.
If j = 0 Then
col.Add strFileName
GoTo NextFile
End If

SearchBlock:
‘ Вычисляется средний индекс в диапазоне и извлекается
‘соответствующее значение.
M >strCompareFileName = col(MidPos)

‘ Имя нового файла сравнивается с текущим значением.
Select Case StrComp(strFileName, strCompareFileName, _
vbTextCompare)
Case -1 ‘strFileName ‘ Если текущий индекс совпадает с начальным( strCompareFileName
‘Новое значение больше текущего.
If MidPos >= j Then
‘ Если текущий индекс совпадает с конечным(> — для надежности),
‘добавляется после конечного значения в диапазоне.
col.Add strFileName, , , j
Else
‘Диапазон ограничивается второй половиной и цикл повторяется.
i = MidPos + 1
GoTo SearchBlock
End If

‘ Case 0 strFileName = strCompareFileName
End Select
NextFile:
strFileName = Dir$(, vbDirectory)
Loop

Using Collections in VBA

A Collection is an type that contains a set of related objects — in memory. Collection types are very useful because it is easy to add items to it and iterate over the items in the collection for whatever purpose you have in mind. Specialized Collection types are used within Office applications. In MS Access for example a Recordset can be a collection of records in a table, the Forms collection contains all the open Forms. In the below the VBA Collections class is explained.

Declare and instantiate a collection

A collection object variable must be created as a «new» collection unless you are going to assign it to an existing collection. Typically, the code to create a collection looks like this:

Add items to the collection

The code below shows how you can add items to a collection. In our case we added custom class objects (Person) to the collection, but you can add anything, including standard types such as strings, or Office application built-in objects.

When you add multiple items, like above, you only have to declare the variable once — you can re-use it with each new object.

To create multiple objects you will have to instantiate new ones for each.

Add item to the collection include a key

Optionally you can include a key when adding an item. The key can be used to access an item in the collection instead of using the positional index. This key should be an unique string. You will have to make sure there is an unambiguous way to construct the key, for example in NL we use Postal Code + house number to uniquely identify an address. Adding a dupplicate error results in error 457: This key is already associated with an element of this collection .

Test if item exists in collection

Often you don’t want to include the same item twice in the collection. To achieve this, you can loop through the collection to see if it is there. Alternatively, if a key was defined for the collection, you can try to get the item directly. If it does not exist, an error occurs, which you should handle.

Find an item in a collection

Find an item in a collection using its key

If you specified the key argument in the collections .Add , you can later use that key to obtain the item from the collection:

Find an item in a collection by looking at the individual properties

An alternative way to pick up the item you require is by looping through the collection and testing one or more properties. Add Exit For if you expect only one item. Alternatively you can process all the items that match the selection criteria.

Читать еще:  Excel vba удалить лист

Delete item from the collection (Remove)

To remove an item from the collection use the .Remove method. For the index argument either the number in the collection or — if available- the key may be used.

CODE VBA — AGORA Software BV Copyright 1997-2019

Vba excel collection

Эта книга поможет освоить интегрированную среду разработки VBA и научиться программировать в VBA с использование объектов, их свойств, методов и событий. Обсуждаемые приемы программирования иллюстрируются примерами, которые можно сразу же опробовать на практике. Полученные знания вы сможете применить как для настройки и усовершенствования популярный офисных приложений, включая приложения Office XP, так и для создания собственных приложений.

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

Книга: VBA для чайников

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

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

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

* Можно объявить имя переменной для объекта и затем использовать оператор Set, чтобы создать ее. При этом в операторе Set нужно использовать ключевое слово New, чтобы создать новую коллекцию. Например:

Dim colMixedBag As Collection

Set colMixedBag = New Collection

colMixedBag.Add «Howard, Ethel»

* Можно обязать VBA создать объект автоматически при первом использовании переменной в программном коде. Для этого нужно поместить ключевое слово New в объявление переменной;

Dim colSetOfStuff As New Collection

‘ следующий оператор создает коллекцию

‘ при добавлении в нее целого значения

Для заполнения созданной коллекции данными используйте метод Add (Добавить) точно так же, как при добавлении объектов во встроенные коллекции VBA-приложения (см, главу 12). Примеры использования метода Add вы найдете и в предыдущем разделе.

Синтаксис метода Add выглядит так:

Add (элемент[, имя] [, before индекс] [, after индекс])

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

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

colFinancials — Add 14 32 3 . 44, «Продажи за февраль»

В этом операторе в коллекции colFinancials добавляется значение 14323 .44. Одновременно для этого элемента создается имя. Вполне допустимо при задании имени использовать переменную типа String.

Имя не только легче запомнить, чем индекс, оно, к тому же, обеспечивает единственный надежный способ доступа к конкретному элементу коллекции. При использовании методов Add и Remove позиции элементов данных в коллекции могут меняться. Даже если элемент с №69 станет 29-м, вы все равно сможете воспользоваться значением этого элемента, если знаете его имя.

Имя элементу можно присвоить только с помощью метода Add. Чтобы присвоить имя элементу, уже существующему без имени, придется удалить имеющуюся копию с помощью Remove и использовать Add для добавления элемента снова.

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

Предположим, что вам нужно вставить новый элемент перед 35-м элементом в коллекции.

Вы можете использовать для этого оператор следующего вида:

colAnimals. Add strSpecies, before 35

Добавленный элемент станет 35-м элементом коллекции, подвинув все последующие (В отличие от- массивов, элементы коллекций нумеруются с 1.) Чтобы сообщить VBA, где вставить новый элемент, можно использовать и имя существующего элемента, например:

Читать еще:  Как нарисовать линию в excel

colОвощи. Add = stiCopt, after «Помидор»

В данном случае VBA найдет в коллекции существующий элемент с именем Помидор и сразу после него вставит новый.

Ясно, что в оператор, использующий метод Add, можно включить либо before, либо after, но никак не оба этих аргумента.

Для добавления в коллекцию множества элементов можно использовать цикл For . . . Next, подобно тому, как это делалось с массивами. Например:

Dim X As Integer, Y As Integer

colHouseOfValues.Add Y * X

Говоря очевидное, сообщаю вам, что метод Remove удаляет элемент из коллекции. Объект для удаления можно задать либо с помощью индекса, либо с помощью имени, например:

Не забывайте, что при удалении элемента, VBA, так сказать, «заполняет дыры» — номера всех элементов, следующих за удаленным, уменьшатся на 1.

Запутаться в размерах коллекции несложно, особенно после многократного использования Add и Remove. Родовой объект Collection имеет только одно свойство – Count (подсчет), — но, в силу вышесказанного, это свойство оказывается жизненно важным. Значение этого свойства можно присвоить переменной, например, так:

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

If colPrices.Count > 1000

MsgBox «Слишком много позиций!»

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

Dim Z As Integer

For Z = 1 To col Prices.Count

MsgBox «Цена » & col Prices ( Z)

Но лучше для этого использовать цикл For Each. . . Next (см. главу 8).

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

datДеньРождения = colДниРождения(«Василий Али-Бабаевич»)

К сожалению, вам придется отслеживать имена самим — VBA не предлагает возможности прочитать имена элементов коллекции. Кроме того, нельзя изменить имя элемента иначе, как удалив этот элемент из коллекции и затем добавив его вновь.

Точно так же нельзя в существующий элемент коллекции поместить новое значение. Следующий оператор присваивания работать не будет: colInventory(1465) = 119

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

Как уже было сказано, применение класса Collection VBA ограничивается тем, что в нем нельзя хранить данные пользовательских типов. С этим ограничением можно смириться, если выбрать подходящую систему создания имен элементов. В следующем примере при каждом проходе цикла For. . .Next в коллекции создается запись базы данных. Эта запись состоит из трех полей, каждое из которых идентифицируется именем, составленным из простого описательного признака и номера записи. Вот соответствующий программный код:

Dim I As Integer, strName As String

Dim strPhone As String, strAddress As String

For I = 1 To Total ‘ I идентифицирует номер записи

‘ получение данных для этой записи

strName = InputBox(«Введите имя для этой записи»)

strPhone = InputBox(«Введите номер телефона для _ этой записи»)

strAddress = InputBox(«Введите адрес для этой записи»)

‘ Добавление «полей» для этой записи в коллекцийе

colDatabase.Add strName, «Имя» & I

colDatabase.Add strPhone, «Номер телефона» & I

colDatabase.Add strAddress, «Адрес» & I

Впоследствии можно извлечь информацию, хранимую в отдельной записи по описательному признаку и номеру, как в следующем операторе:

Если вы собираетесь создавать реальные программы для управления информацией в базах данных, не используйте для этого коллекции объектов — они имеют слишком много ограничений. Вместо этого используйте подключаемые компоненты доступа к базам данных (соответствующие примеры вы найдете в главе 14). Если же вы хотите выполнить всю работу самостоятельно, создайте свои собственные классы для объектов, позволяющих работать с пользовательскими типами данных. Ваши объекты должны позволять хранить в них любые комбинации данных, которые могли бы извлекаться как свойства. Кроме того, неплохо было бы добавить программный код для проверки правильности сохраняемой в отдельных элементах информации. (Модули классов рассматриваются, хотя и не слишком подробно, в главе 14.)

Ссылка на основную публикацию
Adblock
detector