如何优化VBA代码：遍历命名范围或跳过空行写入Excel？

需求背景 #

现有VBA代码可将TextBox58至TextBox66的内容写入工作表「Rabattsatser」的D8:D18区域，但存在以下问题：

• 遇到空文本框时会出错
• 需要跳过D14、D15单元格
• 计划扩展至约150个文本框，无法逐个编写代码，需实现批量处理并跳过指定区域/空行的方案

现有代码 #

Dim RR2 As Integer
For RR2 = 58 To 66
    
    If Controls("TextBox" & RR2).Value = "" Then
        Else
            Worksheets("Rabattsatser").Range("D" & RR2 - 50).Value = Controls("TextBox" & RR2).Value
        End If
        
Next RR2

注：代码中RR2 - 50对应D列行号，如RR2=58时对应D8单元格。

解决方案 #

方案一：跳过指定行+批量处理空文本框 #

通过定义跳过的行数组，循环时先判断目标行是否在跳过列表，再处理非空文本框，同时添加错误处理避免空控件或单元格访问出错。

Dim startTextBox As Integer, endTextBox As Integer
Dim targetRow As Integer
Dim skipRows As Variant
skipRows = Array(14, 15) ' 定义需要跳过的D列行号

' 设置文本框的起始和结束编号（150个的话，58+149=207）
startTextBox = 58
endTextBox = 207

Dim RR2 As Integer
For RR2 = startTextBox To endTextBox
    targetRow = RR2 - 50 ' 计算对应D列行号
    
    ' 先判断是否为跳过行，再处理非空文本框
    If Not IsError(Application.Match(targetRow, skipRows, 0)) Then
        ' 跳过指定行，不执行写入
    ElseIf Controls("TextBox" & RR2).Value <> "" Then
        On Error Resume Next ' 临时屏蔽错误，防止控件不存在或单元格访问失败
        Worksheets("Rabattsatser").Range("D" & targetRow).Value = Controls("TextBox" & RR2).Value
        On Error GoTo 0 ' 恢复默认错误处理
    End If
Next RR2

方案二：使用命名范围（更灵活的扩展方式） #

通过Excel命名范围定义需要写入的目标单元格（自动跳过不需要的行），再按顺序将非空文本框内容填充到命名范围的单元格中。

步骤1：创建命名范围

在Excel中，选中「Rabattsatser」工作表中需要写入的D列单元格（如D8:D13、D16:D18，扩展时直接添加新单元格），然后定义命名范围（比如命名为TargetRanges）。

步骤2：VBA代码实现

Dim startTextBox As Integer, endTextBox As Integer
startTextBox = 58
endTextBox = 207 ' 150个文本框的结束编号

Dim targetCells As Range
Set targetCells = ThisWorkbook.Names("TargetRanges").RefersToRange ' 引用命名范围

Dim txtBox As Control
Dim cellIndex As Integer
cellIndex = 1

' 遍历指定编号的文本框
For RR2 = startTextBox To endTextBox
    Set txtBox = Controls("TextBox" & RR2)
    ' 文本框非空且未超出命名范围单元格数量时写入
    If Not txtBox Is Nothing And txtBox.Value <> "" And cellIndex <= targetCells.Cells.Count Then
        targetCells.Cells(cellIndex).Value = txtBox.Value
        cellIndex = cellIndex + 1 ' 写入成功后移动到下一个目标单元格
    End If
Next RR2

注意事项 #

• 若文本框数量与命名范围单元格数量完全匹配，可去掉cellIndex <= targetCells.Cells.Count的判断
• 扩展文本框数量时，只需修改endTextBox的值，或更新命名范围的单元格，无需大幅修改代码
• 可根据实际需求添加控件存在性判断，避免因控件不存在导致的错误

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