前置き

Excel VBAで限界を感じると手を出してしまう自作DLLですが、
VBA⇔Dllのデータ受け渡しのコードをよく忘れて調べなおすので、
記事を作成しました。

環境

OS：Windows 10 Home
Excel：Microsoft 365
C++エディタ：Microsoft Visual Studo Community 2022 (64 ビット)

前提

・DLLでの関数のエクスポートは定義ファイル（.def）で行っており、そちらの記載は省略しています。
・コードサンプルですが、VBAで実行前に後述のSetDllDirectoryでDLLのディレクトリを追加済みとします。
（起動後に１回実行すれば大丈夫です。また、ライブラリをフルパスで指定する場合は不要です。）
・タイトルの「渡す・受け取る」はVBAを主語にしています。
（VBAがDLLに○○を渡してVBAがDLLから△△を受け取る）

※注意※

・何か間違えるとたいていの場合、Excelが落ちます。実行前に保存は忘れずに。試すのは自己責任でお願いします。
・DeclareステートメントのSubとFunctionを間違えただけでも正常に動きません。
・VBAの引数渡しはByRefが初期値ですので、ByValを省略すると正常に動きません。
・VBAは宣言でデータ型を省略するとVariantになりますのでAs Longなどを省略すると正常に動きません。
・DLLのビルド先ディレクトリを登録した場合はDLLのビルド前にVBEで「リセット」するかEndステートメントを実行しないと上書き禁止でビルドに失敗することがあります。
（ファイル ○○.dll を開いて書き込むことができません。）

コードサンプル

VBAで自作DLLを利用するための事前実行コード

VBAでライブラリをフルパスで指定する場合は不要です。

VBA

'動的にDLLを取得するためのWinAPI
Private Declare PtrSafe Function SetDefaultDllDirectories Lib "kernel32" (ByVal DirectoryFlags As Long) As Long
Private Declare PtrSafe Function AddDllDirectory Lib "kernel32" (ByVal fileName As String) As LongPtr ' PCWSTR
Private Const LOAD_LIBRARY_SEARCH_DEFAULT_DIRS = &H1000 '：0x00001000

' DLLのあるディレクトリのパスを渡します
Public Sub SetDllDirectory(dllDirectory As String)
    SetDefaultDllDirectories LOAD_LIBRARY_SEARCH_DEFAULT_DIRS
    AddDllDirectory StrConv(dllDirectory, vbUnicode)
End Sub

' もしブックと同じディレクトリにDLLを置いた場合は以下を実行します
Public Sub AddCurrentDirectory()
    SetDllDirectory ThisWorkbook.Path
End Sub

整数を渡して整数を受け取る

DllExample.cpp

#include <comutil.h>

int WINAPI Add(int a, int b)
{
    return a + b;
}

VBA

Private Declare PtrSafe Function Add Lib "DllExample" (ByVal a As Long, ByVal b As Long) As Long

Sub AddSample()
    Debug.Print Add(2, 3) ' 5
End Sub

実数を渡して実数を受け取る

DllExample.cpp

#include <comutil.h>

double WINAPI Dev(double a, double b)
{
    return a / b;
}

VBA

Private Declare PtrSafe Function Dev Lib "DllExample" (ByVal a As Double, ByVal b As Double) As Double

Sub DevSample()
    Debug.Print Dev(2, 3) ' 0.666666666666667
    Debug.Print Dev(2, 0) ' inf
End Sub

日付を渡して日付を受け取る

DllExample.cpp

#include <comutil.h>

// C++に日付型はないので同じIEEE 64 ビット (8 バイト) 浮動小数点数であるdoubleで受け取ります
double WINAPI AddDays(double a, int b)
{
    return a + b;
}

VBA

Private Declare PtrSafe Function AddDays Lib "DllExample" (ByVal a As Date, ByVal b As Long) As Date

