空腹おやじのログと備忘録

VBA（主にExcel）でいろいろな実験的な事とか、Linuxのコマンドとか設定とかについて忘れないように、あれこれと・・・

ExcelのVBAで使えるDLLを、C++（Visual Studio 2017）で作る。・・・その４．２（配列編）

VBA C++ DLL Excel プログラミング 配列 Visual Studio

初めに

前回は、予定を変更して、String型の受け渡しをする方法についてまとめました。

今回は、SAFEARRAYについてどのようなものか調べた前々回の続編です。
実際に配列データの受け渡しを行います。

SAFEARRAYに関する追加情報

配列要素のデータ型

前回の記事で、気が付いた人もいるかもしれないが、SAFEARRAY構造体のメンバーには配列要素の型情報がない。
fFeatures に、VARIANT型およびBSTR型の場合に立つフラグがあるが、それ以外の型の場合、構造体メンバーだけでは判断ができない。

これについては、後述する型判定関数があるので、基本的な型であれば問題ない。

引数

VBAでは、配列はByValでは引数に指定できないので、ByRef指定となる。
そのため、DLL側では、SAFEARRAYの受け渡しをするために、引数は、
SAFEARRAYを指すポインタ（SAFEARRAY*）ではなく、
SAFEARRAYを指すポインタへのポインタ（SAFEARRAY**）とする。

一方、呼び出すVBA側では、Declare で受け渡しをする引数をどの様に書けばいいのか？
VBAには、SAFEARRYなるデータ型はない。
結論から言えば、通常のプロシージャと同様で、下記のようになる。

Declare Sub 関数名 Lib "DLL名" (ByRef 配列変数名() As データ型)

As Byteだろうが
As Longだろうが
As Variantだろうが
構わない。

言い換えれば、下記のように、Declare でAlias を設定すれば、DLLのひとつの関数で異なるデータ型の受け渡しも可能となる。
DLLの関数宣言

__declspec(dllexport) void WINAPI FuncX(LPSAFEARRAY* ppsa);

VBA側の宣言
上記のDLLの関数宣言に対し、以下の宣言は、いずれも問題ない。

Declare Sub XByte Lib "DLL名" Alias "FuncX" (ByRef 配列変数名() As Byte)
Declare Sub XLong Lib "DLL名" Alias "FuncX" (ByRef 配列変数名() As Long)
Declare Sub XVariant Lib "DLL名" Alias "FuncX" (ByRef 配列変数名() As Variant)

主な処理と使用する関数

HRESULTを返す関数の成否判定

いくつかの関数は復帰値の型が、HRESULTとなっている。
これらの関数は、成功すると S_OK （0x00000000）を返す。
成功を判定するマクロとして、SUCCEEDED マクロがあり、
失敗を判定するマクロとして、FAILED マクロがあるので、必要に応じて使えばよい。

使用例

HRESULT hResult = SafeArrayAccessData(*ppsa, (void**)&piValue);

if (FAILED(hResult))
    return;

HRESULT が取る値（抜粋？）については、こちらに記載されている。

SAFEARRAYに格納されている要素のデータ型分類値の取得

SafeArrayGetVartype

HRESULT SafeArrayGetVartype(
  SAFEARRAY *psa,
  VARTYPE   *pvt
);

VBAから渡した場合、VERTYPEは、以下のような値を取る。

項目データ型値備考
VBAC++
VT_I2Integershort0x0002
VT_I4Longint0x0003
VT_R4Singlefloat0x0004
VT_R8Doubledouble0x0005
VT_CYCurrencyCY0x0006
VT_DATEDateDATE0x0007
VT_BSTRStringBSTR0x0008
VT_BOOLBooleanBOOL0x00B
VT_VARIANTVariantVARIANT0x00C
VT_UI1Byteunsigned char0x0011
VT_I8LongLonglong long0x001464bit版のみ

docs.microsoft.com

配列の次元数の取得

SafeArrayGetDim

UINT SafeArrayGetDim(
  SAFEARRAY *psa
);

docs.microsoft.com

配列の１要素のサイズの取得

SafeArrayGetElemsize

UINT SafeArrayGetElemsize(
  SAFEARRAY *psa
);

例えば、VBA側で、

Dim arryL(2) As Long
Dim arryV(2) As Variant
Dim arryS(2) As String

と宣言されていた場合、
arryLは、Longのサイズになるので、4 (Byte)が返る。
arryVは、Variantのサイズになるので、16 (Byte)が返る。
arrySは、Stringのポインタサイズ（？）になるので、4 (Byte)が返る。

docs.microsoft.com

指定した次元のインデックスの指定可能最小値(LBound)の取得

SafeArrayGetLBound

HRESULT SafeArrayGetLBound(
  SAFEARRAY *psa,
  UINT      nDim,
  LONG      *plLbound
);

nDim は、対象となる次元。
左端の次元が１となり、右に行くと増えていく。
指定方法が、SAFEARRAY メンバーの rgsabound[n].lLbound とは異なるので、何らかの理由で両関数を使い分ける必要がある場合には注意が必要。

Dim arryL(1 To 2, 3 To 5) As Long

の場合、

nDim Lbound
1 1
2 3

となる。

SAFEARRAYのメンバー、rgsabound[n].lLbound を参照して取得する方法もある。
指定方法は、前回のrgsaboundの説明を参照。
SafeArrayGetLBound の方が、直感的に指定しやすい。

docs.microsoft.com

指定した次元のインデックスの指定可能最大値(UBound)の取得

SafeArrayGetUBound

HRESULT SafeArrayGetUBound(
  SAFEARRAY *psa,
  UINT      nDim,
  LONG      *plUbound
);

SafeArrayGetLBound と同様。

こちらは、SAFEARRAYに直接取得できるメンバーはない。
rgsabound[n].lLbound + rgsabound[n].cElements - 1
で計算はできる。

docs.microsoft.com

配列のロックカウントをインクリメントと、配列データへのポインターの取得

SafeArrayAccessData

HRESULT SafeArrayAccessData(
  SAFEARRAY  *psa,
  void HUGEP **ppvData
);

ポインタを使って、要素にアクセスする場合に使用するものらしい。
配列データへのポインタを返すのと同時に、SafeArrayへのロックを行う。

SAFEARRAY ppvData 使用後、SafeArrayUnaccessData を呼び出さなければいけない。

SafeArrayGetElement および SafeArrayPutElement を使用するよりも高速に処理ができるらしい。

This approach is faster than using SafeArrayGetElement and SafeArrayPutElement.

https://docs.microsoft.com/ja-jp/windows/win32/api/oleauto/nf-oleauto-safearrayaccessdata?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev15.query%3FappId%3DDev15IDEF1%26l%3DJA-JP%26k%3Dk(OLEAUTO%2FSafeArrayAccessData)%3Bk(SafeArrayAccessData)%3Bk(DevLang-C%2B%2B)%3Bk(TargetOS-Windows)%26rd%3Dtrue#examples

多次元配列の場合、前回の記事に記載の通り、ポインタのインクリメントと配列のインデックスに注意が必要。

HUGEP は正直なところ、よくわかっていない。
１６bit時代（？）に使われていたみたいだが・・・
省略して、単に void** にキャストしても、データは取得できた。
とりあえず、使わない方向で行くことに・・・

docs.microsoft.com

配列のロックカウントをデクリメントと、SafeArrayAccessDataによって取得されたポインターの無効化

