エクセルVBAコーディングガイドライン【随時更新】


guide-line

photo credit: amsfrank DSC00201 via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

弊社でお仕事をする場合に、「こういうコーディングルールでやろうね」というのを、自分自身または一緒にお仕事をするパートナーさんにお伝えすべく、エクセルVBAのコーディングガイドラインを作ろうということになりました。

GoogleドキュメントやEvernoteで共有してもいいのですが、拙著「ExcelVBAを実務で使い倒す技術 」にもほとんど書いてある内容ですし、別に企業秘密とするほどのことでもないので、せっかくなら記事にしてしまえ!

ということで、記事として書かせてもらっています。

あくまで、弊社ではこう…というものなので、いろいろとご意見、好み、その他、色々とあるかもしれませんが、一部でも皆様のご参考になれば幸いです。

スポンサーリンク

命名規則

変数、定数、プロシージャなど、名前には意味が伝わる単語を使います。

例えば…

Dim test as Long, a as String, data as Variant

これではどんな役割を持つ変数なのかわかりません…。

なお、VBAにおける名前は短いに越したことはないですが、長くてもメンバー候補が使えますので、それほど気にしなくてもよいです。

【エクセルVBA】初心者にとっても便利なVBEの自動メンバー表示とその使い方
エクセルVBAの業務効率を上げるためのVBEのテクニックについてお伝えしています。今回は、VBEのステキ機能である「自動メンバー表示」とは何か、また自動メンバー表示を使いこなす方法についてです。

意味がわからないよりは、長いほうがマシです。

変数名

変数名はアルファベットのキャメル記法で書きます。

変数の内容が明確かつ宣言から近い距離で完結するのであれば

  • pass
  • url
  • message
  • number
  • client

などの単一単語での変数名も問題ありません。

そうでない場合、データ型に合わせて、以下接頭語で始める方法もあります。

  • str:String
  • date:Date
  • rng:Range
  • ws:Worksheet
  • wb:Workbook
  • obj:Object
  • el:Element

ただ、VBAの場合、数値型は種類が豊富なのでこの限りではありません。

また、状況に応じて以下接頭語を使うことを推奨します。

  • row:行
  • col:列
  • min:最小
  • max:最大
  • avg:平均

ただし、以下については短くてシンプルな名称でも可読性には影響を与えませんので、使っても問題なしです。

  • i, j, k:カウント変数
  • tmp, buf:一時的な変数
  • obj:一時的なオブジェクト変数

コレクションの変数名

コレクションの場合、複数形の変数名にします。

  • values
  • sheets
  • files
  • elements
For Each el In elements
  '処理
Next el

プロシージャ

メインルーチンなどそのマクロ独自のSubプロシージャは日本語名でもOKです。

ただし、他のプロシージャから頻繁に呼び出される可能性があるSubプロシージャ、またはFunctionプロシージャはアルファベットのパスカル記法でお願いします。

他のマクロに再利用する際を考えると、日本語名でないほうが都合がよい場合が多いからです。

また、各ライブラリで定義されているパブリックなメソッドやプロパティはパスカル記法ですので、独自で定義するパブリックなプロシージャもそれに合わせておきます。

「~かどうか」を返すBoolean型のFunctionプロシージャに関しては

  • Is~
  • Has~
  • Can~

を使うとわかりやすいですし、かっこいいです。

定数

定数はアルファベットのスネーク記法で命名をします。

Const TAX_RATE As Currency = 0.08 '消費税率
Const USER_PASSWORD As String = "hogehoge"

列挙体

「e + 単語」のキャメル記法でネーミングします。

Cellsプロパティの引数として列番号などによく使われますので、単語はなるべく短いワードが望ましいです。

また、ワークシートの列数を表す列挙体の場合は、そのオブジェクト名と同じワードを使うのがおすすめです。

例えばワークシートのオブジェクト名が「wsPref」であれば

Enum ePref
  都道府県名 = 1
  県庁所在地
  人口
  面積
End Enum

と命名します。

変数の宣言

まず、前提として、不必要な変数は増やさないようにしたいところです。

変数が増えるということは、管理すべき対象が増えるということなので、無駄に増やすと可読性やメンテナンス性を低下させることになります。

重複や無駄のないように、最低限必要な変数を準備するようにしましょう。

変数の宣言は強制する

VBEのオプション設定で変数の宣言を強制するようにします。

以下記事にある通り、VBEの「ツール」→「オプション」(アクセスキー AltTO )の「編集」タブ、「変数の宣言を強制する」にチェックで、常に作用します。

エクセルVBA超入門!たった10分でスタート地点に立つための方法
エクセルVBA…まず何をしたら良いかわからない…!という声をよく耳にします。今回はVBAプログラミングをいざ書き始める直前の状態を目標に、エクセルVBAの最低限のセッティングの仕方についてお伝えします。

モジュールの宣言セクションに以下記載がある状態であれば、変数の宣言が必須となります。

Option Explicit

宣言を強制することで

  • 変数名のスペルミスの防止
  • 変数の型を明確にする

などのメリットがあります。

暗黙の型は使わない

