Copy、Pasteメソッドを使わないコピペ

Copy, Pasteメソッドを使わないなら
コピペとは言わないとは思いますが
とりあえずわかりやすいと考え、こう表現しました。

まずは、
なぜワザワザCopy, Pasteを使わないのか？ですが
Copy, Pasteを使うプロシージャを実行しながら
他の作業をしているとき面倒が増えることがあります。

他の作業で手動でコピーをして
ペーストをしたい箇所にカーソルを動かす間に、
裏でExcel VBAがCopy、Pasteをしてしまうと
他の作業で手動でコピーしたデータが
クリップボードからなくなり
また手動でコピーをしなくてはならなくなります。

という理由で個人的には
Excel VBAでCopy、Pasteは使わない方針です。

さて、Copy, Pasteメソッドを使わないコピペとは
どうするのかというと
範囲を直接参照するだけです。

１つのセル同士ならイメージが沸きやすいです。

Range("A1") = Range("A2")

＝で結ぶだけです。

複数のセルでもほぼ同じです。

Range("A1:C10") = _
   Range("D1:F10").Value

複数のセル（範囲）だと「.Value」をつけないと
空白のままで値が入りませんでした。

この方法とコピペには1つの違いがあります。
Copy、Pasteはフォーマットと装飾もコピペされるのに対し
直接参照すると装飾はなくなり、
フォーマットは、
反映されたり、されなかったりです。

ん～
フォーマットは、反映されると言うよりは、
値を入れたときにオートフォーマットされるかされないか
といったほうが正しいです。

装飾はともかく
フォーマットは必要ですので、
次のように方法で移します。

Range("A1:C10").NumberFormatLocal = _
   Range("D1:F10").NumberFormatLocal

サンプルコードの前に
下のプロシージャを実行して準備してください。

下のプロシージャを実行して挿入される
シートmacro101127をアクティブにして
その他のプロシージャを実行してください。

Sub macro101127a()
'macro101127系準備
'A列に現在から1分ごとの時間を入れる

    Dim i As Integer
    Sheets.Add.Name = "macro101127"
    Sheets("macro101127").Range("A1") = "日時"
    For i = 2 To 10
        Sheets("macro101127").Range("A" & i) = _
            Now + TimeValue("00:0" & i & ":00")
        Sheets("macro101127").Range("A" & i).NumberFormatLocal = _
            "yyyy年mm月dd日(aaa) hh:mm"
    Next i
    Columns.AutoFit
   
    Debug.Print "セルA2のフォーマット:" & Range("A2").NumberFormatLocal
   
End Sub

個別から汎用へ進んでいきます。

最初は1列同士のコピペです。
例として
範囲A2:A10を範囲B2:B10へコピペします。

Sub macro101127b()
'Copy, Pasteを使わないコピペ
'一列
'元データをコピー先を同じ大きさの範囲を指定
'すべて同一のフォーマット

    '範囲の値を直接指定する
    Range("B2:B10") = Range("A2:A10").Value
   
    'フォーマットを元データと同じにする
    'すべてのセルが同じフォーマットであること
    Range("B2:B10").NumberFormatLocal = _
        Range("A2:A10").NumberFormatLocal
   
End Sub

通常の手動でコピペする時には
左上の1つのセルだけを選択した状態で
ペーストすることが多いと思います。

次のコードはコピー先をそのように指定します。

Sub macro101127c()
'Copy, Pasteを使わないコピペ
'一列
'一番上のセルを基準にコピペ
'すべて同一のフォーマット

    Dim Range1 As Object 'コピー元を指定
    Dim RCount As Integer 'コピー元行数
    Set Range1 = Range("A2:A10")
    RCount = Range1.Rows.Count
   
    Range("B2").Resize(RCount, 1).Select 'わかりやすく選択
       
    '範囲の値を直接指定する
    Range("B2").Resize(RCount, 1) = Range1.Value
   
    'フォーマットを元データと同じにする
    'すべてのセルが同じフォーマットであること
    Range("B2").Resize(RCount, 1).NumberFormatLocal = _
        Range1.NumberFormatLocal
   
