まりふのひと

午後/iPSマクロ研の予定…マクロ残高証明の作成

MAPS-MK VBA


■　マクロ残高証明
前回行った手順をマクロ化してみます。

§１．データを揃えるため、サンプルデータをダウンロードする
  1. ［麻里府発］の まりふのひとの「公開」SkyDrive に入り、
  2. フォルダー「麻里府パソコン同好会」をクリックし、（開く）
  3. フォルダ「iPSマクロ研」をクリックする。 （開く）
  4. 「2013-1213_出納帳サンプルデータ」（圧縮（zip形式）フォルダ）にチェックを入れ、
  5. ［ダウンロード］をクリックする。
  6. ［ファイルを開く］。
  7. 「2013-1213_出納帳サンプルデータ.xlsm」を右クリック ⇒［コピー］する。
  8. 保存先フォルダーを開き‥‥ 貼り付ける。


このサンプルデータには、3っのシートがある。

  • ［出納帳］シート
  • ［名前］シート
    • 「前月繰越金」は、入っている前提。
  • ［作業用］シート

更に、マクロ名「選択したセル内の集計と云う文字を取り去る 」が入っている。（前回作成）


§２．出納帳を集計する

手順を確認しながら進めます。（バグがあるかも...）

§2.1. マクロ名「集計シートのクリア」

［名前］シートの集計場所 Ｅ〜Ｇ列をクリアします。

１）操作
  1. ［名前］シートを選択する。
  2. セル E2 をクリックし、
  3. Shift＋Ctrl＋End を押下する。
  4. ［ホーム］タブ/編集Gr.の［クリア］⇒［数式と値のクリア］［すべてクリア］をクリックする。
  5. セル A1 をクリックし、選択を解除しておく。
