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

メタプログラミング on 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 と入力すると、現在選択されているセルの列インデックス値が表示されます。

dev-uttil0.png

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


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

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

解説

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

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


devInfoCellメソッド

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

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

dev-uttil1.png

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

dev-uttil3.png



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

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

dev-uttil6.png

devRangeをコールします。

dev-uttil4.png

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

dev-uttil5.png

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



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

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

dev-uttil6.png

devCellsをコールします。

dev-uttil7.png

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

dev-uttil8.png

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

dev-uttil9.png

実行すると

dev-uttil11.png


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

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

dev-util20.png

devDoUntilEmptyをコールします。

dev-util21.png

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

dev-util22.png

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

dev-util23.png

実行すると

dev-util24.png



シートの指定

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

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

dev-uttil12.png


行ループの指定

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

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

dev-uttil13.png

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

dev-uttil14.png

dev-uttil15.png



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.ページ下部のダウンロードボタンでモジュールファイルを入手します。

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

dev-uttil16.png

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

dev-uttil17.png

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

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

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

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

dev-uttil18.png

ダウンロード

利用上のご注意
  • ダウンロードしたファイルを利用したことにより生じた結果については、利用者ご自身に責任を負っていただきます。
  • ご利用前に使用方法をご確認ください。
  • 当方は成果物の正確性について最善を尽くしますが保証はいたしません。
  • Windows10-64bit Excel2016-32bit環境でのみ動作確認済み。

DevUtil.bas

DOWN LOADボタンが押下された時点で注意事項に同意したものとみなします。

参考サイト

参照設定をせずにクリップボードへデータをコピーする方法は、次のサイトのやり方を使わせて頂きました。

VBA で参照設定せずに DetaObject を使用する

私は「参照設定がExcelのバージョンによって勝手に外れたり、参照できなくなってバグる事件」を経験して以来、参照設定という仕組みが嫌いです。特に今回のような用途ではいちいち参照設定なんかしていられないので、大変貴重な情報でした。ありがとうございます。

related pages
僕の考えた最強のExcel-VBA学習法
若干、いや、かなり偏ってます。

Excelはその圧倒的シェアによりデファクトスタンダードの地位を揺るぎないものにしています。このような状況を鑑みると、どのような業界、業種であろうとも会社がカネをかけて自社内にVBA使いを養成すべきと思っていますが、そのようなところは極めて少数派でしょう。よって自己投資による学習を要します。

Read More ...
Power Automate DesktopでExcelの表データを扱う
Excelデータの取得とFor~eachを使った繰り返し。

デスクトップレコーダーは簡単に利用できますが、人間が操作した作業を自動記録するだけです。これは劇的な業務効率化をなしとげるには必要不可欠な繰り返し・条件分岐といったロジックを組み込むことはできないということを意味します。そこで今回はフローを直接編集することで繰り返し処理を組み込んでみます。

Read More ...
Excelに最適な画面解像度はどれか
Full HDを表示できないモニターは窓から投げ捨てろ!

結論。WQHD(2560×1440px)以上の解像度でFull HD相当まで拡大表示。 私は4kモニターをデュアルで175%拡大表示にして使っています。最強です(自慢)。

Read More ...
この記事の
作成日

2019-10-25

更新日

2020-11-26

ページ内検索
目次
WEB MASTER
さいた
神エクセル撲滅協会理事(自称)
さいたま市民 埼玉こそ地上の楽園