セル関連のコードはVBA自身に書かせる

Excel-VBAを扱う上で避けて通れないのが、シート、セルといったExcelのGUIへのアクセスです。そもそも、シートやセルへの表示をいろいろしたいからVBAなわけで、そんなの使わないよっていうのであれば、他の言語で処理した方が簡単でスマートです。

逆に言うと、Excel GUIまわりのやりくりはVBA独特の処理ということになります。その中でもセルの取り回しはもっとも使用する機会が多いのではないでしょうか。

セルを指定する方法にはRangeまたはCellsがあります。いずれもWorksheetオブジェクトのプロパティでオブジェクトブラウザーで調べると次のようになっています。

Property Range(Cell1, [Cell2]) As Range
読み取り専用
Excel.Worksheet のメンバー

Property Cells As Range
読み取り専用
Excel.Worksheet のメンバー

どちらも型がRange【オブジェクト】です。RangeプロパティがRangeオブジェクトを返すという、なかなか紛らわしい構造となっています（しかもRangeオブジェクトにもCellsプロパティがあり、型はRangeオブジェクトです）。

このうち、実践的VBAで使うのは専らCellsです。Rangeプロパティでのセル指定は直感的でわかりやすいので、VBA入門書などでは最初はRangeでのセル指定から入ることが多いですが、Rangeが使えるのは静的なセル参照くらいです。VBAのキモであるループ処理で動的にセル参照したい場合は使い物になりません。Cellsを自由自在に使えるようになることが脱初心者への第一歩です。

少し踏み込んだ前提知識（読み飛ばし可）

Cellsでのセル指定はCells(RowIndex,ColumnIndex)というかたちで行います。具体的にはA1セルならCells(1,1)となり、A2セルならCells(2,1)となります。

ところで、オブジェクトブラウザーではCellsプロパティ（内部的にはProperty Get Cellsというgetterメソッドかな？）には引数をとっていないことがわかります。

じゃあ(1,1)とか(2,1)とかは何なのよというと、Cellsプロパティは正確にはシート全体のRangeオブジェクトを返します。言い換えてCellsプロパティにはシート全体のRangeオブジェクトが格納されていると表現した方がわかりやすいかも知れません。

Cellsプロパティの中身であるRangeオブジェクトにはItemというプロパティがあります。Itemプロパティは(RowIndex,ColumnIndex)のかたちで引数を取り、該当するセル範囲のRangeオブジェクトを返します。

このItemプロパティはRangeオブジェクトの「デフォルトプロパティ」という特殊な位置づけにあります。VBAではデフォルトプロパティは記述を省略することが許されます（！）。私はこのデフォルトプロパティという概念がVBAのオブジェクトへの理解を妨げる主たる原因だと思っていますが、Microsoftとしては初心者でもとっつきやすいように、という考えがあるのでしょうか。

とにかく、この仕様によりItemプロパティを参照するときはコードに書いても書かなくてもいいのです。つまり
Range.Item(1,1)　は　Range(1,1)　と同義です。
※太字はオブジェクトであることを表わします。以下同じ。

オブジェクトのあとにメソッドもプロパティも何も書いていなければVBAが「あっ、デフォルトプロパティでいいのね」と察して勝手にデフォルトプロパティを参照しようとします。その結果デフォルトであるItemプロパティに引数(1,1)がわたり、対象のセル範囲が取得されます。

これと同じ理屈がCellsプロパティに適用されます。すなわち
Cells(1,1)　→　Range(1,1)　→　Range.Item(1,1)
となります。

こうしてCellsプロパティに(RowIndex,ColumnIndex)の形で引数をわたすと該当するセル範囲が取得できるというわけです。

プロパティを省略可能と言えば、RangeオブジェクトのValueプロパティが有名です。
str = Range(“A1”)
とすればA1セルの値がstrに入ります。しかし本来ならば
str = Range(“A1”).Value
とすべきなのです。ところがValueは書かなくても値は取得できます。

この仕様に関して明確な説明が公式リファレンスでは見つけられませんでした。Itemプロパティに関しては「既定のプロパティなので省略化」と明言されています。でもItemプロパティが既定ならValueが省略できるのはおかしくない？既定が二つあるの？他言語でいうtoStringみたいなメソッドが裏でうごいている？

どなたかご存じでしたら教えて欲しいです。

追記：よく調べたらわかりやすーくリファレンスに書いてありました。以下リファレンスより引用。そういうことだったんですね。