End Sub

上のコードの
コピー元範囲、コピー先の一番上のセルを
それぞれ引数Range1、Range2に指定して実行するように改造したのが
次のコード

Sub macro101127d(Range1 As Object, Range2 As Object)
'Copy, Pasteを使わないコピペ
'macro101127cを引数付に変更
'Range1 = コピー元範囲
'Range2 = コピー先一番上のセル

    Dim RCount As Integer 'コピー元行数
    RCount = Range1.Rows.Count
   
    Range2.Resize(RCount, 1).Select 'わかりやすく選択
       
    '範囲の値を直接指定する
    Range2.Resize(RCount, 1) = Range1.Value
   
    'フォーマットを元データと同じにする
    'すべてのセルが同じフォーマットであること
    Range2.Resize(RCount, 1).NumberFormatLocal = _
        Range1.NumberFormatLocal
   
End Sub

Sub macro101127dexe()
'macro101127d使用例

    'macro101127d(コピー元範囲, コピー先一番上のセル)
    Call macro101127d(Range("A2:A10"), Range("B2"))
   
End Sub

いままでは1列のみでしたが
次のコードの範囲は複数列でもOKです。

For文を使って1つ1つのセルのフォーマットを指定していくので
すべてバラバラのフォーマットでも大丈夫です。

Sub macro101127e()
'Copy, Pasteを使わないコピペ
'一番左上のセルを基準にコピペ
'すべて同一のフォーマットである必要はない

    Dim i As Integer, j As Integer
    Dim Range1 As Object 'コピー元を指定
    Dim Range2 As Object 'コピー先左上を指定
    Dim RCount As Integer 'コピー元行数
    Dim CCount As Integer 'コピー元列数
    Set Range1 = Range("A2:B10")
    Set Range2 = Range("C2")
    RCount = Range1.Rows.Count
    CCount = Range1.Columns.Count
   
    Range2.Resize(RCount, CCount).Select 'わかりやすく選択
       
    '範囲の値を直接指定する
    Range2.Resize(RCount, CCount) = Range1.Value
    Range2.Resize(RCount, CCount).Columns.AutoFit
   
    'フォーマットを元データと同じにする
    '１つ１つ参照するのですべて同じでなくてよい
    For i = 1 To RCount
        For j = 1 To CCount
            Range2.Offset(i - 1, j - 1).Select
            Range2.Offset(i - 1, j - 1).NumberFormatLocal = _
                Range1(i, j).NumberFormatLocal
        Next j
    Next i

End Sub

上のコードをまたまた
引数Range1、Range2に指定して実行するように改造したのが
次のコードです。

Sub CopyPe(Range1 As Object, Range2 As Object)
'Copy, Pasteを使わないコピペ
'一番左上のセルを基準にコピペ
'すべて同一のフォーマットである必要はない
'Range1 = コピー元範囲
'Range2 = コピー先左上のセル

    Dim i As Integer, j As Integer
    Dim RCount As Integer 'コピー元行数
    Dim CCount As Integer 'コピー元列数
    RCount = Range1.Rows.Count
    CCount = Range1.Columns.Count
   
    Range2.Resize(RCount, CCount).Select 'わかりやすく選択
       
    '範囲の値を直接指定する
    Range2.Resize(RCount, CCount) = Range1.Value
    Range2.Resize(RCount, CCount).Columns.AutoFit
   
    'フォーマットを元データと同じにする
    '１つ１つ参照するのですべて同じでなくてよい
    For i = 1 To RCount
        For j = 1 To CCount
            Range2.Offset(i - 1, j - 1).Select
            Range2.Offset(i - 1, j - 1).NumberFormatLocal = _
                Range1(i, j).NumberFormatLocal
        Next j
    Next i

End Sub

Sub macro101127f()
'CopyPe使用例

    Call CopyPe(Range("A2:B10"), Range("C2"))
   
End Sub

Copyメソッドと同じような使い心地を目指しました。

ただフォーマットを指定するところで
１つ１つセルを回しているので
あまり広い範囲のコピペには時間がかかりそうです。

