excel - VBE中的ReplaceLine方法仅替换部分行

回答this问题，我认为编写一个VBE宏来自动替换看起来像这样的行会很有趣。

DimAll a, b, c, d As Integer

经过

Dim a As Integer, b As Integer, c As Integer, d As Integer

在我的初稿中，我只想修改一个选定的行。建立适当的引用以获取VBE对象模型(请参阅http://www.cpearson.com/excel/vbe.aspx)并进行一些操作后，我想到了:

Function ExpandDim(codeLine As String) As String
    Dim fragments As Variant
    Dim i As Long, n As Long, myType As String
    Dim last As Variant
    Dim expanded As String

    If UCase(codeLine) Like "*DIMALL*AS*" Then
        codeLine = Replace(codeLine, "dimall", "Dim", , , vbTextCompare)
        fragments = Split(codeLine, ",")
        n = UBound(fragments)
        last = Split(Trim(fragments(n)))
        myType = last(UBound(last))
        For i = 0 To n - 1 'excludes last fragment
            expanded = expanded & IIf(i = 0, "", ",") & fragments(i) & " As " & myType
        Next i
        expanded = expanded & IIf(n > 0, ",", "") & fragments(n)
        ExpandDim = expanded
    Else
        ExpandDim = codeLine
    End If
End Function

Sub DimAll()
    Dim myVBE As VBE
    Dim startLine As Long, startCol As Long
    Dim endLine As Long, endCol As Long
    Dim myLine As String
    Set myVBE = Application.VBE
    myVBE.ActiveCodePane.GetSelection startLine, startCol, endLine, endCol
    myLine = myVBE.ActiveCodePane.CodeModule.Lines(startLine, 1)
    Debug.Print ExpandDim(myLine)
    myVBE.ActiveCodePane.CodeModule.ReplaceLine startLine, ExpandDim(myLine)
End Sub

在另一个代码模块中，我有:

Sub test()
    DimAll a, b, c, d As Integer
    Debug.Print TypeName(a)
    Debug.Print TypeName(b)
    Debug.Print TypeName(c)
    Debug.Print TypeName(d)
End Sub

这是很奇怪的部分。当我突出显示以DimAll a开头的行并调用我笨拙的sub DimAll时，在立即窗口中，我看到了

Dim a As Integer, b As Integer, c As Integer, d As Integer

符合预期，但是在代码模块本身中，该行更改为

Dim a, b, c, d As Integer

DimAll已被Dim取代-但该行的其余部分未修改。我怀疑逗号会混淆ReplaceLine方法。关于如何解决此问题的任何想法？

最佳答案

当我与调试器一起运行时，myLine会在两个调用之间更改值。 DimAll在第二次之后变为Dim。

这是因为一旦在codeLine内输入主If条件，就将替换ExpandDim Function的值。

在该函数中创建一个新变量，您应该没事...或将其传递给ByVal，就可以了:

Function ExpandDim(ByVal codeLine As String) As String