Библиотеки vba excel

Классы библиотеки VBA

В библиотеку VBA входят три класса объектов:

1. Collection — встроенный в язык VBA динамический класс данных. Очень мощная языковая конструкция, объединяющая достоинства массивов и динамических линейных списков. Также как и массив задает упорядоченную совокупность элементов, к каждому из которых возможен прямой доступ. Но, в отличие от массива может объединять элементы разного типа, не имеет фиксированной длины, создается динамически. Допускается эффективная вставка и удаление любых элементов коллекции, что для массивов, вообще говоря, недопустимо. В сравнении с динамическими списками, для которых также возможны эффективно реализуемые операции по вставке и удалению элементов, несомненным преимуществом коллекции является возможность прямого доступа к ее элементам. При этом доступ возможен как по индексу элемента, так и по ключу, в качестве которого может использоваться произвольное строковое выражение. В классе определены методы Add, Item, Remove и свойство Count.
2. ErrObject — этот специфический класс описывает единственный объект Err, создаваемый автоматически вместе с проектом и имеющим глобальную область определения. Этот объект содержит информацию о последней ошибке выполнения. Он имеет два метода — Clear и Raise и шесть свойств: Number, Source, Descrition, HelpFile, HelpContext и LastDLLError.
3. Global — этот класс имеет одно свойство UserForms и два метода — Load и Unload. Глобальное свойство UserForms позволяет получить коллекцию форм, связанных с приложением. Заметьте, сам объект UserForm находится в одной библиотеке — Forms 2.0, а коллекция этих объектов принадлежит глобальному классу библиотеки VBA. Методы Load и UnLoad также связаны с формами. Они позволяют загрузить и выгрузить форму. Оба метода должны быть глобальными и не могут принадлежать к методам самой формы, поскольку первый начинает работать, когда формы еще нет, а второй заканчивает работу, когда формы уже нет.

Модули библиотеки VBA

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

Создание COM библиотеки для VBA(Excel)

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

Так же действовал по этому методу, ошибка та же самая.

Судя по посту на geeksengine, VS должен зарегистрировать библу, если установлен параметр «Регистрация для COM взаимодействия», но при попытке зарегистрировать через regsvr32 получаю «Модуль загружен, но точка входа DllRegisterServer не найдена».
Что конкретно я делаю не так?
Есть ли возможность загрузить библиотеку более простым способом, т.к. библа планируется ставиться на несколько компьютеров?

Создание макроса для Word со ссылками на элементы библиотеки переводов в Excel
Всем привет!. начал много и плотно работать с техническими документами на двух языках. Вспомнил.

