2016年5月4日水曜日

エクセルにフォームをつける Part II (01)- vbaプログラミング:[次行][前行]をつける、変数を使う

フォームの機能を増やす(その1) [次行][前行]をつける、変数を使う。

※前回の内容は→ Part I (04)です。
前回終了時点のサンプルファイルはここにあります→ 右クリックでここからダウンロードできます(ファイル名sample04.xlms)

PartIの、VBAの規則(文法)は2つでした。
◆ プログラムは、Subで始まりEnd Subで終わるプロシージャの中にまとめて書く。
◆ セルやフォーム上のオブジェクトの書き方は
・シート上のセルは、WorkSheet("シート名").Cells(行,列)で表す。
・フォーム上のオブジェクトは、オブジェクト名をつけて使う。

PartIIでは、プログラム特有の『変数』『ループ構文』『コメント』などを使って、作りこんでいきます。


今回(Part2の第1回)は、「次へ」「前へ」ボタンをつけて、行を移動できるようにします。

このときに『変数』という考え方を使います。


(II-1-1)始める前の、準備  (可読性のための、オブジェクト名の変更・プログラムの順番、など)--------

すすめる前に「後で読みやすいようにする」目的で、
オブジェクト名を変更し・プログラムの順番を変え・コメントを加えて、コードを整えます。

1) sample04.xlmsを開いて、Visual Basic Editorを立ち上げてください。
  1. [開発] タブをクリック
  2. リボン左端の「Visual Basic」をクリック
2) PartIで作ってきた、オブジェクト(フォームやシートなど)を確認して、オブジェクト名を変更します。
 ・ 標準モジュールの「Module1」 のオブジェクト名を「menu」に変えます。
  (ワークシート上のボタンをクリックしたときに、実行するプログラムは、ここに書くことにします。)

 201_01
3) ユーザフォーム editDataForm の、プログラムを確認して、順番を変更します。
 ・ コードの表示方法 … プロジェクトエクスプローラの 「フォーム」フォルダの「editDataForm」をクリックして、
   [F7]を押す か、  右クリックして「コードの表示」をクリックして、表示します。
   現在のコードは、下記のようになっています。
Private Sub UserForm_Initialize()

'    TextBox1.Value = Worksheets("Sheet1").Cells(5, 1).Value
'    TextBox2.Value = Worksheets("Sheet1").Cells(5, 2).Value
'    TextBox3.Value = Worksheets("Sheet1").Cells(5, 3).Value
'    TextBox4.Value = Worksheets("Sheet1").Cells(5, 4).Value
'    TextBox5.Value = Worksheets("Sheet1").Cells(5, 5).Value
'    TextBox6.Value = Worksheets("Sheet1").Cells(5, 6).Value
    
    TextBox1.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 1).Value
    TextBox2.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 2).Value
    TextBox3.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 3).Value
    TextBox4.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 4).Value
    TextBox5.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 5).Value
    TextBox6.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 6).Value
    
End Sub


Private Sub CommandButton_write_Click()

    Worksheets("Sheet1").Cells(ActiveCell.Row, 1).Value = TextBox1.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 2).Value = TextBox2.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 3).Value = TextBox3.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 4).Value = TextBox4.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 5).Value = TextBox5.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 6).Value = TextBox6.Value

End Sub

Private Sub CommandButton1_Click()
    
    Unload editDataForm
    
End Sub

 [[ 説明 ]] 「' (アポストロフィ)」から始まる行は、コメント行です。その行は実行されません。
 [[ 操作 ]]  コメント行を削除して、プロシージャの順番を変え、各プロシージャにコメントを書いてください。
Private Sub UserForm_Initialize()
    'フォームの初期化
    TextBox1.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 1).Value
    TextBox2.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 2).Value
    TextBox3.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 3).Value
    TextBox4.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 4).Value
    TextBox5.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 5).Value
    TextBox6.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 6).Value
End Sub


