本ページには広告が含まれています。

Excel VBA 配列 InputBoxでセル範囲指定一括代入

Excel VBA 配列 InputBoxでセル範囲指定一括代入

今回から、実際に「配列」をどういう用途で具体的に使うのかという点について、解説するようにしたいと思います。まず手始めは、セルのデータを操作する部分について解説したいと思います

くるみこ
くるみこ

配列」をどういう用途で使うのかについて解説できるように準備していました。Excelで一番使うのはやはりセルのデータを操作することが一番多いのではと思いますので、その部分を具体的に解説していきたいと思います。

セル範囲のデータをコピペしたりすることが多いので「配列」で操作する場合の具体的なことがわかればすごく助かります。
是非よろしくお願いしますm(_ _)m

前回のおさらいはこちらの記事です(^^)/ 下のカードをクリックすれば開きます

くるみこ
くるみこ

前回記事でわかったことは

・LBound関数はインデックス番号の最小値を返す
・UBound関数はインデックス番号の最大値を返す
・Spilit関数は文字列を区切り文字で分割して文字列型の一次元配列にできる

【この記事でわかること
・セル範囲データを一括で配列に入れる方法
・配列に入れたデータをセルに一括で代入する方法
・Application.InputBoxを利用してセル範囲データを設定する方法

スポンサーリンク

セル範囲データを配列に入れ活用する方法

前々回の記事のプチサンプルで「配列のデータを別シートのセルに書き出す」処理を紹介しました。今回に記事では、そのサンプルコードを使ってさらに違った処理方法を紹介していきたいと思います「UBound」配列のサイズを調べるため使用する関数です。それぞれ単独でも使いますが、組み合わせて使うことが多いのであわせて覚えておいたほうがよいでしょう

前々回記事のサンプルコード

'セル範囲データを別シートのセルに書き出す
Sub ArraySample_07()
    Dim db() As Variant      'セルデ-タ処理
    Dim i As Long, j As Long 'Loopカウンタ
    Dim endr As Long        '最終行
    Dim endc As Long        '最終列
 
    'セル範囲取得
    With ThisWorkbook.Sheets("Sheet1")
        endr = .Cells.SpecialCells(xlCellTypeLastCell).Row
        endc = .Cells.SpecialCells(xlCellTypeLastCell).Column

        ReDim db(endr, endc)
        'CurrentRegionプロパティでセル範囲の値を代入
        db = .Range("A1").CurrentRegion.Value
    End With
 
    With ThisWorkbook.Sheets("Sheet2")
        For i = 1 To endr
            For j = 1 To endc
                .Cells(i, j) = db(i, j)
            Next
        Next
    End With
End Sub

・17行目までは、前段の「セルの範囲データをまとめて配列へ代入する方法」の説明で使用したサンプルコードをそのまま使って、別シートに書き出すようにしていました
・書き出し方法は、2次元配列をループ処理でセルごとにひとつづつ書き出す方法でした

・これを前回記事で紹介した「LBound」「UBound」関数を使って次のように書き換えます

セル範囲データを2次元配列で一括代入する

'セル範囲データを2次元配列で一括処理する
Sub ArrayRangeSample_07()
    Dim db() As Variant '動的配列()変数を用意
    Dim sr As Long       '開始行
    Dim sc As Long       '開始列
    Dim er As Long       '最終行
    Dim ec As Long       '最終列
    
    '取得セル範囲を2次元配列に一括代入する
    With ThisWorkbook.Sheets("Sheet1")
        db = .Range("A1").CurrentRegion.Value
        sr = LBound(db, 1)    '1次元の最小値(開始行)
        sc = LBound(db, 2)    '2次元の最小値(開始列)
        er = UBound(db, 1)    '1次元の最大値(最終行)
        ec = UBound(db, 2)    '2次元の最大値(最終列)
    End With
    '2次元配列をセル範囲に一括で代入
    With ThisWorkbook.Sheets("Sheet2")
        .Range(Cells(sr, sc), Cells(er, ec)) = db
    End With
End Sub

・11行目で、CurrentRegionで取得したセル範囲のデータを配列に一括代入しています
・このようにセル範囲は動的配列の大きさを宣言せずに一括で代入することができます
・「LBound」「UBound」関数で2次元配列の大きさを変数に代入しておきます
・19行目で、2次元配列の大きさを取得した変数を使って範囲指定して配列を代入しています

覚えておきましょう

Variant型変数にセル範囲の値を代入した配列のインデックスは必ず「1」から始まります
・ということは、「LBound」しなくていいということです
・Range(”A1”) = Cell(1,1)= 配列(1,1)
・ということは、Range(”A1”)から始まっていない場合、配列のインデックス番号にセル開始位置の行列番号と等しくなるように数値をプラスする必要があるということです

・では、この点を考慮してコードを直してみます

Applicaton.InputBoxでセル範囲を選択設定する方法

Applicaton.InputBoxメソッドの Type:=8「セル参照 (Range オブジェクト)」 を使って、マクロ実行中にセル範囲と貼り付けるセルを指定するように書き換えてみます

Applicaton.InputBoxを使ったセル範囲操作方法

