Vba excel создать коллекцию

Обновлено: 07.07.2024

' Count - это количество книг в коллекции.

' Напечатайте полное имя книги с именем Пример.xlsm

' Напечатайте полное название книги, которая была открыта второй

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

Ч то такое коллекция?

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

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

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

Dim mark As Long

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

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

' Объявите переменную для каждой оценки

Dim mark1 As Long

Dim mark2 As Long

Dim mark100 As Long

' Сохраните отметки на листе в переменной

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

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

Dim collMarks As New Collection

' Прочитайте 100 значений в коллекцию

For Each c In Sheet1.Range("A1:A100")

' Эта строка используется для добавления всех значений

К оллекции против Массивов

Мы рассмотрели, что общего у коллекций и массивов. Так в чем же разница и зачем использовать один вместо другого?

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

П ример: когда Массив лучше

Представьте, что у вас есть лист оценок учеников с одним учеником на строку:

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

' Получить последнюю строку - это количество студентов

Dim lStudentCount As Long

lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

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

Dim arr() As Long

ReDim arr(1 To lStudentCount)

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

Массивы используются, когда размер фиксирован.

Давайте теперь посмотрим на второй пример, где мы не знаем количество предметов заранее.

П ример: когда Коллекция лучше

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

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

Таким образом, в этом примере количество студентов не является фиксированным и сильно меняется. Здесь вы не знаете количество студентов заранее. Поэтому вы не знаете, какой размер массива создать.

Вы также можете изменить размер массива для каждого элемента по мере его добавления. Это очень неэффективно и довольно грязно.

Так что для этого примера лучше использовать коллекцию.

Dim coll As New Collection

' Добавить элемент - VBA следит за изменением размера

' удалить элемент - VBA следит за изменением размера

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

Коллекция используется, когда размер часто изменяется.

Е ще одно преимущество коллекций

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

Создать коллекцию
Добавьте несколько предметов
Прочитайте предмет

Так что, если вы не имеете дело с большим количеством предметов, тогда использование Коллекции может быть намного удобнее в использовании.

Н едостаток коллекций

Основные типы данных (т.е. переменные, такие как string, date, long, currency и т.д.) в коллекциях доступны только для чтения. Вы можете добавить или удалить элемент, но не можете изменить его значение. Если вы собираетесь изменять значения в группе элементов, вам нужно будет использовать массив.

Если вы храните объект в коллекции, вы можете изменить значение, так как коллекция хранит ссылку, а не фактический объект.

Коллекция только для чтения

Теперь, когда мы знаем, когда и зачем использовать коллекцию, давайте посмотрим, как ее использовать.

К ак создать коллекцию

Вы можете объявить и создать в одной строке, как это делает следующий код:

' Объявить и создать

Dim coll As New Collection

Как видите, вам не нужно указывать размер. После того, как ваша коллекция создана, вы можете легко добавлять в нее элементы.

Вы также можете объявить и затем создать коллекцию, если и когда вам это нужно.

Dim coll As Collection

Set coll = New Collection

Н езначительная разница между этими методами

Разница между этими методами заключается в том, что для первого всегда создается коллекция. Для второго метода коллекция создается только при достижении строки Set. Таким образом, вы можете установить код для создания коллекции только при соблюдении определенного условия.

Dim coll As Collection

' Создать коллекцию, если файл найден

If filefound = True Then

Set coll = New Collection

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

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

У даление всех элементов из коллекции

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

Set Coll = New Collection.

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

Во многих приложениях требуется создавать группы связанных объектов и управлять ими. Существует два способа группировки объектов: создать массив объектов и создать коллекцию.

Массивы удобнее всего использовать для создания фиксированного числа строго типизированных объектов и работы с ними. Информацию о массивах см. в разделе Массивы.

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

Коллекция является классом, поэтому необходимо объявить экземпляр класса перед добавлением в коллекцию элементов.

Если коллекция содержит элементы только одного типа данных, можно использовать один из классов в пространстве имен System.Collections.Generic. Универсальная коллекция обеспечивает строгую типизацию, так что в нее нельзя добавить другие типы данных. При извлечении элемента из универсальной коллекции не нужно определять или преобразовывать его тип данных.

Для примеров в этом разделе включите операторы Imports для System.Collections.Generic System.Linq пространств имен и.

Использование простой коллекции

В примерах этого раздела используется универсальный класс List<T>, который позволяет работать со строго типизированными списками объектов.

