PowerPoint プレゼンテーション

(1)

(2)





配列の使い方



固定配列



動的配列



コレクションの使い方

今日の目的

(3)



プロシージャレベル

Dim aryBuf(0 To 5) As Long

モジュールレベル

Private aryBuf(0 To 5) As Long

Public aryBuf(0 To 5) As Long

(4)



プロシージャレベル

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)

(5)



固定配列の宣言例

最小範囲を省略することもできる

(6)



固定配列の宣言例

最小範囲を省略することもできる

Option Base 1

Dim aryBuf(5) As Long

_{→１～５になる}

Option Base 0

**‘* 省略した場合と同じ動き**

Dim aryBuf(5) As Long

(7)



プロシージャレベル

Dim aryBuf(0 To 5) As Long

モジュールレベル

Private aryBuf(0 To 5) As Long

Public aryBuf(0 To 5) As Long

固定配列の宣言例

(8)



固定配列の宣言例

Const lMax As Long = 5

Dim aryBuf(0 To lMax) As Long

範囲には定数をしていできる

（変数はダメ）

(9)



固定配列の使用例

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)

(10)



固定配列の使用例

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()は配列の下限を取得する

この場合は０がかえる

(11)



固定配列の使用例

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がかえる}

(12)



固定配列の使用例

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)

(13)



多次元配列の宣言例

Dim aryBuf(0 To 5, 0 To 1) As String

次元をコンマで区切って宣言できる

最高で６０次元までできる

(14)



多次元配列の使用例

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

(15)



多次元配列の使用例

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の第二引数に

次元を指定することで、その次元の

下限、上限を取得できる

(16)



多次元配列の使用例

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

１次元目の下限を求める

→０になる

(17)



多次元配列の使用例

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

１次元目の上限を求める

→5になる

(18)



多次元配列の使用例

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

２次元目の下限を求める

→０になる

(19)



多次元配列の使用例

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

２次元目の上限を求める

→１になる

(20)



多次元配列の使用例

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

(21)



動的配列

動的配列を宣言すると、コードの実行中に配列の

(22)



動的配列の例

Dim arybuf() As String

lMax = 5

' 動的配列では変数での範囲指定が可能

ReDim arybuf(0 To lMax) As String

(23)



動的配列の例

Dim arybuf() As String

lMax = 5

' 動的配列では変数での範囲指定が可能

ReDim arybuf(0 To lMax) As String

(24)



動的配列の例

Dim arybuf() As String

lMax = 5

' 動的配列では変数での範囲指定が可能

ReDim arybuf(0 To lMax) As String

(25)



動的配列の例

Dim arybuf() As String

lMax = 5

' 動的配列では変数での範囲指定が可能

ReDim arybuf(0 To 0) As String

aryBuf(0) = “ゆっくりしていってね”

(26)



動的配列の例

Dim arybuf() As String

lMax = 5

' 動的配列では変数での範囲指定が可能

ReDim arybuf(0 To 0) As String

aryBuf(0) = “ゆっくりしていってね”

(27)



動的配列の例

Dim arybuf() As String

lMax = 5

' 動的配列では変数での範囲指定が可能

ReDim arybuf(0 To 0) As String

aryBuf(0) = “ゆっくりしていってね”

(28)



動的配列の例

Dim arybuf() As String

lMax = 5

' 動的配列では変数での範囲指定が可能

ReDim arybuf(0 To 0) As String

aryBuf(0) = “ゆっくりしていってね”

(29)



動的配列の例

Dim arybuf() As String

ReDim aryBuf(0 To 5, 0 To 1) As String

aryBuf(0, 0) = "オプーナ・ザ・ワゴン“

(30)



動的配列の例

Dim arybuf() As String

ReDim aryBuf(0 To 5, 0 To 1) As String

aryBuf(0, 0) = "オプーナ・ザ・ワゴン“

(31)



動的配列の例

Dim arybuf() As String

ReDim aryBuf(0 To 5, 0 To 1) As String

aryBuf(0, 0) = "オプーナ・ザ・ワゴン“

(32)

(33)



動的配列の解放

Erase

ステートメントを使用する

