目次

まずはExcel作業を自動化する「手順書」を読んでみよう

「面倒なExcel作業を瞬殺する仕組みを自分で作る」

という発想では、大量の反復作業、複雑な作業……つまり「時間のかかる面倒なExcel作業」を自動化するとは、具体的にはどういうことでしょうか?

「作業を自動的に全部やってくれる独自のExcelファイルを自分で作る」

ということです。そのファイルを開いて、シート上に用意したボタンをクリックすれば、いつもの作業が一瞬で終わる――そんなことができるようになります。

Excel作業を自動化するには、Excelの「マクロ」という機能を使います。

このマクロという機能は、さまざまな「処理」を、「手順」どおりに、あらかじめプログラミングしておくことで、Excelがそのとおりに動くようになる……つまり、「一連の処理」である「手順」を自動化するものです。それこそ、数時間から1週間かかっていた作業も1秒で終わるようにすることる可能な、Excel作業地獄に苦しんでいる方にとっては夢のような機能だといえます。

「マクロではどんなことができるのか?」

「マクロでこんなことはできるか?」

とはよく聞かれる質問ですが、手作業でできることはほぼすべてマクロ機能で自動化することができます。

たとえば、急遽「都道府県別に、47個のフォルダを作る」という作業が発生したとしましょう。手作業でフォルダを47個作ろうと思ったら、

・フォルダを作りたい場所(デスクトップもしくは任意のフォルダの中)で右クリック→新規作成

・「フォルダー」と選ぶと、1つフォルダが出来上がる

・名前を変更

……という作業を47回繰り返すことになります。1回だけならかんたんですが、47回繰り返すとなると辛いものがありますね。

そんなとき、Excelのシート上に以下のようなA列の2行目から48行目まで都道府県名が入った一覧があれば、都道府県の名前がついたフォルダ47個を作る処理は次ページのような手順書を所定の場所に書くことであっという間に終わります。

Sub sample( )
Dimi
For i = 2 To 48
MkDir ThisWorkbook.Path & “\” & Cells( i, 1 ).Value
Next
End Sub

いきなりこんなものを見せられるとわけがわからなくて拒否反応が出てしまうと思いますが、安心してください。こうした文章がどんな作業を行うものなのか、だれでも確実に解読できるコツを解説していきます。読めるようになれば、次は書くこともできるようになります。

日本語に訳せば処理がわかる

ポイントは、処理を日本語に訳してみることです。

たとえば、この文章ではピリオド(.) は「の」と読みます。

「¥」の記号は、「~の中の」と読みます。

「For」と「Next」という単語は、この2つの間に書かれた処理を繰り返す「反復」という意味を持っています。

このように、訳し方のポイントを押さえていけば、その文章がどんな操作を実行するものなのか、どんどん読み解けていってしまうのです。1つずつ見ていきましょう。

「Sub」で始まり、「End Sub」で終わる

まず押さえておきたいのが、Excelを自動化するこのような手順書は「Sub」で始まり、「End Sub」で終わるという点です。

このSubとEnd Subの間に、Excelに実行させたいこと、自動化したい作業内容(これを「処理」といいます)を記入していくことになります。

Subのあとには、半角スペースをおいて「sample」と書いてありますが、これはこの手順書に適当につけた名前です。名前は自分で好きなように自由につけることができます。

そのあとに、空っぽの( )(カッコ)がついていますが、これは手順書の入力中に自動で入力されるもので、当面使わないので、無視してけっこうです。

「For」と「Next」で繰り返し処理を実行する

次にポイントになるのは、「For」と「Next」という2つの単語です。これは、For と Nextの間に書かれた処理が繰り返し実行されるという「For Next構文」というものです。

マクロを使ううえで最も頻繁に使われる、最も重要な構文で、面倒な繰り返し作業を自動化してくれるものです。本書でもこれから何度も扱っていきます。Forという単語があったら、

このForと、その下のほうにあるNextの間に書かれている処理が繰り返されるんだな……

と読み解いていけばいいのです。逆にいえば、反復作業をこれから自動化したい時には、このFor Next構文を書けばいいというわけです。

その繰り返し処理を何回繰り返すのか、ということが「For」のあとに書かれています。「i=2 To 48」と書かれていますね。小文字のアルファベット「 i 」は、「変数」といって、いろいろな数に変身できる文字列です。ここでは、処理を繰り返す回数を記録しておく「カウンター」のようなものだ
とイメージしてください。

これは別に小文字の「 i 」でなければいけないわけではありませんが、多くの場合、Forの直後に書かれる変数は「 i 」が使われます。Forのあと「i = 2 To 48」と書かれている場合、「変数iが、最初は2からスタートして、次に3、次に4、とどんどん1ずつ増えて、48になるまでFor と Nextの間に書かれた処理を繰り返す」という意味になります。

その間、繰り返し行われる処理として次の1行がForとNextの聞に書いてあるわけですが、これは「フォルダを作る」という処理を表しています。

MkDir ThisWorkbook.Path & “\” & Cells(i, 1).Value

ここを読み解いてみましょう。

先頭にある「Mkdir」は「フォルダを作る」という意味の単語です(Makedirectoryの略)。そして、「フォルダを作る」という処理は次のような文法で書くことになっています。「こういう文型をこれから知っていくと仕事がラクになるのだ」という例として、まずは読みとおしてください。

Mkdir フォルダを作りたい場所  ¥  作りたいフォルダの名前

Mkdirと入力したあと、半角スペースを空けて、¥マークでつながれた2つの指定を書きます。

¥マークの手前には、たとえば「デスクトップ」とか「○○フォルダの中に」といった具合に、どこに新しくフォルダを作りたいかを指定します。

MkDir ThisWorkbook.Path & “\” & Cells(i, 1).Value

の処理では、「This Workbook.Path」がそれにあたります

そして、¥マークの後ろに、新しく作りたいフォルダの名前を書くことになっています。それがここでは、「Cells( i,1 ).Value」にあたります。この「ThisWorkbook.Path」と「¥」という記号と、

「Cells( i,1 ).Value」の3つの要素を&記号で接続する形で、フォルダを作りたい場所と作りたいフォルダの名前を指定しています。「¥」マークは文字列なので、このようにダブルクオーテーションで囲んで入力します。

それでは、今回の例の日本語訳に必要なポイントを紹介します。

・「ThisWorkbook」は、そのまま直訳して「このブック」と訳します。

・ピリオド(.) は、「の」と読みます。

・「Path」は、「フォルダ」と訳します。

・¥マークは、「~の中の」と訳します。

・Cells (i,1) は、「セル」を意味しています。Cells (1,1) はA1セル、Cells (2,3) はC2セルを意味します。Cellsのあとのカッコ内で、カンマ(,)で区切って手前から順にセルの行数、列数を指定することで、1つのセルを指定できることになっています。その行数に変数 i が使われているものです。

・「Value」は「値」、つまりセルに入力されている値を意味します。
 Cells(1,2).Valueであれば、B1セル(シートの1行目、2列目) に入力されている値を表します。

.

すると、MkDir This Workbook.Path & “\” & Cells (i,1).Valueは

【フォルダを作る。作る場所はこのブックのフォルダの中、フォルダの名前はシート上A列行目のセルの値】

という処理だと解読できるというわけです。ここではまだ細部については理解できなくてもちろん大丈夫です。「解読するコツがあるんだ」ということをわかっていただくための事例です。

実行すると何が起きるか。

まず、変数が最初は2になった状態でフォルダを作成する処理がスタートしますから、最初は「Cells(2.1).Value」、つまりA2セルの値である「01北海道」が新しく作られるフォルダの名前として使われ、フォルダの作成が実行されます。すると、「01北海道」というフォルダができるわけです。

その処理が終わると、次は変数iは3になり、Cells(3,1) ……つまり、A3セルの値がフォルダ名として使われ、「02青森県」というフォルダができます。

このように、i が2から48まで変化していく間、A2セルからA48セルまでの値を使いながら、都道府県名のついた47個のフォルダを作成するという処理が自動的に繰り返されるわけです。

「47個のフォルダを作る」などの面倒な作業を自動化するというのは、こういうことです。

そして、これぐらいの手順書は、慣れれば書くのに1分もかかりません。そのために必要な知識を順を追って紹介していきます。

最初にやっておくべき設定

読み方のポイントを押さえたら、さっそく手順書を書いてExcelの自動化をしてみたいところですが、その前に、マクロ機能を使っていくために最初に必要な設定をしておきましょう。それぞれのくわしい意味は後述しますので、まずは以下の手順どおりにExcelの設定をしてください。

ファイルの保存形式を変更する

Excelを起動する。

 [ファイル]メニュー→[オプション]→[保存]から[ブックの保存]の[ファイルの保存形式]を「Excelマクロ有効ブック(*.xlsm)」にする。

リボンに開発タブを表示させる

Excelの初期状態では、マクロの作成に必要な開発タブが上に表示されていません。これを表示されるように設定していきましょう。

①[表示]→[オプション]⇨[リボンのユーザー設定]をクリックする

②開発にチェックを入れてOKをクリックする。

③リボン(画面の上)に[開発タブ]が表示される。

セキュリティーセンター マクロの設定

[ファイル]→[オプション]→[セキュリティーセンター]→[マクロの設定]で「警告を表示し、すべてのマクロを無効にする」を選択する。

(気をつけて使用するという意味です)

これらの設定は1回やってしまえば、それ以降はそのまま保存されます。

手順書を作成するツールの重要な設定

Excel作業を自動化するために勉強していくのは、Excelに「このような処理をしろ」という「手順書」を書くための言語である「VBA」(Visual Basic for Applications)というコンピュータ言語です。先ほど出てきたSubから始まる文章、あれもVBAで書いたものです。VBAで処理内容を示す文章(この「文章」を、以降は「コード」と呼びます)を書くためのツールを、VBE (Visual Basic Editor)といいます。このVBEの起動方法と、最初に一度だけやっておかなければならない設定を見ていきましょう。

まずVBEの起動方法ですが、これはショートカット Alt + F11を押すことで起動できます。これから何度も起動することになるので覚えておいてください。

必ず最初に行うVBEの設定

①VBEの[ツール]メニューから[オプションを選択する]

②[オプション]画面で[自動構文チェック]のチェックを外します。必要ないからです。[変数の宣言を強制する]にチェックを入れて、OKをクリックする。

必要な設定ができたら、いよいよ手順書を書いてExcelを動かしてみましょう。

死ぬほど仕事を楽にするための基本~プロシージャとは

先ほども説明したSubから始まり、End Subで終わる一連のコード、これが1つの手順書なのですが、この手順書の正式名称は「プロシージャ」といいます。プロシージャ (procedure)とは、「手続き」「手順」を意味する英単語です。

このプロシージャ、Subから始まらない応用技もありますが、まずは基本としてSubから始まるものだと覚えてください。

先の例ではSubという単語の次に半角スペースを挟んで書かれて

いる「sample」という文字列、これはこのプロシージャの名前として入力したものでした。プロシージャには、このように好きなように名前を付けることができますが、いくつかルールがあります。かんたんに言うと

「先頭に数字は使えない」

「使える記号はアンダーバー()のみ」

「途中に空白は入れられない」

「同一モジュール上で、同じ名前は2回使えない」

(モジュールについては後述します)

この4つを押さえておけば大丈夫です。日本語でももちろん大丈夫です。

タイトルの後ろには()(カッコ)がついていますが、これは自分で入力する必要はなく、自動的に追加されるようになっています。プロシージャの最後はEnd Subとあり、これは見て想像がつくとおり、

「これでこのプロシージャは終わりです」という意味になります。

このSub と End Subの間に、Excelに実行させたい処理を記述していくわけです。

Excelの自動化の手順書はどこに書くのか

標準モジュール

では、実際にプロシージャを書いてみましょう。このようなプロシージャのコードは、関数と違って、通常のExcelシートのセルなどに入力していくわけではありません。先ほども紹介したように、VBEというプロシージャを作るための特別なツールが別途用意されているので、まずはそれを起動しましょう。すでに紹介していますが、キーボードによるショートカット(Alt] + F11」を押すと、このようにVBEが起動されます。

通常、左側に2つのウィンドウが表示されています。タイトルバー部分に「プロジェクト」と書かれているのが「プロジェクトエクスプローラー」、「プロパティ」と書かれているのが「プロパティウィンドウ」といい、この2つは頻繁に使っていくことになります。

次に、このVBEでプロシージャを書くためのシートを追加します。このプロシージャを書くためのシートのことを「標準モジュール」と呼びます。

この標準モジュールは、Excelファイルには最初は入っていないので、わざわざ追加するわけです。

……その前に、「モジュールって何のことか?」と疑問に思いますよね。

もう少し具体的にイメージをしていただくために、「プロジェクトエクスプローラー」をご覧ください。

プロジェクト – VBAProject

E-BVBAProject (Book 1)

Microsoft Excel Objects

Sheet1 (Sheet 1)

ThisWorkbook

このウィンドウを見ると、なんとなくこれはこのExcelファイルの内部構造を示したような図だと理解していただけると思います。この「SheetlSheetl)」や「This Workbook」といったアイコンの1つ1つが「モジュール」と呼ばれるものですが、ここに新たに通常のプロシージャ作成に使用する「標準モジュール」を追加する必要があるのです。

では、その操作を行ってみましょう。VBEの[挿入]メニューから、[標準モジュール]をクリックします。

この「標準モジュール」フォルダに入っているモジュールを「標準モジュール」といい、通常はこの標準モジュールにプロシージャを書いていくことになります。ちなみに、その他のモジュールは「イベント処理)の際に使います。かんたんに言うと、モジュール=プロシージャを入力するためのシートだと考えてください。そのジュールには、標準モジュールとそれ以外のモジュールの種類があり、まずは標準モジュールを使っていきますよ、ということです。