SafeArrayUnaccessData

HRESULT SafeArrayUnaccessData(
  SAFEARRAY *psa
);

SafeArrayAccessData によりロックした配列のアンロックを行う。

docs.microsoft.com

配列記述子と配列内のすべてのデータの破棄

SafeArrayDestroy

HRESULT SafeArrayDestroy(
  SAFEARRAY *psa
);
配列の単一の要素の取得

SafeArrayGetElement

HRESULT SafeArrayGetElement(
  SAFEARRAY *psa,
  LONG      *rgIndices,
  void      *pv
);

rgIndices
配列の各次元のインデックスのベクトル。
右端（最下位）の次元はrgIndices [0]
左端の次元は、rgIndices [psa-> cDims – 1]

２次元以上の配列の場合、要素数が配列の次元数のlong型の配列を用意して、
取得したい要素の各次元のインデックスを格納して、関数に渡す。

Dim  arry(3, 3, 3) As Long

という配列があり、arry(1, 2, 3) の要素を取得したい場合には、以下のようにすればよい。

long  lIndex[] = {1, 2, 3};
int iValue;
SafeArrayGetElement(*ppsa, lIndex, &iValue);

pv
取得した要素を格納する変数

docs.microsoft.com

データ要素を配列内の指定された場所に保存

SafeArrayPutElement

HRESULT SafeArrayPutElement(
  SAFEARRAY *psa,
  LONG      *rgIndices,
  void      *pv
);

使い方は、SafeArrayGetElementと同様。（pvは、書き込むデータ）

long lValue = getSomeValue();
SafeArrayPutElement(*ppsa, &lIndex, &lValue);

ただし、BSTRの場合は注意が必要。

BSTR bstr;
bstr = SysAllocStringByteLen(pszReturn, lenByte);
//&bstrではないので注意！！！(& は不要)
SafeArrayPutElement(*ppsa, &lIndex, bstr);

docs.microsoft.com

配列内のすべてのデータの破棄

SafeArrayDestroy

HRESULT SafeArrayDestroy(
  SAFEARRAY *psa
);

既存の配列記述子と配列内のすべてのデータを破棄します。オブジェクトが配列に格納されている場合、配列内の各オブジェクトでReleaseが呼び出されます。
docs.microsoft.com

VBAからDLLへ渡す

VBAから渡された配列の値を、メッセージボックスで表示してみました。

処理の流れ
  1. 格納されているデータ型の確認
  2. 配列の次元数の確認
  3. 各次元の要素数、インデックスの上下限の確認
  4. データ読み込み
  5. 後処理

DLLからVBAへ返す

VBAから受け取った配列に、何らかの値を格納して返してみました。

処理の流れ
  1. 格納されているデータ型の確認
  2. 配列の次元数の確認
  3. 各次元の要素数、インデックスの上下限の確認
  4. データ書き込み
  5. 後処理

コード

DLL

AccessibleFromVBA.h
AccessibleFromVBA.cpp
追加部分のみ

プロトタイプ宣言

std::wstring	convMbc2Wstr(const char* lpcszSrc);
std::wstring	convMbcBstr2Wstr(const BSTR& bstr);

DLLに配列を渡す処理

DLLで配列を更新して返す処理

文字列変換処理

AccessibleFromVBA.def

VBA

実行結果

getArrayPETest
 2             3             406 
 2             4             408 
 3             3             606 
 3             4             608 
 4             3             806 
 4             4             808 
 5             3             1006 
 5             4             1008 

GetArrayPE:1
GetArrayPE:2
GetArrayPE:3
GetArrayPE:4
GetArrayPE:5
getArrayADTest
 2             3            0x23
 2             4            0x24
 3             3            0x33
 3             4            0x34
 4             3            0x43
 4             4            0x44
 5             3            0x53
 5             4            0x54

 2             3            0x69
 2             4            0x6C
 3             3            0x99
 3             4            0x9C
 4             3            0xC9
 4             4            0xCC
 5             3            0xF9
 5             4            0xFC

GetArrayAD0
GetArrayAD1
GetArrayAD2
GetArrayAD3
GetArrayAD4

GetArrayAD_BSTR0
 4 
GetArrayAD_EMPTY2
GetArrayAD_EMPTY3
setArrayADTest

SetArrayGE
f:id:Z1000S:20191222112853j:plain
f:id:Z1000S:20191222112930j:plain
f:id:Z1000S:20191222112941j:plain
f:id:Z1000S:20191222112954j:plain

SetArrayAD
f:id:Z1000S:20191222113008j:plain
f:id:Z1000S:20191222113020j:plain
f:id:Z1000S:20191222113032j:plain
f:id:Z1000S:20191222113045j:plain

次回予告

次回は、Variant型の非配列変数に、配列を格納して、DLLと受け渡しをする予定です。

過去掲載分

z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com

ExcelのVBAで使えるDLLを、C++（Visual Studio 2017）で作る。・・・その３．２（String型による文字列の受け渡し）

VBA C++ DLL Excel プログラミング Visual Studio

初めに

前回の予告で、「次回は、実際に配列の受け渡しを行ってみます。」などと書いたのだけれど、
「やっぱりString型の配列の受け渡しも欲しいよな。」
となったので、予定を変更して前々回の続編として、
String型で文字列の受け渡しをまとめることにしました。

BSTR

VBAのString型は、BSTR型です。
まずは、BSTRについて調べてみました。

構造

BSTRは、

  1. 長さのプレフィックス
  2. データ文字列
  3. ターミネータ

で構成される複合データ型です。
（C++のヘッダを見ると、BSTRは、上記の「データ文字列」部なのですが・・・。詳細は下記）

項目データ型説明
長さプレフィックスULONG次のデータ文字列のバイト数。符号なし4バイト整数。
データ文字列の最初の文字の直前に配置されます。
この値には、ターミネーターは含まれません。
データ文字列WCHAR[n]複数の埋め込みNULL文字が含まれる場合があります。
データ文字列の終端がNULLである必要はありません。
ターミネーターWCHAR0x0000 (WCHAR)
BSTRは(WCHAR型の)ポインターです。 ポインターが指しているのは、長さプレフィックスではなく、データ文字列の最初の文字です。

メモリ上の配置イメージは、以下のようになります。
f:id:Z1000S:20191204105312j:plain

ヘッダ内では、以下のように定義されています。

typedef WCHAR OLECHAR;
typedef OLECHAR* BSTR;
typedef BSTR* LPBSTR;
メモリ管理

BSTRは、std::wstringとは違って、

  1. 使用する前にメモリの割り当て
  2. 使用後にメモリの解放

が必要です。

メモリの割り当てには、以下の関数を使用します。

メモリの開放には、以下の関数を使用します。

メモリの割り当て、開放を誰が担当するのかは、ケースによって変わってきます。

BSTRがインターフェイス内にとどまっている場合、操作が完了したらメモリを解放する必要があります。ただし、BSTRがインターフェイスを通過すると、受信オブジェクトがメモリ管理を担当します。

  • BSTR引数を必要とする関数を呼び出す場合、呼び出しの前にBSTRにメモリを割り当てて、後で解放する必要があります。
  • BSTRを返す関数を呼び出すときは、自分で文字列を解放する必要があります。
  • BSTRを返す関数を実装する場合、文字列を割り当てますが、解放しないでください。関数を受信すると、メモリが解放されます。