В следующем примере создается список строк, а затем выполняется итерация по строкам с помощью цикла For Each. Следующий оператор.

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

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

Можно использовать для. Оператор Next , а не For Each оператор для прохода по коллекции. Для этого доступ к элементам коллекции осуществляется по позиции индекса. Индекс элементов начинается с 0 и заканчивается числом, равным количеству элементов минус 1.

В приведенном ниже примере выполняется перебор элементов коллекции с помощью оператора For…Next вместо For Each .

В приведенном ниже примере элемент удаляется из коллекции путем указания удаляемого объекта.

В приведенном ниже примере удаляются элементы из универсального списка. Вместо For Each оператора, a для. Используется оператор Next, который выполняет итерацию в убывающем порядке. Связано это с тем, что в результате работы метода RemoveAt элементы, следующие за удаленным элементом, получают меньшее значение индекса.

Для типа элементов в List<T> можно также определить собственный класс. В приведенном ниже примере класс Galaxy , который используется объектом List<T>, определен в коде.

Виды коллекций

В этом разделе описываются следующие часто используемые классы коллекций:

класс Collection в Visual Basic.

Классы System.Collections.Generic

Универсальную коллекцию можно создать, используя один из классов в пространстве имен System.Collections.Generic. Универсальная коллекция применяется в том случае, если все элементы в коллекции имеют одинаковый тип данных. Универсальная коллекция обеспечивает строгую типизацию, позволяя добавлять данные только необходимого типа.

В таблице ниже перечислены некоторые из часто используемых классов пространства имен System.Collections.Generic.

Класс	Описание
Dictionary<TKey,TValue>	Предоставляет коллекцию пар «ключ-значение», которые упорядочены по ключу.
List<T>	Представляет список объектов, доступных по индексу. Предоставляет методы для поиска по списку, его сортировки и изменения.
Queue<T>	Представляет коллекцию объектов, которая обслуживается в порядке поступления (FIFO).
SortedList<TKey,TValue>	Представляет коллекцию пар "ключ-значение", упорядоченных по ключу на основе реализации IComparer<T>.
Stack<T>	Представляет коллекцию объектов, которая обслуживается в обратном порядке (LIFO).

Классы System.Collections.Concurrent

Классы пространства имен System.Collections.Concurrent следует использовать вместо соответствующих типов пространств имен System.Collections.Generic и System.Collections, если несколько потоков параллельно обращаются к такой коллекции. Дополнительные сведения см. в статьях Потокобезопасные коллекции и System.Collections.Concurrent.

Классы System.Collections

Классы в пространстве имен System.Collections хранят элементы не в виде конкретно типизированных объектов, а как объекты типа Object .

Везде, где это возможно, следует использовать универсальные коллекции пространства имен System.Collections.Generic или пространства имен System.Collections.Concurrent вместо устаревших типов пространства имен System.Collections .

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

Класс	Описание
ArrayList	Представляет массив объектов, размер которого динамически увеличивается по мере необходимости.
Hashtable	Представляет коллекцию пар «ключ-значение», которые упорядочены по хэш-коду ключа.
Queue	Представляет коллекцию объектов, которая обслуживается в порядке поступления (FIFO).
Stack	Представляет коллекцию объектов, которая обслуживается в обратном порядке (LIFO).

Пространство имен System.Collections.Specialized предоставляет специализированные и строго типизированные классы коллекций, такие как коллекции строк, связанные списки и гибридные словари.

Класс Collection в Visual Basic

Класс Collection в Visual Basic можно использовать для доступа к элементу коллекции по числовому индексу или ключу String . Элементы можно добавлять в объект коллекции с указанием или без указания ключа. Если добавить объект без ключа, необходимо использовать его числовой индекс для доступа к нему.

Класс Collection в Visual Basic хранит все свои элементы как тип Object , поэтому можно добавить элемент любого типа данных. Нет никакой защиты от добавления неподходящих типов данных.

Везде, где это возможно, следует использовать универсальные коллекции в пространстве имен System.Collections.Generic или пространстве имен System.Collections.Concurrent вместо класса Collection в Visual Basic.

Дополнительные сведения см. в разделе Collection.

Реализация коллекции пар «ключ-значение»

Универсальная коллекция Dictionary<TKey,TValue> позволяет получить доступ к элементам коллекции с помощью ключа каждого элемента. Каждый элемент, добавляемый в словарь, состоит из значения и связанного с ним ключа. Извлечение значения по его ключу происходит быстро, так как класс Dictionary реализован как хэш-таблица.

