ＶＢＡ講座3
条件分岐の続きと繰り返しこの、講座3では、相当高度な技を説明します。
エクセルのシートのセルに、ほとんど式を入力しない方法です。
セルに式を入力すると、ユーザーの手違いで式が変化します。
とても多いのが、切り取り・貼り付けによるトラブルです。
エクセルでシステムを作る人は、必ずシートを保護して、式の入っているセルをロックします。
これによって、式の入っているセルは変更できないはずなのですが？？？
データーを入力するセルを切り取られると、式の入っているセルもエラーになります。
参照するセルが無くなるので当然です。参照セルを絶対参照にしても同じです。
皆さん、これには悩んでいます。
初心者はマウスを右クリックすると切り取りが一番上にありますから、どうしても使ってしまうのです。
切り取りって便利ですよね。データーを１００人分入れた時、行が一つずれていた！
「切り取り貼り付けで、修正完了・・・ラッキー」私かしこい！と普通の人は、やってしまいます。
コピー・貼り付けをして、重複した所をデリートキーで消してくれれば何の問題も無いのですが！
エクセルでシステムを作る人は、「絶対に”切り取り”はしないで下さい。」と言ってるんですが！
あとよくあるのが、他のシステムなどからデーターをコピー貼り付けする際に、
セルの書式ごと貼り付けたり、式を貼り付けたりするケースです。
この場合は、さすがに変だと思うらしくて、詳しい人に助けを求めます。
そして、”値の貼り付け”の助言を受けることになります。
私は、値の貼り付けのアイコンをツールバーに作ることを勧めています。
そして、コピーのアイコンの隣に表示させるようにして、安易に貼り付けアイコンを押さないように助言しています。
初心者は、「絶対しないで下さい！」ということをします。
また、シートやブックを保護し、パスワードをかけていても、解除して式を消してしまいます。
ＶＢＥを開くレベルの人にとってパスワードは無いに等しいです。
ＶＢＡのエディターを開く人はそれなりの知識があるのですが、
その人からパスワードを聞きだした中途半端なレベルの人や、
又聞きした初心者が、式を消したり、とにかくゴジャします。
私の例では160人用のシステムの保護を解除して、200人用のシステムにされました。
当然、最後の40人分は式も入っていませんから作動しませんし、
その他の部分も誤作動します。
また、保護を解除して勝手に式の入っている場所に数値を入れられました。
初心者にとっては、”見えないものは無い”ということのようです。
見た目が、「160人用から200人用になれば、ＯＫみたいな感じ」でしょうか？
とりあえず、切り取り・貼り付けの対策は完了しましたので、後で紹介します。
しかし、パスワードを解除されて式を壊される対策はありません。
ですので、エクセルのシートの中に出来るだけ式は書かないようにしましょう。
さすがに、ＶＢＡのプログラムをさわる初心者はいませんので！
というわけで、この例のシートのセルには、”差”の列以外は何の式も入っていません。
”差”の列はアンダーとトップの差を求める式つまり、”D6”には”=C6-B6”の式だけですから、消されてもすぐ修正できます。
女の子のバストのトップとアンダーのサイズから適切なブラジャーのサイズを求めるＶＢＡプログラムを作成するのですが、
これが出来れば、たいていのお仕事に応用できます。
上の図のアンダーとトップの値を入力し、カップ判定ボタンを押すと適切なサイズのカップが表示され、
ブラ判定ボタンを押すとブラのサイズが表示され、
判定消去ボタンを押すと判定が消去されるというものです。
Private Sub CommandButton1_Click()
Dim sa As Integer
Dim cap As Integer
Dim gyosu As Integer
gyosu = 6
sa = 4
cap = 5
With ActiveSheet
For gyosu = 6 To 14 Step 1
Select Case .Cells(gyosu, sa)
Case 24 To 27
.Cells(gyosu, cap) = "G"
Case 22 To 23
.Cells(gyosu, cap) = "F"
Case 19 To 21
.Cells(gyosu, cap) = "E"
Case 17 To 18
.Cells(gyosu, cap) = "D"
Case 15 To 16
.Cells(gyosu, cap) = "C"
Case 13 To 14
.Cells(gyosu, cap) = "B"
Case 8 To 12
.Cells(gyosu, cap) = "A"
Case 5 To 7
.Cells(gyosu, cap) = "AA"
Case Else
.Cells(gyosu, cap) = "不必要"
End Select
Next gyosu
End With
End Sub
では、説明します。
最初のPrivate Sub CommandButton1_Click()はカップ判定のボタンを押すイベントです。
Sheet1にコマンドボタンを貼り付け、右クリックしてコードを表示させます。
Sheetやユーザーフォームなどに記述する命令文をイベントプロシージャーといいます。
これは、必ずPrivate Sub から始まります。
”カップ判定”などの表示はキャプションで変更できますが、本当のボタン名は”CommandButton1”です。
次のDim sa As Integer
Dim cap As Integer
Dim gyosu As Integer
はいずれも変数です。
次は先の変数に値を与えています。いずれもセル番地の値の変数ですから、整数です。
だから、Integer型になります。
gyosu = 6 ・・・行を示す変数で、上の図の上から６番目のセルを示します。
sa = 4 ・・・差を示す変数でＤ列、つまり左から４番目のセルを示します。
cap = 5 ・・・カップの大きさを表示する列の変数でＥ列、左から５番目
With ActiveSheet
For gyosu = 6 To 14 Step 1
ここからが、条件分岐や繰り返しのプログラムです。
With ActiveSheet
Withは、以後のピリオドから始まる文でのWithから後のActiveSheetを省略することを意味します。
つまり、本当はActiveSheet Cells(gyosu, cap)と書くべきところを.Cells(gyosu, cap) ですませるわけです。
For gyosu = 6 To 14 Step 1・・・これは、gyosu という変数が6から14まで１づつ変化することを示しています。
初期値が6、終値が14で増加分が1です。
Select Case .Cells(gyosu, sa)
Case 24 To 27
.Cells(gyosu, cap) = "G"
Case 22 To 23
.Cells(gyosu, cap) = "F"
Case 19 To 21
.Cells(gyosu, cap) = "E"
Case 17 To 18
.Cells(gyosu, cap) = "D"
Case 15 To 16
.Cells(gyosu, cap) = "C"
Case 13 To 14
.Cells(gyosu, cap) = "B"
Case 8 To 12
.Cells(gyosu, cap) = "A"
Case 5 To 7
.Cells(gyosu, cap) = "AA"
Case Else
.Cells(gyosu, cap) = "不必要"
End Select
条件分岐を示しています。セレクトケース文です。
最初のSelect Case .Cells(gyosu, sa)は、gyosu（行数）初期値は6で、sa（列数）は4ですからD6のセルの値を判断します。
そして当てはまるケースの値を（Gから不必要までのどれか）E6のセルに返します。
次のCase 24 To 27は、ケースの値が24から27の場合（アンダーとトップの差）
.Cells(gyosu, cap) = "G"、つまりGの値（Gカップ）を返すということです。
以下Case 5 To 7
.Cells(gyosu, cap) = "AA"
まで同じように判断します。If文と違って上から順に判断するわけではなく、
いきなり、セルの値に対して該当する戻り値を返します。
Case Else
.Cells(gyosu, cap) = "不必要"
それまでの、どのケースにも当てはまらない場合の判断です。
差が5センチ未満の人が対象になります。”スポーツブラ”の方が良かったかも？
この、システムですと差が28センチ以上の人も"不必要"になりますね。
（そんな人は日本人には居ないという設定です。どうしても28センチ以上にこだわる人は
最初の行で28 To 90ぐらいを設定し”売ってません”にしたら良いと思います。）
Cellsは、セル番地を意味します。普通セル番地はRange(B1)というように示します。
しかし、今回のように変数で示す場合は、Cellsを使います。
この場合前が”行”を、後ろが”列”を意味します。
たとえば、Cells(gyosu, sa)で、変数gyosuが6で、変数saが4ならそれはD6のセルを示しています。
つまり、Range(D6)と同じ意味です。
このプログラムのように、セル番地を変数で表し、
さらにその変数の値が変化する場合はCellsを使わないと上手く作動しません。
Next gyosu
End With
End Sub
Nextで繰り返します。gyosuの値が6から始まり14になるまで繰り返して判定します。
つまり、D6のイソメさんの差からD14のユウコさんまでの差を判定してカップの値を決定するわけです。
カップの値を表示する場所は.Cells(gyosu, cap)で、gyosuは6から14まで変動し、capは5のまま変化しません。
5はE番地ですから、E6からE14までに順に表示します。
また、End Withは省略の終了を意味します。
End Subは、このプロシージャの終了を意味します。
以上で、For Next文とSelect Case文を一気に説明しました。
次に、シートに二つ目のコマンドボタン2（サイズ判定）を貼り付けます。
そして、以下のコードを書きます。
もう、説明は不要ですよね。
Private Sub CommandButton2_Click()
Dim und As Integer
Dim bura As Integer
Dim gyosu As Integer
gyosu = 6
und = 2
bura = 6
With ActiveSheet
For gyosu = 6 To 14 Step 1
Select Case .Cells(gyosu, und)
Case 63 To 67
.Cells(gyosu, bura) = "65"
Case 68 To 72
.Cells(gyosu, bura) = "70"
Case 73 To 77
.Cells(gyosu, bura) = "75"
Case 78 To 82
.Cells(gyosu, bura) = "80"
Case 83 To 87
.Cells(gyosu, bura) = "85"
Case Else
.Cells(gyosu, bura) = "該当ブラなし"
End Select
Next gyosu
End With
End Sub
次に、シートに三つ目のコマンドボタン（判定消去）を貼り付けます。
そして、以下のコードを書きます。
これは、三つ目のボタンを押すと、Kesuyo（消すよ）というプロシージャーを実行するという意味です。
Private Sub CommandButton3_Click()
Kesuyo
End Sub
これは、Kesuyo（消すよ）というプロシージャーの中身です。
このプロシージャーは、今までと違って標準モジュールの中に書きます。
Sub Kesuyo()
Range("E6:F14").Select
Selection.ClearContents
Range("A1").Select
End Sub
Range("E6:F14").Select・・・E6からF14までのセルを選択する。
Selection.ClearContents・・・選択したセルのコンテンツ（中身）をクリアー（消す）する。
Range("A1").Select・・・A1のセルを選択、これは無くてもよいステートメントです。
カーソルを左端の一番上に戻す命令です。
切取り・貼り付けに対する対策
以下のコードは、初心者の切り取り・貼り付けに対する対策です。
プロジェクトエクスプローラーのワークブックをクリックして以下のコードを書きます。
Private Sub Workbook_Deactivate()
Call ahoCut
End Sub
Private Sub Workbook_Deactivate()・・・ワークブックが、アクティブでなくなった時
Call ahoCutaho・・・ahoCut（アホカット）を実行する。
まあ、いないと思うのですが、切取りをした内容を、他のワークブックやアプリに貼り付けされるのを防ぐためです。
次にワークシートをクリックして以下のコードを書きます。
すべてのシートでこれを繰り返します。これで同じシートの中や他のシートに貼り付けされるのを防ぎます。
Private Sub Worksheet_Deactivate()
Call ahoCut
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ahoCut
End Sub
Private Sub Worksheet_Deactivate()・・・ワークシートが、アクティブでなくなった時
Call ahoCut・・・ahoCut（アホカット）を実行する。
という命令文で、他のシートへの貼り付けを防止します。
Worksheet_SelectionChange・・・ワークシートの内容が変更されたら、
Call ahoCut・・・ahoCut（アホカット）を呼び出すという意味です。
これにより、同じシートの他の場所への貼り付けを防止します。
ahoCutというのは、私が勝手に付けた”アホの切取り”という意味で、もちろんどんな名前でもかまいません。
ahoCutという命令文の中身です。これは標準モジュールに書きます。
Sub ahoCut()
If Application.CutCopyMode = xlCut Then
Application.CutCopyMode = False
UserForm1.Show
End If
End Sub
切り取りをした時、その命令を無効にして、ユーザーフォーム1を呼び出すという内容です
If Application.CutCopyMode = xlCut Then・・・If（もし）、このApplication（アプリケーション）で、
xlCut Then（切取りが実行されたら）
Application.CutCopyMode = False・・・（切取り貼り付けモードを無効にする）という意味です。
xlCutが切取りを意味します。
つまり、切取りが行われたら、貼り付けをされる前に無効にするという命令です。
このプロシージャーはxlCut（切取り）がThen（された）でなければ作動しません。
つまり、コピー・貼り付けは出来るということです。・・・これ大切です。コピー貼り付けは必要ですから！
UserForm1.Show・・・そして、ユーザーフォーム1を呼び出します。
私は、以下のようなユーザーフォーム1を作りました。
UserForm1の中のコマンドボタン1（元に戻る）を押した時の命令文です。
Private Sub CommandButton1_Click()
Unload Me
End Sub
Unload Meで、UserForm1は消えます。
UserForm1.Showの代わりに、メッセージボックスでも良いです。その場合は以下の文が良いでしょう。
このメッセージボックスの場合は”OK”ボタンを押すと、ボックスは消えます。
Sub ahoCut()
If Application.CutCopyMode = xlCut Then
Application.CutCopyMode = False
MsgBox"切取り禁止です",vbCritical,"切取り禁止"
End If
End Sub
最初に戻る