VBA如何在字符串中使用变量值 解决Excel文件命名变量失效问题

问题现象 #

需要编写VBA宏生成新Excel工作簿，文件名需包含当前日历周及后续3周的周数，格式示例：Steuerliste_neues_System_KW_25-28.xlsx。代码运行后周数变量未成功拼接到文件名中，生成的文件名为Steuerliste_neues_System_KW_.xlsx。
原有实现代码：

Sub Main()
kw_ermitteln
End Sub

'Kallenderwoche ermitteln
Public Sub kw_ermitteln()
    'kw = DINKw("DD.MM.YYYY")
    kw = DINKw(Range("I1"))
    Dim kwString As String
    kwString = CStr(kw)
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=".../Steuerliste_neues_System_KW_" + kwString + ".xlsx"
    ActiveWorkbook.Close
End Sub

'Function zur ermittlung der KW
Function DINKw(dat As Date) As Integer
    Dim kw As Integer
    kw = Int((dat - DateSerial(Year(dat), 1, 1) + ((Weekday(DateSerial(Year(dat), 1, 1)) + 1) _
    Mod 7) - 3) / 7) + 1
    If kw = 0 Then
        kw = DINKw(DateSerial(Year(dat) - 1, 12, 31))
    ElseIf kw = 53 And (Weekday(DateSerial(Year(dat), 12, 31)) - 1) Mod 7 <= 3 Then
        kw = 1
    End If
    DINKw = kw
End Function

故障原因 #

• 未开启强制变量声明，过程内的kw变量未显式定义，属于Variant类型，取值异常时不会抛出明确错误，只会静默返回空值
• 取周数的日期参数直接用Range("I1")，未指定所属工作表，宏运行时如果活动工作表不是存放目标日期的工作表，会取到空值/非日期值，导致周数计算函数返回空，转字符串后为空
• VBA中字符串拼接优先用&运算符，+运算符在遇到空值、数值类型混合时容易出现非预期的拼接结果
• 原代码仅实现了单周计算逻辑，未覆盖当前周+后续3周的区间需求，且保存路径使用.../属于非法路径格式，Windows环境下无法正确识别

修复后代码 #

' 模块顶部添加，强制所有变量必须显式声明，避免静默取值错误
Option Explicit

Sub Main()
    Call kw_ermitteln
End Sub

Public Sub kw_ermitteln()
    Dim currentKw As Integer
    Dim endKw As Integer
    Dim saveName As String
    Dim savePath As String
    
    ' 默认用当前系统日期计算周数，如需读取I1单元格日期请替换为：
    ' currentKw = DINKw(Sheets("填写存放日期的工作表名称").Range("I1").Value)
    currentKw = DINKw(Date)
    ' 计算区间结束周，处理跨年边界
    endKw = currentKw + 3
    If endKw > 53 Then
        endKw = endKw - IIf(currentKw = 52, 52, 53)
    End If
    
    ' 拼接文件名，统一用&做字符串连接
    saveName = "Steuerliste_neues_System_KW_" & CStr(currentKw) & "-" & CStr(endKw) & ".xlsx"
    ' 替换为实际的文件保存路径，例如存桌面可写为：savePath = Environ("USERPROFILE") & "\Desktop\"
    savePath = "C:\替换为你的实际存储路径\"
    
    Workbooks.Add
    ' 显式指定xlsx格式，避免弹出兼容格式提示
    ActiveWorkbook.SaveAs Filename:=savePath & saveName, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
End Sub

' DIN标准周数计算逻辑无需修改
Function DINKw(dat As Date) As Integer
    Dim kw As Integer
    kw = Int((dat - DateSerial(Year(dat), 1, 1) + ((Weekday(DateSerial(Year(dat), 1, 1)) + 1) _
    Mod 7) - 3) / 7) + 1
    If kw = 0 Then
        kw = DINKw(DateSerial(Year(dat) - 1, 12, 31))
    ElseIf kw = 53 And (Weekday(DateSerial(Year(dat), 12, 31)) - 1) Mod 7 <= 3 Then
        kw = 1
    End If
    DINKw = kw
End Function

使用说明 #

• 必须将代码中savePath替换为本地实际存在的存储路径，不要使用.../这类缩写路径
• 如果需要读取单元格I1的日期计算周数，必须明确指定单元格所属的工作表对象，避免活动工作表切换导致取值错误
  提示：VBA默认不开启强制变量声明，建议在VBA编辑器的工具-选项中勾选「要求变量声明」，从根源避免未定义变量导致的静默错误

内容的提问来源于stack exchange，提问作者Tarik67TK