２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 集計シートのクリア()
  2. Sheets("名前").Select
  3. Range("E2").Select
  4. Range(Selection, ActiveCell.SpecialCells(xlLastCell).Select
  5. Selection.Clear
  6. Range("A1").Select
  7. End Sub
３）補完後マクロ
  • 書式は残してクリアする。
  1. Sub 集計シートのクリア()
  2. 　
  3. Sheets("名前").Select
  4. Range("E2").Select
  5. Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  6. Selection.ClearContents
  7. Range("A1").Select
  8. 　
  9. End Sub

§2.2. マクロ名「作業用シートのクリア」
１）操作
  1. ［作業用］シートを選択する。
  2. セル A1 を選択し、
  3. Shift＋Ctrl＋End を押下する。
  4. ［ホーム］タブ/編集Gr.の［クリア］⇒［すべてクリア］をクリックする。
  5. セル A1 をクリックし、選択を解除しておく。
２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 作業用シートのクリア()
  2. Sheets("作業用").Select
  3. Range("A1").Select
  4. Range(Selection, ActiveCell.SpecialCells(xlLastCell).Select
  5. Selection.Clear
  6. Range("A1").Select
  7. End Sub
３）補完後マクロ
  • ［作業用］シートは無ければ作る。あればクリアする。
  1. Sub 作業用シートのクリア()
  2. Dim wWorksheet As Worksheet
  3. Dim bFlag As Boolean
  4. 　
  5. bFlag = False
  6. For Each wWorksheet In Worksheets
  7. If wWorksheet.Name = "作業用" Then bFlag = True
  8. Next wWorksheet
  9. 　
  10. If bFlag = True Then
  11. Sheets("作業用").Select
  12. Range("A1").Select
  13. Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  14. Selection.Clear
  15. Else
  16. With Worksheets.Add(after:=Worksheets(Worksheets.Count))
  17. .Name = "作業用"
  18. End With
  19. End If
  20. 　
  21. Range("A1").Select
  22. 　
  23. End Sub

§2.3. マクロ名「出納帳を名前で並べ替える」

今回の目的からすれば「名前」のみの並び替えでよいが、先々のことを考慮し「名前／月日」を昇順に並べる。

１）操作
  1. ［出納帳］シートを選択する。
  2. Ctrl＋End ‥‥ これにより、表の右下隅のセルがアクティブになる。
    • これは、マクロの修整に必要な操作で、並べ替えには不要。
  3. セル A1 を選択し、
  4. ［並べ替え］をクリックする。
  5. 【並べ替え】ダイアログボックスで
    • 名前／月日を昇順とする。
    • R先頭行をデータの見出しとして使用する。
    • 8ふりがなを使わない。
  6. ［OK］で並べ替える。
２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 出納帳を名前で並べ替える()
  2. Sheets("出納帳").Select
  3. ActiveCell.SpecialCells(xlLastCell).Select
  4. Range("A1").Select
  5. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Clear
  6. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Add Key:=Range("C2:C113"), _
  7. SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  8. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Add Key:=Range("B2:B113"), _
  9. SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  10. With ActiveWorkbook.Worksheets("出納帳").Sort
  11. .SetRange Range("A1:G113")
  12. .Header = xlYes
  13. .MatchCase = False
  14. .Orientation = xlTopToBottom
  15. .SortMethod = xlStroke
  16. .Apply
  17. End With
  18. End Sub

３）補完後マクロ
  • “113”という行番号が出てくるが、これはデータ量で変わるため固定できない。
  1. Sub 出納帳を名前で並べ替える()
  2. Dim LastRow As Long
  3. 　
  4. Sheets("出納帳").Select
  5. ActiveCell.SpecialCells(xlLastCell).Select
  6. LastRow = ActiveCell.Row
  7. Range("A1").Select
  8. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Clear
  9. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Add Key:=Range("C2:C" & LastRow), _
  10. SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  11. ActiveWorkbook.Worksheets("出納帳").Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
  12. SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  13. With ActiveWorkbook.Worksheets("出納帳").Sort
  14. .SetRange Range("A1:G" & LastRow)
  15. .Header = xlYes
  16. .MatchCase = False
  17. .Orientation = xlTopToBottom
  18. .SortMethod = xlStroke
  19. .Apply
  20. End With
  21. 　
  22. End Sub

§2.4. マクロ名「出納帳を集計する」

出納帳を集計し、名前別の金額合計を［作業用］シートに貼り付ける。

１）操作
  1. ［出納帳］シートを選択する。
  2. セル A1 を選択し、
  3. ［データ］タブ/アウトラインGr.の［小計］をクリックする。
  4. 【集計の設定】ダイアログボックスで下記を設定し、［OK］をクリックする。
    • グループの基準： 名前
    • 集計の方法： 合計
    • 集計するフィールド：R入金額， R出金額
    • R現在の小計をすべて置き換える
    • □グループごとに改ページを挿入する
    • R集計行をデータの下に挿入する
  5. アウトライン記号の［2］をクリックする。
    　→ 集計行と総計のみ表示される。
  6. 次の操作はマクロではできないので、
    1. セルC1をクリックし、
    2. Ctrl＋↓　しておく。
  7. 集計されたデータ（C1〜F124）を選択する。
  8. 「可視セル」のみを選択する。（Alt＋;（セミコロン））
  9. コピーする。（Ctrl＋C）
  10. 作業用シートのセル A1 に値のみ 貼り付ける。
  11. セル A1 をクリックし、選択を解除しておく。
  12. ［出納帳］シートを選択し、
  13. セル A1 をクリックし、
  14. 小計を［すべて削除］する。

２）得られたマクロ

コメント行は適当に加除してある。

  1. Sub 出納帳を集計する()
  2. Sheets("出納帳").Select
  3. Range("A1").Select
  4. Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5, 6), _
  5. Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  6. ActiveSheet.Outline.ShowLevels RowLevels:=2
  7. Range("C1").Select
  8. Selection.End(xlDown).Select
  9. Range("C1:F124").Select
  10. Selection.SpecialCells(xlCellTypeVisible).Select
  11. Selection.Copy
  12. Sheets("作業用").Select
  13. Range("A1").Select
  14. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  15. :=False, Transpose:=False
  16. Range("A1").Select
  17. Sheets("出納帳").Select
  18. Range("A1").Select
  19. Application.CutCopyMode = False
  20. Selection.RemoveSubtotal
  21. End Sub

３）補完後マクロ
  • “124”という行番号が出てくるが、これはデータ量で変わるため固定できない。
  1. Sub 出納帳を集計する()
  2. Dim LastRow As Long
  3. 　
  4. Sheets("出納帳").Select
  5. Range("A1").Select
  6. Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5, 6), _
  7. Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  8. ActiveSheet.Outline.ShowLevels RowLevels:=2
  9. Range("C1").Select
  10. Selection.End(xlDown).Select
  11. LastRow = ActiveCell.Row - 1
  12. Range("C1:F" & LastRow).Select
  13. Selection.SpecialCells(xlCellTypeVisible).Select
  14. Selection.Copy
  15. Sheets("作業用").Select
  16. Range("A1").Select
  17. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  18. :=False, Transpose:=False
  19. Range("A1").Select
  20. Sheets("出納帳").Select
  21. Range("A1").Select
  22. Application.CutCopyMode = False
  23. Selection.RemoveSubtotal
  24. 　
  25. End Sub


§2.5. マクロ名「aa出納帳集計」

今日 作成したマクロを、連続して動かすマクロを作成する。

    • 先頭の“aa”は、［マクロの表示］ボタンをクリックした時の表示の順番の問題で、他意はない。
１）操作
  1. ［マクロの記録］を開始し、
  2. マクロ名を入力し、［OK］する。
  3. マクロの記録を終了する。
２）得られたマクロ

コメント行のみで、中身は無い。

  1. Sub aa出納帳編集()
  2. 　
  3. End Sub
３）補完後マクロ
  1. Sub aa出納帳編集()
  2. 　
  3. Call 集計シートのクリア
  4. Call 作業用シートのクリア
  5. Call 出納帳を名前で並べ替える
  6. Call 出納帳を集計する
  7. 　
  8. End Sub

事後放言

  • 今日行った内容と、帰宅後 気が付いた点で更新した。（次回、確認する）
  • 次回送りになった内容は、冗長になるので削除した。
  • 次回、20日には完成させるッ!!!
  • bootさんから「Excelデータベースの検索」をしたい旨の要望が出たので、次回までに検討する。