Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


7072 / 13644 ツリー ←次へ | 前へ→

【41276】動作速度について てっちゃん 06/8/3(木) 16:42 質問[未読]
【41279】Re:動作速度について neptune 06/8/3(木) 17:09 発言[未読]
【41280】Re:動作速度について Jaka 06/8/3(木) 17:15 発言[未読]
【41281】Re:動作速度について てっちゃん 06/8/3(木) 17:30 お礼[未読]
【41283】Re:動作速度について neptune 06/8/3(木) 19:17 発言[未読]
【41286】Re:動作速度について Hirofumi 06/8/3(木) 20:17 回答[未読]
【41288】Re:動作速度について Hirofumi 06/8/3(木) 21:15 回答[未読]
【41319】Re:動作速度について Hirofumi 06/8/4(金) 19:24 回答[未読]
【41328】Re:動作速度について てっちゃん 06/8/6(日) 8:57 発言[未読]
【41341】Re:動作速度について Hirofumi 06/8/6(日) 23:02 回答[未読]
【41377】Re:動作速度について てっちゃん 06/8/8(火) 7:59 お礼[未読]
【41298】Re:動作速度について Jaka 06/8/4(金) 9:15 発言[未読]
【41327】Re:動作速度について てっちゃん 06/8/6(日) 8:52 お礼[未読]

【41276】動作速度について
質問  てっちゃん  - 06/8/3(木) 16:42 -

引用なし
パスワード
   先ほど質問させて頂いた者ですが、下記のコードを作ってみました。
一応、希望通りの動作はするのですが、Cells(co1 + 2, 17) = Me.Controls("label" & co1).Captionを各月のif文の場所へ追加した結果、動作の速度がかなり遅くなりました。これって仕方ない事なのでしょうか?
まだまだVBAを使い始めて日が浅いもので、コードの書き方等おかしいところも多々あると思いますので、どうぞご指導をお願い致します。

For co1 = 1 To 40
For i = 1 To 400
 
 Select Case i
 Case 1 To 10: co = 3: cou = 17
 Case 11 To 20: co = 4: cou = 7
 Case 21 To 30: co = 5: cou = -3
 Case 31 To 40: co = 6: cou = -13
 Case 41 To 50: co = 7: cou = -23
 Case 51 To 60: co = 8: cou = -33
 Case 61 To 70: co = 9: cou = -43
 Case 71 To 80: co = 10: cou = -53
 Case 81 To 90: co = 11: cou = -63
 Case 91 To 100: co = 12: cou = -73
 Case 101 To 110: co = 13: cou = -83
 Case 111 To 120: co = 14: cou = -93
 Case 121 To 130: co = 15: cou = -103
 Case 131 To 140: co = 16: cou = -113
 Case 141 To 150: co = 17: cou = -123
 Case 151 To 160: co = 18: cou = -133
 Case 161 To 170: co = 19: cou = -143
 Case 171 To 180: co = 20: cou = -153
 Case 181 To 190: co = 21: cou = -163
 Case 191 To 200: co = 22: cou = -173
 Case 201 To 210: co = 23: cou = -183
 Case 211 To 220: co = 24: cou = -193
 Case 221 To 230: co = 25: cou = -203
 Case 231 To 240: co = 26: cou = -213
 Case 241 To 250: co = 27: cou = -223
 Case 251 To 260: co = 28: cou = -233
 Case 261 To 270: co = 29: cou = -243
 Case 271 To 280: co = 30: cou = -253
 Case 281 To 290: co = 31: cou = -263
 Case 291 To 300: co = 32: cou = -273
 Case 301 To 310: co = 33: cou = -283
 Case 311 To 320: co = 34: cou = -293
 Case 321 To 330: co = 35: cou = -303
 Case 331 To 340: co = 36: cou = -313
 Case 341 To 350: co = 37: cou = -323
 Case 351 To 360: co = 38: cou = -333
 Case 361 To 370: co = 39: cou = -343
 Case 371 To 380: co = 40: cou = -353
 Case 381 To 390: co = 41: cou = -363
 Case 391 To 400: co = 42: cou = -373
End Select


 Worksheets(Mys).Select

