Go to Qiita Advent Calendar 2024 Top
LoginSignup
55
91

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@nukie_53

VBAで使えるデータ構造とメリット・デメリット

Last updated at Posted at 2018-03-09

※ この記事では配列やコレクションの類いを「データ構造」と呼んでいます

VBAでは複数の要素をまとめて扱う方法が少なく機能的にも貧弱なため、「迷ったらコレ」と言えるものがありません。

自分なりにどのような特徴があるのか、どんな場面で使うのかを簡単にまとめておきます。

配列

VBA・VBScriptともに使用できる基本的なデータ構造。
メモリ上にまとまった領域を確保し、その領域にデータを保管する。
SAFEARRAYという構造体でラップされているが、中身はC言語の配列に近いらしい。

メリット

  • (適切に使用すれば)省メモリかつ最速。
  • 型を指定できる。
  • 型を指定できるため、Variantに格納できないユーザー定義型もまとめられる。
  • VBAのJoin・Splitをはじめとして、MS OfficeのShapeRangeの取得・ADODB関係など、既存のさまざまなライブラリとの親和性が高い。

デメリット

  • 先に必要な領域を確保する必要がある。
  • 後からの大きさの変更も可能だが、記述が若干面倒であり処理としても重めとなる。

    そのため、大きさの変更回数が多いと強みの速度を活かしきれない(数が少なければ誤差レベルではあるが)。
  • 具象オブジェクト型の配列は中身の型が保証されない(→参考)。
  • 中身が空(要素数が0)を表現しにくい(Windows APIが必要)。
  • VBA標準機能でも一部の型は(0 To -1)の配列を作成できる→参考。

その他

  • 多くの言語と異なり、VBAでは値型のように振る舞う

    (→値渡しや変数への代入でコピーが作成される)。
  • For Eachで列挙可能。この時、配列の型にかかわらずループ変数にはVariant型変数しか使用できず、添え字アクセスに比べると低速となる。

    For Eachであれば、多次元配列でも次元数を意識せずに列挙できるため、そういった面では有用。

使いどころ

  • 予め要素数を想定できる場合。
  • メリットを活かしやすいため。最大数がわかっていれば、大きめに確保してから縮める、という方法もある。
  • ランダムアクセスが必要な場合。
  • Excelのセル範囲への入出力時(1次元配列・2次元配列)。
  • 値型を格納する場合(値の取得時にコピーが発生しないため)。
  • ユーザー定義型を格納する場合。
  • 格納した要素を後から書き換えたい場合。

使用方法

Array
Dim arr(0 To 2) As String
arr(0) = "a"
arr(1) = "b"
arr(2) = "c"

Dim i As Long
For i = LBound(arr) To UBound(arr)
    Debug.Print arr(i)
Next i

参考:汎用処理

VBA 個人的汎用処理 - Qiita

'配列の末尾に要素を追加する汎用処理。
    '要素が数千個ぐらいなら実用範囲だが、あまりにも要素数が多い場合は他の方法を検討すること。

'引数
    'refArray1D :末尾に要素を追加したい1次元配列。
    'inValue    :追加したい要素。
Public Sub ArrayAdd(ByRef refArray1D As Variant, inValue As Variant)
'配列を拡張する。
    ReDim Preserve refArray1D(LBound(refArray1D) To UBound(refArray1D) + 1)
    
'末尾に要素を設定する。
    'Let/Set の判定のため、まずは配列の型を確認する。
    Dim elementType As VBA.VbVarType
    elementType = VBA.Information.VarType(refArray1D) Xor vbArray
    
    If elementType = vbObject Then
        'オブジェクト型なら Set で OK。
        Set refArray1D(UBound(refArray1D)) = inValue
    ElseIf elementType = vbDataObject Then
        Set refArray1D(UBound(refArray1D)) = inValue
    ElseIf elementType = vbVariant Then
        'Variant型なら inValue の型に合わせて Let/Set を判定。
        If VBA.Information.IsObject(inValue) Then
            Set refArray1D(UBound(refArray1D)) = inValue
        ElseIf VBA.Information.VarType(inValue) = vbDataObject Then
            Set refArray1D(UBound(refArray1D)) = inValue
        Else
            Let refArray1D(UBound(refArray1D)) = inValue
        End If
    Else
        'それ以外は値型とみなす。
        Let refArray1D(UBound(refArray1D)) = inValue
    End If
