■ 名前を定義しよう
ExcelVBAで業務システムを考えるとき、まず２つの手法が考えられます。
ひとつはVBやAccessと同じように入力フォームを作成し、そこからデータの入力・訂正・削除をさせる方法。
もう一つはワークシートをフォームのように使用する方法です。
フォームの作成は初心者には若干敷居が高いきらいがあります。まずはいつも使いなれているワークシートを使うことを考えたほうがフレンドリーです。
ということで、フォームの前にワークシートを使ったコードをもう少し掘り下げてみたいと思います。
フォームを利用してプログラミングしたことのある人ならよくご存じだと思いますが、フォームにコントロールを貼り付けるとデフォルトで名前が付いてきます。
たとえばテキストボックスを貼り付けると最初のオブジェクト名は"TextBox1"になります。
実は"Book1.xls"も"Sheet1"もエクセルがデフォルトでつけた名前です。
これらはユーザーが好きなように変更できますよね。
セルの名前ももともとつけられている"A1"から変更して好きな名前が付けられます。
では早速、前回と同じtest.xlsを起動し、Shhet1のセル"A1"を選択して名前ボックスに"あああ"とでも入力してください。名前ボックスの場所はいいですか？
数式バーのすぐ左隣ですよ。
"A1"の名前を"あああ"に変更したら、どこか他のセルを選択してください。名前ボックスの横にある▼をクリックすると今設定した"あああ"があると思います。クリックしてみてください。
"A1"の場所にアクティブセルが移動しましたね。これでセル"A1"はVBAからでも"あああ"でアクセスできます。
ただ"あああ"では訳がわかりませんので"syain_id"に変更しましょう。さあやってみてください。
うまくいきました？
うまくいかなかった人、名前ボックスから変更しようとしませんでした？
変更するときは名前ボックスではなく「名前の定義」ウィンドウを使います。
挿入＞名前＞定義 を選択してください。名前の定義ウィンドウが表示されましたか？
いま定義されている"あああ"を削除してください。そして"syain_id"を追加し、参照範囲を=Sheet1!$A$1にしてください。
おや？ただの=$A$1ではなくSheet1!がついていますね。実はここでシート名を明示してやることで、同じ名前を別々のシートでも使用できます。Sheet1のsyain_id、Sheet2のsyain_id、という具合です。
さて、下準備もオッケーです。早速、VBAのほうにまいりましょう。
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("syain_id") Then
Range("syain_id").Offset(1, 0) = WrkRange(i, 2)
Range("syain_id").Offset(2, 0) = WrkRange(i, 3)
Range("syain_id").Offset(3, 0) = WrkRange(i, 4)
Range("syain_id").Offset(4, 0) = WrkRange(i, 5)
End If
Next i
End Sub
ありゃ。前回よりごちゃごちゃしてしまいましたね。でもこれは前回よりいいコードです。
どこが良いのでしょう？
実はマジックナンバーが５つも減っています。お、マジックナンバー！？いつから野球の話になったんだ？って違いますよ。
プログラムのマジックナンバーとは例えば…
SyoriCode = 10
SyoriCode = 20
SyoriCode = 30
…
のように、製作者にしかわからない10、20、30のような数字です。これがどんな意味を持っているのか、コメントを書いておかないと後から他の人が読み取るのは大変な作業ですし、作った人だって忘れてしまいます。
SyoriCode = 10 'SyoriCodeを引数とする関数に10（未処理）のラベルを渡す
こんな感じです。おっと、コメントの付け方もまだでしたね。簡単ですからここで覚えてください。
頭に ' をつけます。ね、簡単でしょ。
で、それでは可読性が悪いので普通は定数を使います。
以下はLong型の定数として定義して10の値をセットしています。
Const CstMisyori As Long = 10
SyoriCode = CstMisyori
これなら一発で、未処理コードの定数を渡しているのが理解できます。
※定数についてのより詳しい解説はこちらを参照してください。
定数と列挙型を活用しよう
さて、先ほどのコードに戻りましょう。
If WrkRange(i, 1) = Range("A1") Then
これでは"A1"がなんのセルかシートを確認しないとわかりません。そのあとのA2〜A5も同様です。
Range("syain_id") これなら、一目瞭然です。社員IDを入力するセルだとわかります。
そのあとのRange("syain_id").Offset(1, 0) もセル"syain_id"の一行下のセルだとすぐわかります。
Offsetプロパティは基準セル（この場合Range("syain_id")）より（行,列）方向に指定した範囲のセルの値を取得します。
基準セルが"A1"ならOffset(1,0)は"A2"のことを指します。
さて、ここでもうひとつ用語を覚えましょう。Range("○○○")のように絶対的なセル位置を参照することを「絶対参照」といい、Offset(○,○)のように相対的な位置の参照を「相対参照」といいます。
エクセルの場合は、この区別は特に重要な意味を持ちます。言葉の意味だけでも今覚えてください。
もうひとつの、使用例をご紹介しておきましょう。
Sheet1のA2〜A5を選択し、"hyoji_all"と名前を付けてください。
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("syain_id") Then
Range("syain_id").Offset(1, 0) = WrkRange(i, 2)
Range("syain_id").Offset(2, 0) = WrkRange(i, 3)
Range("syain_id").Offset(3, 0) = WrkRange(i, 4)
Range("syain_id").Offset(4, 0) = WrkRange(i, 5)
Exit For
End If
Range("hyoji_all").ClearContents
Next i
End Sub
先ほどまでのコードでは、セル"syain_id"に存在しない社員IDが入力されても、前回表示されていたデータがそのまま残ってしまいました。
そこでデータが存在しないときにクリアする処理を追加しました。11行目にExit For が追加されているのがわかりますか？処理がここに来るときにはすでに社員IDが見つかった場合です。それ以降のループは不要ですので、この時点でループを抜けます。For文のループですのでExit Forです。Do文のときはExit Do、Subから抜けるときはExit Subです。
で、一度もこのExit Forのところに処理が来なかったときは…配列の中に社員IDが見つからなかったんですね。
その時は先ほど定義したRange("hyoji_all")をクリアします。ClearContentsメソッドを使います。
メソッドはオブジェクトに対する命令です。よく意味がわからなくても大丈夫です。メソッド＝命令、と今は覚えてください。
なぜこんなことをしたのかというと、定義する名前が単独のセルに対してだけでないことを理解してほしかったからです。
複数のセル範囲に名前をつけ、VBAから制御することができます。ちょっとすごいでしょ。
ちなみにセル範囲は連続している必要はありません。飛び石のように選択して定義することも可能です。
今回はセルに名前をつけるだけでしたが、実はシート上に配置できるオブジェクトにはみな名前をつけVBAから制御することが可能です。
このことは非常に重要な事ですので、ぜひとも理解しておいてください。
※名前付けルールを極めたい人はこちら！
識別子がよいコードを作る
|
