Post on 16-Apr-2017
最強の Exce l VBA 応用講座Ⅱ
VBAfor
Professionals
Excel VBA Practice CourseⅡ
Copyright © 日本頭脳株式会社 All rights reserved.
講師プロフィール
V - C O NVBA Consulting
EX celercise
エクセル本舗
日本頭脳株式会社 代表取締役。ITコーディネータ、調理師。世界最大級のコンサルティングファームであるプライスウォーターハウスクーパース出身のたたき上げITコンサルタント。10年以上にわたり大企業向けの業務改革プロジェクトをリード。オフィスワークの生産性向上実現をコンセプトとして事業を展開している。
永井 雅明
業務改善コンサルティング
高度IT業務アウトソーシング
高度IT人材育成
Copyright © 日本頭脳株式会社 All rights reserved.
実績
2
✓業務改善コンサルタント歴15年。
✓プロジェクトマネージャー経験7回。
✓全社システム導入会社数10社。
✓システム規模最大6,000ユーザ。
✓画面設計数500以上。
✓データベース(テーブル)設計数1,500以上。
目次
1. ツール設計 20分
2. ツール開発 <前半戦> 80分 基本データ操作Wordファイル操作
・・休憩・・ 10分
3. ツール開発 <中盤戦> 80分 ロジックの組み立て• マッピングデータ• コンテンツコントロール
・・休憩・・ 10分
4. ツール開発 <後半戦> 80分 ブラッシュアップ• 特殊処理• ツールのメンテナンス• エラーハンドリング• デバッグ
・・休憩・・ 10分
5. ハイレベル講義(オプション) - • プロシージャ化• 表の操作
6. アンケート 10分
Copyright © 日本頭脳株式会社 All rights reserved.
講義中のルール
講義中の質問OK
疲れたら手を休めてOK
ついてこれない場合は休憩中にフォロー
トイレ休憩はご自由に
携帯電話・スマートフォンは音が鳴らないように
ツール設計
5
ツール概要
6
Copyright © 日本頭脳株式会社 All rights reserved. 7
全体構成
人事書類一括作成ツール(本体)
・・
人事書類一括作成ツール.xlsm
採用通知書テンプレート
不採用通知書テンプレート
採用通知書 不採用通知書 不採用通知書
Aさん採用
Bさん不採用
Cさん不採用
Copyright © 日本頭脳株式会社 All rights reserved. 8
採用・不採用通知書テンプレートファイル 知識
住所
氏名
提出期限
住所
氏名
Copyright © 日本頭脳株式会社 All rights reserved.
応募者データ
<項目>1. 応募者ID2. 応募者氏名3. 住所4. 1次面接5. 2次面接6. 採用可否7. 提出期限
9
<応募者データ仕様>• 応募者データ1行につき、通知書1枚が発行される• 応募者IDは、重複がない(ユニーク)ものとする• 各応募者に対して送付する通知書は、採用可否によって異なる• 採用の場合⇒採用通知書• 不採用の場合⇒不採用通知書
知識
Copyright © 日本頭脳株式会社 All rights reserved. 10
マッピングデータ
応募者データ列番号
格納先 特殊処理
2 応募者氏名
3 住所
7 提出期限
応募者データの各項目を、通知書のどこに埋めるかを定義した対応関係表
知識
マッピングデータを使うと、値を埋めていく処理を繰返し化でき処理がシンプルになる。最も大きな利点は、項目の追加やレイアウト修正などの際にメンテナンスしやすくなる。逆に使わないと、項目などを追加するたびにプログラムを修正しないといけなくなる。
ツール開発 前半戦<Wordファイル操作>
11
Copyright © 日本頭脳株式会社 All rights reserved. 12
処理の流れ
通知書を作成ボタンクリック時
処理
通知書作成処理
全体像
Copyright © 日本頭脳株式会社 All rights reserved. 13
ボタンを作ってみよう ワーク
1. 応募者データシートに、「通知書の作成」ボタンを配置してみよう2. 「通知書の作成」ボタンに「button_Click」マクロを登録してみよう3. 「makeFiles」というFunctionを作成する(処理の中身はブランクでよい)4. 「通知書の作成」ボタンをクリックすると、
「makeFiles」が実行されるようにしてみよう
ワーク
Sub button_Click()Call makeFiles
End Sub
Function makeFiles()
End Function
正解
ボタンクリックイベントはSubでしか記述できない。
Subの中ではデバッグできないので、Subではプログラムを書かずに、標準プロシージャに書くこと。
ボタン作成時、「Alt」キーを押しながらドラッグすると、セルの外枠にピッタリ合ったボタンを作成できる
Copyright © 日本頭脳株式会社 All rights reserved. 14
コメントを書くクセをつける
'*****************************************'* 通知書作成処理'*'* 引数1'* 戻値1'*****************************************Function makeFiles()
'▼人事書類を作成する処理'応募者データを1行ずつ繰返し処理するFor iRow = 2 To UBound(vData)
'ファイル名を取得する(応募番号-氏名)sFileName = vData(iRow, 1) & "(" & vData(iRow, 2) & " 様)
"'ファイル名が取れない場合はスキップするIf Len(sFileName) = 0 Then
GoTo NEXT_LOOPEnd If
知識
• プロシージャの前には、見出しをつける
• 引数と戻値を書く• 処理名だけではわかりにくい場合は、処理の概要も書く
• 各処理の内容を、第三者が見てもわかるように詳しく書く
• 処理の目的や、制約事項なども記述
Copyright © 日本頭脳株式会社 All rights reserved. 15
通知書作成処理
'***************************'* 通知書作成処理'*'* 引数1'* 戻値1'***************************
'変数を宣言する
'応募者データを取得する'マッピングデータを取得する
'Wordを起動する
‘▼人事書類を一括作成する
'Wordを終了する
'処理完了メッセージを表示する'処理を終了する
'応募者データを1行ずつ繰返し処理する'ファイル名を取得する(応募番号-氏名)'ファイル名が取れない場合はスキップする
'採用・不採用の区分を取得する'採用・不採用に応じて、使用するファイルを変える
'採用の場合は、採用通知書を設定する'不採用の場合は、不採用通知書を設定する'採用でも不採用でもない場合(未定)はスキップする
'作成するファイルパスを設定する‘Wordテンプレートファイルをコピーする'Wordファイルを開く
'マッピングデータを1行ずつ繰返し処理する'配列の値を、わかりやすい変数に置き換える'Wordに値をセットしていく
'タイトルが一致する場合'通常処理と特殊処理に分岐する
'▼通常処理:値をそのままセットする'▼特殊処理:値を加工してセットする
'Wordファイルを保存して閉じる
全体像
Copyright © 日本頭脳株式会社 All rights reserved. 16
Excel表のデータを取得するには? 知識
• Rangeの仲間、UsedRangeを使う
• Variantに格納する(2次元配列となる)
ポイント
イメージ
【なぜ?】可変行・可変列だから。Rangeは固定範囲なので、行や列を追加するたびに修正しないといけない
【なぜ?】Excel表から1行ずつデータを取ってくるより、表全体のデータを一発で取ったほうが処理が早いし楽
Variant型の変数 ThisWorkbook.Sheets(“シート名”).UsedRange
Copyright © 日本頭脳株式会社 All rights reserved. 17
データを取得してみよう ワーク
Excel表からデータを取得し、配列に格納してみよう1. Variant型の変数を2つ宣言する(応募者データ)
• vData:応募者データを格納• vMapping:マッピングデータを格納
2. 応募者データを取得してみよう(UsedRange)3. マッピングデータを取得してみよう(UsedRange)
ワーク
Function makeFiles()
Dim vMapping As Variant, vData As Variant
'応募者データを取得するvData = ThisWorkbook.Sheets("応募者データ").UsedRange
'マッピングデータを取得するvMapping = ThisWorkbook.Sheets("マッピング").UsedRange
End Function
正解