https://docs.microsoft.com/ja-jp/cpp/atl-mfc-shared/allocating-and-releasing-memory-for-a-bstr?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev15.query%3FappId%3DDev15IDEF1%26l%3DJA-JP%26k%3Dk(WTYPES%2FBSTR);k(BSTR);k(DevLang-C%2B%2B);k(TargetOS-Windows)%26rd%3Dtrue&view=vs-2019

以下は別のサイトに記載されていたもので、原文は英語ですが、Googleさんにお願いして翻訳しました。

インターフェースは契約です。 呼び出し元と呼び出し先に期待される動作を記述します。 「このメモリを解放するのは誰ですか？」 その契約の一部であるため、それを解放する所有者を決定し、それをインターフェイスのドキュメントに書き込みます。
ただし、通常は次のことが予想されます。

  • BSTRが「in」パラメータである場合
    • 通常、呼び出し側がそれを解放します。 呼び出し先がそれを所有したい場合、呼び出し先はコピーを作成してコピーを所有できます。
  • BSTRが「out」パラメータである場合
    • エントリでnullである必要があります。そのため、解放する必要はありません。また、明らかに、呼び出し側は結果の文字列を所有します。
  • BSTRが「in / out」パラメータである場合
    • 呼び出し先は渡された値を解放し、置き換えます。 呼び出し元は、新しい値を解放して所有します。
  • BSTRが「out ret」パラメーターである場合
    • 明らかに呼び出し側はそれを解放します。

VBはこれらのルールを予期し、呼び出し側であり、呼び出し側が文字列を解放している場合、ユーザーに代わって文字列を解放します。

https://blogs.msdn.microsoft.com/ericlippert/2003/09/12/erics-complete-guide-to-bstr-semantics/#comment-5180

ポインタを介してのデータの書き換え
前述の通り、BSTRは、(WCHARの)ポインタですが、ポインタを使用して、その内容を直接書き換えてはいけません。
後述する関数から用途に合ったものを使用して行います。

内部のデータ文字列の状態

BSTR は既知のバイト数であるため、ゼロで文字列を終了するという規則は必要ありません。 したがって、ゼロは BSTR 内の正当な値 です。 これは、 BSTR がバイナリイメージを含む任意のデータを含むことができることを 意味し ます。 このため、 BSTR は、文字列に加えてバイナリデータをマーシャリングする便利な方法としてよく使用されます。 これは 、いくつかの奇妙な状況で は、 BSTR が奇数バイトになる場合があることを 意味し ます。 まれですが、可能性に注意する必要があります。

https://blogs.msdn.microsoft.com/ericlippert/2003/09/12/erics-complete-guide-to-bstr-semantics/

VBAでString型とVariant型の変数に文字列を設定してDLLに渡した場合、DLL内で見たメモリの内容は以下のようになりました。
BSTR型で受けた場合、char[]相当の内容になっています。
VARIANT型で受けた場合、WCHAR[]相当の内容になっています。

渡した文字列は、

  • Z1000R：半角英数、偶数文字数
  • ZX-10RR：半角英数記号、奇数文字数
  • カワサキ：全角カタカナ

の３種類です。

受け取る型
BSTRVARIANT
渡す型StringZ1000R
f:id:Z1000S:20191204150121j:plain
ZX-10RR
f:id:Z1000S:20191204150138j:plain
カワサキ
f:id:Z1000S:20191204150154j:plain		 Z1000R
f:id:Z1000S:20191204145611j:plain
ZX-10RR
f:id:Z1000S:20191204145734j:plain
カワサキ
f:id:Z1000S:20191204145807j:plain
Variant-Z1000R
f:id:Z1000S:20191204150839j:plain
ZX-10RR
f:id:Z1000S:20191204150855j:plain
カワサキ
f:id:Z1000S:20191204150911j:plain

アンダーラインの部分の各色の部分は、以下の内容を示しています。
黄色：長さのプレフィックス
シアン：データ文字列
マゼンタ：ターミネーター

「カワサキ」の各文字の文字コードは、以下の通りです。
(CPUがリトルエンディアンなので、メモリのイメージ図上では、上位と下位が入れ替わって配置されています。)

種別 カ ワ サ キ
Shift-JIS 0x834A 0x838F 0x8354 0x834C
UNICODE 0x30AB 0x30EF 0x30B5 0x30AD

「ZX-10RR」は、終端のNULLを除いて、7文字になりますが、String型→BSTR型の場合、前述の通り奇数バイトの値が長さとして設定されているのがわかります。

ヘッダ

WTypes.h
docs.microsoft.com

関数

VBAのString型を使って、DLLとやり取りする場合、主に使用するのは以下の２つ。

  • SysAllocStringByteLen
  • SysFreeString

SysAllocStringByteLen
ANSI文字列を入力として受け取り、ANSI文字列を含むBSTRを返します。
ANSIからUnicodeへの変換を実行しません。

VBA側が、String型で文字列を受け取る場合、この関数を使用します。

BSTR SysAllocStringByteLen(
  LPCSTR psz,
  UINT   len
);

lenは、pszの終端のNULLを含めないByte数を指定する。
また、この値は奇数であっても構わない。

この関数は、バイナリデータを含むBSTRを作成するために提供されています。 このタイプのBSTRは、ANSIからUnicode、またはその逆に変換されない状況でのみ使用できます。

pszがNullの場合、要求された長さの文字列が割り当てられますが、初期化されません。 文字列pszにはヌル文字を埋め込むことができ、ヌルで終わる必要はありません。

docs.microsoft.com

SysAllocString
新しい文字列を割り当て、渡された文字列をコピーします。
VBA側が、Variant型で文字列を受け取る場合、この関数を使用します。

BSTR SysAllocString(
  const OLECHAR *psz
);

docs.microsoft.com

SysAllocStringLen
新しい文字列を割り当て、渡された文字列から指定された数の文字をコピーし、NULL終了文字を追加します。

BSTR SysAllocStringLen(
  const OLECHAR *strIn,
  UINT          ui
);

uiは、コピーする文字数。
docs.microsoft.com

SysFreeString
VARIANTの時にも使ったやつ。
SysAllocString、SysAllocStringByteLen、SysReAllocString、SysAllocStringLen、またはSysReAllocStringLenによって以前に割り当てられた文字列の割り当てを解除します。
docs.microsoft.com

SysStringLen

BSTRの長さを返します。
終端のNULL文字を含まないbstrの文字数。 bstrがnullの場合、戻り値はゼロです。

BSTRにNULL文字が埋め込まれている場合、戻り値はstrlen（bstr）と異なる場合があります。 この関数は、BSTRの割り当てに使用されるSysAllocStringLen関数のcchパラメーターで指定された文字数を常に返します。

https://docs.microsoft.com/ja-jp/windows/win32/api/oleauto/nf-oleauto-sysstringlen#remarks 
UINT SysStringLen(
  BSTR pbstr
);

docs.microsoft.com

SysStringByteLen

BSTRの長さ（バイト単位）を返します。
終端のNULL文字を含まないbstrのバイト数。 bstrがnullの場合、戻り値はゼロです。

BSTRにNULL文字が埋め込まれている場合、戻り値はstrlen（bstr）と異なる場合があります。 この関数は、BSTRの割り当てに使用されるSysAllocStringByteLen関数のlenパラメーターで指定されたバイト数を常に返します。

