VBAで.NET FrameworkのArrayListクラスとRandomクラスを使う

VBAで.NET Frameworkが使えたんだ

VBAと.NET Frameworkは仲が悪いと思ってました。VBAで.NET Frameworkの機能（クラス）なんて使えるわけがないと。

でも、一部ですが使えます。

VBAでも配列についていろいろテクニックが紹介されていますが、.NET FrameworkのArrayListクラスを使ってみます。

WindowsXP .NET Framework 2.0 Excel2002 SP2 で動作確認。
Common Language Runtime Library (mscorlib.tlb)を参照設定。

.NET Frameworkのバージョンは、「インストールされている Microsoft .NET Framework のバージョンおよび Service Pack のレベルを確認する方法(http://support.microsoft.com/kb/318785/jåa)」で確認して下さい。

System.CollectionsのArrayListクラスで配列を操作する

ArrayListクラスの特徴

このArrayListクラスは、必要に応じてサイズが自動的に動的に増減します。
Excel-VBAで動的配列を実現しようとすると、ReDimステートメントやPreserveキーワードをつかって、設定を変えなければなりませんが、ArrayListはより直感的に扱うことができます。

詳細は、msdnライブラリ(ArrayListクラス)(http://msdn.microsoft.com/ja-jp/library/7x4b0a97(v=VS.80).aspx)を参照。
追加、挿入、削除、並び替え（降順、昇順）、そのほかたくさんのメソッドがあります。

ArrayListオブジェクトの作成とaddメソッド

まず手始めに、ArrayListオブジェクトを作り、要素を追加します。

Sub myArrayList_Add()
Dim DataList As ArrayList
Set DataList = CreateObject("System.Collections.ArrayList")

Dim strMsg As String
Dim vCnt, Cnt As Variant
Dim lngCnt As Integer

   'add
   DataList.Add "B"
   DataList.Add "E"
   DataList.Add "C"
   DataList.Add "A"
   DataList.Add "F"
   DataList.Add "D"

strMsg = "DataList.Add"
strMsg = strMsg & vbCrLf & "要素の数: " & vbTab & DataList.Count

lngCnt = 0
For Each vCnt In DataList
   strMsg = strMsg & vbCrLf & "index : " & lngCnt & vbTab & "value : " & vCnt
   lngCnt = lngCnt + 1
Next vCnt
   MsgBox strMsg
Set DataList = Nothing
End Sub

Insertメソッド

特定のインデックスに追加したいときは、Insertメソッドを使います。

DataList.Insert 1, "X" 'index 1 の位置に X を挿入します。

index 1 の位置に X を挿入すると、元のindex 1以上にある項目のindexはそれぞれ1ずつ増え、全体の項目数も自動的に1増える。

IndexOfメソッド

IndexOfメソッドを使うと、格納された値からインデックスを探せます。

たとえば、ArrayList 全体内で「A」が最初に見つかった位置の 0 から始まるインデックスを返すには次のようにします。

DataList.IndexOf_3("A")

指定したインデックスから最後の要素までのArrayList 内の要素の範囲内で「A」が最初に出現する位置の 0 から始まるインデックス番号を返すには、

DataList.IndexOf("A", 2) 'インデックス番号0から2までを走査

指定したインデックスから始まって指定した数の要素を格納する ArrayList 内の要素の範囲内で「A」が最初に出現する位置の 0 から始まるインデックス番号を返します。

DataList.IndexOf_2("A", 2, 3) 'インデックス番号2から4までを走査

Sortメソッド

昇順に並び替えるには、Sortメソッドを使います。

DataList.Sort

Reverseメソッド

逆順に並び替えるには、Reverseメソッドを使います。

DataList.Reverse

指定した範囲の要素を逆順に並び替えるには、

DataList.Reverse_2 3, 2 'インデックス3,4を逆順にします。

Removeメソッド

ArrayList 内で最初に見つかった特定のオブジェクトを削除するには、Removeメソッドを使います。

DataList.Remove ("B") '最初に見つかった「B」を削除する。

「B」を削除すると、「B」よりインデックスが大きいオブジェクトのインデックスが1ずつ小さくなり、全体の要素の数が1減ります。

RemoveAtメソッド

インデックスを指定して削除するには、RemoveAtメソッド。

DataList.RemoveAt (1) 'インデックス番号1を削除。

RemoveRangeメソッド。

特定の範囲を削除するには、RemoveRangeメソッド。

DataList.RemoveRange 2, 3 'インデックス番号2から4の要素を削除。

サンプル

手っ取り早く使ってみたい場合は、こちらをどうぞ。

'==================
'参照設定 Common Language Runtime Library (mscorlib.tlb)
'==================
Sub Sample()
Dim DataList As ArrayList
   Set DataList = CreateObject("System.Collections.ArrayList")

Dim strMsg As String
Dim vCnt As Variant
Dim lngCnt As Long
Dim Cnt As Variant

'add
   DataList.Add "B"
   DataList.Add "E"
   DataList.Add "C"
   DataList.Add "A"
   DataList.Add "F"
   DataList.Add "D"

   strMsg = "DataList.Add"
   strMsg = strMsg & vbCrLf & "要素の数: " & vbTab & DataList.Count

   lngCnt = 0
   For Each vCnt In DataList
      strMsg = strMsg & vbCrLf & "index : " & lngCnt & vbTab & "value : " & vCnt
      lngCnt = lngCnt + 1
   Next vCnt
   MsgBox strMsg

'Insert
DataList.Insert 1, "X"

   strMsg = "DataList.Add"
   strMsg = strMsg & vbCrLf & "要素の数: " & vbTab & DataList.Count

   lngCnt = 0
   For Each vCnt In DataList
      strMsg = strMsg & vbCrLf & "index : " & lngCnt & vbTab & "value : " & vCnt
      lngCnt = lngCnt + 1
   Next vCnt
   MsgBox strMsg

'IndexOf
' Cnt = DataList.IndexOf("A", 2)
' Cnt = DataList.IndexOf_2("A", 2, 3)
Cnt = DataList.IndexOf_3("A")

   strMsg = "DataList.IndexOf(" & """A""" & ", 1)"
   strMsg = strMsg & vbCrLf & Cnt
   MsgBox strMsg

'sort
DataList.Sort

   strMsg = "DataList.Sort"
   strMsg = strMsg & vbCrLf & "要素の数: " & vbTab & DataList.Count

   lngCnt = 0
   For Each vCnt In DataList
      strMsg = strMsg & vbCrLf & "index : " & lngCnt & vbTab & "value : " & vCnt
      lngCnt = lngCnt + 1
   Next

   MsgBox strMsg

'reverse
DataList.Reverse

   strMsg = "DataList.Reverse"
   strMsg = strMsg & vbCrLf & "要素の数: " & vbTab & DataList.Count

   lngCnt = 0

   For Each vCnt In DataList
      strMsg = strMsg & vbCrLf & "index : " & lngCnt & vbTab & "value : " & vCnt
      lngCnt = lngCnt + 1
   Next

   MsgBox strMsg

' Reverse
DataList.Reverse_2 3, 2

   strMsg = "DataList.Reverse_2 3, 2"
   strMsg = strMsg & vbCrLf & "要素の数: " & vbTab & DataList.Count

   lngCnt = 0
   For Each vCnt In DataList
      strMsg = strMsg & vbCrLf & "index : " & lngCnt & vbTab & "value : " & vCnt
      lngCnt = lngCnt + 1
   Next

   MsgBox strMsg

'remove
DataList.Remove ("B")
' DataList.RemoveRange 2, 3
' DataList.RemoveAt (1)

   strMsg = "DataList.Remove(" & """B""" & ")"
   strMsg = strMsg & vbCrLf & "要素の数: " & vbTab & DataList.Count

   lngCnt = 0

   For Each vCnt In DataList
      strMsg = strMsg & vbCrLf & "index : " & lngCnt & vbTab & "value : " & vCnt
      lngCnt = lngCnt + 1
   Next

   MsgBox strMsg

Set DataList = Nothing
End Sub

Randomクラスで乱数を作る

Randomクラスのメソッドをいくつか紹介

ついでなので、Randomクラスも使ってみます。

サンプルで使ったメソッドは次の4つ。

Nextメソッド:0 以上の乱数を返します。
Next_3メソッド:指定した最大値より小さい 0 以上の乱数を返します。
Next_2メソッド:指定した範囲内の乱数を返します。
NextDoubleメソッド:0.0 と 1.0 の間の乱数を返します。

詳細は、msdnライブラリ Random クラス(http://msdn.microsoft.com/ja-jp/library/system.random.aspx)をご覧ください。

サンプル

では、サンプルコードです。

Sub DRandoms()
Dim fixedRands As Random
Dim strMsg As String
Dim i As Integer
   Set fixedRands = CreateObject("System.Random")

'0 以上の乱数を返します。
strMsg = "Next(0以上の乱数):"

   For i = 0 To 5
      strMsg = strMsg & " " & fixedRands.Next
   Next

'指定した範囲内の乱数を返します。
strMsg = strMsg & vbCrLf & "Next_2(5以上10未満の乱数):"

   For i = 0 To 5
      strMsg = strMsg & " " & fixedRands.Next_2(5, 10)
   Next

'指定した最大値より小さい 0 以上の乱数を返します。
strMsg = strMsg & vbCrLf & "Next_3(20より小さい0以上の乱数):"

   For i = 0 To 5
      strMsg = strMsg & " " & fixedRands.Next_3(20)
   Next

'0.0 と 1.0 の間の乱数を返します。(0.0 以上 1.0 未満の倍精度浮動小数点数。)
strMsg = strMsg & vbCrLf & "Nextdouble(0.0と1.0の間の乱数):"

   For i = 0 To 5
      strMsg = strMsg & " " & fixedRands.Nextdouble
   Next

   MsgBox strMsg

Set fixedRands = Nothing
End Sub

COM interopとVBA

技術的背景を少しだけ

COMオブジェクトと.NET Frameworkに関する技術的なことは、MSDNの「Microsoft Office と .NET の相互運用性」(http://msdn.microsoft.com/ja-jp/library/dd313957.aspx)に詳しく説明がされています。

VBAについても少し触れられていて、

Microsoft Office Visual Basic for Applications (VBA – COM (コンポーネント オブジェクト モデル) に基づく) コードと .NET は、互いにネイティブに通信することはできません。しかし、COM interop という .NET 機能が “コーラブル ラッパー” を提供しており、図 1 に示されているように .NET と COM を相互運用できます。

とあります。

おお、VBAから.NET Frameworkが使えるのか!!!

“CCW (COM Callable Wrapper : COM コーラブル ラッパー)” を使用すると、 .NET アセンブリが COM (たとえば、Office VBA) で使用可能になります。

とも書いてあります。

VBAから使えない部分が多いなぁ

しかし、ことVBAに関しては以下のように制限事項が多く、あまり実用的ではないかもしれません。

Office VBA は、.NET Framework クラス ライブラリが提供する機能のいくつかの呼び出しを行うことができますが、このアプローチにはいくつかの重要な制限事項があります。それは、COM (Office VBA が基づいている) が .NET Framework クラス ライブラリの以下のものを認識しないためです。

・パラメータ化されたコンストラクタ : 1 つ以上のパラメータを受け取るコンストラクタを持つ .NET Framework クラス ライブラリの任意クラスは、そのクラスの任意インスタンスが COM の New と等価な設定になっている場合 (クラスが Office Visual Basic オブジェクト ブラウザに表示されていても)、実行時エラーを生成するでしょう。たとえば、Office VBA で Dim objWS As New Excel.Worksheet(Caption:=”Sheet1″) というようなコードは成立しません。これは、コード (Caption:=”Sheet1″) が COM コード構文に従っていないためです。

・パブリックではない静的 (共有) メンバ : Private、Protected、または Static (Shared) とマークされている .NET Framework クラス ライブラリのどのプロパティ、メソッド、またはイベントも COM にアクセスできず、 Office Visual Basic オブジェクト ブラウザに表示されません。 .NET Framework クラス ライブラリ メンバが Public Instance とマークされている場合でも、メンバのクラスにパラメータ化されたコンストラクタが存在する場合は、 Office Visual Basic オブジェクト ブラウザに表示されません。

.NET Framework クラス ライブラリのドキュメントを調べると、 COM から呼び出し可能なクラスとメンバが非常に少ないことに気付くはずです。これは、.NET Framework クラス ライブラリのクラスの多くがパラメータ化されたコンストラクタを使用し、パラメータ化されたコンストラクタを持たないクラスはパブリック インスタンス (Public Instance) のメンバをほとんど所有しないからです。

ということで、「Microsoft Office と .NET の相互運用性」でのVBAに関する話題は、ここで終わっています。

今回は、.NET Frameworkのクラスライブラリ Common Language Runtime Library (mscorlib.tlb)を参照設定しました。

ただ、参照設定しても、あまりメリットはありません。上記の引用にあるように、実際に使えるプロパティやメソッドがあっても、オブジェクトブラウザにはほとんど表示されないのです。自動メンバ表示も同様。

しかし、事前バインディングはできます。

実際に、なにができるの？

より実践的に役に立つドキュメントは、「Hey, Scripting Guy!　発言には注意しましょう(http://technet.microsoft.com/ja-jp/magazine/2007.01.heyscriptingguy.aspx )」にあります。このドキュメントでは、.NET FrameworkクラスをVBScriptから利用する方法について書かれています。ここの中でも、VBScriptから.NET Frameworkクラスが使える部分があるのだが、どれが有用かはよく分かっていないと書かれています。

レジストリのHKEY_CLASSES_ROOTに登録されていれば、COMとして使えるはずですが、上記の制限があるので、使えないクラスがかなりあるようです。

ノンオフィシャルな記事では、開発リソース/JScript/スクリプトから.Net Frameworkクラスを利用する（ノウハウ編） がとても参考になりました。ありがとうございました。

Microsoftのドキュメントも、このあたりのことに触れているものはあまり見当たらず、力が入っていない様子です。ノウハウもほとんど蓄積されていない状態なので、これから使おうとすると、かなり骨が折れそう。COM interopを改良するとかして、VBAからもう少し使いやすくなるといいのですが。

ただ、VBAにとっては未開の地ですので、なにか面白いことが見つかるかも。

---

---