和風スパゲティのレシピ

日本語でコーディングするExcelVBA

ワークシート

3/18(土)20:00~ VBA勉強会「配列入門!Array&Collection&Dictionary欲張りセット」

3/18(土)20:00~ Zoom勉強会に登壇します。「配列入門!Array&Collection&Dictionary欲張りセット」と題し、まだ配列を使ったことがない方、CollectionやDictionaryに触れてみたい方、3つの特長と使い分けポイントを整理したい方に向けた講座を行います。

Worksheets("○○"). の入力選択肢を出す方法

Worksheets("○○")でシートを取得したとき困るのが、「.」を入れても入力選択肢が出ないことです。この対処法として「変数にSet」「シートオブジェクト名」がありますが、第3の方法「Worksheet型にキャストする関数を作る」方法を解説します。

すべてのシートを一括で再表示するマクロ

すべてのシートを一発で再表示するマクロを紹介します。For EachですべてのWorksheetを走査し、ワークシート.Visible = True によって再表示していくコードです。ブックの保護が有効だとエラーになるため、ProtectStructureを判定して回避します。

Protectメソッドを連続実行すると保護が解除される

保護されたシートに再度Protectメソッドを実行した際、引数DrawingObjectsまたはScenariosを指定したときだけ保護が外れる不具合が存在します。Protectメソッドの引数「Contents」にTrueを渡すか、必ずUnprotectを挟むことで解消できます。

マクロでのみ編集可能なシート保護を設定する

シートの保護でユーザーの操作を制限したシートにおいて、マクロのみ編集ができるように設定する方法を解説します。ProtectメソッドのUserInterfaceOnlyを使うことで実装できますが、この設定は保存することができないため注意する必要があります。

ブックを保護する - Workbook.Protect

ブックの保護を設定/解除する方法を解説します。WorkbookオブジェクトのProtectメソッドを使います。ブックの保護という名称ですが、実際に禁止される操作は、ワークシートの追加、表示/非表示、削除、移動、名前変更になります。

シートの保護設定を調べる/同じ設定でシートを保護する

ワークシートの保護設定の内容を調べる方法と、それを利用して同じ設定でシートを保護する方法を解説します。シート保護の各設定がONになっているかどうかを調べるには、WorksheetオブジェクトのProtectionプロパティを使用します。

シートを保護する - Worksheet.Protect

ワークシートの保護を設定/解除する方法を解説します。シートを保護するにはWorksheetのProtectメソッドを使用します。簡単ですが非常に引数が多いメソッドですので、よく扱う方は全引数を設定するコードを保存しておきましょう。

Worksheet.Selectと.Activateの違い

Worksheetオブジェクトから呼び出す場合の、SelectメソッドとActivateメソッドの違いを説明します。どちらもアクティブシートを設定/変更できるメソッドですが、単独のシート選択はActivate、複数のシート選択はSelectがそれぞれ得意です。

すべてのシートをA1セル選択状態にするマクロ

すべてのシートをA1セル選択状態にするマクロを紹介します。For Eachで全シートをActivateし、Range("A1").Selectをして回るだけの簡単なコードですが、ウィンドウ枠の固定に対応する必要があるため、ActiveWindow.ScrollRow = 1も実行します。

アクティブシートを新規ブックにコピーするマクロ

アクティブシートを新規ブックにコピーするマクロを紹介します。コードはたったの一行で、ActiveWindow.SelectedSheets.Copyのみです。「個人用マクロブック」などに搭載しツールバーやショートカットキーへ登録して使ってください。

複数のシートをまとめてコピー/移動する

複数のワークシートを一度にコピー/移動する方法を紹介します。Worksheetsは配列を渡すと複数のシートを返してくれますので、そこからWorksheets(Array("集計表", "データ")).Copyと、単独シートと同じようにCopy/Moveメソッドを使用します。

すべてのシートに同じ処理を行う - For~Nextステートメント

ブック内のすべてのシートをループして、複数のシートに同じ処理を実行するコードを紹介します。たくさんのシートに同じ処理を行うのは、マクロならではの便利な技です。いくつかパターンを用意しましたので、お好きなコードをお持ち帰りください。

アクティブシートを変えずにシートを追加・コピーする

アクティブシートを変えずに、シートを追加・コピー・移動する方法を解説します。アクティブシートを全く変えることなくメソッドを実行する方法はありませんので、元のアクティブシートを記憶し、後でそのシートをActivateして地道に達成します。

新規シートを新しいブックに追加する

新しいシートを、新規のブックに追加する方法を紹介します。それには Set 新規シート = Workbooks.Add.Worksheets(1) というコードを実行します。Workbooks.Addでブックを追加すればそこにシートがありますので、そのシートを取得すればOKです。

