Для правильной работы проверьте путь до M2DLL.DLL в Declare VBA Excel!
Так получилось, что пришлось мне срочно заняться одной задачей на Excel. Проблема была в том, что нужно было выбирать информацию из большого текстового файла по определенным критериям и вставлять её в Excel. Сразу пришла мысль использовать VBA и при помощи него написать функцию, которая будет выдавать мне строку с информацией.
А почему бы не написать DLL, подумал я, ведь раньше я уже пытался писать DLL для VBA на C++. DLL это быстро, это интересно и позволяет отвлечься от каждодневной рутины. Но, так как я давно уже всё делаю на D, то и DLL решил писать на D. В качестве путеводителя по дебрям VBA я использовал замечательную книгу Брюса Мак-Кинни «Крепкий орешек 4 visual basic» 1996 года выпуска. Книга исключительно интересная и занимательная и ни сколько не потеряла своей актуальности.
Однако вернемся в D. Обращаю внимание, что все все примеры, которые представлены ниже, проверены только на Windows 32, Excel 2003 и dmd для Windows. Первым делом пишу строку сборки DLL, где исходный файл m2dll.d, а сама DLL будет называться m2dll.dll. Так же в сборке участвует файл dll.d входящий в поставку dmd и asc1251.d из QtE5.
dmd –ofm2dll.dll m2dll dll asc1251 -L/IMPLIB -release -shared
Небольшое введение. Существует несколько типов вызовов функций отличаются тем как передаются параметры их порядок в стеке и типами возвращаемых значений. Они все стандартизированы и в компиляторах имеются специальные дериктивы указывающие, как должен компилятор оформить функцию. Это следующие типы: pascal, stdcall, winapi и т.д. Более подробно читайте в интернете.
Итак, что у нас. У нас есть D у которого упращенно тип вызова «extern (D)». Есть Excel VBA в котором упрощенно тип вызова «extern (Windows)». DLL – это набор функций (в терминах C и C++) которые могут быть загружены во время работы приложения. Таким образом, у нас фактически будет набор функций, который мы будем вызывать из VBA. В самом VBA надо описать имя и параметры для вызываемой внешней функции.
Declare Function getAdrStringVBA Lib «r:\m2dll.dll» Alias «_getAdr@4» (ByVal buf As String) As Long
Что здесь основное. Это Lib “r:\m2dll.dll” – имя DLL и где она расположена, Function getAdrStringVBA – это как данная функция будет называться в VBA, Alias “_getAdr@4” – это как эта функция называется в DLL ну и напоследок список параметров и возвращаемых значений. Для D эта же функция будет выглядеть так:
export extern (Windows) int getAdr(char* buffer) { … }
Компилятору сказано «export» — быдет видна в DLL «extern (Windows)» это тип вызова, winApi и дальше параметры. Все понятно, кроме имени внутри DLL “_getAdr@4”. Это имя экспортированной функции. Есть много литературы описывающей как задавать эти имена (например в файле DEF и т.д.) но мне лень это все описывать и намного проще в TotalCommander посмотреть список экспортированных функций по кнопке F3 на полученной DLL.
Теперь о параметрах. Дело в том, что типы параметров в VBA и D (32 разр) совпадают лишь частично!
Int D == Long VBA
Long D == нет соответствия в 32 разр VBA
Таким образом, где нам нужно в D иметь int – значит в VBA это будет long. Когда VBA в функции имеет описание о передачи строки, значит передается адрес этой строки, вернее структуры содержащей в том числе и строку.
Мне было интересно проверить расположение данных в структурах VBA, типа как их обрабатывать в D. Для этого я пользовался интерпретатором VBA (окно Immediate в VBA Excel).
Первая задача – это научится смотреть дамп памяти структуры из VBA например строки. Как получить адрес строки в VBA, если самом VBA нет понятия указатель, вернее оно присутствует неявно. Первая наша функция в DLL будет возвращать адрес строки VBA. Их описание приведено выше. В VBA возвращается число (long) которое и есть адрес. Вторая наша функция dumpForVBA(), получая long из VBA, формирует строку С формата и записывает её в буфер, который сформирован VBA внутри функции dumpPointer():
Dim buf As String
buf = String(1000, 0)
Для испытаний, на уровне модуля VBA я определил две переменных:
Public str As String
Public adrStr As Long
Кстати, обращаю внимание, что все переменные в VBA должны быть объявлены явно, если этого не делать, то по умолчанию переменные получают тип Variant с которым D не умеет работать. Я опускаю передачу целых чисел в DLL и их возврат. Тут все просто, все передается и возвращается по значению. Это и понятно, работаем через аппаратный стек. Намного интереснее строки. Для исследования, я использую процедуру t1() в которой записан код VBA. Просмотр результата в окне Immediate. Если поставить текстовый курсор внутри процедуры t1() на любом операторе и нажать F5 – то будет выполнена эта процедура. Это избавляет от лишней писанины.
Sub t1()
'1 - исследуем строку VBA
str = "ABC"
adrStr = getAdrStringVBA(str) 'Взяли адрес
'Распечатали содержимое по адресу
Debug.Print adrStr, " --> " & dumpPointer(adrStr, 0)
End Sub
Результат выполнения:
82468836 —> 65 — 66 — 67 — 0 — 0 — 0 — 0 — 0 — 108 — 0 — 0 — 0 –
Действительно видна наша строка. Если верить «Крепкому орешку», то длина строки расположена в 32 разрядном слове левее нашей строки. Проверим. Для этого вычтем 4 (сместимся на 4 ячейки) и посмотрим дамп.
82468832 —> 3 — 0 — 0 — 0 — 65 — 66 — 67 — 0 — 0 — 0 — 0 — 0 –
Отлично видно, что длина строки равна 3.
После нескольких попыток, выяснилось, что даже в рамках одной процедуры VBA перемещает строки. Вроде по этому адресу должна быть строка, а там её уже нет! Чудеса. Для избавления от этого эффекта пришлось изменить процедуры вызова.
Sub t1()
Dim s1 As String, s2 As String
str = "ABCD"
adrStr = getAdrStringVBA(str)
s1 = dumpPointer(adrStr, 0)
s2 = dumpPointer(adrStr - 4, 0)
Debug.Print adrStr, " --> " & s1
Debug.Print adrStr - 4, " --> " & s2
End Sub
Пришлось ввести две лишних переменных, чтобы предотвратить выделение памяти, которое приводит к перемещению строки в памяти VBA. Теперь результат стабильный.
82338988 —> 65 — 66 — 67 — 68 — 0 — 0 — 0 — 0 — 108 — 0 — 0 — 0 —
82338984 —> 4 — 0 — 0 — 0 — 65 — 66 — 67 — 68 — 0 — 0 — 0 — 0 —
Хорошо. Но надо проверить факт, как это утверждается в документации, того, что VBA всегда ставит 0 (ноль) в конце строки. Как бы это проверить…
Интересная вскрылась ситуация. Оказывается VBA все время меняет расположение строк в памяти. Фактически каждое новое присваивание чего-то строке меняет её адрес в памяти. Причем, старая строка просто занимается новым содержимым, при том уже в формате Unicode.
Dim ms1 As String
ms1 = String(4, 65)
adrStr = getAdrStringVBA(ms1) 'Взяли адрес
s1 = dumpPointer(adrStr, 0)
s2 = dumpPointer(adrStr - 4, 0)
Debug.Print adrStr, " --> " & s1
Debug.Print adrStr - 4, " --> " & s2
ms1 = "BB"
Dim adrStr2 As Long
adrStr2 = getAdrStringVBA(ms1) 'Взяли адрес
Debug.Print adrStr2
s1 = dumpPointer(adrStr, 0)
s2 = dumpPointer(adrStr - 4, 0)
Debug.Print adrStr, " --> " & s1
Debug.Print adrStr - 4, " --> " & s2
EndSub
Вывод:
72519660 —> 65 — 65 — 65 — 65 — 0 — 0 — 53 — 0 — 49 — 0 — 57 — 0 —
72519656 —> 4 — 0 — 0 — 0 — 65 — 65 — 65 — 65 — 0 — 0 — 53 — 0 —
72519860
72519660 —> 66 — 0 — 66 — 0 — 0 — 0 — 32 — 0 — 0 — 0 — 57 — 0 —
72519656 —> 4 — 0 — 0 — 0 — 66 — 0 — 66 — 0 — 0 — 0 — 32 — 0 —
Вначале создаётся строка из 4 букв A и это видно по адресу 72519660, потом я пытаюсь присвоить более короткую строку в надежде, что VBA экономя обращения к памяти, запишет её в тот же адрес. Однако ничего подобного не происходит. Создается совершенно новая переменная (её адрес 72519860), а в старый адрес записывается новая строка в формате Unicode.
Почитав документацию вижу следующую фразу: «VBA при обращении к внешним функциям DLL создаёт полную копию исходной строки, при этом конвертируя её из исходного формата Unicode в ASCII представление с конечным нулем для обработки функциями WinApi». Вот оно оказывается как. Теперь понятно почему оператор ms1 = «BB» вызвал создание новой копии. Это была подготовка к вызову внешней функции. Провожу ещё один эксперимент, пытаюсь понять, неужели VBA на каждое присваивание заново делает выделение памяти. Ниже кусочек кода:
ms1 = String(10, 65) ms1 = "BB" adrStr = getAdrStringVBA(ms1) s1 = dumpPointer(adrStr, 0) s2 = dumpPointer(adrStr - 4, 0) Debug.Print adrStr, " --> " & s1 Debug.Print adrStr - 4, " --> " & s2
Вывод:
72520940 —> 66 — 66 — 0 — 0 — 65 — 0 — 65 — 0 — 65 — 0 — 65 — 0 —
72520936 —> 2 — 0 — 0 — 0 — 66 — 66 — 0 — 0 — 65 — 0 — 65 — 0 —
Вот и ответ. Забиваем строку 10 буквами A (код 65) и тут же присваиваем новое значение “BB” – которое явно короче и может использовать старый буфер. И точно – в ответе видно, что был использован предыдущий буфер, который был забит 10 буквами A в Unicod (65;0), но в него положили уже сконвертированное значение, подготовленное для передачи во внешнюю функцию.
Со строками разобрались. Вывод для работы со строками в DLL: обязательно нужна промежуточная функция на VBA, которая создаст локальный буфер большого размера, в который мы из DLL и будем записывать результирующие строки для возврата в VBA. Далее нужно извлечь из этого буфера нужное количество символов, которое мы вернем как возвращаемое значение. Пример такого подхода Public Function dumpPointer(pointer As Long, sw As Long) As String. Хорошо, а как быть со строками которые нужно отдать в DLL. А тут все просто, VBA сам выделяет буфер и ещё конвертирует из Unicode, да ещё и количество записывает, что для нас очень кстати, так как позволяет передавать и 0 в строке (фактически двоичные данные). Таким образом можно и передать и вернуть двоичные данные. Добраться до внутреннего представления строк VBA в Unicode возможно, но есть ли в этом надобность.
Рассмотрим массивы и их передачу в DLL. Начнем с массива целых чисел. Что бы получить адрес массива, мы воспользуемся той же функцией в DLL, что и для получения адреса строк. Единственно, что немного обманем VBA, написав новую декларацию.
Declare Function getAdrArrayVBA Lib «r:\m2dll.dll» Alias «_getAdr@4» (ByRef buf As Long) As Long
В чем тут хитрость? В том, что мы фактически передаём ссылку на элемент массива. А как указать на весь массив, а просто передать ссылку на его первый элемент и количество таких элементов. Количество передать легко, а вот проверить возможность передачи адреса первого элемента надо.
Dim m(2) As Long m(0) = 1: m(1) = 3 adrStr = getAdrArrayVBA(m(0)) s1 = dumpPointer(adrStr, 0) s2 = dumpPointer(adrStr - 4, 0) Debug.Print adrStr, " --> " & s1 Debug.Print adrStr - 4, " --> " & s2
Вывод:
72514896 —> 1 — 0 — 0 — 0 — 3 — 0 — 0 — 0 — 0 — 0 — 0 — 0 —
72514892 —> 0 — 0 — 0 — 140 — 1 — 0 — 0 — 0 — 3 — 0 — 0 — 0 —
Мы забираем адрес первого элемента массива и в дампе фактически видим сам массив в разрезе четырех байт. Отсюда вывод о том, как работать с массивами. Создаём массив большого размера, в DLL его модифицируем, передавая новую длину, и уже в VBA копируем значимую часть для сохранения результата. Аналогичный должно быть и со структурами, но проверять мне было лень…
Теперь, когда более менее ясно как обмениваться данными из VBA в DLL, вернемся непосредственно в D. Фактически разработчики уже все за нас предусмотрели написав нам dll.d!
В ней описываются точки входа и инициализация GC и Phobos. Таким образом, делать практически ничего не нужно. НО! Есть маленькое но. Так как у нас функции extern (Windows) нам не позволено пользоваться в таких процедурах всеми возможностями динамического распределения памяти. Я не могу дать четкого ответа, чем можно пользоваться а чем нет, но есть выход. Определяем обычные функции (по умолчанию они будут вызова D) и спокойно в них делаем работу, а функции extern (Windows) используем только для обмена параметрами с VBA.
Исходный код m2dll.d с комментариями, а также все необходимые файлы прикреплены ниже. А вот базу с данными, которые передавались в Excel, к сожалению, предоставить не могу, ибо в ней конфиденциальная информация.
Файлы: vba_and_d.zip