VBAの処理を軽くしたい

質問

各行ごとに5回セルを参照した結果を吐き出すプログラムを作成しています。
200行前後の処理が必要なのですが、現在のコードではあまりにも処理が重くなっています。

とにかく処理を軽くしたいのですが、どのような方法が考えられるでしょうか？
初心者なのでコードも含めてご教示頂けたら幸いです。

Sub test(b, c, d)
Dim i1, i2 As Long
Dim A1 As String
　For i1=0 To Cells(Rows.Count, 1).End(xlUp).Row

For i2 = 0 To 5
If Range(b).offset(i1, i2).Value = "x1" Or Range(c).offset(i1, i2).Value = "x1" Then
A1 = "XXX"
ElseIf Range(b).offset(i1, i2).Value = Range(c).offset(i1, i2).Value Then
A1 = "YYY"
Else
A1 = "ZZZ"
End If
Range(d).offset(i1, i2).Value = A1
Next i2
Next i1

End Sub

回答No.5

ka_na_de · ベストアンサー

＃４です。

前回と基本的に変わりませんが、
記述を整理しました。

Sub test(b, c, d)
　　Dim i1 As Long
　　Dim i2 As Long
　　Dim A1 As String

　　Dim bb As Variant
　　Dim cc As Variant
　　Dim dd As Variant
　　Dim myR As Long
　　Dim myC As Long

　　'データ範囲の行数、列数
　　myR = Cells(Rows.Count, 1).End(xlUp).Row + 1
　　myC = 5 + 1

　　'配列への取り込み
　　bb = Range(b).Resize(myR, myC)
　　cc = Range(c).Resize(myR, myC)
　　dd = Range(d).Resize(myR, myC)

　　'比較処理
　　For i1 = 1 To myR
　　　　For i2 = 1 To myC
　　　　　　If bb(i1, i2) = "x1" Or cc(i1, i2) = "x1" Then
　　　　　　　　A1 = "XXX"
　　　　　　ElseIf bb(i1, i2) = cc(i1, i2) Then
　　　　　　　　A1 = "YYY"
　　　　　　Else
　　　　　　　　A1 = "ZZZ"
　　　　　　End If
　　　　　　dd(i1, i2) = A1
　　　　Next i2
　　Next i1

　　'配列からセルへの転記
　　Range(d).Resize(myR, myC) = dd

End Sub

お礼

ありがとうございます。
非常に分かりやすく助かりました。

回答No.4

ka_na_de

一例です。

Sub test(b, c, d)
　　Dim i1 As Long
　　Dim i2 As Long
　　Dim A1 As String

　　Dim bb As Variant
　　Dim cc As Variant
　　Dim dd As Variant
　　Dim myLastRow As Long

　　myLastRow = Cells(Rows.Count, 1).End(xlUp).Row

　　bb = Range(b).Resize(myLastRow + 1, 5 + 1)
　　cc = Range(c).Resize(myLastRow + 1, 5 + 1)
　　dd = Range(d).Resize(myLastRow + 1, 5 + 1)

　　For i1 = 1 To Cells(Rows.Count, 1).End(xlUp).Row + 1
　　　　For i2 = 1 To 5 + 1
　　　　　　If bb(i1, i2) = "x1" Or cc(i1, i2) = "x1" Then
　　　　　　　　A1 = "XXX"
　　　　　　ElseIf bb(i1, i2) = cc(i1, i2) Then
　　　　　　　　A1 = "YYY"
　　　　　　Else
　　　　　　　　A1 = "ZZZ"
　　　　　　End If
　　　　　　dd(i1, i2) = A1
　　　　Next i2
　　Next i1

　　Range(d).Resize(myLastRow + 1, 5 + 1) = dd

End Sub

お礼

ありがとうございます。
すごいです！圧倒的に速いです!!

回答No.3

ziziwa1130

Dim i1, i2 As Long
これでは変数i1がバリアント型 (Variant) で定義されてしまいます。
数値をバリアント型 (Variant) で宣言した場合は、16 バイトのメモリを必要とし、他のデータ型を明示的に指定した変数よりも、アクセス速度がやや遅くなります。
Dim i1 As Long, i2 As Long
にしたらややスピードアップします。

お礼

ありがとうございます。早速修正しました。
遅い原因の根本は>Range(d).offset(i1, i2).Value = A1にあるようです。
1セルごとではなく、まとめて貼り付ける等で軽くすることは出来ないでしょうか？

回答No.2

KURUMITO

Offset関数の仕様をできるだけ避けるようにしてはいかがでしょう。マクロでなくともOFFSET関数は重くなりますね。

お礼

ありがとうございました。
cells()を使ってoffsetを回避しました。

回答No.1

nagare

どこまで早くなるかわかりませんが、
直接セル参照するのではなく、一旦変数に入れるといいですよ
セル参照自体が遅いというがあります

該当箇所
・For i1=0 To Cells(Rows.Count, 1).End(xlUp).Row
・If Range(b).offset(i1, i2).Value = "x1" Or Range(c).offset(i1, i2).Value = "x1" Then
・ElseIf Range(b).offset(i1, i2).Value = Range(c).offset(i1, i2).Value Then

お礼

ありがとうございます。
早速対応してみました。だいぶ見た目もすっきりしました。