Applicaton.InputBoxを使ってセル範囲操作
'選択セル範囲データを2次元配列で一括処理する
Sub InputRangeSample_07()
    Dim db() As Variant 'セルデ-タ取得用
    Dim sr As Long      '開始行
    Dim sc As Long      '開始列
    Dim er As Long      '最終行
    Dim ec As Long      '最終列
    Dim rng As Range    'セル選択用
    
    'Application.InputBoxで対象範囲のセルを選択させる
    On Error Resume Next
    Set rng = Application.InputBox( _
            Prompt:="取得したい連続したセル範囲を1つ選択してください", _
            Title:="セル選択", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    rng.Worksheet.Activate
    sr = rng.Row        '開始行取得
    sc = rng.Column     '開始列取得
    '取得セル範囲を2次元配列に一括代入する
    db = rng.Value
    Set rng = Nothing
    er = UBound(db, 1)   '1次元の最大値(最終行)
    ec = UBound(db, 2)   '2次元の最大値(最終列)
    'Application.InputBoxで貼付け先のセルを選択させる
    On Error Resume Next
    Set rng = Application.InputBox( _
            Prompt:="貼付け先のセル(表の左上)のセルを選択してください", _
            Title:="セル選択", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    rng.Worksheet.Activate
    sr = rng.Row            '貼付け先先頭セルの行番号
    sc = rng.Column         '貼付け先先頭セルの列番号
    '2次元配列をセル範囲に一括で代入
    Range(Cells(sr, sc), Cells(er + sr - 1, ec + sc - 1)) = db
End Sub

・12行目、最初のApplicaton.Inputboxでは取得したいセル範囲を選択してRangeオブジェクトに代入しています
Inputboxで[キャンセル]ボタンが押された場合、「実行時エラー’424′ オブジェクトが必要です。」エラーが発生してしまうので、11行目に「On Error Resume Next」を設定して回避しています。処理が終わったら「On Error GoTo 0」で元に戻します
・16行目、Is Nothingでデータが選択されたかどうか判定し、未選択の場合中止します
・17行目・31行目で、選択したセル範囲のあるシートをアクティブにしています
・18~19行目で選択範囲最初のセル位置(行と列)番号を取得して変数に代入しています
・21行目で、配列にRangeオブジェクト(選択セル範囲)の値を代入しています
・23~24行目で、配列次元ごとの最大値をUBound関数で取得して変数に代入しています
・27行目で、再びApplicaton.Inputboxで貼り付け(代入)先のセルを選択させています
・36行目で、取得した変数データから計算して貼付け範囲を設定し一括で代入しています

・実は、配列を使わずに「Rangeオブジェクト」だけで同じような操作ができます
Rangeオブジェクト」は直接セル範囲を参照(値の取得ではない)しています
・従って、21行目のようにセル範囲の値(Value)配列に代入しています
配列には値だけが入っていますので高速で貼り付けを行うことができます

Rangeオブジェクトだけでセル範囲を操作する例

最後に、Rangeオブジェクトだけでセル範囲データを操作しているサンプルを提示しておきます

'セル範囲データを別シートのセルに書き出す
Sub RangeSample_07_rng()
    Dim rng As Range
    Dim sr As Long      '開始行
    Dim sc As Long      '開始列
    Dim er As Long      '最終行
    Dim ec As Long      '最終列
    
    'セル範囲取得
    With ThisWorkbook.Sheets("Sheet1")
        Set rng = .Range("A1").CurrentRegion
    End With
        sr = Range(rng(1).Address).Row
        sc = Range(rng(1).Address).Column
        er = rng.Rows.Count
        ec = rng.Columns.Count
    With ThisWorkbook.Sheets("Sheet2")
        .Activate
        .Range(Cells(sr, sc), Cells(er + sr - 1, ec + sc - 1)) = rng.Value
    End With
End Sub


まとめ(おわりに)

・いかがでしたでしょうか?
「いつも汎用でだれでも使えて活用できるように考えてvbaを使う」というポリシーを念頭に今回の記事も書いたつもりです。
実行例の「メッセージ画像」などは多用せず極力割愛しています!
実際に動かして確認してもらいたいという思いからです。是非、動かしてみてください!
サンプルファイルを用意していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

Application.InputBoxメソッドを使う方法で、セル範囲を自由に変えられるように考えたコードの作成ついて紹介しました。固定された決められた位置の表であればコード内に設定しておけばいいのですが、汎用的に変更できた方がいいですよね(^^) いかがでしたか?

はい、そうです! 自由に変えられるのはいいですよね!

すごく勉強になりました(^^)

くるみこ
くるみこ

今回はApplication.InputBoxを使いましたが、セルに設定を書いておいて、その設定を読み込んで使う方法でも自由度が効きますね。次回では、そのあたりのことも入れてみますね(^^) では、次回もどういう用途で使うのかについて継続して解説したいと思います!

・配列に一括代入したセル範囲データは範囲を指定して一括代入すれば速い
・セル範囲の値を代入したVariant型配列のインデックスは必ず「1」から始まる
・Applicaton.InputBoxメソッドの Type:=8 でマクロ実行中にセル指定できる
・セル範囲指定も代入(貼付け)先セルの選択も自由に操作できる

マクロ(VBA)を実行する際は必ずバックアップを取ってから行ってください
・マクロ(VBA)は実行後にファイルを保存すると元に戻すことはできません!
・実行後にファイルを保存せず終了すれば、実行前に戻すことができます!

★★★ ブログランキング参加中! クリックしてね(^^)/ ★★★

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてvbaを使う」というポリシー
当面は「中級者?向けマクロVBA」の記事を継続して書いていきます

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう現在検討中です
・その他雑記的に「小ネタなどいろいろ」・・・・・
・今後の記事にもご期待ください(^^)/

スポンサーリンク
スポンサーリンク

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください