VBA練習問題
VBA100本ノック 14本目:社外秘シート削除

VBAを100本の練習問題で鍛えます
最終更新日:2020-11-03

VBA100本ノック 14本目:社外秘シート削除


シートの値貼り付けと社外秘シートを削除する問題です。
客先へブックを送付する場合をそうていした処理になります。


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


出題

出題ツイートへのリンク

#VBA100本ノック 14本目
客先へ送付するブックを作成します。
シート名に「社外秘」の文字が含まれるシートを削除してください。
他のシートは計算式を消して値だけにしてください。
※シート間参照の数式あり。
※条件付き書式・入力規則は未使用。
※対象はアクティブブックで構いません。

マクロ VBA 100本ノック


明日出す解答のVBAを今書いています。
どうしようかと悩んだ処理のいくつかが、集まった回答に見当たらないようでした。
VBAの技術的な話と気遣いの話。
(見落としていたらゴメンナサイ)
どちらも出題としては必須事項ではないのですが…
なので、参考としてその処理を入れます。


頂いた回答

解説

#VBA100本ノック 14本目 解答
考え方中心に、
要点
・客先へ送付
・社外秘シートの削除
・値貼り付け
注意点
・シート削除の前に値貼り付けする
・シート削除できない場合がある
後者の件
・表示シートが無くなる
・xlSheetVeryHidden
最後に、非表示シートのまま内容確認しなくて良いのでしょうか?

Sub VBA100_14_01()
  Const cns社外秘 = "*社外秘*"
  Dim wb As Workbook
  Set wb = ActiveWorkbook
  
  '社外秘の全シート削除の可否
  If Not canDelete(wb, cns社外秘) Then
    MsgBox "送付すべきシートを再確認してください。"
    Exit Sub
  End If
  
  With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    '手動計算で入力したままの可能性があるので一旦再計算
    .Calculation = xlCalculationAutomatic
    .Calculation = xlCalculationManual
  End With
  
  '計算式はワークシートのみ
  Call pasteValues(wb, cns社外秘)
  
  '削除は全種類のシートが対象
  Call delSheets(wb, cns社外秘)
  
  '全シートを表示し、A1を選択しつつ先頭シートへ
  Call AllSheetsGotoA1(wb)
  
  With Application
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
    .ScreenUpdating = True
  End With
End Sub


VBAのコメントを読めばやらんとしていることは理解していただけると思います。
もっと単純なコードにすべきだとも思いましたが、VBAコードよりも考え方の方が重要だと思ったのであえて記載しました。
(もちろん考え方はいろいろあります)
VBA全文は記事補足に掲載しました。


補足

上記VBAの全コードです。

Sub VBA100_14_01()
  Const cns社外秘 = "*社外秘*"
  Dim wb As Workbook
  Set wb = ActiveWorkbook
  
  '社外秘の全シート削除の可否
  If Not canDelete(wb, cns社外秘) Then
    MsgBox "送付すべきシートを再確認してください。"
    Exit Sub
  End If
  
  With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    '手動計算で入力したままの可能性があるので一旦再計算
    .Calculation = xlCalculationAutomatic
    .Calculation = xlCalculationManual
  End With
  
  '計算式はワークシートのみ
  Call pasteValues(wb, cns社外秘)
  
  '削除は全種類のシートが対象
  Call delSheets(wb, cns社外秘)
  
  '全シートを表示し、A1を選択しつつ先頭シートへ
  Call AllSheetsGotoA1(wb)
  
  With Application
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
    .ScreenUpdating = True
  End With
End Sub

'社外秘シート以外で表示されているシートの存在確認
Function canDelete(ByVal wb As Workbook, ByVal aStr As String) As Boolean
  Dim sht As Object
  canDelete = True
  For Each sht In wb.Sheets
    If sht.Visible And Not sht.Name Like aStr Then
      Exit Function
    End If
  Next
  canDelete = False
End Function

'社外秘シート以外の全ワークシート値貼り付け
Sub pasteValues(ByVal wb As Workbook, ByVal aStr As String)
  Dim ws As Worksheet
  For Each ws In wb.Worksheets
    If Not ws.Name Like aStr Then
      'これが一番無難だと思います。
      ws.Cells.Copy
      ws.Cells.PasteSpecial Paste:=xlPasteValues
    End If
  Next
  Application.CutCopyMode = False
End Sub

'社外秘シートを削除する
Sub delSheets(ByVal wb As Workbook, ByVal aStr As String)
  Dim sht As Object
  For Each sht In wb.Sheets
    If sht.Name Like aStr Then
      'xlSheetVeryHidden対策
      sht.Visible = xlSheetVisible
      sht.Delete
    End If
  Next
End Sub

'全シートを表示し、A1を選択しつつ先頭シートへ
'いったん全てのシートを表示します。
'非表示のまま客先へ変なものを送付してしまわないように!
Sub AllSheetsGotoA1(ByVal wb As Workbook)
  Dim i As Long
  For i = wb.Worksheets.Count To 1 Step -1
    wb.Worksheets(i).Visible = xlSheetVisible
    Application.Goto wb.Worksheets(i).Range("A1"), True
  Next
End Sub

上記VBAでは、全シートのループが何度も動きますが、これはほんの一瞬なので特に気にする必要は無いと思います。
それより値貼り付けの方がはるかに時間がかかりますし、検討課題が多いと思います。

値貼り付けはUsedRange.Valueでも構いませんが、使用範囲が大きいとかえって時間もかかりますし、
極端に範囲が大きい場合は、メモリ不足で停止してしまいます。

ws.UsedRange.Value = ws.UsedRange.Value

また、以下のように計算式のあるセル範囲だけを対象にする方法もあります。

On Error Resume Next
Dim rng As Range
For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas).Areas
  rng.Value = rng.Value
Next


SheetsコレクションとWorksheetsコレクションとの違いは理解しておいてください。
Sheetsにはグラフシートも含まれますが、Worksheetsには含まれません。
滅多に使用しないとは思いますが、万一にでもグラフシートが社外秘だったなんてことがあったら困ります。

そして、お客様先に送付する前には人間が目で最終確認すべきだと思います。
「社外秘」の文字が間違っていたり、「社外持ち出し禁止」と書かれていたり・・・
考えたらきりがありません。
もちろん、出来る限りVBAで対処しておくことに越したことはありませんが、限界もあるでしょう。
少なくとも、何らかのルールは必要ですし、最後は人間が確認すべきです。

そして、そもそもの話としては、
最初から、最終的に客先送付するブックとしてこれらを意識して作成しておくべきだと思います。
例えば、ブック間リンクなどはしないようにしておくべきでしょう。

そして、最後は気遣いを忘れないようにしたいですね。


サイト内関連ページ

第40回.セルのコピー・カット&ペースト(Copy,Cut,Paste)
あるセルをコピーまたはカットして、別のセルに貼り付けるVBAの説明です。セルを同じシートの別のセルにコピーしたり、セルを別のシートにコピーしたりするVBAになります。手作業で、セルをコピー(Ctrl+C)またはカット(Ctrl+X)して、他のセルに貼り付け(Ctrl+V後にESCまたはEnter) これと同じ動作をするVBAになります。
第41回.セルのコピー&値の貼り付け(PasteSpecial)
値の貼り付けと題しましたが、値だけではなく、「形式を選択して貼り付け」のいろいろな指定方法です。セルをコピーして、他のセルに「形式を選択して貼り付け」する場合のマクロVBAコードです。セルの値や書式を別のセルにコピーすることはマクロVBAでは定番かつ必須の技術になります。
第42回.セルをコピーするとは
セルをコピーするとは、どういう事でしょうか… セルをコピーするというマクロVBAを少し掘り下げて考えることで、より実践的なマクロVBAコードを書くことが出来るようになります。コピーと一言で言っているものは、何のコピーを指しているのでしょうか。
第66回.シートのコピー・移動・削除(Copy,Move,Delete)
シートをコピーや移動をしたり、また削除する場合の説明です、VBAでは、雛形シートをコピーして使ったり、不要なシートを削除することは頻繁にあります。シートのコピー・移動には、Worksheet.Copyメソッド、Worksheet.Moveメソッド これら、WorkSheetオブジェクトのメソッドを使用します。
第69回.シートの非表示(Visible)
ユーザーが操作・閲覧する必要のないシートは非表示にする事で、使い易いExcelブックにします。マスタ情報やマクロでのみ使用するデータが入っているシート等で、通常使用者が変更することがなく、見る必要もないのであれば、シート保護をするより非表示にしてしまった方が、ユーザーにとっては使いやすいものとなります。




同じテーマ「Python入門」の記事

VBA100本ノック 11本目:セル結合の警告
VBA100本ノック 12本目:セル結合を解除
VBA100本ノック 13本目:文字列の部分フォント
VBA100本ノック 14本目:社外秘シート削除
VBA100本ノック 15本目:シートの並べ替え
VBA100本ノック 16本目:無駄な改行を削除
VBA100本ノック 17本目:重複削除(ユニーク化)
VBA100本ノック 18本目:名前定義の削除
VBA100本ノック 19本目:図形のコピー
VBA100本ノック 20本目:ブックのバックアップ
VBA100本ノック 21本目:バックアップファイルの削除


新着記事NEW ・・・新着記事一覧を見る

VBA100本ノック 34本目:配列の左右回転|VBA練習問題(11月28日)
VBA100本ノック 33本目:マクロ記録の改修|VBA練習問題(11月26日)
VBA100本ノック 32本目:Excel終了とテキストファイル出力|VBA練習問題(11月25日)
VBA100本ノック 31本目:入力規則|VBA練習問題(11月24日)
将棋とプログラミングについて~そこには型がある~|エクセル雑感(11月22日)
VBA100本ノック 30本目:名札作成(段組み)|VBA練習問題(11月22日)
VBA100本ノック 29本目:画像の挿入|VBA練習問題(11月21日)
VBA100本ノック 28本目:シートをブックに分割|VBA練習問題(11月19日)
VBA100本ノック 27本目:ハイパーリンクのURL|VBA練習問題(11月18日)
VBA100本ノック 26本目:ファイル一覧作成|VBA練習問題(11月17日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



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