If Me.Controls("combobox2").Value = "4月" Then
  Cells(co, i + cou) = Me.Controls("CheckBox" & i).Value
  If Me.Controls("CheckBox" & i).Value Then
     Cells(co, i + cou).Value = "○"
   Else
     Cells(co, i + cou).Value = "×"
   End If
Cells(co1 + 2, 17) = Me.Controls("label" & co1).Caption
 
ElseIf Me.Controls("combobox2").Value = "5月" Then
 Cells(co, i + cou + 12) = Me.Controls("CheckBox" & i).Value
  If Me.Controls("CheckBox" & i).Value Then
     Cells(co, i + cou + 12).Value = "○"
   Else
     Cells(co, i + cou + 12).Value = "×"
   End If
Cells(co1 + 2, 29) = Me.Controls("label" & co1).Caption

ElseIf Me.Controls("combobox2").Value = "6月" Then
  Cells(co, i + cou + 24) = Me.Controls("CheckBox" & i).Value
  If Me.Controls("CheckBox" & i).Value Then
     Cells(co, i + cou + 24).Value = "○"
   Else
     Cells(co, i + cou + 24).Value = "×"
   End If
  Cells(co1 + 2, 41) = Me.Controls("label" & co1).Caption

 
 End If

Next i
Next co1

【41279】Re:動作速度について
発言  neptune  - 06/8/3(木) 17:09 -

引用なし
パスワード
   Res付きませんね。回答ではありませんが、ちょっとだけ。

多分コードを読み解いて、ロジックを理解して、バグを探すのが回答者の方は
面倒なんです。・・・という私がそうです。^ ^;

思い通りに動かないと言う事は、自分の考えと、コードが違うということですね。
ですから、そういう時は、プログラムの流れを考えながら、コードにコメント
を付けましょう。そうすると間違いに気がつくかもしれません。
私はわからなくなれば、流れを紙に書いて考えます。

今後質問されるときに気をつけた方がいいと思うこと。

第三者に見てもらう時には付けた方が、親切というものです。
それから、インデントもキチンと付けないと第三者はそこから自分で
訂正しなければいけません。

【41280】Re:動作速度について
発言  Jaka  - 06/8/3(木) 17:15 -

引用なし
パスワード
   >一応、希望通りの動作はするのですが、Cells(co1 + 2, 17) = Me.Controls("label" & co1).Captionを各月のif文の場所へ追加した結果、動作の速度がかなり遅くなりました。これって仕方ない事なのでしょうか?
はい。仕方が無いと思います。
エクセルVBAは、セルに書きこう処理がとても遅いです。
それを1個1個何回も書き込むわけですから....。
まとまった範囲なら配列に入れておいて後で、1活で書き込むといった方あるけど、この場合は無理っぽいです。
Valueプロパティーは、付けるなら付ける習慣を身に付けた方がいいと思います。

なんなら、
Application.ScreenUpdating = False
で、画面描写更新を止めておくと言った方法もありかも。
Trueに戻すのをお忘れなく。
使い方をよく把握しないで頻繁に使いすぎると、メッセージボックスの残像が残るとかの副作用もあります。

【41281】Re:動作速度について
お礼  てっちゃん  - 06/8/3(木) 17:30 -

引用なし
パスワード
   ▼Jaka さん:
>>一応、希望通りの動作はするのですが、Cells(co1 + 2, 17) = Me.Controls("label" & co1).Captionを各月のif文の場所へ追加した結果、動作の速度がかなり遅くなりました。これって仕方ない事なのでしょうか?
>はい。仕方が無いと思います。
>エクセルVBAは、セルに書きこう処理がとても遅いです。
>それを1個1個何回も書き込むわけですから....。
>まとまった範囲なら配列に入れておいて後で、1活で書き込むといった方あるけど、この場合は無理っぽいです。
>Valueプロパティーは、付けるなら付ける習慣を身に付けた方がいいと思います。
>
>なんなら、
>Application.ScreenUpdating = False
>で、画面描写更新を止めておくと言った方法もありかも。
>Trueに戻すのをお忘れなく。
>使い方をよく把握しないで頻繁に使いすぎると、メッセージボックスの残像が残るとかの副作用もあります。


ありがとうございました。
application.screenupdating=false true 書き込んでいます。
時間がかかる間はメッセージboxに登録中と表示させてがまんします。

【41283】Re:動作速度について
発言  neptune  - 06/8/3(木) 19:17 -

引用なし
パスワード
   どうも失礼しました。なぜか質問を間違えて読んでいたようです。
???

完全に筋違いでした。気をつけます。

【41286】Re:動作速度について
回答  Hirofumi  - 06/8/3(木) 20:17 -

引用なし
パスワード
   善く見ていないのでハッキリ解りませんが?
此れて、同じセルに40回づつ同じ代入を繰り返している様な気がしますが?
また、

  Cells(co, i + cou) = Me.Controls("CheckBox" & i).Value
  If Me.Controls("CheckBox" & i).Value Then
    Cells(co, i + cou).Value = "○"
  Else
    Cells(co, i + cou).Value = "×"
  End If

これも、Cells(co, i + cou)に一度、Me.Controls("CheckBox" & i).Valueを入れ(True、False)
直ぐに、"○"か"×"を入れ直している?

因って、同じセルに都合、79回無駄な代入を繰り返していると思いますよ?
だとしたら、遅くなって当然では無いのですか?

【41288】Re:動作速度について
回答  Hirofumi  - 06/8/3(木) 21:15 -

引用なし
パスワード
   もう1ヶ所

  Cells(co1 + 2, 17) = Me.Controls("label" & co1).Caption

此れに至っては、同じ値を400回代入している様ですね?

Testして居ないので、上手くいくか?ですが
以下で同じ動きをすると思います?


  Dim i As Long
  Dim lngRow As Long
  Dim lngColumn As Long
  Dim vntResult1(1 To 40, 1 To 10) As Variant
  Dim vntResult2(1 To 40, 1 To 1) As Variant
  
  For i = 1 To 400
    lngRow = (i - 1) \ 10 + 1
    lngColumn = (i - 1) Mod 10 + 1
    If Me.Controls("CheckBox" & i).Value Then
      vntResult1(lngRow, lngColumn) = "○"
    Else
      vntResult1(lngRow, lngColumn) = "×"
    End If
  Next i
  
  For i = 1 To 40
    vntResult2(i, 1) _
        = Me.Controls("Label" & i).Caption
  Next i
  
  With Worksheets(Mys)
    Select Case Me.Controls("combobox2").Value
      Case "4月"
        lngColumn = 0
      Case "5月"
        lngColumn = 12
      Case "6月"
        lngColumn = 24
    End Select
    .Cells(3, 1 + lngColumn) _
        .Resize(UBound(vntResult1, 1), _
            UBound(vntResult1, 2)).Value = vntResult1
    .Cells(3, 17 + lngColumn) _
        .Resize(UBound(vntResult2, 1)).Value = vntResult2
  End With

【41298】Re:動作速度について
発言  Jaka  - 06/8/4(金) 9:15 -

引用なし
パスワード
   Select Caseのところこれですみますね。

For i = 1 To 400
  Cells(i, 1).Value = 7 - (Int((i - 11) / 10)) * 10
  Cells(i, 2).Value = Int((i - 11) / 10) + 4
  '↑確認用、後で消しておいてください。
  Cou = 7 - (Int((i - 11) / 10)) * 10
  Co = Int((i - 11) / 10) + 4
Next

【41319】Re:動作速度について
回答  Hirofumi  - 06/8/4(金) 19:24 -

引用なし
パスワード
   あ、間違った
以下部分を修正して下さい、元のままだとA3からデータを転記してしまいます

  With Worksheets(Mys)
    Select Case Me.Controls("combobox2").Value
      Case "4月"
        lngColumn = 0
      Case "5月"
        lngColumn = 12
      Case "6月"
        lngColumn = 24
    End Select
'    .Cells(3, 1 + lngColumn) _
        .Resize(UBound(vntResult1, 1), _
            UBound(vntResult1, 2)).Value = vntResult1 '★間違い
    .Cells(3, 18 + lngColumn) _
        .Resize(UBound(vntResult1, 1), _
            UBound(vntResult1, 2)).Value = vntResult1 '★変更
    .Cells(3, 17 + lngColumn) _
        .Resize(UBound(vntResult2, 1)).Value = vntResult2
  End With

【41327】Re:動作速度について
お礼  てっちゃん  - 06/8/6(日) 8:52 -

引用なし
パスワード
   おはようございます。
お礼が送れてすみませんでした。
本当に皆さんありがとうございます。
ばっちりです。まだまだ自分は勉強不足だなぁとつくづく感じました。

