配列に特定の要素が何番目に含まれるかをチェックする

VBAで配列の中身が空であるのを判定する関数です。配列の中で１つでも値が入っていたら、「空でない」と判定します。文字、数値、日付などの配列にも対応。

 

'=========================================================
'[概  要] 配列が空かどうかを判定する関数
'[引  数] arr ： チェック対象の配列
'[戻り値] True：空、False：空でない
'[備  考] 一つでも空でないものがある場合、「空でない」とみなす
'=========================================================
Function CheckArrayEmpty(arr As Variant) As Boolean
   
    If IsArray(arr) Then
        ' 配列が有効な場合
        Dim i As Long
        For i = LBound(arr) To UBound(arr)
            If (IsEmpty(arr(i)) Or IsNull(arr(i)) Or (VarType(arr(i)) = vbString And Len(arr(i)) = 0)) = False Then
                CheckArrayEmpty = False '空でない
                Exit Function
            End If
        Next i
        CheckArrayEmpty = True '空
    Else
        CheckArrayEmpty = True '空
    End If
   
End Function

 

 

 

'テスト実行
Dim testArr(2) As Variant

'値セットなし
Debug.Print CheckArrayEmpty(testArr) '結果：（True 空）

'すべてNull
testArr(0) = Null
testArr(1) = Null
testArr(2) = Null
Debug.Print CheckArrayEmpty(testArr) '結果：（True 空）

'文字列：一部に値をセット
testArr(0) = "a"
Debug.Print CheckArrayEmpty(testArr) '結果：（False 空でない）

'文字列：間に値をセット
testArr(0) = "a"
testArr(1) = ""
testArr(2) = "c"
Debug.Print CheckArrayEmpty(testArr) '結果：（False 空でない）

'数値：一部に値をセット
testArr(0) = 1
Debug.Print CheckArrayEmpty(testArr)  '結果：（False 空でない）

'数値：間に値をセット
testArr(0) = 1
testArr(1) = Null
testArr(2) = 3
Debug.Print CheckArrayEmpty(testArr)  '結果：（False 空でない）

'日付：一部に値をセット
testArr(0) = Now()
Debug.Print CheckArrayEmpty(testArr)  '結果：（False 空でない）

'日付：間に値をセット
testArr(0) = Now()
testArr(1) = Null
testArr(2) = DateAdd("d", 1, Now())
Debug.Print CheckArrayEmpty(testArr)  '結果：（False 空でない）

配列に特定の要素が何番目に含まれるかをチェックする

VBAで、配列に特定の要素が何番目に含まれるかをチェックするサンプルプログラムです。

 

'=========================================================
'[概  要] 配列に特定の要素が何番目に含まれるかをチェックする
'[引数１] arr        : 探索対象の配列
'[引数２] SearchStr  : 探索する文字列
'[戻り値] 要素のある位置（配列の添え字）を返す（0～）
'[備考１] 取得した要素の番号は0始まり
'[備考２] ない場合、エラー時は-1で返す
'=========================================================
Public Function checkArrayContainElement(ByVal arr As Variant, ByVal SearchStr As String) As Integer

On Error GoTo ErrLabel
   
    For i = 0 To UBound(arr)
   
        '部分一致（検索文字を含んでいればOK）
        If InStr(arr(i), SearchStr) > 0 Then
            checkArrayContainElement = i
            Exit Function
        End If
       
        '完全一致ならこちらを使用する
        If arr(i) = SearchStr Then
            checkArrayContainElement = i
            Exit Function
        End If
   
    Next
   
    'ない場合は-1
    checkArrayContainElement = -1
   
ErrLabel:

    'エラー時も空とみなす
    checkArrayContainElement = -1
   
End Function

 

 

 

'上の関数のテスト実行
Dim testArr(2) As Variant

'すべてNull
testArr(0) = "あいう"
testArr(1) = "えお"
testArr(2) = "かきく"

MsgBox checkArrayContainElement(testArr, "う") '結果：0
MsgBox checkArrayContainElement(testArr, "お") '結果：1
MsgBox checkArrayContainElement(testArr, "さ") '結果：-1

イミディエイトウィンドウをクリアする

VBA実行時、イミディエイトウィンドウをきれいにしてから実行したいという人もいると思います。大きく①Debug.Printで改行を200回やる方法と、②SendKeyを使う方法とあるのですが、①Debug.Printだとうまく消えきらなかったため（前回のが残ってしまう）、SendKeyでやることにしました。SendKeyもどうかいう意見もあると思いますが、私の環境では問題なく使えているので是非使ってみてください。

 

'=========================================================
'[概  要] イミディエイトウィンドウ内をクリアする
'[引　数] なし
'[戻り値] なし
'[備　考]
'=========================================================
Sub ClearImmediate()
   
    '誤動作防止：イミディエイトウィンドウが非表示だったら終了
    If Not Application.VBE.Windows("イミディエイト").Visible Then
        Exit Sub
    End If
   
    SendKeys "^g"          'イミディエイトウィンドウにフォーカス
    SendKeys "^a"          '全選択
    SendKeys "{Del}"       'Delete

End Sub

現象

VBAで「VBComponent」を使用しようとした際、「コンパイル エラー:ユーザ定義型は定義されていません。」と表示されたときの対処方法です。

 

 

対処方法

次の２つの設定する必要があります。

 