https://docs.microsoft.com/ja-jp/windows/win32/api/oleauto/nf-oleauto-sysstringbytelen#remarks 
UINT SysStringByteLen(
  BSTR bstr
);

docs.microsoft.com

SysAllocStringとSysAllocStringByteLenの違い

SysAllocStringとSysAllocStringByteLenの両関数に同じ内容の文字列（全く同じものではありません）を渡して生成されたBSTRを、VBAのString型で受け取った場合、どのような違いが出るのか試してみました。

DLL側でのBSTR生成 VBA側でStringで受けた後の状態 備考
SysAllocString(L"Z1000R"); Z 1 0 0 0 R 文字の間に'\0'が入っており、スペースが挟まれているように見える
SysAllocStringByteLen("Z1000R", 6); Z1000R 入力したデータが取得できている
SysAllocString(L"カワサキ"); ｫ0・ｵ0ｭ0 文字化けしている
SysAllocStringByteLen("カワサキ", 8); カワサキ 入力したデータが取得できている

目的に合った関数を使用しましょう。

コード

DLL

AccessibleFromVBA.h

#pragma once

extern "C"
{
#define ACCESSIBLEFROMVBA_API __declspec(dllexport) 

    ACCESSIBLEFROMVBA_API void WINAPI DoNothing();
    ACCESSIBLEFROMVBA_API int WINAPI GetNumberI(int i);
    ACCESSIBLEFROMVBA_API void WINAPI GetNumberI2(int* pi);
    ACCESSIBLEFROMVBA_API void WINAPI SetString(VARIANT vString);
    ACCESSIBLEFROMVBA_API void WINAPI SetStringS(const BSTR sString);
    ACCESSIBLEFROMVBA_API void WINAPI GetStringByParam(VARIANT* pvString);
    ACCESSIBLEFROMVBA_API void WINAPI GetStringByParamS(BSTR* pbstr);
    ACCESSIBLEFROMVBA_API VARIANT WINAPI GetStringByRetVal();
    ACCESSIBLEFROMVBA_API BSTR WINAPI GetStringByRetValS();
}

AccessibleFromVBA.cpp
追加分のみ

ACCESSIBLEFROMVBA_API void WINAPI GetStringByParamS(BSTR* pbstr)
{
    if (!pbstr)
        return;

    //まず開放
    SysFreeString(*pbstr);

    //返す文字列（std::wstringは使用しない）
    std::string sReturn("GetStringByParamS 返却データ文字列");

    //BSTR生成
    *pbstr = SysAllocStringByteLen(sReturn.c_str(), sReturn.length());

    return;
}

ACCESSIBLEFROMVBA_API BSTR WINAPI GetStringByRetValS()
{
    //返す文字列（std::wstringは使用しない）
    std::string s("GetStringByRetValS 返却データ文字列");

    //BSTR生成
    BSTR bstr = SysAllocStringByteLen(s.c_str(), s.length());

    return bstr;
}

ACCESSIBLEFROMVBA_API void WINAPI SetStringS(const BSTR sString)
{
    if (!sString)
    {
        MessageBox(NULL, L"Argment is NULL.", L"DLL", MB_OK | MB_ICONERROR);

        return;
    }

    //VBAからの文字列は、char*で格納されている
    //BSTRの途中に'\0'がある事は想定していない。
    std::string s((char*)sString);

    MessageBoxA(NULL, s.c_str(), "DLL", MB_OK | MB_ICONINFORMATION);

    return;
}

AccessibleFromVBA.def

LIBRARY AccessibleFromVba

EXPORTS
    DoNothing
    GetNumberI
    GetNumberI2
    SetString
    SetStringS
    GetStringByParam
    GetStringByParamS
    GetStringByRetVal
    GetStringByRetValS

stdafx.h

#pragma once

#include "targetver.h"

#define WIN32_LEAN_AND_MEAN    // Windows ヘッダーから使用されていない部分を除外します。
// Windows ヘッダー ファイル:
#include <windows.h>

// TODO: プログラムに必要な追加ヘッダーをここで参照してください
#include <WTypes.h>    //BSTR
#include <atlstr.h>    //VARIANT
#include <iostream>
#include <string>
VBA
Private Declare Sub SetStringS Lib "C:\Datas\MyDatas\Developer\VisualStudioComunity2017\DllForVBA\ForTest\AccessibleFromVBA.dll" (ByVal s As String)
Private Declare Sub GetStringByParamS Lib "C:\Datas\MyDatas\Developer\VisualStudioComunity2017\DllForVBA\ForTest\AccessibleFromVBA.dll" (ByRef s As String)
Private Declare Function GetStringByRetValS Lib "C:\Datas\MyDatas\Developer\VisualStudioComunity2017\DllForVBA\ForTest\AccessibleFromVBA.dll" () As String

Public Sub DllCallTest()

    Dim s   As String

    Call GetStringByParamS(s)
    Debug.Print "GetStringByParamS:" & s

    s = ""
    s = GetStringByRetValS
    Debug.Print "GetStringByRetValS:" & s

    s = "Z1000R"
    Call SetStringS(s)

End Sub

実行結果

f:id:Z1000S:20191208113751j:plain

参考にしたサイト

blogs.msdn.microsoft.com
http://www2.wbs.ne.jp/~kanegon/doc/bstr.txt

まとめ

使用する文字をShift-JISの範囲内等に限定できるのであれば、
VBA側の型をString型としても、DLLとの文字列の受け渡しは（文字化けせずに）できる。
ただし、その場合は、DLL側で使用する関数が、Unicodeで返す場合とは変わってくる。

DLL側のインターフェイスを、

  • VARIANT型
  • BSTR型

どちらにするのか、DLLを作る前にきちんと検討、確認をして決めましょう。

Unicode文字をつかうなら、VARIANT一択なんですけど・・・

次回予告

次回こそ、実際に配列の受け渡しをおこないます。

たぶん・・・

過去掲載分

z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com

【VBA】非配列のVariant型変数に格納した配列のLBoundや要素数を変えてみた

VBA プログラミング 配列 Redim

SafeArrayRedim を使うと、
非配列のVariant型変数に格納した配列でも、LBoundとかElementsとかの変更ができる。

だまって、
ReDim Preserve v(2 To 4)
とかすればいいんだけど・・・

ソース
Private Type SAFEARRAYBOUND
    cElements   As Long
    lLbound     As Long
End Type

Private Declare Function SafeArrayRedim Lib "OleAut32" (ByVal psa As Long, ByRef psaboundNew As SAFEARRAYBOUND) As Long

Private Declare Sub MoveMemory Lib "Kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)


Public Sub redimVariantArray()

    Dim v   As Variant

    v = Array(1, 2, 3)

    Dim psa As Long
    Dim isDynamicArray  As Boolean

    psa = getSafeArrayPointer(v, isDynamicArray)

    If psa = 0 Then
        Debug.Print "Target is not initialized."

        Exit Sub
    ElseIf Not isDynamicArray Then
        Debug.Print "Target is not Dynamic Array."

        Exit Sub
    End If

    Debug.Print "Before"
    Debug.Print "LBound:" & LBound(v)
    Debug.Print "UBound:" & UBound(v)
    Debug.Print ""

    Dim sab As SAFEARRAYBOUND

    sab.cElements = UBound(v) - LBound(v) + 1
    sab.lLbound = LBound(v) + 2

    Call SafeArrayRedim(Not Not psa, sab)

    Debug.Print "After"
    Debug.Print "LBound:" & LBound(v)
    Debug.Print "UBound:" & UBound(v)