Excel自動化の最初の一歩

では、プロシージャを作る具体的な手順を紹介します。次の手順は、マクロ、つまりプロシージャを作る際に毎回行う最初の一歩になります。このあと繰り返し実践しながらマスターしていきましょう。

①小文字で3文字、「sub」と入力して、Spaceキーを1回押す。

sub

②プロシージャにつけたい任意の名前(プロシージャ名)を入力する(ここではsampleと入力)

sub sample

 ③Enterを押す。

Sub sample( )
End Sub

すると、このように小文字で入力したsubの1文字目が自動的に大文字に変わり、名前の後ろにカッコが自動的につき、2行下に End Subが自動的に入力されます。このように、「sub」と入力→名前を入力→Enterを押すという手順で、Subから始まりEnd Subで終わるプロシージャが自動的に出来上がるようになっています。

④続けて「Tabキーを押す。

出来上がったプログラムが読みやすくなるように、「Tabで段落をつける作業です。このような段落を「インデント」と呼びます。

作文と同じように、プロシージャも読みやすく理解しやすいものでなければなりません。使っていくうちに改修などしたくなった際に理解しやすく、メンテナンス性の高いプログラムを作ることが、ゆくゆくは自分自身を助けることにつながります。どのような時にインデントをつけるかは、このあと説明していきます。

こうして、1つのプロシージャが出来上がります。このあと、このSub とEnd Subの間に処理を書いていきます。

最初に必ず書く1行を書いてみよう

プロシージャができたら、Subの下に真っ先に書くべきコードをまず書いてみましょう。次の1行です。

Application.ScreenUpdating = False

日本語に訳すと、「Excelの画面更新を停止せよ」という命令文ですが、要はプロシージャの処理をスピードアップしてくれるコードです。

Application と かScreenUpdatingとかちょっとスペルが長い単語を入力するのは面倒に思えますが、そのような入力を助けてくれる便利機能をVBEは備えているので、それをここで紹介します。以下の操作も、何度も繰り返すうちに、慣れてかんたんになってきます。今後出てくるWorksheetFunction、ThisWorkbookなど、比較的長い単語を入力するときは、面倒なうえにスペルミスも起きやすいので、以下に紹介するショートカット[Ctrl] + Space]を活用してください。

①ショートカット[Ctrl]+[Space]を押すと、入力候補リストが出る。

Sub sample

  A….

② キーボードで「ap」の2文字を入力すると、2つ「Application」が出てくるので、口を押して「Application」を選択する。

③Tabを押すと、Applicationと補完入力される。

Sub sample()
Application

このように、長い単語もショートカット[Ctrl]+[Space]のおかげでラクに入力できます。

④さらに続けてピリオド(.)を入力すると、また入力候補リストが表示される。

sSub sample()
Application.
End Sub

⑤キーボードで「s」と入力すると、「ScreenUpdating」が出てくる。

Sub sample( )
Application s…..
End Sub

⑥ Tabを押すと、ScreenUpdatingまでが補完入力される。

このような入力支援機能もついているのです。

Sub sample ( )
Application. ScreenUpdating
End Sub

⑦=(イコール)を入力すると、FalseとTrueの二択で、また入力候補リストが出てきてくれる。

⑧Falseを選択し、tabを押す。

Sub sample ( )
Application. ScreenUpdating= False
End Sub

このように、全てのプロシージャに共通して、最初に作成する基本です。入力を補助してくれる機能は、入力を効率化するでけでなく、スペルミスも防いでくれます。

実行させたい処理を書く

次に、実行させたい処理を書きましょう。今回は「現時点のシート数をメッセージ画面で表示する」というコードを紹介します。

Microsoft Excel 
1

このように「ポップアップ画面で何かを表示する」という動作は、Excelではマクロ機能でなければできないことの1つです。このようなメッセージ画面を表示するには、VBAの関数の1つであるMsgbox関数というものを使います。

では、先ほど作ったプロシージャの中に続けて書いてみましょう。

①小文字で「msgbox」と入力して、Space」を1回押す。

Sub sample( )
Application.ScreenUpdating = False
msgbox

②小文字で「sheets」と入力する。

Sub sample( )
Application. ScreenUpdating = False
msgbox sheets
End Sub

いちいち「小文字で」と言っているのにはわけがあります。大文字で入力してはいけない理由があるのです。

③ピリオド(.)を入力すると、次に続く単語の入力候補リストが出てくる。

Sub sample( )
Application.ScreenUpdating = False
msgbox sheets…

④次は「count」と入力したいので、キーボードから最初の1文字「c」を入力すると、Countが出てくるので、↓を押して選択する。

Sub sample( )
Application.ScreenUpdating = False
msgbox sheets.c|
End Sub

⑤「Tabを押すと、Countが入力される。

このような入力補助機能に慣れていきましょう。

Sub sample( )
Application. ScreenUpdating = False
msgbox sheets. Count
End Sub

⑥ Enterを押して、この1行の入力を確定すると、小文字で入力した単語の先頭文字が大文字に変わる。

Sub sample( )
Application. ScreenUpdating = False
MsgBox Sheets. Count
End Sub

これは、コードの1行を書いてEnterで確定した際、小文字で書いたものが大文字に変わったら、そのスペルは正しく書けているということです。

逆に、各単語の先頭文字が大文字に変わらなかったら、どこかスペルがまちがっているわけです。これが「コードはすべて小文字で書いてください」と申し上げた理由です。入力確定時に大文字にならなかったら、その時点で何らかの入力ミスに気づくことができます。

プロシージャを実行する方法

これでこのプロシージャは完成です。では、このプロシージャを「実行」してみましょう。作ったプロシージャは、以下の手順ですぐに実行できます。

①入力カーソルが、実行したいプロシージャのSubからEnd Subの間にあることを確認する。

なければ、SubからEnd Subの間のどこかをクリックでもして、カーソルをその間に置いてください。

F5キーを押す。

すると、プロシージャが実行されます。F5がプロシージャ実行のキーなのです。

このプロシージャの場合、「MsgBox Sheets.Count」という「(プロシージャを実行した時点のブック内のシート数をメッセージボックスで表示する」という処理が実行されたわけです。[OK]をクリックすると、このプロシージャの処理は終了します。

今回は最初なので このような極めて単純な例で書き方を説明しましたが、このSubからEnd Subで終わるプロシージャの間にいつもの作業で発生する一連の処理を順番にVBAで書いていくことによって、数時間かかっていた作業もたった1秒、たった1クリックで完了できるような独自のExcelツールを作っていくことができるようになるのです。それは、とてもおもしろい作業です。ぜひ楽しんでください。

まとめると、新しくプロシージャを作る際は次の手順を踏むことになります。

①Alt+F11を押してVBAを起動する。

② 標準モジュールがまだない場合は、VBEの[挿入]→[標準モジュール]を選択して追加する。

③sub→Space→タイトルの順に入力し、最後にEnterでプロシージャを作成→Tabでインデントをつける。

④Subから始まりEnd Subで終わるプロシージャができるので、まず真っ先に「Application.ScreenUpdating = False」を入力する。

⑤プロシージャの中に、自動化したい処理を順番に追加していく。

⑥書いたコードを実行するには、入力カーソルが実行したいプロシージャのSubからEnd Subの間にある状態でF5を押す。

この手順がスムーズにできるように、実際にExcelで繰り返し練習してください。特に初心者の方は、標準モジュールを追加する手順を忘れ、標準モジュール以外のモジュールにプロシージャを作るというミスが多いので、注意してください。

Excel自動化のために最初に一度だけやっておくべき設定の「理由」

ここで、「最初にやっておくべき設定」で行っていただいた、スムーズにプロシージャを作成するために絶対に必要な設定について、なぜそれらが必要なのかを説明します。

「せっかく書いたプロシージャが消えてしまった!」を防止するためにExcelの保存形式の変更を

初期状態では、Excelのファイル保存形式は [Excelブック (*.xlsx)]になっています。しかし、このファイル形式のままでは、標準モジュールにプロシージャを作ってもそれを保存しておくことができません。そのため、ファイル保存形式を以下のどちらかに変更しておく必要があるのです。

・Excelマクロ有効ブック(* .xlsm)

を選んでください。

保存形式がExcelブック(xlsx)になっているファイルでプロシージャ

を作り、保存しようとすると、メッセージが表示されます。

ここでまちがえて[はい]をクリックした瞬間、作ったプロシージャはすべて消えてしまいます。ここで[いいえ]を押してファイル形式を変更して保存する……という手順を踏めば問題ないのですが、ここでうっかり[はい]をクリックしてしまうという悲劇がじつにたくさん起きています。

そうした事態を招かないよう、ここで説明した保存形式の変更は最初にずやっておいてください。

これをしておけば作業が快適に!VBEの「自動構文チェックの無効化」と「変数宣言の強制」

VBEの「自動構文チェック」にチェックが入っていると、VBAの入力に構文に不備があるたびにアラート表示が立ち上がって入力作業を止めれてしまい、非常に煩わしくなります。

「まちがいを指摘してくれるものなので便利だし、勉強にもなる」という意見もあるのですが、現実のプログラミングにおいては、最初から完全なコードを書いていくわけではありません。「詳細な条件設定などは後回しで、まずは大枠を……」ということもあるのです。そうした際に、いちいち「ここは文法が不完全だ!」などとアラート表示が立ち上がって入力を」

められるのは、邪魔以外の何物でもありません。また、自動構文チェックを無効にしても、構文に文法的な不備がある場合は入力したコードが赤く表示されるので、すぐにまちがいに気づくことができます。

変数宣言の強制についてはあとでくわしく説明しますが、これはる種の入力ミスや不備があった時に、あなたがそれを即座に特定できるようにしておくための設定です。必ず忘れないようにチェックを入れておいてください。この設定をしておくと、挿入した標準モジュールの一番上に「Option Explicit」という1行が最初から入力されるようになりますが、これがとても大事な役割を果たします。

まずは超基本から~セルへデータ・数式・関数を入力する

1回だけなら手作業で済ませることができるけど……

Excelで作成されているものの大半は「表」です。なので、Excel作業の自動化をマスターするにあたって、まずは表作成の最も基本になる「セルへの入力」の処理の書き方を説明していきます。

ある会社で実際に行われている、データ加工のExcel作業自動化の実例を見てみましょう。こちらの会社では、WebからダウンロードしたCSVファイルのデータを所定のシートに貼り付けて加工する作業が毎日行われていました。その貼り付けられた状態がこちらです。「データ加工」というシートにデータが貼り付けられています。また、この画面では確認できませんが、「マスタ」というシートに支社名が入力されています。

       A         B          C           D              E

 販売年月   小売店県名 商品コード   商品名      売上金額年

201801 愛知県 27210786 アサヒ本生2992920

201801 愛知県 27220883 のどごし生136920

201801 愛知県 27220957 ジョッキ生997920

201801 愛知県 27220985 サントリー金表56448

201801 愛知県 27260317 アサヒスーパードライ40320

201801 愛知県 27260665 キリン一番絞り794640

201801 愛知県 27350171 サッポロ黒ラベル6670

201801 愛知県 27350921 キリン淡題グリーンラベル 17342

201801 愛媛県 27210786 アサヒ本生286440

データはA列からE列までの5列ありますが、ここからの作業内容は「このシートのF列からH列までの3列に、新たにデータを入力する」という。のになります。入力する内容は以下のとおりです。

・F列のセルには、A列のセルの左から4文字分を入力する。

・G列のセルには、B列の小売店県名を支社名に変換する

・VLOOKUP関数を使って支社名を入力する。

・H列のセルには、E列の売上金額が50万以上だったら、、そうで なかったらBを入力する。

この作業は、1回やるだけなら、次のような手作業で済ませてしまうことができるものです。

・ F2セルに次の関数式を入力する。

=LEFT(A2,4)

・ G2セルに次の関数式を入力する。

=VLOOKUP(B2, マスタ!A:B,2,0)

・H2セルに次の関数式を入力する。

=IF(E2>=500000,”A”,”B”)

・F2セルからH2セルを選択して、データを最下端行までコピーする。

これで終えられる作業です。これを1回やるだけなら、わざわざ自動化などしようとする必要はありません。しかし、この作業を定期的に、しかも何度も繰り返しやることになっている場合――たとえば、同じ作業をあと 10枚あるシートで繰り返しやらないといけないとか、毎日この作業が発生しているならば、自動化をすることで瞬時に終わり、大幅に時短と省力化ができることになります。「作業ミスの防止」という観点からも、自動化が必須です。この作業であれば、次のようなプロシージャで自動化することができます。

Sub データ追加( )
Application.ScreenUpdating = False
Dim i As Long
With Sheets(“データ追加”)
For i = 2 To .Cells (.Rows.Count, 1) – End(xlUp).Row
.Cells(i, 6) = Left(.Cells(i, 1), 4)
.Cells(i, 7) = WorksheetFunction.VLookup (
.Cells(i, 2), Sheets(“マスタ”).Range(“A:B”), 2,0)
If .Cells(i, 5) >= 500000 Then
.Cells(i, 8) = “A”
Else
.Cells(i, 8) = “B”
End If
Next
End With
End Sub

わかる人は処理をどう読み解いているか

解説に入る前に、わかる人がこれを解読する際にどんな風に読んでいく

のか、ちょっと頭の中をのぞいてみましょう。

「Withがあるから……End Withまでの間に書かれてる、ピリオド(.) が先頭についたCellsは”データ追加”シートのセルで.……」

「Forがあるから、Nextまでの間の処理を反復する。その反復する処理ってのが……」

「まず、6列目だからF列のセルに……Left関数で、A列のセルの左から4文字を取って入力……」

「次に、7列目だからG列のセルに……VLookup関数でなんか入れてる。B列のセルを検索値にして、”マスタ”シートのA・Bの2列目を参照してるのか…」

「で、If Then構文だから条件分岐……5列目ってことは、E列の値が50万以上だったら8列目……つまりH列のセルにA、そうじゃなかったらBを入力」

「つまり、F、G、H列へのデータ入力を、2行目から、A列のデタ最終行まで繰り返せというわけか……」

このような感じで読み解いていくことができます。

プロシージャの処理内容は、この呟きのとおり、先ほど示した手順にあるF列、G列、H列への関数処理を伴ったセルへのデータ入力を、「2行目からデータの最終行まで繰り返し行う」というものです。このプロシージャを通じて、以下の必須知識を身につけましょう。.

・For Next構文…ForとNextの間に書かれた内容を繰り返し処理する

・With構文……何度も同じ指定をする面倒やしつこさを解消する

・If Then構文……IF関数の役割を果たす

・Dimという単語の意味、「変数の宣言」とは

・関数の使い方

・1行が長いコードを途中で改行して入力する方法

セルにデータを入力するには

では、まず基本中の基本として、Excelの代表的な作業であるセルへのデータ入力処理の書き方を見ていきましょう。セルにデータを入力するには、以下の2つのことを指定します。

・どのセルに入力するか

・何を入力するか

まずは「どのセルに入力するか」、つまり操作対象(「オブジェクト」といいます)となるセルの指定方法です。VBAでセルを表す単語には、次の2つがあります。

・Range(レンジ)

・Cells(セルズ)

この2つの使い分けについては、セルの指定において、このあと説明する「変数」を使う場合はCells、「変数」を使わない場合はRangeと覚えてください。

たとえば、操作対象としてC5セルを指定する場合は、それぞれ次のように書きます。

【Rangeの場合】

Range(“C5”)

このように、Rangeのあとに)(カッコ)を書き、さらにその中にダブルクオーテーション (“) で囲みながら、操作対象となるセル番地を入力します。