Sub AddDaysSample()
    Debug.Print AddDate(#1/1/2020#, 3) ' 2020/1/4
    Debug.Print AddDate(#1/1/2020#, -3) ' 2019/12/29
End Sub

文字列を渡す

DllExample.cpp

#include <comutil.h>

// ByValでString型を渡す場合はchar*で受けます
void WINAPI MsgBoxA(char* s)
{
    MessageBoxA(NULL, s, "", MB_OK);
}

VBA

Private Declare PtrSafe Sub MsgBoxA Lib "DllExample" (ByVal s As String)

Sub MsgBoxSample()
    MsgBoxA "String型で文字列を渡すとDLLにはマルチバイト文字列で渡されるよ！"
End Sub

文字列を渡して文字列を受け取る

DllExample.cpp

#include <comutil.h>
#include <string>

// VBAにStringを返す場合は返却型をBSTRにします
BSTR WINAPI EchoString(char* s)
{
	std::string str(s);
	str += s;
    return SysAllocStringByteLen(str.c_str(), str.length());
}

VBA

Private Declare PtrSafe Function EchoString Lib "DllExample" (ByVal s As String) As String

Sub EchoStringSample()
    Debug.Print EchoString("ほげ") ' ほげほげ
End Sub

実引数で文字列を受け取る

DllExample.cpp

#include <comutil.h>
#include <string>

// ByRefでString型を渡す場合はBSTR*で受けます
void WINAPI SetString(BSTR* bstr)
{
	SysFreeString(*bstr);
	std::string s = "Hello World. ようこそ世界へ！";
	*bstr = SysAllocStringByteLen(s.c_str(), s.length());
}

VBA

Private Declare PtrSafe Sub SetString Lib "DllExample" (ByRef str As String)

Sub SetStringSample()
    Dim str As String
    SetString str
    Debug.Print str ' Hello World. ようこそ世界へ！
End Sub

文字列をバリアント型で渡す

DllExample.cpp

#include <comutil.h>

void WINAPI MsgBoxV(VARINAT v)
{
    // バリアント型変数の中身が文字列以外の場合は脱出
    if (v.vt != VT_BSTR)
        return;
    
    MessageBoxW(NULL, v.bstrVal, L"", MB_OK);
}

VBA

Private Declare PtrSafe Sub MsgBoxV Lib "DllExample" (ByVal v As Variant)

Sub SetStringSample()
    Dim v As Variant
    v = "バリアント型で文字列を渡すとDLLにはワイド文字列で渡されるよ！"
    MsgBoxV v
End Sub

文字列をバリアント型で受け取る

DllExample.cpp

#include <comutil.h>

VARIANT WINAPI GetVariantString()
{
    VARIANT vstr;
	VariantInit(&vstr);
	vstr.vt = VT_BSTR;
	vstr.bstrVal = SysAllocString(L"Hello World. ようこそ世界へ！");
	return vstr;
}

VBA

Private Declare PtrSafe Function GetVariantString Lib "DllExample" () As Variant

Sub GetVariantStringSample()
    Dim v As Variant
    v = GetVariantString
    Debug.Print v ' Hello World. ようこそ世界へ！
End Sub

バリアント型の実引数で文字列を受け取る

DllExample.cpp

#include <comutil.h>

void WINAPI SetVariantString(VARIANT* v)
{
    // 実引数なしの場合は脱出
    if (!v)
        return;
    
	VariantClear(v);
	v->vt = VT_BSTR;
	v->bstrVal = SysAllocString(L"Hello World. ようこそ世界へ！");
}

VBA

Private Declare PtrSafe Sub SetVariantString Lib "DllExample" (ByRef v As Variant)

Sub SetVariantStringSample()
    Dim v As Variant
    SetVariantString v
    Debug.Print v ' Hello World. ようこそ世界へ！
End Sub

数値の配列を渡す

DllExample.cpp

#include <comutil.h>

int WINAPI Sum(SAFEARRAY*& psa)
{
	// 配列数を取得
	int elementNum = 1;
	for (ULONG i = 0; i < psa->cDims; i++)
		elementNum *= psa->rgsabound[i].cElements;

	// 配列の合計を計算、インデックスは配列の次元数に関係なく0～要素数-1
	int sum= 0;
	for (ULONG i = 0; i < elementNum; i++)
		sum += static_cast<const int*>(psa->pvData)[i];

	return sum;
}

VBA

Private Declare PtrSafe Function Sum Lib "DllExample" (ByRef ary() As Long) As Long

Sub SumArraySample()
    Dim ary(1 To 3, 1 To 2) As Long
    ary(1, 1) = 1
    ary(2, 1) = 20
    ary(3, 1) = 300
    ary(1, 2) = 4000
    ary(2, 2) = 50000
    ary(3, 2) = 600000
    Debug.Print Sum(ary) ' 123456
End Sub

数値の1次元配列を受け取る

DllExample.cpp

#include <comutil.h>

SAFEARRAY* WINAPI CreateArithmeticSequence(int initial, int diff, int term)
{
	// SafeArrayCreateVectorで1次元の配列を作成する
    // 第1引数は要素の型、第2引数は配列のLBound、第3引数は要素数
    // VT_I4はVBAでのLong型相当
	SAFEARRAY* psa = SafeArrayCreateVector(VT_I4, 1, term);
     if (!psa)
		return nullptr;
	
    int n = initial;
    // インデックスはLBoundに関係なく0～要素数-1
    for (ULONG i = 0; i < term; i++)
    {
        // 配列の要素に値を設定
        static_cast<int*>(psa->pvData)[i] = n;
        n += diff;
    }
    return psa;
}

VBA

Private Declare PtrSafe Function CreateArithmeticSequence Lib "DllExample" (ByVal initial As Long, ByVal diff As Long, ByVal term As Long) As Long()

Sub SequenceSample()
    Dim ary() As Long: ary = CreateArithmeticSequence(1, 3, 5)
    Debug.Print ary(1) ' 1
    Debug.Print ary(2) ' 4
    Debug.Print ary(3) ' 7
    Debug.Print ary(4) ' 10
    Debug.Print ary(5) ' 13
End Sub

数値の多次元配列を受け取る

DllExample.cpp

#include <comutil.h>

SAFEARRAY* WINAPI Multiply(SAFEARRAY*& psa, int times)
{
	// 引数と同じ大きさのSAFEARRAYを作成する
	// SAFEARRAYBOUND配列の並びが逆なので注意
	int dims = psa->cDims;
	int elementNum = 1;
	SAFEARRAYBOUND* rgsabound = new SAFEARRAYBOUND[dims];
	for (ULONG i = 0; i < dims; i++)
	{
		elementNum *= psa->rgsabound[i].cElements;
		rgsabound[i].cElements = psa->rgsabound[dims - 1 - i].cElements;
		rgsabound[i].lLbound = psa->rgsabound[dims - 1 - i].lLbound;
	}
	SAFEARRAY* psaResult = SafeArrayCreate(VT_I4, dims, rgsabound);
	delete[] rgsabound;
	if (!psaResult)
		return nullptr;

	// 要素とtimesの積を計算
	for (ULONG i = 0; i < elementNum; i++)
		static_cast<int*>(psaResult->pvData)[i] = static_cast<const int*>(psa->pvData)[i] * times;

	return psaResult;
}

VBA

' 配列はByRefでしか渡せない
Private Declare PtrSafe Function Multiply Lib "DllExample" (ByRef ary() As Long, ByVal times As Long) As Long()

Sub MultiplyArraySample()
    Dim ary(1 To 2, 1 To 3) As Long
    ary(1, 1) = 1
    ary(2, 1) = 2
    ary(1, 2) = 3
    ary(2, 2) = 4
    ary(1, 3) = 5
    ary(2, 3) = 6
    Dim ret() As Long: ret = Multiply(ary, 2)
    Debug.Print ret(1, 1) ' 2
    Debug.Print ret(2, 1) ' 4
    Debug.Print ret(1, 2) ' 6
    Debug.Print ret(2, 2) ' 8
    Debug.Print ret(1, 3) ' 10
    Debug.Print ret(2, 3) ' 12
End Sub

文字列の配列を受け取る

DllExample.cpp

#include <comutil.h>

SAFEARRAY* WINAPI GetStringArray()
{
	int length = 3;
	BSTR bstr[3];
	bstr[0] = SysAllocString(L"Hello!");
	bstr[1] = SysAllocString(L"こんにちは！");
	bstr[2] = SysAllocString(L"ニーハオ");
	
	// VT_BSTRはVBAでのString型相当
    SAFEARRAY* psa = SafeArrayCreateVector(VT_BSTR, 0, length);
    if (!psa)
        return nullptr;
    
    for (int i = 0; i < length; i++)
    {
        HRESULT _ = SafeArrayPutElement(psa, (LONG*)&i, bstr[i]);
    }
    return psa;
}

VBA

Private Declare PtrSafe Function GetStringArray Lib "DllExample" () As String()

Sub GetStringArraySample()
    Dim ary() As String
    ary = GetStringArray
    Dim buf As Variant
    For Each buf In ary
        Debug.Print buf
    Next
End Sub

文字列の2次元配列を受け取る

DllExample.cpp

#include <comutil.h>

SAFEARRAY* WINAPI JapaneseOrder()
{
	SAFEARRAYBOUND rgsabound[2];
	rgsabound[0].lLbound = 1;
	rgsabound[0].cElements = 5; // 行数
	rgsabound[1].lLbound = 1;
	rgsabound[1].cElements = 10; // 列数

	// VT_BSTRはVBAでのString型相当
	SAFEARRAY* psa = SafeArrayCreate(VT_BSTR, 2, rgsabound);
	if (!psa)
		return nullptr;

	// 50音順の文字列を設定
	const wchar_t* hiragana = L"あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもや　ゆ　よらりるれろわ　を　ん";
		
	// 配列に値を設定
	for (ULONG i = 0; i < 50; i++)
	{
		wchar_t moji[2] = { hiragana[i], L'\0'};
		static_cast<BSTR*>(psa->pvData)[i] = SysAllocString(moji);
	}

	return psa;
}

VBA

Private Declare PtrSafe Function JapaneseOrder Lib "DllExample" () As String()

Sub GetStringArraySample2()
    Dim ary() As String: ary = JapaneseOrder
    Debug.Print ary(1, 1) ' あ
    Debug.Print ary(2, 1) ' い
    Debug.Print ary(3, 1) ' う
    Debug.Print ary(4, 1) ' え
    Debug.Print ary(5, 1) ' お
    
    Debug.Print ary(4, 7) ' め
    Debug.Print ary(5, 9) ' ろ
    Debug.Print ary(5, 10) ' ん
End Sub

バリアント配列を渡す

DllExample.cpp

#include <comutil.h>

// 配列の情報と各要素を文字列に変換する
BSTR WINAPI PrintVariantArray(SAFEARRAY*& psa)
{
	if (!psa)
		return nullptr;

	// 配列の各次元の要素のインデックス範囲および要素数を取得
	USHORT dims = psa->cDims;
	LONG* lLbound = new LONG[dims];
	LONG* lUbound = new LONG[dims];
	ULONG* cElements = new ULONG[dims];
	LONG elementNum = 1;
	for (LONG i = 0; i < dims; i++)
	{
        // インデックスの範囲と要素数を取得
        SafeArrayGetLBound(psa, i + 1, &lLbound[i]);
		SafeArrayGetUBound(psa, i + 1, &lUbound[i]);
		cElements[i] = lUbound[i] - lLbound[i] + 1;
		elementNum *= cElements[i];
	}

	// 配列の情報を文字列に変換
	std::string s = "サイズ：";
	s += "(";
	for (LONG i = 0; i < dims; i++)
	{
		if (lLbound[i] != 0)
		{
			s += std::to_string(lLbound[i]) + " To " + std::to_string(lUbound[i]);
		}
		else
			s += std::to_string(cElements[i]);

		if (i < dims - 1)
			s += ", ";
	}
	s += ")";
	s += "、要素数：" + std::to_string(elementNum) + "\n";
	if (dims == 2)
		s += std::to_string(cElements[0]) + "行" + std::to_string(cElements[1]) + "列\n";

	// 配列の各要素を文字列に変換
	LONG* indexies = new LONG[dims];
	for (LONG i = 0; i < dims; i++)
			indexies[i] = lLbound[i];
	VARIANT* pvData;
	SafeArrayAccessData(psa, (void**)&pvData);
	for (LONG i = 0; i < elementNum; i++)
	{
		// インデックスを文字列に変換
		s += "(";
		for (LONG j = 0; j < dims; j++)
		{
			s += std::to_string(indexies[j]);
			if (j < dims - 1)
				s += ", ";
		}
		s += "): ";

		VARIANT* pv = &pvData[i];
		switch (pv->vt)
		{
		case VT_I2:
			s += std::to_string(pv->iVal);
			s += ", Integer";
			break;
		case VT_I4:
			s += std::to_string(pv->lVal);
			s += ", Long";
			break;
		case VT_R8:
			s += std::to_string(pv->dblVal);
			s += ", Double";
			break;
		case VT_BSTR:
			// BSTRをchar*に変換
			char c[64];
			WideCharToMultiByte(CP_ACP, 0, pv->bstrVal, -1, c, sizeof(c) - 1, NULL, NULL);
			s += c;
			s += ", String";
			break;
		case VT_BOOL:
			s += pv->boolVal == VARIANT_TRUE ? "True" : "False";
			s += ", Boolean";
			break;
		case VT_DATE:
			// 日付を文字列に変換
			SYSTEMTIME st;
			VariantTimeToSystemTime(pv->date, &st);
			char date[16];
			GetDateFormatA(LOCALE_USER_DEFAULT, 0, &st, "yyyy年M月d日", date, sizeof(date) - 1);
			s += date;
			s += ", Date";
			break;
		case VT_EMPTY:
			s += "Empty";
			break;
		case VT_VARIANT:
			s += "Variant";
			break;
		default:
			s += "Unknown " + std::to_string(pv->vt);
			break;
		}
		s += "\n";

		// 次の要素のインデックスを計算
		for (LONG j = dims - 1; j >= 0; j--)
		{
			if (indexies[j] < lUbound[j])
			{
				indexies[j]++;
				break;
			}

			indexies[j] = lLbound[j];
		}
	}
    // メモリを解放
	SafeArrayUnaccessData(psa);
	delete[] lLbound;
	delete[] lUbound;
	delete[] cElements;
	delete[] indexies;

	return SysAllocStringByteLen(s.c_str(), (UINT)s.length());
}

VBA

Private Declare PtrSafe Function PrintVariantArray Lib "DllExample" (ByRef ary As Variant()) As String

Sub PrintVariantArray()
    Dim ary() As Variant
    ReDim ary(1 To 3)
    ary(1) = 123
    ary(2) = #10/31/2024#
    ary(3) = "あいう"
    Debug.Print PrintVariantArray(ary)
    ' サイズ：(1 To 3)、要素数：3
    ' (1): 123, Integer
    ' (2): 2024年10月31日, Date
    ' (3): あいう, String
End Sub

バリアント配列を受け取る

DllExample.cpp

#include <comutil.h>

SAFEARRAY* WINAPI GetVariantArray()
{
	// 3×2のバリアント型の2次元配列を作成
	SAFEARRAYBOUND rgsabound[2];
	rgsabound[0].lLbound = 1;
	rgsabound[0].cElements = 3; // 行数
	rgsabound[1].lLbound = 1;
	rgsabound[1].cElements = 2; // 列数
	SAFEARRAY* psa = SafeArrayCreate(VT_VARIANT, 2, rgsabound);
	if (!psa)
		return nullptr;

    // 配列データのポインタの取得と配列のロック
    VARIANT* pvData;
	SafeArrayAccessData(psa, (void**)&pvData);
    // 配列に値を設定
	// (1, 1)
	pvData[0].vt = VT_I4;
	pvData[0].lVal = 123;
	// (2, 1)
	pvData[1].vt = VT_R8;
	pvData[1].dblVal = 3.14;
	// (3, 1)
	pvData[2].vt = VT_BSTR;
	pvData[2].bstrVal = SysAllocString(L"Hello World.");
	// (1, 2)
	pvData[3].vt = VT_BOOL;
	pvData[3].boolVal = VARIANT_TRUE;
	// (2, 2)
	pvData[4].vt = VT_DATE;
	pvData[4].date = 12345.6789;
	// (3, 2)
	pvData[5].vt = VT_EMPTY;
    // 配列のロック解除
	SafeArrayUnaccessData(psa);
	return psa;
}

VBA

Private Declare PtrSafe Function GetVariantArray Lib "DllExample" () As Variant()

Sub GetVariantArraySample()
    Dim ary() As Variant: ary = GetVariantArray
    Debug.Print ary(1, 1) ' 123
    Debug.Print ary(2, 1) ' 3.14
    Debug.Print ary(3, 1) ' Hello World.
    Debug.Print ary(1, 2) ' 1933/10/18 16:17:37
    Debug.Print ary(2, 2) ' True
    Debug.Print ary(3, 2) '
End Sub

まとめ

値渡し

	VBA渡し方	VBA渡し型	DLL受け取り型
整数	ByVal	Long	int
実数	ByVal	Double	double
日付	ByVal	Date	double
文字列	ByVal	String	char*

参照渡し

	VBA渡し方	VBA渡し型	DLL受け取り型
整数	ByRef	Long	int
実数	ByRef	Double	double
日付	ByRef	Date	double
文字列	ByRef	String	BSTR*
配列	ByRef	xxxx()	SAFATYARRAY*&

返却型

	DLL返却型	VBA型
整数	int	Long
整数	short	Integer
実数	double	Double
日付	double	Date
文字列	BSTR	String
配列	SAFATYARRAY*	xxxx()

参考