■ Excel豆知識53_VBAmemo





53_VBAmemo
このページは、豆知識53の各ページの追加説明のリンクページです。それぞれの項目には関連がありません。
------------------------------------------------------------------------------------------
Option Explicit



ページTOPへ
------------------------------------------------------------------------------------------
Sub




ページTOPへ
------------------------------------------------------------------------------------------
名前付けの規則



ページTOPへ
------------------------------------------------------------------------------------------
Dim



ページTOPへ
------------------------------------------------------------------------------------------
データ型




ページTOPへ
------------------------------------------------------------------------------------------
For...Next







ページTOPへ
------------------------------------------------------------------------------------------
With




ページTOPへ
------------------------------------------------------------------------------------------
Set


ページTOPへ
------------------------------------------------------------------------------------------
Offset
A1セルから繋がった範囲は、Range("A1").CurrentRegion で取得することができました。
今度はそこから項目行を除いた範囲を取得したいと思います。
そういう場合には Offset を用います。

VBAのヘルプには下図のようにありますが、言葉が少し難解です。
「指定された範囲からのオフセットの範囲を表す Range オブジェクトを返します。」
というのは、結局、基準になる場所から指定した数の分だけ行数や列数をずらします、ということです。
引数は既定値が0で、省略可能です。ゼロの場合は基準の場所のまま、ということになります。

下図の例で試してみます。

下の9つのコードを実行してみます。

Sub test()
    Range("A1").CurrentRegion.Select
End Sub
下図はこれを実行した図ですが、
試しに Offset() として、Offsetのどちらの引数も省略した形で実行したものも同じ結果です。
Sub test0()
    Range("A1").CurrentRegion.Offset().Select
End Sub
A1セルから繋がった範囲を基準をずらさずに取得します。

次は 行の引数に1を入れました。
Sub test1()
    Range("A1").CurrentRegion.Offset(1).Select
End Sub
範囲の大きさは変わらずに1行下にずれた範囲を取得します。
(今回作成するマクロではこれを使います。)

今度は 行の引数は省略して、列の引数に1を入れました。
カッコの中が「,1」となります。
Sub test2()
    Range("A1").CurrentRegion.Offset(, 1).Select
End Sub
範囲の大きさは変わらずに1列右にずれた範囲を取得します。

次は、行にも列にも引数を指定しました。
Sub test3()
    Range("A1").CurrentRegion.Offset(1, 1).Select
End Sub
範囲の大きさは変わらずに1行下で1列右にずれた範囲を取得します。

上の例では先頭セルがA1ですので、引数にマイナスを入れるとエラーになりますが、
下図のように、左や上に余裕のある範囲ならば負の引数も指定できます。
Sub test4()
    Range("H14").CurrentRegion.Offset(-1, -1).Select
End Sub
範囲の大きさは変わらずに1行上で1列左にずれた範囲を取得します。



Intersect
Offsetでは基準の位置をずらすことは出来ますが、範囲の大きさはそのままです。
なので、「項目行を除いたデータ範囲をぴったり取得したい」などという場合には、
Intersect などを使います。

VBAのヘルプには下図のようにあります。
複数のセル範囲に共通する範囲を取得できます。

Sub test11()
    Intersect(Range("A1").CurrentRegion, Range("A1").CurrentRegion.Offset(1)).Select
End Sub
項目行を除いたデータ範囲のみを取得できます。

この例では特に意味はありませんが、共通する範囲を取得できます。
Sub test12()
    Intersect(Range("A1").CurrentRegion, Range("A1").CurrentRegion.Offset(, 1)).Select
End Sub


Sub test13()
    Intersect(Range("A1").CurrentRegion, Range("A1").CurrentRegion.Offset(1, 1)).Select
End Sub



ページTOPへ
------------------------------------------------------------------------------------------
End



ページTOPへ
------------------------------------------------------------------------------------------
Rows

Rows.Countで、シートの一番下の行が求められますが、
その行数は、Excel2003までは65536で、Excel2007以降は1048576です。

そのため、複数のブックを扱う場合には少し注意が必要です。

自分が使用しているExcelのバージョンが2007以降であれば、
開いたブックが2003までの(.xls)形式であれば、それのシートの行数は65536ですし、
2007以降の形式のExcelブックや、CSVファイルをエクセルで開いたもののシートの行数は1048576という
ことになります。

