Excel VBA の基本 2 はじめに 前回のおさらい 反復処理 (For...Next) ステップ実行 参考文献の 転記処理 VBA 関数 ( 引数と戻り値 ) 参考文献 立山秀利 入門者の ExcelVBA 講談社,2012. 1
( はじめに )Excel でこんなこともできるよ! 1 開発 メニュー 挿入 から [ フォームコントロール ] [ スピンボタン ] を選択 2 ワークシート上でドラッグドロップする 3 作られたスピンボタンの上で右クリックし [ コントロールの書式設定 ] を選択 4 [ リンクするセル ] として $A$1 を選択 2
フォームコントロールと INDEX 関数 B1 セルに次の関数を入力する =INDEX(C1:C5, A1, 1) =INDEX(C:C,A1,1) でも良い 3
4
条件に応じた処理の例 もし チェックに1が入力されて いたら 対応するB列のセルの値を 対応するC列のセルに転記する Sub Test() If Range("A2").Value = 1 Then Range("C2").Value= Range("B2").Value End If End Sub 5
同じ処理を A3 から A6 にも行うには If Range("A2").Value = 1 Then Range("C2").Value= Range("B2 ).Value If Range("A3").Value = 1 Then Range("C3").Value= Range("B3 ).Value If Range("A4").Value = 1 Then Range("C4").Value= Range("B4 ).Value If Range("A5").Value = 1 Then Range("C5").Value= Range("B5 ).Value If Range("A6").Value = 1 Then Range("C6").Value= Range("B6 ).Value 変数と反復処理を活用することで スマートに ( より汎用的に ) 書くことができる 6
変数とは プログラミングにおいての変数とは プログラム のソースコードにおいて 扱われるデータを一定 期間記憶し必要なときに利用できるようにするた めに データに固有の名前を与えたもの 値を入れる箱のようなもの A = 12 12 A セルの値とセル番地 セルの名前 の関係 7
変数を体験してみる 1 Sub Test() A=10 Range( A1 ).Value = A A=A+10 Range( A2 ).Value = A End Sub A=A+10 は A に 10 を足したもの を A に代入せよという意味 A に 10 を足せ 8
変数を体験してみる 2 Sub Test() ABC = はじめに MsgBox(ABC) ABC = 次に MsgBox(ABC) End Sub 9
反復処理 (For...Next) For...Next ステートメント ( 構文 書き方のルール ) 指定した処理を 指定した回数だけ繰り返して実行する 書き方 For 変数名 = 初期値 to 最終値処理 Next 変数は繰り返しの回数の管理に用いる 処理を繰り返すたびに値が 1 ずつ増える 10
反復処理サンプル 1 Sub Test() For a = 1 To 3 MsgBox a Next End Sub 実行したいプロシージャにカーソルをあわせて実行ボタンをクリックしても実行できる 11
反復処理サンプル 2 Sub Test() For a = 1 To 5 Cells(a, 1).Value = a+10 Next End Sub Cells オブジェクト Range と同様にセルを表すオブジェクト位置をセル番地ではなく ( 行の位置 列の位置 ) で表す 12
反復処理 p.3 の例を反復処理で書き直す Range 版 Sub Test() For cnt = 2 to 6 Next End Sub If Range( A & cnt).value = 1 Then Range("C & cnt).value=range("b" & cnt).value End If cnt & カウンタを表す変数名としてよく? 利用される左右の二つを文字として結合する 13
反復処理 p.3 の例を反復処理で書き直す Cells 版 Sub Test() For cnt = 2 to 6 Next End Sub If Cells(cnt,1).Value = 1 Then Cells(cnt,3).Value = Cells(cnt,2).Value End If 14
ステップ実行 :1 行ずつ結果を確かめながら実行できる 1 マクロの実行ダイアログから ステップイン 2 F8 キーを押すたびに 1 行ずつ実行される 3 ワークシートのウィンドウと VBA のウィンドウを並べて表示するとわかりやすい 4 リセットボタン で停止する 15
ウォッチ : 変数の変化を見ることができる 変化を見たい変数 ( 今回は cnt) の上で右クリックして ウォッチ式の追加 を選択 式とプロシージャを確認して OK ステップ実行をすると ウォッチウィンドウ に指定した変数の値が表示される 16
オブジェクトの中のオブジェクトを指定する Worksheets( Sheet1 ).Range("A1 ).Font.Size = 14 Sheet1 ワークシート の セルA1 の フォント の プロパティ Size を 14に設定せよ ヘルプの表示 調べたい対象にカーソルを合わせて F1 を押す F1 17
演習 ( 参考文献より ) 転記処理 http://tatehide.com/bb_vba.html よりファイルを入手 実現したい処理の内容 [ 売上 ] ワークシートの B3 セル ( 顧客名 ) を [ 請求書 ] ワークシート A3 セルに転記する [ 売上 ] ワークシートの各項目について 顧客名が B3 セルに一致するものを [ 請求書 ] ワークシートに転記する 18
どのような アルゴリズム にすればよいか? アルゴリズム 問題を解決するための方法や手順のこと 1 [ 売上 ] ワークシートの [B6] から下に向かって [B3] セルと一致するかどうか確認する 2 一致したらその行を [ 請求書 ] に転記する アルゴリズムのイメージ B6 B23 を調べるもし B3と一致したらそのときはその行を転記する 19
その行を転記する? 1 行目 元の8 行目 2 行目 元の10 行目 3 行目 元の16 行目 もし B3 と一致したら そのときはその行を転記する 転記先の行は 1,2,3 cnt 20
転記先の行数を管理する変数を追加 rw を 1 とする B3 と一致した 8 行目を rw 行目に転記 rw に 1 を足す (=rw を 2 とする ) B3 と一致した 10 行目を rw 行目に転記 rw に 1 を足す (=rw を 3 とする ) B3 と一致した 16 行目を rw 行目に転記 rw に 1 を足す (=rw を 4 とする ) 21
作成するマクロ転記先は 7 行目からスタート ( 請求書ワークシート A7 から ) rw =7 から Sub 請求書作成 () Worksheets(" 請求書 ").Range("A3").Value = Range("B3").Value rw = 7 For cnt = 6 To 23 If Range("B" & cnt).value = Range("B3").Value Then Worksheets(" 請求書 ").Range("A"& rw).value=range("a"& cnt).value (B~F 列までの転記処理を省略 : 次スライド ) rw = rw + 1 End If Next End Sub [ 売上 ] ワークシートでマクロを実行することを想定している ( ワークシート名が明示されていないオブジェクトは 実行したワークシートがその対象となる ) 22
省略した転記処理 Worksheets(" 請求書 ").Range("A" & rw).value = Range("A" & cnt).value Worksheets(" 請求書 ").Range("B" & rw).value = Range("C" & cnt).value Worksheets(" 請求書 ").Range("C" & rw).value = Range("D" & cnt).value Worksheets(" 請求書 ").Range("D" & rw).value = Range("E" & cnt).value Worksheets(" 請求書 ").Range("E" & rw).value = Range("F" & cnt).value 顧客の列を省略しているため 1 列ずつずれることに注意 Cellsを利用して書く場合 Worksheets(" 請求書 ").Cells(rw,1).Value = Cells(cnt,1).Value Worksheets(" 請求書 ").Cells(rw,2).Value = Cells(cnt,3).Value Worksheets(" 請求書 ").Cells(rw,3).Value = Cells(cnt,4).Value Worksheets(" 請求書 ").Cells(rw,4).Value = Cells(cnt,5).Value Worksheets(" 請求書 ").Cells(rw,5).Value = Cells(cnt,6).Value 23
ボタンの挿入とマクロの設定 1 マクロを実行したいワークシートを開く 2 開発 メニュー 挿入 から フォームコントロール の ボタン アイコンを選択 3 シート上でドラッグし ボタンを作成する 4 一覧から 登録するマクロ名を選んで OK 24
何かおかしい? 同じ 音羽金属 でも結果が異なることがある? 前に実行した結果を消していないのが原因なので Worksheets(" 請求書 ").Range("A7:E13").ClearContents を最初に ( 転記処理の前に ) 実行する 25
関数 かんすう 与えられた文字や数値に対し 定められた処理を行って 結果を返す機能のこと 表計算ソフトやデータベースソ フト プログラミング言語などで利用される 関数ごと にさまざまな処理が割り当てられており たとえばExcel でSUMという関数を用いると 指定した範囲の合計が求 められる 与える情報を引数 ひきすう 結果を戻り値と呼ぶ 複数の数値 SUM 関数 合計値 26
ワークシート関数と VBA 関数 ワークシート関数はワークシートで使うもの VBA 関数は VBA で記述するもの ワークシート関数 VBA 関数 同じ機能を持つものも多くある ワークシートで事前に書いておくか VBA 内で書くかは用途による 引数の指定方法が違う場合もある 27
VBA 関数の一覧 28
関数の構文 使い方 の説明 F1を押すと出てくる公式のヘルプ 初心者には わかりづらい http://officetanaka.net/excel/vba/function/left.htmより 29
引数の指定方法 引数の指定方法はいくつかパターンがあるので 使用例を見て真似するとよい 例 :Left 関数の引数は ( 文字列 数値 ) Left(" たのしい VBA",4) Left(Range( A1 ),cnt) cnt は数値を代入した変数 省略できる引数もある 戻り値を使わない場合は括弧を省略する 名前付き引数といったものも 30
戻り値 戻り値のある関数は 変数やセルの値として結果を代入する Moji = Left(" たのしい VBA",4) Range("B1").Value = Left(Range("A1"),cnt) 31
MsgBox関数 http://officetanaka.net/excel/vba/function/msgbox.htm より 32
MsgBox 関数の色々な書き方 MsgBox " こんにちは " メッセージ以外の引数は省略 戻り値を使わないので カッコを省略 MsgBox Date Date 関数 ( 今日の日付を返す ) の戻り値を MsbBox 関数の引数としてそのまま利用 MsgBox(" 良いですか?", vbyesno, " 確認 ") YES と NO が表示される タイトルは 確認 となる 33
Yes/No による条件分岐 yn = MsgBox(" 良いですか?", vbyesno, " 確認 ") If yn = vbyes Then Range("A1").Value = " はい " Else Range("A1").Value = " いいえ " End If 変数 yn を使わずに 次のような書き方もできる If MsgBox(" 良いですか?", vbyesno, " 確認 ") = vbyes Then 34
名前付き引数 ( の指定 ) MsgBox " 文字です ", Title:=" タイトルです " 引数 Title を設定するという意味 InputBox 関数のように指定する引数の種類が多い場合に使われる 35
INPUTBOX 関数 InputBox(Prompt,Title, Default, XPos, YPos) kekka = InputBox( 入力してください, _ Default := 初期値, _ Xpos:=50) MsgBox kekka _( アンダーバー ) はその文が次の行に続く場合に書く 36
Excel VBA の基本 3 VBA 関数 ( 引数と戻り値 ): 前回資料 変数のきちんとした定義方法 オブジェクトに関する記述をまとめる (With) フォームコントロールの活用 37
変数のきちんとした定義 VBA では基本的にどこでも変数を定義できる オブジェクト名などの VBA で利用しない文字列であれば 全部変数として扱う その文字列が最初に出てきた時点で VBA はそれを新たな変数として定義する スペルミスをした場合 わからない場合がある hoge = 100 hoge = 250 hoga = 200 以降 hoge を変数として扱う 値は 100 とする 変数 hoge の値を 250 に変更する 以降 hoga を変数として扱う 値は 200 とする 38
Option Explicit と Dim Option Explicit マクロの一番上 ( 先頭の Sub より前 ) に書くことで 宣言された変数以外は使えない ( 勝手に定義しない ) ようにすることができる Dim 文字列 (as 型 ) この文字列を変数として使う! と宣言する ( 型を指定するとその型以外の情報を代入できなくなる ) 39
オブジェクトに関する記述をまとめる (With) 同じオブジェクト名についての処理が続くときに オブジェクト名を省略することができる書き方 With オブジェクト名. から始まるものに自動的にオブジェクト名が付けられる End With 例 With Range( A1 ).Value = セル A1.Font.Size = 15 End With わかりやすい例 :Office TANAKA With って何ですか? http://officetanaka.net/excel/vba/beginner/16.htm 40
転記処理の完成版を読み解いてみる ( 立山秀利 入門者の ExcelVBA 講談社,2012. より ) Option Explicit Sub 請求書作成 () Dim cnt Dim rw With Worksheets(" 請求書 ").Range("A3").Value = Range("B3").Value.Range("E3").Value = Date rw = 7.Range("A7:E13").ClearContents For cnt = 6 To 23 Next If Range("B" & cnt).value = Range("B3").Value Then End If End With End Sub.Range("A" & rw).value = Range("A" & cnt).value.range("b" & rw).value = Range("C" & cnt).value.range("c" & rw).value = Range("D" & cnt).value.range("d" & rw).value = Range("E" & cnt).value.range("e" & rw).value = Range("F" & cnt).value rw = rw + 1 変数の宣言 Date は日付を取得する VBA 関数 先頭にピリオドがついたものは 請求書ワークシート に対しての処理ついていないものは 今 開いているワークシート に対しての処理 41
Excel の発展的な機能 フォームコントロール ユーザーフォーム フォームを作成して VBA 内部から呼び出すことができる イベント処理 ユーザーが何か操作を行ったときに処理を行うことができる ファイルを開いたとき ボタンをクリックしたときなどなど 42
フォームコントロールを使ってみるスピンボタンでの例 1 開発 メニュー 挿入 から [ フォームコントロール ] [ スピンボタン ] を選択 2 ワークシート上でドラッグドロップする 3 作られたスピンボタンの上で右クリックし [ コントロールの書式設定 ] を選択 4 [ リンクするセル ] として $A$1 を選択 43
セルの参照とは =B3 は セル B3 を参照せよ という意味 セル B3 に入っている値や数式などを持ってこい セル番地を直接指定しない方法もある 44
関数による セルの参照 INDEX: セル範囲 行番号 列番号で指定 =INDEX(A1:D4, 3, 2) 範囲 A1:D4 の 3 行目 2 列目を参照 OFFSET: 基準位置 下への移動数 右への移動数で指定 =OFFSET(A1, 2, 1) A1 から下に 2 右に 1 移動したセルを参照 ( 高さと幅を指定することで範囲を返すこともできる ) INDIRCT: 文字列で指定 =INDIRECT(F1) F1 に書かれた文字列で指定されたセルを参照 ( セルの名前を指定することもできる ) 45
フォームコントロールと INDEX 関数 B1 セルに次の関数を入力する =INDEX(C1:C5, A1, 1) =INDEX(C:C,A1,1) でも良い 46
リストボックス ( コンボボックス ) の中身を動的に変更したい場合 企業名ボックスを変更したときに部署名ボックスの中身を変えたい 次のようなデータを準備する 47
部署名ボックスの入力範囲の指定 B2~B5 を 企業名ボックス で指定した企業にあわせて (INDEX 関数によって ) 変更する 48
企業名ボックスの設定と INDEX 関数の引数 A2,A3 ではなく 1,2 でも良い 0 を表示したくない場合は IF 関数を併用するなど C1 の値 (= 企業名ボックスの選択 ) によって INDEX の指す場所が変わっていく 49
VBA についてのまとめ的に プログラム ( アルゴリズム ) とは人の書いたものをモノマネしていくことで学んでいくもの きちんと動かない場合 間違った部分 ( バグ ) を直していく作業が必要 最も時間がかかる作業 コツコツと学んでいきましょう! 50