型指定をしないとVariant型になりますが、変数の役割が見えづらくなりますので、特に理由がない限りは型を指定をすべきです。

また、以下のような書き方をすると、変数xがVariant型になってしまうので注意が必要です。

Dim x, y As Long

このように記述するのが正しい書き方です。

Dim x As Long, y As Long

また、オブジェクト変数でいうとObject型も便利ではありますが、同様の理由から、できるだけ型指定は詳細にするのが良いです。

スコープはできる限り小さく

原則、変数・定数はプロシージャレベルで宣言をすることで、可読性と安全性が高まります。理由もなくモジュールレベルやパブリックにはしないように…。

また、宣言をするのは、使用する直前での宣言が望ましいです。

プロシージャ冒頭にプロシージャ内で使用するすべての変数を宣言するというしきたりがあったりなかったりしますが、近くにあるほうが

  • 型は何か
  • 宣言時のコメント

といった情報がスクロールなしで得られるようになります。

ただし、定数の場合は、プロシージャの冒頭または宣言セクションでまとめて宣言をしておくのもよいでしょう。後で値を変更するときに、わかりやすい位置にあると便利です。

Integer型は使わない

整数はLong型で統一で問題ありません。

Integer型はメモリ領域をセーブできるというメリットも無きにしも非ずのように見えるかもなのですが、公式にこのように発言されています。

しかし、最近のバージョンでは、Integer型として宣言されていても、VBAはすべての整数値をLong型に変換します。したがって、Integer変数を使用することによるパフォーマンス上の利点はなくなりました。実際、Long変数はVBAで変換する必要がないため、少し速くなる可能性があります。

The Integer, Long, and Byte Data Types

Currency型を使う

小数点を取り扱う金額はCurrency型(通貨型、固定小数点)を使います。

コンピュータはそもそも小数付の計算が苦手で、SingleやDoubleの計算では誤差が発生する場合があるのですが、Currencyなら安心して使えます。

Currencyは小数点以下4桁までを取り扱うことができ、計算時は1万倍して整数にしてから、計算後に1万で割って戻すという処理をしているので、小数付の計算による誤差が出ないのです。

ただし、小数点以下5桁以下は丸められてしまいますので、その点は知っておく必要があります。

コードのフォルム

ネストとインデント

ネストであれば必ずその深さの分のインデントを加えてください。理想としては深さは3つまでがいいですね。

For i = 1 To n
  For j = 1 To n
    Sheets1.Cells(i, j).Value = i * j
    If i = j Then
      Sheet1.Cells(i, j).Interior.ColorIndex = 3
    End If
  Next j
Next i

Withステートメントの活用

Withステートメントを使うとネストは増えてしまいますが、同じオブジェクトを何度も書かなくて済みますし、可読性も上がる場合が多く、コードを書くための強力な武器になります。

With ThisWorkbook.Worksheets("Sheet2")
  Point = .Cells(i, 1).Value 'A列から点数を取得

  '中略

  .Cells(i, 2).Value = grade 'グレードをB列に記入

End With

縦に揃える

一行が長いときには、アンダースコアによる行連結シーケンスによる改行が有効ですが、縦に揃えることを意識すると可読性が高まります。

長いメッセージを記述するときや

Dim m As String
m = ""
m = m & "既にデータが記録されています。" & vbCrLf
m = m & "「はい」をクリックするとデータを上書き、" & vbCrLf
m = m & "「いいえ」をクリックすると処理を中断します。"

引数の多い命令を記述するときに有効です。 

With ThisWorkbook.Worksheets("Sheet1")
  x = WorksheetFunction.SumIfs( _
  .Range("F:F"), _
  .Range("B:B"), .Cells(i, 2).Value, _
  .Range("C:C"), .Cells(i, 3).Value _
  )
End With

コメント

コードを見てわかるコメントは不要

一般的にコードを見ればわかるような内容についてのコメントは不要です。

例えば以下のようなパターン。

If n > 50 Then 'nが50より大きい場合
  MsgBox "50より大きい"
ElseIf n <= 50 Then 'nが50以下の場合
  MsgBox "50より小さい"
End If

コメント入れなくてもわかりますから…情報密度が薄くなっちゃいます。

プロシージャの説明

頻繁に呼び出されることのあるプロシージャであるなら、役割、引数、返り値などについてコメントを入れておきます。

例えば、以下のようなコメントです。

'-----------------------------------------------------------
' 機能: 範囲rngから値varを取得して行(または列)番号を返す
' 引数: rng/検索範囲、var/検索値、strDim/r→行、c→列
' 返り値: 行(または列)番号
'-----------------------------------------------------------
Function findCell( _
  ByRef rng As Range, _
  ByVal var As Variant, _
  Optional strDim As String = "r")

  '処理

End Function

役割や使い方がぱっとわかりますし、再利用もしやすくなります。

マジックナンバーは禁止

マジックナンバーは使用禁止です。メンテナンス性を著しく犠牲にします。マジックナンバー、絶対ダメ!

マジックナンバーになりやすいポイントとしては

  • 行数、列数
  • 係数
  • セルのアドレス
  • 配列やオブジェクトの要素数
  • 引数
  • ファイル名、パス名
  • パスワード
  • URLやメールアドレス

