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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

記述方法はこちら。

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

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

実際のコードの例

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

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

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

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

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

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

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

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

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

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

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

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

こんな感じで。


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

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

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

注意点

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

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

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

SheetsInNewWorkbook,エラー,VBA

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

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

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

最後に

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

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

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

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

  投稿者プロフィール

ノグチフリーランスPG・SE
元製造子会社SE。
業務システム保守・導入両方やった経験をお伝えすることで、少しでもシステムや手作業に悩まされる人が減ればいいなぁと日々夢見ております。

コメント