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

Sheetinnewbook,excel,vba,eyecatch
皆様こんにちは、ノグチです。

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

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

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

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

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

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

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

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

注意点

指定できるシート数には制限がある

手動でエクセルのシート数を設定するとき同様、VBAで設定できるデフォルトシート数には指定できる範囲があります。

この範囲から外れた値を入力してコードを実行してみると…

SheetsInNewWorkbook,エラー,VBA

この通り、エラーになってしまいます。

もちろん、0も指定できません。

設定したいシート数には、1から255の範囲の数値を入力しましょう。

最後に

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

VBAに限らずですが、ツール類はなるべく同じ環境で使ってほしいものですが、人間の手でツールが推奨する環境に設定するのは煩わしいですよね。

ApplicationオブジェクトのSheetsInNewWorkbookプロパティのように、VBAで設定を変更する方法を使えば、人間の手を介することなく、エクセルの設定をマクロの想定通りに揃えることができますよ

それでは、最後までお読みいただきありがとうございました!

タイトルとURLをコピーしました