Erase

動的配列

→メモリの解放。

配列を再度しようするには

_{ReDimが必要}

Erase

固定配列

→要素の再初期化

(34)



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

(35)



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

(36)



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

(37)



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

(38)



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

(39)



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)

(40)



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)

(41)



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)

(42)



Const lMax As Long = 5

Dim aryDyn() As String

ReDim aryDyn(0 To lMax) As String

‘ ～略（要素を埋めます）～

Erase

aryDyn

Debug.Print Lbound(aryDyn)

(43)



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

(44)



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

(45)



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がエラーではない

＝配列として使用できる

(46)



err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext End If IsAllocatedArray= False End Function

LBoundがエラー

ErrCatchラベルへ飛ぶ

(47)



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

インデックス範囲外の

エラーじゃなければ

そのエラーを再度発生させ

る

(48)



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)

(49)



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)

(50)



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)

(51)



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)

動的配列の型を変えると

型の不一致エラーになる

(52)



Private Function IsAllocatedArray (ByRef ary As Variant) On Error GoTo ErrCatch:

err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext End If IsAllocatedArray= False End Function

引数の変更

ary() As String

→ary As Variant

(53)



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にすると

予定通り動作する

(54)

(55)



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型の変数に配列を代入すると、

配列として普通に使えるようになり

ます

(56)





配列の使い方



固定配列



動的配列



コレクションの使い方

今日の目的

(57)





異なる型やオブジェクトを格納できる



各要素の追加、削除が容易



連想配列としても使用できる

(58)



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

(59)



(60)



(61)



(62)



(63)



(64)



(65)



(66)



(67)



(68)



Dim i As Long

For i = 1 To cll.Count

Debug.Print TypeName(cll.Item(i)) & ":" & cll.Item(i) Next

Set cll = Nothing

バリアント型に一端格納しない

パターンの例

(69)



Dim i As Long

(70)



Dim i As Long

(71)



Call cll.add(“ゆっくりしていってね”, Before:=1 ) Dim i As Long

_Before:=x

とすることで要素ｘの前に新しい要

素を追加する

(72)



Call cll.add(“ゆっくりしていってね”, Before:=1 ) Dim i As Long

(73)



Call cll.add(“ゆっくりしていってね”, After:=1 ) Dim i As Long

_After:=x

とすることで要素ｘの後に新しい要

素を追加する

(74)



Call cll.add(“ゆっくりしていってね”, After:=1 ) Dim i As Long

(75)



Call cll.add(Sheet1.Cells(1, 1)) Call cll.Remove( 1 )

Dim i As Long

Removeメソッドを使用することで

指定の要素を削除できる

(76)



Call cll.add(Sheet1.Cells(1, 1)) Call cll.Remove( 1 )

Dim i As Long

(77)





String型の一意のキーを指定して効果発動



プログラマはキーを元にデータの取得、削除ができ

る

(78)



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

(79)



Dim cll As New Collection Call cll.add("カイジ", "顎")

Call cll.add(“アカギ”, “倍プッシュ", before:="顎") Set cll = Nothing

(80)



Call cll.add(“アカギ”, “倍プッシュ", before:="顎") Call cll.add(“黒沢”, “最強”, after:=“倍プッシュ") Set cll = Nothing

(81)



Call cll.add(“アカギ”, “倍プッシュ", before:="顎") Call cll.add(“黒沢”, “最強”, after:=“倍プッシュ")

Call cll.Remove("最強") Set cll = Nothing

(82)





String型の一意のキーを指定して効果発動



プログラマはキーを元にデータの取得、削除ができ

る

(83)



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

キーの存在チェック

(84)



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

キーの存在チェック

(85)



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, "シート１")

Debug.Print "Trueのはず；" & HasCollectionKey(cll, "シート１ ")

Debug.Print "Falseのはず；" & HasCollectionKey(cll, "シート２ ")

Set cll = Nothing End Sub

(86)





ExcelVBA006.xlsmの標準モジュール



_{mdlArraySample}

 配列のサンプルプログラム



_{mdlCollectionSample}

 コレクションのサンプルプログラム

サンプル

(87)