Range の既定のメンバーは、パラメーターなしの呼び出しを Value プロパティに転送し、パラメーター付きの呼び出しを Item メンバーに転送します。したがって、someRange = someOtherRangeはsomeRange.Value = someOtherRange.Value、someRange(1)はsomeRange.Item(1)、someRange(1,1)はsomeRange.Item(1,1)と同等です。

Cellsは面倒くさい

Cellsでカッコの中のどっちが行でどっちが列なのか？間違えて逆にデータを書き込んでしまうのは誰でも通る道です。私は未だに間違えます。

さらに列方向はExcel上のアルファベット表記の列名を数値に置き換える必要があります。列数が増えると頭の中で変換する方法では間違える可能性が高くなります。

Cellsを敬遠する理由は大半がこの2つにあるのではないでしょうか。

行列の間違いはインテリセンスでヒントが表示されますので、注意して見ていればわかるのでいいとして、アルファベット→数値変換はひと工夫必要です。どっかで設定するとExcel上で数値表示に変更できた気がしますが、一般的ではないのでこれはなしです。

すぐに思いつくのが、A列からセルにオートフィルで横へ数値を振っていく方法です。この方法では確認のたびにいちいちセルの操作が必要であり、すでにセルにデータがあった場合に困ります。よって下策とします。

次にVBEのイミディエイトを使う方法が考えられます。イミディエイトの使い方については詳しく説明しませんのでGoogle先生にお聞きください。VBAを書いているのにイミディエイトウィンドウが非表示になっているあなた。今すぐ表示させて使い方を勉強してください。今のあなたは必要のない作業に労力を浪費しています。

イミディエイトに　?ActiveCell.Column　と入力すると、現在選択されているセルの列インデックス値が表示されます。

上の画像のようにAZ列はインデックス値が52だとわかります。タイプの手間はありますが、インテリセンスが効きます。セルは選択するだけでいいので、数値を振っていくより遙かに楽です。これは中策とします。

このようにVBAを利用すればセルのパラメータを簡単に取得できることがわかります。それならば、その取得したパラメータを使って、CellsのコードそのものをVBAで作ってしまえば良い。そうすれば行列を間違えることもない。

このようなコンセプトで作成されたのが、今回の本題であり上策のdevUtilモジュールです。

解説

devUtilモジュールはVBAプログラマーを支援する開発用ユーティリティです。

イミディエイトでdevUtilモジュールの各メソッドを実行すると、選択されているセルに応じて情報を表示したり、コードを文字列として生成してクリップボードへコピーしたりできます。コピーしたコードはプロシージャで貼り付けて利用できます。実装されているメソッドは次の通りです。正確にはクラスじゃないのでメソッドと呼べないですが便宜上。

devInfoCellメソッド
選択したセルの情報をイミディエイトウィンドウへ表示します。

次のセルを選択した状態で

devInfoCellをコールすると、セルの情報をイミディエイトウィンドウに出力します。

devRangeメソッド
選択されているセルに応じたRangeプロパティを取得するコードを文字列として生成してクリップボードへコピーします。

次のセルを選択した状態で

devRangeをコールします。

プロシージャで貼り付けます。

あとは必要に応じて、処理を追加してください。

devCellsメソッド
選択されているセルに応じたCellsプロパティを取得するコードを文字列として生成してクリップボードへコピーします。

次のセルを選択した状態で

devCellsをコールします。

プロシージャで貼り付けます。（1行目はdevRangeの実行結果の残骸です）

そのままではシンタックスエラーとなりますので、やりたい処理を書き加えます。例えば次のようにします。

実行すると

devDoUntilEmptyメソッド
選択されているセルから下へセルが空になるまで走査していくDoLoop文を文字列として生成してクリップボードへコピーします。

次のセルを選択した状態で

devDoUntilEmptyをコールします。

プロシージャへ貼り付けます。

処理を完成させます。例えば次のようにします。

実行すると

シートの指定

devRangeメソッド、devCellsメソッド、devDoUntilEmptyメソッドには第一引数として　specifyWorksheet を指定できます。型はBooleanです。Trueで選択しているセルのシートのオブジェクト名をコードに組み込みます。

devCells True　でコールするとこのようにコードが生成されます。

行ループの指定

devCellsメソッドには第二引数として　variableRow　を指定できます。型はBooleanです。行指定をカウンタ変数で置き換えてForループ文の中にCellsプロパティを組み込みます。

