Excel VBA部分字符串加粗报错：变量未定义，求代码修正与优化

编译错误原因及修复 #

触发编译错误的核心问题是拼写失误和变量声明不规范，具体修正点如下：

1. 变量拼写错误

   • 代码中多处把Result1误写为Resultl（最后一位是小写L而非数字1），把Range1误写为Rangel，这些未定义的拼写变体直接触发了variable not defined错误。
   • 单元格地址错误：Sheet2.Range("Bl")里的Bl是小写L，应改为B1。

2. 变量声明不规范
   VBA中Dim a, b, c As Range仅会将c声明为Range类型，a和b默认是Variant类型，需逐个明确类型：

   Dim Result1 As Range, Result2 As Range, Result3 As Range
   Dim Range1 As Range, Range2 As Range, Range3 As Range, Range4 As Range, Range5 As Range, Range6 As Range
   

3. 其他语法问题

   • Sheet1.Active是错误写法，激活工作表需用Sheet1.Activate。
   • 字符串拼接时Range2.Value & ")"多了一个空格，应改为Range2.Value & ")"。

修正后的完整代码 #

Option Explicit

Sub BoldPart()
    Dim Result1 As Range, Result2 As Range, Result3 As Range
    Dim Range1 As Range, Range2 As Range, Range3 As Range, Range4 As Range, Range5 As Range, Range6 As Range

    Set Result1 = Sheet1.Range("A4") '结果输出单元格
    Set Result2 = Sheet1.Range("B2") '结果输出单元格
    Set Result3 = Sheet1.Range("A5") '结果输出单元格

    Set Range1 = Sheet2.Range("B1") '需要加粗的内容单元格
    Set Range2 = Sheet2.Range("B2") '需要加粗的内容单元格
    Set Range3 = Sheet2.Range("B3") '需要加粗的内容单元格
    Set Range4 = Sheet2.Range("B4") '需要加粗的内容单元格
    Set Range5 = Sheet2.Range("B5") '需要加粗的内容单元格
    Set Range6 = Sheet2.Range("B6") '需要加粗的内容单元格

    Application.EnableEvents = False
    '生成目标字符串
    Result1.Value = "Accendance/Academic profile of your ward," & Range4.Value & ", a Roll No." & Range5.Value & " has promoted to " & Range1.Value & " canberk "
    Result2.Value = "Subject:: Progress Report - " & Range1.Value & " 2017-18(Session" & Range2.Value & ")"
    Result3.Value = "This is for your info The Issue of Progress Report date is " & Range3.Value

    '为Result1中的指定内容加粗
    With Result1
        .Characters(InStr(Result1.Value, Range1.Value), Len(CStr(Range1.Value))).Font.FontStyle = "Bold"
        .Characters(InStr(Result1.Value, Range4.Value), Len(CStr(Range4.Value))).Font.FontStyle = "Bold"
        .Characters(InStr(Result1.Value, Range5.Value), Len(CStr(Range5.Value))).Font.FontStyle = "Bold"
        '注意：Result1的字符串中未包含Range6的内容，该语句不会生效
        .Characters(InStr(Result1.Value, Range6.Value), Len(CStr(Range6.Value))).Font.FontStyle = "Bold"
    End With

    '为Result2中的指定内容加粗
    With Result2
        .Characters(InStr(Result2.Value, Range1.Value), Len(CStr(Range1.Value))).Font.FontStyle = "Bold"
        .Characters(InStr(Result2.Value, Range2.Value), Len(CStr(Range2.Value))).Font.FontStyle = "Bold"
    End With

    '为Result3中的指定内容加粗
    With Result3
        .Characters(InStr(Result3.Value, Range3.Value), Len(CStr(Range3.Value))).Font.FontStyle = "Bold"
    End With
    
    Application.EnableEvents = True
    Sheet1.Activate
End Sub

需求满足情况 #

你的核心需求是将多个单元格内容插入到指定字符串的特定位置并设置为加粗，修正后的代码基本满足需求，但有一处细节需注意：

• Result1生成的字符串中未包含Range6的内容，因此针对Range6的加粗代码不会产生任何效果，可根据实际需求调整字符串内容或删除该语句。
• 代码通过InStr定位插入内容的位置，再用Characters方法设置加粗，逻辑是成立的——只要插入内容在目标字符串中唯一存在，就能准确加粗对应部分。

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