无法添加上下边框：Excel VBA生成数据行时格式设置报错

我看了你代码里的边框设置部分，问题出在Borders属性的语法错误上——你把所有边框参数都放在括号里用逗号分隔，这不符合VBA的语法规则，所以才会报参数无效的错误。

错误原因分析 #

你原来的错误代码：

.Borders (xlEdgeTop), LineStyle = xlContinuous, ColorIndex = 0, TintAndShade = 0, Weight = xlThin

VBA中设置边框时，需要针对每个边框类型（比如xlEdgeTop、xlEdgeBottom）单独设置属性，或者通过Range的Borders集合批量设置，不能像你那样把所有参数堆在一起。

修正后的边框设置代码 #

我们可以先定位到当前操作的行（row_ptr对应的行），然后给它添加上下边框。比如用下面的写法：

' 设置当前行的上下边框
With InputWorksheet.Rows(row_ptr).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .Weight = xlThin
End With
With InputWorksheet.Rows(row_ptr).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .Weight = xlThin
End With

或者更简洁的批量写法（如果需要同时设置多个边框）：

With InputWorksheet.Rows(row_ptr)
    ' 设置上边框
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).ColorIndex = 0
    .Borders(xlEdgeTop).Weight = xlThin
    ' 设置下边框
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).ColorIndex = 0
    .Borders(xlEdgeBottom).Weight = xlThin
End With

整合到你的完整代码中 #

把这段边框设置代码放在填充完该行数据之后，row_ptr = row_ptr + 1之前，完整代码如下：

rownbrMA_Inflight = DataSourceWorksheet.Range("C" & Rows.Count).End(xlUp).Row 'Set the Management Action row count
row_ptr = 31 'Set starting row on home page for new table values

For i = 8 To rownbrMA_Inflight 'Not sure of the reason for this
    If DataSourceWorksheet.Range("C" & i).Value = "Open" Then 'Only copy items with status as "Open"
        InputWorksheet.Rows(row_ptr).Insert Shift:=xlDown 'Select the row_ptr and insert a new row with formating from above
        AddStr = "MA_Inflight!" & "$F$" & CStr(i) ' String to be added is the Cell value for the hyperlink
        
        With InputWorksheet ' Set the worksheet
            .Hyperlinks.Add Anchor:=.Range("A" & row_ptr), Address:="", SubAddress:=AddStr, TextToDisplay:=DataSourceWorksheet.Range("O" & i).Value
        End With ' End Hyperlink function
        
        '------------------------------------
        InputWorksheet.Range("B" & row_ptr).Value = DataSourceWorksheet.Range("H" & i).Value ' Set the 6 week due date
        InputWorksheet.Range("C" & row_ptr).Value = DataSourceWorksheet.Range("I" & i).Value ' Set the MA Close date
        InputWorksheet.Range("D" & row_ptr).Value = DataSourceWorksheet.Range("K" & i).Value ' Set the Service Assurance Owner
        
        ' 新增：设置当前行的上下边框
        With InputWorksheet.Rows(row_ptr)
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).ColorIndex = 0
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).ColorIndex = 0
            .Borders(xlEdgeBottom).Weight = xlThin
        End With
        
        row_ptr = row_ptr + 1 ' Last row is row_ptr +1
    End If ' End the set loop
Next i ' Move to next row

这样修改后，每次插入并填充完一行数据，就会自动给该行添加上下边框，不会再报参数无效的错误了。

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