Private Sub CommandButton_write_Click()
    'セルに書き込む
    Worksheets("Sheet1").Cells(ActiveCell.Row, 1).Value = TextBox1.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 2).Value = TextBox2.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 3).Value = TextBox3.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 4).Value = TextBox4.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 5).Value = TextBox5.Value
    Worksheets("Sheet1").Cells(ActiveCell.Row, 6).Value = TextBox6.Value
End Sub

Private Sub CommandButton1_Click()
    'フォームを閉じる
    Unload editDataForm
End Sub


[[ 操作 ]]  [閉じる]ボタン のオブジェクト名を変更して、コードを書き換えてください。
 
   「閉じる」とわかるように「CommandButton_close」と変えます。
   その後、上の「フォームを閉じる」部分を書き換えてください。
201_02
Private Sub CommandButton_close_Click()
    'フォームを閉じる
    Unload editDataForm
End Sub

4) シート名を変更する
 ・ Visual Basic Editor を閉じて、表に戻ってください。
 ・ Sheet1を 「data」 という名前に変えてください。
  プログラムからは、Worksheets("data") として操作します。
201_03

 ・ オブジェクト名を変えたので、それに合わせて、プログラムを書き換えます。
 Visual Basic Editorの、「フォームの初期化」「セルに書き込む」の2つのプロシージャ部分です。

 ( ここは練習なので、途中でオブジェクト名を変えました。実際は途中で変えることはやりません。書き換えがたいへんなので。)
 下記のように、 Worksheets("Sheet1") → WorkSheets("data") に変更します
Private Sub UserForm_Initialize()
    'フォームの初期化
    TextBox1.Value = Worksheets("data").Cells(ActiveCell.Row, 1).Value
    TextBox2.Value = Worksheets("data").Cells(ActiveCell.Row, 2).Value
    TextBox3.Value = Worksheets("data").Cells(ActiveCell.Row, 3).Value
    TextBox4.Value = Worksheets("data").Cells(ActiveCell.Row, 4).Value
    TextBox5.Value = Worksheets("data").Cells(ActiveCell.Row, 5).Value
    TextBox6.Value = Worksheets("data").Cells(ActiveCell.Row, 6).Value
End Sub


Private Sub CommandButton_write_Click()
    'セルに書き込む
    Worksheets("data").Cells(ActiveCell.Row, 1).Value = TextBox1.Value
    Worksheets("data").Cells(ActiveCell.Row, 2).Value = TextBox2.Value
    Worksheets("data").Cells(ActiveCell.Row, 3).Value = TextBox3.Value
    Worksheets("data").Cells(ActiveCell.Row, 4).Value = TextBox4.Value
    Worksheets("data").Cells(ActiveCell.Row, 5).Value = TextBox5.Value
    Worksheets("data").Cells(ActiveCell.Row, 6).Value = TextBox6.Value
End Sub
★ **** おまけ: 置換: 一気に置換する方法です ******
 1. [編集]-[置換]
 2. ダイアログボックスで、[検索する文字列](上)に Worksheets("Sheet1") 、[置換後の文字列](下)に WorkSheets("data") 。対象を「カレントモジュール」。 検索方法を「全体」。チェックボックスは全部OFF。と設定して
 3. [すべて置換] をクリック
 4. 終了メッセージがでたら [OK]
 5. ダイアログボックスを[キャンセル] で閉じる
 201_04_1
 201_04_2
 201_04_3
 201_04_4
 ************************************
これで、準備は完了です。


(II-1-2) [次へ] [前へ] ボタンを増やす --------

ユーザーフォームに、コマンドボタンを2つ増やしてください。
 ・ [次へ]ボタン … オブジェクト名「 CommandButton_next 」、Caption 「次へ」
 ・ [前へ]ボタン … オブジェクト名「 CommandButton_previous 」、Caption 「次へ」
 ※ オブジェクト名(半角英数字で)も、キャプション(日本語OK)も、 自由につけて構いません。
   ここでは、CommandButton_next、CommandButton_previous としました。
 ボタンの位置や色は、自由に作ってください。