セルの値を配列に格納して使う

Excelはセルの値を直接使えば、
わざわざ配列を使わなくても済むということを
以前の記事で書きました。

しかし、配列に入れて使ったほうが
処理が速い場合もあるようです。

最初に
配列の添え字の最小値を1にするために
使用するモジュールの一番上に

Option Base 1

を入力してください。

セルの値を配列に格納して使う方法の基本の流れは次のようです。

1.Variant型の変数と出力用配列を宣言する
2.Variant型の変数にセルの値を入れる(これが配列になる)
3.出力用配列のサイズを指定
4.Variant型の変数に入れた値を処理して出力用配列に入れる
5.出力用配列の値をセルに入れる

まずは１列のデータから１列のデータを出力してみます。

セルのA1からA5に１から５までの数字が入っているとします。
これを２乗する処理を配列を使ってやります。
出力用配列のサイズ指定に注意してください。

ReDim dOutput(UBound(vData))

と指定すると1行5列の配列になってしまいます。

コードはこちら

Sub macro101120a()
'セルを配列に入れて使う
'1列のデータから1列のデータを出力

    Dim i As Long

    '1.Variant型の変数と出力用配列を宣言する
    Dim vData As Variant
    Dim dOutput() As Double
   
    '2.Variant型の変数にセルの値を入れる(これが配列になる)
    vData = Range("A1:A5") '5x1

    '3.出力用の配列のサイズを指定
    ReDim dOutput(UBound(vData), 1) '5x1

    '4.Variant型の変数に入れた値を処理して出力用配列に入れる
    'ここでは2乗している
    For i = 1 To UBound(vData)
        If IsNumeric(vData(i, 1)) Then
            dOutput(i, 1) = vData(i, 1) * vData(i, 1)
        End If
    Next i

    '5.出力用配列の値をセルに入れる
    Range("B1").Resize(UBound(dOutput, 1), 1).Select 'わかりやすく選択する
    Range("B1").Resize(UBound(dOutput, 1), 1) = dOutput '5x1

End Sub

実行前

実行後

B1からB5に2乗された値が入力されました。

次は２列のデータから１列のデータを出力してみます。
上の画像のA1:B5のデータを使って足し算したものを
C1:C5に出力します。

Variant型の配列の最大の添え字を返すUBoundの引数を
上のコードから変更しています。

コードはこちら

Sub macro101120b()
'セルを配列に入れて使う
'2列のデータから1列のデータを出力

    Dim i As Long

    '1.Variant型の変数と出力用配列を宣言する
    Dim vData As Variant
    Dim dOutput() As Double
   
    '2.Variant型の変数にセルの値を入れる(これが配列になる)
    vData = Range("A1:B5") '5x2

    '3.出力用の配列のサイズを指定
    ReDim dOutput(UBound(vData, 1), 1) '5x1

    '4.Variant型の変数に入れた値を処理して出力用配列に入れる
    'ここでは2乗している
    For i = 1 To UBound(vData, 1)
        If IsNumeric(vData(i, 1)) And IsNumeric(vData(i, 2)) Then
            dOutput(i, 1) = vData(i, 1) + vData(i, 2)
        End If
    Next i

    '5.出力用配列の値をセルに入れる
    Range("C1").Resize(UBound(dOutput, 1), 1).Select 'わかりやすく選択する
    Range("C1").Resize(UBound(dOutput, 1), 1) = dOutput '5x1

End Sub

実行後

最後に２列のデータから２列のデータを出力してみます。
前のコードでも使ったA1:B5のデータを使って
足し算と掛け算をしたものを出力します。

出力用の配列のサイズの指定と
出力用の配列をセルに入れるときの範囲の指定のところに
変更を加えました。

コードはこちら