などがあります。

定数や列挙型を使うことで、マジックナンバーを避けましょう。

ユーザーの干渉・省略

テーブルの走査

テーブルの走査にはDo While~Loop文を使います。

繰り返し回数の指定を変更することなく、テーブルのレコード追加や削除に対応できます。

Dim i As Long
i = 2
Do While Sheet1.Cells(i, 1).Value <> ""
  '処理
  i = i + 1
Loop

コレクションの走査

コレクションの走査にはFor Each~Next文を使います。

コレクションのCountプロパティを使えば、For~Next文でも対応はできますが、For Each~Next文のほうがループ内でのオブジェクトの指定が簡潔になります。

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
  Debug.Print ws.Name
Next ws

ワークシートの指定はオブジェクト名で

シートの指定はできる限りオブジェクト名を使います。

  • どのモジュールからでも同名でアクセスできる
  • 可読性が確保できる
  • シート名の変更の影響を受けない
  • 宣言が不要

など大量のメリットがあります。

【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
初心者向けエクセルVBAで請求書を作成するシリーズ。今回は、エクセルVBAで頻繁に取り扱うワークシートを「オブジェクト名」を使って指定する方法です。オブジェクト名とは何か、またその編集方法もお伝えします。

特に、インデックス番号による指定は、シートの並び順に依存するので危なっかしいです。

シート名を省略した場合は、暗黙的にActiveSheetへの処理となります。しかし、ActiveSheetはユーザーの操作によって想定しないシートが対象になることもあるので注意です。

ワークシートの新規作成をした直後であれば、戻り値を使ってオブジェクト変数にすぐさま格納するのが良いでしょう。

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets.Add

ワークブックを明示する

以下のように親オブジェクトであるワークブックを省略することも避けるほうが良いです。

Worksheets("Sheet1").Name

この場合は、ActiveWorkbookが親オブジェクトとして認識されますが、ユーザーの操作によって想定しないオブジェクトが指定されたりします。

基本的にThisWorkbookを使うか、オブジェクト変数で特定をしましょう。

またActiveSheetと同様なのですが、ワークブックを新規作成をした直後には、戻り値を使ってオブジェクト変数に格納しちゃいましょう。

Dim wb As Workbook
Set wb = Workbooks.Open(ThisWorkbook.Path & "\hoge.xlsx")

構造データの使用

スクリプトで主に操作をするスプレッドシートのデータは、特に理由がない限りテーブル形式になるように心がけましょう。

  • A1セルから構成する
  • 空白行・空白列を設けない
  • セルの結合を使わない
  • 見出しは1行で構成する
  • 同じ種類のデータはシートを分けない

つまり、ピボットテーブルを作成できるデータの整理の仕方が望ましいです。

データを構造データで持つかどうかが、それを操作するプログラムの作りやすさに大きく影響します。

コピー&ペースト

Selectメソッドは使わない

コピペ動作をマクロ記録すると

Sheets("Sheet1").Select
Range("A1").Select
Selection.Copy

Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste

などといったコードが出力されますが、コピペにSelectメソッドは不要です。

Selectをするたびにアプリケーションの動作が入り、実行速度が犠牲になりますし、クリップボードを使うのはユーザーの干渉を受ける可能性があり危険です。

以下のよいうに、Copyメソッド一文でOKです。

Sheet1.Range("A1").Copy Sheet2.Range("A1")

セル範囲で処理できるなら一発で

Copyメソッドは範囲でも機能します。

単なるコピペであれば、繰り返しを使って1セルずつコピペをするのではなく、範囲で一気にコピペすることを優先しましょう。

Sheet1.Range("A:A").Copy Sheet2.Range("A1")

実行速度に圧倒的な差が生まれます。

高速化

特に理由がない限り、シートの処理をするのであれば、以下アプリケーションオブジェクトに関するプロパティ群を設定して、高速化を図ります。

'画面の再描画/自動計算/イベント受付を停止
With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
  .EnableEvents = False
End With

'処理

'画面の再描画/自動計算/イベント受付を再開
With Application
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
  .EnableEvents = True
End With
遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法
エクセルVBAの実行速度が遅い・重いって時ありませんか?そんな時にエクセルVBAのプログラムの処理速度を速くするテクニックを紹介します。プログラムの実行時間を測定する方法も合わせてお伝えします。

日付リテラル

エクセルVBAにおいて日付の指定はダブルクォーテーション囲みでも許されていますが、「日付リテラル」つまりシャープ記号囲みがおすすめです。

というのも、日付リテラルを使うことで、日付として受け付けられない文字列をはじくこともできますし、入力後に規定のフォーマットに変換されるので、想定外の値が入力されることも防ぐことができます。

dateTmp = #6/9# '#6/9/2017#に自動変換
timeTmp = #13:15# '#1:15:30 PM#に自動変換

まとめ

以上、エクセルVBAのコーディングガイドラインでした!

どうぞご活用くださいませ。

良いガイドライン見つけたら、随時更新していきますね。


タイトルとURLをコピーしました