【VBA】VBAでワードリストからデータ作成

こんにちは！Excelで事前に用意したワードリストをランダムに選択し新たにデータリストを作成するためのＶＢＡコードを紹介します。

使用するワードリスト
作成するデータ数
ＶＢＡの実行結果
コード解説★★★（コピーはここ）★★★
使用している技術
まとめ

使用するワードリスト

まず使用するワードリストについて解説します。
ワードリストの構成
ワードリストは「候補」のワークシート上に、「№」「品番」「型番」「産地」「価格」という構成となっており、今回はデータを１０行分作成しました。

作成するデータ数

ワードリストから作成するデータの数は下図の赤枠で指定しています。

ＶＢＡの実行結果

ＶＢＡを実行した結果下図のような結果を得る事が出来ます。この組合せは実行の都度ランダムで違う結果を得ることが出来ます。

コード解説★★★（コピーはここ）★★★

Sub RANDAM()

' ワークシート名とセル範囲の設定
' ワードリストのワークシート名
Const WordSheetName As String = "候補"
' 抽選結果を出力するワークシート名
Const ResultSheetName As String = "結果"
' ワードリストの開始行
Const StartRow As Long = 5
' ワードリストの開始列
Const StartCol As Long = 3
' 作成するデータ数を指定するセル
Const DataCountCell As String = "D2"
' 抽選結果を出力する開始セル
Const ResultStartCell As String = "C3"

' 変数宣言
Dim LAST_C As Long, LAST_R As Long, END_YYY As Long
Dim i As Long, j As Long, randomIndex As Long
Dim WORD() As Variant, RESULT() As Variant
Dim wsWord As Worksheet, wsResult As Worksheet

' ワークシートオブジェクトの設定
Set wsWord = Worksheets(WordSheetName)
Set wsResult = Worksheets(ResultSheetName)

' ワードリストの列数と行数を取得
With wsWord
  LAST_C = .Cells(StartRow, StartCol).End(xlToRight).Column - StartCol + 1
  LAST_R = .Cells(StartRow, StartCol).End(xlDown).Row - StartRow + 1
  ' 作成するデータ数を取得
  END_YYY = .Range(DataCountCell).Value
  ' 候補のワードを配列に格納
  WORD = .Range(.Cells(StartRow, StartCol), .Cells(StartRow + LAST_R - 1, StartCol + LAST_C - 1)).Value
End With

' 配列のサイズを候補表のサイズに合わせる
ReDim RESULT(1 To END_YYY, 1 To LAST_C)

' データ種類ごとの抽選を実施
For i = 1 To END_YYY
  For j = 1 To LAST_C
    Do
      ' ランダムなインデックスを生成
      randomIndex = Int(Rnd() * LAST_R) + 1
      ' 値がない場合は再抽選
    Loop While IsEmpty(WORD(randomIndex, j))
    ' 抽選結果をRESULT配列に格納
    RESULT(i, j) = WORD(randomIndex, j)
  Next j
Next i

' 抽選結果を「結果」シートの指定位置に出力
wsResult.Range(ResultStartCell).Resize(END_YYY, LAST_C).Value = RESULT
End Sub

Sub RANDAM()

' ワークシート名とセル範囲の設定

' ワードリストのワークシート名

Const WordSheetName As String = "候補"

' 抽選結果を出力するワークシート名

Const ResultSheetName As String = "結果"

' ワードリストの開始行

Const StartRow As Long = 5

' ワードリストの開始列

Const StartCol As Long = 3

' 作成するデータ数を指定するセル

Const DataCountCell As String = "D2"

' 抽選結果を出力する開始セル

Const ResultStartCell As String = "C3"

' 変数宣言

Dim LAST_C As Long, LAST_R As Long, END_YYY As Long

Dim i As Long, j As Long, randomIndex As Long

Dim WORD() As Variant, RESULT() As Variant

Dim wsWord As Worksheet, wsResult As Worksheet

' ワークシートオブジェクトの設定

Set wsWord = Worksheets(WordSheetName)

Set wsResult = Worksheets(ResultSheetName)

' ワードリストの列数と行数を取得

With wsWord

LAST_C = .Cells(StartRow, StartCol).End(xlToRight).Column - StartCol + 1

LAST_R = .Cells(StartRow, StartCol).End(xlDown).Row - StartRow + 1

' 作成するデータ数を取得

END_YYY = .Range(DataCountCell).Value

' 候補のワードを配列に格納

WORD = .Range(.Cells(StartRow, StartCol), .Cells(StartRow + LAST_R - 1, StartCol + LAST_C - 1)).Value

End With

' 配列のサイズを候補表のサイズに合わせる

ReDim RESULT(1 To END_YYY, 1 To LAST_C)

' データ種類ごとの抽選を実施

For i = 1 To END_YYY

For j = 1 To LAST_C

' ランダムなインデックスを生成

randomIndex = Int(Rnd() * LAST_R) + 1

' 値がない場合は再抽選

Loop While IsEmpty(WORD(randomIndex, j))

' 抽選結果をRESULT配列に格納

RESULT(i, j) = WORD(randomIndex, j)

Next j

Next i

' 抽選結果を「結果」シートの指定位置に出力

