Excel VBA業務効率化

あなたのExcel定型業務をVBAで効率化、省力化をお手伝いします。

詳細はこちら

エクセルVBA練習帳、超初心者向けコード解説、リストボックスに連続したitemを追加するプロシージャ

エクセルVBA
この記事は約5分で読めます。

値を返すプロシージャ Functionを使う

データベースのイメージ

Functionその1、データベース範囲を一行で二次元配列に格納して返す

データ範囲を一行で配列に格納

Function ArrayMyData() As Variant
Dim max As Integer
‘データベース範囲のデータ行数取得
max = Range(“A11”).CurrentRegion.Rows.Count – 1
‘データの範囲を取得して返却(この場合、要素番号は1から始まる)
ArrayMyData = Range(Cells(12, “A”), Cells(11 + max, “D”)).Value
End Function

Functionその2、データベース範囲をFor~Nextで二次元配列に格納して返す

データ範囲をFor~Nextを使って配列に格納

Function ArrayMyData2() As Variant
Dim gymax As Integer
Dim retmax As Integer
Dim r As Integer
Dim i As Integer
Dim data As Variant

‘データベース範囲のデータ行列数取得
gymax = Range(“A11”).CurrentRegion.Rows.Count – 1
retmax = Range(“A11”).CurrentRegion.Columns.Count

‘配列の大きさを決定(要素番号を1からにする)
ReDim data(1 To gymax, 1 To retmax)

’配列 dataにデータベース範囲を格納
For  r = 1 To gymax  ’行の繰り返し

For  i = 1 To retmax  ’列の繰り返し

data(r,  i) = Cells(r + 11,  i).Value

Next

Next

‘返却
ArrayMyData2 = data
End Function

上記で作った二通りのFunctionプロシージャから値を取得する

データ範囲を一行で格納したFunctionから

’変数宣言(配列ではVariantを指定)
Dim itemgyou As Variant

’データ範囲を一行で格納したFunctionから返却された二次元データを取得
itemgyou = ArrayMyData

データ範囲をFor~Nextで格納したFunctionから

Dim arrydata As Variant

’データ範囲をFor~Nextで格納したFunctionから返却された二次元データを取得
arrydata = ArrayMyData2

Functionプロシージャから取得した二次元配列のデータを展開する

‘リストボックスのアイテムに追加
With ActiveSheet.ListBox1

.Clear

For  r = 1 To UBound(itemgyou)

.AddItem “ID=”  &  itemgyou(r,  1)  &  ”→”  &  itemgyou(r,  2)

Next

End With

この部分がリストにItemを追加しているコードになります
.AddItemID=” & itemgyou(r,  1) & “” & itemgyou(r,  2)
配列、itemgyouには下表のイメージでデータが格納されています

Forのrには1から順にデータの総行数まで入るので
文字列「ID=」に続けて配列の1行1列目のデータに更に文字列「」を挟んで配列の1行2列目のデータが合成されて.AddItemで追加していしています。そしてForで繰り返すたびにr行数を増加した配列値を格納するというイメージになります。

UBound(itemgyou)のUboundは引数にしている配列(itemgyou)の行数を返す関数です。VBAが標準で備えているFunctionです。
正確にはUBound(itemgyou,1)と書いて行数を取得し、UBound(itemgyou,2)と書くと配列の列数を取得します(第二引数を省略すると行数が返ります)
ここではForの繰り返す上限に配列の行数を指定しているので、配列の行数分だけ繰り返し処理が続くことになります

‘—————————————————–

‘コンボボックスのアイテムに追加

’こちらはコンボボックスコンテナーですが基本的にはリストを追加する方法はリストボックスと同様です。

With ActiveSheet.ComboBox1

.Clear

For  r = 1 To UBound(arrydata)

.AddItemID=”  &  arrydata(r,  1)  &  ““  &  arrydata(r,  3)

Next

.Value = .List(ListIndex)

End With

以上今回は二通りのFunctionプロシージャの使用例でした。一つの結果を表現、実現する方法が色々ありますがどのスタイルがいいのかわかりませんが色々やってみるのがいいと思います。

コード全文

ダウンロードは下記から

コメント

タイトルとURLをコピーしました