End Sub

Public Sub redimVariantRefArray()

    Dim v   As Variant
    Dim lArray() As Long

    ReDim lArray(3)

    lArray(0) = 2
    lArray(1) = 3
    lArray(2) = 4
    lArray(3) = 5

    v = lArray

    Dim psa As Long
    Dim isDynamicArray  As Boolean

    psa = getSafeArrayPointer(v, isDynamicArray)

    If psa = 0 Then
        Debug.Print "Target is not initialized."

        Exit Sub
    ElseIf Not isDynamicArray Then
        Debug.Print "Target is not Dynamic Array."

        Exit Sub
    End If

    Debug.Print "Before"
    Debug.Print "LBound:" & LBound(v)
    Debug.Print "UBound:" & UBound(v)
    Debug.Print ""

    Dim sab As SAFEARRAYBOUND

    sab.cElements = UBound(v) - LBound(v) + 2
    sab.lLbound = LBound(v) + 2

    Call SafeArrayRedim(Not Not psa, sab)

    Debug.Print "After"
    Debug.Print "LBound:" & LBound(v)
    Debug.Print "UBound:" & UBound(v)

End Sub

Public Function getSafeArrayPointer(ByRef v As Variant, ByRef isDynamicArray As Boolean) As Long

    Const VT_ARRAY          As Integer = &H2000
    Const VT_BYREF          As Integer = &H4000

    Const FADF_STATIC       As Integer = &H2
    Const FADF_FIXEDSIZE    As Integer = &H10

    Const OFFSET_VT         As Long = 0&
    Const OFFSET_PARRAY     As Long = 8&
    Const OFFSET_FFEATURES  As Long = 2&

    Dim pArray  As Long
    Dim vt      As Integer

    isDynamicArray = False

    Call MoveMemory(vt, v, Len(vt))

    If (vt And VT_ARRAY) = 0 Then
        '配列ではない
        Exit Function
    End If

    'VARIANTのparrayのアドレス
    Call MoveMemory(pArray, ByVal VarPtr(v) + OFFSET_PARRAY, Len(pArray))

    If (vt And VT_BYREF) = VT_BYREF Then
        'VARIANTのpparrayから、parrayのアドレスを引っ張ってくる
        Call MoveMemory(pArray, ByVal pArray, Len(pArray))
    End If

    If pArray = 0 Then
        'Redimがまだか、Eraseした後
        Exit Function
    End If

    Dim fFeatures   As Integer

    Call MoveMemory(fFeatures, ByVal pArray + OFFSET_FFEATURES, Len(fFeatures))

    isDynamicArray = CBool((fFeatures And (FADF_STATIC Or FADF_FIXEDSIZE)) <> (FADF_STATIC Or FADF_FIXEDSIZE))

    getSafeArrayPointer = pArray

End Function
実行結果

redimVariantArray

Before
LBound:0
UBound:2

After
LBound:2
UBound:4

f:id:Z1000S:20191206233348g:plain

redimVariantRefArray

Before
LBound:0
UBound:3

After
LBound:2
UBound:6

ExcelのVBAで使えるDLLを、C++（Visual Studio 2017）で作る。・・・その４．１（配列 準備編）

VBA C++ DLL Excel プログラミング 配列 Visual Studio

はじめに

前回の文字列の受け渡しでは、VARIANTを使いました。（自分でもびっくり）

今回の配列でも、int* とか short* とかを使うのかと思いきや
SAFEARRAYなるものを使うことになります。

VBA の配列は OLE の SafeArrays です。C/C++ については、Windows OLE/COM ヘッダー ファイル内で SAFEARRAY として定義されています。

https://docs.microsoft.com/ja-jp/office/client-developer/excel/how-to-access-dlls-in-excel

ということで、配列の１回目は、SAFEARRAYとはどのようなものなのか、調べてみます。

SAFEARRAY

docs.microsoft.com

SAFEARRAY構造体

こんな感じで定義されています。

typedef struct tagSAFEARRAY {
  USHORT         cDims;
  USHORT         fFeatures;
  ULONG          cbElements;
  ULONG          cLocks;
  PVOID          pvData;
  SAFEARRAYBOUND rgsabound[1];
} SAFEARRAY;

メンバ内容
cDims配列の次元数
fFeaturesフラグ
cbElements配列の１要素のサイズ
cLocks対応するロック解除なしでアレイがロックされた回数。
原文：The number of times the array has been locked without a corresponding unlock.
よくわかりません・・・ m(_ _)m
pvData配列要素の先頭データを指すポインタ
rgsabound配列の指定次元の
１．要素数
２．指定できるインデックスの下限

cDims
配列の次元数が格納されている。
１次元配列なら、１
２次元配列なら、２
３次元配列なら、３
と言った具合です。

fFeatures
項目値備考
FADF_AUTO0x0001配列はスタック上に割り当てられる。
FADF_STATIC0x0002配列は静的に割り当てられる。
FADF_EMBEDDED0x0004配列は構造体に組み込まれる。
FADF_FIXEDSIZE0x0010配列はリサイズ、再割り当てできない。
FADF_RECORD0x0020レコードを含む配列。
FADF_HAVEIID0x0040インターフェイスを識別するIIDを持つ配列。FADF_DISPATCHまたはFADF_UNKNOWNも設定されている場合にのみ設定される。
FADF_HAVEVARTYPE0x0080VARIANT型を持つ配列。
FADF_BSTR0x0100BSTR型の配列。
FADF_UNKNOWN0x0200IUnknown*型の配列。
FADF_DISPATCH0x0400IDispatch*型の配列。
FADF_VARIANT0x0800VARIANT型の配列。
FADF_RESERVED0xF008将来のために予約済み。
VBA側から渡す配列によって異なり、以下のようになるようです。
f:id:Z1000S:20191113210606j:plain
DateTime型、Currency型はいずれも数値型と同じになりました。

cbElements
配列の要素１個のサイズ。
VBAのIntegerの配列なら２、Longの配列なら４
MSのサイトには、「データオブジェクトのサイズは含まれない」と書かれている（が理解できていない・・・）。

pvData
データを指すポインタ。

１次元配列の場合は、普通にインクリメントしても（範囲内であれば）大丈夫。
２次元以上の配列の場合、インクリメントしても、おそらくほとんどの人が意図した要素のアドレスを指していないと思う。

なぜなら、メモリ上の要素の並びが以下のようになっているから。

元データ（Byte型の３次元配列）

x(0, 0, 0) = &H0
x(0, 0, 1) = &H1
x(0, 1, 0) = &H10
x(0, 1, 1) = &H11
x(1, 0, 0) = &H20
x(1, 0, 1) = &H21
x(1, 1, 0) = &H30
x(1, 1, 1) = &H31

SAFEARRAYのpvDataの状態（メモリデータ）
f:id:Z1000S:20191114215409j:plain
次項のrgsaboundでも書いているが、インデックスの並びが左右逆になっている。

混乱する元になるので、多次元配列の場合は、ポインタを使わないようにした方がよさそう・・・

rgsabound
各次元の境界情報
下記構造体で定義されている。

typedef struct tagSAFEARRAYBOUND {
  ULONG cElements;
  LONG  lLbound;
} SAFEARRAYBOUND, *LPSAFEARRAYBOUND;

