EXCELの拡張（VBAに限界を感じたら・・・）

ＶＢＡの拡張

ＶＢＡの拡張について調べてみた。

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

はっきり言って、なんで今さら・・・
って感じですが。

あまりにも古すぎて、逆に情報を得るのに戸惑っていました。

引数について

とりあえず、専門用語を無視して、分かりやすくまとめます。

アクセス方法

Declare宣言にて、ＶＢＡに組み込む

[Public | Private] Declare Sub name Lib "libname" [Alias "aliasname"] [([arglist])]

スコープ

標準モジュールでは、Public、Privateは使えますが、クラスモジュールでPublic宣言すると、下記のように怒られます。

これは変数宣言のスコープというやつが、クラスでは下記みたいですね

クラスモジュールの宣言部

宣言したクラスモジュール内でのみ有効、同一モジュールの他プロシージャからは参照できるが、別モジュールからの参照はできない。インスタンス終了時に値を破棄

ライブラリ

Libの部分は32bit/64bitで変わります。

WIN32APIというのは、32ビットのライブラリですので、
EXCELが64ビットのときには、宣言の書き方を変える必要があります。

こんな感じで、IF文で振り分けるのが、一般的みたいですが、
逆にEXCELの64bitを使われている方は、皆無だとは思いますが・・・

＃If Win64 Then  
Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long  
＃Else  
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long  
＃End If  

Alias　

エイリアスというやつは、別名で定義するという意味です。

VBA から DLL 関数およびコマンドを呼び出す

呼び出し方はsubと,functionの２つ。
Cで言えば、subの場合はvoid型、Functionの場合はそれ以外の型を指す。

　[Public | Private] Declare Sub name Lib "libname" [Alias "aliasname"] [([arglist])]
　[Public | Private] Declare Function name Lib "libname" [Alias "aliasname"] [([arglist])] [As type]

VBA では、Visual Basic モジュールで示された引数リストと戻り値が DLL でコード化されたものと同じであることを確認できません。間違いがあると Excel がクラッシュする可能性があるため、自身で慎重に確認する必要があります。

VBAは馬鹿なので、ちゃんとライブラリの引数とかチェックせえよ・・・とのこと

関数またはコマンドの引数が参照またはポインターによって渡されない場合、arglist 宣言の ByVal キーワードを前に付ける必要があります。C/C++ 関数がポインター引数を受け取る場合、または C++ 関数が参照引数を受け取る場合は、ByRef を渡す必要があります。ByRef キーワードは、VBA の既定値であるため、引数リストから省略できます。

