PowerPoint プレゼンテーション

(1)

Excel VBA の基本②

 はじめに  前回のおさらい  反復処理（For...Next）  ステップ実行  参考文献の「転記処理」  VBA関数（引数と戻り値）  参考文献 • 立山秀利「入門者のExcelVBA」講談社,2012.

(2)

（はじめに）Excel でこんなこともできるよ！ ① 【開発】メニュー【挿入】から［フォームコントロール］［スピンボタン］を選択 ② ワークシート上でドラッグドロップする ③ 作られたスピンボタンの上で右クリックし［コントロールの書式設定］を選択 ④ ［リンクするセル］として $A$1 を選択

(3)

フォームコントロールとINDEX関数

 B1セルに次の関数を入力する

=INDEX(C1:C5, A1, 1)

(4)

(5)

条件に応じた処理の例

 もし「チェックに1が入力されて」いたら • 対応するB列のセルの値を • 対応するC列のセルに転記する Sub Test() If Range("A2").Value = 1 Then Range("C2").Value= Range("B2").Value End If

(6)

同じ処理を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

 変数と反復処理を活用することで、スマートに

(7)

変数とは

 プログラミングにおいての変数とは、プログラムのソースコードにおいて、扱われるデータを一定期間記憶し必要なときに利用できるようにするために、データに固有の名前を与えたもの  値を入れる箱のようなもの A = 12 セルの値とセル番地（セルの名前）の関係

A

12

(8)

変数を体験してみる①

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を足せ

(9)

変数を体験してみる②

Sub Test() ABC = “はじめに” MsgBox(ABC) ABC = “次に” MsgBox(ABC) End Sub

(10)

反復処理（For...Next）

 For...Next ステートメント（構文、書き方のルール） • 指定した処理を、指定した回数だけ繰り返して実行する  書き方 For 変数名 = 初期値 to 最終値処理 Next  変数は繰り返しの回数の管理に用いる • 処理を繰り返すたびに値が１ずつ増える

(11)

反復処理

サンプル①

Sub Test() For a = 1 To 3 MsgBox a Next End Sub 実行したいプロシージャにカーソルをあわせて

(12)

反復処理

サンプル②

Sub Test()

For a = 1 To 5

Cells(a, 1).Value = a+10 Next

End Sub

Cellsオブジェクト

Rangeと同様にセルを表すオブジェクト

(13)

反復処理

p.3 の例を反復処理で書き直す Range版

Sub Test()

For cnt = 2 to 6

If Range(“A” & cnt).Value = 1 Then