単一要素の配列となっているのは、
多次元配列の場合に、インデックスに指定する値を変えることで各次元の情報が得られるような仕組みとするためと思われる。
指定できるインデックスは、0 ～ cDims -1
ただし、インデックスの指定には注意が必要。
３次元配列を例にすると、下表のようになる。
配列の１番右の次元がのインデックスが０となる。

rgsabound
インデックス		210
 ↓↓↓
配列(１次元目,２次元目,３次元目)

VBA側で、下記配列を用意した場合、下表のような値となる。

Dim lArray(1 To 2, 2 To 4, 3) As Long

項目rgsabound
インデックス
012
cElements432
lLbound021


docs.microsoft.com

次回予告

SAFEARRAYの基礎知識を得たところで、
次回は、実際に配列の受け渡しを行ってみます。
さらに、第３回は、VARIANTの非配列変数に、配列を格納して受け渡しをしてみる予定です。

過去掲載分

z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com
z1000s.hatenablog.com

【VBA】UTF-8 CSV の読み込みを、ADODB.Recordset と ADODB.Stream で比べてみた・・・第２回　ADODB.Streamを使ったコードの処理速度改善

VBA プログラミング CSV UTF-8 ADO

前回のコードでは、完敗だったADODB.Stream ですが、汚名返上なるか？
見つけたサイトに掲載された方法による効果を検証してみます。


前回
z1000s.hatenablog.com


目次

前回の結果概要

処理時間

３０列、１００万行＋αのCSVの読み込み速度を比較。
その結果、

処理方法 処理時間
ADODB.Recordset ７秒弱
ADODB.Stream 130秒強

と圧倒的な差。

不明な点
  1. ADODB.Stream があまりにも遅く、その原因が不明。
  2. ２回以上処理を繰り返すと、１回目に比べ、２回目以降がさらに遅くなる。

ADODB.Stream を使った処理の分析と改善

参考サイトの説明

参考としたサイトは、
www.mussyu1204.com
説明によると、「後ろの行ほどその行を取得するための時間が長くなっていく」
ことが原因とのこと。
今回のコードで、どの程度の変化があるのかを確認してみる。

１万行あたりの読み込み時間

１万行処理毎に開始からの経過時間を出力し、その差から１万行あたりの処理時間を求めてグラフにしてみた。
f:id:Z1000S:20191023095428j:plain
リンク先のサイトの説明にあるように
行が進むにつれて、読み込みに要する時間が増加していくのが確認できる。
それ以外にも

  • ２回目以降は、６～７万件を超えるあたりから、１回目よりも遅いことも確認できる。
  • ２回目と３回目は同じような傾向にあり、その差は小さい。

といった事がわかる。

注）

  • １回目とは、Excel 起動後に最初にプロシージャを実行したもの
  • ２回目とは、１回目の処理終了後、Excel を終了させずに、再度プロシージャを実行したもの
  • ３回目とは、２回目の処理終了後、Excel を終了させずに、再度プロシージャを実行したもの
改善策

リンク先サイトに書かれている方法
指定した文字数を読み込み、その中から行データを切り出すという方法をとっている。
ただし、リンク先のコードは、改行コードがCRLFなので、今回のデータの場合にはLFに変更する必要がある。

実行結果
１回に読み込む文字数を、512～16,382文字の範囲で変えて、それぞれの場合の時間を計測してみた。

結果として、１回で読み込む文字数を増やしていくと、

  • 読み込み時間が短くなっていくことが確認できる。
  • １回目と２回目以降の差が小さくなっていくことが確認できる。

まとまった文字数の読み込みが、効果があることが確認できた。

全データの読み込みにかかった時間は、以下の通り。
大幅に改善されている。

１回で読み込む文字数時間（秒）
Split 有Split 無
51237.6329.51
102415.538.64
204815.328.60
409610.924.16
819210.553.77
1638410.243.45
１行（１回目）145.61134.88
１行（２回目）353.99340.79

読み込み文字数別
縦軸のスケールが、グラフによって異なるので注意。
f:id:Z1000S:20191023105933j:plain
f:id:Z1000S:20191023105952j:plain
f:id:Z1000S:20191023110006j:plain
f:id:Z1000S:20191023110110j:plain
f:id:Z1000S:20191023110121j:plain
f:id:Z1000S:20191023110133j:plain

読み込み回別
f:id:Z1000S:20191023110334j:plain
f:id:Z1000S:20191023110346j:plain
f:id:Z1000S:20191023110355j:plain

文字数固定読み込みのコード
ADODB.Stream の ReadText メソッドに渡すパラメータに、読み込む文字数を指定するとで、必要な文字数の読み込みができる。
今回のコードでは、定数 READ_CHAR_NUM に読み込む文字数を設定して、１回あたりの読み込み文字数を変えている。

ADODB.Recordset との比較

ADODB.Recordset の読み込み速度
ADODB.Stream と同様に、１万件毎の読み込み時間を計測してみた。
その結果、

  • ADODB.Recordset を使用した場合、読み込み行数に対する依存は確認できない。
  • １万行あたりの読み込み時間は、カーソルロケーションをClient とした場合が、最速であった。

f:id:Z1000S:20191023113352j:plain
f:id:Z1000S:20191023113406j:plain

ADODB.Recordset と、ADODB.Stream の比較

ADODB.Recordset と ADODB.Stream（１回目）の比較
ADODB.Recordset の方が速い事は変わらないが、ADODB.Stream でも、条件によって、それに迫る結果を出すことが出来ている。
f:id:Z1000S:20191023120639j:plain

感想、考察

ファイル後半の行ほど、読み込みが遅い事に関して

あくまでも推測なのだが、ADODB.Stream の読み込みは、
読み込みするポインタ位置を、毎回ファイル先頭から探しているような印象がある。
そのため、
「１行毎に読み込むから遅い」
のではなく、
「（行単位であろうと、文字数単位であろうと）読み込みを行った回数が多くなるほど遅くなる」
となっているというのが実情なのではないかと思っている。

２回目が１回目より遅い事に関して

原因は不明。
Microsoft 公式によるClose メソッドの説明より抜粋
同サイトの日本語翻訳がおかしいので、英語のままで。

Use the Close method to close a Connection, a Record, a Recordset, or a Stream object to free any associated system resources. Closing an object does not remove it from memory; you can change its property settings and open it again later. To completely eliminate an object from memory, close the object and then set the object variable to Nothing (in Visual Basic).

https://docs.microsoft.com/ja-jp/sql/ado/reference/ado-api/close-method-ado?view=sql-server-ver15

Googleさんに翻訳してもらうと、こんな感じ。

Closeメソッドを使用して、Connection、Record、Recordset、またはStreamオブジェクトを閉じ、関連するシステムリソースを解放します。オブジェクトを閉じても、メモリからは削除されません。プロパティ設定を変更して、後で再度開くことができます。オブジェクトをメモリから完全に削除するには、オブジェクトを閉じてから、オブジェクト変数を（Visual Basicで）Nothingに設定します。

こう書かれてはいるが、Colse 後に、Nothing を設定しても、メモリ上に残っているような感じを受けた。
１回目の処理でゴミが残っていて、そのまま２回目を行うと、１回目のような結果を出せていないように思える。