В приведенном ниже примере создается коллекция Dictionary и выполняется перебор словаря с помощью оператора For Each .

Чтобы вместо этого использовать инициализатор коллекции для создания коллекции Dictionary , можно заменить методы BuildDictionary и AddToDictionary приведенным ниже методом.

В приведенном ниже примере используется метод ContainsKey и свойство Item[] Dictionary для быстрого поиска элемента по ключу. Item Свойство позволяет получить доступ к элементу в коллекции с elements помощью elements(symbol) кода в Visual Basic.

В приведенном ниже примере вместо этого используется метод TryGetValue для быстрого поиска элемента по ключу.

Использование LINQ для доступа к коллекции

Для доступа к коллекции можно использовать язык LINQ. Запросы LINQ обеспечивают возможности фильтрации, упорядочения и группировки. Дополнительные сведения см. в разделе Начало работы с LINQ в Visual Basic.

В приведенном ниже примере выполняется запрос LINQ применительно к универсальной коллекции List . Запрос LINQ возвращает другую коллекцию, содержащую результаты.

Сортировка коллекции

Приведенный ниже пример демонстрирует процедуру сортировки коллекции. В примере сортируются экземпляры класса Car , которые хранятся в List<T>. Класс Car реализует интерфейс IComparable<T>, который требует реализации метода CompareTo.

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

В методе ListCars оператор cars.Sort() сортирует список. Этот вызов метода SortList<T> приводит к тому, что метод CompareTo вызывается автоматически для объектов Car в List .

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

Вы можете определить коллекцию, реализовав интерфейс IEnumerable<T> или IEnumerable. Дополнительные сведения см. в разделе Перечисление коллекции.

В приведенном ниже примере определяется настраиваемый класс коллекции с именем AllColors . Этот класс реализует интерфейс IEnumerable, который требует реализации метода GetEnumerator.

Метод GetEnumerator возвращает экземпляр класса ColorEnumerator . Класс ColorEnumerator реализует интерфейс IEnumerator, который требует реализации свойства Current, метода MoveNext и метода Reset.

Iterators

Итератор используется для выполнения настраиваемого перебора коллекции. Итератор может быть методом или методом доступа get . Итератор использует оператор yield для возвращения каждого элемента коллекции по одному за раз.

Для вызова итератора используется оператор For Each. Следующий оператор. Каждая итерация цикла For Each вызывает итератор. При достижении оператора Yield в итераторе возвращается выражение, и текущее расположение в коде сохраняется. При следующем вызове итератора выполнение возобновляется с этого места.

Дополнительные сведения см. в разделе итераторы (Visual Basic).

В приведенном ниже примере используется метод-итератор. Метод итератора содержит Yield оператор, который находится внутри блока for. Следующий цикл. В методе ListEvenNumbers каждая итерация тела оператора For Each создает вызов метода-итератора, который переходит к следующему оператору Yield .

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

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

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

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

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

Метод Add

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

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

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

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

* Аргументы «До» и «После» не могут применяться одновременно. Если аргументу «До» или «После» присвоено числовое значение, оно должно быть в пределах диапазона от 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, добавление в коллекцию трех элементов, определение количества элементов в коллекции, извлечение одного и того же элемента по индексу и по ключу:

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

Структура объектов

Проще всего рассматривать объектную модель как некое дерево или иерархическую структуру, так как каждый объект имеет свое ответвление. Кусочек этой структуры вы можете увидеть на рисунке далее.

Самый главный объект, вершина нашей структуры, это собственно само приложение Excel - объект Application. Какие объекты входят в Excel? правильно книги (файлы Excel), поэтому в объекте Application содержится коллекция из книг - объект Workbooks. Из чего состоят книги - из листов, диаграмм, соответственно внутри объекта Workbooks мы видим объект Worksheets и Charts. Идем дальше, лист в свою очередь состоит из строк, столбцов, ячеек это объекты Rows, Columns, Range.

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

Объекты и коллекции объектов

Многие объекты, которые мы перечислили выше являются коллекциями. Коллекция - это группа однотипных объектов (или классов). Например Workbook s - это коллекция всех открытых в данный момент книг (объектов Workbook). А коллекция Worksheet s состоит из листов книги (объектов Worksheet), и так далее. Как правило все коллекции заканчиваются буквой S.

Давайте посмотрим, как обращаться к конкретным элементам коллекции в коде VBA.

