Как подключить библиотеку в vba excel
Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели
Импорт данных с web-страниц в Excel средствами VBA
Мне действительно нравится Excel и, при каждом удобном случае, я стараюсь найти ему нестандартное применение. И в этот раз я задумал импортировать плей-листы радиостанций.
Вполне понятная логика, которую также можно было бы указать и через макрос VBA. Но вернемся к плейлистам. Поисковая выдача google выдала сразу ряд сайтов, которые выгружают плейлисты. Возьмем, для примера, самую верхнюю строчку
Если в левой части сайта можно нажать на «Плейлисты радиостанций», затем выбрать интересующую нас радиостанцию и нажать в верхней части на кнопку «Плейлист радиостанции за вчера». Почему за вчера? Потому что если импортировать песни за текущий день мы получаем только часть дневного эфира и сравнение получится некорректным
Здесь все просто и логично, никаких параметров
avtoradio — название радиостанции
20151003 — дата плейлиста в формате YYYYMMDD
Далее нам необходимо импортировать содержимое страницы в Excel. На помощь нам приходит макрорекордер и импорт данных. На вкладке Разработчик включаем запись макроса (или в строке состояния нажав на кружок), затем идем на вкладку «Данные» и запускаем импорт внешних данных «Из интернета»
К сожалению, как и большинство сайтов интернета, этот также не очень удобно импортировать — вместо аккуратной таблички со списком песен придется импортировать все содержимое страницы (желтая стрелочка в левом верхнем углу страницы)
Макрорекордер записал следующий код
Не все бактерии одинаково полезны, а макрорекордер, как всегда, записал все параметры, даже те, которые нам не нужны. Если код подсократить и объявить переменные, можно получить
Остальное — дело техники, подчистить результат, задать массив с названиями радиостанциями и использовать цикл для загрузки плейлистов всех доступных радиостанций 🙂
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
В своем коде можно задействовать другие объекты приложения, задав ссылки на библиотеку объектов этого приложения.
Добавление объекта
На вкладке "Разработчик" откройте окно проекта Visual Basic. Увидеть вкладки «разработчик» отображается ниже, если она еще не открыта.
В меню Сервис выберите пункт Ссылки.
В разделе Доступные ссылки установите флажок для библиотеки объектов, которую требуется задействовать в вашем проекте Visual Basic.
Примечание: Чтобы ускорить компиляцию, снимите флажки для библиотек объектов, которые не будут использоваться в проекте.
Чтобы задать порядок доступа к библиотекам объектов, в разделе Доступные ссылки выберите любую библиотеку и переместите ее вверх или вниз с помощью кнопок приоритета.
Чтобы добавить в раздел Доступные ссылки другую библиотеку, нажмите кнопку Обзор и укажите путь к необходимому файлу.
На верхней панели выберите Excel > настройки > панель инструментов и ленты.
В разделе Настройка ленты выберите Основные вкладки и установите флажок Разработчик.
Вы можете получить доступ к функции или команде DLL в Microsoft Excel несколькими способами:
с помощью модуля кода Microsoft Visual Basic для приложений (VBA), в котором функция или команда была сделана доступной с помощью выражения Declare;
через лист макросов XLM, используя функции CALL или REGISTER;
непосредственно с листа или из настроенного элемента в пользовательском интерфейсе.
В этой документации не рассматриваются функции XLM. Рекомендуем использовать один из двух других подходов.
Для непосредственного доступа с листа или из настроенного элемента пользовательского интерфейса функцию или команду необходимо сначала зарегистрировать в Excel. Сведения о регистрации команд и функций см. в статье Accessing XLL Code in Excel.
Вызов функций и команд DLL из VBA
Вы можете получать доступ к функциям и командам DLL в VBA с помощью выражения Declare. Это выражение имеет один синтаксис для команд и другой — для функций.
Синтаксис 1: команды
Синтаксис 2: функции
Команды должны возвращать тип void. Функции должны возвращать типы, которые VBA может распознавать ByVal. Это означает, что некоторые типы легче возвращать путем изменения аргументов: строк, массивов, пользовательских типов и объектов.
VBA не может проверить совпадение списка аргументов и возвращаемого значения в модуле Visual Basic со значениями, закодированными в DLL. Это необходимо внимательно проверить самостоятельно, поскольку ошибка может привести к сбою Excel.
Когда аргументы функции или команды не передаются по ссылке или указателю, им должно предшествовать ключевое слово ByVal в объявлении arglist. Если функция C/C++ принимает указатели в качестве аргументов или функция C++ принимает в качестве аргументов ссылки, то их следует передавать ByRef. Ключевое слово ByRef можно не указывать в списках аргументов, поскольку этот режим выбран в VBA по умолчанию.
Типы аргументов в C/C++ и VBA
Следует помнить об указанных ниже фактах при сравнении объявлений типов аргументов в C/C++ и VBA.
Тип String в VBA передается как указатель на структуру BSTR байтовых строк в режиме ByVal и как указатель на указатель в режиме ByRef.
Тип Variant в VBA, содержащий строку, передается как указатель на структуру BSTR строк Юникода из двухбайтовых знаков при передаче в режиме ByVal и как указатель на указатель в режиме ByRef.
Integer VBA — это 16-битный тип, эквивалентный signed short в C/C++.
Long VBA — это 32-битный тип, эквивалентный signed int в C/C++.
VBA и C/C++ допускают определяемые пользователем типы данных (применяются операторы Type и struct соответственно).
VBA и C/C++ поддерживают тип данных Variant, заданный для C/C++ в файлах заголовка Windows OLE/COM как VARIANT.
Массивы VBA — это объекты OLE SafeArrays, определенные для C/C++ в файлах заголовка Windows OLE/COM как SAFEARRAY.
Тип данных Currency в VBA передается как структура типа CY, определенная в файле заголовка Windows wtypes.h, в режиме ByVal и как указатель на него в режиме ByRef.
Ниже приведен пример определений эквивалентных пользовательских типов.
Excel передает только значения Variant указанных ниже типов в определяемую пользователем функцию VBA.
Тип данных VBA | Битовые флаги типа Variant C/C++ | Описание |
---|---|---|
Double | VT_R8 | |
Boolean | VT_BOOL | |
Date | VT_DATE | |
String | VT_BSTR | Строка байтов OLE Bstr |
Диапазон | VT_DISPATCH | Ссылки на ячейку и диапазон |
Переменная, содержащая массив | VT_ARRAY VT_VARIANT | Литеральные массивы |
Ccy | VT_CY | 64-битное целое число, масштабированное для достижения точности 4 знака после запятой. |
Переменная, содержащая ошибку | VT_ERROR | |
VT_EMPTY | Пустые ячейки или пропущенные аргументы |
Вы можете проверить тип переданного аргумента Variant в VBA с помощью VarType, если функция не возвращает тип значений диапазона при вызове со ссылками. Чтобы определить, является ли Variant объектом ссылки Range, можно использовать функцию IsObject.
В VBA можно создавать Variant, содержащие массивы переменных, назначая свойство Value Range для Variant. Ячейки в исходном диапазоне, отформатированные с использованием стандартного денежного формата для действующих региональных параметров, преобразуются в элементы массива типа Currency. Ячейки, отформатированные как даты, преобразуются в элементы массива типа Date. Ячейки, содержащие строки, преобразуются в Variant BSTR с расширенными символами. Ячейки, содержащие ошибки, преобразуются в Variant типа VT_ERROR. Ячейки, содержащие значения True или False типа Boolean, преобразуются в Variant типа VT_BOOL.
Variant хранит значение True как –1, а значение False — как 0. Числа, не отформатированные как даты или суммы валют, преобразуются в Variant типа VT_R8.
Аргументы строк и переменных
Excel внутренне работает со строками Юникода из расширенных символов. Когда пользовательская функция VBA объявляется как принимающая аргумент String, Excel преобразует предоставленную строку в байтовую строку в соответствии с региональными стандартами. Если вам нужно, чтобы функции передавалась строка Юникода, пользовательская функция VBA должна принимать Variant вместо аргумента String. Тогда функция DLL сможет принять эту строку BSTR из расширенных символов типа Variant из VBA.
Чтобы из DLL возвращались строки Юникода в VBA, следует изменить имеющийся аргумент строки Variant. Для этого следует объявить функцию DLL как принимающую указатель на Variant в коде C/C++, а также объявить аргумент в коде VBA как ByRef varg As Variant . Память предыдущей строки следует освободить, а значение новой строки, созданное с использованием строки OLE Bstr, работает только в DLL.
Чтобы байтовая строка возвращалась в VBA из DLL, следует изменить имеющийся аргумент BSTR байтовой строки. Для этого следует объявить функцию DLL как принимающую указатель на BSTR в коде C/C++, а также объявить аргумент в коде VBA как ByRef varg As String.
Строки, переданные такими способами из VBA, следует обрабатывать только с помощью строковых функций BSTR OLE. Это позволит избежать проблем, связанных с памятью. Например, необходимо вызывать функцию SysFreeString, чтобы освободить память, прежде чем перезаписывать переданную строку, и функцию SysAllocStringByteLen или SysAllocStringLen, чтобы выделить место для новой строки.
Вы можете создавать ошибки листов Excel как Variants в VBA с помощью функции CVerr с аргументами, показанными в следующей таблице. Ошибки листов также можно возвращать в VBA из DLL с помощью объектов Variants типа VT_ERROR со следующими значениями в поле ulVal.
Обратите внимание на то, что значение Variant ulVal эквивалентно значению аргумента CVerr с шестнадцатеричным значением x800A0000.
Вызов функций DLL непосредственно с листа
Вы не сможете получить доступ к функциям DLL Win32 с листа, если не используете, к примеру, интерфейсы VBA или XLM либо не сообщите Excel заранее о функции, ее аргументах и типе возвращаемого значения. Этот процесс называется регистрацией.
Ниже приведены способы, которыми можно получить доступ к функциям DLL на листе.
Объявите функцию в VBA, как показано выше, и получите доступ к ней через пользовательскую функцию VBA.
Сначала обеспечьте вызов функции DLL с помощью CALL на листе макросов XLM, а затем — доступ к ней с помощью определяемой пользователем функции XLM.
Используйте команду XLM или VBA, чтобы вызвать функцию XLM REGISTER, которая предоставляет сведения, необходимые Excel для опознания функции при ее вводе в ячейке листа.
Преобразуйте DLL в XLL и зарегистрируйте функцию с помощью функции xlfRegister C API после активации XLL.
Четвертый подход изолированный: код, регистрирующий функции, и код функций хранятся в одном объекте кода. Изменение надстройки не включает изменение листа XLM или модуля кода VBA. Чтобы сделать это с широкими возможностями управления, оставаясь в рамках возможностей API C, необходимо преобразовать DLL в XLL и загрузить получившуюся надстройку с помощью диспетчера настроек. Это позволяет Excel вызывать функцию, предоставленную библиотекой DLL, при загрузке или активации надстройки, из которой затем можно зарегистрировать все функции, которые содержит XLL, и выполнять другие задачи инициализации DLL.
Вызов команд DLL непосредственно из Excel
Команды DLL Win32 недоступны напрямую из диалоговых окон и меню Excel без интерфейса, например VBA, или без предварительной регистрации команд.
Получать доступ к командам DLL можно следующими способами:
Объявите команду в VBA так, как описано выше, и получите к ней доступ с помощью макроса VBA.
Сначала обеспечьте вызов команды DLL с помощью CALL на листе макросов XLM, а затем — доступ к ней с помощью макроса XLM.
Используйте команду XLM или VBA, чтобы вызвать функцию XLM REGISTER, которая предоставляет сведения, необходимые Excel для опознания команды при ее вводе в диалоговом окне, которое запрашивает имя команды макроса.
Преобразуйте DLL в XLL и зарегистрируйте команду с помощью функции xlfRegister C API.
Excel игнорирует возвращаемое значение, если оно не вызывается с листа макросов XLM. В этом случае возвращаемое значение преобразуется в значение TRUE или FALSE. Таким образом, следует возвращать значение 1, если команда выполнена успешно, и 0, если она завершилась с ошибкой или была отменена пользователем.
Память DLL и многочисленные экземпляры DLL
Когда приложение загружает библиотеку DLL, ее исполняемый код загружается в глобальную кучу, чтобы его можно было запустить, и в глобальной куче выделяется место для его структур данных. Windows использует сопоставление памяти, чтобы эти области памяти отображались как часть процесса приложения и приложение могло получать к ним доступ.
Если второе приложение затем загрузит библиотеку DLL, Windows не будет создавать еще одну копию ее исполняемого кода, так как эта память доступна только для чтения. Windows сопоставляет память исполняемого кода DLL с процессами в обоих приложениях. Тем не менее выделяется дополнительное место для личной копии структур данных DLL, а эта копия сопоставляется только со вторым процессом. Это гарантирует, что ни одно из приложений не будет конфликтовать с данными DLL другого приложения.
Это означает, что разработчикам DLL не следует беспокоиться о том, что несколько приложений (или несколько экземпляров одного приложения) будут получать доступ к статическим и глобальным переменным и структурам данных. Каждый экземпляр всех приложений получает собственную копию данных DLL.
Разработчикам DLL следует позаботиться о том, чтобы один и тот же экземпляр приложения не вызывал DLL много раз из разных потоков, так как это может привести к состязанию за данные этого экземпляра. Дополнительные сведения см. в статье Управление памятью в Excel.
Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели
Как подключить библиотеку в VBA?
В VBA есть много интересных библиотек позволяющих работать с объектом FSO, словарями, регулярными выражениями, объектами MS Office, а также много другое.
Можно и обойтись без их подключения и использовать конструкцию CreateObject(«»), но есть два маленьких нюанса
-
Вы теряете чуть-чуть в производительности. Я, если честно, не до конца понимаю как так, но факт есть факт. С подключенной библиотекой, на коротких примерах ускорение в 2 раза — с 0,1 сек до 0,05 сек
Вы теряете подсказки в среде VBE. Для кода, который вы отдали в использование — это нормально, но для себя любимых включение может быть лишним действием (опять же вы можете в какой-то момент забыть какую библиотеку включали)
Итак, проблематика ясна. Как бороться? В три этапа.
Первый этап — макросам должен быть предоставлен доступ к объектной модели VBA. Само собой вы при этом берете на себя риски — ведь при этом макросы могут удалить все и вся.
Файл ->
Параметры ->
Центр Управления Безопасностью ->
Параметры центра управления безопасностью ->
Параметры макросов ->
Включить галочку «Предоставлять доступ к объектной модели проектов VBA»
Второй этап — посмотреть GUID код соответствующей библиотеки, которую будете проверять/включать программным способом. Для этого в VBE перейдите в меню и включите галочку напротив библиотеки.
Ну и третий этап — сам макрос. Верхняя часть выводит в окно Immediate Window все библиотеки, проверяет включена ли библиотека. Ну а строчка .AddFromGuid производит подключение
Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели
Excel VBA. SQL-запросы в подключениях
UPDATE 21.10.15 Добавил «обратный» макрос — VBA в SQL и макрос для доступа к строке запроса SQL
Некоторое время назад я прошел несколько курсов по SQL. И мне было очень интересно — какую часть из мощного инструмента под названием T-SQL можно применять без использования SQL-Server (не дают мне сервачек под мои нужды, хнык-хнык).
Итак… Начнем с простого — подключение через Query Table в VBA. Можно записать через макрорекордер — для этого нужно создать подключение через Microsoft Query.
Выбираем Excel Files, указываем путь к файлу (пытаясь при этом не ругать разработчиков за интерфейс из 90х годов), фильтруем как-угодно поля. Нам сейчас это не важно — главное получить код, который дальше можно будет корректировать.
Должно получится что-то вроде этого:
Теперь начинаем копаться глубже — какого уровня запросы можно строить из VBA. Самые-самые базовые, основные конструкции — все работает, все ок.
Заполнение нового столбца одинаковым значением
Переименование столбцов
Фильтрация записей
Сортировка
Агрегация записей
Работа с датой
Дату можно впрямую через конструкцию
Но я люблю отталкиваться от текущей даты. За пару текущая дата-время отвечает функция SYSDATETIME() и она может вернуть в том числе текущий день. Для этого нужна еще одна функция — CONVERT(type,value)
С функцией DATEFROMPARTS строка запроса в Excel почему-то не дружит, поэтому придется использовать костыли функцию DATEADD:
Эта строчка в любой день октября 2015 вернет значение — 30.11.15 23:59
А теперь — немного best practice!
Объединение + Агрегация + Join + Подзапросы. И самое интересное — подключение к нескольким источникам:
Одна проблема — если осуществлять такого вида запрос для соединения нескольких Excel-файлов, он будет выполняться достаточно медленно. У меня вышло порядка 2 минут. Но не стоит думать что это бесполезно — если подобные запросы выполнять при подключении к SQL-серверу, то время обработки будет 1-2 секунды (само собой, все зависит от сложности запроса, базы, и прочие прочие факторы).
Бонусы
Формировать более-менее сложный запрос SQL вручную в VBA мягко говоря неудобно. Поэтому я написал мини-макрос, который берет информацию из буфера обмена, и возвращает туда строчки для вставки в VBE.
Сами запросы просто и удобно создавать, например, используя Notepad++. Создали многострочный запрос SQL, копируете его в буфер обмена, запускаете макрос и вуаля — в буфере обмена строчки кода, готовые для вставки в ваши макросы. При желании вы можете настроить название переменной и количество табуляций.
Читайте также: