PowerPoint プレゼンテーション

配列とコレクション

配列の使い方 固定配列 動的配列 コレクションの使い方 今日の目的

固定配列の宣言例 プロシージャレベル Dim arybuf(0 To 5) As Long モジュールレベル Private arybuf(0 To 5) As Long Public arybuf(0 To 5) As Long

固定配列の宣言例 プロシージャレベル Dim arybuf(0 To 5) As Long モジュールレベル Private arybuf(0 To 5) As Long Public arybuf(0 To 5) As Long 配列の最小範囲と最大範囲を指定この場合は下記のような要素 arybuf(0), arybuf(1), arybuf(2) arybuf(3), arybuf(4), arybuf(5)

固定配列の宣言例 最小範囲を省略することもできる Dim arybuf(5) As Long

固定配列の宣言例 最小範囲を省略することもできる Option Base 1 Dim arybuf(5) As Long 1~5 になる Option Base 0 * 省略した場合と同じ動き Dim arybuf(5) As Long 0~5 になる

固定配列の宣言例 プロシージャレベル Dim arybuf(0 To 5) As Long モジュールレベル Private arybuf(0 To 5) As Long Public arybuf(0 To 5) As Long 最小範囲と最大範囲は明示しとこう

固定配列の宣言例 Const lmax As Long = 5 Dim arybuf(0 To lmax) As Long 範囲には定数をしていできる ( 変数はダメ )

固定配列の使用例 Const lmax As Long = 5 Dim arybuf(0 To lmax) As Long Dim i As Long For i = LBound(aryBuf) To UBound(aryBuf) Debug.Print i & ":" & arybuf(i) Next i

固定配列の使用例 Const lmax As Long = 5 Dim arybuf(0 To lmax) As Long Dim i As Long For i = LBound(aryBuf) To UBound(aryBuf) Debug.Print i & ":" & arybuf(i) Next i LBound() は配列の下限を取得するこの場合は 0 がかえる

固定配列の使用例 Const lmax As Long = 5 Dim arybuf(0 To lmax) As Long Dim i As Long For i = LBound(aryBuf) To UBound(aryBuf) Debug.Print i & ":" & arybuf(i) Next i UBound() は配列の上限を取得するこの場合は 5 がかえる

固定配列の使用例 Const lmax As Long = 5 Dim arybuf(0 To lmax) As Long Dim i As Long For i = LBound(aryBuf) To UBound(aryBuf) Debug.Print i & ":" & arybuf(i) Next i

多次元配列の宣言例 Dim arybuf(0 To 5, 0 To 1) As String 次元をコンマで区切って宣言できる最高で 60 次元までできる

多次元配列の使用例 Dim arybuf(0 To 5, 0 To 1) As String Dim i As Long Dim j As Long For i = LBound(aryBuf, 1) To UBound(aryBuf, 1) For j = LBound(aryBuf, 2) To UBound(aryBuf, 2) Debug.Print i &, & j & ":" & arybuf(i, j) Next j Next i

多次元配列の使用例 Dim arybuf(0 To 5, 0 To 1) As String Dim i As Long Dim j As Long For i = LBound(aryBuf, 1) To UBound(aryBuf, 1) For j = LBound(aryBuf, 2) To UBound(aryBuf, 2) Debug.Print i &, & j & ":" & arybuf(i, j) Next j Next i LBound,UBound の第二引数に次元を指定することで その次元の下限 上限を取得できる

多次元配列の使用例 Dim arybuf(0 To 5, 0 To 1) As String Dim i As Long Dim j As Long For i = LBound(aryBuf, 1) To UBound(aryBuf, 1) For j = LBound(aryBuf, 2) To UBound(aryBuf, 2) Debug.Print i & ", & j & ":" & arybuf(i, j) Next j Next i 1 次元目の下限を求める 0 になる

多次元配列の使用例 Dim arybuf(0 To 5, 0 To 1) As String Dim i As Long Dim j As Long For i = LBound(aryBuf, 1) To UBound(aryBuf, 1) For j = LBound(aryBuf, 2) To UBound(aryBuf, 2) Debug.Print i & ", & j & ":" & arybuf(i, j) Next j Next i 1 次元目の上限を求める 5 になる

多次元配列の使用例 Dim arybuf(0 To 5, 0 To 1) As String Dim i As Long Dim j As Long For i = LBound(aryBuf, 1) To UBound(aryBuf, 1) For j = LBound(aryBuf, 2) To UBound(aryBuf, 2) Debug.Print i & ", & j & ":" & arybuf(i, j) Next j Next i 2 次元目の下限を求める 0 になる

