固定のセル(Range)を定数(Const)で定義する

「固定セルを定数で扱いたい！」という場面は結構多いです。

↑この「対象月」や「更新日」のセルとかですね。

ここでいう「固定」とは、位置が固定という意味です。
値を変更したり、取得したりはしたいけど、セルの位置は固定されているから、
常に同じRangeオブジェクトをくれる定数が欲しいな～。ということです。

さてその方法ですが、ズバリ！

ありません。

残念ながら、Rangeに限らず、オブジェクトはConstでは定義できません。

でも焦らないでください。

Constにできないだけで、↓の方法で目的を果たすことはできます。

' なんちゃってRange定数
Function 更新日セル() As Range
    Set 更新日セル = ThisWorkbook.Worksheet("集計シート").Range("E3")
End Function

' 実際の使用例

' 集計マクロ実行時に、更新日セルを本日に更新
更新日セル.Value = Date

' 更新日が対象月の翌月になったら、確定という意味で赤色に
If Month(更新日セル.Value) > 対象月セル.Value Then
    更新日セル.Font.ColorIndex = 3
End If

めっちゃFunctionって書いてありますね。

定数じゃないどころか、変数ですらないです。

でもですね。

• 実行マクロの中で呼び出せば、Rangeオブジェクトとして扱える
• 実行マクロの中で、「集計シートのE3であること」を変えることはできない
• 変更する場合は宣言部分をいじる

なんですよ。

定数だコレ！

使用例を眺めても、どう見ても定数にしか見えませんね。

ということで、この方法で、Rangeを定数「だと思って使う」ことはできます。

Rangeだけでなく、オブジェクトを定数として定義したい場合は、
今回のような「絶対に同じオブジェクトしか返さない関数」を定義することで、
疑似的な定数化を行うことができます。

完璧な答えではなく申し訳ないですが、何かのヒントになれば幸いです。

親のシートが固定でないセルをどう定数にするか

「親のブック、親のシートが完全に固定されたセル」であれば、↑の方法で行けますが、ワークシートが固定でない場合はこのままでは使えません。

集計シート(4月)、集計シート(5月)、…
となるようなやつですね。

それぞれのE2にある更新日セルを、どう指定するか考えましょう。

アドレスをStringの定数で持っておく

' Range("○○")の○○部分をString定数で定義
Const 更新日セルのアドレス = "E2"

' 使用例（更新日を本日に）
Worksheets("4月").Range(更新日セルのアドレス) = Date

このように、セルアドレス"E3"をStringの定数として定義しておき、
Range(アドレスのString定数)でセルを取得することができます。

この方法は、呼び出しコードはそこまできれいにはなりませんが、
定義の方法がお手軽で、メンテナンスが楽なのが利点です。

Const Adrs集計シート_対象月 = "E2"
Const Adrs集計シート_更新日 = "E3"
Const Adrs集計シート_作業者 = "F2"
Const Adrs集計シート_課長押印欄 = "F3"

のように規則性のある変数名にしておけば、
集計シートのセルの位置が変わったときに、ここだけメンテすればよくなりますし、

Adrs + Ctrl + Space で、↓こんな感じの選択肢が出ておすすめです。

Functionの引数で親シートを指定する。

冒頭のなんちゃってRange定数に引数を追加して、親シートを指定してみます。

Function 更新日セル(ws親シート As Worksheet) As Range
    Set 更新日セル = ws親シート.Range("E2")
End Function

例えば4月を処理するときはこんなコードになります。

' 集計マクロ実行時に、更新日セルを本日に更新
更新日セル(Worksheets("4月")).Value = Date

' 更新日が対象月の翌月になったら、確定という意味で赤色に
If Month(更新日セル(Worksheets("4月"))) > 対象月セル(Worksheets("4月")) Then
    更新日セル(Worksheets("4月")).Font.ColorIndex = 3
End If

うーん。イマイチですね。

4月を処理するって決めたら、そこしか処理しないと思いますが、
4月、4月、4月、4月ってしつこいです。