devCells True,True　でコールするとこのようにコードが生成されます。

処理を完成させて実行すると

devShowCurrentRegionメソッド
選択セルのCurrentRegionをSelectします。

Selection.CurrentRegion.Selectのショートカットです。メソッド名を短くして使いましょう。私はdevcrにしています。表をごっそり取ってきたいときなどに、CurrentRegionでいけるか確認するのをよくやるので入れています。

その他、セル選択の範囲によって若干挙動が変わりますが、詳しい解説は長くなってしまったので割愛します。ソースコードで確認してください。

ソースコードは次の通りです。

Option Explicit

Sub devInfoCell()
    With Selection
        Debug.Print "------- cell info --------"
        Debug.Print "Address: " & .Address
        Debug.Print "Column: " & .Column
        Debug.Print "Row: " & .Row
        Debug.Print "--------------------------"
    End With
End Sub

Sub devCells(Optional specifyWorksheet As Boolean = False, Optional variableRow As Boolean = False)
    With Selection
        Dim ws As String
        Dim sr As String
        Dim er As String
        Dim statement As String
        
        If specifyWorksheet Then
            ws = .Parent.CodeName & "."
        Else
            ws = ""
        End If
        
        If variableRow Then
            sr = "i"
            er = "i"
            statement = "dim i as long" & vbCrLf & "for i =" & .Row & " to " & .Rows(.Rows.Count).Row & vbCrLf
        Else
            sr = .Row
            er = .Rows(.Rows.Count).Row
        End If
        
        
        If .Count = 1 Or variableRow And .Columns.Count = 1 Then
            statement = statement & ws & "Cells(" & sr & "," & .Column & ")"
        Else
            statement = statement & "Range(" & ws & "Cells(" & sr & "," & .Column & ")," & ws & "Cells(" & er & "," & .Columns(.Columns.Count).Column & "))"
        End If
        
        If variableRow Then
            statement = statement & vbCrLf & "next"
        End If
    End With
    
    setCb statement
    
    Debug.Print "---- Copy to clipboard ----"
End Sub

Sub devRange(Optional specifyWorksheet As Boolean = False)
    With Selection
        Dim ws As String
        
        If specifyWorksheet Then
            ws = .Parent.CodeName & "."
        Else
            ws = ""
        End If
        
        setCb ws & "Range(""" & .Address & """)"
    End With
    
    Debug.Print "---- Copy to clipboard ----"
End Sub

Sub devDoUntilEmpty(Optional specifyWorksheet As Boolean = False)
    With Selection
        Dim ws As String
        Dim statement As String
        
        If specifyWorksheet Then
            ws = .Parent.CodeName & "."
        Else
            ws = ""
        End If
        
        statement = "dim i as long" & vbCrLf & "i = " & .Row & vbCrLf & "do until " & ws & "cells(i," & .Column & ").value = """"" & vbCrLf
        statement = statement & "i = i + 1" & vbCrLf & "loop"
        
        setCb statement
    End With
    
    Debug.Print "---- Copy to clipboard ----"
End Sub

Sub setCb(str As String)
    Dim cb As Object
    Set cb = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    cb.SetText str
    cb.PutInClipboard
End Sub

Sub devShowCurrentRegion()
    Selection.CurrentRegion.Select
End Sub

使用方法

1.ページ下部のダウンロードボタンでzipファイルをダウンロードして展開しモジュールファイルを入手します。

2.モジュールファイルをVBEへドラッグ＆ドロップします。

3.標準モジュールにdevUtilが追加されます。

4.イミディエイトウィンドウでメソッドをコールしてください。
実行結果はクリップボードにコピーされます。プロシージャーの必要なところへペーストしてご利用ください。

開発開始時にモジュールをブックに組み込み、開発が終了したら削除するのが良いでしょう。

話は変わりますが、皆さんの開発環境（物理）はどうなっていますか？今回のようにイミディエイトウィンドウや、ローカルウィンドウなどをフルに使うとデバッグ効率が飛躍的に上がります。会社のPCではそうそう自由にできませんが、趣味プログラマーならば自宅にデュアルモニター以上の環境を推奨します。いや、必須です。

ワークブックとVBEを1画面ずつ表示して同時に確認しながら作業ができると、めちゃくちゃはかどります。会社の10倍は効率が出てますね。どうでもいいとは思いますが私のVBAプログラミング中の画面のスクショはこんなです。フルHD2枚でやっています。