Here is another entry on the VBA RubberDuck Refactoring menu:

What exactly does this mean?

Initially, I was thinking the scope was a function in a module, and it would move a function closer to a function call made. I was just DEAD WRONG!

It’s actually something to use to move your variable declaration closer to it’s usage in the same module scope.

Given this function:

Private Function TestFunction(Param2 As String, Param1 As String, Optional Param4, Optional Param3) As String
    Dim TestVar As String
    Debug.Print Param1
    Debug.Print Param2
    Debug.Print Param3
    Debug.Print Param4
    Debug.Print TestVar
    TestFunction = Param1 & Param2 & Param3 & Param4
End Function

Notice I declared the TestVar at the beginning of the function, and I placed a Debug.Print TestVar in order to use the variable at the end of the function. I am expecting after right clicking on the TestVar declaration and choosing the Refactor > Move Closer To Usage option, the Dim statement will be moved just above the line using it. Here’s the code after using this option:

Private Function TestFunction(Param2 As String, Param1 As String, Optional Param4, Optional Param3) As String
    Debug.Print Param1
    Debug.Print Param2
    Debug.Print Param3
    Debug.Print Param4
    Dim TestVar As String
    Debug.Print TestVar
    TestFunction = Param1 & Param2 & Param3 & Param4
End Function

The Move Closer To Usage did exactly what I expected it to do after I understood the scope it was using.

How about if I have multiple variables declared on the line? I’ll add another variable and another debug statement and see how it handles it:

Private Function TestFunction(Param2 As String, Param1 As String, Optional Param4, Optional Param3) As String
    Dim TestVar As String, AnotherVar As String
    Debug.Print AnotherVar
    Debug.Print Param1
    Debug.Print Param2
    Debug.Print Param3
    Debug.Print Param4
    Debug.Print TestVar
    TestFunction = Param1 & Param2 & Param3 & Param4
End Function

It’s important to note, that each time you change the code, you should refresh RubberDuck or it will make most of the Refactor methods be disabled. After I click the “Refresh” button on the RubberDuck toolbar, I can right click on TestVar again and get the Refactor > Move Closer To Usage option highlighted again. And with this new code, it works perfectly and moves only the variable I selected down toward the point it’s used:

Private Function TestFunction(Param2 As String, Param1 As String, Optional Param4, Optional Param3) As String
    Dim AnotherVar As String
    Debug.Print AnotherVar
    Debug.Print Param1
    Debug.Print Param2
    Debug.Print Param3
    Debug.Print Param4
    Dim TestVar As String
    Debug.Print TestVar
    TestFunction = Param1 & Param2 & Param3 & Param4
End Function

Very nice, it separated the declarations and moved just the one I wanted.

Although I prefer declarations to be at the beginning of the method, this actually helps with another refactoring ability to extract code to another method. If you move all the variables in a particular section down next to that section, you can select the variable declarations along with the code to extract it all. Although as I was just playing with it, it does try to extract the variables along with the method. There are some different behaviors depending on how you do that.

For example, if you have multiple variables on the same line, Extract Method will extract the Dim line twice which produces an error if we try to compile it. It also will leave the original line in the parent method with the declarations. I suppose this is probably good in case you are using the variables elsewhere, but if you are doing that, you probably would want to pass them to the new method by reference to actually keep the same behavior.