wsResult.Range(ResultStartCell).Resize(END_YYY, LAST_C).Value = RESULT

End Sub

解説

今回のコードは大きく分けて下記の７つに分かれています。

ユーザー設定項目
変数の宣言
ワークシートオブジェクトの設定
実行条件の取得
配列のサイズ調整
データリストの作成

データリストの格納ではそれぞれ順番に説明していきます。

ユーザー設定項目

Sub RANDAM()
Const WordSheetName As String = "候補"
Const ResultSheetName As String = "結果"
Const StartRow As Long = 5
Const StartCol As Long = 3
Const DataCountCell As String = "D2"
Const ResultStartCell As String = "C3"

Sub RANDAM()

Const WordSheetName As String = "候補"

Const ResultSheetName As String = "結果"

Const StartRow As Long = 5

Const StartCol As Long = 3

Const DataCountCell As String = "D2"

Const ResultStartCell As String = "C3"

ここではこのＶＢＡで使用するワークシートの名称や表の開始位置を指定しています。コードをコピーして使用する場合にはこの部分を自身のExcelに合わせて変更する事で利用できます。

変数の宣言

Dim LAST_C As Long, LAST_R As Long, END_YYY As Long
Dim i As Long, j As Long, randomIndex As Long
Dim WORD() As Variant, RESULT() As Variant
Dim wsWord As Worksheet, wsResult As Worksheet

Dim LAST_C As Long, LAST_R As Long, END_YYY As Long

Dim i As Long, j As Long, randomIndex As Long

Dim WORD() As Variant, RESULT() As Variant

Dim wsWord As Worksheet, wsResult As Worksheet

ここではＶＢＡ内で使用する変数や配列、オブジェクトを宣言します。

ワークシートオブジェクトの設定

Set wsWord = Worksheets(WordSheetName)
Set wsResult = Worksheets(ResultSheetName)

1 2	Set wsWord = Worksheets(WordSheetName) Set wsResult = Worksheets(ResultSheetName)

ここでは変数に事前に設定したワークシートの名称を入力しています。

実行条件の取得

With wsWord
　　LAST_C = .Cells(StartRow, StartCol).End(xlToRight).Column - StartCol + 1
　　LAST_R = .Cells(StartRow, StartCol).End(xlDown).Row - StartRow + 1
　　END_YYY = .Range(DataCountCell).Value
　　WORD = .Range(.Cells(StartRow, StartCol), .Cells(StartRow + LAST_R - 1, StartCol + LAST_C - 1)).Value
End With

With wsWord

　　LAST_C = .Cells(StartRow, StartCol).End(xlToRight).Column - StartCol + 1

　　LAST_R = .Cells(StartRow, StartCol).End(xlDown).Row - StartRow + 1

　　END_YYY = .Range(DataCountCell).Value

　　WORD = .Range(.Cells(StartRow, StartCol), .Cells(StartRow + LAST_R - 1, StartCol + LAST_C - 1)).Value

End With

ここでは「wsWord」に指定したワークシートからワードリスト表の行列数や作成するデータ数、ワードリストのワードデータを配列に格納しています。

配列のサイズ調整

ReDim RESULT(1 To END_YYY, 1 To LAST_C)

1	ReDim RESULT(1 To END_YYY, 1 To LAST_C)

ここでは抽選結果を格納する配列 RESULT のサイズを先ほど取得した作成するデータ数に合わせて動的に調整しています。

データリストの作成

For i = 1 To END_YYY
　　For j = 1 To LAST_C
　　　　Do
　　　　　　randomIndex = Int(Rnd() * LAST_R) + 1
　　　　Loop While IsEmpty(WORD(randomIndex, j))
　　　　RESULT(i, j) = WORD(randomIndex, j)
　　Next j
Next i

For i = 1 To END_YYY

　　For j = 1 To LAST_C

　　　　　　randomIndex = Int(Rnd() * LAST_R) + 1

　　　　Loop While IsEmpty(WORD(randomIndex, j))

　　　　RESULT(i, j) = WORD(randomIndex, j)

　　Next j

Next i

ここでは繰り返し処理を使用してワードリストの中からランダムに言葉を抽選してデータのレコードを作成していきます。

データリストの格納

wsResult.Range("C3").Resize(END_YYY, LAST_C).Value = RESULT
End Sub

1 2	wsResult.Range("C3").Resize(END_YYY, LAST_C).Value = RESULT End Sub

ここではランダム抽出にて作成したデータレコードを事前に設定したワークシートに出力しています。

使用している技術

基本：FOR－NEXT、二次元配列、ＲＮＤ、Ｒｏｗ、Ｃｏｌｕｍｎ

まとめ

この記事では、Excelのワードリストからランダムにデータリストを生成するVBAコードを紹介しました。このコードは、WORD 配列からランダムに言葉を選び、RESULT 配列に格納して、新しいデータリストを作成するものです。処理の流れとしては、まず必要な変数と配列を宣言し、次にワークシートからデータを取得。続いて、配列のサイズを設定し、ランダムに言葉を選びながらデータリストを作成します。最後に、そのデータを指定したシートに出力します。基本的なVBA技術を用いたこのコードを使えば、効率よくデータリストを作成できます。