Public変数で処理するシートを指定する

これを解決したのがこちらです。

' 処理するシートをパブリック変数で定義
Public ws集計シート As Worksheet

' なんちゃってRange定数は、パブリック変数のシートを親にして定義
Function 更新日セル() As Range
    Set 更新日セル = ws集計シート.Range("E2")
End Function

↑こんな風に、Public変数でなんちゃって定数の親シートを先に定義しておきます。

そしていざ使うときは、
「まずシートをセット」⇒「後の処理はそのシートに対して行われる」
という仕組みにするわけです。

' 使う際は、まず処理するシートをセットする
Set ws集計シート = Worksheets("4月")

' 以降のなんちゃってRange定数は、セットしたシート内のセルを指す

' 集計マクロ実行時に、更新日セルを本日に更新
更新日セル.Value = Date

' 更新日が対象月の翌月になったら、確定という意味で赤色に
If Month(更新日セル) > 対象月セル Then
    更新日セル.Font.ColorIndex = 3
End If

シートをセットする処理の後は、冒頭と同じ読みやすいコードになりました。

これで「処理するシートを伝票の処理日から取得する」など、
キレイなコードを掛けそうな気がしますね。

定数化は準備が結構大変ですが、
準備が終われば書きやすく読みやすいコードになります。

領収書1枚みたいな単発シートならRange("E3")とベタ打ちでもいいですが、
よく登場する重要なデータや、同じレイアウトで12ヶ月分の12シートがあるなど、
複雑で規模が大きいマクロの場合は、こういった定数化による恩恵が非常に大きくなります。

是非やってみてください。

おまけ：クラスモジュールでやる

実は↑の「Publicでシートを用意して～」な考え方こそ、
オブジェクト指向の根幹だったりします。

今回の例は、超簡単なクラスモジュールのサンプルになります。

クラスモジュールをすでに使っている方は、
私が「まずPublicのワークシート変数を用意して」と説明を始めたときに、
「いやこれクラスでやれや」って思ったんじゃないでしょうか。

クラスモジュールって難しそう…。
オブジェクト指向って難しそう…。

って思ってる人は、
ひとまずその苦手意識を忘れて、今回のコードを書き換えただけだと思って、
↓の例を見てください。

クラスモジュールの名前

クラスはモジュールの名前をコードに使いますので、
名前をちゃんと付けておきます。

クラスモジュールの中身

Public ws As Worksheet

Function 更新日セル() As Range
    Set 更新日セル = ws.Range("E2")
End Function

Function 対象月セル() As Range
    Set 対象月セル = ws.Range("E3")
End Function

パブリックのシートと、なんちゃってRange定数を普通に書きます。

後で出てきますが、これらの変数は「クラスの名前.ws」で指定できるため、
変数名は省略した「ws」だけでいいです。

実行する標準モジュールの書き方

' クラスをインスタンス化（このクラスを使い始めますよ～という、変数の宣言みたいなもの）
Dim くだもの集計シート As New Classくだもの売上集計シート

' クラスのPublic変数「ws」に集計対象のシートをセット
Set くだもの集計シート.ws = Worksheets("4月")


' 集計マクロ実行時に、更新日セルを本日に更新
くだもの集計シート.更新日セル.Value = Date

' 更新日が対象月の翌月になったら、確定という意味で赤色に
If Month(くだもの集計シート.更新日セル) > くだもの集計シート.対象月セル Then
    くだもの集計シート.更新日セル.Font.ColorIndex = 3
End If

Public変数のws集計シートが消えて、代わりにクラスをNewしていますね。

クラスの中に「ws」というPublic変数があり、
「くだもの集計シート.ws」という呼び出しで、そこに4月のシートをセットしています。

セルを呼び出す際は、「くだもの集計シート.更新日セル」で呼び出しています。

ちなみに書くときは、こんな風に選択肢から選んで書くことができます。

これだけです。
これで十分オブジェクト指向なプログラムです。

