ReplaceLine method in VBE only replacing part of line

Question

In response to this question I thought it would be fun to write a VBE macro that would automatically replace lines which look like

DimAll a, b, c, d As Integer

by

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

In my first draft I just want to modify a single selected line. After establishing the appropriate references to get to the VBE object model (see http://www.cpearson.com/excel/vbe.aspx ) and playing around a bit I came up with:

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

In another code module I had:

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

This is the weird part. When I highlight the line which begins DimAll a, and invoke my awkwardly named sub DimAll, in the immediate window I see

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

which is as expected, but in the code module itself the line is changed to

Dim a, b, c, d As Integer

DimAll has been replaced by Dim -- but the rest of the line is unmodified. I suspect that the commas are confusing the ReplaceLine method. Any ideas of how to fix this?

1. Be careful with modifying the ActiveCodePane. I just accidentally modified the code that was doing the modifying... For more on that, I recommend posting on http://codereview.stackexchange.com/ The VBIDE is an obsession of mine. I'd be happy to help you polish this up there. 2. You might want to see [this](https://github.com/rubberduck-vba/Rubberduck/wiki/MultipleDeclarationsInspection) which is part of [this](https://github.com/rubberduck-vba/Rubberduck). (Disclaimer: I'm one of the devs.) — RubberDuck, Jun 22 '15 at 19:59
@RubberDuck This was mostly proof of concept. It definitely needs polishing. For one thing, probably no one writes Dim a,b,c,d As Integer with the intention of declaring a,b,c as Variants. It would make more sense to skip that part about DimAll and just expand all things that look like Dim a,b,c,d As Integer in the entire project. Ultimately, I would like to figure out how to turn it into a VBE add-in with keyboard shortcuts. I'll look at that link — John Coleman, Jun 22 '15 at 20:12
Check out the project. We're always looking for more help. =;)- — RubberDuck, Jun 22 '15 at 20:19

score 4 · Accepted Answer · answered Jun 22 '15 at 19:54

4

When I run with the debugger, myLine changes value between the two calls. The DimAll becomes Dim on the second time through.

This is because you are replacing the value of codeLine once you enter the main If conditional inside the ExpandDim Function.

Create a new variable in that function and you should be fine... or pass it ByVal and you're good:

Function ExpandDim(ByVal codeLine As String) As String

answered Jun 22 '15 at 19:54

Byron Wall

3,970
2
13
29

I see -- it was a simple byRef bug (my ExpandDim function had a side effect). The problem is also fixed if I put the byVal modifier before the argument in that function definition. I have almost no experience scripting the VBE so I suspected a deep misunderstanding of its object model on my part rather than a simple passing semantics problem. Thanks. – John Coleman Jun 22 '15 at 20:02

ReplaceLine method in VBE only replacing part of line

1 Answers1