この時、セル番地の列記号のCは大文字で入力することを強くおすすめしておきます。理由は読みやすさです。小文字でも文法的には問題なく、基本的には動作するのですが、まれにエラーの原因となることがあります。大文字で書いておけば、そのようなリスクはなくなります。

【Cellsの場合】

Cells(5,3)

Cellsを使う場合は、このように、()の中にカンマ(,)で区切って、指定したいセルの行番号と列番号を入力します。C5セルはシートの5行目、3列目にありますね。なので、Cells(5,3) と いうように、カッコの中のカンマの手前に行番号、カンマの後に列番号を入力して指定します。また、列番号がすぐに何列目だかわからないセルの指定で、数えたりCOLUMN関数で確認するのも面倒・・・・・という場合は、Cells(5,”C”)というように、ダブルクオーテーション(“)で囲んだ列記号を入力する形式でも列を指定することができます。

では次に、こうして指定したセルにデータを入力する処理の記述方法を見ていきましょう。

数値を入力するにはまずは数値です。とってもかんたんで、たとえばC5セルに数値を100と入力する処理は、次のように書きます。

Range(“C5”) = 100

①まず入力先のセル指定、つまり入力したい値を入れる「入れ物」を書く。

②イコール(=)を入力する。

③イコールの右辺に入力したい値を書く。

これだけです。Cellsを使う場合も同様です。

この=記号は「等しい」という意味ではなく、「右辺の値を左辺の入れ物に入れる」(代入演算子)という意味で理解してください。

このように、セルに値を入力する処理で使う=記号は、「を」と訳します。

たとえば、

Range(“C5”) = 100

ならば「C5セルを100にする」と訳せます。

文字列を入力する場合は、次のように入力したい文字列をダブルクオーテーション(“)で囲みます。

Range(“C5″) = “株式会社すごい改善”

ちなみに、一般的には、セルに値を入れる処理は

Range(“A1”).Value = 100

というように、セル指定のあとにピリオド(.)でつなげて「Value」という単語を入力すると説明されます。ただ、この「Value」は省略することが可能なので、省略形で解説を進めます。理由は「このほうが読みやすいから」「入力がラクだから」「文法的に省略可だから」ということです。

セルに値が入る様子を見てみる

では、セルにデータを入力するプロシージャを作って実行し、セルに値が入る様子を見てみましょう。Excel作業の自動化で真っ先に覚えていただきたい基本中の基本です。パソコンで実際に作業できる場合は、いっしょにやってみてください。

まずはExcelを立ち上げ、Alt +F11を押してVBEを起動→標準モジュールを1つ挿入→プロシージャを1つ作成してください。名前は何でも大丈夫ですが、ここでも「sample」としておきましょう。

プロシージャ

Sub sample( )
End Sub

次に「range(“C5”)」と入力していくのですが、ここでRangeという単語によるセル指定のおすすめの方法を紹介します。

①まず、小文字で「range()」とカッコまで入力してしまう。

Sub sample( )
range ( )
End Sub

②カーソルキーの左を1回押してカッコの中に入力カーソルを入れ、ダブルクオーテーション(“)を2つ入力する。

Sub sample
range( “ ” )
End Sub

③カーソルキーの左を1回押してダブルクオーテーションの中に入力カーソルを入れ、「C5」と指定したいセルの番地を入力する(列記号Cは大文字で入力)。

Sub sample( )
range (“C5”)
End Sub

④カッコの外にカーソルを出し、続けてイコール(=)、セルに入力したい数値を続けて入力する。

Sub sample( )
range(“C5″)=100
End Sub

⑤最後にEnterを押すと、改行されて、この行の入力が確定する。

Sub sample( )
Range(“C5”) = 100
End Sub

文法やスペルにミスがなければ、小文字で入力したrangeの頭文字が自動的に大文字に変わります。また、=の前後のスペースが微妙に自動で広がります。入力にミスがある場合……たとえばカッコ内のダブルクオーテーション(*) が1つない状態で[Enter]を押すと、以下のようにrangeの頭文字が大文字にならず、またこの1行が赤文字に変わってしまいます。

このように、「入力した単語の頭文字が大文字にならない」「その行全体が赤文字になる」という合図で、入力にミスがあることを知らせてくれる。ようになっているので、覚えておいてください。

では、このプロシージャを実行して、C5セルに100が入力されるか確認してみましょう。復習ですが、プロシージャを実行するには、実行したいプロシージャのSub と End Subの間に入力カーソルがある状態で、F5を1回押します。すると、現在選択中のシート、つまり「アクティブシート」のC5セルに、100と入力されます。

( )やダブルクオーテーション(“)といった「枠組み」を作ってからその中身を入れるという考え方は、このあとでも重要になってきます。手順的にスムーズになります。

数式の入力方法と注意点

以上のように、数字や文字を入力するのはとてもかんたんです。ただ、当然ながら、実務においてセルに入力するのはそのような固定値ばかりではありません。むしろ、数式や関数を使ってセルに値を入力することのほうが多くなります。

四則演算などの数式については、シート上でセルに入力する際と同じ演算子を使って式を作ることができます。たとえば、A3セルにA1セルとA2セルの数値を足し算、引き算、かけ算、割り算、文字列結合した値を入力する処理は、それぞれ次のような書き方になります。

・足し算 → Range(“A3”) = Range(“A1”) + Range(“A2”)

・引き算Range(“A3”) = Range(“A1”) – Range(“A2”)

・かけ算Range(“A3”) = Range(“A1”) * Range(“A2”)

・割り算 → Range(“A3”) = Range(“A1”) / Range(“A2”)

・文字列結合 Range(“A3”) = Range(“A1″) & Range(“A2”)

また、「A2セルの数値に 1.1をかけ算してA3セルに入力する」という処理は、次のような書き方になります。

Range(“A3”) = Range(“A2”) * 1.1

ここで認識しておいていただきたいのは、このような場合は「セルには計算結果の値のみが入力される」ということです。

たとえば、A1セルに1、A2セルに2が入力されている時、この2つのセルの合計をA3セルに入れるとしましょう。セルに直接数式を入力する場合は、A3セルに次のように入力します。

=A1+A2

こう入力してEnter]を押すと、A3セルには数式の出す答えとして

「3」という数字が表示されます。

しかし、このA3セルに実際に入っているのは「3」という数字ではなく、あくまでも「=A1+A2」という数式ですね。このような形であれば、単純な話、A3セルをクリックして数式バーを見れば「このセルはA1セルとA2セルを足し算しているんだな」というようにそのセルでどのような数式処理をしているかがわかります。

ところが、同じケースで

Range(“A3”) = Range(“A1”) + Range(“A2”)

という処理が実行された結果、A3セルに入力されるのは、A1セルをA2セルを足し算した結果である「3」という値のみだということです。たまに困るのは、このようにプロシージャで計算した結果を入力する場合、そのセルの値がどんな計算をした結果のものなのかが、セルを見ただけではわからなくなってしまうことです。もちろん、VBEを立ち上げ、プロシージャを確認して

Range(“A3”) = Range(“A1”) + Range(“A2”)

というコードを見れば「A1セルとA2セルを足した数をA3セルに入れてるんだな」ということはわかるのですが、確認の手間はかかるようになります。計算結果の値のみが入力されるということでファイルが軽くなるなどのメリットはありますが、シート上でセルの数式を確認できないために余

計な確認の手間がかかるケースがあるのも事実です。

そのようなケースでは、セルへの数式入力として、次のような書き方も可能です。

Range(“A3″) = “=A1+A2”

この形であれば、”の中に書いたとおりの数式がA3セルに入力されることになります。

この「セルには計算結果の値のみが入力される」という特徴は、次に説明する関数での入力の場合も同様です。

VLOOKUP関数はそのままじゃ使えない!Excel作業の自動化で関数を使う際の注意点

関数は、もちろんプロシージャでも使うことができます。ただ、プロシート上でセルに入力して使われる、SUM関数やVLOOKUP関数などプロシージャ内で使う関数は大きく分けて2種類あります。

・関数名の前に「WorksheetFunction.」と入力する必要があるもの

・「WorksheetFunction.」と入力する必要がないもの

便宜上、前者を「ワークシート関数」、後者を「VBA関数」と呼びます。

それぞれ、くわしく説明します。

Left関数のような文字列操作関数、YearやMonthのような日付関数などは、関数名の前に「WorksheetFunction.」と書く必要はありません。

まず、「WorksheetFunction.」をつけなくていいVBA関数の書き方から見ていきましょう。たとえば、A1セルの文字列の左から4文字だけを抽出してB1セルに入力する処理を行う場合。この処理のため、セルに直接関数を入力する場合は、次のようなLEFT関数の式をB1セルに入れます。

=LEFT(A1,4)

この処理は、VBAでは次のようなコードになります。

Range(“B1”) = Left(Range(“A1′), 4)

このように、関数名、また関数の引数の指定については、ほとんどの関数で、シートのセルに関数を入力する際と同じ形になります。

次に、「WorksheetFunction」と書かなければ使えないワークシート関数を見てみましょう。代表例が、Sum、Counta、Sumif、Countifといった数値集計の関数、またVLookupやMatchなどです。一番基本的な関数の1つであるSum関数をマクロで使う場合を例に、書き方を見てみましょう。

A11セルにA2セルからA10セルまでの範囲の数値の合計値を入力する場合は、こうなります。

Range(“A11″) = WorksheetFunction.Sum(Range(“A2:A10”))

単純に、WorksheetFunctionという単語とSumという関数名をピリオド( . ) でつないでいるだけです。

Excelの超基本関数であるIF関数ですが、じつはこれに該当する関数はVBAにはありません。ではどうやってIF関数と同様の処理、つまり条件分岐を行うかというと、If Then構文というものを使います。

=IF(論理式真の場合,偽の場合)

これに対して、If Then構文は次のようなパターンになります。

【If Then構文】
If 論理式 Then
真の場合
Else
偽の場合
End if

たとえば、「A1セルの値が80以上であればB1セルにA、そうでなければB1セルにBと入力する」という処理を行う場合、セルにIF関数を入力する方法であれば、B1セルに次の式を入力することになります。

=IF(A1>=80,”A”,”B”)

この作業は、VBAでは次のように記述します。

If Range(“A1”) >= 80 Then
Range(“B1”) = “A”
Else
Range(“B1”) = “B”
End If

IF関数を使える方であれば、このIf Then構文もすぐに理解できると思います。

・Ifで始まり、End Ifで終わる

・Ifの行は、論理式のあとにThenという「ならば」といった意味合いの単語を書く

・Elseの手前の行に、真の場合の処理を書く

・Elseの次の行に、偽の場合の処理を書く

このようになります。

サンドイッチ構文の2つのポイント

ここで強調しておきたいのは、このIf Then構文は「Ifで始まり、End Ifで終わる」

ということです。何度か出てきている「For Next構文」も、Forで始まり、Nextで終わります。

このように、「~で始まり、~で終わる」という、決まった組み合わせのキーワードで間に処理をはさむサンドイッチ型の構文がVBAにはいくつかあります。プロシージャそのものも、「Subで始まり、End Subで終わる」という構造になっていますよね。

このような、決まった組み合わせの2つの単語で、その間に何らかの処理のコードをはさむ構文を、本書では「サンドイッチ構文」と呼ぶことにします。このサンドイッチ構文は6種類あり、If Then構文もその1つというわけなのですが、このサンドイッチ構文の書き方には大事なポイントが2つあります。