Мы можем указать порядковый номер (начиная с единицы) элемента в коллекции. Worksheets(3) - в этом случае мы обращаемся к 3-му листу книги.

Мы также можем указать название листа в кавычках Worksheets("Название листа").

Аналогичным образом можно обратиться к конкретной книге Workbook("Названием книги").

Чтобы обратиться к объекту Excel (к листу, или ячейке) необходимо указать ссылку на этот объект, перечислив полный путь к нему в соответствии с объектной моделью.

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

На самом деле полный путь писать не обязательно. Application - можно практически всегда не указывать. Workbooks("Название книги") - можно не указывать, если необходимо обратиться к ячейке из активной книги, а Worksheets("Название листа") можно не писать в случае если код должен выполнять действия на активном листе. Т.е. в можно упростить до:

Но всегда имейте ввиду, что это будет ячейка на активном листе.

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

Все объекты имеют свойства и методы. Например, объект Range (диапазон ячеек) имеет свойство Value, в котором содержится значение.

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

А следующий код присвоит свойству Value новое значение 12345.

Протестируйте эти функции в своем файле.

Кстати, есть свойства объектов, которые доступны только для чтения. Т.е. вы сможете только получить значения этих свойств, но не сможете присвоить им другие значения. Например, свойство Range("A1").Address которое содержит адрес ячейки. При попытке записать новое значение в такое свойство будет возникать ошибка.
Или же свойство Worksheets.Count - вы можете только получить количество листов, но не можете изменить количество листов, задав новое свойство Count. Для этого существуют методы объектов.

Методы объектов

В отличие от свойств, которые просто сохраняют или передают значение, методы выполняют какие-либо действия с объектом. Например метод Worksheets.Add создает новый пустой лист в книге. Еще один пример это метод Clear, который позволяет очистить содержимое ячеек. Следующий код очистит данные и форматы из диапазона ячеек A1:C10.

Есть аналогичная функция, которая не удаляет при этом форматирование ячеек, а только очищает их от значений

Методы могут иметь дополнительные аргументы. Например, метод AddComment имеет обязательный аргумент Text, в котором необходимо передать текст комментария к ячейке.

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

Приемы и лайфхаки

Автоматическое заполнение кода по начальным символам

Часто, особенно на первых порах, вы не запомните точное название всех объектов, но будете помнить их первые символы. В редакторе кода вы можете использовать отобразить список названий и автоматически записать объект по первым символам. Для этого используйте сочетание клавиш Ctrl+ J и увидите список из предложенных вариантов.

Просмотр свойств и методов у объекта

Когда вы будете писать свои программы, то редактор кода VBE будет подсказывать список свойств и методов у объекта. Чтобы отобразить этот список достаточно ввести объект, например, Worksheets и поставить точку, отобразится список свойств и методов. Выберите нужный с помощью мышки или же используйте стрелки. Вы также можете начать писать название свойства и редактор кода подберет подходящее. Когда выбор сделан - нажимайте клавишу Tab.

Явно указывайте тип переменных

Предыдущий лайфхак не будет работать для созданных локальных переменных до тех пор, пока вы явно не укажите их тип. Например, если вы захотели создать переменную MySheet в которой хотите хранить текущий лист. То список свойств и методов у такой переменной вы не увидите. Но стоит добавить строчку кода и явно указать тип переменной:

И подсказки снова заработают.

Быстрый просмотр справки

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

Чтобы быстро посмотреть информацию по интересующему вас объекту, свойству или методу просто поставьте курсор на интересующий элемент кода и нажмите клавишу F1. В окне браузера откроется официальная справка по этому элементу.

Поиск по объектной модели

Также вначале пути у вас постоянно будут возникать вопрос "А как называется свойство, которое отвечает за. ". Иногда мы и не заем есть ли такое свойство/метод вообще. Хороший вариант изучения - просмотр объектной модели. Переходите в редактор кода VBA и нажимайте клавишу F2. Откроется окно с поиском по объектной модели. Тут вы найдете все имеющиеся свойства, методы, события и прочие элементы в модели. Просматривайте их, как правило в названии элемента заложена его суть и вы найдете то что искали. А в процессе поиска будете запоминать и другие элементы, которые будут попадаться на глаза.

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