多次元配列の使用例 Dim arybuf(0 To 5, 0 To 1) As String Dim i As Long Dim j As Long For i = LBound(aryBuf, 1) To UBound(aryBuf, 1) For j = LBound(aryBuf, 2) To UBound(aryBuf, 2) Debug.Print i & ", & j & ":" & arybuf(i, j) Next j Next i 2 次元目の上限を求める 1 になる

多次元配列の使用例 Dim arybuf(0 To 5, 0 To 1) As String Dim i As Long Dim j As Long For i = LBound(aryBuf, 1) To UBound(aryBuf, 1) For j = LBound(aryBuf, 2) To UBound(aryBuf, 2) Debug.Print i & ","; j & ":" & arybuf(i, j) Next j Next i

動的配列動的配列を宣言すると コードの実行中に配列の サイズを変更できる

動的配列の例 Dim arybuf() As String lmax = 5 ' 動的配列では変数での範囲指定が可能 ReDim arybuf(0 To lmax) As String

動的配列の例 Dim arybuf() As String lmax = 5 ' 動的配列では変数での範囲指定が可能 ReDim arybuf(0 To lmax) As String

動的配列の例 Dim arybuf() As String lmax = 5 ' 動的配列では変数での範囲指定が可能 ReDim arybuf(0 To lmax) As String

動的配列の例 Dim arybuf() As String lmax = 5 ' 動的配列では変数での範囲指定が可能 ReDim arybuf(0 To 0) As String arybuf(0) = ゆっくりしていってね ReDim arybuf(0 To lmax) As String

動的配列の例 Dim arybuf() As String lmax = 5 ' 動的配列では変数での範囲指定が可能 ReDim arybuf(0 To 0) As String arybuf(0) = ゆっくりしていってね ReDim arybuf(0 To lmax) As String

動的配列の例 Dim arybuf() As String lmax = 5 ' 動的配列では変数での範囲指定が可能 ReDim arybuf(0 To 0) As String arybuf(0) = ゆっくりしていってね ReDim Preserve arybuf(0 To lmax) As String

動的配列の例 Dim arybuf() As String lmax = 5 ' 動的配列では変数での範囲指定が可能 ReDim arybuf(0 To 0) As String arybuf(0) = ゆっくりしていってね ReDim Preserve arybuf(0 To lmax) As String

動的配列の例 Dim arybuf() As String ReDim arybuf(0 To 5, 0 To 1) As String arybuf(0, 0) = " オプーナ ザ ワゴン ReDim Preserve arybuf(0 To 6, 0 To 1) As String

動的配列の例 Dim arybuf() As String ReDim arybuf(0 To 5, 0 To 1) As String arybuf(0, 0) = " オプーナ ザ ワゴン ReDim Preserve arybuf(0 To 6, 0 To 1) As String

動的配列の例 Dim arybuf() As String ReDim arybuf(0 To 5, 0 To 1) As String arybuf(0, 0) = " オプーナ ザ ワゴン ReDim Preserve arybuf(0 To 5, 0 To 2) As String

動的配列の解放 Eraseステートメントを使用する Erase 動的配列 メモリの解放 配列を再度しようするには ReDim が必要 Erase 固定配列 要素の再初期化

Const lmax As Long = 5 Dim arydyn() As String Dim aryfix(0 To lmax) As String ReDim arydyn(0 To lmax) As String arydyn(0) = " オプーナ aryfix(0) = arydyn(0) ~ 略 ( 要素を埋めます ) ~ Debug.Assert false Erase aryfix Erase arydyn Debug.Assert false

Const lmax As Long = 5 Dim arydyn() As String Dim aryfix(0 To lmax) As String ReDim arydyn(0 To lmax) As String arydyn(0) = " オプーナ aryfix(0) = arydyn(0) ~ 略 ( 要素を埋めます ) ~ Debug.Assert false Erase aryfix Erase arydyn Debug.Assert false

Const lmax As Long = 5 Dim arydyn() As String Dim aryfix(0 To lmax) As String ReDim arydyn(0 To lmax) As String arydyn(0) = " オプーナ aryfix(0) = arydyn(0) ~ 略 ( 要素を埋めます ) ~ Debug.Assert false Erase aryfix Erase arydyn Debug.Assert false

Const lmax As Long = 5 Dim arydyn() As String Dim aryfix(0 To lmax) As String ReDim arydyn(0 To lmax) As String arydyn(0) = " オプーナ aryfix(0) = arydyn(0) ~ 略 ( 要素を埋めます ) ~ Debug.Assert false Erase aryfix Erase arydyn Debug.Assert false

