業務を効率化! ExcelVBA 実践コースレポート問題集解答 解説 第 1 単位 VBM-01-1 1406 問 2~ 問 5の解答は グレーのあみかけで示しています 本レポート問題集では テキストで学習したなかでも 特に 実務でよく使われる項目や 注意が必要なポイントについて出題しています 誤った設問については 解答を確認する前に 再度プロシージャを実行してみてください エラーが発生する場合は ステップ実行をするなどして まず自分でエラーの原因を探してみましょう そのうえで テキストの該当ページを参照し 学習内容についての理解を徹底させてください
< 問 1> 設問 ⑴ ( イ ) 解説 :( テキスト10ページ参照 ) マクロを含むブックの拡張子は.xlsm です 互換モード(.xls ) を使用することが多い場合は マクロを含むブックを保存するときに ファイル形式に注意しましょう 設問 ⑵ ( ア ) 解説 :( テキスト13ページ参照 ) Functionプロシージャは処理した結果を呼び出し元に返すプロシージャで 自作の関数を作成することもできます 設問 ⑶ ( ア ) 解説 :( テキスト14ページ参照 ) オブジェクトの階層構造を意識してコードを記述することで プログラムを作成する際のミスが減り 思わぬエラーを避けることができます 設問 ⑷ ( イ ) 解説 :( テキスト18 20ページ参照 ) データ型を指定しなかった場合 エラーが起きた原因が見つけにくくなります 変数を宣言するときには あらかじめデータ型を指定するようにしましょう 設問 ⑸ ( イ ) 解説 :( テキスト25 26ページ参照 ) 配列のインデックスは0から始まるため 要素数は 指定した値 + 1 になります
設問 ⑹ ( イ ) 解説 :( テキスト29 31ページ参照 ) 実際のプログラミングでミスしがちな点です 条件式を指定する場合には = の有無をきちんと確認しましょう 設問 ⑺ ( イ ) 解説 :( テキスト33ページ参照 ) Is 句や To 句を使用して 値の範囲を指定することができます また,( カンマ ) で区切って複数の条件を指定することもできます 設問 ⑻ ( ア ) 解説 :( テキスト36ページ参照 ) Step 句には負の値を指定することもできます 省略した場合は 1ずつ増加されます Step 句は単純な繰り返し処理では使用しないことも多いですが 処理を逆順に行いたい場合などに使用する大切なキーワードです 設問 ⑼ ( ア ) 解説 :( テキスト38ページ参照 ) For Each Nextステートメントは 繰り返し処理の対象がグループであることが重要なポイントです 設問 ⑽ ( ア ) 解説 :( テキスト42ページ参照 ) Do Loop ステートメントを使用する際には 特に 無限ループ の状態にならないよう注意が必要です 3
< 問 2> 設問 ⑴ Sub Q_2_1() Rangeオブジェクトを使用してセルA2とC6を選択する Range("A2,C6").Select 解説 :( テキスト46 47ページ参照 ) 離れたセルを指定する場合は : ではなく, を使用します なお テキストでは Range プロパティ と表現していますが Range オブジェクト も同義ととらえてかまいません より正確に表現すると Range プロパティを使用して取得できるセル ( オブジェクト ) が Range オブジェクトになります 設問 ⑵ Sub Q_2_2() Sheet1 ワークシートにある社員名簿のセルB1からC6を Sheet1 ワークシートのセルA10 以降にコピーして貼り付ける (Pasteメソッドは使用しない ) Range("B1:C6").Copy Range("A10") 解説 :( テキスト53 56ページ参照 ) 単発のコピー処理であれば Pasteメソッドを使用するよりも Copyメソッドを使用したほうが効率的です なお テキストでは触れていませんが 引数名を明示して つぎのように記述しても正解となります Range("B1:C6").Copy Destination:= Range("A10") 4
設問 ⑶ Sub Q_2_3() セルA1をコピーする Range("A1").Copy セルB1からC1に書式のみ貼り付ける Range("B1:C1").PasteSpecial xlpasteformats 解説 :( テキスト53 56ページ参照 ) コピーしたセルを 形式を選択して貼り付け するには PasteSpecialメソッドを使用します 書式のみ貼り付ける場合には xlpasteformats を引数に指定します 実務でもよく行う作業ですので 指定のしかたを確認しておきましょう 設問 ⑷ Sub Q_2_4() セルE1の値のみをコピーしてセルB8に貼り付ける (Copyメソッドは使用しない) Range("B8").Value = Range("E1").Value 解説 :( テキスト49 57ページ参照 ) 実務でも 数式を入力したセルの結果だけを貼り付けたいという場合がよくあります Valueプロパティを使用した値の貼り付けは記述が簡単で便利ですので 覚えておくとよいでしょう なお テキストでは触れていませんが Valueを省略しても同じように動作します ただし 動作する というだけで よい記述のしかたとはいえない ( また 現在では一般的ではない ) ため 不正解とします 5
設問 ⑸ Sub Q_2_5() 実線で格子の罫線をセルA1からC6の表全体に引く Range("A1:C6").Borders.LineStyle = xlcontinuous 二点鎖線の罫線を表の1 行め ( セルA1からC1) の下側に引く Range("A1:C1").Borders(xlEdgeBottom).LineStyle = xldashdotdot 解説 :( テキスト63 65ページ参照 ) Bordersプロパティで セルの個々の罫線を設定することができます 引数を省略した場合は 格子状に罫線が設定されます LineStyleプロパティで罫線の種類を指定します 本設問のように 表などに罫線を設定する場合は まず全体を指定し そのあと個別の設定を行うとよいでしょう < 問 3> 設問 ⑴ Sub Q_3_1() 削除後警告のメッセージを非表示にする Application.DisplayAlerts = False 3 月 ワークシートを削除する Worksheets("3 月 ").Delete 削除後警告のメッセージを表示する Application.DisplayAlerts = True 解説 :( テキスト72 74ページ参照 ) ワークシートを削除する場合 警告 のメッセージを非表示にするコードも記述することが大切です プロシージャの終了後もその設定は有効になるため ワークシートを削除したあとに 警告 のメッセージを表示する設定に戻す必要があります この一連の処理は まとめて覚えておきましょう 6
設問 ⑵ Sub Q_3_2() 4 月 ワークシートのコピーをブックの一番右端に作成する Worksheets("4 月 ").Copy After:=Worksheets(Worksheets.Count) コピーしたワークシートの名前を 6 月 にする Worksheets(Worksheets.Count).Name = "6 月 " After は 大文字 小文字を区別しません 解説 :( テキスト75 76ページ参照 ) ワークシートをコピーするときの引数の指定方法について確認しました Countプロパティで取得した値を Worksheetsプロパティのインデックス番号として指定できる点がポイントです また ワークシート名の設定方法についても あわせて確認しておきましょう 7
設問 ⑶ Sub Q_3_3() Dim FileName As String Dim Book As Workbook [ ファイルを開く ] ダイアログボックスを表示し 選択されたブックのパスを変数 FileNameに代入する ダイアログボックスに表示されるファイルの種類は Excelファイル ("Excelブック,*. xlsx") を指定する FileName = Application.GetOpenFilename("Excelブック,*.xlsx") 選択されたブックを開き 変数 Bookに代入する Set Book = Workbooks.Open(FileName) MsgBox Book.Worksheets(1).Range("A1").Value Book.Close 解説 :( テキスト80 83ページ参照 ) ファイルを開く ダイアログボックスを表示する処理は 業務用のプログラムを作成する際にも頻出するため 覚えておくとよいでしょう また ブックを開くと 開いたブックがアクティブになります そのため アクティブブックに対する処理を行うコードを記述してしまいがちですが これはエラーの原因になります ブックを開く処理を行う際には 開くと同時に変数に代入することが大切です 8
設問 ⑷ Sub Q_3_4() Dim Book As Workbook 新規ブックを追加して 変数 Bookに代入する Set Book = Workbooks.Add 追加したブックを 保存データ.xlsx という名前で 1-3.xlsm ブックと同じフォルダーに保存する なお 1-3.xlsm ブックが保存されているフォルダーは Pathプロパティを使用して取得すること Book.SaveAs ThisWorkbook.Path & " " & " 保存データ.xlsx" 解説 :( テキスト84 86ページ参照 ) ブックを追加したときに 変数に代入しています こうすることで そのあとの処理を確実に行うことができます Addメソッドだけでなく この一連の流れを確認しておきましょう また 名前を付けてブックを保存する処理も プログラム作成時に大切な処理です 覚えておきましょう なお & " " & " 保存データ.xlsx" の部分は & " 保存データ.xlsx" と記述しても正解となります また テキストでは触れていませんが & " " & の部分をつぎのように記述することもできます & Application.PathSeparator & PathSeparatorプロパティは 区切り文字( ) を表します 9
< 問 4> 設問 ⑴ Sub Q_4_1() Dim num As Long セルA2からA6に入力されているデータの個数をCOUNTAワークシート関数を用いて取得し 変数 numに代入する なお 引数にはRangeオブジェクトを使用し : を使った形式でセル範囲を指定すること num = Application.WorksheetFunction.CountA(Range("A2:A6")) MsgBox " 人数 :" & num 解説 :( テキスト96 97ページ参照 ) ワークシート関数をVBAで利用するには WorksheetFunctionオブジェクトのメソッドを使用します VBAでワークシート関数を利用できると Excelのワークシートを操作する感覚で関数を使うことができるため とても便利です 10
設問 ⑵ Sub Q_4_2() Dim temp As String Dim pos As Long Dim vname As String Dim i As Long For i = 2 To 6 temp = Cells(i, 2).Value 半角スペースの位置をInStrRev 関数を使用して取得し 変数 posに代入する pos = InStrRev(temp, " ") 氏名 から 名 のみを取得して変数 vnameに代入する vname = Right(temp, Len(temp) - pos) Debug.Print vname Next 解説 :( テキスト100 103ページ参照 ) テキストで紹介している ブックのフルパスからファイル名のみを取得するプロシージャの応用です たとえば 住所から県名のみを取り出すなど ある文字を区切り文字としてその前後のいずれかの文字を取り出すという処理は 実務でもよく登場します コードの記述方法をきちんと理解しておきましょう なお InStrRev 関数を使用する指示が特になければ InStr 関数を使用して つぎのように記述することもできます pos = InStr(temp, " ") 11
設問 ⑶ Sub Q_4_3() Dim Target As Range Dim temp As String Dim i As Long For Each Target In Range("C2:C6") セルの値をすべて全角に置換して変数 tempに代入する 変換する文字列は Valueプロパティを使って取得する temp = StrConv(Target.Value, vbwide) 置換後の値の全角スペースを半角スペースに変換し 元のセルに入力する Target.Value = Replace(temp, " ", " ") Next 解説 :( テキスト104 108ページ参照 ) 文字列の変換と置換を1つのプロシージャ内で行っています 文字列を変換するには StrConv 関数を 置換するにはReplace 関数を使用します 文字列操作は このように複数の処理を組み合わせて行うことがよくあります 12
設問 ⑷ Sub Q_4_4() Dim temp As Date Dim vstr As String 本日の日付から30 日後の日付を求め 変数 tempに代入する temp = DateAdd("d", 30, Date) 変数 tempの値の書式を yyyy/mm/dd にして変数 vstrに代入する vstr = Format(temp, "yyyy/mm/dd") MsgBox "30 日後 :" & vstr 解説 :( テキスト112 116ページ参照 ) 日付の処理は 業務でも必須のものです そのため DateAdd 関数について テキストとは少し異なる応用問題で確認しました また ここで行っているように 日付のフォーマットを整えることは 書類を作成するうえで大切なことです Format 関数を使った表示形式の設定もできるようにしておきましょう < 問 5> 設問 ⑴ Private Sub Workbook_Open() With Worksheets("Sheet1").Activate A 列の入力行を取得する.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Select End With 13
解説 :( テキスト132 135ページ参照 ) [ プロジェクトエクスプローラ ] で ThisWorkbook ブックモジュールをクリックして [ コードウィンドウ ] を開き Openイベント プロシージャにコードを記述します Endプロパティを使用して表の最終行を取得する処理 Offsetプロパティを使用して最終行の1つ下のセルを取得する処理は プログラムで表を扱う際に必須の処理です ここでは Withステートメントを使用して記述を省略していることに注意しましょう なお テキストでは触れていませんが つぎのように記述しても正解となります.Range("A" &.Rows.Count).End(xlUp).Offset(1).Select.Cells(.Rows.Count,"A").End(xlUp).Offset(1).Select 設問 ⑵ Private Sub Worksheet_Change(ByVal Target As Range) With Range("A1").CurrentRegion If Not Intersect(Target,.Columns("A")) Is Nothing Then If Len(Target.Value) <> 5 Then MsgBox " 会員番号は5 桁の値です " イベント処理が発生しないようにする Application.EnableEvents = False 入力されたセルの値のみ削除する ( 書式は削除しない ) Target.ClearContents イベント処理が発生するようにする Application.EnableEvents = True End If End If End With 解説 :( テキスト136 141ページ参照 ) [ プロジェクトエクスプローラ ] で Sheet1 シートモジュールをクリックして[ コードウィンドウ ] を開き Changeイベント プロシージャにコードを記述します 14
セルの内容だけを削除したい場合は ClearContentsメソッドを使用します また Changeイベント内でセルに対する処理を行うときには イベントが発生しないようにすることが大切です この処理を行わなくてもプログラムが動作することはありますが その場合 必ずムダな処理が発生します イベントを有効に戻しておくことも忘れないようにしましょう 設問 ⑶ Sub Q_5_3() Dim sh As Worksheet エラーが発生したときに ラベル (ErrHdl) にジャンプして処理を行う On Error GoTo ErrHdl Set sh = Worksheets(" 名簿 ") sh.activate MsgBox " 処理を終了します " プロシージャを抜けて処理を終了する Exit Sub ErrHdl: MsgBox " エラーが発生しました " 解説 :( テキスト159 166ページ参照 ) エラー処理の基本的な記述方法を確認しました このプロシージャは 名簿 という名前のワークシートがあれば アクティブにして 処理を終了します というメッセージを表示し ない場合には ラベルまでジャンプして処理を行います 特に Exit Sub を記述して処理を終了することが大切です この記述を忘れてしまうと エラーが発生しなかった場合でも ラベル以降の処理が行われてしまいます 15
16