シート名を取得・変更する - Worksheet.Name

ワークシートの名前を取得したり、変更する場合は、WorksheetオブジェクトのNameプロパティを使用します。シート名 = Worksheet(1).Nameというコードで名称を取得でき、Worksheet(1).Name = "変更シート名"というコードで名称を変更できます。

シートを削除する - Worksheet.Delete

ワークシートを削除するDeleteメソッドの使い方を解説します。シートを削除する場合は、[削除するシート].Deleteを実行します。削除時の警告メッセージを表示せずにシートを削除する場合はApplication.DisplayAlerts = False/Trueでこのコードを挟みます。

重複しないよう連番付きでシート名を設定する

シートの名称設定は、すでに使われているシート名を指定するとエラーになってしまいます。これを回避するために、シート名が重複した場合は(2)、(3)、(4)…と連番を付与してからシート名を設定するコードを紹介します。

ブック内の最終(右端)シートを取得する

あるブックの最後のシートを取得する方法を解説します。これを一発で取得するWorkbooks("○○").LastSheetみたいなプロパティはありませんので、Set 最終シート = 指定ブック.Worksheets(指定ブック.Worksheets.Count)と地道に取得することになります。

文字列からシート名に使えない文字を消去する

ワークシート名には文字数と使用文字に条件があり、この条件に違反するとエラーが発生します。文字数の方は簡単で、Left関数で31文字以降をカットしてしまえばOKなのですが、禁則文字の方はReplace関数で該当の文字を1個ずつ消してあげる必要があります。

ワークシートを指定する方法7選

「ワークシートの指定方法」はいろいろなやり方があり、どれがいいというわけではなく一長一短があります。このいろいろなワークシートの指定方法を、「基本的だけど効果の高い方法」に厳選して解説していきますので、是非習得していってください。

偶数シートなど飛び飛びのシートをループする

偶数番のシートや、3つおきのシートなど、規則性がある飛び飛びのシートをループ処理するコードを紹介します。Forステートメントの「Step」を使うのが最も基本的で、For シート番号 = 2 To 6 Step 2 という記述で2,4,6番目のシートを処理できます。

隣のシート(前のシートや次のシート)を取得する

あるシートを変数などで持っているときに、隣(前後)のシートを取得する方法を紹介します。Worksheetオブジェクトには、これを一発で取得するプロパティが用意されており「Next/Previousプロパティ」を使うことで、前後のシートを取得することができます。

指定のシートが何番目のシートか調べる

指定のシートが何番目のシートか調べる方法を解説します。例えばActiveSheetが何番目のシートかを調べるには、ActiveSheet.Indexを取得します。このIndexで求めた数値は、グラフシートなどがなければWorksheets(x)のxに入れて使うことができます。

シートを新規ブックにコピー/移動する

ワークシートを新しいブックにコピー/移動する方法を紹介します。この方法はとても簡単で、Worksheets("○○").Copy でOKです。移動する場合は、Worksheets("○○").Move になります。

シートを別のブック(の末尾)にコピー/移動する

ワークシートを別のブックにコピー/移動する方法を紹介します。コピー・移動する際は、 Worksheetの、Copy/Moveメソッドを使用し、これらのメソッドの引数「After/Before」には別のブックのシートも特に制約なく指定できます。

シートを移動する - Worksheet.Move

ワークシートを移動する「Moveメソッド」の使い方を解説します。[移動するシート].Move Before:=[ここで指定したシートの1つ前に挿入]、または[移動するシート].Move After:=[ここで指定したシートの1つ後ろに挿入]というコードを実行します。

シートをコピーする - Worksheet.Copy

ワークシートをコピーする「Copyメソッド」の使い方を解説します。[コピーするシート].Copy Before:=[ここで指定したシートの1つ前に挿入]、または[コピーするシート].Copy After:=[ここで指定したシートの1つ後ろに挿入]というコードを実行します。

シート名をセル値から取得してWorksheetを指定する方法

セルの値からシート名を取得したくなることがあります。Worksheets(Range("A1"))と書きたくなるのですが、これではエラーになってしまいます。この解決策は非常に簡単で、Worksheets(Range("A1").Value)と、Valueプロパティ明示すればOKです。

英訳してはいけない変数名第1位「ワークシート」

今回は日本語変数に関するお話です。単刀直入に、シート名が日本語だった場合に、その変数名をわざわざ英訳してはいけません。VBAではワークシート名もソースコードの一部です。同じオブジェクトの変数名は、すべてのコードで同じ単語を当てましょう。