VBA100本ノック 33本目：マクロ記録の改修

公開日：2020-11-25　最終更新日：2021-02-22

VBA100本ノック 33本目：マクロ記録の改修

マクロの記録から作成したVBAを使いやすいように改修して処理速度アップする問題です。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。

出題

#VBA100本ノック 33本目
「このVBAはマクロの記録から作ったのですが、件数の数値を変更してから実行しなければならず、データ件数も多くて何分も時間がかかりとても困っています。なんとかしてもらえないでしょうか?」
こう頼まれました。VBAを書いて対応してあげましょう。
※VBAソースはリプにて


  
    
      Sub Macro1()

　　Sheets("データ").Select

　　For i = 2 To 1001

　　Range("D" & i).Select

　　ActiveCell.FormulaR1C1 = _

　　　　"=IFERROR(VLOOKUP(RC[-2],マスタ!C[-3]:C[-1],2,FALSE),"""")"

　　Range("E" & i).Select

　　ActiveCell.FormulaR1C1 = _

　　　　"=IFERROR(VLOOKUP(RC[-3],マスタ!C[-4]:C[-2],3,FALSE),"""")"

　　Range("F" & i).Select

　　ActiveCell.FormulaR1C1 = _

　　　　"=RC[-1]*RC[-3]"

　　Range("D" & i & ":F" & i).Select

　　Selection.Copy

　　Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

　　　　:=False, Transpose:=False

　　Application.CutCopyMode = False

　　Next

　　Range("A1").Select

End Sub

処理方法は問いません。
要件を満たし結果が同じであればどのような方法でも構いません。

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_33.xlsm
https://excel-ubara.com/vba100sample/VBA100_33.zip

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

元々が微妙なコードではありますが、
多少なりともVBAが書ける人に渡すVBAとして書き直すという観点で書いていきます。
・Option Explicit
・インデント
・最終行の取得
・Applicationのプロパティ
ここまでは必須ですね。
最初のコードにある数千件程度ならこれだけでも十分な場合は多いでしょう。


  
    
      Sub VBA100_33_01()

　　Application.ScreenUpdating = False

　　Application.Calculation = xlCalculationManual

　　

　　Dim i As Long

　　With Sheets("データ")

　　　　.Select

　　　　For i = 2 To .Cells(.Rows.Count, 2).End(xlUp).Row

　　　　　　.Cells(i, 4).FormulaR1C1 = _

　　　　　　　　"=IFERROR(VLOOKUP(RC[-2],マスタ!C[-3]:C[-1],2,FALSE),"""")"

　　　　　　.Cells(i, 5).FormulaR1C1 = _

　　　　　　　　"=IFERROR(VLOOKUP(RC[-3],マスタ!C[-4]:C[-2],3,FALSE),"""")"

　　　　　　.Cells(i, 6).FormulaR1C1 = _

　　　　　　　　"=RC[-1]*RC[-3]"

　　　　　　.Range("D" & i & ":F" & i).Value = .Range("D" & i & ":F" & i).Value

　　　　Next

　　End With

　　

　　Application.ScreenUpdating = True

　　Application.Calculation = xlCalculationAutomatic

　　

　　Range("A1").Select

End Sub

さらに件数が増えることを考え、全行一括入力（Ctrl+Enter）を教えておきたい。
マクロの記録を見る限り、Ctrl+Enterを知らない可能性があります。
VBAと同時に、シート操作もしっかり伝えたいところです。
各種プロパティ等、伝えたいことは山盛りですね。


  
    
      Sub VBA100_33_02()

　　Dim cnt As Long

　　With Sheets("データ")

　　　　.Select

　　　　cnt = .Range("B1").CurrentRegion.Rows.Count - 1

　　　　.Range("D2").Resize(cnt).Value = "=IFERROR(VLOOKUP(B2,マスタ!A:C,2,FALSE),"""")"

　　　　.Range("E2").Resize(cnt).Value = "=IFERROR(VLOOKUP(B2,マスタ!A:C,3,FALSE),"""")"

　　　　.Range("F2").Resize(cnt).Value = "=C2*E2"

　　　　.Range("D2:F2").Resize(cnt).Value = .Range("D2:F2").Resize(cnt).Value

　　　　.Range("A1").Select

　　End With

End Sub

せっかく自分でマクロ記録からVBAを書いているので、なんとか次の段階に進んでほしい。
この依頼者には、まだ難しいかもしれません。
直ぐには書けるようにはならないと思いますが、まずは読んで理解してもらえることを願って。
今回は特に補足はありません。

補足

補足はありません。

サイト内関連ページ

第16回．繰り返し処理（For Next）

・For Next ステートメント・For Next 例文・For Next をステップイン実行で目で見て確認しましょう。・１行置きに処理する場合・Exit For ・For～Nextのネスト（入れ子）・最後に一言

第18回．最終行の取得（End,Rows.Count）

・エクセルVBAにおける最終行取得の必要性・.End(xlDown)：Ctrl+↓ ・.End(xlUp)：Ctrl+↑ ・Endプロパティの方向（↑↓←→）について・セルの行数を取得するRowプロパティ・Cells(Rows.Count, 1).End(xlUp).Rowを日本語に訳す・EndプロパティがRangeオブジェクトを返す・Endプロパティの問題点・最終行に関するサイト内のページ

第38回．セルに計算式を設定（Formula）

・計算式を設定できるプロパティ・Valueプロパティ・Formulaプロパティ , FormulaLocalプロパティ・FormulaR1C1プロパティ , FormulaR1C1Localプロパティ・R1C1参照形式・Localが付くプロパティについて・それぞれの違い（Localは除く）・何故、こんなに多くのプロパティが存在しているのか・R1C1形式を使うメリット・たった1行のVBAで複数のセルに計算式を入れる

第82回．RangeのResizeプロパティ

・Resizeプロパティの構文・Resizeの使用例・Resizeのまとめ

同じテーマ「VBA100本ノック」の記事

30本目：名札作成（段組み）
31本目：入力規則
 32本目：Excel終了とテキストファイル出力
33本目：マクロ記録の改修
34本目：配列の左右回転
 35本目：条件付き書式
 36本目：列の並べ替え
 37本目：グラフの色設定
 38本目：1シートを複数シートに振り分け
 39本目：数値リストの統合（マージ）
40本目：複数ブックの統合

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
33本目：マクロ記録の改修

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA練習問題
VBA100本ノック 33本目：マクロ記録の改修