・始まりと終わりを先に書いてから中身を書く

・中身は「Tabでインデント (段落)を付けて読みやすくする

Excel作業に時間がかかる原因を解消してくれる最強の呪文!「反復」のFor Next構文

コピペやオートフィルをそのまま書こうとしない

作業事例をもう一度見てみましょう。

①F2セルに、A2セルの左から4文字分を入力する。

②G2のセルに、B2セルの小売店県名を支社名に変換するVLOOKUP関数を使って支社名を入力する(数式は以下)。

(=VLOOKUP(B2, マスタ!A:B,2,0))

③E2セルの値が50万以上だったらA、そうでなかったらBをH2セルに入力する。

ここまでの作業をVBAで書くと、次のようになります。

Cells(2, 6) = Left(Cells(2, 1), 4)
Cells(2,7) = WorksheetFunction. Lookup(Cells(2, 2), Sheets(“マスタ”).Range(“A:B”), 2,0)
If Cells(2, 5) >= 500000 Then
Cells(2, 8) = “A”
Else
Cells[2, 8) = “B”
End If

④ F2セルからH2セルを選択して、データを最下端行までコピーする。

問題は、最後の④のコピー作業です。

面倒なExcel作業をマクロを使って自動化するなら、まず「手作業ならどのような手順になるかを、日本語で書いてみる」ことが重要です。手作業であれば

「F2セル、G2セル、H2セルに関数を入力して、この3つのセルを選択→選択範囲右下のフィルハンドルをダブルクリックしてデータ最下端行までコピーする」

という作業になります。

しかし、ここで発想の転換が必要になります。このような「データの連続入力」については、手作業で行うコピペやオートフィルといった機能をそのままVBAで書こうというのではなく、「2行目で行った

Cells(2,7) = WorksheetFunction. Lookup(Cells(2, 2), Sheets(“マスタ”).Range(“A:B”), 2,0)

という入力を最終行まで繰り返す」という考え方をここでは採用します。そして、そのような繰り返し作業を自動化してくれるのが、「For Next構文」です。

【For Next構文】

For 変数 = 初期値 To終了値
処理
Next

For Next構文は、For と Nextの間に書かれた処理を繰り返します。その反復は、Forの後に書かれた変数が初期値から終了値になるまで繰り返されます。初期値と終了値とはいずれも数値であり、基本的には初期値より終了値のほうが大きい数字になります。

指定するセルの行数を次々に変化させる~変数

まず、F列のLeft関数の処理を例に、For Next構文で2行目から最終行まで関数で処理した値を入力させる処理を検討してみましょう。

Cells(2,6) = Left(Cells(2,1), 4)

(F2にA2の文字を頭から4つ取り出し、代入するという意味)

この処理をたとえ何回繰り返し実行しても、Cells(2,6)、つまりF2セルにしかデータは入りません。Cellsの行数指定に「2」という固定値が書いてありますから、当然2行目しか処理されません。

では、この処理を2行目から最終行まで繰り返させるにはどうすればいいのでしょうか。ここでは、次のように考えます。

「指定するセルの行数を、2から10まで、順に1ずつ増えるように変更しながら、繰り返し処理する」

そのためには、Cellsの行数指定に、上記のコードのように「2」などの固定値ではなく、「変数」というものを使います。変数は、さまざまな数値や文字を入れることのできる文字のことです。アルファベットでも日本語の文字でも、変数として使うことができます。

変数についてまず最初に知っておかなければならないのは、変数を使う場合は「変数の宣言」と呼ばれる処理を先に書く必要があるということです。この変数の宣言には、「Dim」(ディム)という単語を使います。

たとえば、小文字のアルファベット「i」を変数として使う場合は、次のような「変数 i を宣言する」という意味の処理を先に書きます。

Dim i

これで、変数としてiが使えるようになります。Dimという単語のあとに、半角スペースを挟んで、変数に使いたい文字を書くだけです。

次に、この変数に、数字の2になってもらう処理を見てみましょう。

i=2

この処理によって、この変数」は「2」という数値として使えるようになります。変数iに「2」という数値を入れるという処理だと理解してください。セルへの入力方法のところで、=記号というのは右辺の値を左辺の入れ物に入れる記号だと説明しました。

同様に、「変数」とは、文字や数字などを入れておくことのできる「入れ物」というイメージで理解してください。

次のプロシージャで、変数に値を入れ、その変数を使う、というかんたんな例を見てください。

Sub sample( )
Dim i
i = 2
MsgBox  i
End Sub

このプロシージャは、次のような処理を行います。

①変数 i を宣言する。

②変数 i に2という値を入れる。

③その変数iの値 (2ですね)がメッセージボックスで表示される。

Microsoft Excel 
2
OK

変数iに2が入ると、その変数iは「2」という数字として使えるということです。

入力するセルの行数を次々に変えることで関数を連続入力させる

では、変数を、セルの行数指定に使ってみましょう。

Cells(1,6) = Left(Cells(i,1), 4)

このセルの行数指定に使われている変数iを、2から10まで1ずつ増やしながらこの処理を繰り返させるのがFor Next構文です。この処理を、ForNext構文のFor と Nextの間に書きます。このように、セルの指定に変数を使う場合は、RangeではなくCellsを使います。

For i = 2 To 10
Cells( i , 6 ) = Left( Cells( i , 1 ) ,4 )
Next

このコードを実行すると、シートの6列目、つまりF列の2行目から10行目に、Left関数で処理された値が次々に入力されていくことになります。

では、ここでいったんこのFor Next構文の実際の入力手順を見てみましょう。

① 変数 i を宣言、「dimi」とすべて小文字で入力してEnterを押す。

Sub sample ( )
Application. ScreenUpdating = False
Dim i
End Sub

②  for i=2 to 10と入力する。

Sub sample( )
Application. ScreenUpdating = False
Dim i
for i=2 to 10
End Sub

③「NEXT」と入力する。

Sub sample( )
Application. ScreenUpdating = False
Dim iFor i = 2 To 10NEXT
End Sub

これで、ForとNextの間に書かれた処理内容が繰り返し実行される構造ができました。大事なのは、「Forの行を書いたら、次に2行下にNextを書く」ということです。具体的な手順としては

Forの行を書いたら[Enter]を2回押す

2行下にNextを書く

↑を押す

「Tab]を押す

という流れでスムーズにコードが書けるようになります。

なぜ先にNextを書いておくべきかというと、Forの行を書いた次の行に繰り返し行う処理内容を書いていくと、最後にNextを書き忘れるミスが起こりやすくなるからです。このルールは、ほかのサンドイッチ構文すべてにあてはまります。「枠を書いてから中身を書く」ということを守ると、スムーズにプロシージャを書いていけるようになります。

④やはりすべて小文字で、以下のように入力する。

Sub sample ( )
Application. ScreenUpdating = False
Dimi
For i = 2 To 10
cells(i, 6)=left(cells(i,1),4)]
Next
End Sub

⑤↑か↓を押してカーソルを入力中の行からずらすと、入力が確定する。

Sub sample( )
Application. ScreenUpdating = False
Dim i
For i = 2 To 10
Cells(i, 6) = Left(Cells(i, 1), 4)
Next
End Sub

これで、「データ加工」シートのF列 (6列目ですね)の2行目から10行目に、Left関数でA列のセルの左から4文字だけを取り出して入力するプロシージャが完成しました。

さっそく実行してみましょう。Sub と End Subの間に入力カーソルがある状態でF5を押して実行すると、関数入力の処理があっという間に実行されます。

処理するデータの数は毎回変わる!その変化に臨機応変に対応できなければ意味がない

先ほどの例では、2行目から10行目までということなので、For Next構文の終了値に「10」を指定しました。しかし、実際には扱うデータの行数は常に変わるのが普通です。なので、終了値を「10」などの固定値で指定していては、毎回処理するたびにデータが何行あるのかを確認してプロシージャを修正しなければならなくなり、実用的ではありません。

その問題の解決策として、何行目まで処理するかは毎回自動で確認させる工夫を加えましょう。For Next構文の終了値の指定に、「Cells(Rows.Count, 1).End(xlUp).Row」というコードを使います。

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 6) = Left(Cells(i, 1), 4)
Next

これであれば、データの行数が変わっても問題なく使えるようになります。

「Cells(Rows.Count, 1).End(xlUp).Row」は、ここではまず「A列の何行目までデータが入力されてるか」を調べるものと理解して使ってください。

セルは「どのシートのセルか」まで指定する~シートの指定方法

ここまで、値の入力や関数の引数など、処理に使うセルの指定はRangeか Cellsという単語を使うと説明してきました。A1セルなら「Range(“A1”)」または「Cells(1,1)」と書くわけですが、実際には、セルの指定は「どのシートのセルか」まで含めて指定する必要があります。

ここまでのように、ただ「Range(“A1”)」とだけ……つまりどのシートのセルかを指定しないで書いた場合、そのA1セルとはアクティブシート、つまりプロシージャの実行時に選択中のシートのA1セルということになるのです。

ここからは、必ずシート指定も含めてセルを指定していきます。

シートを指定する最も基本的な方法

シートの指定方法は3通りありますが、ここでは最も基本的なものを紹介します。たとえば、「データ追加」という名前のシートは、以下のように書きます。

Worksheets(“データ追加”)

Rangeと同じです。カッコの中にダブルクオーテーション(“) を書いて、その中に指定したいシート名を入力します。

以下の書き方で解説していきます。

Sheets(”データ追加”)

Sheetsを採用するのは「こっちのほうが短いので書くのがラク」という理由です。いずれの場合も、この指定方法の場合、実際のシート名と完全に同一の文字列である必要があるので、シートタブからコピペで入力するのが確実です。

「マスタ」というシートのA列からB列を指定する場合は、以下のように書きます。

Sheets(“マスタ”).Range(“A:B”)

シートの指定とセルの指定をピリオド(.) でつなぐだけです。このピリオドは「の」と読みます。「”マスタ”シートの範囲A:B」を指定しているということです。

基本的には、すべてのセル指定はこのようにシートも指定したうえで書きます。しかし、同じシートのセルを何度も指定する場合、その都度シートを指定しながら書くのは非常に非効率で、以下のように見た目にも読みづらいコードになってしまいます。

For i = 2 To Sheets(“データ追加”].Cells (Sheets(“データ追加 1. Count, i).End(xlUp).Row
Sheets(-560].Cells(i, 6) = Left (Sheets(E-300) Coilsi. 1), 4)
Next

同じシート指定が4回も繰り返されている!シートの一括指定で書くのも理解するのもラクに~With構文

そこで役立つのが、「With構文」です。With構文を使うと、以下のように記述をすっきりさせることができます。

With Sheets(“データ追加”)
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(i, 6) = Left(.Cells(i, 1), 4)
Next
End With

With構文は、Withで始まり、End Withで終わります。Withの半角スペースあとに一括指定したいシート指定を1回書くだけでよくなります。

そして、With と End Withの間のコードで、いくつかCellsの先頭にピリオド(.) が追加されています。これは、そのピリオドがついているセルは、Withのあとに書かれているシート、つまり”データ追加”シートのセルだ、という意味になっているのです。

このWith構文でセルの所属シートを一括指定することのメリットは、処理の記述がラクになるだけでなく、「このプロシージャはどのシートをおもに処理するものなのかがわかりやすくなる」というものもあります。上記の処理であれば、”データ追加”シートで処理を行うものなのだとわかりやすくなるということです。

【演習】ひととおりプログラムを書いて実行してみましょう

ではここであらためて、「Excel作業自動化のためのプロシージャを作って、実行する」という一連の流れをまとめてやってみましょう。

1、Alt+F11でVBEを起動→

標準モジュールを挿入→プロシージャを次の状態まで書く。

Sub sample ( )
Application. ScreenUpdating = False
Dim i
End Sub

2、With構文による”データ追加”シート一括指定の構造を作る。

Sub sample( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加”)
End With
End Sub

With構文もサンドイッチ構文なので、Withの行を書いたらEnterを2回→先にEnd Withを入力、↑を1回→Tab の順で入力を進めます。

3、 For Next構文の繰り返し構造を書く。

cells、rowsの前にピリオド(.) を忘れないようにつけて書いてください。

Sub sample( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加”)
For i = 2 To .cells(.Rows.Count, 1). End(xlUp).Row
Next
End With
End Sub

4、Left関数を使いながら、F列への入力を実行する次の処理を入力してEnterを押す。

.cells(i, 6) = left(.cells(i, 1), 4)

Cellsの手前にピリオドを忘れないように、またすべて小文字で入力してください。

Subsample( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加”)
For i = 2 To .Cells (.Rows. Count, 1). End (xlUp). Row
Cells(i, 6) = Left(.Cells(i, 1),4)
Next
End With
End Sub

5、.cells(i,7)=まで入力したら、「WorksheetFunction」を入力するため、「Ctrl+Space]→[wo]の2文字を入力すると、2つめにWorksheetFunctionが出てくるので、矢印キーで選択する。

Sub sample( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加”)
For i = 2 To .Cells( . Rows. Count, 1). End(xlUp).Row
.Cells(i, 6) = Left(.Cells(i, 1),4)
.cells(i, 7)=wal
Next
End With
End Sub

6、Tabを押すと、残りの文字がすべて自動で入力される。

Sub sample( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加”)
For i = 2 To .Cells(, Rows.Count, 1). End(xlUp).Row
.Cells(i, 6) = Left (.Cells(i, 1), 4)
.cells(i, 7)=WorksheetFunction
Next
End With
End Sub

7、 続けてピリオド(.)を入力するとワークシート関数入力候補リストが出てく