Const lmax As Long = 5 Dim arydyn() As String Dim aryfix(0 To lmax) As String ReDim arydyn(0 To lmax) As String arydyn(0) = " オプーナ aryfix(0) = arydyn(0) ~ 略 ( 要素を埋めます ) ~ Debug.Assert false Erase aryfix Erase arydyn Debug.Assert false

Const lmax As Long = 5 Dim arydyn() As String ReDim arydyn(0 To lmax) As String Debug.Print LBound(aryDyn) ~ 略 ( 要素を埋めます ) ~ Erase arydyn Debug.Print LBound(aryDyn)

Const lmax As Long = 5 Dim arydyn() As String ReDim arydyn(0 To lmax) As String Debug.Print LBound(aryDyn) ~ 略 ( 要素を埋めます ) ~ Erase arydyn Debug.Print LBound(aryDyn)

Const lmax As Long = 5 Dim arydyn() As String ReDim arydyn(0 To lmax) As String Debug.Print LBound(aryDyn) ~ 略 ( 要素を埋めます ) ~ Erase arydyn Debug.Print LBound(aryDyn)

Const lmax As Long = 5 Dim arydyn() As String ReDim arydyn(0 To lmax) As String ~ 略 ( 要素を埋めます ) ~ Erase arydyn Debug.Print Lbound(aryDyn)

Private Function IsAllocatedArray (ByRef ary() As String) On Error GoTo ErrCatch: Dim i As Long i = LBound(ary) IsAllocatedArray = True Exit Function ErrCatch: If err.number <> 9 Then err.raise err.number, err.source, err.description, err.helpfile, err.helpcontext End If IsAllocatedArray= False End Function

Private Function IsArrocatedArray(ByRef ary() As String) On Error GoTo ErrCatch: Dim i As Long i = LBound(ary) IsArrocatedArray = True Exit Function ErrCatch: If err.number <> 9 Then err.raise err.number, err.source, err.description, err.helpfile, err.helpcontext End If IsArrocatedArray = False End Function

Private Function IsAllocatedArray (ByRef ary() As String) On Error GoTo ErrCatch: Dim i As Long i = LBound(ary) IsAllocatedArray= True Exit Function ErrCatch: If err.number <> 9 Then err.raise err.number, err.source, err.description, err.helpfile, err.helpcontext End If IsAllocatedArray = False End Function LBound がエラーではない = 配列として使用できる

Private Function IsAllocatedArray (ByRef ary() As String) On Error GoTo ErrCatch: Dim i As Long i = LBound(ary) IsAllocatedArray= True Exit Function ErrCatch: If err.number <> 9 Then err.raise err.number, err.source, err.description, err.helpfile, err.helpcontext End If IsAllocatedArray= False End Function LBound がエラー ErrCatch ラベルへ飛ぶ

Private Function IsAllocatedArray (ByRef ary() As String) On Error GoTo ErrCatch: インデックス範囲外の Dim i As Long エラーじゃなければ i = LBound(ary) そのエラーを再度発生させ IsArrocatedArray = True る Exit Function ErrCatch: If err.number <> 9 Then err.raise err.number, err.source, err.description, err.helpfile, err.helpcontext End If IsAllocatedArray = False End Function

Const lmax As Long = 5 Dim arydyn() As String ReDim arydyn(0 To lmax) As String ~ 略 ( 要素を埋めます ) ~ Debug.Print IsAllocatedArray (arydyn) Erase arydyn Debug.Print IsAllocatedArray (arydyn)

Const lmax As Long = 5 Dim arydyn() As String ReDim arydyn(0 To lmax) As String ~ 略 ( 要素を埋めます ) ~ Debug.Print IsAllocatedArray (arydyn) Erase arydyn Debug.Print IsAllocatedArray (arydyn)

Const lmax As Long = 5 Dim arydyn() As String ReDim arydyn(0 To lmax) As String ~ 略 ( 要素を埋めます ) ~ Debug.Print IsArrocatedArray(aryDyn) Erase arydyn Debug.Print IsArrocatedArray(aryDyn)

Const lmax As Long = 5 Dim arydynlng() As Long ReDim arydynlng(0 To lmax) As Long ~ 略 ( 要素を埋めます ) ~ Debug.Print IsAllocatedArray (arydynlng) Erase arydynlng Debug.Print IsAllocatedArray (arydynlng) 動的配列の型を変えると型の不一致エラーになる

Private Function IsAllocatedArray (ByRef ary As Variant) On Error GoTo ErrCatch: Dim i As Long i = LBound(ary) IsAllocatedArray= True Exit Function ErrCatch: If err.number <> 9 Then err.raise err.number, err.source, err.description, err.helpfile, err.helpcontext ary As Variant End If IsAllocatedArray= False End Function 引数の変更 ary() As String