実際に、１回目終了後、そのまま３０分放置し、再度同じ処理を走らせても、結果は１回目直後に実施した場合との違いは認められなかった。
その直後に、Excel を終了させ、再度 Excel を立ち上げて、同じプロシージャを走らせると１回目のパフォーマンスが出ている。
ADODB.Stream 変数に対し、Nothing を設定しても、開放処理がきちんと行われていないような気がする。
もし本当にそうなのであれば、VBAのコードでは対処のしようがない。

ADODB.Recordset のカーソルロケーションが Client の場合、１万行あたりの読み込み速度と総読み込み時間がアンマッチな理由

カーソルロケーションを Client に設定した時が、１万行あたりの読み込み速度が１番速かったが、最終的な読み込み完了までの時間は、Server に設定した場合よりも遅くなっている。
これは、カーソルロケーションによって内部の処理の内容が変わることが変わることが原因で、間違っているわけではない。

サーバ側データのスナップショットコピーがクライアント側の Recordset オブジェクト内に一括して取り出されます。

https://blogs.msdn.microsoft.com/nakama/2008/10/16/ado/

上の記事内には書かなかったが、処理を開始してから先頭のレコードの読み込みを開始するまでに要する時間の差が非常に大きいため、このような現象が起こっている。

カーソルロケーション先頭のレコード
読み込み開始までの所要時間（秒）
Server0.47
Client23.00
これは読み込むレコード数が多いほど顕著に現れるようである。
このため、読み込むレコード数によってカーソルロケーションを選ばないと、よいレスポンスが得られない。
通常は、Client 側で良いと思うが、ある程度以上のレコード数があり、速度が出ない場合には、Server 側で試してみると改善する場合があるかもしれない。

まとめ

不明な点として上げた現象の根本的な原因は不明。
ただし、改善策として、今回のコードの有効性は確認できた。

  • ADODB.Stream を使用する場合
    • 読み込むデータ量が多く、速度が出ない場合には、面倒でも１行ずつではなく、ある程度まとまった文字数を読み込み、都度改行コードで分割して処理したほうがよい。
    • その場合、１回に読み込む文字数が少ないと速度が出ない。
    • １行ずつ読み込みを行う場合は、データ量が少ない場合に限定したほうがよい。
    • 改行コードが、CRLF以外の場合は、コード内で指定が必要
    • 自分で区切り文字に応じた分解処理が必要
  • ADODB.Recordset を使用する場合
    • レコード数が少なければ、カーソルロケーションを Client、多ければ、Server に設定。
    • Schema.ini が原則必要（文字セット、ヘッダ有無、区切り文字等。必要であれば各フィールド情報）
    • 区切り文字に応じた分解処理は不要であるが、自分の意図した分割がされない場合には、Schema.ini の内容見直しが必要になる場合があるかもしれない。
    • Recordset からのデータ取り出し時、Null 対応が必要な場合がある。（( ﾟдﾟ) そんなの何処に出てきた？）

今回は、CSVの読み込みとしたが、実際には、CSVに限らず、プレーンテキストの場合でも同様のことが起こる事が考えられる。
その場合にもおそらく有効に使えそうな気がする。

おまけ

改行コードが、CRLFの場合、多分こんな感じでできると思います。（動作未確認なので、使う場合には確認してから使って下さい）

【VBA】UTF-8 CSV の読み込みを、ADODB.Recordset と ADODB.Stream で比べてみた・・・第１回　１行ずつ読み込みしてみる

VBA プログラミング CSV UTF-8 ADO

初めに

以前、ADODB.Recordset を使って、CSV の読み込みについてまとめたのだけれど、
世間一般では、UTF-8 のCSVに限って言えば、ADODB.Stream を使うのがメジャーなようです。
というか、「ADODB.Recordset の記事あるの？」って感じです。

自分が紹介した ADODB.Recordset による方法のマイナー感が拭えなくて、ちょっと悔しかったこともあり、２つを比べてみました。

また、後からまとめていた際に、分かってきたこともあり、追加調査分も含めて２回に分けて書いていこうと思います。

z1000s.hatenablog.com

データ

まずは、使うデータ。
自分で作るのは面倒なので、公開されているものを探したら
以下のサイトが見つかったので、使わせてもらうことにした。
使ったのは、その中の東京のデータ（13_tokyo_all_20190930.csv）

  • 文字セット：UTF-8
  • 列数：30
  • レコード数：1,063,583 <=== Excelのシート１枚に入り切らない！！！
  • ヘッダ：無し
  • 改行コード：LF（0x0A）

ADODB.Streamを使う場合には改行コードが、CRLFではないので、指定が必要です。
先頭２行抜粋したのが以下

1,1000011000005,01,1,2018-04-02,2015-10-05,"国立国会図書館",,101,"東京都","千代田区","永田町１丁目１０－１",,13,101,1000014,,,,,,,2015-10-05,1,"National Diet Library","Tokyo","1-10-1,Nagatacho, Chiyoda ku",,"コクリツコッカイトショカン",0
2,1000012010003,01,1,2018-04-02,2015-10-05,"内閣法制局",,101,"東京都","千代田区","霞が関３丁目１－１中央合同庁舎第４号館",,13,101,1000013,,,,,,,2015-10-05,1,"Cabinet Legislation Bureau","Tokyo","3-1-1,Kasumigaseki, Chiyoda ku",,"ナイカクホウセイキョク",0

ダウンロード元
t.co

処理概要

ADODB.Recordset

ADODBでデータベースに接続する場合と同様の処理です。
接続文字列を設定して、SQL発行して、Recordset を取得し、EOFまでループします。

ADODB.Stream

一般的に公開されているサンプルと同様の処理です。
Stream を開いて、LoadFromFile でロードして、
ReadText(adReadLine)で１行ずつ読み込み、Splitして、
EOFまでループします。

コード

事前準備

参照設定：Microsoft ActiveX Data Objects 6.X Library
バージョンは、お使いの環境に合わせて・・・

共通部分
Option Explicit

Private Const TARGET_FOLDER As String = "C:\Datas\CSV\法人情報\"
Private Const TARGET_NAME   As String = "13_tokyo_all_20190930.csv"
ADODB.Recordset
Public Sub readByAdoRecordset()

    Dim cn  As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs  As ADODB.Recordset
    Dim lRecords    As Long
    Dim i           As Long

    Dim sgStart     As Single
    Dim sgStop      As Single

    Dim v           As Variant

    sgStart = Timer

    On Error GoTo ERR_EXIT

    Set cn = New ADODB.Connection

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
            & "Data Source=" & TARGET_FOLDER & ";" _
            & "Extended Properties=""Text;" _
            & "HDR=No;" _
            & "FMT=Delimited"""

    Set cmd = New ADODB.Command

    Set cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT * FROM [" & TARGET_NAME & "]"

    Set rs = New ADODB.Recordset

    rs.CursorLocation = adUseServer
'    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly

    rs.Open cmd

    lRecords = 0

    Do Until rs.EOF
        lRecords = lRecords + 1

        v = rs.GetRows(1)
    Loop

ERR_EXIT:
    If Err.Number <> 0 Then
        Debug.Print "[" & Err.Source & "]" & "[" & CStr(Err.Number) & "] " & Err.Description
    End If

    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then
            rs.Close
        End If

        Set rs = Nothing
    End If

    If Not cmd Is Nothing Then
        Set cmd.ActiveConnection = Nothing

        Set cmd = Nothing
    End If

    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then
            cn.Close
        End If

        Set cn = Nothing
    End If

    sgStop = Timer

    Debug.Print "Done. [ " & Format$(sgStop - sgStart, "0.00") & " sec.][ " & CStr(lRecords) & " records.]"