Открываем объектную модель клавишей F2.
Так как мы хотим защитить лист, то логично предположить, что это метод в объекте Worksheet. Введем его в строку поиска и нажмем бинокль.
В результатах поиска выбираем наш объект Worksheet и просматриваем его элементы.
Находим метод Protect (Защита).
Мы также можем просмотреть описание всех аргументов этого метода
Конечно если что-то не понятно, то жмем клавишу F1 и открываем справку по этому методу с подробным описанием каждого аргумента.

На этом данную статью завершим, а в следующих мы более подробно поговорим про самый распространенный объект Excel - объект Range.

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

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

К статье прикреплено 2 примера файла с макросами на основе этой функции:

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

Смотрите также расширенную версию макроса на базе этой функции:

Макрос FolderStructure выводит в таблицу Excel список файлов и подпапок с отображением структуры (вложенности файлов и подпапок)

ПРИМЕЧАНИЕ: Если вы выводите на лист список имен файлов картинок (изображений), то при помощи этой надстройки вы сможете вставить сами картинки в ячейки соседнего столбца (или в примечания к этим ячейкам)

Внимание: если требуется, чтобы поиск не зависел от регистра символов в маске файла
(к примеру, обнаруживались не только файлы .txt, но и .TXT и .Txt),
поставьте первой строкой в модуле директиву Option Compare Text

Этот код позволяет осуществить поиск нужных файлов в выбранной папке (включая подпапки), и выводит полученный список файлов на лист книги Excel:

Ещё один пример использования:

PS: Найти подходящие имена файлов в коллекции можно при помощи следующей функции:

Нужно выше дописать
Dim FSO As Object?
Или в настройках excel 2016 что-то не так? Притом ругается на все не объявленные переменные.
А переменные типа Filename$ вообще не воспринимает как переменные. В чем может быть дело?
Гуглинг пока не помог.

Здравствуйте.
DoEvents никак не влияет на правильность работы (и не может повлиять)
А количество активных гиперссылок на листе Excel ограничено, — никак не сделать, чтобы на одном листе было более 50 или 65 тысяч АКТИВНЫХ гиперссылок.

Доброго времени суток. Огромное спасибо за программу!

Добавлю от себя и задам вопрос.

При использовании "DoEvents" программа может не правильно работать, в том числе выводить не все значения. Я ее закомментировал.

При привышении 65532 строк гиперссылки прекращают формироваться. Как можно победить?

Здравствуйте.
Под заказ что угодно могу сделать (платно)

Здравствуйте. Для примера из файла "FilenamesCollectionEx.xls" - можете сделать, чтобы выводимый на лист Excel список файлов был отсортирован по размеру(по уменьшению)?

Здравствуйте.
Могу сделать под заказ
Оформляйте заказ на сайте, и обязательно прикрепляйте пример файла с примером результата.

Здравствуйте! Меня тоже интересует макрос по поиску файлов. Можете сделать так что бы в ячейках к примеру A1 задать имя файла, A2 задать тип файла и A3 путь к папке?

Огромное Вам спасибо! Столько времени мне съэкономили.
СПА-СИ-БО! :-)

Спасибо. Очень полезная вещь!

Добрый день
В случае если в именах файлов встречаются нестандартные символы (допустимые в Win) макрос выдает ошибку
Ошибка в строке ДатаСоздания = FileDateTime(ПутьКФайлу)
Можно добавить onError Resume Next но это пропуск ошибки будет а размер файла не будет определен. Есть ли варианты сделать определение размера файлов и для таких файлов тоже?

Игорь, подскажите, а можно ли в файл FilenamesCollectionEx.xls добавить маску имени подпапки, в которой производить поиск? Ситуация: файл с одинаковм именем может лежать в подпапках с разными именами. Я точно знаю, что нужная мне версия должна лежать в определенной подпапке. И проверять таким образом только их?

Так вроде и то и другое выводится
Код открыт ведь, — поменяйте как вам надо, если лишний столбец мешает.

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

Отбой, разобрался. Виноват оказался не этот макрос, а тот, который его результаты использовал. Мораль — люди, не юзайте Dir, если вам нужно что-то сделать с папкой, к которой он обращается.

В моём макросе нет MoveFolder - так что мой макрос точно не виноват в вашей проблеме.
Проблема - либо в неверном использовании MoveFolder (не то или не туда перемещаете), либо нет прав доступа на перемещение в заданное место.

