如何将文本框指定为Active.Sheet.Range？VBA报错解决

核心错误原因 #

Range(D3)中的D3未用引号包裹，VBA会将其识别为未定义变量，而非单元格地址，这是触发报错的直接原因。

修正后的完整代码 #

Private Sub cmdTRANSLATE_Click()
    Application.StatusBar = "TRANSLATING THE RECIPE -  TRANSLATING THE RECIPE - TRANSLATING THE RECIPE - TRANSLATING THE RECIPE - TRANSLATING THE RECIPE "
   
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
   
    Dim Rng As Range
    Dim What As Variant
    Dim Repl As Variant
    Dim i As Long
    
    ' 提前指定目标单元格，无需循环内重复设置
    Set Rng = ActiveSheet.Range("D3")
    
    What = Array("(Milk)", "(Celery)", "(Wheat)", "(Sulphites)", "(Crustaceans)", "(Egg)", "(Fish)", "(Soya)", "(Peanuts)", "(Nuts)", "(Mustard)", "(Sesame)", "(Lupin)", "(Molluscs)", "(Egg, Celery)", "(Milk, Egg)")
    Repl = Array("[ÌËßÊÎ]", "[ÖÅËÈÍÀ]", "[ÏØÅÍÈÖÀ]", "[ÑÓËÔÈÒÈ]", "[ÐÀÊÎÎÁÐÀÇÍÈ]", "[ßÉÖÅ]", "[ÐÈÁÀ]", "[ÑÎß]", "[ÔÚÑÒÚÖÈ]", "[ßÄÊÈ]", "[ÃÎÐ×ÈÖÀ]", "[CÓÑÀÌ]", "[ËÓÏÈÍ]", "[ÌÅÊÎÒÅËÈ]", "[ßÉÖÅ, ÖÅËÈÍÀ]", "[ÌËßÊÎ, ËÓÏÈÍ]")
    
    For i = 0 To UBound(What)
        Rng.Replace What:=What(i), Replacement:=Repl(i), MatchCase:=False
    Next i

    ' 恢复Excel默认运行设置
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
    Application.StatusBar = False
End Sub

关键修正细节 #

• 给单元格地址加引号：将Range(D3)改为Range("D3")，明确告诉VBA这是单元格引用
• 优化对象创建：把Set Rng移到循环外，避免重复创建Range对象，提升运行效率
• 修正语法错误：把End Subs改为End Sub，符合VBA语法规范
• 补充环境恢复：添加代码恢复Excel的计算模式、屏幕更新和提示框设置，防止影响后续操作
• 可选优化：建议用具体工作表名称（如ThisWorkbook.Worksheets("翻译表").Range("D3")）替代ActiveSheet，避免因当前工作表切换导致的目标单元格错误

内容的提问来源于stack exchange，提问作者Willy Van Miegroet