和風スパゲティのレシピ

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

トップ > 汎用マクロ・便利ツール > ワークシートのオブジェクト名をマクロで設定する

ワークシートのオブジェクト名をマクロで設定する

汎用マクロ・便利ツール マクロでマクロを書く 変数・定数 VBA

ワークシートのオブジェクト名をマクロで設定する方法を紹介します。


ワークシートをオブジェクト名で指定するのはとても便利です。


シートオブジェクト名の設定

こうやっておくと、
　

WS集計表.Range("A1") = WSデータ.Range("B2")

このようにコードが書けますので、

　

  • 変数が不要
  • シート名を変更しても、コードを変更不要

というメリットを、簡単に享受できます。



全シートをオブジェクト名で指定してしまうのが簡単なのですが、
初期設定が面倒なので、それをマクロにやらせてしまおう、という魂胆です。


「アクティブシートのオブジェクト名を、シート名と同じ名前にする」

という仕様の、ユーザー実行型の便利マクロにしましたので、
Excel起動時に裏で開かれる「個人用マクロブック」などに搭載して使ってください。


頻繁に使うマクロになるので、「オブジェクト名」「ワークシート」の頭文字から、
「Ctrl + (Shift + ) OやW」あたりにショートカットを設定すると便利です。

　

ソースコード

Sub アクティブシートのオブジェクト名を設定する()
    On Error Resume Next
    
    ' 「WSアクティブシート名」というオブジェクト名に
    Dim 設定オブジェクト名 As String
    設定オブジェクト名 = "WS" & ActiveSheet.Name
    
    ' (○○)は_○○に書き換え、スペースは消去
    設定オブジェクト名 = Replace(設定オブジェクト名, "(", "_")
    設定オブジェクト名 = Replace(設定オブジェクト名, "（", "_")
    設定オブジェクト名 = Replace(設定オブジェクト名, ")", "")
    設定オブジェクト名 = Replace(設定オブジェクト名, "）", "")
    設定オブジェクト名 = Replace(設定オブジェクト名, " ", "")
    設定オブジェクト名 = Replace(設定オブジェクト名, "　", "")
    
    ' 文字制限
    設定オブジェクト名 = Left(設定オブジェクト名, 16)

    ' オブジェクト名を設定
    ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name _
         = 設定オブジェクト名
    
End Sub

使い方

このマクロをショートカットにセットし、



ブックサンプル

このようなブックで、シート4枚分実行すれば、



オブジェクト名サンプル

こんな感じで設定されます。


「WS」をオブジェクト名に接頭しているのは、ws+Ctrl+Spaceで

自動メンバーサンプル

この選択肢から選べるのが、日本語入力も不要で、とても便利だからです。



ガンガン使ってください。



とその前に。


↑のコードを実行しようとしても、おそらく初回はエラーが出ます。

マクロでマクロを編集するためには、ひとつセキュリティ設定を切る必要があり、

「開発」⇒「マクロのセキュリティ」⇒
「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する」

にチェックを入れることで使用可能になります。


セキュリティに関することなので、このセキュリティの詳しい説明と、
このマクロの使用は、自己責任でお願いいたします。

コードの解説

「VBProject」「VBComponents」あたりが、
マクロでマクロを書く際の基本的なオブジェクト・プロパティになりますが、
この解説をしだすと長いので割愛します。


このマクロはコピペで使ってしまっていいと思います。

本当にマクロでマクロを書く機会が訪れたら、詳しいサイトで勉強してください。


一応、シートオブジェクト名の設定を汎用関数にするとこちらです。

Sub シートオブジェクト名を設定する(設定シート As Worksheet, 設定オブジェクト名 As String)
    設定シート.Parent.VBProject.VBComponents(設定シート.CodeName).Name _
        = 設定オブジェクト名
End Sub

' 上の実行マクロの、この部分が↓
ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name _
    = 設定オブジェクト名

' こう書き換わる
Call シートオブジェクト名を設定する(ActiveSheet, 設定オブジェクト名)

ActiveSheet以外のオブジェクト名を変えたい場合はこちらを使用してください。



他の部分は、WSを接頭して、"(○○)"を、"_○○"に変えているだけです。


WSを接頭するのは、前述の通り

自動メンバーサンプル

これに便利なのですが、


シートが増えていくと選択肢がすごい量になるので、

  • 変数名はws、オブジェクト名はOWSにして分ける
  • DTws売上データ、MTws商品マスタなど、種別で接頭詞を分ける

あたりで細分化してもいいかもしれません。


その場合は、"WS"を"OWS"にしたり、

If Right(ActiveSheet.Name, 3) = "マスタ" Then
    設定オブジェクト名 = "MT" & 設定オブジェクト名
End If

を加えたりするとよいでしょう。


このあたりはみなさんの好みや、
お勤め先のシート名のルールなどがすごく出る部分ですので、
人とマクロを共有しない限りは、好きな英字にカスタマイズして下さい。



なお、このマクロはあまり堅牢ではありません。


シートオブジェクト名は「半角1バイト、全角2バイトで31バイトまで」なのですが、
VBAのLeftB関数は半角も2バイトで数えます。

この再現が割と面倒で、開き直ってLeftでやっているため、
必要以上に文字列をカットすることがあります。


あとは、Unicode文字などの禁則文字も考慮していません。


が、このマクロはちょっと楽をするためのマクロですので、
こういう細部の仕様にこだわるのはやめましょう。


別にエラー起きたら自分で設定すればいいだけです。


プログラミングにありがちな、

  • 10分を1分にするマクロは30分で書ける
  • さらに1分を30秒にするマクロは5時間で書ける

という罠にかからないように気をつけましょう。


商品として販売するマクロでもなければ、
「最後は手でやればいいや」は非常に重要な手法です。

プログラミングを勉強し過ぎて、視野が狭くならないように気をつけましょう。


こんな時こそ「名を瞑ってOn Error Resume Next」です。
（乱用は絶対ダメですよ）


と、もっともらしい言い訳を書いて終わります。


結構気に入っているマクロですので、ぜひ使ってくださいね。

おまけ：併せて使ってほしい列番号Enumコード自動生成

列番号のEnum定数コードを自動生成する

www.limecode.jp

このマクロと、是非一緒に使ってください。


実行エリア

ここを選択してマクロを実行すると、
　

Public Const R1stくだもの売上データ =  3 
Public Enum CNoくだもの売上データ
    品物 = 2
    価格
    個数
    売上
End Enum

これが自動生成されるマクロです。


共に「シート名」をベースとした変数名になるので、

WSくだもの売上データ.Cells(R, CNoくだもの売上データ.価格)

と、「シートオブジェクト名とEnum名の一致」をもって、
バグのないコーディングを支援します。


シートごとにショートカットを2回ずつ実行するだけで、
定数定義が勝手に終わり、いきなりコーディングから始められるのは、
なかなか気持ちがいいです。


ぜひ使ってみてください。