Создание файла Excel. Библиотеки
Здравствуйте. Работаю над консольным приложение, хочу реализовать создание листа Excel (или.

Какие Excel библиотеки необходимы для создания CreateObject(‘Excel.Application’) ?
проблема в том что клиент не хочет пользоваться MS Excel. существуют ли возможность работы с Open.

Создание папок VBA Excel
Добрый день!Пожалуйста помогите с VBA Exсel 1.При начале нового дня создать новую папку с именем.

Является обязательным для COM объекта

Попробуйте переписать в точности пример по ссылке и, не трогая библиотеку с папки компиляции, подрубиться к ней (.tlb)
При компиляции не забудьте галочку поставить в разделе «компиляция» «регистрация для COM-взаимодействия»

Вроде разобрался, необходимо было зарегистрировать через RegAsm, независимо от того, стоит ли галочка «Регистрация для COM взаимодействия».
До этой темы моя ошибка была в том, что я пытался скормить путь к dll’ке regasm’у из папки проекта без кавычек и не заметил этого.
И конечно же нужно использовать RegAsm из C:WindowsMicrosoft.NETFramework64 для х64 системы, иначе будет та же ошибка в excel, проверял для AnyCPU.

Столкнулся с той же ошибкой 429 на строке

Раз 10 перечитал/перепробовал все, включая ссылки, — ничего не помогает.

Причем, если после компиляции dll в VS зарегистрировать ее еще и через RegAsm:
C:WindowsMicrosoft.NETFramework64v4.0.30319RegAsm.exe «E:ДокументыVisual Studio 2017Projects VBClassTestClassTestbinReleaseClassTest.dll»,
тогда Excel выдает: «не удается найти указанный файл», (см.скрин),
хотя, при написании кода, VBE показывает даже параметры функции в подсказке,
т.е. библиотека подключена и видна.
Если не регистрировать при компиляции, а только через RegAsm с созданием tlb, — тоже самое.

Куда еще копать?

Win7 (64), Excel 2016 (64), VS 2017 (AnyCPU)

Была мысль в эту сторону , но собственных нейронов не хватило довести до ума.
(Новичок еще в VB.NET, больше в VBA сидел.)

Если тот же AnyCPU-DLL зарегистрировать 32-битным «регазмом», тогда проблем с «поиском файла» нет.
Но все-равно появляется ошибка 429.
А если в VS заменить AnyCPU на x64, (для моего Excel), то и без RegAsm сразу все взлетело!

(Правда непонятно, как же человек смог запустить AnyCPU на 64?
описание выше: https://www.cyberforum.ru/post11234282.html
если я его правильно понял. )
—

И теперь новый подвох!
Если отключить регистрацию в VS и делать ее только через RegAsm, — снова ошибка с поиском файла.

Сравнительный анализ реестра показал, что VS, при регистрации DLL, добавляет в реестр 4 строки с путем к файлу:

(или добавка 2-х строк, а реестр сам добавляет еще 2 — тут плохо понимаю. )

В тоже время, если в RegAsm добавить ключ /codebase, то как и сказано в документации,
если DLL не является «сборкой со строгим именем», — работать не будет. 🙁

Можно, конечно, еще «нарисовать топор» с «ручным» прописыванием в реестр этих строк с путем. для регистрации DLL на стороннем компе.
Но, по идее, должно же быть как-то проще без этих танцев с бубном?
В общем, я что-то плохо понимаю в этом зоопарке.

К сожалению толковой инфы в инете не нашел — что это, что дает и как проверить ее наличие?
У меня сборка офиса, которая не дает выбора отдельных компонентов для каждого приложения.

Добавлено через 1 час 47 минут
Step_UA, и еще, исходя из вопроса, тогда на стороннем компе «поддержка программирования .net» тоже должна присутствовать.

Step_UA, на это я уже писал:

а потому был и ряд встречных вопросов.

В частности, если бы я писал библу только для себя, тогда мне этот компонент выходит и не нужен,
т.к. достаточно скомпилить DLL в х64, и «на лету» зарегистрировать ее VS-ом — все работает.

А вот если на сторонних компах каждого пользователя заставлять выяснять и устанавливать доп.компоненты для Excel, — это уже никуда не годится.
Тем более у многих, как и у меня, могут также элементарно отсутствовать доп.компоненты.

Тогда проще программно залезть к юзеру в реестр с «топором», как я уже писал выше, и добавить туда недостающие строчки.
Тем более что авто-регистрацию DLL по любому еще придется прикручивать.
Только вот хотелось бы как-то более цивилизованно это сделать.

Знакомство с программированием в Excel

Добавление на ленту вкладки разработчика

Написание кода Ваших процедур и редактирование макросов производится в редакторе Visual Basic, который доступен с вкладки «Разработчик». Данная вкладка по умолчанию скрыта. Для ее отображения необходимо выполнить следующие действия:

Для Excel 2007. Зайдите в параметры Excel, используя кнопку Office, и в «основных параметрах работы с Excel» установите галочку на пункте «Показывать вкладку Разработчик на ленте».

Для Excel 2010 и 2013. В параметрах Excel нужно выбрать пункт «Настроить ленту» и в категории основные вкладки установить галочку для вкладки «Разработчик».

Знакомство с редактором Visual Basic в Excel

Чтобы попасть в редактор кода, кликните на вкладке разработчика в области «Код» по кнопке «Visual Basic».

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

№1 на изображении. Кнопка сохранить. Сохраняет текущую книгу Excel. Необходимо быть внимательнее при работе с несколькими книгами, так как текущей считается та, которая выделена в окне проектов (№3 на изображении).

№2 на изображении. Кнопки запуска, остановки и прерывания выполнения кода. Во время запуска начинает выполняться код той процедуры, на которой размещен курсор. Данные кнопки дублируются вкладкой «Run» основного меню редактора.

№3 на изображении. Окно управления проектами VBA открытых книг (Project Explorer) и кнопка, отображающая это окно.

№4 на изображении. Кнопка, отображающая окно свойств объектов (Properties Window), выбранных в окне №3, и элементов пользовательских форм (работа с объектами, их свойствами и методами будет рассмотрена в отдельной статье этой категории).

№5 на изображении. Кнопка вызова окна объектов (Object Browser). В нем описаны все доступные для работы объекты, их свойства и методы.

Кнопки №3, №4 и №5 так же доступны на вкладке «View» главного меню редактора. Там же можно вызвать другие полезные для работы окна, которые не будут рассмотрены в этом материале.

Написание простой процедуры на Visual Basic for Application

Код любой процедуры (подпрограммы) располагается в модуле, поэтому необходимо его добавить, чтобы приступить к программированию. Выберите пункт «Module» на вкладке «Insert» основного меню редактора VBA. В основной области редактора (на изображении имеет серый фон) должно появиться новое окно кода – Имя_книги – имя_модуля (Code), а в окне управления проектами (№3) к дереву выбранного проекта добавится вновь созданный модуль.

Добавьте в модуль следующий код:

Данная процедура выводит на экран диалоговое окно с сообщением «Привет пользователь!». Протестируйте ее работу, нажав кнопку Rub Sub (№2 на изображении) или вызвав как обычный макрос.

Теперь более подробно разберем приведенный код.

Любая процедура начинается со строки объявления, состоящей из оператора Sub (сокращение от Subprogram), после которого следует имя подпрограммы. За именем следует перечень аргументов, заключенный в скобки.

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

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

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

Последняя строка содержит оператор End с последующим ключевым словом Sub и сообщает о завершении процедуры.

Учимся пользоваться Object Browser

Выше была упомянута функция MsgBox, но ее возможности полностью не рассмотрены. Это хороший случай, чтобы научиться использовать браузер объектов (№5 на изображении окна редактора VBA).

Нажмите кнопку на панели или клавишу F2, чтобы отобразить Object Browser:

Выпадающий список №1 содержит перечень всех подключенных библиотек. Используйте его только в том случае, если точно знаете, к какой библиотеке относится изучаемый класс, функция и т.д. (подключить или отключить любую из доступных библиотек можно, выбрав пункт «References» на вкладке «Tools» главного меню редактора VBA).

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

Поле №2 предназначено для поиска свойств, методов, классов подключенных библиотек.

Результаты поиска отображаются в поле №3 и представляют из себя таблицу с тремя столбцами: библиотека, класс и член класса.

Поле №4 содержит перечень всех классов, выбранных в поле №1 библиотек.

В области №5 содержится список всех членов класса, выбранного в поле №4.

Поле №6 содержит краткую справку о выделенном результате поиска, классе или члене класса.

Примечание: библиотеки, классы, функции и процедуры (методы), свойства и т.п. в Object Browser имеют различные обозначения, представленные набором пиктограмм. Краткая справка (поле №6) всегда указывает какой тип элемента выделен. Обращайте на это внимание, чтобы в дальнейшем лучше ориентироваться в работе с объектами.

Примечание: Понятие объекта не относится к теме данной статьи и будет рассмотрено в следующих материалах.

Теперь найдем функцию MsgBox с помощью браузера объектов. В поле поиска (№2) впишите ее название и кликните по кнопке с изображением бинокля. В поле результатов поиска выделите строку со значением MsgBox в столбце «Member». В поле №6 появилась краткая информация, сообщающая нам, что выбранный член является функцией, имеет перечень аргументов, возвращает результат типа VbMsgBoxResult, относиться в классу Interaction библиотеки VBA.

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

Изучите самостоятельно возможности функции MsgBox.

Ссылка на процедуру VBA

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

Для лучшего понимая рассмотрим пример (в примере используется псевдокод):

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

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

VBA Excel. Управление приложением Word

Создание нового экземпляра приложения Word из кода VBA Excel или подключение к открытому для работы с документами. Функции CreateObject и GetObject.

Работа с Word из кода VBA Excel
Часть 1. Управление приложением Word
[Часть 1] [Часть 2] [Часть 3] [Часть 4] [Часть 5]

Создание объекта Word.Application

Экземпляр приложения Word необходим для того, чтобы с его помощью создавать новые документы или открывать существующие для редактирования из кода VBA Excel.

Создать новый экземпляр объекта Word.Application можно при помощи раннего или позднего связывания, используя при позднем связывании функцию CreateObject. Подключиться к открытому экземпляру приложения Word можно только при помощи позднего связывания, используя функцию GetObject.

Раннее связывание приложения Word

Создание нового экземпляра Word.Application и присвоение ссылки на него переменной myWord:

Для раннего связывания переменной с объектом Word.Application необходимо подключить в редакторе VBA Excel ссылку на библиотеку Microsoft Word Object Library, если она не подключена. Подключается ссылка в окне «References VBAproject», перейти в которое можно через главное меню редактора: Tools–>References…

Раннее связывание позволяет при написании кода использовать лист подсказок для выбора и вставки свойств и методов привязанных объектов (Auto List Members). Если проект VBA Excel создается на заказ, то, после его завершения, раннее связывание следует заменить на позднее, так как на компьютере пользователя может не оказаться нужной библиотеки, и код работать не будет.

Позднее связывание приложения Word

Создание нового экземпляра Word.Application с помощью функции CreateObject и присвоение ссылки на него переменной myWord:

Присвоение переменной myWord ссылки на открытый экземпляр приложения Word с помощью функции GetObject:

Если открытого приложения Word нет, выполнение функции GetObject приведет к ошибке. Чтобы ее избежать, следует предусмотреть создание нового экземпляра Word.Application с помощью функции CreateObject, если открытое приложение не будет найдено (смотрите пример 3).

В программы VBA Excel, работающие с Word, следует включать обработчик ошибок.

Закрытие объекта Word.Application

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

Если перед завершением процедуры VBA Excel необходимо приложение Word закрыть, используйте метод Quit:

Если переменная не содержит ссылку на приложение (myWord = Nothing), метод Quit возвратит ошибку. Чтобы этого не произошло, перед применением метода Quit необходимо проверить наличие ссылки в переменной myWord (смотрите пример 3).

Примеры открытия и закрытия Word

Пример 1
Создаем новый экземпляр объекта Word.Application с ранним связыванием и отображаем его на экране:

Запустите код примера 1 на выполнение. Вы увидите появившийся на панели задач ярлык приложения Word. Перейдите на него и закройте приложение вручную.

Пример 2
Создаем новый экземпляр объекта Word.Application с поздним связыванием, отображаем его на экране, останавливаем программу и наблюдаем закрытие приложения методом Quit:

Запустите код примера 2 на выполнение. Закройте информационное окно MsgBox и смотрите, как исчезнет с панели задач ярлык приложения Word, созданного перед остановкой кода.

Пример 3
Пытаемся создать ссылку на открытый экземпляр приложения Word с помощью функции GetObject, а если открытого экземпляра нет, создаем новый с помощью функции CreateObject:

Строка On Error Resume Next передаст управление следующему оператору, если открытого экземпляра программы Word не существует, и выполнение функции GetObject приведет к ошибке. В этом случае будет создан новый экземпляр Word.Application с помощью функции CreateObject.

В код добавлен обработчик ошибок On Error GoTo Instr , который поможет корректно завершить программу при возникновении ошибки. А также он позволит во время тестирования не наплодить большое количество ненужных экземпляров приложения Word. Проверяйте их наличие по Диспетчеру задач (Ctrl+Shift+Esc) и удаляйте лишние.

Строка Exit Sub завершит процедуру, если она прошла без ошибок. В случае возникновения ошибки, будет выполнен код после метки Instr: .

Библиотека функций в Excel

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

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

Как получить доступ к библиотеке

Чтобы получить доступ к библиотеке функций Excel, выберите вкладку Формулы. Все возможные категории и команды вы можете увидеть в группе Библиотека функций.

Разберем, какую задачу выполняет каждая из команд группы:

Вставить функцию

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

Автосумма

Команда Автосумма позволяет автоматически вычислять результаты для наиболее распространенных функций Excel, таких как СУММ, СРЗНАЧ, СЧЕТ, МАКС и МИН.

Последние

Команда Последние дает доступ к функциям Excel, с которыми Вы работали недавно.

Финансовые

Категория Финансовые содержит функции для финансовых расчетов, например, сумма периодического платежа ПЛТ или процентная ставка по кредиту СТАВКА.

Логические

Функции из категории Логические используются для проверки аргументов на соответствие определенному значению или условию. Например, если сумма заказа меньше $50, то добавляется цена доставки $4.99, а если больше, то стоимость доставки не взимается. В данном примере целесообразно использовать функцию ЕСЛИ.

Текстовые

В категории Текстовые содержатся функции, которые работают с текстом в качестве значений аргументов. С помощью них можно решать такие задачи, как преобразование текста в нижний регистр (СТРОЧН) или замена части текста на другое значение (ЗАМЕНИТЬ).

Дата и время

Категория Дата и время содержит функции для работы с датами и временем в формулах. Например, функция СЕГОДНЯ возвращает текущую дату, а функция ТДАТА дополнительно к дате еще и время.

Ссылки и массивы

В категории Ссылки и массивы содержатся функции, которые предназначены для просмотра и поиска информации. Например, Вы можете добавить гиперссылку (ГИПЕРССЫЛКА) в ячейку или вернуть значение, которое расположено на пересечении заданных строки и столбца (ИНДЕКС).

Математические

Категория Математические включает в себя функции для обработки числовых аргументов, выполняющие различные математические и тригонометрические вычисления. Например, вы можете округлить значение (ОКРУГЛ), найти значение Пи (ПИ), произведение (ПРОИЗВЕД), промежуточные итоги (ПРОМЕЖУТОЧНЫЕ.ИТОГИ) и многое другое.

Другие функции

Раздел Другие функции содержит дополнительные категории библиотеки функций, такие как Статистические, Инженерные, Аналитические, Проверка свойств и значений, а также функции, оставленные для поддержки совместимости с предыдущими версиями Excel.

Как вставить функцию из библиотеки

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

1. Выделите ячейку, которая будет содержать формулу. В нашем примере это ячейка D3.
2. Выберите вкладку Формулы на Ленте, чтобы открыть Библиотеку функций.
3. В группе команд Библиотека функций, выберите нужную категорию. В нашем случае мы выберем Дата и время.
4. Выберите нужную функцию из выпадающего меню. Мы выберем функцию ЧИСТРАБДНИ, чтобы вычислить количество рабочих дней между датами заказа и получения товара.
5. Появится диалоговое окно Аргументы функции. Здесь вы можете ввести или выбрать ячейки, которые будут составлять аргументы. Мы введем B3 в поле Нач_дата и С3 в поле Кон_дата.
6. Если аргументы введены правильно, нажмите ОК.
7. Функция будет вычислена, и Вы увидите результат. Результат показывает, что доставка заказа заняла 4 рабочих дня.

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