53_VBAmemo

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

------------------------------------------------------------------------------------------
Option Explicit

------------------------------------------------------------------------------------------
Sub

------------------------------------------------------------------------------------------
名前付けの規則

------------------------------------------------------------------------------------------
Dim

------------------------------------------------------------------------------------------
データ型

------------------------------------------------------------------------------------------
For...Next

------------------------------------------------------------------------------------------
With

------------------------------------------------------------------------------------------
Set

------------------------------------------------------------------------------------------
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」となります。

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

範囲の大きさは変わらずに１列右にずれた範囲を取得します。

次は、行にも列にも引数を指定しました。

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

範囲の大きさは変わらずに１行下で1列右にずれた範囲を取得します。

上の例では先頭セルがA1ですので、引数にマイナスを入れるとエラーになりますが、
下図のように、左や上に余裕のある範囲ならば負の引数も指定できます。

Sub test4()
    Range("H14").CurrentRegion.Offset(-1, -1).Select
End Sub

範囲の大きさは変わらずに１行上で１列左にずれた範囲を取得します。

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

------------------------------------------------------------------------------------------
End

------------------------------------------------------------------------------------------
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

------------------------------------------------------------------------------------------
MsgBox

------------------------------------------------------------------------------------------
If...Then...Else

------------------------------------------------------------------------------------------
Exit

------------------------------------------------------------------------------------------
InputBox

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

------------------------------------------------------------------------------------------
Dir

------------------------------------------------------------------------------------------
Workbooks.Open 

------------------------------------------------------------------------------------------
Workbooks.Close

------------------------------------------------------------------------------------------
Name

------------------------------------------------------------------------------------------
Application.ScreenUpdating

------------------------------------------------------------------------------------------
RefreshAll

------------------------------------------------------------------------------------------
AdvancedFilter

------------------------------------------------------------------------------------------
Do...Loop

------------------------------------------------------------------------------------------
ローカル ウィンドウ