Ribbit.work

VBAでマジックナンバーをスマートに消したい!

VBA

last modified date2021-9-2

publish date2021-9-1

以下のテーブルをVBAで操作するとき、あなたならどうされますか?

番号名前住所電話番号商品オプション
1佐藤北海道090-0000-000おもちゃオプション1
2田中青森090-1111-111おかしオプション2
3本田秋田090-2222-222ゲームオプション3
4岡本新潟090-3333-333スポーツオプション4
5山田山口090-4444-444オプション5

プログラムにおいて、マジックナンバーは原則避けるべきコーディングです。

VBA 以外の言語においては、少し学びさえすればマジックナンバーが現れることはほぼ無いかと思いますが、VBA ではなかなか避けられないケースは多いです。

理由は個人的にですが、主に以下のような理由があるんじゃないかと思っています。

  • 取り扱うデータのほとんどが、シート内のデータや CSV であるため
  • メモリ管理が複雑で、メモリ使用量と可読性が反比例することが多い
  • 現在主流の言語と振る舞いが大きく異なる(熟練度の問題)

扱うデータの性質上、何も考えずにコーディングすればマジックナンバーが自然と生まれてしまいます。今回は上記のテーブルの例を使って、私がよく使う対処方法をコードと共に紹介したいと思います。

対処方法

個人的にオススメ、Collection と Dictionary

個人的に最もオススメの方法が、Collection オブジェクトと Dictionary オブジェクトを組み合わせた、連想配列パターンです。javascript のような、json ファイルを扱うことの多い言語を習得されている方には馴染みやすい方法だと思います。

------------------------------------------------
'
' 対象Rangeを連想配列に変換します
'
' @param r 変換するRangeオブジェクト
' @return 連想配列
'
'------------------------------------------------
Public Function GetCollection(ByRef r As Range) As Collection

    Dim items As Collection, item As Object
    Set items = New Collection

    Dim row As Long, col As Long

    For row = 2 To r.Rows.Count

        Set item = CreateObject("Scripting.Dictionary")

        For col = 1 To r.Columns.Count

            item.Add r(1, col).Value, r(row, col).Value

        Next col

        items.Add item
    Next row

    Set GetCollection = items
End Function

もともと 2 次元配列のように扱うことのできる Range オブジェクトを、わざわざ連想配列に置き換えることは冗長に感じるかもしれませんが、可読性という点ではこの方法が一番気に入っています。

変換をかけた後の Range オブジェクトは、json ファイルで例えると以下のような状態になります。

{
	"1": {
		"番号": "1",
		"名前": "佐藤",
		"住所": "北海道"
		"電話番号": "090-0000-000",
		"商品": "おもちゃ",
		"オプション": "オプション1"
	},
	"2": {
		"番号": "2",
		"名前": "田中",
		"住所": "青森"
		"電話番号": "090-1111-111",
		"商品": "おかし",
		"オプション": "オプション2"
	},
	"3": {...},
	"4": {...},
	"5": {...}
}

データを取り出すときは、以下のように行います。

Dim items as Collection

Set items = GetCollection(Range)

items(1)("名前") ' → "佐藤"

イテレータ(for each)を使えば、更にわかりやすくなります。

Dim items as Collection, item as Object

Set items = GetCollection(Range)

For Each item in items
	item("名前") ' → "佐藤", "田中"...
next

この対処法の欠点はいくつかありますが、まずは速度です。単純にデータを 2 回ループ(配列作成と実処理)させるので、処理速度が落ちます。もう一つは、ヘッダー行の値に重複がないことが条件というところです。重複がある場合・・・など条件を足してしまうと反ってコードを辿りづらくなるため、素直に別の方法を使いましょう。

マジョリティは Enum

マジックナンバーを避ける方法として、最もメジャーだと思われるのが Enum を使った方法です。最初に紹介した Collection, Dictionary を使った方法より初心者にも分かりやすく、メンテナンス性では最も優秀ではないでしょうか。

Enum TableColumn
    番号 = 1
    名前 = 2
    住所 = 3
    電話番号 = 4
    商品 = 5
    オプション = 6
End Enum

定数群を定義しているだけなので、呼び出しは数字を Enum に置き換えるだけで実装可能です。すでに完成しているプログラムのマジックナンバーを取り除く場合も簡単ですね。

Range(1, TableColumn.名前).value ' → "佐藤"

ループさせる場合は以下のような形です。

Dim i as Long
For i = 1 to Range.Rows.Count
	Range(i, TableColumn.名前).value ' → "佐藤", "田中"...
Next

この方法については、特にデメリットはないかと思います。迷ったらこの方法を使いましょう。

最もシンプル、定数

この記事をご覧になっている方は全員ご存知だと思ったので紹介するか迷いましたが、最も処理速度の速い方法として、ヘッダー列を1つずつ定義する方法があります。

Private Const TABLE_COLUMN_番号 As Long = 1
Private Const TABLE_COLUMN_名前 As Long = 2
Private Const TABLE_COLUMN_住所 As Long = 3
Private Const TABLE_COLUMN_電話番号 As Long = 4
Private Const TABLE_COLUMN_商品 As Long = 5
Private Const TABLE_COLUMN_オプション As Long = 6

スコープと型を能動的に指定するため、速度は最速です。ただし、最も重たい部分はループ処理だと思いますので、Enum とはほとんど差はできません。コーディングルールなどに指定がない限りは、Enum で良いかと思います。

この記事を読んだ方におすすめの記事

Excel VBAの高速化はこれだけでOK!コピペで使えるコードを紹介
2021-9-3

Excel VBAの高速化はこれだけでOK!コピペで使えるコードを紹介

VBA
ExcelからChatworkにメッセージを送る
2021-9-3

ExcelからChatworkにメッセージを送る

VBA
ExcelからChatworkのユーザ一覧を取得する
2021-9-3

ExcelからChatworkのユーザ一覧を取得する

VBA
VBAでエラー処理とThrow処理を両方行う方法
2021-8-16

VBAでエラー処理とThrow処理を両方行う方法

ExcelVBA
【参照設定不要】Base64フォーマットにエンコードする
2021-8-16

【参照設定不要】Base64フォーマットにエンコードする

ExcelVBA

最新の記事

特定のHTML要素のclassListを全て削除(リセット)する方法
2021-10-14

特定のHTML要素のclassListを全て削除(リセット)する方法

JavaScript
KintoneからChatworkのユーザ一覧を取得する
2021-9-15

KintoneからChatworkのユーザ一覧を取得する

JavaScriptKintone
TypeScriptで楽天ブックス書籍検索APIを使う📚
2021-9-13

TypeScriptで楽天ブックス書籍検索APIを使う📚

TypeScriptJavaScript
【kintone】Webフォントを適用する
2021-9-8

【kintone】Webフォントを適用する

KintoneJavaScript
KintoneからChatworkにメッセージを送る
2021-9-6

KintoneからChatworkにメッセージを送る

JavaScript