Const lmax As Long = 5 Dim arydynlng() As Long ReDim arydynlng(0 To lmax) As Long ~ 略 ( 要素を埋めます ) ~ Debug.Print IsAllocatedArray (arydynlng) Erase arydynlng Debug.Print IsAllocatedArray (arydynlng) 引数を Variant にすると予定通り動作する

Variant 型に配列を格納 Dim arybuf(0 To 5) As String arybuf(0) = " オプーナ ザ ワゴン Dim v As Variant v = arybuf For i = LBound(v) To UBound(v) Debug.Print i & ":" & v(i) Next i Variant 型の変数に配列を代入すると 配列として普通に使えるようになります

配列の使い方 固定配列 動的配列 コレクションの使い方 今日の目的

コレクション 異なる型やオブジェクトを格納できる 各要素の追加 削除が容易 連想配列としても使用できる

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim vdata As Variant For Each vdata In cll Debug.Print TypeName(vData) & ":" & vdata Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) バリアント型に一端格納しないパターンの例 Call cll.add(sheet1.cells(1, 1)) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Call cll.add( ゆっくりしていってね, Before:=1 ) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing Before:=x とすることで要素 x の前に新しい要素を追加する

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Call cll.add( ゆっくりしていってね, Before:=1 ) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Call cll.add( ゆっくりしていってね, After:=1 ) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing After:=x とすることで要素 x の後に新しい要素を追加する

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Call cll.add( ゆっくりしていってね, After:=1 ) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Call cll.remove( 1 ) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing Remove メソッドを使用することで指定の要素を削除できる

Dim cll As New Collection Call cll.add(" オプーナ ") Call cll.add(#1/3/2008 3:32:00 AM#) Call cll.add(23432&) Call cll.add(sheet1.cells(1, 1)) Call cll.remove( 1 ) Dim i As Long For i = 1 To cll.count Debug.Print TypeName(cll.Item(i)) & ":" & cll.item(i) Next Set cll = Nothing

連想配列 String 型の一意のキーを指定して効果発動 プログラマはキーを元にデータの取得 削除ができる

Dim cll As New Collection Call cll.add(" カイジ ", " 顎 ") Debug.Print cll.item(" 顎 ") Set cll = Nothing

Dim cll As New Collection Call cll.add(" カイジ ", " 顎 ") Call cll.add( アカギ, 倍プッシュ ", before:=" 顎 ") Set cll = Nothing

Dim cll As New Collection Call cll.add(" カイジ ", " 顎 ") Call cll.add( アカギ, 倍プッシュ ", before:=" 顎 ") Call cll.add( 黒沢, 最強, after:= 倍プッシュ ") Set cll = Nothing

Dim cll As New Collection Call cll.add(" カイジ ", " 顎 ") Call cll.add( アカギ, 倍プッシュ ", before:=" 顎 ") Call cll.add( 黒沢, 最強, after:= 倍プッシュ ") Call cll.remove(" 最強 ") Set cll = Nothing

連想配列 String 型の一意のキーを指定して効果発動 プログラマはキーを元にデータの取得 削除ができる

Private Function HasCollectionKey(ByRef cll As Collection, _ ByVal keyname As String) As Boolean On Error GoTo ErrCatch Dim lret As Long lret = VarType(cll.Item(keyName)) HasCollectionKey = True Exit Function ErrCatch: If err.number <> 5 Then キーの存在チェック err.raise err.number, err.source, err.description, _ err.helpfile,err.helpcontext Exit Function End If HasCollectionKey = False End Function

Private Function HasCollectionKey(ByRef cll As Collection, _ ByVal keyname As String) As Boolean On Error GoTo ErrCatch Dim lret As Long lret = VarType(cll.Item(keyName)) HasCollectionKey = True Exit Function ErrCatch: If err.number <> 5 Then キーの存在チェック err.raise err.number, err.source, err.description, _ err.helpfile,err.helpcontext Exit Function End If HasCollectionKey = False End Function

Public Sub TestHasCollectionKey() Dim cll As New Collection ' キーのチェック Call cll.add(" アカギ ", " 倍プッシュ ") Debug.Print "True のはず ;" & HasCollectionKey(cll, " 倍プッシュ ") Debug.Print "False のはず ;" & HasCollectionKey(cll, " 最強 ") ' オブジェクトも検索可能 Call cll.add(sheet1, " シート 1") Debug.Print "True のはず ;" & HasCollectionKey(cll, " シート 1 ") Debug.Print "False のはず ;" & HasCollectionKey(cll, " シート 2 ") Set cll = Nothing End Sub

ExcelVBA006.xlsmの標準モジュール mdlarraysample サンプル 配列のサンプルプログラム mdlcollectionsample コレクションのサンプルプログラム

配列とコレクション