なんとなく便利そうですが、実際はどんなメリットがあるのでしょうか？

一番は、「一度クラスを作ってしまえば、変数や関数を取り違えることがなくなる」ことです。

ただの定数と変数だと、↓のように、書こうと思えば違う組み合わせでコードが書けてしまいます。

Worksheets("くだもの4月").Range(やさい集計シートの対象月)

このように、バグであろうコードも普通に動きます。
くだものとやさいで名前は違いますが、所詮はただのWorksheetとStringですからね。

しかしクラスモジュールで定義した場合は、
クラス内の変数・関数を「.」から選ぶしかないので、この心配がありません。

こういう風に「同じ処理に使用する関数と変数をまとめて、そこでしか使えないようにする」ことを、オブジェクト指向で言うところの「カプセル化」と呼びます。

シートやセルなどの変数だけでなく、関数も作ることができ、
クラスモジュール内に、

Function Countデータ数() As Long
    Countデータ数 = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row - 5 ' 最終セル行番号 - 5
End Function
Function Get売上合計 As Long
    Get売上合計 = WorksheetFunction.Sum(ws.Range("E6").Resize(Countデータ数))
End Function

これを追加しておけば、
「くだもの集計シート.Countデータ数」でデータの数を、
「くだもの集計シート.Get売上合計」でE列のSUM関数合計を、
いつでも持ってきてくれるようになります。

合計範囲や合計シートを間違ったりする心配もありません。

このカプセル化だけでも、いろいろ便利にしていけそうで、夢が広がりますね。

次のメリットとしては、「クラスをたくさん複製できる」ことです。

集計シートを4月、5月、…と同時に扱う必要があったとします。

Dim 当月集計シート As New Classくだもの売上集計シート
Dim 前月集計シート As New Classくだもの売上集計シート

Set 当月集計シート.ws = Worksheets("5月")
Set 前月集計シート.ws = Worksheets("4月")

' 当月と前月の売上を比較
If 当月集計シート.Get売上合計 > 前月集計シート.Get売上合計 Then
    Call Get社長からのお褒めの言葉
End If

こんな感じで、クラスを複製して、並列で処理できます。

先ほどの「Get売上合計」Functionがうまく機能していて、
「5月シートのE列をSUMで合計」と「4月シートのE列をSUMで合計」を比較するコードが、こんなに簡単にわかりやすく書けています。

クラスは「設計図」みたいなもので、
同じ設計図から、変数のようなものをたくさん作れます。

この変数のようなものを「インスタンス」と呼び、
設計図(クラス)を元にして、変数のようなもの(インスタンス)を作ることを、「インスタンス化」と呼びます。

今回の例では、「Classくだもの売上集計シート」という設計図を使って、
当月用のインスタンスに5月のシートを、
前月用のインスタンスに4月のシートを、
それぞれセットしたわけです。

当月・前月のそれぞれのインスタンスが、
更新日などの各セルや、Get売上合計などの計算値を持ってくれるので、
シートの数だけ変数や定数を準備しなくて良くなるというわけです。

クラスモジュールって、ぶっちゃけそれだけです。

オブジェクト指向という大層な名前があり、
「クラス」「インスタンス化」「カプセル化」「隠蔽」など、他にもたくさん専門用語が出てきますが、

やってることは「コードの整理整頓」です。
別にオブジェクト指向にしかできない高度な処理があるわけではありません。

やってることは、

• 洋服ダンスを買う(クラスを設計)
• タンスの引き出しに「礼服」ってシールを張る(インスタンス化)
• 礼服をしまって、「御霊前」「寿」の封筒も一緒にしまう(カプセル化)
• ピン札と、そうでないお札も一緒にしまい、急な出費に備える(隠蔽)

と何にも変わりません。

Rangeを定数にしたい！という発想がでてくる人は、
オブジェクト指向を扱う素質があると思い、取り上げてみました。

クラスモジュールってなんか難しそうとか思っていた人がいたら、
これを機に始めて見ると、案外とっつきやすいかもしれませんよ？