レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

残業時間がいちばん多い従業員の情報を出力する。Max関数、Min関数より便利なマクロ

受講生さんの投稿

(投稿ID: 747)

小川先生
問題3をFor ～ Eachを使って書いてみました。
変数MaxはRange型の方が良かったのでしょうか？
また、このような問題の場合は、For ～ NextとCellsを使ってネストさせるより、For ～ Eachを使った方がいいのでしょうか？
その他、修正点がありましたら教えて下さい。

Sub mondai3_For_Each()
Dim rng As Range, Max As Single, r As Long, c As Long
For Each rng In Range("C6:H33")
If Max < rng.Value Then
Max = rng.Value
r = rng.Row
c = rng.Column
End If
Next rng
Cells(4, "K").Value = Cells(r, 2).Value
Cells(4, "L").Value = Cells(5, c).Value
Cells(4, "M").Value = Cells(r, c).Value
End Sub

2014/09/28 19:14

小川慶一さんのコメント

(コメントID: 1627)

匿名さん:

>変数MaxはRange型の方が良かったのでしょうか？

試してないですが、Range型にしてセルへの参照をそのまま利用したほうがよさそうですね。

>また、このような問題の場合は、For ～ NextとCellsを使ってネストさせるより、For ～ Eachを使った方がいいのでしょうか？

For Each構文のほうが僕の好みです
理由は、以下の２つ:

[1] 構造が少ない
[2] For Each構文はコレクション指定した段階でコレクションのメンバーすべてへの参照がメモリに読み込まれるので高速

もっとも、常に確実に For Each 構文を使うほうがよいかというと、そうでもありません。
理由は以下にて。
http://www.exvba.com/blog/?p=4378

2014/09/29 22:22

受講生さんのコメント

(コメントID: 1633)

小川先生:

Range型を使用し、書きなおしてみました。
Sub mondai3_For_Each()
Dim rng As Range, Max As Range
Set Max = Range("C6")
For Each rng In Range("C6:H33")
If Max.Value < rng.Value Then
Set Max = rng
End If
Next rng
Cells(4, "K").Value = Cells(Max.Row, 2).Value
Cells(4, "L").Value = Cells(5, Max.Column).Value
Cells(4, "M").Value = Max.Value
End Sub

数値型と違い、Range型はセルそのものを参照するため、行数や列数を管理する変数を
別に用意しなくてもいいのですね。
また、初期値がNothingであるため、暫定勝者の設定が必要。
オブジェクト型であるため、代入する時にはSetが必要(If文の箇所)。
という認識でよろしいでしょうか？

今までこのようなケースでは、For ～ Nextのネストしか思いつかなかったため、Cellsを使わない場合は
どうするのだろうと思っていたのですが、今回For ～ Eachの新たな使い方を発見できました。

2014/09/30 10:11

ゲストさんのコメント

(コメントID: 1637)

匿名さん:

>数値型と違い、Range型はセルそのものを参照するため、行数や列数を管理する変数を
>別に用意しなくてもいいのですね。
>また、初期値がNothingであるため、暫定勝者の設定が必要。
>オブジェクト型であるため、代入する時にはSetが必要(If文の箇所)。
>という認識でよろしいでしょうか？

OKです。

以下のコードもシンプルでとてもよいですね (^^

Sub mondai3_For_Each()
    Dim rng As Range, Max As Range
    Set Max = Range("C6")
    For Each rng In Range("C6:H33")
        If Max.Value < rng.Value Then
            Set Max = rng
        End If
    Next rng
    Cells(4, "K").Value = Cells(Max.Row, 2).Value
    Cells(4, "L").Value = Cells(5, Max.Column).Value
    Cells(4, "M").Value = Max.Value
End Sub

>今までこのようなケースでは、For ～ Nextのネストしか思いつかなかったため、Cellsを使わない場合は
>どうするのだろうと思っていたのですが、

あとは、offsetを使うことですね。

2014/09/30 19:39

受講生さんのコメント

(コメントID: 1641)

小川先生:

>あとは、offsetを使うことですね。
ということですので、Offsetを使った例を考えてみました。

Sub mondai3_offset()
Dim i As Long, j As Long, Max As Single, r As Long, c As Long
For i = 0 To 27
For j = 0 To 5
If Max < Range("C6").Offset(i, j) Then
Max = Range("C6").Offset(i, j)
r = i
c = j
End If
Next j
Next i
Range("K4").Value = Range("B" & r + 6).Value
Range("L4").Value = Range("C6").Offset(-1, c)
Range("M4").Value = Max
End Sub

今回は最後にセルへ代入する部分も、あえてCellsを使わないでやってみましたが、
この部分はCellsを使った方が断然楽だと思いました。
Cellsを使わずに横にループする方法もあることが分かってよかったです。

2014/10/01 05:22

小川慶一さんのコメント

(コメントID: 1644)

匿名さん:

よくできていると思います。

>この部分はCellsを使った方が断然楽だと思いました。

ここは好みですね。
でも、ほかを Range で書いているから、統一性という意味で言うと Range で書いたほうがよいかな、とも思います。

ひきつづきお楽しみください☆

2014/10/01 13:27