■ ワークシートを簡易データベースとして使おう
さて、せっかく最終行、最終列を取得する方法もわかったことですし、今度はワークシートを簡易データベース的に使ってみましょう。
方法はいろいろとありますが…できるだけ簡単で汎用性の高いものをご紹介したいと思います。
まずワークシートを一つ追加してください。
そして追加したワークシートの名前を"SyainMST"に変更しましょう。
シートの追加と名前の変更は大丈夫ですよね。挿入＞ワークシートでシートの追加、タブをダブルクリックして名前を変更です。
セルA1、B1、C1、D1、E1にSyainID、SyainNAME、Kinzoku、Syozoku、Yakusyokuと それぞれ入力してください。
実際の社員マスタはもっとたくさんの項目があると思いますが、ここはあくまでVBAの勉強用です。このくらいの項目数で十分でしょう。
５行くらいテストデータを入れてください。こんな感じです。
|
1001 |
鈴木一郎 |
20 |
商品部 |
課長 |
|
1002 |
小野真弓 |
5 |
経理部 |
一般 |
|
1003 |
北野 武 |
35 |
商品部 |
部長 |
|
… |
|
|
|
|
データはどんなものでも結構です。あとでわかりやすくするために身近な人をデータにしたほうがいいでしょう。
データのほうの準備は出来ましたか？
では早速、VBAでプログラムしてみましょう。プログラムの動作はSheet1のA1セルに入っている社員IDのデータを拾ってきてA2、A3、A4、A5セルに名前、勤務年数、所属、役職を表示させるものとします。
Public Sub test()
Dim i As Long
Dim WrkRow As Long
Dim WrkCol As Long
Dim WrkRange As Variant
With Sheets("SyainMST")
WrkRow = .Cells(Rows.Count, 1).End(xlUp).Row
WrkCol = .Cells(1, Columns.Count).End(xlToLeft).Column
WrkRange = .Range("A1").Resize(WrkRow, WrkCol)
End With
For i = 1 To WrkRow
If WrkRange(i, 1) = Range("A1") Then
Range("A2") = WrkRange(i, 2)
Range("A3") = WrkRange(i, 3)
Range("A4") = WrkRange(i, 4)
Range("A5") = WrkRange(i, 5)
End If
Next i
End Sub
うん。なかなかいいコードです。Sheet1のセルA1に試しに社員IDの1001を入力、実行させてみましょう。
が表示されたと思います。パチパチパチ。
さて、コードの行っている処理を順に説明してみましょう。まず５行目のDim WrkRange As Variant ですが、Variant型というのは初めて出てきましたね。
基本のデータ型の４つにない型です。
Variantは何でも入るとても便利な型です。型宣言をしなければ自動的にこの型が割り当てられます。まずWrkRangeをこの何でも入る便利な型で宣言します。
次の行のWithですが、Sheets("SyainMST")を指定しています。データの入っているシートSyainMSTはプログラムを実行させるSheet1とは別のシートです。別のシートからデータを拾ってくるときは明示的にシート名を指定してあげる必要があります。
WrkRow = Sheets("SyainMST").Cells(Rows.Count, 1).End(xlUp).Row
WrkCol = Sheets("SyainMST").Cells(1, Columns.Count).End(xlToLeft).Column
WrkRange = Sheets("SyainMST").Range("A1").Resize(WrkRow, WrkCol)
と書いてもいいのですが、ちょっと見にくいです。Withをつかってまとめてやると、
With Sheets("SyainMST")
WrkRow = .Cells(Rows.Count, 1).End(xlUp).Row
WrkCol = .Cells(1, Columns.Count).End(xlToLeft).Column
WrkRange = .Range("A1").Resize(WrkRow, WrkCol)
End With
いくぶん見やすくなりましたね。
WrkRow、WrkColに代入されるのはSyainMSTのデータが入力されている範囲の最終行と最終列です。
WrkRange = .Range("A1").Resize(WrkRow, WrkCol)
の部分で何でも入る便利な型（Variant）のWrkRangeにセルA1から（データのある）最終行、最終列のセルまでを直接代入しています。
Resize(行,列)これでRange("A1")から最終行、最終列の範囲までを指定しています。WrkRangeには指定した範囲内のデータが二次元配列として格納されます。
おお…配列の説明がまだでしたね。大丈夫です、全然難しくありませんから。
一階建ての平屋のアパートを連想してください。部屋が1号室から5号室まで5部屋あります。これが一次元配列です。
Dim MyApartment(4) As String
こんなふうに宣言します。この変数にはMyApartment(0)からMyApartment(4)まで5個の異なる文字列を保持できます。
大家さんが、がんばって二階建てにしたとします。部屋は101号室から205号室まで10部屋になりました。これが二次元配列です。
Dim MyApartment(1,4) As String
こんな感じになります。配列は0から数えるので(0〜1,0〜4)で10個のデータが保持できます。ちょっと変な感じですけど。
三次元配列はさらに同じアパートを別棟で建てたと考えてください、詳細は省略します。配列は大体こんな感じです。
※より詳しい配列の解説はこちらに記述していますので参照してください。
配列（動的配列 静的配列）の中身を一気に複写
さて、WrkRangeにセル範囲が代入された時点でWrkRangeはWrkRange(行,列)の二次元配列となります。（ただし配列の開始値は0ではなく1です）
WrkRangeの配列の中身はこんな感じです。
WrkRange(1,1)="SyainID"
WrkRange(1,2)="SyainNAME"
WrkRange(1,3)="Kinzoku"
WrkRange(1,4)="Syozoku"
WrkRange(1,5)="Yakusyoku"
WrkRange(2,1)="1001"
WrkRange(2,2)="鈴木一郎"
…
あとは簡単ですね。For文でループさせ社員IDがセルA1と同一のときにそのほかのデータをセルA2〜A5に出力させてます。
ふう。でもこれならVLOOKUP関数を使ったほうが早そう…と思ってませんか？あなた。
それにFor文でデータの頭から該当IDを探しに行くのも非効率ですよね。IDで検索させて該当レコードだけを抜き出すほうが手っ取り早いと思うでしょう。
全くもってその通りです。これはこういう使い方をするためのコードではありません。
このコードの便利なところはVariantの配列をそのまま一気にセル領域に出力できるところにあります。
つまり、あるシートの大量のデータを一気に取得してプログラム内で更新処理し、元の場所のデータを一気に置き換える。このような使い方をしてこそ威力を発揮します。
たとえば上のコード、こんな風にも記述できます。
Public Sub test()
Dim i As Long
Dim WrkRange As Variant
WrkRange = Sheets("SyainMST").UsedRange
For i = 1 To UBound(WrkRange)
If WrkRange(i, 1) = Range("A1") Then
Range("A2") = WrkRange(i, 2)
Range("A3") = WrkRange(i, 3)
Range("A4") = WrkRange(i, 4)
Range("A5") = WrkRange(i, 5)
End If
Next i
End Sub
ずいぶんあっさりしちゃいましたね。特筆すべきは４行目のUsedRangeプロパティです。これはシート内で使用されている全セル範囲を取得します。
先ほどは最終行をカウンタの上限に設定していましたが…
For i = 1 To UBound(WrkRange)
最終行だけ取得するのはバカらしいのでUBound関数を使って最終レコードを取得しちゃいましょう。
インデックスを含むデータが10行あればUBound(WrkRange)は10を返します。10は最終行と同じ値ですね。
ループの最中に各項目の値を変更（例えば勤続年数を増分）し、
Sheets("SyainMST").UsedRange = WrkRange
とすれば、変更された値が一気に元の場所に書き込まれます。
勤続年数の年度更新処理など、使い方はいくらでもあり実に汎用性の高いテクニックです。
この方法を突き進めていきますと、ワークシートでもかなりデータベース的な使い方が可能になります。
※さらにワークシートのデータベース的利用法を極めたい方はこちら！
データベースへ接続してみよう！ワークシートへの接続
|