【41328】Re:動作速度について
発言  てっちゃん  - 06/8/6(日) 8:57 -

引用なし
パスワード
   ▼Hirofumi さん:
>あ、間違った
>以下部分を修正して下さい、元のままだとA3からデータを転記してしまいます
>
>  With Worksheets(Mys)
>    Select Case Me.Controls("combobox2").Value
>      Case "4月"
>        lngColumn = 0
>      Case "5月"
>        lngColumn = 12
>      Case "6月"
>        lngColumn = 24
>    End Select
>'    .Cells(3, 1 + lngColumn) _
>        .Resize(UBound(vntResult1, 1), _
>            UBound(vntResult1, 2)).Value = vntResult1 '★間違い
>    .Cells(3, 18 + lngColumn) _
>        .Resize(UBound(vntResult1, 1), _
>            UBound(vntResult1, 2)).Value = vntResult1 '★変更
>    .Cells(3, 17 + lngColumn) _
>        .Resize(UBound(vntResult2, 1)).Value = vntResult2
>  End With


ありがとうございました。
動作はばっちりいきました。
自分にはまったくここまでの知識がない為思いつきませんでした。
出来れば説明を付け加えていただければ、勉強になりますのでお願いしたいのですが、わがまま言ってすみません。

【41341】Re:動作速度について
回答  Hirofumi  - 06/8/6(日) 23:02 -

引用なし
パスワード
   まず、元々のコードの何処が悪いのかを説明して置きます
これは、関連の無いループ(「For co1 = 1 To 40」、「For i = 1 To 400」)をネストした事によります
此れを分けるだけで、440回の代入だけに成りますので、相当速く成ります


Public Sub Sample3()

  Dim i As Long
  Dim co1 As Long
  Dim co As Long
  Dim cou As Long
  
  Worksheets(Mys).Select
  
  For i = 1 To 400
    Select Case i
      Case 1 To 10: co = 3: cou = 17
      Case 11 To 20: co = 4: cou = 7
      Case 21 To 30: co = 5: cou = -3
      Case 31 To 40: co = 6: cou = -13
      Case 41 To 50: co = 7: cou = -23
      Case 51 To 60: co = 8: cou = -33
      Case 61 To 70: co = 9: cou = -43
      Case 71 To 80: co = 10: cou = -53
      Case 81 To 90: co = 11: cou = -63
      Case 91 To 100: co = 12: cou = -73
      Case 101 To 110: co = 13: cou = -83
      Case 111 To 120: co = 14: cou = -93
      Case 121 To 130: co = 15: cou = -103
      Case 131 To 140: co = 16: cou = -113
      Case 141 To 150: co = 17: cou = -123
      Case 151 To 160: co = 18: cou = -133
      Case 161 To 170: co = 19: cou = -143
      Case 171 To 180: co = 20: cou = -153
      Case 181 To 190: co = 21: cou = -163
      Case 191 To 200: co = 22: cou = -173
      Case 201 To 210: co = 23: cou = -183
      Case 211 To 220: co = 24: cou = -193
      Case 221 To 230: co = 25: cou = -203
      Case 231 To 240: co = 26: cou = -213
      Case 241 To 250: co = 27: cou = -223
      Case 251 To 260: co = 28: cou = -233
      Case 261 To 270: co = 29: cou = -243
      Case 271 To 280: co = 30: cou = -253
      Case 281 To 290: co = 31: cou = -263
      Case 291 To 300: co = 32: cou = -273
      Case 301 To 310: co = 33: cou = -283
      Case 311 To 320: co = 34: cou = -293
      Case 321 To 330: co = 35: cou = -303
      Case 331 To 340: co = 36: cou = -313
      Case 341 To 350: co = 37: cou = -323
      Case 351 To 360: co = 38: cou = -333
      Case 361 To 370: co = 39: cou = -343
      Case 371 To 380: co = 40: cou = -353
      Case 381 To 390: co = 41: cou = -363
      Case 391 To 400: co = 42: cou = -373
    End Select
    If Me.Controls("combobox2").Value = "4月" Then
      If Me.Controls("CheckBox" & i).Value Then
        Cells(co, i + cou).Value = "○"
      Else
        Cells(co, i + cou).Value = "×"
      End If
    ElseIf Me.Controls("combobox2").Value = "5月" Then
      If Me.Controls("CheckBox" & i).Value Then
        Cells(co, i + cou + 12).Value = "○"
      Else
        Cells(co, i + cou + 12).Value = "×"
      End If
    ElseIf Me.Controls("combobox2").Value = "6月" Then
      If Me.Controls("CheckBox" & i).Value Then
        Cells(co, i + cou + 24).Value = "○"
      Else
        Cells(co, i + cou + 24).Value = "×"
      End If
    End If
  Next i
    
  For co1 = 1 To 40
    If Me.Controls("combobox2").Value = "4月" Then
      Cells(co1 + 2, 17) = Me.Controls("label" & co1).Caption
    ElseIf Me.Controls("combobox2").Value = "5月" Then
      Cells(co1 + 2, 29) = Me.Controls("label" & co1).Caption
    ElseIf Me.Controls("combobox2").Value = "6月" Then
      Cells(co1 + 2, 41) = Me.Controls("label" & co1).Caption
    End If
  Next co1