るので、「vl」の2文字を入力するとVLookupが出てくる。

Sub sample( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加”)
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(i, 6) = Left (.Cells(i, 1). 4)
.cells (i, 7) =WorksheetFunction wil
Next
End With
End Sub

8、[Tab]を押して確定、最初のカッコを入学したところで改行を入れる。

改行するには(Space + アンダーバー(-) を入力してからEnter]を押します。

9、続けてVLookup関数の4つの引数を入力する。

改行後はTabで行頭をずらすと、あとで読みやすくなります。

Subsample( )
Dim i
Application. ScreenUpdating = False
With Sheets(“データ追加”)
For i = 2 To .Cells(. Rows.Count, 1). End(xlUp).Row
Cells(i, 6) = Left(.Cells(i, 1),4)
.Cells(i, 7) = WorksheetFunction. VLookup( . Cells(i, 2), Sheets(マスタ”).Range(“B”), 2.0)
Next
End With
End Sub

あとは最後の処理、E列の数字が50万以上ならA、そうでなければBをH列に入れる If Then構文を入力します。

10、Ifの行を入力する。

Sub sample ( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加”)
For i = 2 To .Cells(. Rows. Count. 1). End (xlUp). Row
.Cells(i, 6) = Left(.Cells(i, 1), 4)
.Cells(i, 7) = WorksheetFunction. VLookup(
.Cells(i, 2), Sheets(マスタ”).Range(“A:B”), 2,1
if .cells(i,5)>=500000 then
Next
End With
End Sub

11、 Enterを2回押して先に「end if」と入力し、↑を押してTabを押す。

Sub sample ( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加)
For i = 2 To Cells ( Rows. Count, 1). End (xlUp). Row
.Cells(i, 6) = Left (.Cells(i, 1), 4)
.Cells(i, 7) = WorksheetFunction. VLookup
.Cells(i, 2). Sheets (“325”).Range(“A:B”). 2.
If .Cells(i, 5) >= 500000 Then
End If
Next
End With
End Sub

12. If Then構文の中身を書いて完成させる。

Sub sample( )
Application. ScreenUpdating = False
Dim i
With Sheets(“データ追加”)
For i = 2 To .Cells(. Rows. Count, 1). End (xlUp). Row
.Cells(i, 6) = Left (.Cells(i, 1), 4)
.Cells(i, 7) = WorksheetFunction. VLookup
.Cells(i, 2), Sheets(“735”). Range (“Ā:B”), 2,0
If .Cells(i, 5) >= 500000 Then
.Cells(i, 8) = “A”
Else
.Cells(i, 8) = “B”
End If
Next
End With
End Sub

書き終わったら、F5で実行してみると、以下のようにF列からH列にデータが入力されます。

このプロシージャをファイルに保存しておけば、次回以降また同じ作業が発生しても一瞬で終わらせることができる強力な自動化ファイルになるということです。しかし現実には、より実践的な自動化ツールを作るノウハウがこの先必要になってきます。

この演習を実際にサンプルファイルで練習された場合、保存の必要はないので、そのままファイルは閉じて大丈夫です。

そして、このファイルを使って、何も見なくてもこの演習で書いたプロシージャが書けるようになるまで、何度もこの入力手順の練習を繰り返してみてください。英語の学習において例文暗記が有効であるように、VBAのプログラミングでもサンプルとなるプロシージャを丸暗記してしまうのは極めて効果的なスキルアップの方法です。

わかりやすくするため押さえておきたいこと!なぜ、Tabで段落をつけるのか

インデントの重要性を理解する

Sub データ追加( )
Application. ScreenUpdating = False
Dim i As Long
With Sheets(“データ追加”)
  For i = 2 To .Cells (. Rows. Count, 1). End (xlUp). Row
    .Cells(i, 6) = Left(.Cells(i, 1), 4)
    .Cells(i, 7) = WorksheetFunction. VLookup( _
    .Cells(i, 2), Sheets(“7”). Range(“A:B”), 2, 0)
      If .Cells(i, 5) >= 500000 Then
       .Cells(i, 8) = “A”
      Else
       .Cells(i, 8) = “B”
      End If
  Next
End With
End Sub

インデント(段落)によって、With と End Withのサンドイッチ構造、ForとNextのサンドイッチ構造、If と End Ifのサンドイッチ構造がわかりやすいですね。

文法的なルールではありませんが、プログラムを読みやすくするための知恵として慣習となったものです。読みづらいプログラムにしていくことに何らメリットはありませんから、インデントはつけるようにしてください。

処理を自動化する知識を身につけるため
押さえておきたいこと

セルに対して行う処理にはどのようなものがあるか

結局のところ、Excelファイルを使うときに操作するもの、つまりExcelの「操作対象」といえば、セル、シート、ブックです。これらExcelの操作対象のことを「オブジェクト」という総称で呼びます。

では、それぞれのオブジェクトに対して、普段のExcel作業ではどのような処理をしているでしょうか。

セルに対して行う代表的な処理といえば、値の入力、そして逆にキーボード操作ならDeleteを押して行う値の消去があります。ほかにも、セルを右クリックして出てくるメニューを見てみると、いろいろできることがわかります。

セルの書式設定、セルや行や列の挿入や削除、オートフィルタ、並べ替え、セルのコピーや貼り付けなどがありますね。貼り付けにも「形式を選択して貼り付け」という処理もありますし、名前の定義やハイパーリンクを設定することもあるわけです。

シートに対して行う処理についても、いずれかのシートのタブを右クリックして出てくるメニューを見てみましょう。

シートを挿入したり削除したり、名前の変更、シートの移動やコピー、シートを非表示にしたり再表示するなどの処理があります。また、シートのタブの色を変えることもできます。

ブックに対して行う処理には、ブックを開く、閉じる、上書き保存、名前をつけて保存などがありますね。

また、実際のパソコン作業では、ファイルをコピーしたり名前を変えたり削除したりすることもありますし、フォルダを作ったり削除したりといった作業も発生します。

このような1つ1つの「処理」は、たいして手間や時間がかかるものではありません。しかし、処理が複数組み合わさった一連の作業に時間がかかって面倒だという際は、1つ1つの処理を正しい順番で行われるように手順書=プロシージャにまとめてしまえば、以降はExcelのマクロ機能が自動でやってくれるので、それまで長時間かかっていた作業も一瞬で終えてしまえてラクです。

ブックに対するさまざまな処理の書き方について、代表的なものを紹介していきます。ここではひとつひとつマスターしていこうとはせず、次のポイントを強く意識して、ひととおり読みとおしてください。

処理をきちんと日本語訳して理解する

「すでにマクロが組んであるファイルを引き継いだ時、その内容がわからなくて困ってる……」

というのがよくある話です。なので、「まずプロシージャを解読できるようになりましょう」とお話ししています。読めないものは、当然書くこともできないからです。

プロシージャを解読するには、1つずつ処理内容を日本語に訳すと手っ取り早いです。その際に役立つ2つのポイントを紹介します。

・ピリオド(.)は「の」と訳す。

・イコール (=) は「~(左辺)を~(右辺)にする」と訳す。

たとえば、「Sheets.Add」という処理であれば、そのまま直訳で「シートの追加」と訳します。

また「Range(“A1”).Font.Color = vbRed」という処理であれば、日本語訳は「A1セルの字の色を赤にする」といった具合です。

「その処理が行われたらExcelはどのように動くか?」をイメージする

たとえば、「Sheets.Add」という処理が実行されたら、「シートが1枚追加された状態」そして「手作業で追加した時と同じように、そのシートがアクティブになっている」と頭の中でその処理によるExcelの動作をイメージできるようになると理解が早くなります。

「Range(“A1”).Font.ColorvbWhite」という処理が実行されたら、「AIセルの字が白くなる」とイメージできるようになると、理解が早くなります。

そのほか、文法的な専門用語も出てきますが、ここでは「そういう言い方があるのね」ぐらいの感覚で読み進めてください。

見やすい表を作るための作業をラクにする~セルの書式設定

Excelでの資料作成では、表を見やすくするためにセルの字体を変えたり、セルに色や罫線をつけたりといった「セルの書式設定」という機能を使う場面がよくありますね。とても大切な作業ですが、この作業に日常的に時間がかかっているとしたら、そんな作業に時間をかけている場合ではありません。資料は一瞬で完成させて、その資料を活用する時間とエネルギーを増やしましょう。こうした作業を自動化するにはどんな処理をプロシージャに追加すればいいのか見ていきましょう。

書式設定の6つの種類

まず、[セルの書式設定]の画面を見て、そもそも書式設定にはどんな種類があるのか見てみましょう。

タブを見てみると、「表示形式」「配置」「フォント」「罫線」「塗りつぶし」「保護」と6つの設定ができることがわかります。それぞれ、さらに細かい設定

をできるようになっているわけですね。

RangeまたはCellsという単語で指定する「セル」に対してこれらの設定を行う処理をプロシージャに加えるには、それぞれどのような単語を使えばいいのか、その一部を紹介していきます。

セルに入力されているフォントを太字にする

Range(“A1”).Font.Bold = True

【日本語訳】 A1セルの「Font」の「Bold」を「True」にする

「True」にするというのは、「有効にする」とか「オンにする」といったニュアンスでとらえてください。逆に、「無効にする」「オフにする」には「False」という単語を使います。ちなみに、このTrueとFalseをまとめて「論理値」と呼びます。

VBAで書く処理の多くは、日本語訳としては「シートの削除」とか「セルを赤く塗りつぶす」というように、「何を、どうする」という文になります。このRange(“A1”).Font.Bold = Trueという処理であれば、「セルのフォントを、太字にする」といった具合です。

この場合、操作の対象になるのは「セルのフォント」です。VBAでいえば、Range(“A1”).Fontです。この部分、つまりこのような操作対象を指定する言葉を「オブジェクト」と呼びます。

では、Boldという単語は何なのか? これは、「プロパティ」という種類の単語です。

いきなり「オブジェクト」とか「プロパティ」といった専門用語が出てきました。このあと、「メソッド」という言葉も出てきます。これらは、VBAに使う単語の種類です。通常の言語でも、「名詞」や「動詞」や「形容詞」、または「目的語」などの文法的な分類があるように、VBAの単語にもこのような分類があります。今出てきたものでいえば、「Range」や「Font」は「オブジェクト」という種類の単語、「Bold」は「プロパティ」という種類の単語です。

プロパティとは、ひと言でいえば「何かについての情報の種類」のことです。人間にたとえていえば、1人の人間に関する情報には名前や血液型、生年月日、身長や体重のような、その人についての「情報の種類」がありますよね。Excelでも、たとえばシートの「名前」、セルの「幅」、フォントの「色」や「サイズ」といった「情報の種類」があり、その情報の種類を指定するのが「プロパティ」なのだと、ここではかんたんに理解して読み進めてください。

セルを設定する

VBAでは、たとえばA1セルに入力されている文字の色を白にする処理は次のような書き方になります。

Range(“A1”).Font.Color = vbWhite

【日本語訳】 A1セルの「Font」の「Color」を「白(vbWhite)」にする

この場合は、Range(“A1”).Fontがオブジェクト、Colorがプロパティになります。

オブジェクトとプロパティがピリオド(.) でつながってますね。ピリオドは「の」、イコール (=) は「~(左辺)を~(右辺)にする」と訳すニュアンスが伝わってきましたでしょうか。

セルのフォントのサイズを変える

A1セルに入力されている文字の大きさを変える……たとえば14ポイントにする処理をプロシージャに加えるには、次のような処理を書きます。

Range(“A1”).Font.Size = 14

【日本語訳] A1セルの「Font」の「Size」を「14」にする

この場合は、Range(“A1”).Fontがオブジェクト、Sizeがプロパティになります。やはり、オブジェクトとプロパティがピリオド(.)でつながってます。

このように、ピリオドは「の」、イコールは「~(左辺)を~(右辺)にする」と処理内容をそのまま日本語に訳して理解できると、あとはそれぞれの単語について調べながら理解できるようになっていきます。

セルを塗りつぶす

A1セルを赤で塗りつぶす処理をプロシージャに加えるには、次のような処理を書きます。

Range(“A1”).Interior.Color = vbRed

【日本語訳] A1セルの「Interior」の「Color」を「赤(vbRed)」にする

この場合は、Range(“A1”).Interiorがオブジェクト、Colorがプロパティになります。Color、Sizeなど、プロパティが「情報の種類」であるということが段々理解できてきたでしょうか。

セルに罫線をつける

A1セルに実線で罫線をつける処理をプロシージャに加えるには、次のような処理を書きます。

Range(“A1”).Borders.LineStyle = xlContinuous

【日本語訳】 A1セルの「Borders」の「LineStyle」を「実線 (xlContinuous)」にする

この場合は、Range(“A1”).Bordersがオブジェクト、LineStyleがプロパティです。Bordersとは、「セルの境界線」というセルの構成要素の1つを示しているわけです。その境界線につける線の種類 (LineStyle) を実線にする、という処理ですね。

処理の文型をおさらい

以上紹介した処理の文型は、オブジェクトとプロパティという言葉を使って説明すると

オブジェクト.プロパティ = 値

というパターンになっています。この文型を、本書では「値の設定文」といいます。

オブジェクトの指定とプロパティをピリオド(.) でつないで、「何を、どうする」の、「何を」という部分を指定します。

一方、イコール (=) の右辺には「値」とありますが、これは「何を、どうする」の、「どうする」の部分ですね。赤にするなら、vbRed という「値」をイコールの右辺で指定するということです。

ちなみに、上記以外の「セルの書式設定」はどうやるんだ……という時は、「自動記録」という機能で調べることになります。