①「Microsoft Visual Basic for Applications Extensibility x.x」を追加

ツール＞参照設定＞「Microsoft Visual Basic for Applications Extensibility x.x」を追加

 

 

②VBAプロジェクトオブジェクトモデルへのアクセスを信頼するにチェックON

EXCELのメニュー＞開発＞マクロの設定＞VBAプロジェクトオブジェクトモデルへのアクセスを信頼するにチェックON

 

 

これでできると思いますので、試してみて下さい。

行間調整

Variantは非常に柔軟な型で、どんな種類のデータも格納できるため変数を宣言するのにとても便利です。「なんでもかんでもVariantを使えばいいじゃん！」と思いますが、Variantにもいくつかデメリットがあります。

 

①コードの可読性の低下

変数は、他の開発者や自分自身にとって理解しやすい形であることが重要ですが、型を指定しないとその変数がどのようなデータを期待しているのかが不明確になり、可読性が低下する可能性があります。

 

②メモリ使用量の増加

Variantはオーバーヘッドがあるため、他の特定のデータ型よりもメモリ使用量が多くなり、実行速度が低下する可能性があります。

 

③実行時エラーのリスク

Variantは実行時に型変換が行われるため、型が一致しない場合に実行時エラーが発生する可能性があります。型を厳密に指定することで、コンパイル時にエラーを検出することができ、安全性が向上します。

デメリットを踏まえて、できるだけ具体的なデータ型を選択して変数を宣言することが、コードの安全性、可読性、およびパフォーマンス向上につながると思います。

２次元配列の指定した行に1次元配列を挿入する

VBAで、2次元配列の指定した行に1次元配列を入れるサンプルプログラムです。

 

'=========================================================
'[概  要] ２次元配列の指定した行に1次元配列を挿入する
'[引数１] arr        : 2次元配列（この配列に挿入する）
'[引数２] rowIndex   : 挿入する行（0から始まるため注意）
'[引数３] newData    : 1次元配列（この配列を挿入する）
'[戻り値] 挿入後の配列
'[備　考]
'=========================================================
Function InsertAndShift(arr() As String, rowIndex As Integer, newData() As String) As String()

    Dim numRows As Integer
    Dim numCols As Integer
    numRows = UBound(arr, 1) + 1
    numCols = UBound(arr, 2) + 1

    ' 新しい行の追加
    Dim newArr() As String
    ReDim newArr(0 To numRows, 0 To numCols - 1)
    For i = 0 To numRows - 1
        For j = 0 To numCols - 1
            newArr(i, j) = arr(i, j)
        Next j
    Next i

    ' 指定位置以降のデータを後ろにシフト
    For i = numRows To rowIndex + 1 Step -1
        For j = 0 To numCols - 1
            newArr(i, j) = arr(i - 1, j)
        Next j
    Next i

    ' 新しいデータを挿入
    For j = 0 To numCols - 1
        newArr(rowIndex, j) = newData(j)
    Next j

    ' 結果の配列を返す
    InsertAndShift = newArr
   
End Function

 

 

'上の関数のテスト実行
Sub TestInsertData()

    Dim arr() As String
    ReDim arr(0 To 1, 0 To 3)

    '2次元配列の初期値を設定
    arr(0, 0) = "a0"
    arr(0, 1) = "a1"
    arr(0, 2) = "a2"
    arr(0, 3) = "a3"
    arr(1, 0) = "b0"
    arr(1, 1) = "b1"
    arr(1, 2) = "b2"
    arr(1, 3) = "b3"
  
    '挿入するデータを定義
    Dim newData() As String
    ReDim newData(0 To 3)
    newData(0) = "c0"
    newData(1) = "c1"
    newData(2) = "c2"
    newData(3) = "c3"

    '★ここで実行
    'データを挿入してシフト
    arr = InsertAndShift(arr, 1, newData)

    'データの中身を表示する
    Dim i As Integer
    Dim j As Integer
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            Debug.Print "arr(" & i & "," & j & ") = " & arr(i, j)
        Next j
    Next i
   
End Sub

行間調整

Debug.printではデフォルトで改行してしまいますが、これを防ぐにはprint文の最後に「;」をつけることで改行されなくすることが可能です。";"には"で囲む必要はないことに注意して下さい。

'改行して表示する　最後に「;」をつけない
Debug.Print "a"

'改行なし
Debug.Print "a";

 

 

'実行テスト用
Sub ClearImmediateWindow()
    Dim i As Integer
   
    For i = 1 To 200
        '改行あり
        Debug.Print i
    Next i
   
    '改行せず表示するパターン　最後に「;」をつける
    For i = 1 To 200
        '改行なし
        Debug.Print i;
    Next i
   
End Sub

最終行を取得する、最終列を取得する

エクセルでデータのある最終行を取得する方法と、データのある最終列を取得する方法を紹介します。

 

'最終行を取得する
Worksheets(シート名).Cells(Rows.Count, 最終行をチェックする列番号).End(xlUp).Row

'実行例
Worksheets(シート名).Cells(Rows.Count, 1).End(xlUp).Row


'最終列を取得する
Worksheets(シート名).Cells(最終行をチェックする行番号, Columns.Count).End(xlToLeft).Column

'実行例
Worksheets(シート名).Cells(1, Columns.Count).End(xlToLeft).Column