Sub macro101120c()
'セルを配列に入れて使う
'2列のデータから2列のデータを出力

    Dim i As Long

    '1.Variant型の変数と出力用配列を宣言する
    Dim vData As Variant
    Dim dOutput() As Double
   
    '2.Variant型の変数にセルの値を入れる(これが配列になる)
    vData = Range("A1:B5") '5x2

    '3.出力用の配列のサイズを指定
    ReDim dOutput(UBound(vData, 1), 2) '5x2

    '4.Variant型の変数に入れた値を処理して出力用配列に入れる
    'ここでは2乗している
    For i = 1 To UBound(vData, 1)
        If IsNumeric(vData(i, 1)) And IsNumeric(vData(i, 2)) Then
            dOutput(i, 1) = vData(i, 1) + vData(i, 2)
            dOutput(i, 2) = vData(i, 1) * vData(i, 2)
        End If
    Next i

    '5.出力用配列の値をセルに入れる
    Range("C1").Resize(UBound(dOutput, 1), UBound(dOutput, 2)).Select 'わかり

やすく選択する
    Range("C1").Resize(UBound(dOutput, 1), UBound(dOutput, 2)) = dOutput '5x2

End Sub

実行後

これらの例は簡単な計算ですので
ワークシート関数を使ったほうが速いです。

参考URL：Excel 2007 におけるパフォーマンスの改善

商と余り

基本的なことですが
しっかり調べてみるといろいろと知らないことがあるものです。

いままで紹介してきたプロシージャでも
商と余りは利用してきました。
最近の記事ではこのように使いました。

商： Int( i / j )
余り： i - Int( i / j ) * j

除数と被除数が共に０より大きいなら問題ないですが
どちらがマイナスのときInt関数では間違いであることがわかりました。

この式を使うなら、
Fix関数を使わないと
答えがマイナスのときに値が違ってきます。

その理由は
Int関数とFix関数は引数が正の値なら同じ値を返しますが
引数が負の値のとき
Int関数はその値を超えない最大の負の値を返して
Fix関数はその値以上の最小の負の値を返すからです。
VBAヘルプの例をそのまま使いますが具体的には

Int(-8.4) = -9
Fix(-8.4) = -8

-8.4 = -84 / 10 = -8 余り -4
ですから上の式でマイナスも扱うならFix関数が適切です。

まあこんな式を使わなくても
便利な演算子があります。

商： i \ j
余り： i Mod j

Mod演算子は余りを使用するとき使うので知っていましたが、
\の方は恥ずかしながら新発見でした。

使用例のコードはこちら

Sub macro101113a()
'商と余り

    Dim i As Integer, j As Integer
    i = 11: j = 4
    Debug.Print i & "を" & j & "で割ったときの商は " & i \ j & _
        " 、余りは " & i Mod j
       
End Sub

商と余りを使って
ある数からある数までの繰り返す数を生成したいと思います。
実際のところ「余り」しか使ってませんが…

0と1の繰り返し、
1から10までの繰り返し、
1から任意の数までの繰り返し、
を生成する３つのプロシージャです。

コードはこちら

Sub macro101113b()
'0と1を繰り返し生成する

    Dim i As Integer
    For i = 0 To 19
        Debug.Print i Mod 2
    Next i
   
End Sub

Sub macro101113c()
'1から10までの数字の繰り返しを生成する

    Dim i As Integer
    For i = 0 To 99
        Debug.Print i Mod 10 + 1
    Next i
   
End Sub

Sub macro101113d()
'1から任意の整数までの数字の繰り返しを生成する

    Dim i As Integer, j As Integer
    j = 7
    For i = 0 To 99
        Debug.Print i Mod j + 1
    Next i
   
End Sub

割り算で忘れてはいけないことは
0で割らないことです。

0で割るともちろんエラーになります。

コピペ関係の原因不明のミスで
値が入っているはずのセルを参照したつもりが
空白のセルの値で割ってしまうことがたまにあります。

割り算を使った式でエラーになったときは
分母をウォッチウィンドウで調べてみてください。
0かもしれません。

1時間の内で15分ごとに実行する

プロシージャを繰り返し実行する為に
OnTimeメソッドを使った方法を
記事「 一定時間ごとに繰り返し実行する 」で取り上げました。

