エクセルVBA

質問

Sub PlusA001()
Dim a As Range
Dim b As Integer
Range("e1").Value = "氏名"
Range("e2").Value = "甲"
Range("e2").AutoFill Destination:=Range("e2:e10"), Type:=xlFillDefault

Range("f1:j1").Value = Array("国", "数", "理", "社", "英")
Set a = Range("f2")
For i = 1 To 5
Do Until b = 9
a.Value = Int(100 * Rnd) + 1
b = b + 1
Set a = a.Offset(1, 0)
Loop
b = 0
Set a = a.Offset(-9, 1)
Next i

End Sub

サンプルコードの例ですが、どうも実行しても納得できない部分があります。それはSet a=a.offset(-9,1)の部分です。Set a = Range("f2")においてf2を始点としているのは判りますが、ｆ２からであればa=a.offset（－９、５）
とすればいいのかと思い実行したのですが、ぐちゃぐちゃになります。なぜ（－９、５）ではなく（－９、１）何ですか？いくら読み解いても判りません。教えてください。

回答No.1

noname#112806 · ベストアンサー

順を追って考えてみましょう。
まず、最初にaは「F2」の位置にあります。
そして、
Do Until b = 9
　　b = b + 1
　　Set a = a.Offset(1, 0)
Loop
によって、aの位置は「F3」→「F4」というふうに9行下の「F11」まで移動します。
そして、その後の
Set a = a.Offset(-9, 1)
によって、aは9行上、1列右の「G2」に移動します。
この処理を
For i = 1 To 5

Next
という風に5回実行するのでaの動きは
F2→F3→…→F11→G1→G2→…→G11→H1→…→…→J11→K1
と最終的にはK1に移動した段階で処理は終了です。

補足

この処理を実行するとどの行も「１０」まできちんと数字が入ってますが、aの位置がなぜ９行下の「Ｆ１０」ではく、「Ｆ１１」なんですか？Ｓet a =a.offset(1,0)があるからですか？

回答No.5

Wendy02

こんにちは。

この際ですから、再度、Wendy02が書いておきます。

#3「基本的なことですが」で書いたのですが、もう少し修正が必要な気がしました。
VBAのテキストには載っていることなのですが、市販のVBAの本などには、最初は、直接、値を代入させて、セルとRangeオブジェクト(Cells)のつながりだけに着目させます。ですから、Excel VBAの記録マクロから本格的なプログラミングにするための、「最適化」技法というのは、後回しになってしまうことが多いようです。

例えば、
・オブジェクト変数を使う

　というものがあります。これは、Visual Basic のメソッドやプロパティは、インターフェイスを使っている関係で、呼び出しするために時間が掛かっています。また、変数に入れるときに、時間が掛かっています。

　だから、その都度呼び出し、変数に入れるというのは、無駄に時間を使ってしまっています。

一度、変数に入れて、それを起点とすれば、呼び出し回数は減ります。
With ステートメントは、明示的なオブジェクト変数を設定しなくても、参照回数を減らすことができるので、この点で、もう少し速くさせることが出来ます。

ただ、こういう問題は、何千・何万という呼び出し回数をしないと、人間の感知する時間ではありませんが、そういう癖をつけておかないと、使い分けというのは出来ないものなのです。

・変数の値は、プロパティの値を取得するよりも高速

　ループ内で、オブジェクト変数を取得するというのは、あまり関心しません。また、同じように、プロパティを直接セルから取得するというのも良くありません。オブジェクト変数に入れるということは、Rangeオブジェクトの一切を引き受けますっていうことです。これを、VBAでは、オーバーヘッドと呼んでいます。それでは無駄が多いからです。これは、セルを直接参照するということも同様です。

一旦、オブジェクト変数を以下のように取ったら、
　Set a = Range("f2")
　　a という変数を使って、処理することが一番なのです。

・変数型は、明示的に型を宣言し、なるべく、Variant型は避けなくてはなりません。抜け落ちを防ぐために、Option Explicit の宣言をしておくのがよいかと思います。

　なお、本来は、AutoFill メソッドは、ワークシート上のマクロで記録されたものであって、VBAでは明示的な値を代入させたほうがよいのです。

「最適化」の原則に則って、私自身のコードを「修正」しました。