Range("C“ & cnt).Value=Range("B" & cnt).Value

End If Next

End Sub

cnt カウンタを表す変数名としてよく?利用される

(14)

反復処理

p.3 の例を反復処理で書き直す Cells版

Sub Test() For cnt = 2 to 6 If Cells(cnt,1).Value = 1 Then Cells(cnt,3).Value = Cells(cnt,2).Value End If Next End Sub

(15)

ステップ実行：１行ずつ結果を確かめながら実行できる ① マクロの実行ダイアログから【ステップイン】 ② 【F8】キーを押すたびに１行ずつ実行される ③ ワークシートのウィンドウとVBAのウィンドウを並べて表示するとわかりやすい ④ リセットボタンで停止する

(16)

ウォッチ：変数の変化を見ることができる

 変化を見たい変数（今回はcnt）の上で右クリックして【ウォッチ式の追加】を選択  式とプロシージャを確認して【OK】  ステップ実行をすると「ウォッチウィンドウ」に指定した変数の値が表示される

(17)

オブジェクトの中のオブジェクトを指定する Worksheets(“Sheet1”).Range("A1“).Font.Size = 14 「Sheet1 ワークシート」の「セルA1」の「フォント」のプロパティ Size を 14に設定せよ  ヘルプの表示 • 調べたい対象にカーソルを合わせて［F1］を押す |

(18)

演習（参考文献より）

転記処理

 http://tatehide.com/bb_vba.html よりファイルを入手実現したい処理の内容  ［売上］ワークシートのB3セル（顧客名）を［請求書］ワークシートA3セルに転記する  ［売上］ワークシートの各項目について、顧客名がB3セルに一致するものを［請求書］ワークシートに転記する

(19)

どのような「アルゴリズム」にすればよいか？  アルゴリズム • 問題を解決するための方法や手順のこと ① ［売上］ワークシートの［B6］から下に向かって、［B3］セルと一致するかどうか確認する ② 一致したらその行を［請求書］に転記するアルゴリズムのイメージ B6 → B23 を調べるもし B3と一致したら

(20)

その行を転記する？

 もし B3と一致したら、そのときはその行を転記する cnt  転記先の行は 1,2,3…… １行目元の8行目 ２行目元の10行目 ３行目元の16行目 ……

(21)

転記先の行数を管理する変数を追加

rwを1とする B3と一致した 8行目を rw 行目に転記 rw に1を足す （=rwを2とする） B3と一致した 10行目を rw 行目に転記 rw に1を足す （=rwを3とする） B3と一致した 16行目を rw 行目に転記

(22)

作成するマクロ

転記先は７行目からスタート（請求書ワークシート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 ※ ［売上］ワークシートでマクロを実行することを想定している（ワークシート名が明示されていないオブジェクトは、実行したワークシートがその対象となる）

(23)

省略した転記処理

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

(24)

ボタンの挿入とマクロの設定

① マクロを実行したいワークシートを開く ② 【開発】メニュー【挿入】から「フォームコントロール」の【ボタン】アイコンを選択 ③ シート上でドラッグし、ボタンを作成する ④ 一覧から、登録するマクロ名を選んで【OK】

(25)

何かおかしい？

 同じ「音羽金属」でも結果が異なることがある？  前に実行した結果を消していないのが原因なので

(26)

関数（かんすう）  与えられた文字や数値に対し、定められた処理を行って結果を返す機能のこと。表計算ソフトやデータベースソフト、プログラミング言語などで利用される。関数ごとにさまざまな処理が割り当てられており、たとえばExcel でSUMという関数を用いると、指定した範囲の合計が求められる  与える情報を引数（ひきすう）、結果を戻り値と呼ぶ

SUM

関数

複数の数値合計値

(27)

ワークシート関数とVBA関数

 ワークシート関数はワークシートで使うもの  VBA関数はVBAで記述するもの  同じ機能を持つものも多くある • ワークシートで事前に書いておくか、VBA内で書くかは用途によるワークシート関数 VBA関数

(28)

(29)

関数の構文（使い方）の説明

F1を押すと出てくる公式のヘルプ （初心者には）わかりづらい

(30)

引数の指定方法

 引数の指定方法はいくつかパターンがあるので、使用例を見て真似するとよい  例：Left関数の引数は（文字列、数値） • Left（"たのしいVBA",4） • Left（Range(“A1”),cnt） ※ cntは数値を代入した変数  省略できる引数もある  戻り値を使わない場合は括弧を省略する  名前付き引数といったものも……

(31)

戻り値

 戻り値のある関数は、変数やセルの値として結果

を代入する

• Moji = Left（"たのしいVBA",4）

(32)

MsgBox関数

(33)

MsgBox関数の色々な書き方

 MsgBox "こんにちは" • メッセージ以外の引数は省略 • 戻り値を使わないので、カッコを省略  MsgBox Date • Date関数（今日の日付を返す）の戻り値を • MsbBox関数の引数としてそのまま利用  MsgBox（"良いですか？", vbYesNo, "確認"） • YESとNOが表示される

(34)

Yes/Noによる条件分岐

yn = MsgBox("良いですか？", vbYesNo, "確認") If yn = vbYes Then Range("A1").Value = "はい" Else Range("A1").Value = "いいえ" End If 変数ynを使わずに、次のような書き方もできる If MsgBox("良いですか？", vbYesNo, "確認") = vbYes Then

(35)

名前付き引数（の指定）

 MsgBox "文字です", Title:="タイトルです"

• 引数Titleを設定するという意味

 InputBox関数のように指定する引数の種類が多

(36)

INPUTBOX関数

 InputBox(Prompt ,Title, Default, XPos, YPos)

kekka = InputBox(“入力してください”, _

Default := “初期値”, _

Xpos:=50) MsgBox kekka

(37)

Excel VBA の基本③

 VBA関数（引数と戻り値）：前回資料

 変数のきちんとした定義方法

 オブジェクトに関する記述をまとめる（With）

(38)

変数のきちんとした定義

 VBAでは基本的にどこでも変数を定義できる • オブジェクト名などのVBAで利用しない文字列であれば、全部変数として扱う  その文字列が最初に出てきた時点で、VBAはそれを新たな変数として定義する • → スペルミスをした場合、わからない場合がある hoge = 100 以降hogeを変数として扱う。値は100とする hoge = 250 変数 hoge の値を250に変更する hoga = 200 以降hogaを変数として扱う。値は200とする

(39)

Option Explicit と Dim

 Option Explicit • マクロの一番上（先頭のSubより前）に書くことで「宣言された変数以外は使えない（勝手に定義しない）」ようにすることができる  Dim 文字列（as 型） • 「この文字列を変数として使う！」と宣言する • （型を指定するとその型以外の情報を代入できなくなる）

(40)

オブジェクトに関する記述をまとめる（With）  同じオブジェクト名についての処理が続くときに、オブジェクト名を省略することができる書き方 With オブジェクト名【 . から始まるものに自動的にオブジェクト名が付けられる】 End With 例 With Range(“A1”) .Value = “セルA1” .Font.Size = 15 End With  わかりやすい例：Office TANAKA「Withって何ですか？」

(41)

転記処理の完成版を読み解いてみる （立山秀利「入門者の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

If Range("B" & cnt).Value = Range("B3").Value Then .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

End If

Dateは日付を取得するVBA関数

(42)

Excelの発展的な機能

 フォームコントロール  ユーザーフォーム • フォームを作成して、VBA内部から呼び出すことができる  イベント処理 • ユーザーが何か操作を行ったときに処理を行うことができる • ファイルを開いたとき • ボタンをクリックしたときなどなど

(43)

フォームコントロールを使ってみる スピンボタンでの例 ① 【開発】メニュー【挿入】から［フォームコントロール］［スピンボタン］を選択 ② ワークシート上でドラッグドロップする ③ 作られたスピンボタンの上で右クリックし［コントロールの書式設定］を選択 ④ ［リンクするセル］として $A$1 を選択

(44)

セルの参照とは

=B3 は「セルB3を参照せよ」という意味

 「セルB3に入っている値や数式などを持ってこい」

(45)

関数による「セルの参照」

 INDEX：セル範囲、行番号、列番号で指定 • =INDEX(A1:D4, 3, 2) • 範囲A1:D4の３行目２列目を参照  OFFSET：基準位置、下への移動数、右への移動数で指定 • =OFFSET(A1, 2, 1) • A1から下に２右に１移動したセルを参照 • （高さと幅を指定することで範囲を返すこともできる）  INDIRCT：文字列で指定 • =INDIRECT(F1)

(46)

フォームコントロールとINDEX関数

 B1セルに次の関数を入力する

=INDEX(C1:C5, A1, 1)

(47)

リストボックス（コンボボックス）の中身を 動的に変更したい場合

 企業名ボックスを変更したときに部署名ボックス

の中身を変えたい

(48)

部署名ボックスの入力範囲の指定

 B2～B5を「企業名ボックス」で指定した企業に

(49)

企業名ボックスの設定と INDEX関数の引数

A2,A3……ではなく 1,2…… でも良い

(50)

VBAについてのまとめ的に

 プログラム（アルゴリズム）とは人の書いたものをモノマネしていくことで学んでいくもの  きちんと動かない場合、間違った部分（バグ）を直していく作業が必要 → 最も時間がかかる作業  コツコツと学んでいきましょう！