上記の記事の方法だと
一番最初にプロシージャを実行した時間から
15分後に再度プロシージャが実行されます。
この繰り返しです。

実際は、ほかのプロシージャが実行中なら
それが終わってから実行されるため時間がずれることもあります。
また、実行するプロシージャ自身が時間がかかるため
ここでも時間がずれていきます。

ずれても問題がなければいいのですが
例えば1時間の内の0分から14分、15分から29分、30分から44分、45分から59分までの
それぞれの時間の間で1回づつ実行したい場合など
タイミングが悪いと実行できない時間帯が出てくるかもしれません。

そこで1時間の内で15分ごとに実行する方法を考えてみます。
プロシージャを繰り返し実行するのには
上記記事と同様にOnTimeメソッドを使います。
OnTimeメソッドの時間の指定を工夫します。

1時間のうちの15分ごとに実行すればよいので
テキトーに5分、20分、35分、50分に実行するようにします。

0分から14分、15分から29分、30分から44分、45分から59分の時間帯を
それぞれ時間帯0、時間帯1、時間帯2、時間帯3とします。

プロシージャを実行している時間帯で条件分岐して
次回の時刻を決定したいと思います。

具体的には、

0分から14分は、次回の実行時刻は20分
15分から29分、次回の実行時刻は35分
30分から44分、次回の実行時刻は50分
45分から59分、次回の実行時刻は5分

になります。

時間帯を区別するには次の式を使います。

Int(Minute(Now) / 15)

つまり、分を15で割った商です。

0分から14分は、Int(Minute(Now) / 15) = 0
15分から29分、Int(Minute(Now) / 15) = 1
30分から44分、Int(Minute(Now) / 15) = 2
45分から59分、Int(Minute(Now) / 15) = 3

になります。

下のプロシージャについての簡単な説明をします。
Dateは現在の日付を返します。
TimeSerial関数は引数で指定した時、分、秒に対応する時刻を返します。
例えば、
TimeSerial(12, 32, 08) = 12時32分8秒
詳細はVBAヘルプにあります。

コードはこちら

Sub macro101106a()
'1時間の内の15分ごとに実行する

    Dim MyTime As Date
   
    '処理
    Debug.Print "現在の時刻：" & Now
   
    '次回の実行時刻を設定
    Select Case Int(Minute(Now) / 15)
        Case 0
            MyTime = Date + TimeSerial(Hour(Now), 20, 0)
        Case 1
            MyTime = Date + TimeSerial(Hour(Now), 35, 0)
        Case 2
            MyTime = Date + TimeSerial(Hour(Now), 50, 0)
        Case 3
            MyTime = Date + TimeSerial(Hour(Now), 65, 0)
    End Select
   
    'MyTimeにmacro101106a自身を実行
    Debug.Print "次の実行時間：" & MyTime
    Application.OnTime MyTime, "macro101106a"

End Sub

さて、プログラムとしてこれで機能すると思いますが、
条件分岐なんか使わずに
1つの式でやっちゃいたい願望があるので
別の方法も考えてみます。

まずは時刻設定の説明用のプロシージャです。
i を Minute(Now) の代わりにしています。
「15分の区切り」とは15、30、45、60(0)のことです。

Sub macro101106b()
'1時間の内の15分ごとに実行する
'時間を指定する式についての準備
'iを「分」とする (0 <= i <= 59)

    Sheets.Add
    Dim i As Integer
   
    'タイトル
    Cells(1, 1) = "i = 分"
    Cells(1, 2) = "Int(i / 15)" & Chr(10) & _
        "=現在(分)の時間帯"
    Cells(1, 3) = "(Int(i / 15) + 1) * 15" & Chr(10) & _
        "=現在(分)の次の15分の区切り"
    Cells(1, 4) = "(Int(i / 15) + 1) * 15 - i" & _
        Chr(10) & "=現在(分)の次の15分の区切りまでの時間（分）"
    Cells(1, 5) = "(Int(i / 15) + 1) * 15 - i + 5" & _
        Chr(10) & "=左に5分を足したもの（分後に実行）"
   
    For i = 0 To 59
        Rows(2).Insert
        '現在(分)
        Cells(2, 1) = i
        '現在(分)は1時間のどの時間帯か
        Cells(2, 2) = Int(i / 15)
        '現在(分)の次の15分の区切り
        Cells(2, 3) = (Int(i / 15) + 1) * 15
        '現在(分)の次の15分の区切りまでの時間（分）
        Cells(2, 4) = (Int(i / 15) + 1) * 15 - i
        '上に5分を足したもの
        Cells(2, 5) = (Int(i / 15) + 1) * 15 - i + 5
    Next i
    Columns("A:E").ColumnWidth = 100
    Columns("A:E").AutoFit
    Rows(1).AutoFit
   