VBAからライブラリを呼び出す際の引数の話。
値渡し（ByVal）　：　メモリの領域を確保して値をコピーするのか？
参照渡し(ByRef）　：　値の入っているメモリの領域（ポインタ）を渡すのか？

C/C++ および VBA の引数の型

C/C++ と VBA の引数の型の宣言を比較するときには、次の点に注意が必要です。

VBA の String は、ByVal 渡しの場合はバイト文字列 BSTR 構造体へのポインターとして渡されます。ByRef 渡しの場合はポインターへのポインターとして渡されます。
文字列を格納している VBA の Variant は、ByVal 渡しの場合は Unicode ワイド文字文字列 BSTR 構造体へのポインターとして渡されます。ByRef 渡しの場合はポインターへのポインターとして渡されます。
VBA の Integer は、C/C++ の signed short と同等の 16 ビット型です。
VBA の Long は、C/C++ の signed int と同等の 32 ビット型です。
VBA と C/C++ は、どちらもユーザー定義データ型の定義が可能です。それぞれ、Type ステートメントと struct ステートメントを使用します。
VBA と C/C++ は、どちらも Variant データ型をサポートしています。C/C++ については、Windows OLE/COM ヘッダー ファイル内で VARIANT として定義されています。
VBA の配列は OLE の SafeArrays です。C/C++ については、Windows OLE/COM ヘッダー ファイル内で SAFEARRAY として定義されています。
VBA の Currency データ型は、ByVal 渡しの場合は Windows ヘッダー ファイル wtypes.h 内で定義されている CY 型の構造体として渡されます。ByRef 渡しの場合はそのポインターとして渡されます。
VBA では、ユーザー定義データ型のデータ要素は 4 バイト境界にパッキングされます。Visual Studio では、このデータ要素が既定で 8 バイト境界にパッキングされます。 そのため、C/C++ 構造体の定義は #pragma pack(4) … #pragma pack() ブロックで囲んで要素の配置がずれないようにする必要があります。

長い！
要約はなんやねん！
とりあえず、グダグダになりそうなので、ここは割愛！

同等のユーザータイプ定義の例

Type VB_User_Type
    i As Integer
    d As Double
    s As String
End Type

struct C_user_type
{
    short iVal;
    double dVal;
    BSTR bstr; // VBA String type is a byte string
}

ちょっと難しいですね。

VarType を使用して、渡された VBA バリアント型の種類を確認できます。ただし、参照を使用して呼び出されたときに範囲の値の型を返す関数を除きます。バリアント型が範囲の参照オブジェクトであるかどうかを判断するには、IsObject 関数を使用します。
範囲から VBA のバリアントの配列を含むバリアント型を作成するには、その Value プロパティをバリアント型に割り当てます。ソース範囲内のセルが、その時点での地域設定の標準通貨書式を使用してフォーマットされている場合、Currency 型の配列要素に変換されます。日付として書式化されているセルは、Date 型の配列要素に変換されます。文字列を含むセルは、ワイド文字の BSTR バリアントに変換されます。エラーを含むセルは、VT_ERROR 型のバリアント に変換されます。True または False のブール値を含むセルは、VT_BOOL 型のバリアント に変換されます。

注意

バリアント型と文字列の引数
Excel は、ワイド文字 Unicode 文字列を使用して内部で動作しています。VBA ユーザー定義関数が String 引数を取るように宣言されている場合、Excel は指定した文字列をロケール固有の方法でバイト文字列に変換します。関数に Unicode 文字列を渡す場合、VBA ユーザー定義関数は String 引数の代わりにバリアント型を受け入れる必要があります。その後、DLL 関数は、VBA から バリアント BSTR ワイド文字列を受け入れることができます。
DLL から VBA に Unicode 文字列を返すには、バリアント文字列引数を修正する必要があります。これが機能するには、C/C++ コードでバリアントへのポインターを使用するよう DLL 関数を宣言し、VBA コードで引数を ByRef varg As Variant として宣言する必要があります。古い文字列のメモリを解放し、OLE Bstr 文字列を使用して作成された新しい文字列値は DLL でのみ機能すべきです。
DLL から VBA にバイト文字列を返すには、バイト文字列 BSTR 引数をインプレースで変更する必要があります。これが機能するには、C/C++ コードで BSTR へのポインターへのポインターを使用するよう DLL 関数を宣言し、VBA コードで引数を「ByRef varg As String」として宣言する必要があります。
このような方法で VBA から渡された文字列は、メモリ関連の問題を避けるために、OLE BSTR 文字列関数を使用してのみ処理する必要があります。たとえば、** SysFreeString** を呼び出して、渡された文字列を上書きする前にメモリを解放し、SysAllocStringByteLen または SysAllocStringLen を呼び出して、新しい文字列の領域を割り当てる必要があります。
次の表に示すように、CVerr 関数を引数と共に使用すると、VBA で Excel ワークシートのエラーをバリアントとして作成できます。ワークシートのエラーは、** VT_ERROR ** 型のバリアントを使用し、ulVal フィールドに次の値を指定して、DLL から VBA に返すこともできます。
所定のバリアント型の ulVal 値は、CVerr 引数の値に 16 進数の x800A0000 を加えたものと同じになる点に注目してください。

長ったらしいので、放置！

ワークシートから DLL 関数を直接呼び出す

たとえば、インターフェイスとして VBA または XLM を使用することなく、ワークシートから Win32 DLL の関数にアクセスすることはできません。また、その関数の引数と戻り値の型について事前に Excel に知らせておく必要もあります。これを行うプロセスは登録と呼ばれます。
次に示すような方法で、ワークシートから DLL の関数にアクセスできます。
前述したように VBA で関数を宣言して、その関数に VBA ユーザー定義関数でアクセスします。
XLM マクロ シートで CALL を使用することで DLL 関数を呼び出して、XLM ユーザー定義関数からアクセスします。
XLM コマンドまたは VBA コマンドを使用して XLM の REGISTER 関数を呼び出します。これにより、関数がワークシート セルに入力されたときに、その関数を認識するために Excel が必要とする情報を提供します。
DLL を XLL に変換して、XLL を有効化するときに C API の xlfRegister 関数を使用して関数を登録します。
4 番目のアプローチは自己完結型であり、関数を登録するコードと関数のコードは、どちらも同じコード プロジェクトに含まれています。アドインに変更を加えても、XLM シートや VBA コード モジュールに変更を加える必要はありません。C API の機能を維持したまま適切に管理された方法でこれを行うには、DLL を XLL に変換して、その結果のアドインをアドイン マネージャーで読み込む必要があります。これにより、アドインが読み込まれると (または有効化されると)、DLL で公開してる関数を Excel から呼び出せるようになり、XLL に含まれるすべての関数を登録して、その他の DLL の初期化を実行できます。
Excel から DLL コマンドを直接呼び出す
Win32 DLL コマンドは、VBA などのインターフェイスが存在しない場合や、事前にコマンドが登録されていない場合は、Excel のダイアログ ボックスやメニューから直接アクセスすることはできません。
次に示すような方法で DLL のコマンドにアクセスできます。
前述したように VBA でコマンドを宣言して、VBA マクロからアクセスします。
XLM マクロ シートで CALL を使用することで DLL コマンドを呼び出して、XLM マクロからアクセスします。
XLM コマンドまたは VBA コマンドを使用して XLM の REGISTER 関数を呼び出します。これにより、マクロ コマンドの名前を期待するダイアログ ボックスにコマンドが入力されたときに、そのコマンドを認識するために Excel が必要とする情報が提供されます。
DLL を XLL に変換して、C API の xlfRegister 関数を使用してコマンドを登録します。
DLL 関数に関して前述したように、4 番目のアプローチが最も自己完結的であり、登録のコードをコマンドのコードに近づけられます。これを行うには、DLL を XLL に変換し、その結果のアドインをアドイン マネージャーで読み込む必要があります。この方法でコマンドを登録すると、コマンドをユーザー インターフェイスの要素に加えることもできます (カスタム メニューなど)。また、特定のキーボード操作などのイベントでコマンドを呼び出すイベント トラップを設定することもできます。
Exce に登録されたすべての XLL コマンドについて、Excel では、次の形式になっていると見なされます。

放置

戻り値

Excel は戻り値を無視します。ただし、XLM マクロ シートから呼び出されたものを除きます (この場合、戻り値は TRUE または FALSE に変換されます)。そのため、コマンドが正常に実行された場合は 1 を返す必要があります。また、コマンドが失敗したり、ユーザーによって取り消されたりした場合は 0 を返す必要があります。
DLL のメモリと複数の DLL インスタンス
アプリケーションが DLL を読み込むと、DLL の実行可能コードがグローバル ヒープに読み込まれ、実行できるようになり、データ構造のグローバル ヒープに領域が割り当てられます。Windows では、メモリ マッピングを使用して、これらのメモリ領域がアプリケーションのプロセス内にあるように表示し、アプリケーションがそこにアクセスできるようにします。
その後、2 番目のアプリケーションが DLL を読み込んでも、Windows は DLL の実行可能コードのコピーを別に作成することはありません (そのメモリは読み取り専用になっているため)。Windows は、DLL の実行可能コードのメモリを両方のアプリケーションのプロセスにマッピングします。ただし、DLL のデータ構造のプライベート コピー用に 2 番目の領域を割り当て、このコピーを 2 番目のプロセスにのみマッピングします。これにより、どちらのアプリケーションも相互の DLL データに干渉しないようにします。
そのため、DLL 開発者は、静的変数やグローバル変数、データ構造体が複数のアプリケーションからアクセスされたり、同じアプリケーションの複数のインスタンスからアクセスされたりすることについて心配する必要がなくなります。すべてのアプリケーションの全インスタンスは、DLL のデータの独自のコピーを取得します。
DLL 開発者は、アプリケーションの同じインスタンスが、そのインスタンス専用の DLL を別のスレッドから何回も呼び出すことについて配慮する必要があります。この場合は、そのインスタンスのデータに競合が発生する可能性があります。詳細については、「Excel のメモリ管理」を参照してください。

ちょっと重要なことが書かれている！
そのうちまとめよう！