Excel VBA Constの使い方｜Dim違い・Public Const徹底解説

'// 同じモジュール内で使う定数
Private Const SHEET_NAME As String = "集計"

'// プロシージャ内だけで使う定数
Sub Sample()
    Const TAX_RATE As Double = 0.1
End Sub

'// 複数モジュールから使う定数
Public Const APP_NAME As String = "残業0集計ツール"

Private Const TAX_RATE As Double = 0.1

Private Const MAX_RETRY_COUNT As Long = 3

Private Const OUTPUT_SHEET_NAME As String = "出力"

Sub ConstBasicSample()
    Const TAX_RATE As Double = 0.1

    Dim price As Long
    Dim tax As Double

    price = 1000
    tax = price * TAX_RATE

    MsgBox "税額は " & tax & " 円です。"
End Sub

'// 悪い例: 値の意味が分かりにくい
tax = price * 0.1

'// 良い例: 値の意味が名前で分かる
tax = price * TAX_RATE

Private Const SHEET_MASTER As String = "マスタ"

Private Const SHEET_OUTPUT As String = "出力"

Private Const MAX_ROW_COUNT As Long = 100000

Private Const TAX_RATE As Double = 0.1

Private Const SHOW_CONFIRM As Boolean = True

Private Const DEADLINE_DATE As Date = #5/31/2026#

Sub CopyMasterToOutput()
    Private Const SOURCE_SHEET As String = "マスタ"
End Sub

Sub CopyMasterToOutput()
    Const SOURCE_SHEET As String = "マスタ"
    Const OUTPUT_SHEET As String = "出力"

    Worksheets(SOURCE_SHEET).Range("A1").Copy _
        Destination:=Worksheets(OUTPUT_SHEET).Range("A1")
End Sub

Private Const SOURCE_SHEET As String = "マスタ"
Private Const OUTPUT_SHEET As String = "出力"

Sub CopyMasterToOutput()
    Worksheets(SOURCE_SHEET).Range("A1").Copy _
        Destination:=Worksheets(OUTPUT_SHEET).Range("A1")
End Sub

Sub ClearOutput()
    Worksheets(OUTPUT_SHEET).UsedRange.ClearContents
End Sub

Sub DimSample()
    Dim total As Long
    Dim i As Long

    For i = 1 To 10
        total = total + i
    Next i

    MsgBox total
End Sub

Sub ConstAndDimSample()
    Const MAX_COUNT As Long = 10

    Dim total As Long
    Dim i As Long

    For i = 1 To MAX_COUNT
        total = total + i
    Next i

    MsgBox total
End Sub

'// 列番号の意味が分かりにくい
Cells(i, 5).Value = "完了"

Private Const STATUS_COL As Long = 5

Sub MarkDone()
    Dim i As Long

    For i = 2 To 10
        Cells(i, STATUS_COL).Value = "完了"
    Next i
End Sub

Sub ReadCellValue()
    Dim userName As String

    userName = Range("A2").Value
    MsgBox userName & "さんのデータを処理します。"
End Sub

'// これはNG
Const TODAY_TEXT As String = Format(Date, "yyyy/mm/dd")

Sub TodayTextSample()
    Dim todayText As String

    todayText = Format(Date, "yyyy/mm/dd")
    MsgBox todayText
End Sub

'// 標準モジュール ModuleConfig の先頭に書く
Public Const APP_NAME As String = "売上集計マクロ"
Public Const MASTER_SHEET As String = "マスタ"
Public Const OUTPUT_SHEET As String = "集計"

Sub ShowAppName()
    MsgBox APP_NAME & "を開始します。"
End Sub

Option Explicit

'// 定数を宣言
Private Const SOURCE_SHEET As String = "元データ"
Private Const OUTPUT_SHEET As String = "集計"
Private Const CUSTOMER_COL As Long = 2
Private Const AMOUNT_COL As Long = 5

Sub BuildSummary()
    Dim lastRow As Long
    Dim i As Long
    Dim total As Double

    With Worksheets(SOURCE_SHEET)
        lastRow = .Cells(.Rows.Count, CUSTOMER_COL).End(xlUp).Row

        For i = 2 To lastRow
            total = total + .Cells(i, AMOUNT_COL).Value
        Next i
    End With

    Worksheets(OUTPUT_SHEET).Range("B2").Value = total
End Sub

'// ModuleConfig
Option Explicit

Public Const APP_NAME As String = "売上集計マクロ"
Public Const MASTER_SHEET As String = "マスタ"
Public Const OUTPUT_SHEET As String = "集計"
Public Const LOG_SHEET As String = "処理ログ"

Const FILE_PATH As String = "C:\新しいパス\新しいファイル名.xlsx"

Sub OpenReport()
    Workbooks.Open FILE_PATH
End Sub

Sub SaveReport()
    ThisWorkbook.SaveAs FILE_PATH
End Sub

Const ERROR_MESSAGE As String = "An unexpected error has occurred."

Sub ExampleProcedure()
    On Error GoTo ErrorHandler
    
    '// コード本体
    Exit Sub

ErrorHandler:
    MsgBox ERROR_MESSAGE
End Sub

Sub ConstErrorSample()
    Const MAX_COUNT As Long = 3

    '// これはエラーになります
    MAX_COUNT = 5
End Sub

Sub VariableSample()
    Dim maxCount As Long

    maxCount = 3
    maxCount = 5
End Sub

'// これはConstでは書けません
Const TARGET_SHEETS = Array("元データ", "集計", "ログ")

Private Const SHEET_DATA As String = "元データ"
Private Const SHEET_SUMMARY As String = "集計"
Private Const SHEET_LOG As String = "ログ"

Function GetTargetSheets() As Variant
    GetTargetSheets = Array(SHEET_DATA, SHEET_SUMMARY, SHEET_LOG)
End Function

Public Enum ProcessMode
    ModePreview = 0
    ModeExecute = 1
    ModeRetry = 2
End Enum

Sub RunByMode(ByVal mode As ProcessMode)
    Select Case mode
        Case ModePreview
            MsgBox "確認モードです。"
        Case ModeExecute
            MsgBox "実行モードです。"
        Case ModeRetry
            MsgBox "再実行モードです。"
    End Select
End Sub