自動記録は、セルの書式設定に限らず、さまざまな処理の書き方がわからない時に参考になる大事な機能ですが、その自動記録を使いこなすためにも、まずはこうした処理の意味を解読できるようになっておく必要があります。

セルの値を消去する

セルに値を入力するコードは、たとえばA1セルに 100と入力するなら、以下のようになります。

Range(“A1”) = 100

一方、セルの値を消去する操作、つまり手作業でやるならセルを選択して「Delete]を押すという処理をプロシージャに加えるには、次のように書きます。

Range(“A1”).ClearContents

【日本語訳】 A1セルの値消去(Delete]を押す操作)

ちょっと文型が変わりましたね。イコール (=) 記号がありません。ここに出てきたClearContentsは、オブジェクトでもプロパティでもなく、「メソッド」という種類の単語です。このあと、AddとかDeleteなどが出てきますが、このように動詞が使われる単語がメソッドです。

このメソッドを使う処理の書き方は、本書では「メソッド文」といいう文型になります。

オブジェクトメソッド

この文型でも、ピリオド(.) はやはり「の」と訳すことで理解しやすくなります。「Range(“A1″).ClearContents」なら、「A1セルの、値を消去」といった具合です。

表へのデータの入力時、既存データが入力されていたら、まずそれを消してから入力する~初期化の重要性

セルの値を消去する命令である、このClearContentsが実際によく使われる具体的なケースを見てみましょう。

決まった形式の表にデータを入力していく作業を自動化する場合、その表にすでにデータが入っていたら、そのデータはいったん消してから、つまり「初期化」してからデータを入力していくように作業手順を設計します。

たとえば、以下のように「No」~「判定」までの4項目を2行目からデータを入力していく場合。

No 氏名 得点 判定

1 笠井  73      B

2 中島  95    A

・・・・・・・・・・

すでに21行目までデータが入っている場合、いったん消しておかないと、新規の入力が21行目より手前で終わってしまったときに、それ以降の行には最初から入っていたデータが残った状態になってしまいます。そのような問題から、セルにデータを入力する作業は、いったんその入力セル範囲でデータを消去してから入力するという「初期化」の手順が必要になるのです。

2行目から21行目を消去する処理を加えるには、次のように書きます。

Range(“A2:D21″).ClearContents

ただ、このような範囲指定だと、22行目以降にもデータが追加されている状態ではすべて消去することができませんよね。かといって、その都度プロシージャを修正しなければならないようでは実用的ではありません。このような場合は、次に紹介するような行数の変化に自動対応できる範囲指定の書き方を使います。

セルの範囲指定を行う方法 ~CurrentRegionとOffsetを使いこなす

まず、どのように書けばどのような範囲が指定されることになるのかを確認していきましょう。

指定したセルを選択する Selectメソッドによる動作結果を例に解説します。

Range(“A1:D21”).Select

この処理が実行されると、以下のように範囲選択されます。

しかし先ほども触れたように、この「Range(“A1:D21”)」という書き方では、きっちりセル範囲A1:D21 しか指定できず、融通が利くこともなく、データの行数が変わった際に対応できません。

そこで、次のような処理を使います。

Range(“A1”).CurrentRegion.Select

Range(“A1”).CurrentRegion…… これは、A1セルを選択してショートカット Ctrl+Aを押すと選択される範囲を指定します。CurrentRegionはプロパティになりますが、この方法であれば、データの行数はもちろん、列数が増えても、A1セルを選択した状態で[Ctrl] + A を押すと選択される範囲(これをA1セルの「アクティブセル範囲」といいます)を指定できます。

ただ、この指定方法だと、項目名のセルである1行目も範囲指定に含まれており、この範囲指定のままClearContents、つまりセルの値消去をしてしまうと、本来残しておきたい1行目の項目名のセルまで値が消えてしまいます。

1行目は値消去の範囲指定に含まれないようにする Offset

そこで、この範囲指定を1行分だけ下にずらして、1行目は値消去の範囲指定に含まれないように工夫します。そのとき使うのがOffsetです。

Range(“A1”).CurrentRegion.Offset(1, 0).Select

Offset もプロパティに分類される単語で、そのあとに続くカッコの中の数字に従って、操作対象となるセルの位置をずらします。カッコの中には、カンマ(,)で区切って数字を2つ書きます。この処理では、Offset(1, 0) となっ

ています。カッコの中の数字は、次のような意味になります。

【書式】Offset(下にずらす数、右にずらす数)

※数字をマイナスにすると、上にずらす数、左にずらす数になる

つまり

Range(“A1”).CurrentRegion.Offset(1, 0)

は、「A1セルのアクティブセル範囲 (CurrentRegion)を下に1つ、右にゼロ個ずらした範囲」とい

うことになり、先ほどの図で選択されている範囲を指定します。すると、

Range(“A1”). CurrentRegion.Offset(1, 0).ClearContents

という処理で、1行目の項目名のセルは残して2行目以降のデータだけ消去できるようになります。

表にいろいろ入力していく作業を自動化するプロシージャでは、このような入力前に既存データをいったん消去する「初期化」の手順を加えるという発想が非常に大切です。

セルのコピペ作業を自動化する

コピペ(コピー&ペースト) 作業の繰り返しに苦しんでいる方は多いですが、ひとくちにコピペといってもいろいろあります。ここでは、使用頻度の高いものを紹介します。

書式も含めてすべてコピーする

セルのコピーには、Copyというメソッドを使います。たとえば、A1セルの値をA2セルにコピーする場合は、次のような処理になります。

Range(“A1”).Copy Destination:=Range(“A2”)

メソッド文の応用パターンが出てきました。セルをコピーしたら、普通どこかに貼りつけますよね? Copyメソッドは貼り付け先もいっしょに指定することができ、それが上記の書き方になります。

Copyメソッドの後ろに、半角スペースを空けて書かれている

Destination (「行先」という意味の英単語です)は、メソッドの「引数」という種類の単語です。わかりやすくいうと、メソッドに関する「質問」だと理解してください。このDestinationの場合、「コピーした後の貼り付け先は?」という質問をしています。そして、その「回答」を、コロンイコール(ー)の後ろに書いています。つまり、「A2セルが貼り付け先だ」と指定しているわけです。

この:=を書くべき部分を普通の=だけ書いてしまうミスが多いので注意、してください。「:=を使うのは、メソッドの引数を指定するときだけ」とも覚えておいてください。引数を省略した形として、次のような書き方もできます。

Range(“A1”).Copy Range(“A2”)

「形式を選択して貼り付け」をしたい場合

「書式はコピーしたくない」とか、数式の入ってるセルをコピーして値貼り付けして数式をなくすような場合があります。その場合は、「形式を選択して貼り付け」という作業になりますが、そのためにはPasteSpecial メソッドを使います。以下のコードでは、引数Pasteを書き、:=のあとに貼り付けの形式(ここでは、値貼り付けのxlPasteValues) を指定しています。

Range(“A1”).Copy
Range(“A2”).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

こちらの2行目も、省略形として次のような書き方ができます。

Range(“A1”).Copy
Range(“A2”).PasteSpecial xlPasteValues
Application.CutCopyMode = False

「この書き方もできる」というより、PasteSpecial メソッドを入力して半角スペースを押すと、いきなりこの貼り付けの形式を指定する単語の候補リストが出てくるので、そこでxlPasteValuesを選んで「Tab]を押すと、この省略形のコードになります。

どちらがいいかですが、「動作としてはどちらでも問題ないものの、引数を書いたほうが意味がわかりやすい」という程度の好みの問題です。

3行目の「Application CatCopyMode = False」は、コピー中のセルを示す線の表示を消す処理です。

セルを削除する

セルを削除する操作の基本をおさえておきましょう。

削除するセルの右側にあったセルを左方向に詰める

→左方向にシフト

・削除するセルの下側にあったセルを上方向に詰める

→上方向にシフト

・そのセルを含む行全体を削除する 

→ 行全体

・そのセルを含む列全体を削除する 

→列全体

セルの削除には、Deleteメソッドを使います。たとえば、A1セルを削除する操作を自動化するには、次のように書きます。

Range(“A1″).Delete

このとき、削除したあとに左方向にシフトするのか上方向にシフトするのかを指定しておかないと、Excelが勝手にシフトする方向を判断してしまいます。そこで、意図せぬ動作をしないように、引数shiftを使ってシフトする方向を指定します。

左方向にシフト Range(“A1”).Delete shift:=xlToLeft

上方向にシフト Range(“A1”).Delete shift:=xlUp

また、そのセルを含む行全体、列全体を削除する場合は、それぞれ次の書き方になります。

・行全体 Range(“A1”).EntireRow.Delete

・列全体  Range(“A1”).EntireColumn.Delete

「行全体」という意味のEntireRow、「列全体」という意味のEntireColumn というプロパティを使ってオブジェクトを指定し、Deleteメソッドで削除します。これもピリオドを「の」と読めば、各単語のおおよその意味から処理内容がわかるようになってきます。

データを並べ替える

たとえばD列の判定で昇順、C列で降順という優先順位で並べ替える場合を見てみましょう。

この処理をプロシージャに加えるには、次の処理を書きます(CurrentRegion.は省略可)。

Range(“A1″).CurrentRegion.Sort keyl: =”判定”, order1:=xlAscending,
key2:=”得点”, order 2:=xlDescending, Header:=xlYes

並べ替え操作の自動化には、Sortというメソッドを使います。ただ、Sortメソッドだけでは、「どの列を基準に並べ替えをするのか」そして「昇順なのか、降順なのか」がわかりません。

そのため、ここではkey、order、headerという3つの引数(=質問)を使います。

key:並べ替えの基準になる列を指定する

並べ替えの基準になる列を指定する引数は、keyです。指定方法は、「範囲名」かセル指定です。上記で紹介した処理は、範囲名で「判定」や「得点」などのように、並べ替える基準になる範囲の一番上のセルの値、要は項目名を使っています。

上記の例では、手作業で「並べ替え」機能を使う際のダイアログ画面との対応でわかりやすいようにと考え、項目名を使った書き方を紹介しました。しかしこの書き方だと、プロシージャ内に書いた項目名とシート上の項目名と一致しなかった場合にエラーが起こります。それでは不安定なの

で、以下のようにkeyの指定にはセル指定を使うことをおすすめします。

Range(“A1”). CurrentRegion. Sort key1: =Range(“D1”), order 1:=xlAscending,
key2:=Range(“C1”), order2:=xIDescending, Header:=xlYes

order:昇順か降順かを指定する

並べ替えの順序を指定する引数は、orderです。order:=(コロンイコール)に続けて、昇順か降順かを以下のように指定します。

昇順xlAscending

降順 xlDescending

header:先頭行をデータの見出しとして使用する

先頭行をデータの見出しとして使用するかどうかを、headerという引数で指定します。header:= (コロンイコール)に続けて、それぞれ以下のように指定します。

見出しとして使う場合 XİYes

見出しとして使わない場合 XINo

「keyl と orderl」「key2 と order2」というように、最優先するキー、次に優先するキーの設定をそれぞれ行っていきます。key3まで設定できます。

データ処理の定番「不要な行の削除」を自動化するには

「システムからダウンロードしたデータから、まずC列が“対象外”、またF列がゼロになっている行を削除する」

そんな「データから不要な行を削除する」という処理は、データ加工の定番としてよく行われます。このような処理には、データから特定のデータだけを抽出する作業に便利なオートフィルターを使って抽出→抽出された行を削除、という手順を踏みます。オートフィルターの処理をプロシージャに加えるには、Autofilterメソッドを使います。たとえば、以下のデータで、「判定」の列で、値がBのデータだけを抽出する場合を見てみましょう。

Range(“A1″).CurrentRegion. AutoFilter field:=4, Criteria1:=“B”

AutoFilterメソッドは、field、criterial という2つの引数を使います。それぞれ、「どの列でフィルターするか?」「抽出基準は?」という質問に相当します。

引数fieldは、データの何列目でフィルターするかを指定します。この場合、データの4列目(シートの4列目ということではありません)を対象にするわけですから、:=のあとに4と入力します。

引数Criterialは、fieldで指定した列で抽出条件を指定します。今回の場合、「B」という文字を抽出したいわけなので、”B”と指定しているわけです。

このようにオートフィルターで不要なデータを抽出

→抽出されたセルを削除

→オートフィルターを解除

この処理を1行ずつ加えてできたのが、次のプロシージャです。

Sub オートフィルターして削除( )
Range(“A1″).CurrentRegion.AutoFilter field:=4, Criteria1:=”B”
Range(“A1”).CurrentRegion.Offset(1, 0).EntireRow.Delete
Range(“A1”).CurrentRegion.AutoFilter
End Sub

Range(“A1”).CurrentRegionが3回同じものが繰り返し出てきてくどいですし、毎回書いたりコピペするのも面倒なので、With構文で次のように書き換えましょう。

Sub オートフィルターして削除()
With Range(“A1”).Current Region
.AutoFilter field:=4, Criteria1:=“B” ①
.Offset(1, 0).EntireRow.Delete ②
.AutoFilter  ③
End With
End Sub

① オートフィルターです。データ4列目にてBを抽出します。

②A1セルのアクティブセル範囲をそのまま削除すると、1行目の項目行も

削除してしまうことになるので、削除の対象範囲を1行下にずらした範囲のセルを行全体 (EntireRow) で削除します。

③オートフィルターがかかっている状態で、AutoFilterメソッドを引数なしで実行すると、オートフィルターが解除されます。

まずはWith構文のおさらいです。

①の「.AutoFilter field:=4, Criterial:=”B”」の場合、先頭のピリオド(.)

