エクセルVBAで外部ファイルからデータをランダムに取得する方法【VBA】

2023年12月24日

経緯

エクセルのVBA（Visual Basic for Applications）は、日々の作業を効率化するための強力なツールです。今回は、外部のエクセルファイルからランダムにデータを取得し、現在の作業中のシートにそのデータを反映させる方法を紹介します。この技術は、大量のデータからランダムにサンプルを抽出する際などに非常に便利です。

コード

まず、コードを書きます。hayashi.xlsxのデータを取得しています。

Sub FillAdjacentCellFromExternalFile()

    Dim TargetCell As Range
    Dim ExternalWorkbook As Workbook
    Dim DatabaseRange As Range
    Dim RandomText As String
    Dim AdjacentText As String

    ' 対象のセルをアクティブなセルとして設定
    Set TargetCell = ActiveCell

    ' 外部のExcelファイルを開く
    Set ExternalWorkbook = Workbooks.Open("C:\Users\mori\デスクトップ\hayashi.xlsx")

    ' データベースの範囲を設定 (例: C1:C100)
    Set DatabaseRange = ExternalWorkbook.Sheets(2).Range("C1:C100")

    ' ランダムに文字を取得 & 隣のセルの文字も取得
    RandomText = GetRandomText(DatabaseRange, AdjacentText)

    ' F列のセルにランダムに取得したデータを設定
    TargetCell.Value = RandomText
    
    ' O列のセルにランダムに取得したセルの隣のデータを設定
    TargetCell.Offset(0, 8).Value = AdjacentText

    ' メッセージボックスで取得した文字を表示
    MsgBox "取得した文字: " & RandomText

    ' 外部のExcelファイルを閉じる（変更を保存せず）
    ExternalWorkbook.Close SaveChanges:=False

End Sub

Function GetRandomText(rng As Range, ByRef AdjacentData As String) As String
    Dim RandomIndex As Long
    Dim MaxRow As Long

    Randomize ' この行を追加

    ' ランダムにインデックスを取得
    MaxRow = rng.Rows.Count
    RandomIndex = Int((MaxRow - 1 + 1) * Rnd + 1)
    
    ' 隣のセルのデータを取得
    AdjacentData = rng.Cells(RandomIndex, 2).Value

    ' ランダムに取得した文字を返す
    GetRandomText = rng.Cells(RandomIndex, 1).Value

    ' 診断情報を表示
    MsgBox "最大の行数: " & MaxRow & vbCrLf & "ランダムに選択されたインデックス: " & RandomIndex & vbCrLf & "取得されたテキスト: " & GetRandomText

End Function

基本的なコードの構造

示されたVBAコードは、大きく分けて二つの部分から構成されています。一つはメインのサブルーチン Sub FillAdjacentCellFromExternalFile()、もう一つは補助的な関数 Function GetRandomText()です。

Sub FillAdjacentCellFromExternalFile()

このサブルーチンの役割は、外部ファイルを開いて特定の範囲（この例ではC1:C100）からランダムにテキストを取得し、それをアクティブなセルとその隣のセルに挿入することです。また、取得したデータをメッセージボックスで表示し、最後に外部ファイルを閉じます。

Function GetRandomText()

この関数は、指定された範囲からランダムにテキストを選び出し、その隣のセルのデータも取得する役割を担っています。ランダムなインデックスの生成にはRnd関数を使用し、関連する診断情報をメッセージボックスで表示します。

注意点と改善の余地

このコードを使用する際には、いくつかの注意点があります。まず、外部ファイルのパスが固定されているため、ファイルの場所が変わった場合はパスを更新する必要があります。また、エラーハンドリングが含まれていないため、ファイルが見つからない場合やデータ範囲が不適切な場合にはエラーが発生します。これらの点を改善することで、より堅牢で使いやすいコードにすることができます。