End Sub


Sub SampleOfArrayAdd()
    Dim arr() As Variant
    ReDim arr(0 To 0)
    arr(0) = 100
    
    ArrayAdd arr, New VBA.Collection
    ArrayAdd arr, Err
    ArrayAdd arr, VBA.DateTime.Time
    ArrayAdd arr, VBA.[_HiddenModule].Array(0, 1, 2)
    ArrayAdd arr, VBA.Strings.Split("a b c")
    
    Dim i As Long
    For i = 1 To 10000
        ArrayAdd arr, String$(10000, "a")
    Next i
    Stop
End Sub

VBA.Collection

Collection オブジェクト | Microsoft Docs

VBAで使用可能なデータ構造(VBScriptでは使用不可)。
挙動などを見る限り中身は片方向連結リスト。

シーケンシャルアクセスしかしない、文字列など巨大な値型を入れない、VBScriptへの移植が必要ないのであれば一番無難と思われる。

メリット

  • Addメソッドで容易に要素を追加可能。

デメリット

その他

  • 自作クラスをFor Eachに対応させる場合の選択肢の一つ。
  • PR：VBScriptでCollectionっぽく使えるクラス（中身は配列）→VBS用配列ラッパークラス。

使いどころ

  • 格納したい要素の数を想定できない場合。
  • オブジェクト型を格納する場合。

使用例

VBA.Collection
Dim col As VBA.Collection
Set col = New VBA.Collection
col.Add "a"
col.Add "b"
col.Add "c"

Dim iter As Variant
For Each iter In col
    Debug.Print iter
Next iter

Scripting.Dictionary

Dictionary オブジェクト | Microsoft Docs

キーと値をペアにして要素を格納するデータ構造。
いわゆる連想配列やハッシュテーブルと呼ばれるもの。

一般的なハッシュテーブルと異なる点としては

  • キーには文字列以外も指定可能(ユーザー定義型、配列、LongLong型以外ならOK)。
  • 要素を削除しない限り、追加した順番が維持される。

メリット

  • 数字以外のものを使って要素にアクセスできる。
  • キー・値の一覧を配列に変換可能。

デメリット

  • 要素の格納に時間がかかる(他のデータ構造と比較した場合)。
  • ローカルウィンドウから中身が確認できない(キーが列挙される)。

その他

  • Scripting.Dictionary自体はFor Eachに対応しているが、Implementsされた場合はその限りでは無い。
  • For Each時のKeyの列挙速度は速いが、KeyからItemを取得するのに多少時間がかかる。
  • 数万単位の要素数かつ、KeyとItemを両方列挙する場合はKeys()・Items()で事前に配列化しておくと速度的に有利な場合もある(要検証)。
  • VBA.Interaction.CallByName(dic, "_NewEnum", VbMethod)でIEnumVARIANTを取得できるため、自作クラスをFor Eachに対応させる場合に使うこともできる。

使いどころ

  • 数字以外のものを使ってランダムアクセスしたい場合。
  • 要素の参照回数が多い場合。
  • 重複を排除したい場合。

使用例

Scripting.Dictionary
Dim dic As Object 'As Scripting.Dictionary
Set dic = VBA.Interaction.CreateObject("Scripting.Dictionary")
dic.Item(1) = "a"
dic.Item(2) = "b"
dic.Item(3) = "c"

Dim k As Variant
For Each k In dic
    Debug.Print dic.Item(k)
Next k

ADODB.Recordset

データベースとの接続でよく使用されるオブジェクトだが、単独でも使用可能。
メモリ上における表のようなオブジェクトで、非常に多くの機能を持っている。

(200518書きかけ)

メリット

  • ある程度型を指定して要素を格納できる
  • Scripting.Dictionaryを要素とするVBA.Collectionやユーザ定義型の配列に近い立ち位置
  • 様々な形式に変換できる。
  • ソートやフィルターなどの便利機能がある。