の手前に、Withのあとに書かれている「Range(“A1”).CurrentRegion」が省

略されています。つまり、これは以下と同じ処理だと解釈します。

Range(“A1″).CurrentRegion.AutoFilter field:=4, Criterial:=”B”

いきなりピリオド(.) から始まるコードや単語があったら、その手前に、Withのあとに書かれているオブジェクトを当てはめて読むのです。

ピリオドは、すべて「の」と読みますが、With構文の中の3行はいずれもいきなりピリオドから始まっています。この場合、

.AutoFilter

とだけ書かれてあるのを素直に読むと

・・・のAutoFilter

ということになり、「……の」って何? ということになりますね。

しかし、この「の」、つまりピリオドの手前にはRange(“A1”).CurrentRegionが省略されていると解釈することで、

「ああ、これはRange(“A1”).CurrentRegionのAutoFilter、ということか」と理解できるようになります。

②のコードでは、データ1行目の項目行のセルは削除する範囲から外すために、Offsetプロパティで対象範囲を1行下にずらした範囲のセルをDeleteメソッドで削除しています。

オートフィルタで抽出されたセル範囲を手作業で、つまりシート上でマウスの右クリックメニュー→削除を選択することで削除しようとすると、確認メッセージが出ることがあります。

②のコードにEntireRowを使わず、

.Offset(1, 0).Delete

だけの状態だと、このメッセージが出てしまって、[OK]をクリックしないと先に進まなくなってしまいます。それを回避するため、あらかじめ行全体を操作の対象として指定する役割を持つEntireRowという単語を追加することによって、この確認メッセージが出ないようにする工夫の1例です。

シートの処理を自動化する!シートを追加する処理の書き方

まずは、新規シートを追加する処理の書き方を見てみましょう。

Sheets. Add

【日本語訳】 シートの追加

これも、「オブジェクトメソッド」の文型です。ピリオド(.) は、やはり「の」と読めます。

Addメソッドを使っていますが、Add とは「追加する」という意味の動詞ですね。

オブジェクトは、「Sheets」というように最後にsがついて複数形になっていることに注意してください。

また、位置を指定してシートを追加することもできます。たとえば、「Sheet1」というシートの直後(右隣)に新しくシートを追加したい場合は、次のような処理を書きます。

Sheets.Add after:=Sheets(“Sheet1”)

【日本語訳] “Sheet1″シートの直後にシートを追加する

この処理が実行されると、Sheet1の直後(after)に新しいシートが追加されます。

Sheets.Add before:=Sheets(“Sheet2”)

【 日本語訳] “Sheet2″シートの直前にシートを追加する

シートを追加したい場合、さらに追加する場所も指定したいケースがありますよね。そこで、シートに対するAddメソッドには、「after」や「before」という引数が用意されているわけです。

追加したシートに名前をつける

追加されたシートは、手作業でシートを追加した時と同様、「Sheetl」や「Sheet2」という名前で追加されますが、任意の名前を設定する方法を紹介します。次の2行の処理は、追加したシートの名前を”data”に設定します。

Sheets. Add
ActiveSheet.Name = “data”

「Sheets.Add」でシートが1枚追加されますが、手作業でシートを追加する時と同様に、追加されたシートは必ず選択された状態、つまり「アクティブ」な状態になります。そのような「アクティブなシート」を指定するためのActivesheetというオブジェクトを使って、「その時アクティブなシートの名前をdataにする」ことができます。

「ActivesheetオブジェクトのNameプロパティの値をdataに設定する」という処理だと理解してください。

シートを削除する

シートを削除する処理の書き方も見ておきましょう。

Sheets(“data”).Delete

【日本語訳】”data”シートの削除

Sheets(“data”)というオブジェクトに対して、Delete メソッドを実行します。

ただ、シートを削除するときは、手作業の場合でも必ず次のアラート画面が出てきて、「削除」を押さないとシートを削除することができません。

複数のシートを削除する処理を自動化する際、マクロを実行すると何度もこの確認画面が出てきて、その都度「削除」をクリックしなければならなくなってしまいます。そこで、シートの削除処理を含むプロシージャを作る際は、次の処理をプロシージャの先頭に書いて、このようなアラートが出ないようにしておくのが定石です。

Application.DisplayAlerts = False

このコードがプロシージャの先頭にあれば、そのプロシージャの実行中はもうアラートが出なくなります。

「これを書いたら、プロシージャの最後にApplication.Display Alerts =Trueを書くべきか?」

という質問が浮かびますが、回答としては「書いても書かなくても結果は同じ。だから書かなくても問題ないけど、心配だったら書いておいても問題はない・・・という程度です。

シートをコピーする

シートのコピーについては若干注意が必要です。シートのコピーにはCopyメソッドを使いますが、引数を指定するかしないかで実行結果が変わります。たとえば

Sheets(“Sheet1″).Copy

というように、Copyメソッドだけ単独で書いて引数を書かない場合、新規ブックが自動的に作成され、”Sheet1″のコピーはそのブック内に作られます。

そうではなく、同一ブック内にシートのコピーを作りたい場合は、以下のように、引数beforeかafterでコピーが作成される場所を指定する必要があります。

Sheets(“Sheet1”).Copy after:=Sheets(Sheets.Count)

【日本語訳) “Sheet1″のコピーがブックの一番後ろに作られます。

Sheets(“Sheet1”).Copy before:=Sheets(1)

【日本語訳] “Sheet1″のコピーがブックの先頭に作られます。

「その都度入手するブック対して毎回同じ処理を行う」
解決策

「ファイルを開いては閉じ、開いては閉じの繰り返し……あと何個ファイルがあるんだ……」

「社員全員分の勤怠入力ファイルの作成で小1時間かかるし、まちがえるし、もうイヤ.……」

「Excelで請求書を何枚もつくる単純作業、絶対マクロで済むと思うんですけど、どうすれば……」

実際の苦しみの声です。ではいよいよ、「この面倒なExcel作業をどうにか効率化したい!」というとき、「その作業はこうして自動化する!」というプロセスを把握していきましょう。そのために、実際の事例を参考に、具体的にどのように考え、どのような手順で自動化ツールを作成し、そして日々使っていくのかを具体的に見ていきましょう。

カギとなるのは「別のブックを開く」という処理

ここまで紹介してきたプロシージャは、すべて1つのブックの中で処理が完結していました。つまり、処理するデータと、処理を実行するプロシージャが同じブックの中にあったということです。しかし現実には、定期的に他人から受け取るブックや、ダウンロードするブックなどを処理することが多いわけです。

そのように定期的に発生する「その都度入手するブックに対して毎回可じ処理を行う」といったルーティン作業において、それらのブックをその処理のたびに開き、標準モジュールを挿入し、その処理を自動化するプロシージャを書いて実行する、というのは実用的なマクロの活用方法ではありません。

しかも、複数のブックを処理することもありますよね。ブックの数が増えてくると、とてもそんなことはやってられません。

この問題を解決するために、次のようなプロセスの考え方が必要になります。

1.定型業務を自動化するためのプロシージャが保存されている「マクロ実行ブック」を用意する。

2.その「マクロ実行ブック」によって、処理したいブックを開いて処理する。

この「マクロ実行ブック」に作成するプロシージャに必要になるのが、「別のブックを開く」という処理です。ブックを開く、または閉じる処理の書き方についても覚えていきましょう。

まずは基本から~既存ブックを開く処理の基本構文

次のような状況で説明します。

デスクトップに「データ追加作業フォルダ」というフォルダがあります。

その中に「実行.xlsm」ファイルと「操作対象ブックフォルダ」があります。

この「操作対象ブックフォルダ」の中の「saisoku.xls」を処理するプロシージャを「実行.xlsm」の標準モジュールに作成していきます。

まず、ブックを開く処理の書き方の基本構文を押さえましょう。

Workbooks.Open Filename:=開きたいブックの保存場所¥開きたいブックの名前

ブックを開く処理には、Workbooks.Openというメソッド文を使います。

引数Filenameのあとにコロンイコール (:=)をはさんで、開きたいブックの保存場所と、開きたいブックの名前を¥マークでつないだ文字列(この文字列をフルパスといいます)を書きます。「データ追加作業フォルダ」がパソコンのデスクトップにある状態の場合だと、次のような書き方になります。

Workbooks.Open Filename: =
“C:\Users\ichigeki\Desktop\データ追加作業フォルダ単操作対象ブックフォルダ\202004061121.xls”

この例の場合、「C:\Users\ichigeki\Desktop\データ追加作業フォルダ\操作対象ブックフォルダ」の部分が「開きたいブックの保存場所」にあたります。

そして、開きたいブックの名前は「202004061112.xls」です。

この2つを¥マークでつないだ文字列であるフルパス、つまり

“C:\Users\sugoi_kaizen\Desktop\データ追加作業フォルダ\操作対象
ブックフォルダ\201904061121.xls”

という文字列が、引数Filenameのあとに書いてあるわけです。文字列なので、ダブルクオーテーション(“)で囲んであります。

ややこしく見えますが、記号の訳し方さえ押さえればとてもわかりやすいものです。

: (コロン) → 「ドライブ」と訳します。
¥「~の中の」と訳します。

これをそのまま当てはめて読んでいくと、ここで出てきたフルパスは次のように訳せます。

【Cドライブの中の、Usersの中の、sugoi_kaizenの中の、Desktopの中の「データ追加作業フォルダ」の中の、「操作対象ブックフォルダ」の中の、201904061121.xls】
Workbooks.Openのあとに指定した、このブックを開けと言ってるわけです。

「Cドライブって何?」とわからなくても実務で困ることはあまりありませんが、あくまでも参考までに解説しておくと、パソコンの中にあるハードディスクの名前の1つだということです。複数あれば、Dドライブといった名前になることもあります。

その「C:」の中に「Users」というフォルダがあり、その中に「sugoi.kaizen」というフォルダがあり、その中に「Desktop」というフォルダがあり(デスクトップも1つのフォルダといえます)、その中に「データ追加作業フォルダ」というフォルダがあり、その中に「操作対象ブックフォルダ」というフォルダがあり、その中にある 201904061121.xlsというブックを指定する文字列だということです。要は、いずれかのブックを開くには、そのブックの名前だけでなく、そのブックの保存場所も合わせてWorkbooksOpenのあとに書かなければならないという基本をここでは理解してください。

各ブックの保存場所は、ブックのアイコンを右クリックして出てくるメニューから「プロパティ」を選択して出てくる画面の「場所」で確認できます。

「そんなやり方じゃ実務では使いものにならない」理由

先ほどの書き方は実用的ではありません。

それは、この書き方では「データ追加作業フォルダ」の保存場所が

「C:\Users\sugoi_kaizen\Desktop」である場合しか使えなくなってしまうからです。「sugoi_kaizen」のように、パソコンによって変わってしまうフォルダの名前が含まれているので、このような保存場所を固定的に指定する方法だとこの「データ追加作業フォルダ」が特定のパソコンの常に同じ場所に置いてある状況でないと使えなくなってしまいます。

せっかく作った自動化ツールは、Excelさえ入っていればどのパソコン上でも動くようにしておかないと困ります。パソコンの機種変更や他人にその自動化ツール一式を渡して使ってもらう場合に対応できなくなるからです。

また、もっと大事な問題として、開きたいブックの名前が毎回同じであることは少ないのが実務の現実です。

実際にWebやシステムからダウンロードするCSVなどのファイルは、多くの場合、その名称が「202004061121.xls」などのように、日時などの情報を含むため、常に変わるケースが多いのです。そうした現場では、このような固定的な保存場所やブック名の指定では融通が利かず、使い物にならないのです。

自動化ツールをまとめるフォルダ構成

では、どうするか。まず、Excel作業の自動化ツール一式は、1つのフォルダにまとめて使っていくのが基本です。その中に、いわゆる「マクロ実行ブック」……つまり、標準モジュールを挿入し、そこに一連の処理を自動化するためのプロシージャを作って保存しておくためのブックをまず作ります。

そして、開かれて処理されるブックと、その「マクロ実行ブック」が常に同じ位置関係になるようにフォルダ構成を工夫します。

ですから、通常Excelのマクロでルーティン作業を自動化するツールを作ろうという際は、まず真っ先に、デスクトップにフォルダを1つ作ることから始まる次の手順を踏みます。

①デスクトップにフォルダを1つ作る。

デスクトップで右クリック→右クリックメニューから「フォルダー」を選択します。フォルダ名は任意のもので大丈夫です。あとから変更しても問題ありません。一連の処理に必要なすべてのブックやフォルダ一式を この中に入れて、デスクトップ以外の場所やほかのパソコンに移しても使えるように工夫します。

② そのフォルダの中で、マクロを実行するExcelブックを1つ作る。

フォルダの中で右クリック→右クリックメニューから「Microsoft Excelワークシート」を選択します。このブックも任意の名前をつけてください。

あとから変更しても問題ありません。ただし、ファイル形式が「マクロ有効ブック(.xlsm)」である必要があります。

③さらにそのフォルダの中で、開かれて処理されるブックを入れるためのフォルダを作成する。

このフォルダの名前は任意のものでかまいませんが、プログラムに影響するため、このフォルダの名前は変更不可になります。ここでは「操作対象ブックフォルダ」という名前にしておきます。

このようなフォルダ構成をまず基本として整えます。

固定的な保存場所名を使わず、その都度調べる書き方

この構成のもと、「実行ブック」の標準モジュールに作成するプロシージャにより、「操作対象ブックフォルダ」の中にあるブックを開く処理を考えてみましょう。

