グラフを作成する際、もっとも多いトラブルの1つが**「シート名を変更したことで参照が切れてしまう」**という問題です。
VBAで "Worksheets("データ")" のように名前でシートを参照している場合、シート名変更でコードが動かなくなり、実行時エラー9（Subscript out of range） が発生します。
本記事では、シート名が変更されても壊れない「参照切れ防止テクニック」をまとめます。
目次
1. コードネーム（CodeName）で参照する方法【最強で推奨】
ワークシートには「コードネーム」があり、
シート名が変わっても絶対に変わりません。
VBAのプロパティウィンドウで次のように表示されます：
- 左側：コードネーム（例：
Sheet1） - 右側：シート名（例：
売上データ）
シート名は自由に変更可能ですが、コードネームはユーザーが変更しない限り固定です。
● コードネームを使った参照例
Sub Sample()
Sheet1.Range("A1").Value = "OK"
End Sub上記の Sheet1 はシート名ではなく「コードネーム」。
ユーザーがシート名を「売上表」「DataSheet」などに変更してもコードは壊れません。
● グラフ作成時もコードネームを使う
Sub CreateChart_CodeName()
Dim ws As Worksheet
Set ws = Sheet1 '← 変更されない参照
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim dataRange As Range
Set dataRange = ws.Range("A1:B" & lastRow)
Dim chtObj As ChartObject
Set chtObj = ws.ChartObjects.Add(Left:=300, Top:=50, Width:=400, Height:=300)
chtObj.Chart.SetSourceData dataRange
End Subこれが最も安定します。
2. シート名ではなく Worksheets(Index) で参照する方法
コードネームが使えない場合は、インデックス番号で参照する方法も有効です。
Set ws = Worksheets(1)ただし、
- シートの並び順が変わると壊れる
- 新しいシート挿入で番号がズレる
といったリスクがあります。
コードネーム＞インデックス＞シート名 の順で安全です。
3. シート名を変えられても自動で見つける方法（Name検索）
どうしてもシート名を使いたい場合、
存在チェックをしてから参照する ことでエラーを回避できます。
● シート存在チェック関数
Function ExistsSheet(sheetName As String) As Boolean
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = sheetName Then
ExistsSheet = True
Exit Function
End If
Next ws
ExistsSheet = False
End Function● 使用例（存在しなければ終了）
Sub SafeSheetReference()
Dim targetName As String
targetName = "データ"
If Not ExistsSheet(targetName) Then
MsgBox "シート名「" & targetName & "」が見つかりません。", vbExclamation
Exit Sub
End If
Dim ws As Worksheet
Set ws = Worksheets(targetName)
End Sub※ これは「参照切れ防止」にはなるものの、
シート名が変わった場合は処理自体は止まるので 完全な対策ではない ことに注意。
4. グラフの元データがシート名変更で壊れる問題への対策
グラフが内部で参照しているデータ範囲には
シート名が文字列として含まれるため、変更すると壊れます。
例：=Sheet1!$A$1:$B$10
これがシート名変更時に無効になります。
● VBAでグラフ系列の参照を再設定する
Sub ResetChartSeries()
Dim ws As Worksheet
Set ws = Sheet1 'コードネーム
Dim cht As Chart
Set cht = ws.ChartObjects(1).Chart
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
cht.SetSourceData ws.Range("A1:B" & lastRow)
End Subコードネームとの併用で参照切れを完全に回避できます。
5. 実務向け：シート名依存を完全になくすテンプレート
Sub CreateChart_NoSheetNameRisk()
'シート名ではなくコードネームを使用
Dim ws As Worksheet
Set ws = Sheet1
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "データが不足しています。", vbExclamation
Exit Sub
End If
Dim dataRange As Range
Set dataRange = ws.Range("A1:B" & lastRow)
Dim chtObj As ChartObject
'既存グラフ削除（重複防止）
For Each chtObj In ws.ChartObjects
chtObj.Delete
Next chtObj
'グラフ作成
Set chtObj = ws.ChartObjects.Add(Left:=300, Top:=50, Width:=400, Height:=300)
chtObj.Chart.SetSourceData dataRange
End Sub6. ポイントまとめ
| 方法 | 安定度 | 特徴 |
|---|---|---|
| コードネーム参照（最強） | ★★★★★ | シート名変更の影響ゼロ |
| Worksheets(Index) | ★★★☆☆ | 並び順変更で壊れる |
| Worksheets(“名前”) | ★★☆☆☆ | 名称変更でエラー |
| 存在チェック関数 | ★★★☆☆ | 例外処理として有効 |
コメント