Игорь, всё это прекрасно. Непонятно только, что нужно сделать с Вашим макросом, чтобы после его вызова с папкой можно было бы ещё и что-нибудь сделать, например, переместить. Сейчас после вызова FSO.MoveFolder вылетает с ошибкой Access denied. Проверено, виноват именно Ваш макрос — если закомментировать ТОЛЬКО его вызов, FSO.MoveFolder отрабатывает нормально.

Спасибо, ОГРОМНОЕ.
Выручайте ребята! макрос в целом отличный, но для моих целе нужно немного переделать.
Нужно чтоб все файлы находящиеся в каждой папке были в одной ячейке через разделитель ( | )
Например:
C:\images\04-2016\10\32g.jpg|C:\images\04-2016\10\33g.jpg|C:\images\04-2016\10\33g.jpg

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

Добрый день!
Скажите, пожалуйста, сделали ли вы макрос для Александра?
Если да, то за сколько его можно приобрести?
Если нет, то какие сроки выполнения?
Спасибо!

Напишите на почту стоимость и сроки выполнения

Александр, в этом случае нужен более сложный макрос.
Могу сделать под заказ.

Здравствуйте, Макрос хороший. Всё отлично выводит. Но как сделать дерево? Имеется несколько папок, далее нажимаешь на папку или плюс или еще что-то, она открывается, появляется подпапки, опять жмешь на подпапку появляются подпапки и т.д.

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

Ограничение на полное имя файла, включая расширение - 259 символов. Соответственно, все файлы, имеющие более длинное имя при выполнении
Set curfold = FSO.GetFolder(FolderPath)
будут проигнорированы. Тестировал на EX2010, W7 и MSServer 2008. У меня из 28 (curfold.Соunt) файлов реально в коллекции только 15 (curfold.items(1). curfold.items(15))

А как сделать макрос чтобы он мне показал только пустые папки?

Ограничений по длине имени файла, вроде как, нет (по крайней мере, за много лет использования этого кода на тысячах компов, с проблемами не сталкивался)

Добрый день.
файл 148 знаков (рус.буквы) не обрабатывается,
и сам файл на сервере (если файл на раб.столе то все работает)
какая максимальная длина имени и можно-ли ее обойти.

Адаптировал к access - все работает, спасибо, очень помогло

Ринат, посмотрите макрос обработки файлов из папки.
Там выводится диалоговое окно папки, и обрабатываются все файлы в ней (независимо от имён файлов)

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

Большое спасибо автору! Список использую для каталогизации архива сканов документов.

Да, можем сделать такой макрос под заказ.
Минимальная стоимость заказа 1500 руб.

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

или например на основе Вашего FilenamesCollectionEx.xls нашел все файлы на диске/папке нужные -нажимаешь на файл и ты нужен выбрать ячейку куда вписать имя файла
заранее спасибо

У меня почему-то размер файла в байтах выводится абсолютно иной, иногда даже с отрицательным значением.
Пример:
1.вес файла 3 840 327 Кб или 3,66 Гб, а таблица выдает "-362 472 675"
2.вес файла 5 082 087 Кб или 4,84 Гб, таблица выдает "909 089 137"

Добрый день! Подскажите, возможно ли добавить столбцы "продолжительность" и "ширина кадра", которые имеются в данных файлов?

Здравствуйте, Елизавета.
Причин может быть несколько, навскидку:
- проблемный файл, или файл, к которому у вас нет доступа (ошибка 53 - файл не найден)
- слишком длинное имя папки (много уровней вложенности) и/или файла
- сбой в файловой системе
- ошибка в макросе (что-то в коде не учтено)

Игорь, огромное вам спасибо за эту работу!
Несколько лет использую ваш файл для классификации фильмов, но пару недель назад почему-то он перестал работать. Никакой критичности в этом нет, т.к. главное исправила благодаря обсуждениям тут, но мне непонятно и жутко интересно, почему так происходит. Может, это связано с активацией офиса(примерно в то же время было)? Офис 10й.
У меня 2 вкладки в этом файле, обновляю список на 2й, и затем новые позиции копирую в первую (накапливаю). При обновлении списка, после 60-70 позиций, макрос останавливается и сообщает об ошибке Run-time error 53 со сслыкой на строку ДатаСоздания = FileDateTime(ПутьКФайлу). Дело не файле, т.к. его удаление не помогло. Я добавила в скрипт "On Error Resume Next", список обновляется до конца, но перестают запускаться фильмы по гиперссылке в 1й вкладке "не удается открыть указанный файл" (во 2й работают), хотя файл и макросы одни и те же. Знаете, в чем может быть причина?

Читайте также: