ワンランク上のExcel VBAプログラミング｜電子書籍出版

2022/9/15Update

Amazonレビューで頂いた指摘を踏まえ、誤字脱字はもちろんのこと解説文も全て読みやすいように修正しました。
========================================================

電子書籍の出版を致しました。その題名は【ワンランク上のExcel VBAプログラミング】です。

このブログでは入門レベルから、メールの配信やシェアポイントへのアップロードと言った上級者向けまで記事を公開しています。しかし、付いたコメントはほとんどが上級者向けの記事に対する質問です。つまり、私のブログを見て下さっている方は、繰り返しとか条件分岐の解説は今更求めていないわけですね。

その事に気づいたとき、私が昔から思っていたことを思い出しました。VBAは（に限らずですが）入門書や初心者向けは腐るほど本もネットも情報があふれているのに、上級者向けがないよね、と。

ここで言う上級者はすごい人と言う意味ではなく、それなりに自由自在にプログラミングできて自動化ツールも作れるという人です。静的配列書けたら立派に上級者だと思います。クラスモジュールでPropertyプロシジャ書けたら初段～二段とか、上には上がいます。配列レベルですね。

そんな中で、「繰り返しとかIf文とかは完璧だぜ」とか「配列まで分かってるぜ」と言う人に、その次の本を渡したいという思いで書きました。

VBAは上級者と初段の壁が厚すぎる。

配列を使いこなしてから、クラスモジュールを使えるまでの敷居が高いんですよ。その解説は難しすぎるか英語かどちらかです。だから、オブジェクトの本質から理解していこうと掘り下げたのが本書です。

配列まで分かっている人に、Workbooks.Addはメソッドという話に戻ってもらうのは勇気の要る作業でしたが、オブジェクトとは何なのかを語るにはかえって近道なのではないかと思います。

VBAでオブジェクト指向の入口に立つための一冊。
Amazonでの検索は「ワンランク上のExcel VBAプログラミング」でどうぞ。

ワンランク上のExcel VBAプログラミング: クラスとオブジェクトを身に付けるハンズオンガイド

VBA FileDialogオブジェクト｜フォルダをユーザーに選択させる

VBAで自動化ツールを作成する場合に、ユーザーに成果物を保存する場所やファイル名をしてもらいたい場合があります。そんなときに便利なダイアログボックスを表示する、FileDialogオブジェクトを解説します。

なお、サンプルコードが一部横に長くなっており、スマホでの閲覧の場合はスマホを横にしてもらえると比較的快適にサンプルコードを閲覧することができると思います。

FileDialogオブジェクトでフォルダをユーザに選択させる

あるエクセル作業の後工程がさらなる加工の元データになっている場合などは、エラー回避の為に人の手を介さずに全自動にするのが良い場合があります。そのような場合は成果物の保存の際のファイル名も固定の文字列にDateSerial関数で加工した文字列などをフルパスにするなどの工夫が奏功します。

一方で、後工程が人による確認作業の場合は、ユーザーが自由に成果物の保存場所やファイル名を指定できる事が望ましい場合もあります。今回はこのような場合にユーザーにファイル名や保存先のフォルダを指定させるためのダイアログボックスが簡単に表示できるFileDialogオブジェクトを解説します。

FileDialogオブジェクトの文法

それではさっそくFileDialogオブジェクトの文法を見ていきましょう。書き方に入る前にそもそもオブジェクトとは何かを説明しておきます。オブジェクトが何かと言うことを理解しておくことは今回だけでなく、今後も様々な作業をプログラムをする中で非常に重要です。オブジェクトがわかっていないとただサンプルコードをコピペするしかできない人になってしまいます。

そもそもオブジェクトとは何か

オブジェクトはモノと言う意味ですが、ここではオブジェクトとは機能やデータを束ねた一塊のモノです。例えばエクセルにはワークシートというものがありますが、このシートの集まりを表すSheetsはオブジェクトです。SheetsにはAddという機能があります。Addという機能を使うとSheetsというワークシートの集まりに新たなワークシートを追加します。また、Deleteという機能を使うとSheetsというワークシートの集まりのうち、指定したワークシートを削除します。また、Sheets（1）のようにインデックス番号をつけることによって、ワークシートの集まりから特定のワークシートを指定することができます。この指定したワークシートに対して、Sheets(1)
.Tabには１番目のワークシートの名前を表す値が入っています。

上記の例で紹介したAddやDeleteと言ったオブジェクトに関する機能をメソッド、Tabの様にオブジェクトに関するデータを格納する部分をプロパティと言います。

このように、複数の機能やデータを束ねたものをオブジェクトと言います。そして、オブジェクト.メソッドやオブジェクト.プロパティの形でそのオブジェクトに関する機能やデータを取り出すことができるというのがオブジェクトの正しい使い方であり、理解です。

この部分は、クラスモジュールを理解する時に必要になりますので、頭に入れておきましょう。

FileDialogオブジェクトの書き方

オブジェクトの意味が分かったところで、本題のFileDialogオブジェクトの書き方についての解説に移ります。きちんとオブジェクトの意義が分かっていれば、Sheets.AddやSheets.Tab＝”～～”と同じような感覚で身に着けることができます。しかし、FileDialogは同じ名前のプロパティとオブジェクトがあるので混乱しやすくなっています。この際、きっちり理解しておきましょう。

FileDialogオブジェクトの文法は以下のようになっています。

FileDialogオブジェクトを取得する文法

ApplicationというのはOfficeの各アプリケーションです。このブログではエクセルVBAについて解説しているので、今回はApplicationはエクセルのソフトそのものを指します。そして、そのエクセルが持っているFileDialogプロパティを使います。書き間違いではありません。FileDialogプロパティです。

つまり、エクセルと言うアプリケーション(=ソフト)が持っている、FileDialogと言う種類のデータに開きたいダイアログボックスの種類を設定すると、その種類のダイアログボックスが開くということです。

一番重要な概念のところの解説が終わりました。続けて具体的なコードを見ていきます。

フォルダを選択する｜msoFileDialogFolderPicker

実際のサンプルコードを見ていく中で理解を深めていきましょう。まずはユーザーにフォルダを選択してもらうダイアログボックスを出す手順を順に説明していきます。

Sub フォルダ選択ダイアログボックスを表示する()

    Application.FileDialog(msoFileDialogFolderPicker).Show
    
End Sub

上のサンプルコードを実行すると、ユーザーにフォルダを選択させるダイアログボックスが表示されます。FileDialogプロパティの引数にmsoFileDialogFolderPickerを渡しています。msoFileDialogFolderPickerはフォルダ選択ダイアログボックスを意味する定数です。

よって、Application.FileDialog(msoFileDialogFolderPicker)の部分がフォルダ選択ダイアログボックスのオブジェクトを意味します。あのダイアログボックスそのものだと思って構いません。.Showと書いてあるので、そのダイアログボックスを表示させなさいという意味です。

これではダイアログボックスを表示しただけなので、選択されたフォルダのパスを取得するように変更します。

Sub 選択されたフォルダのパスを取得する()
    
    Dim path As String

    Application.FileDialog(msoFileDialogFolderPicker).Show  ' ダイアログボックスを表示する
    path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)  'パスを代入する
    
    MsgBox "選択されたパスは" & path & "です。"
    
End Sub

ユーザーが選択したフォルダのパスを代入するためにpathという変数を用意しました。パスを代入している行に記述されているSelectedItemsプロパティの引数に１と記述されていますが、この１についてはこのオブジェクトを使ってファイルを選択する時にのみ関係します。フォルダの選択の場合は必ず１しか使わないと思って構いません。Selectedは英語で選択された、と言う意味なのでSelectedItemsでユーザーが選択したフォルダを意味するということです。

これでユーザーが選択したフォルダのパスが取得できたので、あとはファイルを保存する時に使ったり自由に使えばよいということになります。

フォルダ選択プログラムのエラーをトラップする

フォルダ選択ダイアログボックスはユーザーが【キャンセル】ボタンや右上のバツボタンを押して閉じることができます。前項のプログラムではキャンセルやバツボタンが押されるとエラーとなります。このエラーへの対応を行っておきましょう。

実は.Showは戻り値を持つのでその値を調べることで条件分岐ができます*1。

パスが取得できなかった場合、Showメソッドは0を返します。一方、正しくパスが取得できた場合は-1を返します。これを踏まえてエラーをトラップすると以下のようになりま。

Sub エラー対策済みフォルダ選択()
    
    Dim path As String
    Dim n As Long

    n = Application.FileDialog(msoFileDialogFolderPicker).Show ' ダイアログボックスを表示する
    
    If n = 0 Then
        MsgBox "処理を中断します。"
        End
    Else
        path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)  'パスを代入する
    End If
    
    MsgBox "選択されたパスは" & path & "です。"
    
End Sub

フォルダ選択プログラムのブラッシュアップ

これで一通りの機能が実装できました。それにしてもApplication.FileDialog(msoFileDialogFolderPicker)の部分があまりに長いので、これを変数に代入することで見やすくします。

Sub 変数を使って見やすくする()

    Dim path As String
    Dim n As Long
    Dim fd As FileDialog ' fdはファイル・ダイアログの略
    
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    n = fd.Show
    
    If n = 0 Then
        MsgBox "処理を中止します。"
        End
    Else
        path = fd.SelectedItems(1)
    End If
    
    MsgBox "選択されたパスは" & path & "です。"
    
End Sub

そして、共通するオブジェクトについては、With-End Withでまとめると見やすくなるととてもメンテナンス性が上がります。

Sub フォルダを選択させる最終形()

    Dim path As String
    Dim n As Long
    Dim fd As FileDialog ' fdはファイル・ダイアログの略
    
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fd
        n = .Show
        If n = 0 Then
            MsgBox "処理を中止します。"
            End
        Else
            path = .SelectedItems(1)
        End If
    End With
    
    MsgBox "選択されたパスは" & path & "です。"
    
End Sub

公式ドキュメントや参考書はこのように順を追わずに最後のサンプルだけ見せるのでわかりにくなっていますが、この記事でより多くの人がオブジェクトの考え方とフォルダを選択させるFielDialogオブジェクトに親しんでいただければと思います。

＜ゆんの電子書籍＞

ワンランク上のExcel VBAプログラミング: クラスとオブジェクトを身に付けるハンズオンガイド

はじめてのアメリカ駐在　生活立ち上げがこれ１冊でまるわかり: 渡米前の準備から渡米後の帯同家族の受け入れまで実体験を徹底解説！

*1:クラスモジュールでオブジェクトを自作するとわかりますが、オブジェクトが持つ機能はSubプロシジャかFunctionプロシジャで実装されます。Functionプロシジャで実装した場合は戻り値を返すことができます。つまり、.ShowメソッドはFunctionプロシジャで記述された機能だったのです

VBA GetOpenFilenameメソッドの使い方

VBAで自動化ツールを開発するときに使い勝手の良いユーザーにファイルを選択してもらうGetOpenFilenameメソッドについて解説します。

目次

GetOpenFilenameメソッドでファイルをユーザーに選択させる

操作するファイルの対象をユーザーに選択させるファイル選択ダイアログボックスを表示するGetOpenFilenameメソッドを解説します。GetOpenFilenameと言ってもこのメソッドはダイアログボックスを開いてユーザーが選択したファイルのフルパスを取得するのみで開くところまで自動で行うわけではありません。次項から解説していきます。

単にファイルを開く方法の復習

まず、ファイルを開く方法について復習しておきます。エクセルブックを開くにはWorkbooksクラスのOpenメソッドを使います。サンプルコードは以下の通りです。

Sub ファイルを開く例()

    Workbooks.Open Filename:="..." ' エクセルブックの場合
    
End Sub

上のサンプルで"..."に当たる部分を取得するのがGetOpenFilenameメソッドの目的です。"..."の部分はフォルダのパスとファイル名を結合したいわゆるフルパスと呼ばれる文字列が入ります。

GetOpenFilenameメソッドの文法

GetOpenFilenameメソッドの文法は以下のようになっています。

GetOpenFilenameメソッドの文法

ファイルのフルパスを代入する為の変数を用意しておき、GetOpenFilenameメソッドで取得したフルパスを代入するようにしておきます。GetOpenFilenameメソッドにおいて知っておくべきは第１引数と第３引数だけです。他の引数を覚える必要はありません。第１引数だけは大事なので良く覚えておきましょう。

なお、引数は【ひきすう】と読み、渡す値という意味です。GetOpenFilenameメソッドにおいては第１引数にFileFilterとして文字列を引数にとり、第３引数にTitleとして文字列を引数にとる、と言う言い方をします。

GetOpenFilenameメソッドの２つの引数｜FileFilterとTitle

前項で説明したようにGetOpenFilenameメソッドでは第１引数のFileFilterと第３引数のTitleが重要です。順に解説します。

第１引数FileFilter

第１引数のFileFileterは文字通りユーザーがファイルを選択する際にフィルタをかける機能です。ユーザーが選択できる拡張子を限定して、予期せぬファイルが選択される確率を減らす事ができます。書式は以下のようにします。

GetOpenFilenameメソッドの第１引数の書式

慣れない記述なので気合で覚えてしまいましょう。まず、文字列で指定するので””（ダブルクォーテーション）が囲みます。そしてその中身はファイルの種類の説明, *.xlsxの様にファイルの種類の説明と拡張子の先頭にアスタリスクを付けたものをカンマ区切りにします。サンプルコードを掲載すると以下のようになります。

Sub 第1引数しかない例()
    
    Dim フルパス As String
    フルパス = Application.GetOpenFilename("元データ,*.xlsx")
    
End Sub

このプログラムを実行すると、ファイルを開くダイアログボックスが表示されます。ダイアログボックスの下の部分を見ると下図のようになっています。

第１引数にファイルの説明と拡張子を指定した例

開く、キャンセルの２つのボタンの上に元データ（*.xlsx）と表示されています。ここの部分が第１引数で指定した部分です。「元データ」の部分は「売上データ」なり「商品マスタ」なりユーザーにとってわかりやすい実際のデータの種類を書けばOKです。

また、第１引数はダブルクォーテーション内に【ファイルの説明,*.拡張子】の組み合わせをカンマ区切りで複数記述することによって、複数の拡張子を指定することも可能です。

Sub 複数種類の拡張子の例()
    
    Dim フルパス As String
    フルパス = Application.GetOpenFilename("元データ,*.xlsx,古い元データ,*.xls")
    
End Sub

このプログラムを実行するとファイルを開くダイアログボックスが表示されます。その下の部分に着目すると以下のようになっています。

ファイルの説明,*.拡張子　の組み合わせを２つ記述した例

あくまで第１引数全体が一つの文字列なので"元データ,*.xlsx,古い元データ,*.xls"の様になります。間違いやすいので注意しましょう。覚え方は第１引数には【ファイルの説明,*.拡張子】の組み合わせを書く。【ファイルの説明,*.拡張子】の組み合わせを複数書くこともできる、と言う具合です。

さらに、拡張子の部分にアスタリスクを使うこともできます。

Sub 複数種類の拡張子の例2()
    
    Dim フルパス As String
    フルパス = Application.GetOpenFilename("元データ,*.xls*")
    
End Sub

このプログラムで表示されたファイルを開くダイアログボックスには.xlsファイル、.xlsxファイル、.xlsxファイルのいずれもが表示されます。

第3引数Title

第3引数Titleは表示されるダイアログボックスの上部に表示させるメッセージを指定します。省略可能で、省略すると、【ファイルを開く】という文字列が表示されます。

ダイアログボックス上部

Sub 第3引数を指定するサンプル()

    Dim フルパス As String
    フルパス = Application.GetOpenFilename("元データ,*.xlsx", , "元データを選択してください", , True)
    
End Sub

このプログラムを実行すると上部のタイトル部分に【元データを選択してください】と表示されたダイアログボックスが表示されます。

タイトル部分に指定した文字列が表示されたダイアログボックス

キャンセルが押された場合の対応

ファイルを開くダイアログボックスの右下にはキャンセルボタンも用意されています。このボタンを押すとGetOpenFilenameメソッドはFalseを返します。

フルパスが”False”と言うことはあり得ないため、これを開こうとするとそんなファイルありません、と言うエラーになります。そこで、キャンセルを押されたときの対策をすることが必要です。

Sub 適切にエラーを処理するサンプル()

    Dim フルパス As String
    フルパス = Application.GetOpenFilename("元データ,*.xlsx", , "元データを選択してください", , True)
    
    If フルパス = "False" Then
        MsgBox "処理を中断しました"
        End
    End If
    
End Sub

Falseは本来は特殊な値ですが、VBAでは文字列として取り扱うこともできるため、上のようなサンプルコードでトラップすることができます。

GetOpenFilenameメソッドでファイルをユーザーに選択させるまとめ

この記事の内容をまとめます。

• GetOpenFilenameメソッドはフルパスを取得するだけでファイルを開くわけではない
• GetOpenFilenameメソッドは第１引数と第３引数だけ覚えておけばほとんどの場合は事足りる
• 第１引数はファイルの説明と拡張子をセットにした【ファイルの説明,*.拡張子】と言う書式で書く
• 第１引数は【ファイルの説明,*.拡張子】のセットを複数書くこともできる
• 【ファイルの説明,*.拡張子】のセットを複数書く場合でも、ダブルクォーテーションは全体を囲む
• 第３引数はダイアログボックスのタイトルを指定する
• 「キャンセル」を押されたときはFalseが変えるので適切に処理を行う必要がある

＜関連記事＞

• VBA 中級者から上級者へ③ ブックを開く周辺を堅牢にする

＜ゆんの電子書籍＞

ワンランク上のExcel VBAプログラミング: クラスとオブジェクトを身に付けるハンズオンガイド

はじめてのアメリカ駐在　生活立ち上げがこれ１冊でまるわかり: 渡米前の準備から渡米後の帯同家族の受け入れまで実体験を徹底解説！

VBA Enumで数値の定数をまとめて定義する

VBAのプログラムで定数をまとめて定義するEnum~End Enumステートメントを解説します。

目次

定数とは

定数とは変更の予定がない値を入れておくもので、上書きできないことと定義時に値を代入する点が変数と異なります。また、変数は宣言と呼ぶのに対して定数は宣言とも定義とも言います。区別がわかりやすいようにこの記事では定数に関しては定義と呼ぶことにします。

定数を使う簡単なサンプルコードを示します。

Sub 定数サンプル()

    Const 午前中のあいさつ As String = "おはよう"
    MsgBox 午前中のあいさつ 'メッセージボックスに　おはよう　と表示される
    
End Sub

Constステートメントに続けて定数名を宣言し、その場で値を代入しています。これが定数の宣言の方法です。定数の使い所や意義などについてはVBA 定数Constの使い所と定数を使う理由で詳しく解説しました。

まとめて定数を定義するメリット

次にまとめて定数を定義するメリットについて説明します。同じカテゴリーの定数をカテゴリーごとにまとめて定義すると、Constで列挙するよりもまとまりがよく、コードがわかりやすくなるというメリットがあります。Enumの文法の説明を後にサンプルコードを掲載してConstを列挙した場合と比較します。

Enum-End Enumの使い方

それでは数値の定数をまとめて定義するEnumステートメントの書き方を説明します。

Enum-End Enumの文法

Enum-End Enumの書き方は、EnumとEnd Enumの間に定数名と値を定義します。

列挙型定数の定義のしかた

Enum-End Enumはモジュールの一番初めのプロシジャのさらに上の部分に記述します。この部分を宣言セクションと言います。Public/Privateは省略することが可能です。省略するとPublicを記述したのと同じ意味となり、他のモジュールを含めたすべてのプロシジャで使うことができる定数を定義することになります。

一方、PrivateをつけるとそのEnum-End Enumが記述されているモジュールでのみ使うことができる定数となります。同じ定数名の別の定数を別のモジュールで使う場合はPrivateをつける必要があります。

宣言した定数を使うときは、列挙する定数のカテゴリ名.定数名とします。具体的なサンプルは後述します。

Enum-End Enumの注意点

Enum-End Enumの注意点を上げておきます。

• プロシジャの中で定義できない為、ローカルレベルで使うことはできない
• 整数型しか定義できない
• Privateを忘れるとどのモジュールのプロシジャでも使えてしまうので、同名の別の定数には使えない

実際に使う場面になると忘れてしまう為、気を付けましょう。

Enum-End Enumの使いどころとサンプルコード

列挙型の定数の使いどころを紹介します。色番号など数字では意味が分かりにくい値を定数として定義するのがよくある使い方ですが、ここではビジネスでありがちな場面を想定して、エクセルの列番号に定数を使うことを使いどころとして紹介します。

下のような売上実績の集計表を想定します。やりたいことは売上高を求めるために価格と売上数量の列を掛け算した値を売上高の列に記入することです。

サンプルコードの為の売上高集計表

'これは宣言セクション、一番上に書きます
Private Enum 売上高集計表
    商品名列 = 1
    価格列 = 2
    売上数量列 = 3
    売上高列 = 4
    データ開始行 = 2
End Enum

'これはEnd Enumの下に書きます
Sub 実践的定数サンプル()
    
    Dim i As Long
    Dim 最終行 As Long '最終行取得用
    
    最終行 = Cells(Rows.Count, 売上高集計表.商品名列).End(xlUp).Row
    
    For i = データ開始行 To 最終行
        Cells(i, 売上高集計表.売上高列) = Cells(i, 売上高集計表.価格列) * Cells(i, 売上高集計表.売上数量列)
    Next i
    
End Sub

これを実行すると、D列の売上高の列に掛け算の結果が入力できました。

実行結果

このプログラムのどこが良いかと言うと、Subプロシジャの中でセルの行番号や列番号をすべて定数、もしくは変数が使われているという点です。２や３と言った数値そのものが使われていません。このような２や３と言った具体的な値（文字列含む）の事をリテラルがプログラムの中に多用されることはあまり歓迎されていません。

理由はプログラムを見ただけではその２や３という数字がどこから来たのかがわかりづらいためです。今回のサンプルプログラムは短いので問題ありませんが、少し長くなるとあっという間にプログラムの可読性が落ちてしまうので、できればリテラルではなく定数を使うほうが好ましいと言えます。このため、列番号をヘッダ名で定数としてまとめて定義できるEnumはとても便利なのです。今回のサンプルのように売上高集計表.売上高列と言った表記になるため、この行を見ただけで売上高集計表と言う表の売上高の列を更新するプログラムなんだな、と分かります。

Enumで定義すると入力補助が出る点も魅力的だと言えるでしょう。

また、メリットの項で説明したようにEnumは複数宣言することができます。Enum-End Enumを宣言セクションに複数記述することができるということです。これは処理するシートが複数あるような場合に使えます。今回は売上高集計表というシートを更新するサンプルでしたが、ほかにも商品マスタや顧客マスタといったようなシートがある時にシートごとにEnumを用意して列番号を定義することができます。

Enumで数値の定数をまとめて定義するまとめ

この記事の内容をまとめます。

• Enum-End Enumを使うメリットはカテゴリごとに定数を定義できること
• Enumは宣言セクションに記述する
• Publicをつける、または何もつけないとどのモジュールのどのプロシジャからでも使える定数となる
• 同じ定数名で別モジュールで別の定数として使うにはPrivateをつけて定義する必要がある
• Enumは整数しか定義できない

以上、Enumで数値の定数を定義する方法と使いどころについて解説しました。

＜関連記事＞

• VBA 定数Constの使い所と定数を使う理由

＜ゆんの電子書籍＞

ワンランク上のExcel VBAプログラミング: クラスとオブジェクトを身に付けるハンズオンガイド

はじめてのアメリカ駐在　生活立ち上げがこれ１冊でまるわかり: 渡米前の準備から渡米後の帯同家族の受け入れまで実体験を徹底解説！

VBA 定数Constの使い所と定数を使う理由

エクセル作業の自動化に便利なVBAで定数を宣言するConstを解説します。

目次

定数とは何か

そもそも定数とは何でしょうか。それは、固定した値で後から変更する予定のないものです。上書きで内容が変えられないところが変数と異なります。この記事では、値をそのまま使わずあえて定数を使う理由と変数でも問題ないのにわざわざ定数を用意する理由について深掘りしていきます。

定数の文法

定数の宣言Const

定数を宣言するにはConstキーワードを使います。文法はConstに続けて定数の型を記述し、値を代入します。

Sub 定数サンプル()

    Const あいさつ = "こんにちは"
    MsgBox あいさつ  'メッセージボックスに「こんにちは」と表示される
    
End Sub

上のサンプルで型を明記するAs Stringの部分は省略できます。一方、値を代入する部分は省略できません。この点が変数の宣言と異なる部分です。VBAでは変数は逆に宣言時に初期値として値を代入することができません。

定数は値の書き換えができない

Constで宣言された定数は後から値を代入して変更することはできません。だからこその定数です。

Sub 定数エラーサンプル()

    Const あいさつ = "こんにちは"
    あいさつ = "おはよう"  'ここでエラーになる
    
    MsgBox あいさつ
    
End Sub

上のサンプルプログラムを実行するとコンパイルエラーというエラーが発生してプログラムの実行が止まります。

定数に後から値を代入しようとして出るエラー

定数の使い所は自己防衛が必要な場面！？

前項で定数の値は書き換えられないことが確認できました。この書き換えられないことが定数の特徴です。それでは定数はどういった場合に使うのでしょうか？そのまま考えると、変わって欲しくない値を入れる、と言うところでしょうか。このブログでは一歩踏み込んで以下のように定数の使い所を定義したいと思います。

定数の使い所：この値は変わらないと主張する場面

定数を使う理由はプログラムの堅牢性を高めるとか、メモリの消費領域を節約して実行速度を高める、とかそういうことではありません。プログラムを書くあなたをユーザーや依頼者から守るのが定数です*1。あなたをユーザーや依頼者から守るというのは以下のような状況です。

＜想定事例＞

あなたは無人島を専門とした不動産ビジネスを営むタヌキに雇われた事務職員です。VBAのプログラムができる点を買われて採用になりました。

下図のような売上実績管理表というシートがあるとします。これは実務担当者（まめきち）が販売実績に応じてポチポチと入力をしていくもので*2、請求書の発行や売上集計、お客様への請求書の発行など、様々な業務の元データになっています*3。

売上実績管理表のフォーマット

今回、あなたが開発を命じられたプログラムはF列の売上高の値を自動で記入するプログラムです。社長（たぬきち）はエクセルシートの中に式を残すとエクセルブックが重くなったり再計算が都度発生して時間の無駄だと思っています。そこでエクセルシートには値しか入力しない、させない方針で、計算が必要なときにはVBAに夜プログラムで一気にF列にD列の価格とE列の販売数量をかけ算した結果をF列に記入するプログラムを書くように言っています。

そこで問題が発生しました。ある社員が、「結局いくら売り上げたのかが経営には大事なので、F列の売上高をB列に移しましょう。そうすれば左の方だけ見ればOKではないですか」と提案しました。

その提案は売上集計フォーマットを以下のように変更することでした。

フォーマット変更の具体案

要するに、売上高の列を左の方に移動させ、B列に持ってくるだけです。

そしてこの変更は、【右クリックを押して列の削除、もう一度右クリックして列の挿入を押す】という簡単な作業だったため、この提案は即採用されました。しかし、この売上実績集計のシートはその後の様々な業務の元データになるものです。

このようなことがあり得るのです。それほど大事なシートなら、フォーマット自体が変更できないようにしておくべき*4ですが、ここで定数の出番です。【この値は変わらないと主張する場面】と言う意味が伝わってきたでしょうか。

Sub 売上実績集計()

    Const ヘッダ行 As Long = 2
    Const 日付列 As Long = 1
    Const 商品名列 As Long = 2
    Const 顧客名列 As Long = 3
    Const 価格列 As Long = 4
    Const 販売数量列 As Long = 5
    Const 売上高列 As Long = 6
    
    'ここから価格x販売数量を計算するFor文を書いていく
    
End Sub

このようにすれば、この内容はこの列番号に書いています、と主張することになります。

プログラム本編は省略して、宣言の部分だけを掲示しました。使いどころは理解できましたか。ここまでくれば、定数はツールやシステムを依頼する人、使う人ではなく、あなた自身を守るのに役立つことが分かった事と思います。しかし、これは実は守っているのはあなただけではないのです。

定数Constを使う理由

前項で、プログラム内で変数ではなく定数を使うことによって「これは変わらない値だと聞いています」と言うことを
プログラムの中で伝えることができるということを解説しました。これは間違いなくあなたを助けます。しかし、本当はそこがポイントではありません。

本当のポイントは、むしろちゃんとした仕様変更を受け入れられるようになる事です。定数はプログラムの冒頭で宣言しましたね。と言うことは、プログラムの中に立ち入らなくても、フォーマットを変えようよ、と言う話になれば、もしちゃんと定数で列を定義していれば、冒頭の定数の定義部分の数字のみを入れ替えればいいということになります。

なるほど！となりましたか？一見、「変数でいいじゃん」と思ってしまう定数にはこんな力があるのです。Microsoft社さんはすべて分かった上で、あえて、「値にわかりやすい名前を割り当てることができます」という説明にとどめているのですね。シンプルすぎて難解なんですが、この記事を読んでMicrosoft社のシンプルな説明が意味深長に味わい深く捉えられるようになればこれほどの事はありません。

VBA 定数Constの使い所と定数を使う理由まとめ

今回の解説の内容をまとめます。

• 定数は変わらない予定の値ではなく、変わらないと主張する値（一番言いたいこと）
• Constの使い方１：型は省略できる
• Constの使い方２：値の代入は省略できない（VBAの変数との違い）
• 蛇足ながらVBAの変数は宣言と同時に初期値が設定できない
• この説明をすべて分かった上で、シンプルな公式ドキュメントを読むといいかもしれない

＜参考＞
定数の宣言 (VBA) | Microsoft Learn

＜ゆんの電子書籍＞
ゆんの電子書籍はすべてkindle unlimitedで読み放題です。

ワンランク上のExcel VBAプログラミング: クラスとオブジェクトを身に付けるハンズオンガイド

はじめてのアメリカ駐在　生活立ち上げがこれ１冊でまるわかり: 渡米前の準備から渡米後の帯同家族の受け入れまで実体験を徹底解説！

*1:この主張はこのブログ筆者、ゆんの意見であり、Microsoft社のものではありません。本家Microsoft社の公式ドキュメントによると定数の意味は「値にわかりやすい名前を割り当てることができます」（冒頭部分抜粋）だそうです。引用先は本文に記載していますのでご覧下さい

*2:その作業こそ自動化や業務改善でなんとかしてあげたい

*3:今回の例では更新型のデータベースなので、本当はできればエクセルよりアクセスの方が望ましいです

*4:イベントプロシジャを使う事によって可能です

VBA 繰り返しのFor~Next文をネストするコツ｜4桁のパスワードを破る

VBAのプログラミングで繰り返しのFor~Next文をネストするコツを解説します。人に教える中で普通のFor文が書けてもFor文のネストの話になると苦労した人がいrるためです。For~Next文の書き方の解説は世の中にあふれるほどありますが、ネストのコツを解説したものは一度しか見たことがないので、この記事が参考になればと思います。

目次

For~Next文の構造

まずは普通のFor~Next文の構造を確認します。文法は以下の通りです。

For~Next文の文法

For文は、変数をある数から一定の値ずつ変化させ、決めた数値に至るまでForとNextの間に書かれた作業を繰り返し、ある数に達するとループを抜けます。Step 数値の部分を省略すると1ずつカウントアップします。Nextの次に書いてあるiは省略可能ですが、複雑なプログラムになってくるとどのNextがどのForに対応しているのかわかりづらづらくなるので、省略せずに書くのがオススメです。For~Next文の簡単なサンプルコードを3つ挙げます。

なお、Debug.PrintはVBEの表示⇒イミディエイトウィンドウで表示させられるイミディエイトウィンドウにその内容を表示させるというメソッドです。なぜ親しみのあるMsgBoxを使わないかと言うと、いちいち【OK】を押すのが面倒だからです。

Sub For_Next文サンプル1()

    Dim i As Long
    
    For i = 3 To 5
        Debug.Print "iの値は" & i; "です。"
    Next i

End Sub

このサンプルではStepの記述を省略しているので1ずつカウントアップします。iを行番号に使う事によって表の操作などに使う事ができ、よく見かける使い方です。
＜実行結果＞

サンプル1実行結果

Sub For_Next文サンプル2()

    Dim i As Long
    
    For i = 10 To 1 Step -2
        Debug.Print "iの値は" & i; "です。"
    Next i

End Sub