End Sub

次が条件分岐なしで時間を設定する方法のプロシージャです。
コードはこちら

Sub macro101106c()
'1時間の内の15分ごとに実行する

    Dim MyTime As Date
   
    '処理
    Debug.Print "現在の時刻：" & Now
   
    '次回の現在からの実行時刻を設定
    MyTime = TimeSerial(0, _
            (Int(Minute(Now) / 15) + 1) * 15 - Minute(Now) + 5, _
            -Second(Now))
   
    '現在からMyTime後にmacro101106c自身を実行
    Debug.Print "次の実行時間：" & Now + MyTime
    Application.OnTime Now + MyTime, "macro101106c"
   
End Sub

さらに柔軟性を高めて
固定した間隔ではなく
x 分ごとに実行する方法です。
説明用のコードはこちら

Sub macro101106d()
'1時間の内のx分ごとに実行する
'時間を指定する式についての準備
'iを「分」とする (0 <= i <= 59)

    Sheets.Add
    Dim i As Integer
    Dim x As Integer '(1 < x)
    x = 10
   
    'タイトル
    Cells(1, 1) = "i = 分"
    Cells(1, 2) = "Int(i / x)" & Chr(10) & _
        "=現在(分)の時間帯"
    Cells(1, 3) = "(Int(i / x) + 1) * x" & Chr(10) & _
        "=現在(分)の次のx分の区切り"
    Cells(1, 4) = "(Int(i / x) + 1) * x - i" & Chr(10) & _
        "=現在(分)の次のx分の区切りまでの時間（分）"
    Cells(1, 5) = "(Int(i / x) + 1) * x - i + Int(x / 2)" & _
        Chr(10) & "=左にInt(x/2)分を足したもの(分後に実行）"
   
    For i = 0 To 59
        Rows(2).Insert
        '現在(分)
        Cells(2, 1) = i
        '現在(分)は1時間のどの時間帯か
        Cells(2, 2) = Int(i / x)
        '現在(分)の次のx分の区切り
        Cells(2, 3) = (Int(i / x) + 1) * x
        '現在(分)の次のx分の区切りまでの時間（分）
        Cells(2, 4) = (Int(i / x) + 1) * x - i
        '上にInt(x/2)分を足したもの
        Cells(2, 5) = (Int(i / x) + 1) * x - i + Int(x / 2)
    Next i
    Columns("A:E").ColumnWidth = 100
    Columns("A:E").AutoFit
    Rows(1).AutoFit
   
End Sub

次が
x 分ごとの条件分岐なしで時間を設定する方法のプロシージャです。
コードはこちら

Sub macro101106e()
'1時間の内のx分ごとに実行する
'条件、 1 < x <= 60 かつ xは60の約数

    Dim MyTime As Date
    Dim x As Integer
    x = 10
   
    '処理
    Debug.Print "現在の時刻：" & Now
   
    '次回の現在からの実行時刻を設定
    MyTime = TimeSerial(0, _
            (Int(Minute(Now) / x) + 1) * x - Minute(Now) + Int(x / 2), _
            -Second(Now))
   
    '現在からMyTime後にmacro101106e自身を実行
    Debug.Print "次の実行時間：" & Now + MyTime
    Application.OnTime Now + MyTime, "macro101106d"
   
End Sub

定数xで間隔を指定します。
ただし、x に条件があります。