End Sub

次に、「Select Case i」の部分が煩雑ので此れを整理します
其処で、1〜400まで1列に並んだ「CheckBox」を如何やって方形に並び替えるかを考えます
昔から、商を求める演算子「¥」と剰余を求める演算子「Mod」を使って、
1元の配列を2元に並べる定石の様な物が存在します
今回の場合、1〜400の番号を、10日毎のグループ分けを行う訳なのですが?
話を簡単にする為、0〜399の番号として考えます

iの値が0〜399まで変かする時
i = 0の時、0 \ 10 = 0、0 Mod 10 =0
i = 1の時、1 \ 10 = 0、1 Mod 10 =1
i = 2の時、2 \ 10 = 0、2 Mod 10 =2
  ・
  ・
i = 398の時、398 \ 10 = 39、398 Mod 10 =8
i = 399の時、399 \ 10 = 39、398 Mod 10 =9

と成り、「¥」はグループ番号(この場合「行」)、
「Mod」はグループ内の順位(この場合、「列」)を表します
また、Cellに対して1つづつ代入を行うと非常に遅く成るので、
配列を使って範囲に値を一遍に代入します

Public Sub Sample()

  Dim i As Long
  Dim lngRow As Long
  Dim lngColumn As Long
  Dim vntResult1(1 To 40, 1 To 10) As Variant
  Dim vntResult2(1 To 40, 1 To 1) As Variant
  
  'CheckBoxの1〜400まで繰り返し
  For i = 1 To 400
    '配列の行位置を取得
    lngRow = (i - 1) \ 10 + 1
    '配列の列位置を取得
    lngColumn = (i - 1) Mod 10 + 1
    'CheckBoxのi番がTrueの場合
    If Me.Controls("CheckBox" & i).Value Then
      '配列のlngRow行、lngColumn列に"○"を代入
      vntResult1(lngRow, lngColumn) = "○"
    Else
      vntResult1(lngRow, lngColumn) = "×"
    End If
  Next i
  
  For i = 1 To 40
    'Labelのi番の値を配列のi行目に代入
    vntResult2(i, 1) _
        = Me.Controls("Label" & i).Caption
  Next i
  
  With Worksheets(Mys)
    'ComboBox2の値が
    Select Case Me.Controls("combobox2").Value
      Case "4月"
        '基準位置からの列Offsetを0とする
        lngColumn = 0
      Case "5月"
        lngColumn = 12
      Case "6月"
        lngColumn = 24
    End Select
    '基準位置から、lngColumn分右のセル位置を先頭として40×10の範囲に結果配列の値を代入
    .Cells(3, 18 + lngColumn) _
        .Resize(UBound(vntResult1, 1), _
            UBound(vntResult1, 2)).Value = vntResult1
    '基準位置から、lngColumn分右のセル位置を先頭として40×1の範囲に結果配列の値を代入
    .Cells(3, 17 + lngColumn) _
        .Resize(UBound(vntResult2, 1)).Value = vntResult2
  End With
  
End Sub

【41377】Re:動作速度について
お礼  てっちゃん  - 06/8/8(火) 7:59 -

引用なし
パスワード
   おはようございます。
本当に細かな説明ありがとうございました。
おかげさまでよく理解できました。

7072 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free