Sub TestSample2()
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
Range("E1").Value = "氏名"
Range("E2").Value = WorksheetFunction.Transpose( _
　　　　　Array("甲", "乙", "丙", "丁", "戊", "己", "庚", "辛", "壬"))
Range("F1:J1").Value = Array("国", "数", "理", "社", "英")
With Range("F2")
For j = 0 To 4
　For i = 0 To 8
　　Randomize '乱数発生ルーチンを初期化
　 .Offset(i, j).Value = Int(100 * Rnd) + 1
　Next i
Next j
End With
Application.ScreenUpdating = True
End Sub

Randomize は、乱数発生を繰り返していると、ある同じ数字のパターンに意外に早くめぐり合います。回数にもありますが、数百回以内のはずです。本来の乱数の組合わせよりも、はるかに少ないです。そのために、乱数を発生するときには、乱数ジェネレータのルーチンを初期化する必要があります。
.

参考URL：

http://www.microsoft.com/japan/developer/library/VBCon98/vbconoptimizingobjects.htm

回答No.4

imogasi

質問に直接答えていませんので、すみません。
人それぞれ好みがありますので断定はできません。
またＯｆｆｓｅｔやＤｏＵｎｔｉｌの練習問題かもしれません。
しかし私に言わせればなんでこんなに難しくプログラムを組むのといいたい。
Sub PlusA001()
Dim a As Range
Dim b As Integer
'----
Range("e1").Value = "氏名"
Range("e2").Value = "甲"
Range("e2").AutoFill Destination:=Range("e2:e11"), Type:=xlFillDefault

Range("f1:j1").Value = Array("国", "数", "理", "社", "英")
Set a = Range("f2")
For b = 2 To 11 '第２行から１０人分
For i = 6 To 10 'F列から５科目分Ｊ列まで
Cells(b, i) = Int(100 * Rnd) + 1
Next i
Next b
End Sub
のようなのをお勧めします。
結果
氏名 国 数 理 社 英
甲 50 42 70 18 43
乙 55 82 55 43 51
丙 23 62 49 69 89
丁 38 31 30 16 53
戊 23 59 37 88 48
己 20 69 75 62 79
庚 17 81 21 96 7
辛 7 80 38 47 12
壬 12 18 5 72 54
癸 57 22 47 75 76

回答No.3

Wendy02

こんにちは。

基本的なことだと思うのですが、

ループ上の中で、
　Set a = a.Offset(1, 0)
必要以上には、しないことですね。設定の際に、新たなRangeオブジェクトを取り込む関係で、動きが遅くなります。起点から、Offsetで移動していくのが普通です。Offset は、起点を「０」としますから、ひとつ減ります。発想の転換をされたほうがよいですね。

ご質問で出されたコードに対しては、回答のしようがありません。

Sub TestSample1()
Dim a As Range
Dim b As Integer
Dim i As Integer
Range("E1").Value = "氏名"
Range("E2").Value = "甲"
Range("E2").AutoFill Destination:=Range("E2:E10"), Type:=xlFillDefault

Range("F1:J1").Value = Array("国", "数", "理", "社", "英")
For i = 0 To 4
With Range("F2").Offset(0, i)
　For b = 0 To 8
　 .Offset(b, 0).Value = Int(100 * Rnd) + 1
　Next b
End With
Next i
End Sub

お礼

回答してくれてありがとうございました。

回答No.2

noname#112806

F2の9行下はF11ですよ。
Do Until b = 9
　　a.Value = Int(100 * Rnd) + 1
　　b = b + 1
　　Set a = a.Offset(1, 0)
Loop
ここの部分を詳しく説明すると
Do～Loopまでの処理をb=9になるまで繰り返すということです。

a.Value = Int(100 * Rnd) + 1
1．最初のaの位置であるF2にランダムで数値が入力されます。

b = b + 1
2．bに1を足します（この時点でbの値は1）

Set a = a.Offset(1, 0)
3．aの位置を1行下に移動します。（F2の1行下はF3）

データを入力した後でbを増加させaの位置を移動させるので、8行下のF10にデータが入力されbが9になりaの位置が9行下のF11になったあと繰り返し処理が終わります。
つまり11行目にはデータが入力されることなく
Set a = a.Offset(-9, 1)
で2行目に戻されるということです。
ここで9行上に移動させると同時に1列右に移動させることによってG、H、I、Jの列にもデータが入力されるわけです。

説明が下手でもうしわけございませんが分かりましたでしょうか？

お礼

わかりやすい回答で、有難うございました。
今まで変数というものを名前を自由に変えられる。という程度にしか考えず、ソースの流れの中での重要性というものに気づかされました。これからは変数というものに着目しつつ勉強していきたいと思います。