まず、開くブックのフルパス(保存場所を含めてブック名を指定する文字列)ですが、先ほどのようにUsers と かsugoi kaizen と いった固定的なフォルダ名を使わず、「このブック (実行ブック xlsm)の保存場所」をその都度調べる書き方があります。

「ThisWorkbook.Path」

という書き方です。

これを使うことで、この「データ追加作業フォルダ」がどこに保存してあっても問題なく使えるようになります。

Workbooks.Open Filename:=ThisWorkbook.Path & “\操作対象ブックフォルダ\201904061121.xls”

【日本語訳】 ブックのオープン。対象ファイル名は、このブックが入っているフォルダの中の、「操作対象ブックフォルダ」の中の、201904061121.xls

ThisWorkbookというのは、「このブック」と訳したように、実行するプロシージャが含まれている、マクロを実行するブック自分自身のことを意味します。Pathは「保存場所」と訳してもいいのですが、より具体的なイメージがわかりやすくなるように「フォルダ」と訳して説明しています。

ピリオド(.)は「の」と訳してきましたが、「このブックのフォルダ」では日本語として理解しづらい恐れがあるので、ここではThis Workbook.Pathを「このブックが入っているフォルダ」と訳して説明しています。

この処理を含むプロシージャが入っているブックがどこにあろうと、”This Workbook.Path”がその「現在位置」を取得する「値の取得」の処理を行います。この書き方を使うことで、「データ追加作業フォルダ」をどこに移動しようと使えるプロシージャになるのです。

いちいち開きたいブックの名前の指定などしていられない

ただ、この書き方でもまだ、「開きたいブックの名前」が「201904061121.xls」という固定的なブック名での指定となっています。これだと、開きたいブックの名前が変わるたびに、プロシージャの該当部分、つまり「開きたいブック名」の指定部分を毎回正確に書きかえなければなりません。

では、どうするか。「開きたいブックの保存場所」を

ThisWorkbook.Path

という処理でその都度取得する処理にしたように、「開きたいブックの名前」もその都度取得する仕組みにすることで、この問題を解決します。そのために、わざわざ「操作対象ブックフォルダ」を用意して、その中に開きたいブックを入れる、というフォルダ構成にするのです。その開きたいブックの名前が何であろうと、「操作対象ブックフォルダ」の中に入ってきえいれば、そのブックを確実に開けるように工夫します。これが、「操作対象ブックフォルダ」というフォルダ名は変えてはいけない理由です。

この仕組みを作るために、まず「操作対象ブックフォルダ」の中に入っているブックの名前をその都度調べて変数に入れる処理を加えます。その変数を、Workbooks.Openのあとに書くブック名の指定に使うのです。

ブックの名前を調べるこの処理

【Dir関数】

変数 = Dir(名前を調べたいブックのフルパス)

Dir関数は、カッコの中にフルパスで指定したブックの名前を調べて変数に入れる関数です。指定したブックがもし存在しなかったら、変数は空白のままになります。

既存の別ブックを開く処理の正しい手順の最終的な結論となる書き方が次のものです。

Dim bookname As String
bookname = Dir(ThisWorkbook.Path & “\操作対象ブックフォルダ\”)
Workbooks.Open Filename: ThisWorkbook.Path\操作対象ブックフォルダ\” & bookname

開きたいブックの名前を入れる変数として、文字列型の変数booknameを宣言しています。

「操作対象ブックフォルダ」の中に入っているブックの名前を変

数booknameに入れています。この変数booknameを次の手順で

Workbooks.Openの「開きたいブック名」として使うのです。これが、「開きたいブックの名前はその都度変わる。いちいち開きたいブックの名前など指定していられない」という問題を解決する工夫です。

開きたいブックの名前を入れた変数booknameを使って、Workbooks.Openというメソッド文によりそのブックを開く処理になっています。

1. 開きたいブックを入れるための専用のフォルダを用意する。
2.その中に開きたいブックを1つ入れる。
3. Dir関数で、そのブックの名前を変数に入れる。
4.その変数を開きたいブックの名前として使う。

これが、実務で本当に使い物になる「ブックの開き方」の仕組みです。

この時、もし「操作対象ブックフォルダ」の中に何もブックがなければ、変数booknameには何も値が入らないので、エラーになって処理が止まります。

また、「操作対象ブックフォルダ」の中に入れるファイルは1つだけにする、というルールを前提にしています。

これで、フォルダがどこに移動しても使えるようになります。

ただし、メール添付で送るなどした場合、圧縮されメールに添付されているままの状態で開いてもうまく使えません。一度デスクトップなどに置いて、かつZIPファイルなどになっている場合は展開し、圧縮されてない普通のフォルダとしてデスクトップなどに置いてある状態で使う必要があります。

ここで改めて、面倒なExcelによるルーティンワークを自動化する際の具体的な手順をまとめます。

① デスクトップにフォルダを1つ作る。

このフォルダの中に、作業に必要なファイルやフォルダ一式を入れ、仕組み化された1つのパッケージとして使っていくことになります。

② その中に新規Excelブックを1つ作る。

このブックを開いて標準モジュールを挿入し、作業を自動化するプロシージャを作ります。

これが「マクロ実行ブック」になります。作業を自動化するマクロを使うときはこのブックを開き、マクロを登録したボタンをクリックして作業を一瞬で終わらせることになります。

③ 必要に応じて、処理するブックを入れるためのフォルダなどを用意する。

完成したツールの使い方として、処理したいブックは所定のフォルダに入れてからマクロを実行するなどの使用上のルールが必要になります。

極論すると、Excel作業自動化ツールの開発手順の最初の一歩は「フォルダを1つ作る」、ここからスタートするということです。そして、その中にどのようなブックやフォルダを設置するかという「フォルダ構成を整える」という発想の重要性を強調しておきます。

終わりに

最初から細かい文法知識までマスターする必要はありません。しかし基礎は大切です。その全体像だけでも押さえておくだけで知識が整理されスムーズな録画の活用につながっています。

単語分類と解説

【オブジェクト】

Excelでの処理における操作対象。Excelの操作対象といえば、セルやシート、ブックですね。以下のような単語で指定することができます。

・セル→Range、Cells

・シート→Sheets, ActiveSheet

・ブック →Workbooks. ActiveWorkbook

Excelそのものを指す言葉として、Applicationという単語もあります。これら、操作対象を指す単語を総称して「オブジェクト」といいます。

【プロパティ】

たとえば、セルについての情報は、中に入っている「値」や文字の「色」、「サイズ」など、さまざまな種類があります。そのような情報の種類を指定する役割のValueやColor、Sizeなどの単語を総称して「プロパティ」といいます。

【メソッド】

動作命令を行う単語で、英単語の動詞が使われます。Add、Delete、Open、ClearContents、Sortなどです。

【ステートメント】

具体的な操作を行うステートメントとしてはフォルダを作るMkdirやファイルを移動するNameなどがありますが、ほかにも

・変数宣言のDim

・オブジェクトを変数に入れるSet

・別のプロシージャを呼び出して実行するCall

など、コードの先頭にほかの単語とピリオド(.) で接続されずに書かれる単語は「ステートメント」という分類に入ります。サンドイッチ構文、たとえばFor Next構文のForやNextも、それぞれForステートメント、Nextステートメントという分類になります。

なお、プロシージャの先頭にあるSubという単語も、Subステートメントという単語として説明されます。また、プロシージャの先頭はSubばかりでなく、他人が作ったプロシージャでPrivateやPublic、Friend、Static、Functionなどから始まるものに出会うこともあるかもしれません。

【値(あたい)】

値とは、数値や文字列のことです。また、その数値や値を作るための数式や関数なども、ここでは「値」と位置づけます。

セルに入力するデータや塗りつぶしの色、シートの名前、データの行数など、さまざまな「値」を指定しながら処理をプロシージャに加えていく際に使われるものです。

【数値】

0、1、2、3……といった数値です。そのままプロシージャ内で入力することができます。

【文字列】

数値以外の漢字、ひらがなカタカナ、アルファベットなどからなる文字です。プロシージャ内で入力する際は、ダブルクオーテーション(“)で囲んで入力します。

【関数】

関数名の前に WorksheetFunctionという単語をつける関数と、つけない関数の2種類があります。また、MsgboxやInputBoxなどの関数もあります。どのような関数があるかについても、Webサイトで紹介しています。

【変数】

さまざまな値を一時的に入れておくことのできる文字。自由な文字列を使用できます。標準モジュールの先頭にOption Explicitという1文があると、その標準モジュール上ではDimステートメントによって「変数の宣言」をした変数しか使えなくなります。この設定により、変数の入力ミスによるエラーの原因を容易に発見できるようになります

【定数】

vbRedやxlUp、xlAscendingなどがありますが、先頭にvbやxlとついた単語を「定数」と分類します。これらは、本来は数値で指定するものなのですが、数値では意味がわかりづらいので、いくつかこのように意味がわかりやすい単語がVBAに用意されています。

ちなみに、色について用意されている定数には以下があります。

• vbBlack

• vb Red

•vbGreen

• VbBlue

• vb Yellow

• vb Magenta

• vbCyan

• vbWhite

色の三原色である赤、青、緑の3つと、印刷で使われる4色(CMYK……シアン、マゼンタ、イエロー、ブラック)の4つ、それと白が用意されているわけですね。

変数と同じように、定数は自分で作ることもできます。Constという単語を見かけたら、それは定数を宣言しているものだと読みときます。たとえば、

Const i = 100

という処理は、「定数iを宣言して100を入れる」という処理です。つまり、定数iを100として使うわけです。

変数との性質の違いは、この定数iの中身を変えられないことです。「変数だって、1回100を入れて、そのあと別の値を入れる処理を書かなければいいんじゃないの?」という指摘はまさにそのとおりで、自分でプロシージャを作る際にはこれを知らなくても困ることはありません。ただ、意味はわかるようになっておきたいものです。

【値の取得】

値を取得する以下のような処理

・シート数を調べるSheets.Count

・データの最終行数を調べるCells(Rows.Count, 1).End(xlUp).Row

・プロシージャを実行しているブックの保存場所を指す      ThisWorkbook.Path

これらのように、プロシージャの実行時点における何らかの値をその都度調べて「値」として使うフレーズを「値の取得」という分類で整理します。

【論理值】

TrueとFalseの2つは、「論理値」という分類で整理されます。

「処理」の文型の3種類

1、値の設定文

【文型】

オブジェクト.プロパティ = 値

※セルへの入力、セルの書式設定、シート名の変更など

2、メソッド文

【文型】

オブジェクト、メソッド

またはオブジェクトメソッド 引数:=値

※セル・シートの挿入や削除、ブックを開くまたは閉じる、セルの値を消去など

3、ステートメント文

【文型】 ※Mkdirの場合

Mkdir フォルダを作りたい場所¥作りたいフォルダの名前

※フォルダの作成・削除、ファイルのコピー、削除、移動、名前の変更などができます。

6つのサンドイッチ構文

【構文名】と【機能】

・For Next 

反復(通常の繰り返し処理。DoLoopとの使い分けは好みの問題)

・DoLoop 

反復(ファイル名一覧を作るときに必須)

・For Each 

反復(必須度は低いが、読めるようにはなっておきたい)

・If Then分岐

 (2つまでの分岐)

・Select Case 

 分岐 (3つ以上の分岐はぜひこれで)

・With

オブジェクトの一括指定

いろいろな記号

四則演算

これはセルでの数式と同じです。+、-、*、/ の4種類です。

=(イコール)

これは2通りの使われ方があります。

1.「値の設定文」での使用

→この場合は、「右辺の値を左辺に代入する」という「代入演算子」としての機能になります。

2. If Then構文での条件式での使用Like演算子

If Then構文の条件式が部分一致の場合は、イコールの代わりにこの

Likeという単語を使います。

:= (コロンイコール)

メソッドの引数を設定するのに使います。ここで普通のイコール 

を使ってしまわないように要注意です。

&(アンド)

結合演算子。入力の際、前後には必ずスペースを入れます。

. (ピリオド)

オブジェクト、プロパティ、メソッドの各単語をつなげる記号です。「の」と訳して読むと、処理内容がわかりやすくなります。

,(カンマ)

メソッドの引数が複数ある場合、カンマで区切って入力します。関数の引数が複数ある場合にも、その区切りに使います。

 _ (スペース+アンダーバー)

プロシージャの中で改行するときに使います。

‘(シングルクオーテーション)

コメントを書くとき、またはプロシージャの一部をコメント化する際などにも使います。

“ (ダブルクオーテーション)

文字列は、ダブルクオーテーションで囲んで入力します。

なぜ、顧客データExcelは管理しづらくなるのか?

「顧客データをExcelで管理しているのですが、たくさんの項目をシートに横方向に入力していく作業がやりづらくて。それに、電話番号などを確認したい時に、一覧性が悪くて使いづらいです。」

このような悩みは完全に自作してしまえるぐらいかんたんなものであることも多いです。

データを「入力する場所」と「保存する場所」は分ける

こうした際におすすめしたいのが、以下のような入力フォームを作ることです。

Excelのマクロ機能を駆使すれば、このようなオリジナルの入力フォームを作ることができます。これを「ユーザーフォーム」といいます。これを使うと、入力がしやすくなるだけでなく、顧客情報をこのユーザーフォームに呼び出すことも可能になり、顧客データの確認などの際にも一意性がよくなるという効果があります。

入力作業はこのフォームを使って行い、最後に [OK]ボタンをクリックすると、その内容が別途用意したExcelのシートにデータベースとして保存されていきます。

このように、「データを入力する場所と保存する場所を分ける」というのが、データを使いやすい環境を作る第一歩になります。