次はStepに-2を設定した例です。マイナスの数を入れることによってカウントダウンが可能です。列を右から左に順に操作したい場合などに使います。
＜実行結果＞

実行結果

Sub For_Next文サンプル3()

    Dim f As Double
    
    For f = 2 To 2.5 Step 0.1
        Debug.Print "fの値は" & f; "です。"
    Next f

End Sub

カウントアップのStepに小数を使う事もできるというサンプルです。実行結果としてなぜ2.5が出力されていないのかは分かりません。もし分かる方がいらっしゃったらコメント欄でご教示頂ければ大変ありがたいです。

＜実行結果＞

実行結果

For~Next文を書くコツ

ここでFor~Next文を書くコツを一つ紹介します。それはForの行とNextの行を先に記述してから、その間に繰り返したい作業を記述する、と言う事です。上から順番に書いていくことももちろん可能です。しかし、For~Nextの間にさらに複数のFor~Nextを記述したり、If~End Ifを記述する必要がある場面があります。上から順番に書いていく方法だと、どのNextがどのForなのか分からなくなってしまい、書き忘れたNextを書こうとしてもどこにかけばいいのかわからない、と言うようなことが発生する可能性があります。

先にForとNextの行をセットで記述してしまう。その中にまたForとNextの行をセットで記述する。このようにすることで、安心してFor~Next文の中にFor~Next文を記述する事ができます。このように、For~Next文の中にFor~Next文を記述する、と言った様に同じ構造を持った一塊の文法を中に入れることをネストするや入れ子にすると言います。

以下、ネストを書くコツを解説します。ひとつはすでに解説した先にFor~Nextをセットで記述してしまうと言う事です。

For~Next文における2通りのネストの考え方

For~Next文をネストする時につまづいた方は処理の流れがよく分からない感じでした。F8でステップイン*1実行すると流れをなんとか追い掛けることができるのですが、自分では書けない、という感じです。

記述するときに役立つ考え方は2通りあります。

• 繰り返す事（A）を繰り返す（B）と言う捉え方
• 内側から外側に処理が移るイメージ

いずれも同じ事を言っています。今回は繰り返す事（A）を先に作成してしまうイメージを解説します

繰り返すこと(A)を繰り返す(B)と言う捉え方

ストレートに解釈した捉え方です。サンプルコードを使って解説します。アクティブなシートは下図のようになっています。

1～3行目のA~D列に値が入力されている

まず、繰り返すこと（A）は左から右にあいさつを表示させる事です。列方向にカウントアップの為の変数を活用する事で実現可能です。

Sub A列からD列の値を順に表示させる()

    Dim i As Long
    
    For i = 1 To 4
        Debug.Print Cells(1, i)
    Next i

End Sub

＜実行結果＞

実行結果

これはカウントアップする変数を列番号に利用する事によって、A列からD列までの値を取り出したと言う事です。繰り返しのFor~Next文を使って値を取り出しているのですね。次はこのA列からD列への繰り返しを行方向に繰り返します。繰り返す事を繰り返すとはこういうことです。

Sub A列からD列の値を順に表示させることを3行目まで繰り返す()

    Dim i As Long '列方向のカウントアップ
    Dim j As Long '行方向のカウントアップ
    
    For j = 1 To 3
    
        '内側のFor~Nextは前のプログラムの行番号をｊに書き換えただけ
        For i = 1 To 4
            Debug.Print Cells(j, i)
        Next i
        
    Next j
    
End Sub

＜実行結果＞

実行結果

慣れてくるとこのようなことをせずに外側のFor~Nextを書いてから内側のFor~Nextを書くようになります。その時は先に外側の枠を用意して、「よし、これから繰り返したい中身を書くぞ」という感じです。一方、この慣れてくると、の方法は上のような1つのネストの時には有効ですが、2回以上のネストをする場合は、やはり繰り返す事を繰り返す、という繰り返される中身の方を先に作る方が書きやすい場合があります。

次の項で例を示します。

サンプル題材：4桁の数字のパスワードを破る

簡単な文法で複雑なことを実現する。この第一歩の練習として適切なサンプルを紹介します。どのようなプログラムを書けば問題が解決できるか考えてみて下さい。

題材の内容

今回は数字4桁のパスワードを破るプログラムを考えます。

状況は以下のようにA1セルに数字4桁が入力されています。

セルの書式背屮が文字列に変更されたA1セルに4桁の数値が入力されている

A1セルはセルの書式設定が文字列に変更されており、0123と入力されれば0123として文字列が保持されように変更されています。セルの書式設定が標準のままだと、0123と入力すると0が落ちて123となってしまうため、書式の設定を変更しています。

今回やりたい事は、0000、0001、0002と順番にA1セルの値と比較して、A1セルの値と一致したときに「パスワードは5926です」などと表示してプログラムを終了することです。ひたすら数字を4つ組み合わせた文字列を照合してパスワードを破るというプログラムが今回のサンプルです。

サンプルコードに含まれる文法の補足解説

このプログラムを作成するにあたり、必要な文法を予め解説します。解説する文法は以下です。

・文字の結合
・Endステートメント

＜文字の結合＞
文字列を結合するには&を使います。「アンド」と言う事が多いですが、「アンパサンド」とも言います。どちらも正解です。文字列の結合とは文字つなげるということです。以下にサンプルを示します。

Sub アンパサンドサンプル()
    
    Debug.Print "こんにちは、" & "お元気ですか"""
    Debug.Print TypeName(0)
    Debug.Print TypeName(3)
    Debug.Print 0 & 3
    Debug.Print TypeName(0 & 3)
    
End Sub

実行結果は以下の通りです。

実行結果

一つ目は文字列と文字列の結合なので予想通り、「こんにちは、お元気ですか」と表示されました。TypeName関数は引数の型名を返します。0も3も数字なのでIntegerと表示されました。これは整数という意味で、Longと同じだと思って良いです。

注目すべきは次の行で、「0 & 3」が「03」と表示され、その方はString、つまり文字列となっているところです。＆を使うと、数値型など文字列型以外の方の値を結合しても文字列型となる事が分かりました。

このことは何を意味しているかというと、＆で結合した数値同士は文字列なので0、1、2、3の4つの数値を結合したときに0が勝手に落ちることがないことを意味しています。

＜Endステートメント＞
Endステートメントはその場で全てのVBAプログラムを終了するコードです。中断ではありません。中断はStopステートメントで実装します。For~Next文などの繰り返しはExitステートメントを使う事でループを抜けることができますが、現在実行されている部分のFor~Nextを抜けるだけなので、ネストにした場合は外側のFor~Nextに移る事となります。

今回はパスワードの照合作業が完了した段階でプログラムを終えることが目的なのでEndステートメントを利用しました。他にもGoToステートメントを利用して一気にネストしたFor~Nextを抜けることもできますが、無理矢理プログラムの流れを制御している感じがするので、今回はEndを使う事にしましょう。

サンプルコードと内容の解説

それではサンプルコードを作成していきます。以下の手順を見る前に少し自分で考えてみましょう。どのような手順で記述していけば良いでしょうか。少し複雑なプログラムになる場合はいきなり書き始めるよりも構造を考えてから記述した方が良い場合があります。

私は以下の方針でプログラムを記述することにしました。

• 左から順に1桁目をn1、2桁目をn2、3桁目をn3、4桁目をn4とするループを入れ子にする
• 一番内側のFor~Nextの中でn4を生成したのちにn1 & n2 & n3 & n4を結合し、A1セルの値と照合する

繰り返す事を繰り返すと言う方針を説明したので、入れ子の内側から作る手順で紹介します。

Sub 一番内側のn4の部分()
    
    Dim try_password As String '照合用
    Dim ans_password As String '答えのパスワードを代入しておく
    
    ans_password = Cells(1, 1)

    For i = 0 To 9
        n4 = i
        try_password = n4
        If try_password = ans_password Then
            MsgBox "パスワードは【" & try_password & "】です。"
            End
        End If
    Next i
    
End Sub

まず、For~Next以外の部分では結合した値を入れておく変数とA1のあたいを入れておく変数を用意しました。こういった変数を用意する方が、プログラムの中身が分かりやすくなるためです。For~Nextの間の部分で一番右の桁にiを代入して作成。それを照合用の変数try_passwordに代入しています。ここはn1、n2、n3と結合していくように書き換える予定です。称号下結果、一致していればメッセージを表示してプログラムが終わるようにしています。

繰り返す事を繰り返すので、右から2番目の桁のn3生成の分だけさらに繰り返す様にします。n3が0，1，2・・・とカウントアップしていくそれぞれの過程でn4が0，1，2、・・・とカウントアップしていくようにしていきます。こうして入れ子にすると以下のようになります。

Sub 左から3桁目の部分を追記()
    
    Dim try_password As String '照合用
    Dim ans_password As String '答えのパスワードを代入しておく
    
    ans_password = Cells(1, 1)
    
    For j = 0 To 9  'このループを追記
        n3 = j  '左から3桁目を生成する部分を追記
        For i = 0 To 9
            n4 = i
            try_password = n3 & n4 ' 左から3桁目を結合するように変更
            If try_password = ans_password Then
                MsgBox "パスワードは【" & try_password & "】です。"
                End
            End If
        Next i
    Next j
    
End Sub

これで二桁の称号ができました。コツが分かったので一番左から2番目と一番左の部分も外側に付け足していってプログラムを完成させるようにすると以下の様になります。

Sub BruteForce()
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'【考え方】
'このプロシジャは4桁の数字のみからなるパスワードを破る
'数字を結合させて仮パスワードを生成する
'生成した4桁のパスワードを片っ端から照合していく

'【プログラムの流れ】
'左から順に1桁目～4桁目を生成するFor文を入れ子にする
'一番内側のFor~Nextの中で生成と照合を実施する
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    Dim try_password As String '照合用
    Dim ans_password As String '答えのパスワードを代入しておく
    Dim i As Long, j As Long, k As Long, l As Long
    Dim n1 As String, n2 As String, n3 As String, n4 As String
    
    ans_password = Cells(1, 1)
    
    For l = 0 To 9
        n1 = l
        For k = 0 To 9
            n2 = k
            For j = 0 To 9
                n3 = j
                For i = 0 To 9
                    n4 = i
                    try_password = n1 & n2 & n3 & n4
                    If try_password = ans_password Then
                        MsgBox "パスワードは【" & try_password & "】です。"
                        End
                    End If
                Next i
            Next j
        Next k
    Next l

    MsgBox "パスワードの解除に失敗しました。"
    
End Sub

総当たりですが、万が一称号ができなかったときに備えて最後にパスワードの解除に失敗した旨のメッセージを追加しました。

入れ子を理解するには、繰り返す事を繰り返すと言う事を意識するのがよい事について解説し、実際にその手順に従ってプログラムを書いてきました。しかし、この手順は実は、理解のための考え方です。

For~Next文がバッチリ理解できて、ネストしたプログラムを書くのにも慣れてくると、内側から書くのではなく外側から書くのが早いし、記述ミスも少なくなくなります。その方法は、外側から順にFor Nextを書いてしまうと言うやり方です。

Sub 慣れてきたらこの手順で記述する()

    Dim try_password As String '照合用
    Dim ans_password As String '答えのパスワードを代入しておく
    Dim i As Long, j As Long, k As Long, l As Long
    Dim n1 As String, n2 As String, n3 As String, n4 As String
    
    ans_password = Cells(1, 1)
    
    For i = 0 To 9
        For j = 0 To 9
            For k = 0 To 9
                For l = 0 To 9
                    
                Next l
            Next k
        Next j
    Next i

End Sub

先にここまで書いてしまってから、それぞれのFor~Nextの中身を追記していくと言うのがお住めての順です。このように書く事によって、カウントアップ用の変数のi,j,k,lが内側から使われているという違和感もなくなります。

簡単な文法を組み合わせて複雑な処理をすると言うこと

この解説記事を書いていて改めて感じたことがあります。それはVBAにおけるプログラミング力はFor~Next文やIf~End If文のような簡単な文法を使ってどれだけ複雑な処理がこなせるかが鍵になると言う事です。この、簡単な文法で複雑な処理をこなすと言うところが肝心です。よく知恵袋などの掲示板でこのようなことは可能でしょうかというような質問をお見かけします。ニュアンス的にはこのような事が可能な文法があれば教えて下さい、と言うように見えるのですが、工夫すれば簡単な文法を組み合わせればなんとかなるものが多いのです。

このブログではAPIやOLE、といった高度な文法の解説もしていますが、それらは必要な時にピンポイントで使えれば良いと思います。やはり大事なのはForやIf。これらを使いこなすことだな、と感じます。

For~Next文をネストするコツまとめ

この記事で解説した内容をまとめます。

• For~Next文はForとNextの行を先に書いてしまい、後から繰り返す中身を書く
• For~Nextの入れ子の理解が難しいなら、【繰り返す事を繰り返す】と言う事を意識する
• 慣れるまでは【繰り返す事を繰り返す】を実装するために、先に内側のFor~Nextを記述し、それを繰り返すFor~Nextを後に書く
• 慣れてきたら外側のFor~Nextから内側へと順に書くが、すべてのFor~Nextを先に書いてしまう
• プログラミング力はFor~Nextのような簡単な文法を組み合わせて複雑なことが実現できる能力である

今回解説したネスト構造の理解の仕方はDo~Loopや条件分岐でも役に立ちます。しっかり理解して、プログラミング力を向上していきましょう。

＜ゆんの電子書籍＞ゆんの電子書籍はすべてkindle unlimitedで読み放題です!

Excel VBA 高速化のテクニック: 理論から実践まで

ワンランク上のExcel VBAプログラミング: クラスとオブジェクトを身に付けるハンズオンガイド

Excel VBA 作りながらじっくり身に付けるユーザーフォームとクラス: 「なんとなく」で済ませない！手を動かして身に付けるホントのキホン

はじめてのアメリカ駐在　生活立ち上げがこれ１冊でまるわかり: 渡米前の準備から渡米後の帯同家族の受け入れまで実体験を徹底解説！

カナディアンロッキー小紀行

*1:1行ずつプログラムを実行すること

VBA開発最大の弱点｜属人化への対応方法

VBAは自動化やデータのチェックといった業務改善ツールを、非エンジニアでも簡単に開発することができる便利なプログラミング言語です。非エンジニアの事務職にとっても取り組みやすく非常にありがたい存在です。VBAでエクセル作業を自動化する事は作業時間の短縮だけでなく、作業品質の安定化にもつながります。今回は、その死角なしに見えるVBAによるツール開発の弱点、属人化への対応方法を解説します。

目次

VBAの弱点、属人化に打ち勝つ

今回はVBAの弱点である属人化に打ち勝つ方法を解説します。VBAで自動化を行うと複雑なエクセル作業を誰でも簡単に行う事ができるようになります。そしてミス無くなります。そのため、一見するとVBAで作業を自動化すると属人化の逆である、作業の標準化が進んだように見えます。

確かに、エクセル職人に頼らずに誰もが同じように短時間で同じ作業成果を出せるようになるのですから、標準化が進む面があります。しかし、このブログでは「VBAで作った便利ツールそのものが属人化しているのではないか」という見方から、ツールを開発することによって開発者に依存するタイプの属人化に対応する方法を解説してきます。

VBAは事務職が自己啓発程度の勉強で十分身に付けられるプログラミング言語

VBAはちょっとした自己啓発程度の勉強程度でも気軽に身に付けられるプログラミング言語です。始めはこのブログのような初心者向け講座で感覚を掴み、まずは一つ小さくてもいいのでツールを完成させることが大事です。取りかかることは簡単である一方で、ある程度の継続、ある程度の経験は必要となります。それさえできれば必ず身につける事ができますので頑張って行きましょう。

＜この項に関する関連記事＞
業務効率化ならExcel VBAから始めるのがオススメな理由 10選
Excel VBA入門 本当のど初心者向けシリーズ
業務改善におすすめのプログラミング入門言語 3選 + 厳選入門書
プログラミング関連の本やネットで言う「初心者」の3つの意味
Excel VBAの実力を絶対評価で定義する｜初段～四段

ちょっとした便利ツールを気軽に作れると言うメリット

ほぼ全てのプログラミング言語において、勉強を始める前に環境構築という作業が必要です。環境構築というのはプログラムを書いたり実行するのに必要な設定を行うと言う事です。通常、この環境構築作業は非常に重たいソフトをインストールしたり、個別の設定を行ったり、バージョンが変わるたびにアップデートしたりと面倒です。面倒なだけでなく、初心者にとっては難易度が高い作業で、勉強を始める前の段階で挫折してもおかしくないと思います。

