ここでは 実務寄りで「ByRef を使うとどう便利になるか」がすぐ理解できる 3 本のサンプルを用意しました。
すべて Excel シートで実行でき、セルに結果を書き込みます。
実務寄りサンプル①：セル値を ByRef で書き換える
指定したセル（例：A1）の値をプロシージャで変更し、呼び出し元に反映させる例。
コード
Sub Sample_CellValue_ByRef()
Dim cellValue As String
cellValue = Range("A1").Value ' A1 の値を取得
Call AddPrefix_ByRef(cellValue) ' 値に「[済] 」をつける
Range("A2").Value = "After:"
Range("A3").Value = cellValue ' A3 に変更後を表示
End Sub
' --- 実務でよくある「値を加工して戻す」 ---
Sub AddPrefix_ByRef(ByRef text As String)
If text <> "" Then
text = "[済] " & text
End If
End Sub
実務での用途
- チェック済み項目に「済」印を付ける
- データの前処理（前後の文字追加、不正文字の除去など）
- ログ用に日時を付ける「加工ルール」専用プロシージャを作れる
ByRef を使うと、多くのセル値を一気に加工する共通関数が作りやすくなります。
実務寄りサンプル②：シート名を ByRef の変数で変更する
プロシージャ側で変数の内容を変え → その値でシート名を変更 → 呼び出し元にも変更後の名前が残る
という実務でよくある処理を実現します。
コード
Sub Sample_SheetName_ByRef()
Dim newName As String
newName = "レポート"
Range("A5").Value = "Before: " & newName
Call MakeSheetNameUnique(newName) ' ※名前を変更＆加工
' 変更された名前でアクティブシート名を変更
ActiveSheet.Name = newName
Range("A6").Value = "After: " & newName
End Sub
' --- シート名を「重複しない形」に加工する実務処理 ---
Sub MakeSheetNameUnique(ByRef nameText As String)
Dim baseName As String
baseName = nameText
Dim i As Integer
i = 1
' 同名シートがあれば連番を付けていく
While SheetExists(nameText)
i = i + 1
nameText = baseName & "_" & i
Wend
End Sub
Function SheetExists(sheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sheetName)
SheetExists = Not ws Is Nothing
End Function
実務での用途
- 日報・月報などを自動で連番シートへ作成
- 「レポート」「レポート_2」「レポート_3」などの重複回避
- 自動出力時の命名規則を統一化
ByRef を使うことで「変更したシート名」が呼び出し元でもそのまま使えるため便利。
実務寄りサンプル③：Range オブジェクトを参照渡し（ByRef）で扱う
Range（セル範囲）をプロシージャに渡して、
プロシージャ側で範囲を別の範囲に変更 → 呼び出し元の Range 変数も変更される
という参照渡しの特徴がわかる例です。
コード
Sub Sample_RangeObject_ByRef()
Dim target As Range
' 最初は A1 を指す
Set target = Range("A1")
Range("A10").Value = "Before target:"
Range("A11").Value = target.Address ' → $A$1
' --- プロシージャ側で Range を変える ---
Call ChangeRange_ByRef(target)
Range("C10").Value = "After target:"
Range("C11").Value = target.Address ' → 変更後のセル
End Sub
' --- Range を別のセルに変更してしまう ---
Sub ChangeRange_ByRef(ByRef rng As Range)
' rng が指すセルを B5 に変更
Set rng = Range("B5")
' 実務では「次の空きセルを返す」などで使う
End Sub
実務での用途
- 「次に書き込むセル」を探して Range 変数を更新しながら処理を進める
- 複数の Sub で同じ Range 変数を共有して処理する
- 可変範囲のレポート生成時（行数が増えるたびに下方向へ移動）
- ループごとに書き込み位置を更新する場合
例：
「WritePosition」という Range 変数をB2 → B3 → B4 → … と自動的に進めながら書き込みたいときに使う。
まとめ：実務での ByRef の価値
| 用途 | ByRef が便利な理由 |
|---|---|
| セル加工（文字追加・正規化） | 変換後の値を戻す必要がある |
| シート名生成 | プロシージャ側で決めた名前を呼び出し元にも反映したい |
| Range（位置）管理 | 書き込み位置などを動的に更新できる |
| 何度も使う共通関数の作成 | 複数の値を変更して返せる |