201_04_5


(II-1-3) [次へ] [前へ] ボタンのコードを書く

ボタンをクリックするたびに、編集する行を移動させます。

[ 考え方 ]

編集する行番号を記録しておいて、[次へ] [前へ]ボタンで行番号を増やしたり減らしたりすればよい。

[ 手順 ]

[1] 編集フォームを呼び出す前に、クリックした行の番号を記録しておきます。
 [2] 編集フォームを呼び出して、記録した行番号のデータを表示します。
   → このとき、『変数』 を利用します。
 [3] [次へ] ボタンの、プロシージャを書きます。
 [4] [3]と同様に、[前へ] ボタンのプロシージャを書きます。



[ 操作 ]

[1] 編集する前に、クリックした行の番号を記録します。

ここでは、記録用のシートを用意して、そこに行番号を記録します。
1. <準備> シートを挿入して、 シート名を「 param 」 にします。
201_05

2. 行番号をワークシートparamのセル(2,1)に記録します。
  記録のタイミングは、編集フォームを表示する前です。
 コードは、「標準モジュール」フォルダの「menu」モジュールに書きます。
 ポイント
 ・ Sub edit_data() の中に記述
 ・ 行番号 … クリックした行 「ActiveCell.Row 」
 ・ paramシートのセル(2,1) … worksheets("param").cells(2,1)
現在のコード
Sub edit_data()

    editDataForm.Show

End Sub
書き加えます
Sub edit_data()
    '------------
    'クリックした行の番号を、ワークシートparamのセル(2,1)に記録
    Worksheets("param").Cells(2, 1).Value = ActiveCell.Row
    '------------
    'フォームを立ち上げる
    editDataForm.Show
End Sub 
※上枠のコードには、コメントを加えました。
-----確認してください -----
dataシートの6行目を選択 (どのセルでも可)
[データの編集] ボタンをクリックして、 何もせず フォームを[閉じる]
paramシートの、2行目1列のセルに 「6」 が記録されていることを確認
--------------------


[2] 編集フォームを書き換えて、記録した行番号のデータを表示します。
   → 記録した行番号を利用するために、『変数』 を使います。

 ポイント
 コードは、「フォーム」の「editDataForm」に書きます
 ・ Private Sub UserForm_Initialize() の中に記述
 ・ 行番号はparamシートのセル(2,1)に記録されています … worksheets("param").cells(2,1)
 ・ 行番号を格納する変数 … 今回は「pointRow」という変数名にします。型はLong。
   変数を使用するには「Dim pointRow As Long」と書きます(説明は後述)。
1. 記録した行番号を取得するための、変数を宣言します。(ここではpointRowとします)
Private Sub UserForm_Initialize()
    'フォームの初期化
    '------------
    '記録した行番号を利用するため、変数pointRowを準備し、paramシートから値を入力
    Dim pointRow As Long
    pointRow = Worksheets("param").Cells(2, 1)
    '------------
    'データの表示
    TextBox1.Value = Worksheets("data").Cells(ActiveCell.Row, 1).Value
    TextBox2.Value = Worksheets("data").Cells(ActiveCell.Row, 2).Value
    TextBox3.Value = Worksheets("data").Cells(ActiveCell.Row, 3).Value
    TextBox4.Value = Worksheets("data").Cells(ActiveCell.Row, 4).Value
    TextBox5.Value = Worksheets("data").Cells(ActiveCell.Row, 5).Value
    TextBox6.Value = Worksheets("data").Cells(ActiveCell.Row, 6).Value
End Sub
※「Dim pointRow As Long」について
   Dim ... 変数を定義するための宣言文句です。
   pointRow ... 変数名は自由につけてください。(原則は半角英数で。ここではpointRowとします)
   As Long ... 変数には「型」があります。Asの後ろに型を記述します。今回はLong型にしました。