一方で、エクセル VBAはMicrosoftのエクセルさえインストールされていれば、環境設定は既に済んでいると言っても過言ではない状況でいきなりプログラムを書き始めることができます。これは非常に大きなメリットです。また、インターネットや書籍など日本語の解説情報も非常に充実しているため、学習しやすい環境が整っていると言えます。

これで、もし日常業務にエクセルが多用されているとすれば、これはもうVBAを学ばない手はありません。このように誰でも気軽に学ぶ事ができ、作業の効率化が図れることがこの言語のメリットと言えます。

気軽に開発できるからこそ陥るデメリット

このようにエンジニアに限らず誰でも気軽にプログラミングに取り組むことができると言う大きなメリットがもたらすデメリットに目を向けてみましょう。

プログラミング入門書に書かれていない大切なこと

VBAではエンジニアでもなくても、一般企業の事務職の方でも自動化ツールが開発できるプログラミング言語です。つまり、自分で開発した便利ツールは商品として販売するわけではなく、自分だけで使う前提です。もしくは自分だけで使う所から始まり、実力が付いてきて周囲の人に配布している場合もあるでしょう。いずれにせよ、自分だけが使う便利ツールがスタート地点である事に変わりはありません。

ここで入門書には書かれていないとても大切な事について触れます。

それは、人に使ってもらうとなるとVBAと言えども難易度が大きく変わってくると言う事です。例えばファイルを取り込むという簡単な操作一つにしても、あなたが想定したボタンと違うボタンをユーザーが押すかも知れません。取り込むデータのヘッダーなどデータの形も変わるかも知れません。自分だけが使うツールなら想定外の操作に対応する必要も無ければ、仕様の変更が必要であればプログラムを手直しすれば済む話です。しかし、人に使ってもらうとなるとあらゆる操作に対してエラーが出ないように作り込む必要がありますし、仕様変更の必要が発生したら「暇ができたとき」ではなくユーザーの都合に合わせて速やかに改修して最新版を配布しなくてはなりません。

つまり、ツールというモノは人に渡すと言う前提に立った途端に開発者に依存する事になるのです。ツールによって一見標準化された業務が実は開発者に属人化してしてしまうとはこのことです。これまで個別の業務担当であったユーザーに依存しなくなった代わりに開発者に依存するようになってしまったわけです。考え方によってはプログラミングスキルがある人しかメンテナンスできないという意味でよりたちの悪い属人化と言えるかも知れません。

属人化に打ち勝つ方法

VBA開発の最大の弱点である属人化に対応する前に認識しておきたいことがあります。それは以下の2点です。

• 開発者に依存すると言う事はプログラミングスキルのことなので、プログラミングができる人を増やせば良い
• プログラムの中身を明確にしておけば、プログラムさえ書ければ再現やメンテナンスができる

つまり、開発者に依存するので属人性が高まるという話は、属人化の話の1つめのスキルの面しか語っていないと言う事です。「わたしはプログラミングなんかできない」と思っているとここで思考が停止してしまいます。ここで思考回路が停止しては半分しか議論したことになりません。

2点目の、プログラムの中身を明確にしておけば、ツールの改修が必要になったときにメンテナンスができる事になります。話はかなり単純になりました。あとはこの2点への対応を行えば、VBAツールによる属人化への対応ができ、安心して自動化などの業務改善を進めることができる様になります。

複数の開発者を育成する

そもそもVBAは非エンジニアの事務職でも取り組みやすいプログラミング言語でした。と言う事は、努力次第である程度までは誰でも身につける事ができるのです。できないとすれば、それは心理的に「わたしなんかにプログラミングなんてムリ」と抵抗があるだけに過ぎません。

自己啓発で自らスキルアップを図る週間のない人には業務としてVBAを学んでもらうようにします。読み書きができる人材を増やす事によって特定のひとりへの属人化を防ぐことができます。これはリスク管理の上に非常に大切な事なので、VBAツールがある部署においては少なくとも2人はVBA人材の確保をするようにしましょう。できれば3人以上が望ましいところです。

開発ドキュメントをそろてプログラムの中身を明確にする

開発ドキュメントには様々な種類がありますが、非エンジニアが作るちょっとした便利ツールレベルであれば要求仕様書があればほぼ大丈夫です。要求仕様書とは、そのツールで実現したい内容を明確に定義したドキュメントです。これがある事により、そのツールを紛失してしまったり、データの仕様が変わってしまっても新たなツールを新たに開発して元のツールを再現したり、元のツールを改修したりすることができる様になります。

開発ドキュメントについては会社員がVBAで自動化するのに必要な開発ドキュメント３点で詳しく解説しているので詳細はリンク先をご参照下さい。

VBA 属人化への対応方法まとめ

この記事の内容をまとめると以下の通りです。

• VBAは事務職でも取り掛かりやすい優秀なプログラミング言語
• 業務の属人化を防ぐ一方、ツールそのものが属人化してしまう弱点がある
• ツールの属人化を防ぐには、人材育成と開発ドキュメントの整備の２点を進める

最後に一点大事なことを付け加えます。それは、ツールの属人化さえ防ぐことができれば業務自体はツールで属人化を防ぐことができているので、真に担当者に依存しない強力な組織を構築することにつながるということです。VBAで業務改善を行う風土のある職場では、一歩進んで、ツールそのものをメンテナンスする体制の構築を進め、真の属人化フリーを目指しましょう。

＜お知らせ＞
Excel VBAでクラスやオブジェクトの概念と使い方を丁寧に解説し、ワンランク上の実力を目指すガイドが電子書籍になりました。この本で本物の実力を身に付けて一皮むけてみませんか？
books.rakuten.co.jp

VBA 初心者が最初に押さえるべき文法10選

VBAは非エンジニアの事務職でプログラミング経験がゼロの人でも取り組みやすい言語です。一方で、取り組みやすいと言ってもどこから手をつければ良いのかわからない人もいるのではないでしょうか。今回は始めに押さえておくべき10個の文法を紹介します。これだけでもかなりのことができる様になります。

目次

VBA 初心者が最初に押さえるべき文法10選

VBAは非エンジニアの事務職の方がちょっとした自動化などの便利ツールを気軽に作るのにうってつけのプログラミング言語です。簡単に始められる一方で、極めようと思えば奥が深く相当いろいろな事ができるという初心者から上級者まで幅広く学ぶ余地がある言語であるとも言えます。

この記事では、VBAの初心者が手っ取り早く便利ツールが作れるようになるための最低限押さえておくべき文法を10個絞り込んで紹介します。この記事で紹介した文法をマスターすれば相当なツールが作れるようになります。ここで紹介する文法をマスターしたら、ひとつのツールを仕上げてしまいましょう。その後、使い勝手の向上や高速化を目指して使える文法の幅を広げていくとスムーズに上達していく事ができます。

変数と定数

プログラミングを行うに際し不可欠なのが変数です。定数は始めて作成する便利ツールには不要かも知れませんが、ゆくゆくレベルアップしていく段階では必要となりますので、セットで覚えてしまいましょう。

1．変数と定数

変数はよく箱に例えられますが、ここではメモのようなモノだと言う説明をしたいと思います。電話が掛かってきて、何かを説明されたら、要点をメモしますよね。そして用が済んだらそのメモは捨ててしまいます。このように一時的に値を保存しておいて後で使う為のメモが変数です。

一方、定数は変数と似ており、後で使う為に用意するものです。こちらはメモではなく、備忘のためのフセンです。例えば、パソコンを開いたらまずメールのチェックを忘れずに行うように、ラップトップPCのカバーの部分に貼っておくフセンのようなモノです。毎回同じ事をするので書き換えることがない点がメモと異なる点で、これが変数と定数の違いに当たります。

演算子

演算とは計算をすることです。演算子とは演算を表す記号のことです。

2．算術演算子

VBAで覚えるべき算術演算子は6つです。

• +：和を求める。足し算。
• -：差を求める。引き算。
• *：積を求める。かけ算。
• /：商を求める。割り算。
• Mod：剰余を求める。割り算の余り。
• ^：べき乗をする。

Modがなじみがないかも知れませんが、これは余りを求める計算です。エクセルのワークシートではModは関数ですが、VBAでは演算子です。割り算で/と書く代わりにModと書けば、余りが求められる、と理解しておけばOKです。下のサンプルを見て確認して下さい。MsgBoxに答えを表示させます。

Sub 算術演算子サンプル()
    
    MsgBox 7 + 3    '10
    MsgBox 7 - 3    '4
    MsgBox 7 * 3    '21
    MsgBox 7 / 3    ' 2.33333333333333
    MsgBox 7 Mod 3  '1
    MsgBox 7 ^ 3    '343
    
End Sub

シングルクォテーションでコメントした部分がメッセージボックスで表示される部分です。足し算、引き算、かけ算、割り残はOKですね。Mod演算子の部分は7÷3 = 2余り1なので、余りの1が出力されます。^はキャップと読みます。7 ^ 3は7 × 7 × 7 = 343で343が出力されます。

3．比較演算子

比較演算子はふたつの値を比較した結果を返します。後に紹介する条件分岐の条件の部分でよく使います。戻り値はTrueまたはFalseと言う特別な値です。具体的に見てみます。

Sub 比較演算子サンプル()
    
    MsgBox 7 > 3    'True
    MsgBox 7 >= 3   'True
    MsgBox 7 < 3    'False
    MsgBox 7 <= 3   'False
    MsgBox 7 = 3    'False
    MsgBox 7 <> 3    'True
    
End Sub

小学校で習った不等号と同じです。真であればTrue、偽であればFalseが返ります。また、以上、以下を表す不等号はイコールが右側に来る、と覚えておきましょう。将来、VBA以外のプログラミング勉強する事になった時も、以上、以下のイコールは右側です。

＜＞は等しくないと言う意味です。＜または＞、と言うことは等しくないときに成り立つ、と考えれば覚えやすいでしょう。

繰り返し

VBAによる自動化ツール作成で避けては通れないのが繰り返しです。簡単な文法なのでさっさと理解してしまいましょう。

4．For~Nextステートメント

VBAには繰り返しをする為の文法は2つありますが、先に覚えるべき構文としてFor~Nextステートメントを紹介します。VBAにおいては、ステートメントとはプログラムの流れをコントロールするキーワード、と言う意味です。

For ~ Nextは For とNextで囲んだ部分を繰り返すというステートメントで、変数とセットで使います。具体的なサンプルを見てみましょう。

Sub 繰り返しサンプル()

    For i = 1 To 5
        MsgBox i & "回目"
    Next i
    
End Sub

i は変数です。唐突に出てくる始めで出てくる文字列が変数であると認識されます。この記事の一番最後に変数をきちんと変数として宣言する方法説明します。

このサンプルコードでは変数iに1をまず代入し、For~Nextの中が実行され、次にiに2が代入され、For~Nextの中が実行され・・と言う具合にコードが実行されていきます。Toの後に5と書かれているので5まで代入します。VBEの画面でF8を押すと一行ずつプログラムを実行して共同を確認することができるので是非、試してみましょう。

実行結果。メッセージボックスが5回繰り返して表示される。

＜関連記事＞

• Excel VBA 入門 本当のど初心者向け③ 繰り返しのFor文

条件分岐

ある程度複雑な事を行うようになれば条件分岐が必須です。条件分岐ではもしAだったら、Bを行うと言うような書き方をします。ここでは条件分岐の基本である、If~End If文を紹介します。

5．If ~ End If

If ~ End IfはIfの後にTrue、もしくはFalseをあたえます。そして、Ifの後がFalseでなければIf とEnd Ifの中身が実行されます。

Sub 条件分岐サンプル()
        
    If True Then
        MsgBox "Trueです"
    End If
    
End Sub

実行結果。Ifの中身が実行された。

Ifの後がTrueとなっているので、その中身のMsgBoxを表示させる命令が実行されました。

比較演算子のところで2つの値を比較して真であればTrue、偽であればFalseが変える事を勉強しました。これをIfの後の部分に使います。

Sub 条件分岐サンプル2()
        
    If 7 > 3 Then
        MsgBox "7は3より大きいです"
    End If
    
End Sub

無事に実行されました。不等号を逆向きにするとIf ~ End Ifの中身は実行されません。Ifの後の部分を条件式と言います。条件式の値がTrueの場合、ThenからEnd Subの中身が実行されます。If文はバリエーションがあり、End Ifを使わずに1行で表現する方法もあるのですが、この方法は条件式の値がTrueの時の命令がひとつしか書けないのと、後から見たときに見づらいのでオススメしません。よってここでは紹介しません。

＜関連記事＞

• Excel VBA 入門 本当のど初心者向け④ 条件分岐のIf文

オブジェクト

オブジェクトとはいろいろな機能を持ったモノのことです。エクセルには様々なオブジェクトが用意されていますが、まずはワークブック、シート、レンジを覚えておけばOKです。

6．Workbooks、Sheets、Range

オブジェクトの中でもsをつけて複数形になっているものをコレクションと言います。コレクションとは集まりのことです。沢山ある集まりのうち、Aというワークブック、Bというシート、と言った具合です。

7．プロパティ

実はプロパティとこの次の項で説明するメソッドというのは、初めのうちは詳しく分けて理解する必要はありません。しかし、将来的な事を考えてプロパティとはどういうことなのか、メソッドとはどういうことなのかと言う事をざっくりと把握しておくのが良いでしょう。

プロパティとはそのオブジェクトの状態を表すパラメータです。パラメータとは、設定できる値のことです。たとえば、シートの名前、列の幅、セルの背景の色と言った様なものです。

簡単な例を挙げます。

Sub オブジェクト_プロパティ()

    Range("A1").Value = "Hello"
    
End Sub

これでA1セルにHelloと入力されます。これはRangeと言うオブジェクトのうち、A1で表される部分のValue、つまり値をHelloにする、と言う意味です。VBAは空気を読んでくれるプログラミング言語なので、Valueを省略すると、エクセルが「Valueを省略したんだろうな」と推測して同じ結果にしてくれます。

Sub オブジェクト_プロパティ2()

    Range("A1") = "Hello" 　'.Valueを省略しても同じ結果となる
    
End Sub

8．メソッド

メソッドとはオブジェクトに備えられた機能のことです。オブジェクトごとに様々な機能が備えられています。例えばワークシートにはワークシートを追加する、という機能があります。

Sub オブジェクト_メソッド()

    Sheets.Add
    
End Sub

これでシートを追加する事ができます。よくVBAの解説で誤った解説を見かけます。それはSheetsをAddするからSheets.Addと記述する、と言う解説です。これは間違っています。SheetsがもっているAddと言う機能を使う、というのが正しいです。

ステートメント

ステートメントとはプログラムの流れを制御する文です。プログラムは上から順に一行ずつ実行されるのが基本ですが、それ以外の動作を行うときに記述するのがステートメントだと言えます*1。すでに出てきたFor~Nextステートメントもステートメントです。繰り返しの部分が流れを制御した結果のためです。

9．Callsステートメント

とても小さなプログラムでは不要ですが、少しでもボリュームが出てくると、機能と処理の流れを分離してプログラミングを行うのが合理的な場面が出てきます。その時、機能の塊を作っておき、それを呼び出す事によって、プログラムの流れを分かりやすく事ができます。プログラムを呼び出すのがCallステートメントです。

10．Dimステートメント

一番始めの項で変数の説明をしました。始めてプログラムの中に出てくるキーワードではない文字列が変数として認識されるという説明をしましたが、「この文字列を変数として使います」という宣言を行う事も可能です。こうすることによって、何を変数として使うかが明確になるというメリットがあります。

変数は宣言しなくてもプログラミングが可能ですが、プログラムの規模が大きくなると宣言をしておいた方が結局自分が助かることになるでしょう。

さらに上を目指す方法

以上、簡潔にVBAを学ぶに当たって最初に身に付けるべき10の文法を紹介してきました。プログラミングの勉強は網羅的に文法を学ぶより、必要最低限を学んだらとりあえず実際に書いてみる、アウトプットをしてみると言うことが非常に大切です。その中でうまくいかないことやできないことが出てくるので、その都度調べて知識と技術を拡張していくのが最短の上他鬱方法といえます。この記事では、アウトプットを出すために必要な最小限の文法を紹介しました。

