【エクセルVBA】エクセルブックのデフォルトシート数を変更する方法

皆様こんにちは、ノグチです。

折角VBAで作ったエクセルのツールが、エクセルのオプションの値によってエラーになってしまったり、想定以外の動作になってしまうこと、ありませんか？

前回記事でご紹介した通り、エクセルのブックを開いたときにすでに挿入されているシート数は、エクセルのバージョンによって異なりますし、また、ユーザーが手動で変更できるようになっています。

つまり、「ある人のエクセルはブックを開いたとき3シート挿入されているけど、ある人は1シートのみ挿入されている」というように、人それぞれ、という状態になります。

この、人によってエクセルの環境が違うというのは、VBAのツールを使う上ではちょっとしたリスクですよね。

そこで今回は、VBAでエクセルブックのデフォルトシート数を変更する方法をご紹介します。

デフォルトシート数を手動で変更する方法については、下記の記事をご覧ください。

不要なシートを作らせない！エクセルのデフォルトシート数を変更する方法

エクセルのブックを開いたときに自動的に作成されるシート数を、任意の数に変更する方法をご紹介しています。オプションから基本設定を開いて、該当項目の値を設定するだけで変更完了！「ブックを開いたときに不要なシートがある！」「毎回不要なシートを削除するのが面倒だ！」という方にオススメです。

VBAでブックのシート数を設定するメリット
Application.SheetsInNewWorkbookでシート数を変更する
実際のコードの例
1. 変更したシート数をもとに戻したい場合
注意点
1. 指定できるシート数には制限がある
最後に

VBAでブックのシート数を設定するメリット

例えば、あるVBAで開発されたツールのコードに、新しいブックを開いて2番目のシートを選択する、という処理が書いてあった場合。

シートが1枚しか挿入されていない人のエクセルで先のツールを使用すると、2枚目のシートが無いためにエラーとなってしまいます。

エラーを回避するには、エクセルのオプションからデフォルトシート数を変更してもらえればいいのですが、ユーザーに対して手動でシート数を変えてもらうのも煩わしいですし、何より設定漏れのリスクもあります。

ならばVBAのコード内で、人間の手を介することなく、シート数を変更してしまえばいいのです！

そしてそれは、ApplicationオブジェクトのSheetsInNewWorkbookプロパティを使えば簡単ですよ。

Application.SheetsInNewWorkbookでシート数を変更する

エクセルのデフォルトシート数を変更するには、ApplicationオブジェクトのSheetsInNewWorkbookプロパティを使います。

記述方法はこちら。

Application.SheetsInNewWorkbook = 設定したいシート数

この、設定したいシート数には直接数値を指定するか、変数や定数で値を指定することができます。

実際のコードの例

では、実際に下記のコードでエクセルのデフォルトシート数を変更してみましょう。

Sub ChangeSheetNum()

    '新規ブックのシート数を変更
    Application.SheetsInNewWorkbook = 5

End Sub

このコードを実行したあとに、エクセルのオプションからデフォルトシート数を確認してみると…

エクセル,オプション,シート数,基本設定

この通り、コードで指定した「5」が設定されていますね。

これで、次にオープンするブックには5枚のシートが作られるようになります。

変更したシート数をもとに戻したい場合

さて、Application.SheetsInNewWorkbook構文でシートの数は変えられました。

しかし、人によってはシート数をあえて設定している場合もあります。

VBAで変更したシート数は、VBAを使って元に戻しておきましょう。

まずは、シート数を変更する前に、現状設定されているシート数を取得しましょう。

今エクセルのシート数に設定されている値は、同じくApplication.SheetsInNewWorkbookで取得することができますので、シート数を変更する前に変数に退避させておきましょう。

こんな感じで。

dim lngTmpSheet
lngTmpSheet = Application.SheetsInNewWorkbook

そして、このコードの後に新規ブックの追加などの処理を記述しておきます。

その後に、変数に退避しておいたシート数を、再度Application.SheetsInNewWorkbookに設定します。

Application.SheetsInNewWorkbook = lngTmpSheet

全体ではこんな感じのコードになります。

Sub ChangeSheetNum()
Dim lngTmpSheet    As Long '既存シート数退避用
Dim lngSheet　　　 As Long '設定したいシート数

'既存のデフォルトシート数を変数に退避
lngTmpSheet = Application.SheetsInNewWorkbook

'新規ブックのシート数を変更
lngSheet = 2
Application.SheetsInNewWorkbook = lngSheet

Application.Workbooks.Add

'デフォルトシート数をもとに戻す
Application.SheetsInNewWorkbook = lngTmpSheet

End Sub