End Sub
Scema.ini
[13_tokyo_all_20190930.csv]
Format=CSVDelimited
CharacterSet=65001
ColNameHeader=False
ADODB.Stream（１行ずつ読み込み）
Public Sub readByAdoStream()

    Dim strm        As ADODB.Stream
    Dim sLine       As String
    Dim vItems      As Variant

    Dim lRecords    As Long
    Dim i           As Long

    Dim sgStart     As Single
    Dim sgStop      As Single

    sgStart = Timer

    On Error GoTo ERR_EXIT

    Set strm = New ADODB.Stream

    With strm
        .Type = adTypeText
        .Charset = "UTF-8"
'今回使用したファイルの改行コードは、LF
'        .LineSeparator = adCRLF
        .LineSeparator = adLF
        .Open

        .LoadFromFile TARGET_FOLDER & TARGET_NAME

        Do Until .EOS
            sLine = .ReadText(adReadLine)

            vItems = Split(sLine, ",")

            lRecords = lRecords + 1
        Loop

        .Close
    End With

ERR_EXIT:
    If Err.Number <> 0 Then
        Debug.Print "[" & Err.Source & "]" & "[" & CStr(Err.Number) & "] " & Err.Description
    End If

    If Not strm Is Nothing Then
        If strm.State = adStateOpen Then
            strm.Close
        End If

        Set strm = Nothing
    End If

    sgStop = Timer

    Debug.Print "Done. [ " & Format$(sgStop - sgStart, "0.00") & " sec.][ " & CStr(lRecords) & " records.]"

End Sub

結果

今回の方法では、結果は以下の通り、
ADODB.Recordset を使った方が圧倒的に速い。
Split 無しの場合で、約20倍
Split 有りの場合で、約6倍
の速度となりました。
あくまで、今回の方法ではです。（重要！）

種類カーソル
ロケーション		Split有無時間（秒）備考
RecordsetServer無6.76
有24.11
Client無24.10
有34.97
Stream-無134.88１回目
340.79２回目
-有145.61１回目
353.99２回目

但し、上記の結果は全てをそのまま鵜呑みには出来ない部分もあります。

  1. ADODB.Recordset の各フィールドデータと、Splitによる分割をした結果が明らかに異なっている。
    これは処理したコードの問題。（こちらにサンプル）
  2. ADODB.Stream の読み込みが、２回目以降が極端に遅くなること。

１項は、１行のデータを要素に分割する手段として、単純に Split しただけのため、

  • 「"」で囲まれた要素は、「"」が残る。
  • 「"」で囲まれた要素の中に、「,」がある場合、本来１つの要素が複数に分割されてしまった。

ことが原因です。
こちらにもう少し詳しくまとめてあります。

Split 有りのデータについては、参考程度に御覧ください。

２項は根本原因はわかりません。
（予想される要因はありますが、あくまで予想であり、コードで対応できる問題ではなさそうなので・・・）
ただ、改善策はあるので、それは次回とします。

要素分割が正しく行われていなかった・・・
元になるCSVの１行に、こんなデータがありました。

1,1000011000005,01,1,2018-04-02,2015-10-05,"国立国会図書館",,101,"東京都","千代田区","永田町１丁目１０－１",,13,101,1000014,,,,,,,2015-10-05,1,"National Diet Library","Tokyo","1-10-1,Nagatacho, Chiyoda ku",,"コクリツコッカイトショカン",0

この中に、

"1-10-1,Nagatacho, Chiyoda ku"

「"」で括られた中に、「,」があるので、以下の様になってしまいました。

ADODB.Recodeset 1-10-1,Nagatacho, Chiyoda ku
ADODB.Stream "1-10-1 Nagatacho Chiyoda ku"

ADODB.Recorset は、望んだ値が得られています。
一方、ADODB.Stream の方は、何も考えず、Split しただけなので、当然残念な結果になっています。
分割もおかしいし、要素の前後の「"」も残っています。
今回は、読み込み速度の比較がメインなので、このままとします。
上記の対応をしたところで、現時点での速度差が圧倒的なので、無視できる範囲内と想定できるので。
それに、対応すると、差が広がる一方なので・・・

まとめ

それぞれの方法の比較
項目 Recordset Stream 備考
速度 速い 遅い
２回目以降は特に遅い
要素を分割する処理コード 不要 必要
改行コード指定 不要 必要（CRLF以外の場合）
Schema.ini 必要 不要
接続文字列 必要
少し複雑		 不要

上にも書いたのですが、要素の分割の手間を考えると、
個人的には、ADODB.Recordset の使用がオススメです。
処理速度の点もありますが

  • Schema.ini を書く手間
  • Split処理を書く手間

「どちらを選択するか？」と言われれば、私は、Schema.ini 書きます。
必要なら、Schema.ini のテンプレート作るツール作ります。（たぶん・・・）

使うデータにも依存するので、どちらを選ぶかは、使う方の判断で・・・

次回予告

今回の結果だけを見ると「ADODB.Recrdset の圧勝」となってしまったのだけれど、色々調べていると
1行読み込みが遅い時の対処法
というのを見つけたので、これについて検証してみます。
また、この対応をしたコードで同じファイルを使って速度を比較してみます。
www.mussyu1204.com


2019/10/23 追記
続編はこちら
z1000s.hatenablog.com

エクセルクイズ 回答コード

ネタ元はこちら

皆さん、CONCAT 関数で回答されていたのですが、
うちのExcelは、2013 なので、
CONCAT 関数が使えない

くやしいので、VBAでやったやつがこれ。

Public Enum ConvertMode
    cmDecode
    cmEncode
End Enum

Public Function convertText(ByVal sText As String, Optional ByVal mode As ConvertMode = cmDecode) As String

    Dim sResult As String
    Dim lLength As Long
    Dim i       As Long

    lLength = Len(sText)

    sResult = Space(lLength)

    For i = 1 To lLength
        If Mid$(sText, i, 1) <> " " Then
            If mode = cmDecode Then
                Mid$(sResult, i, 1) = decode(Mid$(sText, i, 1))
            Else
                Mid$(sResult, i, 1) = encode(Mid$(sText, i, 1))
            End If
        End If
    Next i

    convertText = sResult

End Function

Private Function decode(ByVal s As String) As String

    Const CONV_TEXT As String = "VWXYZABCDEFGHIJKLMNOPQRSTU "

    Dim lBaseCode   As Long
    Dim lIndex      As Long

    lBaseCode = Asc(UCase(Left$(s, 1)))
    lIndex = (lBaseCode - Asc("A") + Len(CONV_TEXT)) Mod Len(CONV_TEXT) + 1

    decode = Mid$(CONV_TEXT, lIndex, 1)

End Function

Private Function encode(ByVal s As String) As String

    Const CONV_TEXT As String = "FGHIJKLMNOPQRSTUVWXYZABCDE "

    Dim lBaseCode   As Long
    Dim lIndex      As Long

    lBaseCode = Asc(UCase(Left$(s, 1)))
    lIndex = (lBaseCode - Asc("A") + Len(CONV_TEXT)) Mod Len(CONV_TEXT) + 1

    encode = Mid$(CONV_TEXT, lIndex, 1)

End Function

FYFWFXMNN UH YT TKKNHJ MTXMNN