紹介するに止まっており、身に付けられるほど詳しい解説ができていません。それぞれについて詳しい解説を書いていますし、上達のステップも示していますので、ぜひ下の関連記事を参考にしてレベルアップしていきましょう！

＜関連記事＞

• 業務効率化ならExcel VBAから始めるのがオススメな理由 10選
• Excel VBAの実力を絶対評価で定義する｜初段～四段
• Excel VBA入門 本当のど初心者向け①とりあえず何か書いてみる
• エクセルVBAでできることと限界｜業務自動化からピアノ演奏まで
• 業務改善におすすめのプログラミング入門言語 3選 + 厳選入門書

*1:Withはプログラムの流れを制御するわけではありませんが、ステートメントと呼ばれています

プログラミング｜業務改善関連用語事典

プログラミングと言えばコンピュータが動作する指示を人間が分かる様にしたプログラミング言語を使って記述することです。ここでは業務改善のための道具という立場から見たプログラミングを解説します。

目次

業務改善のためのプログラミング

早速プログラミングという作業を業務改善の立場から見てみましょう。ここで言うプログラムとはVBAやPythonのようなスクリプト言語を使って自動化ツールや特定の目的を達成する事ができるシステムの一部*1を作る事を指します。目的は業務改善です。

ゲームや基幹システムを作るようなプロのエンジニアが取り扱うプログラミングとはニュアンスが異なります。エンジニアが開発するプログラムは厳密な要件定義を必要とし、また、チームを組んで開発します。このような点も参考になる部分は取り入れるのが好ましいですが、業務改善においてはプログラミングは目的ではなく手段であるという立場を取ります。

かなりいろいろな事ができる

エンジニアのような基幹システムを作る事ができなくても、VBAやPython使う事ができれば実に様々なことができます。VBAでできる事はエクセルVBAでできることと限界｜業務自動化からピアノ演奏までに詳しくまとめていますが、一部例を挙げると下記の様なものです。

• 請求書作成の自動化
• Webスクレイピングの自動化
• メール配信自動化
• パワーポイント会議資料更新の自動化

以上は自動化の例です。他にもデータの更新のための入力を制限したり、Pythonではデータの分析や前処理というようなことも可能です。ある程度の機能を実装しようとするとそれなりに努力が必要ですが、逆に一度身に付ければ特にVBAは長く使える技術ですので、オススメです。

自動化ではRPAも一時期話題となりましたが、慣れるとVBAやPythonの方が思い通りに細かい作業が指定できますし、メンテナンスも自分で作った文やりやすいのでRPAに手をつけるならVBAかPythonのどちらかを先に学ぶ事を推奨します。またRPAはソフトのバージョンが頻繁に上がるため、バージョンの違いによる不具合などにも対応する必要があります。VBAは良くも悪くも20年前と今でほとんど同じ文法です。

プログラミングは地道な作業

Pythonにおけるデータ分析など世の中の注目を集めている技術は華やかに感じます。しかし、プログラミングというのは相当地味な作業です。華やかさとは縁遠いと言わざるを得ません。私感ですが、その作業工程はプラモデルや鉄道模型、ジオラマ、レゴブロックを作るのに似ていると思います。

まず全体としてどのようなモノを作るのかを考え、全体やユーザーが触れる部分をどのように作るかを考えます。この工程を外部設計と言います。これはプラモデルやジオラマでは構想で紙に絵を描いていく作業に当たります。次に細かい機能面を考え、どのように実装するのかを考えます。これを内部設計と言います。それぞれの部分を作成したらつなげてテストをしていきます。それぞれの部分を作成し、つなげるというのもプラモデルやレゴに近いモノがあります。この細かいところをそれぞれ作ったり、つなげて実装する部分がプログラミングに当たる部分です。

作りたいモノを考え、自分の手を動かして作ると言うのが好きな人はプログラミングもきっと好きになるのではないでしょうか。念のための補足ですが、エンジニアがゲームや基幹システムを仕事で作る話ではありません。非エンジニアの事務職が業務改善で行う手段としてのプログラミングについての解説です。

業務改善におけるプログラミングのまとめ

簡単ですがまとめます。

• VBAやPythonなどのプログラミング言語を学ぶと自動化を始めとしていろいろな事ができるようになる
• 自動化だけでなくデータの更新のための入力をさせたり、分析のための前処理を行う事もできる
• プログラミング作業はツールを作る作業の一部
• 外部設計、内部設計、プログラミングの順に進む
• プログラミングは地味な作業
• プラモデルやジオラマ、レゴを組み立てる様な作業なので手を動かしてモノを作るのが好きな人は好きかも知れない

• この記事で解説しているプログラミングは、非エンジニアの事務職が業務改善の用途で使うもので、エンジニアのそれとは異なる

興味が持てればぜひチャレンジしてみましょう。文系/理系は全く関係ありません。始めて挑戦してみたい方は、下に挙げている関連記事も参考にしてみて下さい。

＜関連記事＞

• エクセルVBAでできることと限界｜業務自動化からピアノ演奏まで
• 【プログラミング】文法の勉強だけでは遠回り｜挫折しない上達のコツ
• 非エンジニアがプログラミングを勉強すべき理由
• 【退屈なことはPythonにやらせよう】のAmazonレビューに見る、初心者本を選ぶときの注意点
• Excel VBA入門 本当のど初心者向け①とりあえず何か書いてみる

*1:サブシステム

VBA 上級技 エクセルでピアノを作る

エクセルでピアノの鍵盤を作る方法を解説します。直接的に何かの役に立つわけではなく、プログラミングの練習、もしくは単なる遊びです。しかし、勉強する事と遊びの境目がなくなれば 最強 です。楽しんでやれば挫折しないからです。それでは早速見ていきましょう。

目次

エクセルでピアノを作る事の概要

エクセルでピアノを作る方法についてその流れを押さえ、全体像を把握する事から始めます。その前に前提のお話を少しだけさせて下さい。

前提：APIは上級技だけど難しくない

題名に上級技とありますが、ピアノを作るエクセルのプログラミングが難しいと言うわけではありません。APIというWindowsの機能を借りてきて利用する事が一般にVBAでは「上級」と言われているためにその言い方に合わせました。他のシステムの機能を借りてきて使う事がエクセルの中だけで処理が済むプログラムより高度なので上級と呼ばれているのですが、上級＝難しいと言うわけではありません。逆にIfやForなど基礎的な文法市価使っていなくても、複雑な分岐をする必要がある場合などはプログラミングをする事が難しい場合があります。このように、「上級」とされている技術を利用することが難しいとは限らないため、先入観で新しい技術を取り入れるのをためらわないようにしましょう。

エクセルでピアノを作る作業の流れ

エクセルでピアノを作る作業は下のような流れとなります。

1. エクセルシートに鍵盤の絵を描く
2. 標準モジュールにメインのプロシジャを挿入する
3. 標準モジュールの冒頭にAPIの宣言を書く
4. シートモジュールにプロシジャを挿入する
5. 標準モジュールを仕上げるクリックした場所に応じた音を出すようにする

一見難しいようでもこのように作業を細分化し、ひとつひとつを着実にこなしていくことで目的を達成する事ができます。

エクセルシートに鍵盤の絵を描く

まず、ピアノの鍵盤となる部分を作成します。分かりやすい様に、シート名はSheet1からkey_boardに変更しました。

次に、行と列のスケールを調整します。私の場合は1行目の行の高さを150、2行目の行の高さを100にしました。そして、E列からAT列までの列の幅を1.5に変更します。これはE列からAT列の部分ピアノの鍵盤にするためです。これで行と列の幅を整える作業は完了です。

次に、黒鍵の部分を作ります。ピアノの鍵盤は白黒白黒白白黒白黒白黒白となっています。黒鍵は1行目に作ります。言葉で説明しても分かりづらいので、自作される方は下の図を真似して作成してみて下さい。

エクセルシートに作成したピアノの鍵盤

罫線を引けば鍵盤はほぼ完成です。見栄えを良くするためにセルの灰色枠線を非表示にしています。セルの枠線を非表示にする方法はリボンの上にあるタグから表示を選択して、目盛線のチェックボックスを外せばOKです。

表示タグを選択して「目盛線」のチェックボックスを外す

標準モジュールにメインのプロシジャを挿入する

今回はイベントプロシジャを使いますが、押された場所に応じた音を鳴らす部分は標準モジュールに記述します。ピアノをもしたエクセルシートを作成するのが今回の目的のため、メインのプロシジャ名はPianoにします。

' これは標準モジュールに書きます
Sub Piano()
    
End Sub

そしてこのPianoプロシジャの中にAPIを呼び出して、クリックされた場所に応じた音を鳴らすプログラムを書いていきます。

標準モジュールの冒頭にAPIの宣言を書く

このプログラムの心臓部分となるPianoプロシジャの中身の作成に入る前に、APIを使う準備を行います。

APIとは、他のアプリケーションの機能を借りてくることです。ここではWindows API、つまりWindowsの音を鳴らす機能を借ります。エクセルには音を鳴らす機能が無いからです*1。そとから機能を借りてくるので、何の機能を借りてくるのかを予め宣言しておく必要があります。そこで、先ほどのイベントプロシジャの上にAPIの宣言を記述します。