RowsプロパティのVBAのヘルプには
何のRowsかの指定をしない場合には、アクティブシートのRowsになるよと書いてあります。


エラーになるケースを再現してみます。
<設定>
使用しているExcelのバージョン:Excel2010
マクロのあるブックの形式     :Excel2003までの形式 .xls(シート行数は65535)
アクティブになっているブック :CSVファイルをエクセルで開いたブック(シート行数は1048576)

Sub 取込test を実行するとエラーになります。

デバッグをクリックすると、エラーの行が黄色で表示されます。

ここで、イミディエイトウィンドウを出して、rows.Count を調べてみると 1048576と出ます。
これは、アクティブになっているブックのシートの行数です。

ThisWorkbook というのはマクロコードのある「貼付先Sample.xls」ですから、そのブックのDataシート
には「A1048576セル」は存在しませんのでエラーになってしまいます。

ここで、「貼付先Sample.xls」のほうをアクティブにしてみて、イミディエイトウィンドウでrows.Count
を調べると、今度は65536になります。

この状態で Sub 取込test を実行すると、エラーにならずに操作が実行されます。


上記のようなエラーはRows.Countに「何の」という指定が無かったために、それがアクティブシートの
ものと判断されてしまったために起こりました。

ですので、何のRows.Countかを指定してやれば良い訳です。

ThisWorkbook.Worksheets("Data").Rows.Count というふうにブックとシートをきちんと指定してやれば
どのブックがアクティブになっているかということには関係なく、エラーにならずに欲しい値を取得する
ことが出来ます。
Sub 取込test()
    Dim LastRow As Long

    LastRow = ThisWorkbook.Worksheets("Data").Cells(ThisWorkbook.Worksheets("Data").Rows.Count, 1).End(xlUp).Row
    Workbooks("Sample2.csv").Worksheets(1).Range("A1").CurrentRegion.Offset(1).Copy _
            Destination:=ThisWorkbook.Worksheets("Data").Cells(LastRow + 1, 1)
End Sub
バージョンの混在が無ければ問題はないのですが、こんなこともありますので、出来るだけ「どのブック
のどのシート」なのかを指定したほうが良いと思っています。


ただ、何度も ThisWorkbook.Worksheets("Data") が出てきて鬱陶しいので、Withを利用してまとめたい
と思います。
Sub 取込test()
    Dim LastRow As Long

    LastRow = ThisWorkbook.Worksheets("Data").Cells(ThisWorkbook.Worksheets("Data").Rows.Count, 1).End(xlUp).Row
    Workbooks("Sample2.csv").Worksheets(1).Range("A1").CurrentRegion.Offset(1).Copy _
            Destination:=ThisWorkbook.Worksheets("Data").Cells(LastRow + 1, 1)
End Sub
赤字のThisWorkbook.Worksheets("Data")を、下図のように With ThisWorkbook.Worksheets("Data") 
と、外へ出して、今までThisWorkbook.Worksheets("Data")を指定していた場所には、その後ろのピリ
オドからが残ります。
Sub 取込test()
    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Data")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Workbooks("Sample2.csv").Worksheets(1).Range("A1").CurrentRegion.Offset(1).Copy _
                Destination:=.Cells(LastRow + 1, 1)
    End With
End Sub



ページTOPへ
------------------------------------------------------------------------------------------
MsgBox



ページTOPへ
------------------------------------------------------------------------------------------
If...Then...Else



ページTOPへ
------------------------------------------------------------------------------------------
Exit



ページTOPへ
------------------------------------------------------------------------------------------
InputBox
InputBoxメソッドとInputBox関数があり、用途によって使い分けます。





ページTOPへ
------------------------------------------------------------------------------------------
Dir



ページTOPへ
------------------------------------------------------------------------------------------
Workbooks.Open 



ページTOPへ
------------------------------------------------------------------------------------------
Workbooks.Close



ページTOPへ
------------------------------------------------------------------------------------------
Name




ページTOPへ
------------------------------------------------------------------------------------------
Application.ScreenUpdating



ページTOPへ
------------------------------------------------------------------------------------------
RefreshAll



ページTOPへ
------------------------------------------------------------------------------------------
AdvancedFilter



ページTOPへ
------------------------------------------------------------------------------------------
Do...Loop



ページTOPへ
------------------------------------------------------------------------------------------
ローカル ウィンドウ



ページTOPへ