2. データを表示する部分で、行番号を「ActiveCell.Row」から「pointRow」に書き換えます。
Private Sub UserForm_Initialize()
    'フォームの初期化
    '------------
    '記録した編集指定行を利用するため、変数pointRowを準備し、paramシートから値を入力
    Dim pointRow As Long
    pointRow = Worksheets("param").Cells(2, 1)
    '------------
    'pointRowのデータを表示
    TextBox1.Value = Worksheets("data").Cells(pointRow, 1).Value
    TextBox2.Value = Worksheets("data").Cells(pointRow, 2).Value
    TextBox3.Value = Worksheets("data").Cells(pointRow, 3).Value
    TextBox4.Value = Worksheets("data").Cells(pointRow, 4).Value
    TextBox5.Value = Worksheets("data").Cells(pointRow, 5).Value
    TextBox6.Value = Worksheets("data").Cells(pointRow, 6).Value
End Sub
-----確認してください -----
dataシートの6行目を選択(どのセルでも可)
[データの編集] ボタンをクリックして、
6行目のデータが、フォームに表示されていたらOKです。
--------------------
続いて、[次へ]ボタンを作成しましょう。


[3] [次へ] ボタンの、プロシージャを書きます。

 ポイント
 コードは、「フォーム」の「editDataForm」に書きます。
 ・ 新しいプロシージャ「Private Sub CommandButton_next_Click()」 を作ります。
 ・ <次行へ移動する処理>
   ・記録した行番号を取得して、1 加えます。
   ・ 1加えた現在行番号を、paramシートに記録し直します
   ・ dataシートのセルポインタも現在行に移動します
 ・ データをフォームに表示します。
1. コードは、「フォーム」の「editDataForm」に書きます。
 ・ 新しいプロシージャ「Private Sub CommandButton_next_Click()」 を作ります。
  (Private Sub UserForm_Initialize():フォームの初期化 の下に書きました。)
Private Sub CommandButton_next_Click()
    '次データボタン
End Sub
2.次の行へ移動する処理
   
  ・新しいプロシージャなので、新しく変数を作ります(pointRowとしました)。
   「Dim pointRow As Long」
   ※ [2]のPrivate Sub UserForm_Initialize()でも変数を作っていますが、プロシージャが変わったので使えません。新しく作ります。詳細説明は後述。

Private Sub CommandButton_next_Click()
    '次データボタン
    '------------
    '記録した行番号を取得するために、変数を作る
    Dim pointRow As Long
End Sub

  ・paramシートに記録した行番号を、変数pointRowに入れます。
  「pointRow = Worksheets("param").Cells(2, 1).Value」
Private Sub CommandButton_next_Click()
    '次データボタン
    '------------
    '記録した行番号を取得するために、変数を作る
    Dim pointRow As Long
    '記録した行番号を取得する
    pointRow = Worksheets("param").Cells(2, 1).Value
End Sub

  ・pointRowに入れた行番号を、1増やします。
  「pointRow = pointRow + 1」
  ※変わった式に見えますが、意味は「右辺の計算結果を、左辺に代入する」です。
  pointRowを1増やした値を、pointRowに再セットしています。
Private Sub CommandButton_next_Click()
    '次データボタン
    '------------
    '記録した行番号を取得するために、変数を作る
    Dim pointRow As Long
    '記録した行番号を取得する
    pointRow = Worksheets("param").Cells(2, 1).Value
    '行番号を、1 増やす
    pointRow = pointRow + 1
End Sub

  ・paramシートの記録も書き換えます。
   「Worksheets("param").Cells(2, 1).Value = pointRow」
Private Sub CommandButton_next_Click()
    '次データボタン
    '------------
    '記録した行番号を取得するために、変数を作る
    Dim pointRow As Long
    '記録した行番号を取得する
    pointRow = Worksheets("param").Cells(2, 1).Value
    '行番号を、1 増やす
    pointRow = pointRow + 1
    '------------
    '現在行番号を、paramシートに記録し直す
    Worksheets("param").Cells(2, 1).Value = pointRow