'これは標準モジュールのPianoプロシジャの上に書きます。
Declare PtrSafe Function Beep Lib "kernel32" _
    (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long

APIの宣言は冒頭で行いますので、先ほど挿入したPianoプロシジャの上に記述して下さい*2。

なお、APIの使い方や必要なAPIの調べ方について詳しく知りたい方はVBA Windows APIを使う手順（調べる→宣言する→使う）で詳しく解説していますので参考にしてみて下さい。

シートモジュールにメインのプロシジャを呼び出すプロシジャを記述する

今回はイベントプロシジャを利用します。イベントとは、ユーザーの入力など外部からの影響をトリガーに実行されるプログラムのことです。今回はシートをクリックされたら、標準モジュールを呼び出すようにします。このようにする理由は、押されたら音を鳴らすというイベントプロシジャにする必要があることと、シートモジュールのようにオブジェクトにくっついているモジュールではAPIの宣言ができないためです。

シートモジュールはオブジェクトにくっついているため、既に存在している

このことからシートモジュールはメインのモジュールを呼び出すところだけを担当し、メインの押された場所に応じて音を鳴らす部分は標準モジュールに記述します。

シートモジュールはワークシートに付属したモジュールのため、既に存在します。ここに標準モジュールのPianoプロシジャを呼び出すVBAを記述します。イベントプロシジャを記述するときはモジュール名は自分で書くのではなく、トリガーとなる出来事に応じたプロシジャ名を選択します。

イベントプロシジャが始めてでよく分からない、と言う方はVBA 3分で分かる便利なイベントの使い方　ワークブックにチェック機能をつけるにイベントプロシジャの書き方を具体例を挙げながら解説しているので、参考にして下さい。

ユーザーがシート上でクリックをするとクリックをしたところのセルがActiveとなります。つまり選択した場所が変わります。このため、Worksheet_SelectionChangeを選択します。すると、下記の様になります。

'これはシートモジュールに書きます。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

このPrivate SubとEnd Subの間に標準モジュールに記述した音を流すプログラムを呼び出す記述をします。

'これはシートモジュールに書きます。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Call Piano(Target.Row, Target.Column)
    
End Sub

押された場所が大事なので、呼び出すPianoプロシジャの所に引数にTarget.Row、Target.Columnを書く事で、クリックされた場所の行番号と列番号をPianoプロシジャに渡します。

音を出すようにする

準備が整いました。ここから実際にシート上のどこをクリックしたかで出す音が変わるプログラムを記述していきます。

音程とヘルツ数を調べる

ドレミのという音の高さはヘルツという数字で表されます。そこで、ドからシまでのそれぞれの音程を調べます。

261.626　：ド
277.183　黒鍵
293.665　：レ
311.127　黒鍵
329.628　：ミ
349.228　：ファ
369.994　黒鍵
391.995　：ソ
415.305　黒鍵
440.000　：ラ
466.164　黒鍵
493.883　：シ

523.251　：ド
554.365　黒鍵
587.330　：レ
622.254　黒鍵
659.255　：ミ
698.456　：ファ
739.989　黒鍵
783.991　：ソ
830.609　黒鍵
880.000　：ラ
932.328　黒鍵
987.767　：シ

2オクターブ分の鍵盤があるので周波数も２オクターブ分調べました。

なお、この記事では音階を調べるために下のページを参考にさせて頂きました。
https://tomari.org/main/java/oto.html

今回使うAPIでは音の高さをヘルツを使って表現します。

とりあえず音が出るようにする

それではメインのPianoプロシジャを作成してみます。まずは音を単に音を鳴らしてみましょう。Beepという音を鳴らすAPIを使うという宣言を既に行っているため、たった一行で音を鳴らすことができます。まずはラの音を1秒流すだけのプログラムを書いてみましょう。

Sub Piano()
    
    Call Beep(440, 1000)
    
End Sub

ラは440Hzなので、第１引数に440を渡しました。Beepは音を流す間の時間をミリ秒で指定します。1秒は1,000ミリ秒なので、1000を第２引数にしました。この状態でどこかをシート状のどこかのセルをクリックすると「ポー」という聴力検査のような音が出ます。APIの利用が完了です。たった一行でとても簡単ですね。冒頭で述べた上級技なのに簡単ということはこういうことです。

メインのプログラムを記述する

それでは次にメインのプログラムを作成していきます。

クリックされてアクティブになったセルの行と列の値を受け取れるようにする

Sub Piano(rw, clm)
    
    Call Beep(440, 1000)
    
End Sub

まずは、クリックされた場所を知るためにイベントプロシジャから呼び出すときに渡された引数を受け取れるようにします。イベントプロシジャではPiano(Target.Row, Target.Column)というように２つの引数を渡しながらPianoプロシジャを呼び出しました。この値をPianoプロシジャ側ではrwとclmという変数で受け取ります。rwはRowで行の意味、clmはColumnで列の意味です。ここは自分がわかりやすい変数名にしておけばよいです。

条件分岐させてクリックされた場所に応じた音が出るようにする

どこを押されたかによって出す音が違うので条件分岐を行うことにします。そして、If文は最大で20個までしかElse Ifが使えません。そこで、IfとSelect Case分を組み合わせて使うことにします。

プログラムがしやすいようにセルの表示をR1C1形式にしておきます。リボンの上のタブのファイル⇒オプション⇒数式⇒R1C1参照形式を使用すると進みます。すると、列がABCではなく、1、2、3と言う表示になります。

R1C1形式表記にしたエクセルシート

ピアノの構成をよく見て条件分岐をどうするか考えます。まず、列は5列目から46列目の間以外は音が鳴らないことが確定です。5~46列目であれば、どこを選択したらド、などと一つ一つ条件分岐していきます。

例えば5列目、6列目ならド。7列目なら、1行目ならドの♯。2行目ならド、と言った具合です。これを地道にプログラミングしていくと下記の通りとなります。

Declare PtrSafe Function Beep Lib "kernel32" _
    (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long


Sub Piano(rw, clm)
    
    If rw <= 2 Then
        Select Case clm
            Case 5, 6
                Call Beep(262, 500)
            Case 7
                If rw = 1 Then
                    Call Beep(277, 500)
                Else
                    Call Beep(262, 500)
                End If
            Case 8
                If rw = 1 Then
                    Call Beep(277, 500)
                Else
                    Call Beep(294, 500)
                End If
            Case 9
               Call Beep(294, 500)
            Case 10
                If rw = 1 Then
                    Call Beep(311, 500)
                Else
                    Call Beep(294, 500)
                End If
            Case 11
                If rw = 1 Then
                    Call Beep(311, 500)
                Else
                    Call Beep(330, 500)
                End If
            Case 12, 13
                Call Beep(330, 500)
            Case 14, 15
                Call Beep(349, 500)
            Case 16
                If rw = 1 Then
                    Call Beep(370, 500)
                Else
                    Call Beep(349, 500)
                End If
            Case 17
                If rw = 1 Then
                    Call Beep(370, 500)
                Else
                    Call Beep(392, 500)
                End If
            Case 18
                Call Beep(392, 500)
            Case 19
                If rw = 1 Then
                    Call Beep(415, 500)
                Else
                    Call Beep(392, 500)
                End If
            Case 20
                If rw = 1 Then
                    Call Beep(415, 500)
                Else
                    Call Beep(440, 500)
                End If
            Case 21
                Call Beep(440, 500)
            Case 22
                If rw = 1 Then
                    Call Beep(466, 500)
                Else
                    Call Beep(440, 500)
                End If
            Case 23
                If rw = 1 Then
                    Call Beep(466, 500)
                Else
                    Call Beep(494, 500)
                End If
            Case 24, 25
                Call Beep(494, 500)
            
            'オクターブ
            Case 26, 27
                Call Beep(262 * 2, 500)
            Case 28
                If rw = 1 Then
                    Call Beep(277 * 2, 500)
                Else
                    Call Beep(262 * 2, 500)
                End If
            Case 29
                If rw = 1 Then
                    Call Beep(277 * 2, 500)
                Else
                    Call Beep(294 * 2, 500)
                End If
            Case 30
               Call Beep(294 * 2, 500)
            Case 31
                If rw = 1 Then
                    Call Beep(311 * 2, 500)
                Else
                    Call Beep(294 * 2, 500)
                End If
            Case 32
                If rw = 1 Then
                    Call Beep(311 * 2, 500)
                Else
                    Call Beep(330 * 2, 500)
                End If
            Case 33, 34
                Call Beep(330 * 2, 500)
            Case 35, 36
                Call Beep(349 * 2, 500)
            Case 37
                If rw = 1 Then
                    Call Beep(370 * 2, 500)
                Else
                    Call Beep(349 * 2, 500)
                End If
            Case 38
                If rw = 1 Then
                    Call Beep(370 * 2, 500)
                Else
                    Call Beep(392 * 2, 500)
                End If
            Case 39
                Call Beep(392 * 2, 500)
            Case 40
                If rw = 1 Then
                    Call Beep(415 * 2, 500)
                Else
                    Call Beep(392 * 2, 500)
                End If
            Case 41
                If rw = 1 Then
                    Call Beep(415 * 2, 500)
                Else
                    Call Beep(440 * 2, 500)
                End If
            Case 42
                Call Beep(440 * 2, 500)
            Case 43
                If rw = 1 Then
                    Call Beep(466 * 2, 500)
                Else
                    Call Beep(440 * 2, 500)
                End If
            Case 44
                If rw = 1 Then
                    Call Beep(466 * 2, 500)
                Else
                    Call Beep(494 * 2, 500)
                End If
            Case 45, 46
                Call Beep(494 * 2, 500)
        End Select
    End If
        
End Sub

プログラムはかなり長くなってしまいましたが、どこを押されたかをひたすら分岐しているだけなので、命令としては1行しか実行されません。オクターブはヘルツ数を2倍しています。反省点としては音の長さが決め打ちな所です。クリックした瞬間から0.5秒を機械的にならしています。別のAPIを使えばマウスのクリックを離した瞬間*3に音が途切れるようにするなどが考えられます。また、複数の音を同時にならすこともできません。

APIに慣れ親しむ目的で紹介しましたので、このあたりでご容赦頂ければと思います。

VBA 上級技 エクセルでピアノを作る方法のまとめ

この記事の内容をまとめます。

• VBAによるプログラミングでAPIは上級向けとされているが、難しくはない
• APIは宣言さえ分かればあとは呼び出すだけで使える
• ピアノを作ろうとすると分岐が沢山必要である
• ピアノの鍵盤をセルで代用しようとすると、イベントプロシジャが必要となる

勉強とは思わずに、遊びでこのようなプログラムを作ってみるのも良いと思います。

＜関連記事＞

• VBA 三段から四段へのステップアップ ～APIを身に付ける～
• VBA Windows APIを使う手順（調べる→宣言する→使う）

＜お知らせ＞
Excel VBAでクラスやオブジェクトの概念と使い方を丁寧に解説し、ワンランク上の実力を目指すガイドを書きました。この本で本物の実力を身に付けて一皮むけてみませんか？
books.rakuten.co.jp

*1:文字を読み上げる機能はあります

*2:Option Explicitを挿入している方はその下です

*3:マウスアップと言います

エクセルVBAでできることと限界｜業務自動化からピアノ演奏まで

プログラミング未経験でエクセルVBAに興味を持たれている方に向けて、エクセルVBAでできることを紹介します。自分が勉強しようとしているプログラミング言語がどこまでのポテンシャルを秘めているかを知り、作りたいもののイメージや目的意識を持つことはプログラミング習得にとても効果的です。

目次

エクセルVBAでできること

この記事ではエクセルVBAでできる事の様々な具体例を挙げていきますが、例がたくさんある為、大きく3つのカテゴリーに分けて順に説明していきます。

• 自動化ツール
• 業務システム
• その他のプログラム

まず、ツールとシステムの違いについて切り分けを明確にしてから具体例の紹介に移っていきます。具体例の見知りたい場合は目次から直接飛ぶことができます。

エクセルVBAでできることの概要

エクセルVBAでできることは多岐にわたります。どのようなことができるか、と言うのはどのようなことを実現したいかの数だけあると言っても良いでしょう。それではどの範囲でできるかというと、

• Officeの製品でできる事
• Office以外のソフトで機能を提供しているものを利用すること

の範囲でできます。Officeの製品とはエクセルやOutlookと言ったものです。Office以外のソフトとは例えばGoogle Mapなどです。これが、エクセルVBAでできることです。あとはそれぞれのソフトの機能をどう組み合わせるかアイデア次第です。

ツールとシステムの違い

この記事ではエクセルVBAでできることをツール、システム、その他のプログラムの3つにわけて説明します。そのため、まずはこの記事における、ツールとシステムの言葉の使い分けについて明確にしておきます。

ツールとは何かの目的を達成するためのひとつの機能を提供するプログラムのことです。例えば、お客さんからの注文に対して請求書を作成したい場合、注文の品番と数量、単価の情報を元に請求書を自動で生成するという機能を持ったプログラムを作成すればそれは自動で作成するツールを作成したことになります。

システムとは、何かの目的を達成するための仕組みのことです。複数の機能やデータから成り立ちます。例えば、販売管理システムであれば、販売に関する様々な業務を行い、管理する事が目的です。そのために必要な機能やデータを持っています。

販売管理システムは過去の売上高を集計する機能や請求書を作成する機能、確定申告のデータをまとめる機能などいろいろな機能を備えています。また、それらの機能を利用する前提として過去の売上データを保持しています。このように複数の機能やデータが集まって仕組み、つまりシステムを構築することで販売を管理するという目的を達成する事ができます。これがシステムの意味です。

自動化ツール

それでは自動化ツールについて説明していきます。自動化ツールは何かの作業を自動で行うツールです。自動化できる作業は様々なものが考えられます。以下のようなものです。

• 作成ツール
• 抽出ツール
• 加工ツール
• チェックツール

それでは見ていきましょう。

請求書発行ツール

ツールとシステムの違いで例として挙げた請求書発行ツールです。売上データから請求書発行を行うツールです。Excel2016ではブックの新規作成時にテンプレートが選択できます。簡易請求書というテンプレートがあるのでこれを選択して請求書のフォーマットを選択しましょう。

新規ブック作成時にテンプレートから選択した請求書のフォーマット

VBAを使わないのであればこの請求書に品目番号や数量と言った項目を手で入力していく事になります。しかし、同じエクセルブック内の別シートに受注一覧表を用意しておけば、たとえば、ある期間の請求書やある得意先向けの請求書を一括で作成すると言った様な事が可能となります。

なお、VBAでツールを作成する場合は、テンプレートにデフォルトで入っている計算式は不要ですので消しておきましょう。

Webスクレイピングツール

Webスクレイピングとはインターネット上の情報を取得する事です。例えば、毎朝8時にウェザーニュースのウェブサイトにアクセスして、きょうの天気予報を取得して共有フォルダに保存すると言った様なことができます。毎日チェックしているニュースサイトなど日常的な情報取得を自動化することができます。

ちなみにウェザーニュースのウェブサイトは下記です。
weathernews.jp

メール自動配信ツール

上でウェザーニュースの天気予報を共有フォルダに取得する例を紹介しました。これをメールで自分宛に配信すると言ったようなことも可能です。

他のアイデアとしては、顧客情報データベースから誕生日の1週間前の人だけを抽出して、誕生日特別割引クーポンをダイレクトメール配信すると言った事が考えられます。お客さんが10人くらいならメールもコピーペーストで送ることができますが、何千、何万の顧客のお客さんにもれなく同じ内容のメールを送るのはムリです。しかも名前の○○様の部分はそのお客さんの名前にしなくてはなりません。これは是非とも自動化して間違いなく、モレなく実行したい業務です。

もちろん、季節セールのお知らせなど「○○様」の部分だけを変えて全員に個別メール配信することも可能です。

このツールを作成するにはまず、顧客一覧表を用意します。顧客の数が数千～数万程度であればエクセルのシートに作成しておいて問題ありません。イメージとしては下の図のような一覧表を作成します。

メール自動配信ツールで利用する顧客一覧イメージ

この一覧表を上から順にチェックしていき、誕生日が本日から1週間以内の人を抽出してメールを作成します。メールを作成する部分のプログラムは別に用意しておいて呼び出すようにすると、いろいろなメールに対応しやすいプログラムにする事ができます。

パワーポイント会議資料更新ツール

会議の為にパワーポイントで資料を作成することがあります。あるいは記録として実績をパワーポイントで残しておく場合もあります。そのような資料で多く目にするのが実績のグラフをパワーポイントに貼り付ける作業です。

よくある手順としてはこのような感じです。

1. エクセルでデータを更新する
2. 折れ線グラフのデータ範囲を1セルだけズラす
3. グラフを画像としてコピーしてパワーポイントに貼り付ける

そしてできあがったパワーポイントのグラフは位置が微妙にズレたり大きさが違って不細工なデキになってしまうという悲しい結果となります。

エクセルVBAを使うと、更新するデータの入力をフォームから入力すると、グラフの範囲を更新してグラフを画像としてコピーしてパワーポイントに貼り付けて保存して閉じるところまで全て自動で行う事ができます。

販売実績更新フォーム

担当者に実績を入力させるときにグラフの元データを更新させると入力間違いが起きる可能性があります。自分の社員番号と実績数値のみを入力させて、昨日のデータを入力すべきセルの位置は本日の日付からプログラムで求めるようにしておきます。こうすることによって入力ミスを防ぐことができます。この入力フォームに会議資料作成担当者用のボタンを配置し、パワーポイントの資料を作成するようにしています。

受注集計ツール

一週間に一回、顧客からの受注の金額をとりまとめる必要がある、と言った場合やあるカテゴリの商品だけ集計して分析したい場合など、いちいちフィルターをかけてコピーペーストで必要なデータを抜き取るのは面倒な作業です。

毎回似たような抽出や集計を行うのであれば、元データから決まった条件でデータを抽出し後工程で利用する形に加工するところまでを自動で行ってしまえば良いのです。イメージとしては、ある日の売上実績の一覧から支店ごとの売上の表を作成する、と言った様な事が考えられます。

売上実績データのサンプル

この売上データを集計して下のような表を出力するというものです。

集計結果のイメージ

集計は分析の基本です。つまり、集計の方法やグラフのまとめ方が決まっていればデータ分析の前処理ができるということです。データ分析と言うとどうしてもPythonが連想されてしまいますが、エクセルは立派にデータ分析ツールとして利用することができます。ということはエクセルを操作するVBAもデータ分析の一翼を担うことができるということです。

また、これまで紹介したツールの機能を利用して、メール配信したり支店ごとの売上推移グラフを更新したりといった工夫でツールの価値を高めることもできます。

顧客データ入力チェックツール

ユーザーがデータを操作したときに、その操作が正しいかどうかチェックを行うことができます。部門内で各メンバーに情報を入力してもらう場合、入力を間違う場合があります。例えば、ハイフンなしで電話番号を書くべきところをハイフンを入れてしまったり、市外局番の両側に不要なカッコをつけてしまったりといった場合、ユーザーに再入力を促したり自動で値を修正したりすることができます。

このように実行ボタンを押したときのみ起動するのではなく、データが入力されたらそのデータが正しいか確認する、といったようにイベントをトリガーにプログラムを実行することもできます。

業務システム

ここまで、ツールについて見てきました。ここからはシステムについて見ていきます。システムとは何かの目的を達成するための仕組みのことで、複数の機能やデータから成り立つのでした。こう考えると、ツールを作ることができれば、それをうまく組み合わせることでシステムができることになります。

ツールの組み合わせによってできるシステムは膨大な種類となります。というか工夫次第で何でもできるといっても過言ではありません。ここでは例として販売管理システムを例として挙げます。

データベースへのアクセス機能を持つ販売管理システム

受注⇒出荷⇒入金という３つの作業の流れからなる業務があり、これを販売業務と呼ぶとします。販売管理システムはこの販売業務を管理するシステムなので、受注、出荷、入金に関するシステムだといえます。受注はさらに請求書発行や倉庫への出庫指示伝票発行、受注金額集計といった作業に分解できます。出荷、入金の業務もそれぞれ複数の業務からなります。つまり販売管理システムはツールの集まりである受注システム、出荷システム、入金システムを統合したものだといえます。

このようにシステムは階層的になる場合があります。むしろ、大抵の場合システムは階層的です。そして受注システム、出荷システム、入金システムというような上位のシステムを構成するシステムの事をサブシステムと呼びます。

一つのエクセルブックに複数のサブシステムを作成するのはあまりお勧めではありません。この場合は３つのサブシステムをまず作成します。そして販売管理システムというマクロブックを作成し、販売管理システムはそれぞれのサブシステムを呼び出す役割だけを持たせるようにします。

そしてVBAはACCESSやMySQLといったデータベースにアクセスすることができます。メール自動配信のところで数万レコード程度ならエクセルシートで間に合うと書きましたが、全社的に利用する販売実績データなどの管理にはデータベースを使うようにします。日々何千件という受注データがあれば、数万行レベルしか実務で耐えられないエクセルでは歯が立たないからです。

エクセル自体は数万行のデータにしか耐えられませんが、エクセルVBAはデータベースへのアクセスや操作が可能です。データベースにVBAを使ってアクセスし、ユーザーはインターフェースとして慣れたエクセルの画面でデータベースを操作できる、といったシステムを構築することができます。

なお、データベースに接続するVBAプログラムを書くためにはVBAだけでなく、データベースそのものとSQLというデータベースを操作する文を勉強する必要があります。また、エクセルの機能ではない機能を借りてきて使う必要があるため、エクセル内で完結するプログラムを作成するより、その分だけ難易度は上がります。

とはいえ、しっかり勉強すれば必ず理解できるので、恐れる必要は全くありません。

その他のプログラム

エクセルVBAは仕事以外の事に使うこともできます。普段エクセルを仕事に使っているため、エクセルVBAは業務改善、自動化の為のプログラミング言語だというイメージがあるかもしれません。しかし、動くプラットフォームがエクセルというだけです。ここでは業務以外のエクセルVBAプログラミングのアイデアを紹介します。

ゲーム

エクセルVBAはアクションゲームを作成することもできます。エクセルVBAの著書で有名な武藤玄さんのウェブサイトを紹介します。シミュレーションゲームやマージャンゲームなど本格的なゲームが提供されています。

home.att.ne.jp

ここまでの本格的なゲームを作成するのは難易度も高いですし、プログラムの量も大変多くなってしまいます。しかし、ピクロス程度のゲームであれば少し勉強すれば十分に開発できます。まずはじゃんけんやおみくじクイズといった簡単なゲームでプログラミングの文法を抑えてからキーボードからの入力を受け取る方法を学んでいけば、徐々に本格的なゲームが作成できるようになっていきます。迷路くらいまではできます。

ネット上でもエクセルによるゲームの動画があるので、そちらも参照してみるとよいと思います。

楽器

エクセルVBAはWindows APIと言うWindowsの機能を借りて利用する技術を使うことができます。これを利用して、Windowsの音を鳴らすことができます。音を鳴らすことができるということは、音の高さや長さも指定することによって変更が可能ということです。というわけで、ピアノが作れます。

エクセルで作ったピアノの鍵盤

上の図はセルの幅と高さを調節し、黒く塗りつぶすことでエクセルシートでピアノの鍵盤を表現したものです。このシートを操作するプログラムとして、セルがクリックされたら音を鳴らすというコードを記述します。クリックされたセルの位置がここならこのヘルツ数の音を鳴らす、という具合にプログラムを記述すればエクセルピアノの出来上がりです。

無料で作曲できるソフトも入手できる昨今、エクセルでピアノが再現できたからって…と否定的にとらえる必要はありません。こういうこともできますよ、ということです。業務システムがツールを組み合わせてできていたように、音を鳴らす方法もエクセルシートにピアノの絵を描くことでピアノを再現できました。何かと何かを組み合わせて何かを作る、そのアイデアが新しいアイデアを生むのだと考えるようにしましょう。

エクセルVBAでできる事の限界

ここまで、エクセルVBAでできることについて述べてきました。この項ではエクセルVBAの限界について説明します。限界を知ることで過度な期待を防ぐことができますし、将来落胆せずに済みます。それに、限界を知るということは、逆を言えばそこまではできるということです。

クロスプラットフォームではない

エクセルVBAはエクセル上で動きます。つまり、エクセルがインストールされているパソコンでないと動きません。よって、組込みソフトウェアはエクセルVBAで作成することはできません。基本的にWindowsを利用しているユーザーの為のツールやシステムしかできません。

高速処理は苦手

「VBAは遅い」と言っている人がいます。しかし、多くの場合それは工夫されていないプログラムの為です。多くの場合はプログラムの内容を工夫することで処理時間を大幅に短縮することができます。しかし、一方で言語仕様的にどうしても高速処理が苦手だと言わざるを得ない場面があります。それは、コンパイルが不要な言語ということとエクセルそのものが重たいということがあります。

パソコンは考える時に０と１の２つの数字の組み合わせを使っています。しかし、プログラミングは０や１を打ち込んで行うわけではありません。For、Next、If、Elseなど英単語を組み合わせでプログラミングをします。英語がもとになっています。このおかげで、プログラミング言語は覚えやすく書きやすくなっています。

そこで、英単語で書いたプログラムを０や１に変換する作業が必要*1となります。その作業をあらかじめやっておく必要がある言語の場合、純粋にプログラムを実行するだけなので、素早く処理ができます。

一方、エクセルVBAは一行ずつ０や１に変換しながら実行しています。つまり、日本語訳された洋書を読むのと、英語を日本語に訳して、訳した日本語を読むのとの違いです。これは繰り返し構文に如実に表れます。なので、なるべく無駄な繰り返しはしないように工夫する必要があるのですが、仕様的にやむを得ない部分があります。

また、言語の仕様ではなくエクセルの仕様という面があります。エクセルVBAは場面的に、エクセルブックを開くことが多いです。何かのエクセルブックを開いて、加工して、保存して閉じる、という処理です。この開くところと保存するところと閉じるところが壊滅的に遅いです。これはエクセルVBAの仕様ではなく、エクセルというアプリケーションソフトの仕様です。

エクセルは誰でも簡単に取り扱うことができるとても便利なアプリケーションです。そして様々な機能を持っています。この誰でも簡単に取り扱えるということと様々な機能を持っているということを同時に実現しようとすると、大変たくさんのプログラムをエクセル自体が持っていることになります。そしてエクセルのブック自体も初期状態で重たいです。

その証拠に空のエクセルを開くだけでも時間がかかります。プログラム言語と関係なくエクセル自体が重いのは仕方がありません。テキストファイルとしてデータを保持し内部的に開いて処理するなどの工夫が必要となります。

エクセルVBAの短所

エクセルVBAの短所は上の【エクセルVBAでできる事の限界】に記した内容があることです。逆に言うとそれだけです。しかし、短所は調書の裏側と言うように、これは仕様の問題と言えます。Microsoftが誰でも気軽に便利に使えるスプレッドシートを作ったのです。エクセルVBAの短所は織り込み済みで便利さと引き換えに遅い、重いという短所もある、という前提の上で、なるべく遅くならないように余計な処理はさせないようにプログラムを工夫するという姿勢が大事です。

あとは、言語仕様ですが、クラスの概念が限定的です。専門的になってしまいますがオブジェクト指向と言われるプログラミング言語はクラスという概念があります。このクラスというのがうまく使うととても便利なのですが、エクセルVBAの場合クラスの機能が隠蔽化のみとなっています。これは言語仕様です。実際これ以外のクラスの概念はエクセルの操作にはほぼ不要です。よって短所と言うより言語仕様的にできないことを選択している、と言ったほうが正確かもしれません。

エクセルVBAの長所

エクセルVBAの調書についてまとめていきます。短所の裏側ですが、身に着けやすさ、実用性が最高に素晴らしいです。

開発環境の構築が不要もしくはめちゃくちゃ簡単

プログラミングをしようと思うと開発環境を整える必要があります。開発環境とは何かというと、プログラムを書くためのエディタであったり、コンパイルをするためのソフトをインストールするということです。インストールするだけではなく、環境設定が必要となります。この環境設定が難しく、プログラムを始める前に挫折してしまうこともあります。

しかし、エクセルVBAの場合は環境設定は必要ありません。なぜならエクセルがインストールされていれば、そのエクセルに開発環境が付属しているからです。あえて言えば、エクセルが入っていないのであればエクセルをインストールすることがエクセルVBAの開発環境を構築する事と同義になります。

文法の取得が比較的容易

別にエクセルVBAの文法が簡単だ、と言うつもりはありません。しかし、エクセルVBAを身に着けるのは他言語に比較して簡単だといえるでしょう。それは勉強するための環境にとても恵まれているからです。理由は下記の通りです。

• すでに使いなれたエクセルの機能を使うことから始められる
• 日本語の文献やWebサイトの解説が充実している
• エクセルがある程度使えれば、PCそのものにそれほど詳しくなくても習得できる

このブログでもたくさんVBAの文法や使い方について解説しています。参考にしてもらえればと思います。

すぐに実務で活用できるものの開発に着手できる

ほかのプログラミング言語だと、ある程度文法を身に着けるところまではいくものの、そこから実際に使えるものが作れるようになるまでの壁があります。文法を勉強しただけでは実務的には役に立たないということです。

その点エクセルは文法を勉強すればすぐに実務で役立つツールを作ることができます。これはなぜかというと、目の前にエクセルブックやエクセルシートといった目に見える操作できるモノがあるからです。勉強した文法もこれらの操作をたくさん扱っています。

そのため、文法を勉強する中で身についたブックを開いたり、保存して閉じるといった操作がそのままツールの一部になっていくわけです。

エクセルVBAでできることまとめ

この記事ではたくさんのエクセルVBAでできることを紹介してきました。そのうえで限界と調書、短所について解説しました。その内容を簡潔にまとめます。

• ツールは単一の機能を持ったプログラム
• システムはツールやデータベースなど複数の要素が組み合わさった仕組み
• エクセルVBAはツールだけでなくシステムを作成することもできる
• Webスクレイピングやパワーポイント操作、Windows APIといったエクセル外のアプリケーションを利用することもできる
• 限界はクロスプラットフォームでないことと、言語仕様的な遅さとエクセルそのものの重さにある
• 速さに関することは工夫次第で相当改善できるが、エクセルそのものの重さの為ブックを開いたり閉じたるする遅さは避けようがない
• エクセルVBAの長所は身に着けやすいところとすぐに実務に生かせるところ

さあ、エクセルVBAでできることと限界がわかったところで、安心してVBAの勉強を始めましょう！

質問、お気づきの点あればコメント欄にお願いします。

＜お知らせ＞
Excel VBAでクラスやオブジェクトの概念と使い方を丁寧に解説し、ワンランク上の実力を目指すガイドが電子書籍になりました。この本で本物の実力を身に付けて一皮むけてみませんか？
books.rakuten.co.jp

*1:本当は中間言語というのを経て０と１にする２段階のステップです

【VBA】大量のエクセルシートにテーブルを設定する｜Power BI対策でテーブルを設定

Power BIというMicrosoft社のBIツールを利用するに当たり、大量にエクセルファイルにテーブルをセットする必要が生じたので、それを解決するVBAのサンプルコードをご紹介します。より深く理解したい方は、関連記事やブログ後半の解説を併せてご覧下さい。

＜関連記事＞

• VBA 上級者から初段へ② 標準モジュールの役割と理想的な処理の流れ
• VBA フォルダの中をループで検索して、きょうの日付のファイルを開く

目次

大量のエクセルシートにテーブルを設定する

単刀直入にサンプルコードを入手したい人の為に、いきなりサンプルコードを書きます。まずはコアとなるテーブルをセットする部分のコードです。

次々にテーブルをセットするサンプルコード

Sub テーブルをセットする()
    
    Dim rw As Long '最終行を取得する
    Dim clm As Long '最終列を取得する
    Dim rng As Range 'テーブルをセットする範囲を取得する
    
    rw = Cells(Rows.Count, 1).End(xlUp).Row
    clm = Cells(1, Columns.Count).End(xlToLeft).Column
    rng = Range(Cells(1, 1), Cells(rw, clm))
    
    ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "テーブル-" & ActiveSheet.Index
    
    ActiveWorkbook.Close SaveChanges:=True
    
End Sub

左端、1行目から表が始まっている前提です。次にデスクトップに保存してあるエクセルファイルに次々にテーブルを設定するように変更します。

Sub テーブル作成の処理の流れをコントロールする()
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'このプロシジャはデスクトップに保存してある全てのエクセルファイルの全てのシートに
'テーブルを設定するための処理の流れを記述する
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    Const folder As String = "C:\Users\hogehoge\Desktop\" 'ここは実際のフォルダ名にする
    Dim temp_file As String '順にファイルをチェックしていくときに、ファイル名を一時的に格納する
    Dim ws As Worksheet '順にファイル内のシートを操作していくときにシートを一時的に格納する
    
    temp_file = Dir(folder & "")  '初期設定
    
    Do While temp_file <> "" 'Dirが何も返さなくなるまで、つまりファイルを全てチェックし終わるまで繰り返す
        
        Workbooks.Open Filename:=folder & temp_file
        
        For Each ws In Worksheets
            ws.Activate
            Call テーブルをセットする
        Next ws
        
        temp_file = Dir() '引数をとらなければ次の未検索のファイルを探す。未検索のファイルがなくなると””を返す
    Loop
    
End Sub

Dir（）関数を使って次々に特定のフォルダ内のファイル名を取得してそのファイルを開いていきます。実際にこのサンプルコードを使う時には、「ここは実際のフォルダ名にする」の部分を実際のフォルダ名に変更します。元のデータをバックアップとして残すために、テーブルセット用のフォルダを用意して、そこにテーブルをセットしたいファイルをコピーして作成しておくのがオススメです。VBAで加工したファイルはCtrl+Zで戻せないので、バックアップを忘れないようにしましょう。

ちょっとだけ解説

自分で自在にこういったコードを作れるようになりたい人や、上記のサンプルコードを改造したい人の為に少し解説します。

テーブルをセットする部分

テーブルをセットする部分のコアとなるコードは下記の通りです。ちなみに手作業でテーブルを設定するにはCtrl+Tです。

ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "テーブル-" & ActiveSheet.Index

ひとつ目の引数はおまじないです。ふたつ目の引数はテーブルを設定する範囲です。みっつ目の引数は空白です。よっつ目の引数が大事で、ここをxlYesにしておくと一行目がヘッダーとしてテーブルを生成します。xlNoにすると全てをデータだとしてテーブルを生成します。BIツールに取り込むのであれば、一行目はヘッダになっているはずなのでxlYesにしています。省略するとエクセルさんが判断してくれますが、明確に決まっているのであればxlYesを指定すべきです。

テーブルを設定すると同時にテーブル名を設定する必要があるので、右辺で代入します。テーブル名がダブらないように「テーブル」という文字列ににシートのインデックス番号を結合したものをテーブル名としました。

次々にブックとシートを呼び出す部分

テーブルをセットする部分を別のプロシジャにしたことによって、それを呼び出す部分に特化したプログラムにしています。
実際、このように特定の機能を別のプロシジャにしないと一つのプロシジャが長くなりすぎますし、ループの中にループがあってさらにその中に処理があるので、可読性が落ちます。

明確に機能が分離できるときはこのように機能と処理の流れを分離するのがオススメです。

VBA フォルダの中をループで検索して、きょうの日付のファイルを開くで解説したように、Dir()関数を使ってフォルダ内検索を行っています。Dir()関数は引数を省略すると、未検索のファイル名を取得する、という挙動を利用しています。

以上、大量の作業や毎日の提携作業に便利なエクセルVBAを使って、業務の効率化を図っていきましょう。

＜関連記事＞

• VBA 上級者から初段へ② 標準モジュールの役割と理想的な処理の流れ
• VBA フォルダの中をループで検索して、きょうの日付のファイルを開く
• VBA ｜初心者向けオススメ独学本レビューまとめ

【プログラミング】文法の勉強だけでは遠回り｜挫折しない上達のコツ

きょうはプログラミング上達のコツについてお話しします*1。私はエンジニアではありませんが、会社で事務職の社員を相手に簡単な自動化プログラムが作れるようにするための講師を務めています。今回お伝えする内容は、どのプログラミング教室を選ぶかよりもずっと大事な考え方です。

正しい勉強法を知って、最短で無理なく無駄なく上達していきましょう。なお、プログラミングが始めて、あるいは始めたものの、いつまでたってもできるようにならない、という方を読者に想定しています。

目次

プログラミングの上達のコツ

AIや人工知能、データサイエンスと言う言葉もすっかり定着し、高度AI人材の需要が高まっています。そんな中、プログラミングやエンジニアに興味を持っている人も多いのではないでしょうか。転職サービスや転職サポートの付いているプログラミング教室の広告も沢山見かけます。それでは早速プログラミング上達のコツに付いて見ていきましょう。

上達が停滞するとモチベーションが下がり、挫折につながる

まずは挫折の原因から見て行きます。私が人に教えたり、自分自身が勉強した経験から言うと、多くの場合、初心者が挫折してしまうのはプログラミングの文法が分からないからではないように思います。

では、どういうときに挫折するかというと、上達が停滞する時です。プログラミングの勉強では概念的に理解が難しい部分がありますが、それはレベルの高い話です。では上達が停滞するとはどういうことかについてみていきましょう。

上達が停滞する時はいつか

ここから本質に迫ります。上達が停滞する時とはズバリ、基本文法のインプットが一通り終わった時です。具体的には繰り返しと条件分岐が終わったあたりです。この段階の弱点は、何か完成品を作ったことがない事です。

正直なところ、一通り基礎文法のインプットが終わった段階と、アプリの完成品を作れるレベルには段差があります。そのため、いままで順調に新しい文法を覚えてきたのにそれを応用する事ができずに停滞してしまうのです。これが上達が停滞するときです。

そして、この段差が乗り越えられないと、停滞が長引き、挫折につながって言ってしまうわけです。

挫折をしないためには上達を停滞させない

ここまで、挫折するのは上達が停滞する時期が長期化するから嫌になるということについて説明してきました。ここまでのことから挫折しないためには上達を停滞させないことが大切だと言えます。それでは上達を停滞させないためにはどうすれば良いかというと、目的に向かって一直線に進むことです。目的とは何をしたいかと言う事です。Webアプリが作りたい、とか、エクセル作業を自動化したい、とか、ゲームを作りたいとか、そういうことです。

プログラミング学習が流行っているから、何となくプログラムの勉強を始めた、と言う人は、文法を一通りインプットするところまでは行きますが、ここに来て目的がないために、成長が止まり、挫折につながっていきます。目的を明確にして単刀直入、ピンポイントでいきなり目的のモノを作り始めればいいと言うことです。

単刀直入、いきなりものづくりを始める

完成度やエラーに強いか、セキュリティなどはこの段階ではどうでもいいです。コアな部分だけでも、どんなに小さい機能でもいいので、とりあえず一つ作りきることが非常に大事です。

始めから使い勝手や完成度、セキュリティまで考慮したモノ作りをするのはまずムリです。思い切って捨てます。捨てて、コアなところに絞ってとにかく完成させます。そうすると、基礎文法は一通りインプットしたつもりでも、案外てが止まりまくりでなかなか前に進まないことに気づくでしょう。

ここが踏ん張り時です。ここで、分からないところを調べて書く事がまさに本当の勉強であり、上達につながる部分です。さっさと基礎文法を終わらせた段階で、難しい文法は後回しにし、一直線に作りたいモノのコアな部分だけ作る。これが最大のコツです。具体的に作りたいモノの作成に取りかかっているので、分からないところを調べる時も調べるべき内容がはっきりしていますし、徐々に完成に近づいていくのが楽しく、モチベーションも保ちやすいです。

そしてやがて、完成度が低いモノができあがります。しかしこの時点で、基礎文法のインプットが終わった段階とは雲泥の違いがあります。非常に大きな一歩です。

完成度が低いモノができた次のステップ

完成度が低いモノができたら、次はその完成度を高めます。しかし、上達という面ではここでは、新たに始めから作るようにすることが重要です。最初に作った完成度の低いモノを改造するのではありません。もう一度始めから作り直します。

理由は、改造するのは難しいことと、始めて作ったものなんてプログラムの流れもぐちゃぐちゃでどうしようもないからです。大工事より最初から作る方が簡単です。一度完成品を作った経験があれば、2週目は初めての時より遙かに早く完成までこぎ着けることができるでしょう。

「完成品を作る」の注意点

ここで注意点が2つあります。それは、写経で作ったモノは完成品に含めないということです。

初心者向けのプログラミング学習書に多い形式として、サンプルのアプリやツールを実際に作りながら基礎文法を学ぶと言うモノがあります。大変分かりやすいですよね。これは、「とにかくまずひとつ完成させ」たモノにカウントしてはダメです。写経で作ったモノは「とりあえず作る」に含みません。ここ、勘違いしやすいので要注意です。写経して一通りその本が終わったら、その本で作ったツールとは違うモノを作ってみましょう。これが「とりあえず何か作る」の何かにカウントできるモノです。

写経が有効なのは、繰り返し構文や条件分岐など基礎文法の習得部分です。というか基礎文法の習得は無料動画でもスクールでも本でも何でもいいです。基礎文法ではそもそもあまり挫折しません。

プログラミングの挫折しない上達のコツまとめ

この記事の内容をまとめます。

• 挫折するのは上達が停滞するから
• 基礎文法では挫折しない
• 挫折しない上達のコツはいきなり目的のモノを作り始めること
• 写経で作ったサンプルアプリは、「とりあえず作る」の実績にカウントしてはいけない
• 基礎文法のインプット部分は本でも動画でもスクールでも何でもいい

それでは、目的のモノをいきなり作り始めましょう！

*1:HTMLにも適用できる考え方です

VBA Errオブジェクトの使い方

VBAによる自動化ツールの作成において、Errオブジェクトの使いどころを解説します。この記事は、VBAのエラーをトラップする事ができるErrオブジェクトを取り上げています。エクセルのErrorクラスとは別モノなのでご注意下さい。

目次

Errオブジェクトの使い方

Errオブジェクトは、実行時エラーが発生した際にエラーの種類を表すエラー番号をプロパティに持つオブジェクトです。このことから、デバッグの差異や、エラーが起きてそれをトラップする事を前提にプログラムを作成するときに使います。

インスタンスを自分で生成する必要はありません。Sheetsとかのオブジェクトと同様、すでに実態がそこにあるオブジェクトです。

実行時エラーのエラートラップを行う

実行時エラーが発生するプログラムを作成する前に、エラーについて簡単に知識を整頓しておきます。VBAによるプログラミングのエラーには、以下の2種類が存在します。

• コンパイルエラー
• 実行時エラー

Errオブジェクトでトラップできるのは実行時エラーのみです。エラーオブジェクトのサンプルコード解説の前に2つのエラーについて簡単に意味をおさらいします。

コンパイルエラーとは

コンパイルエラーとはキーワードや識別子*1の綴りを間違ってしまうなど、プログラムの文法自体が間違っていること。VBAではプログラムを記述していいる最中の改行をする都度とプログラムを実行する時に各プロシジャが始まる直前の2段階でプログラミングの中身が間違っていないかの判断を行っています。

この段階で検出できるエラー、つまりプログラムを実行していないときに検出されるエラーコンパイルエラーです。コンパイルエラーのサンプルを挙げます。

Sub エラーが発生する()
    
    msgbx "こんにちは"
    
End Sub

「こんにちは」と表示するメッセージボックスを出そうとしたところ、MsgBoxの綴りを間違えてしまいました。このプロシジャを実行するとコンパイルエラーが発生します。

実行時エラーが発生する

実行時エラーとは

次に実行時エラーです。実行時エラーとはプログラムの一行一行の記述は文法的に間違っていないものの、実行しようとすると、実行できないエラーのことです。例えば、ブックを開こうとしたものの、指定されたブックのフルパスのファイルが存在しない、とか、存在しないシート名のシートをコピーしようとしたと言ったエラーです。文法的に合っていても、そのファイルやシートが存在するかどうかは実際に実行してみないと分かりません。このように実行時に発生するエラーを実行時エラーと言います。

いか、実行時エラーが発生するプログラムを作成して解説を進めていきます。

Numberプロパティ

それではErrオブジェクトの使い方を見て行きます。まず、実行時エラーが発生するプログラムを作成します。

Sub 実行時エラーが発生する()
    
    Workbooks.Open Filename:="こんな変な名前のファイルありませんよ"
    
End Sub

実行すると、下記の様な実行エラーが発生します。

存在しないフルパスのファイルを開こうとして実行時エラーが発生した

WorkbooksクラスのOpenメソッドはFilename引数で受け取ったフルパスのファイルを開こうとします。フルパスと言う事は「ディレクトリ名￥ファイル名」と言うような文字列です。今回は「こんな変な名前のファイルありませんよ」という存在しないフルパスを指定しているのでエラーになっています。プログラムを記述しているときは分からなかったが、実行してみて始めて存在しないフルパスだと言うことに気づくという実行時エラーが発生しました。

実行時エラーのメッセージボックスの一行目に、実行時エラー '1004':　と言う記述があります。この1004の部分がどのようなエラーか、エラーの種類を表す番号です。これがErrオブジェクトのNumberプロパティとなります。この実行時エラーの番号をトラップできるように、先ほどのプログラムを書き換えてみます。

Sub 実行時エラーが発生する()
    
    On Error Resume Next
    Workbooks.Open Filename:="こんな変な名前のファイルありませんよ"
    Debug.Print Err.Number  'イミディエイトウィンドウに1004と表示される
    
End Sub

On Errorステートメントはエラーが発生したときに処理の流れをコントロールします。Resume Next ステートメントは次に戻ると言う意味です。つまり、On Error Resume Nextで、「エラーが発生した場合にはその行は実行せずに次の行から処理を再開せよ」と言う意味となります。

今回はそのままだとWorkbooks.Openの部分で実行時エラーが発生して、処理が止まってしまうため、On Error Resume NextをWorkbooks.Openの直前に記述する事によって、エラーで処理が止まらないようにしています。そして、その直後にDebug.Pringメソッドでイミディエイトウィンドウにエラー番号を表示させています。

このように、デバッグ時にエラーの種類を特定することができます。

Descriptionプロパティ

次にDescriptionプロパティを利用します。実行時エラーの時に表示されるメッセージボックスの3行名以降に表示されるエラー内容の説明文がErrオブジェクトのDescriptionプロパティに格納されている値です。Descriptionプロパティの内容を取得してみます。

Sub 実行時エラーが発生する()
    
    On Error Resume Next
    Workbooks.Open Filename:="こんな変な名前のファイルありませんよ"
    Debug.Print Err.Description  '「申し訳ございません。～」がイミディエイトウィンドウに表示される
    
End Sub

Workbooks.Openでよく見かけるエラーが今回のサンプルのように存在しないファイルを開こうとした場合です。このときのエラーをトラップする目的ならこのようにDescriptionプロパティを使う事ができます。

Sub 実行時エラーをトラップして処理を分岐する()
    
    On Error Resume Next
    Workbooks.Open Filename:="こんな変な名前のファイルありませんよ"
    
    If InStr(Err.Description, "見つかりません") <> 0 Then
        MsgBox "ファイルが見つかりません。処理を中断します。"
        End
    End If
    
End Sub

これはInstr関数を使って、エラーメッセージに「見つかりません」の文字列が含まれている場合は、実行時エラーのメッセージボックスと処理の中断を発生させずに、自分で用意したメッセージボックスを表示して、処理を中断するプログラムです。

Descriptionプロパティはこのような処理の分岐に使う事もできますが、やや邪道な感はあると思います、ここからは主観になりますが、次の項で理由を説明したいと思います。

On Error Resume Nextの注意点

前項で、On Error Resume Nextをつかって実行時エラーを無視し、Descriptionプロパティでエラー内容をトラップしました。もちろんコレでもエラーを検出する事はできます。しかし、On Error Resume NextはVLOOKUPで検索範囲内に検索値が存在しない値がある事が想定できる場合のように、エラーが発生することが分かっていても防げない場合に利用を限定すべきだと考えます。

理由としてはエラーが発生することが分かっているのであれば、エラーが発生しないようにプログラムを組むのが基本的な考え方だと思うからです。というわけで、Descriptionプロパティでエラー内容をトラップすることも可能ですが、あくまでデバッグに使うのが基本の考え方であると、このブログでは解説を結論づけたいと思い思います。

Errオブジェクトのまとめ

今回の解説の内容をまとめます。

• エラーの種類には大きく分けてコンパイルエラーと実行時エラーが存在する
• Errオブジェクトは実行時エラーをトラップする
• ErrオブジェクトのプロパティにはNumberとDescriptionが存在する
• NumberやDescriptionプロパティを使えば実行時エラーの種類が特定できる
• Errオブジェクトのプロパティを使って処理を分岐することができるが、あくまでデバッグ用に使う事を推奨する

以上、Errオブジェクトの使い所でした。デバッグ作業を効率化して、開発効率を高めていきましょう！

＜関連記事＞

• VBA 中級者から上級者へ⑤ デバッグを効率化する簡単なコツ

＜お知らせ＞
Excel VBAでクラスやオブジェクトの概念と使い方を丁寧に解説し、ワンランク上の実力を目指すガイドを書きました。この本で本物の実力を身に付けて一皮むけてみませんか？
books.rakuten.co.jp

*1:メソッドやプロパティ名、既定のオブジェクト名やステートメントの名前のこと

VBA 開発速度を高める開発手順まとめ

VBAで自動化ツールを作る際の手順をルーチン化しておけば、開発の効率を高めることができます。今回は開発効率を高めるための手順をまとめます。

目次

開発効率を高めるためには開発手順をルーチン化する

自動化ツールに限らず、効率化の要諦はルーチン化にあります。毎回同じ手順で開発ができれば、それだけで開発効率があがりますし、さらに手戻りに強い手順でルーチン化できれば、よりよい効率化を図ることができます。

それでは早速、VBAの自動化ツールの開発効率を高める具体的な開発手順を見ていきましょう。

結論：具体的な手順

具体的な手順は下記の通りです。

1. 概要書受領
2. 要求仕様書を共同で仮作成（完璧を目指さない）
3. 操作画面や機能面の深堀するためのPoC
4. 要求仕様書を共同で修正
5. リリース前のチェックリストを作成
6. 開発用サンプルデータ受領
7. プログラミング着手
8. コアな部分が一通りできたらレビュー
9. 機能の追加、修正を行い、必要なら要求仕様書に反映
10. プログラミング再開
11. リリース前テスト
12. リリース

上記の手順を読んだだけで納得できた方はもう、最後まで読まなくても大丈夫かもしれません。早速、自動化の準備を進めていきましょう。それ以外の方は、最後までお付き合いください。

順番に説明していきます。

概要書受領

開発のきっかけになるものです。この件に着手することを明確にすることが目的です。そのため、堅苦しいフォーマットは不要です。メールで依頼を受けるだけでもOKです。

口頭だけで話を進めるのはやめましょう、ということです。

要求仕様書を共同で仮作成（完璧を目指さない）

キーワードが３つも出てきましたが、大丈夫です。順に解説します。

• 要求仕様書

依頼者が実現したいこと、要するに何がしたいのか、どんな処理をしたいのかを記載した文書のことです。口頭ではなく、文書に残すこといよって、エラーが出たり、想定外の処理が実行された時にこの文書を参照することによって、求められていた機能が実装されていたのか、という事実に基づいた議論ができるようになります。

• 共同

要求仕様書は依頼者側と開発者側の双方を守る開発ドキュメントです。プロのシステム開発では要求仕様は依頼者側が用意するのが当たり前ですが、非エンジニアのちょっとした業務効率改善ツールなら、依頼者と一緒に作るのが現実的です。

また、共同で作成することにより、作成過程での齟齬、勘違いを防ぐことが可能となります。プロは使う言葉を共通にするなどの工夫をすることにより要求仕様をユーザー側で作成できるようにしているようですが、非エンジニアは自動化ツールの作成が本職ではないので、そこまでは不要です。

膝を付き合わせて一緒に要求仕様をまとめるようにしましょう。

操作画面や機能面の深堀りするためのPoC

PoCはProof of Conceptの頭文字をとったものです。意味は「あなたが求めているのはこういうことですよね。これで狙った成果があげられる、と証明してください！」ということです。

私の苦い失敗談があります。ユーザーの言う通りの自動化ツールを作成したものの、利用されなかったということがありました。使ってみた感想が、「んー、まあそういうことだけど、なんか使いにくい。」

依頼されたことを単に実装するだけではダメなのです。実際に使ってみると「なんか違う」とか「勢いで依頼してしまったけど実は緊急性はなかった」ということが少なくありません。

このタイミングで、「ユーザーが必要なこと」と「それは本当にやって意味があること」であることを確認するPoCを作っておくのがよいでしょう。操作画面は実際にプログラムを書かなくてもパワーポイントでイメージ図を作ったり、A4の紙に手書きするだけでも有効です。

要求仕様書を共同で修正

非エンジニアの素人同士が頭を捻って要求仕様書を作ったところで、たかがしれています。PoCをつくると要求仕様に足りないところが出てくるので、ここで、深掘りしていきましょう。

「非エンジニアの素人」とはバカにした表現ではありません。私自身がそうですし。ポイントは、修正があることを前提に設計すべし、ということです。

最初から完璧な要求仕様を作るのは難しいので、要求仕様は修正を前提としてざっくりと作りPoCとその後の修正を大切にするようにしましょう、ということです。

リリース前のチェックリストを作成

依頼者側であるユーザーとのトラブルを防ぐための対策で、大事なステップです。

実際に開発を進める段階ではサンプルデータを使って挙動を確かめながらプログラミングを進めていくことになります。

これらの開発用のサンプルデータとは別にリリース前のテスト内容を決めておくことが大事です。

どういう条件でどんな不具合を出さずにどんな処理結果が出たら、合格、というのをあらかじめ決めておくのです。

たとえば、ユーザーが間違った入力データを選択した場合、処理を中断する、というように、うまくいったとき以外の処理方法についても、どんなテストをすべきかあらかじめ決めておくべきです。

プログラミング着手

下準備が完了したので、実際にプログラミングに着手します。

コアな部分が一通りできたらレビュー

VBAはとりあえずコアな部分だけ先に作ってしまう、ということが可能なプログラミング言語です。エラーの細かい処理は後で実装することにして、真っ先にコアな部分を作成してしまいましょう。

プログラミング開始後のPoCといえる段階です。

機能の追加、修正を行い、必要なら要求仕様書に反映

PoCの確認をしたにも関わらず、やっぱり何かしらの追加の要求が出てきます。
この時に「初めから言ってよ」というおのはNGです。

依頼する側も素人なので、ここも手直しを前提に進めるべきです。あまり細かく初めから作り込みすぎない方がよいゆえんです。

VBAは後から安心してコードを追加できます。

プログラミング再開

追加の要求、想定内の仕様変更を実装して仕上げていきます。

リリース前テスト

リリース前テストのチェックリストに従ってテストを実行します。

ここでのポイントは、ここでエラーが出ているようではダメということです。

リリース前のテストは受かるように準備をすべきです。この段階のテストは言わば、ユーザーが安心して使えるようにするための儀式です。

事前にリリース前のテスト内容は合意しているのですから、そのテストに合格する品質に高めてからリリーステストに望むのが当たり前です。

リリース前テストの目的は、バグ出しではなく、安心感を与えるための儀式。これを肝に命じておきましょう。

リリース

必要に応じて、運用手順書を作成してもらいます。作成するんじゃないですよ。ユーザー側に作成することを促すだけです。

この記事は会社員でちょっとした自動化をVBAで組める人がよりトラブルなく、効率的に開発ができるように考えて書きました。

プロのITの現場とは違う場面が多々あることをご了承ください。

それでは、改善のためのプログラミング作業の改善も図っていきましょう！

＜関連記事＞

• 会社員がVBAで自動化するのに必要な開発ドキュメント３点
• 要求仕様書｜VBAで人の為に作る開発ドキュメント
• チェックリスト｜VBAで人の為に作る開発ドキュメント