VBA中如何将现有代码至少前两行替换为WorksheetFunction写法

你原代码前两行的Goto跳转选中、激活单元格写公式的逻辑完全可以替换，用WorksheetFunction实现不需要依赖选中态，稳定性和运行效率都更高。

核心替换逻辑说明 #

• 原前两行的作用是跳转到Handling Units工作表的命名范围HUNUM01（即填充起始单元格B5），写入编号生成公式
• 用WorksheetFunction.Rept可以直接在VBA内存里完成字符串拼接计算，不需要选中单元格、不需要写入工作表公式再触发计算
• 连后续的AutoFill步骤也可以一并优化，不需要模拟手动填充操作，直接给目标区域批量赋值即可

优化后完整代码 #

Sub GenerateHandlingUnitNo()
    Dim wsHU As Worksheet
    Dim lastDataRow As Long
    Dim targetFillRange As Range
    Dim whNo As String, startNo As String, fixedPrefix As String
    Dim i As Long
    Dim currAValue As Variant
    
    ' 直接绑定目标工作表，无需激活跳转
    Set wsHU = ThisWorkbook.Worksheets("Handling Units")
    ' 获取A列最后一行有数据的行号
    lastDataRow = wsHU.Cells(wsHU.Rows.Count, "A").End(xlUp).Row
    Set targetFillRange = wsHU.Range("B5:B" & lastDataRow)
    
    ' 读取命名范围的固定参数值
    whNo = CStr(ThisWorkbook.Names("WHNUMB").RefersToRange.Value)
    startNo = CStr(ThisWorkbook.Names("STARTNUMB").RefersToRange.Value)
    ' 用WorksheetFunction.Rept提前拼接固定前缀，对应原公式的固定拼接部分
    fixedPrefix = whNo & WorksheetFunction.Rept("0", 14 - Len(startNo)) & startNo
    
    ' 批量给目标单元格赋值，无需写入公式、无需自动填充
    For i = 1 To targetFillRange.Rows.Count
        currAValue = targetFillRange.Cells(i, 1).Offset(0, -1).Value
        If IsNumeric(currAValue) Then
            targetFillRange.Cells(i, 1).Value = fixedPrefix & (currAValue - 1)
        Else
            targetFillRange.Cells(i, 1).Value = ""
        End If
    Next i
End Sub

如果你需要保留单元格公式 #

如果不想直接写入计算值，要保留单元格公式方便后续数据变动自动更新，也不需要用Goto、ActiveCell和AutoFill，直接一次性给整个目标区域写入公式即可：

Sub WriteHUNumFormula()
    Dim wsHU As Worksheet
    Dim lastDataRow As Long
    Set wsHU = ThisWorkbook.Worksheets("Handling Units")
    lastDataRow = wsHU.Cells(wsHU.Rows.Count, "A").End(xlUp).Row
    ' 直接给全区域写R1C1公式，一步到位
    wsHU.Range("B5:B" & lastDataRow).FormulaR1C1 = "=IFERROR((WHNUMB&REPT(0,14-LEN(STARTNUMB))&STARTNUMB+(RC[-1]-1)),"""")"
End Sub

注意：原写法依赖ActiveCell、Selection的逻辑非常脆弱，只要代码运行前用户选中了其他单元格、或者工作表处于隐藏状态，代码就会报错，优化后的写法完全避开了这类问题。

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