End Sub

  ・dataシートも、現在行に移動します。
   「Worksheets("data").Cells(pointRow, 1).Activate」
   ※「ワークシートdataのpointRowセルを、Activateする」と書きます
Private Sub CommandButton_next_Click()
    '次データボタン
    '------------
    '記録した行番号を取得するために、変数を作る
    Dim pointRow As Long
    '記録した行番号を取得する
    pointRow = Worksheets("param").Cells(2, 1).Value
    '行番号を、1 増やす
    pointRow = pointRow + 1
    '------------
    '現在行番号を、paramシートに記録し直す
    Worksheets("param").Cells(2, 1).Value = pointRow
    '------------
    'dataシートのセルポインタも現在行に移動する
    Worksheets("data").Cells(pointRow, 1).Activate
End Sub


3. フォームにデータを表示します。
 
 データの表示は、Private Sub UserForm_Initialize() のデータ表示部分と全くです。
Private Sub CommandButton_next_Click()
    '次データボタン
    '------------
    '記録した行番号を取得するために、変数を作る
    Dim pointRow As Long
    '記録した行番号を取得する
    pointRow = Worksheets("param").Cells(2, 1).Value
    '行番号を、1 増やす
    pointRow = pointRow + 1
    '------------
    '現在行番号を、paramシートに記録し直す
    Worksheets("param").Cells(2, 1).Value = pointRow
    '------------
    'dataシートのセルポインタも現在行に移動する
    Worksheets("data").Cells(pointRow, 1).Activate
    '------------
    'pointRowのデータを表示
    TextBox1.Value = Worksheets("data").Cells(pointRow, 1).Value
    TextBox2.Value = Worksheets("data").Cells(pointRow, 2).Value
    TextBox3.Value = Worksheets("data").Cells(pointRow, 3).Value
    TextBox4.Value = Worksheets("data").Cells(pointRow, 4).Value
    TextBox5.Value = Worksheets("data").Cells(pointRow, 5).Value
    TextBox6.Value = Worksheets("data").Cells(pointRow, 6).Value
End Sub

以上で、[次へ]ボタンのコードが完成です。確認してください。

[4] [前へ] ボタンの、プロシージャを書きます。

[3][次へ] ボタンの、プロシージャと同様です。
 [前へ]なので、行番号が -1 となります。

Private Sub CommandButton_previous_Click()
    '次データボタン
    '------------
    '記録した行番号を取得するために、変数を作る
    Dim pointRow As Long
    '記録した行番号を取得する
    pointRow = Worksheets("param").Cells(2, 1).Value
    '行番号を、1 減らす
    pointRow = pointRow - 1
    '------------
    '現在行番号を、paramシートに記録し直す
    Worksheets("param").Cells(2, 1).Value = pointRow
    '------------
    'dataシートのセルポインタも現在行に移動する
    Worksheets("data").Cells(pointRow, 1).Activate
    '------------
    'pointRowのデータを表示
    TextBox1.Value = Worksheets("data").Cells(pointRow, 1).Value
    TextBox2.Value = Worksheets("data").Cells(pointRow, 2).Value
    TextBox3.Value = Worksheets("data").Cells(pointRow, 3).Value
    TextBox4.Value = Worksheets("data").Cells(pointRow, 4).Value
    TextBox5.Value = Worksheets("data").Cells(pointRow, 5).Value
    TextBox6.Value = Worksheets("data").Cells(pointRow, 6).Value
End Sub
以上で、[次へ][前へ]ボタンが動くようになりました。


※ 続きはまた今度。Part II(02)「同じ処理をまとめる」の予定です。
※ ここまでのデータは、こちらからダウンロードできます。(リンクを右クリックして保存)




0 件のコメント:

コメントを投稿

gmailの 赤い文字の「提案」を消す

【問題】 gmail。要件はとっくに片付いているのに、いつまでも提案がでる。こんな提案は全く不要。 【消す方法】 1)[設定]の[全般]で、「アクションの提案」を探す。 2)その「返信するメールを提案」のチェックをはずす。 ついでに「フォローアップするメー...