デメリット

  • For Eachできない。
  • 複雑な構成の要素を格納する際はフィールド名の管理が重要。

便利機能の紹介

メンバー 説明
Clone .Clone(adLockReadOnly)で読み取り専用のビューを作成可能。
Filter 文字列で表現できるものに限られるが、絞り込みが可能。
Sort いくつか制約があるものの、複数のフィールドで優先順をつけて昇順・降順ソートが可能。
GetRows 任意のフィールドを2次元配列に抽出可能。
GetString TSV・CSVに近い文字列へ変換。
Save ファイルに保存して永続化。

System.Collections.ArrayList

.NET Frameworkの ArrayList クラス (System.Collections)。
配列ベースのコレクション。

メリット

  • ArrayListそのものの各種メソッド(Sortなど)を使用できる。

デメリット

使いどころ

使用例

System.Collections.ArrayList
Dim arrList As Object 'As mscorlib.ArrayList
Set arrList = VBA.CreateObject("System.Collections.ArrayList")
arrList.Add "a"
arrList.Add "b"
arrList.Add "c"

Dim iter As Variant
For Each iter In arrList
    Debug.Print iter
Next iter
As_IList_IEnumerable
'参照設定：mscorlib.dll
Dim arrList  As mscorlib.IList
Set arrList = VBA.CreateObject("System.Collections.ArrayList")
arrList.Add "a" '入力補完が効く
arrList.Add "b"
arrList.Add "c"


Dim iEum As mscorlib.IEnumerable
Set iEum = arrList

Dim iter As Variant
For Each iter In iEum
    Debug.Print iter
Next iter

JScriptのArray

ScriptControlやこちらを使用することでVBAでもJScriptのArrayオブジェクトを使用可能。

現行JavaScriptのArrayに比べると機能は劣るが、push/pop、shift/unshift、sort当たりは使用可能。
後期のIEエンジンから取得すれば、mapなども使用できる。

メリット

  • Arrayの各種メソッド(sortなど)を使用できる。

デメリット

  • 入力補完が効かない。
  • メンバー名は大文字小文字を区別するため、安定動作のためにはCallByNameを多用する必要がある。

使いどころ

  • ソートなどを手実装するのが面倒な場合。
  • JScriptの機能を使いたい場合のおまけ。

使用例

JScriptArray
'require JSFunc ( https://qiita.com/nukie_53/items/297e524bcc8e43f9b5d1 )
Dim jsArr As Object
Set jsArr = JSFunc("", "[]")(Empty)

Dim push As Object
Set push = JSFunc("a,v", "a.push(v)")
push jsArr, "a"
push jsArr, "b"
push jsArr, "c"

Dim i As Long
For i = 0 To VBA.CallByName(jsArr, "length", VbGet) - 1
    Debug.Print VBA.CallByName(jsArr, CStr(i), VbGet)
Next i

Dim convetToCol As Object
Set convetToCol = JSFunc("a,c", _
    "var l=a.length;" & _
    "for(var i=0;i<l;i++)c.Add(a[i]);" & _
    "return c", False)

Dim iter As Variant
For Each iter In convetToCol(jsArr, New VBA.Collection)
    Debug.Print iter
Next iter

個人的まとめ（自分の場合）

純粋なデータ格納用途としては配列かVBA.Collection、それ以外は付随機能を使いたい場合に考慮する。

配列・VBA.Collectionの使い分けは、

値型かつ要素数がわかっている場合は配列。
オブジェクト型や要素数が不定の場合はVBA.Collection。

要素数が不定だが、ランダムアクセスが必要な場合などは、VBA.Collectionで取得してから配列に入れ直し。

参考

VBA標準機能で作成できる要素数0の配列 - Qiita
VBS用配列ラッパークラス
VBAからJScriptのfunctionオブジェクトを使用する（64bit対応） - Qiita

VBAから.Net Framework がつかえなくなった | RelaxTools Addin for Excel 2010/2013/2016

55
91
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
55
91

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?