Renaming form controls and underlying code

Question

I was working on adjusting some variable naming on an Excel project and ran into an issue that the MSForm control names needed to be updated. When you change the properties of the control on the form, the underlying code for the form doesn't seem to update accordingly.

Examples:

1. You rename btn_button1 to btn_populateList. Now when you click the button it still wants to call btn_button1_Click() rather than btn_populateList_Click().
2. You rename inputbox1 to listToPopulate but when you run pre-existing code, it still refers to inputbox1 instead of the new name of the control.

Anyhow, I've always wanted to mess around to get VBA to do stuff in the VBE, so this was my chance!

The macro finds the target project and target form and then updates the control names and appends the string NEW to oldName such that it's oldNameNEW. This was done so I could easily revert the names back using the same procedure with left(len()-3).

Ideally, the array of new names would be populated by the user with descriptive names and would be populated outside of the For loop, obviously.

I'm sure there's some clever refactoring that can be done, but I wasn't sure if I'd break it.

Also, I'm worried I misunderstand how Forms call their macros given it's not designed the same way as controls on sheets.

Public Sub FindReplaceInEntireModule()
 Const TARGET_ADDIN As String = "bUTLAddIn"
 Const TARGET_FORM As String = "form_chtGrid"
 Dim targetProject As VBIDE.VBProject
 Dim targetModule As VBIDE.VBComponent
 Dim targetCode As VBIDE.CodeModule
 Dim controlIndex As Long
 Set targetProject = Application.VBE.VBProjects(TARGET_ADDIN)
 Set targetModule = targetProject.VBComponents(TARGET_FORM)
 Dim targetForm As Object
 Set targetForm = targetModule.Designer
 Dim numberOfControls As Long
 numberOfControls = targetForm.Controls.count
 Dim newControlNames() As String
 ReDim newControlNames(1 To numberOfControls)
 Dim oldControlNames() As String
 ReDim oldControlNames(1 To numberOfControls)
 For controlIndex = 1 To numberOfControls
 oldControlNames(controlIndex) = targetForm.Controls(controlIndex).name
 newControlNames(controlIndex) = oldControlNames(controlIndex) & "NEW"
 targetForm.Controls(controlIndex).name = newControlNames(controlIndex)
 Next
 Set targetCode = targetModule.CodeModule
 Dim findWhat As String
 Dim replaceWith As String
 Dim numberOfLines As Long
 numberOfLines = targetCode.CountOfLines
 Dim lineNumber As Long
 Dim lineText As String
 For controlIndex = 1 To numberOfControls
 findWhat = oldControlNames(controlIndex)
 replaceWith = newControlNames(controlIndex)
 For lineNumber = 1 To numberOfLines
 lineText = targetCode.Lines(lineNumber, 1)
 If InStr(1, lineText, findWhat, vbTextCompare) > 0 Then
 targetCode.ReplaceLine lineNumber, Replace(lineText, findWhat, replaceWith, , , vbTextCompare)
 End If
 Next
 Next
End Sub

• Mathieu Guindon: Event handlers (be them in forms or elsewhere) are always named [objectname_eventname], so for Button1.Click you'll have a handler named Button1_Click; if you want to rename the button to OkButton you'll need to rename the handler to OkButton_Click

Answer 1

You've basically implemented (parts of) a rename refactoring.

The problem is that the VBIDE API only gives you strings to work with, so you essentially have a "smart find & replace", but it's still a find & replace.

A LOT of things can go wrong when processing code without a symbol table.

Imagine a control named MyButton that you want to rename to SomeButton. Then you have this code in the module:

Dim MyButtonForeColor As Long
MyButtonForeColor = vbBlack

Your code will rename MyButtonForeColor too, because it contains MyButton - but did you mean to? In this case, probably.

UserForm controls are Public, so you can have code outside the form that does this:

With New UserForm1
 .Show vbModal
 ActiveSheet.Cells(1, 1) = .SomeTextBox.Text
End With

And that code will break when you rename SomeTextBox to anything else.

Or, you could have conflicting but unambiguous names in different scopes, that a simple find & replace will break:

UserForm1.SomeButton.Caption = UserForm2.SomeButton.Caption

Naming is hard. Renaming is even harder.

---

Having worked on Rubberduck for over two years, I can assure you that there is absolutely no way to implement any kind of rename refactoring without a symbol table - and that means lexing, parsing and resolving the VBA code you want to refactor.

Ctrl+H isn't a refactoring tool. Rubberduck is. I don't mean to sound like an ad, but your code needs to know exactly which code across the entire project is referencing the control you're renaming - and that simply isn't possible to implement reliably in VBA. Heck, Rubberduck has a proper parser and resolver, and still struggles with a number of edge cases.

I give you this code to play with - it's EVIL beyond words, I know... but it's 100% legal VBA code, and Rubberduck sorts it all out:

'Project Name = MyProject
'Module Name = MyModule
Option Explicit
Private MyVar As MyProject
Private MyVar1 As MyModule
Private Type MyProject
 MyModule As String
 MySub As String
End Type
Private Type MyModule
 MyVar As String
 String As MyProject
End Type
Private Type MySub
 MyVar As MyModule
 MyVar1 As MyProject
End Type
Private Type MyVar
 MyProject As MyModule
End Type
Sub MySub()
 Dim MyProject As MyProject
 Dim MySub As MySub
 Dim MyVar As MyVar
 MyVar.MyProject.MyVar = "Smith"
 MySub.MyVar1.MySub = MyProject.MySub
 'My brain hurts....
End Sub

Your strategy (I know you're only looking at controls, but the principle is the same) will fail to correctly rename MySub here (whichever you pick).

Your code looks great though - good naming, clean code, nice spacing, indentation, casing, a bit large for a single procedure though, but as I said I don't think any of it is relevant, since there's no way any identifier-renaming VBA macro can work [in all cases] simply by looking at context-less strings.

• Raystafarian: ugh. I always do manual find & replace with Find Whole Word Only and Match Case enabled and completely forgot those aren't default.
• Mathieu Guindon: @Raystafarian but even Find whole word only and Match case won't help you deal with mixed scopes.
• Raystafarian: Definitely. I meant only in terms of your first point. It's doomed overall regardless. I still had fun writing it though!
• Mathieu Guindon: @Raystafarian notice one of the MyModule members is named String, which is legal for a UDT member. "Doomed" is a correct assessment ;-)
• ThunderFrame: Dang, who wrote that Evil Code....

Answer 2

Referring to the current project

I know you probably meant for this code to be generic so that it works across any project, but it would seem that the code is supposed to sit inside your addin.

Const TARGET_ADDIN As String = "bUTLAddIn"
Set targetProject = Application.VBE.VBProjects(TARGET_ADDIN)

If the name of your add-in changes, you'd need to update the constant, but VBIDE allows you to use:

Set targetProject = ThisWorkbook.VBProject

Dim & ReDim

You can use ReDim without using Dim, and still get a strongly typed array:

ReDim newControlNames(1 To numberOfControls) As String
ReDim oldControlNames(1 To numberOfControls) As String

You could also omit the numberOfControls variable, and just use the count of the controls.

But you might consider using a 2D array, or maybe a dictionary to keep track of the names.

Referring to the Form Designer/Controls

You use Object as the type of the Form, when, in this case, it will always actually be a UserForm, but you don't actually ever need the form... you need the Controls. And the Controls are enumerable so you can use 'For..Each`.

'Dim targetForm As UserForm 'Early bound type
'Set targetForm = targetModule.Designer
Dim targetControls As Controls
Set targetControls = targetModule.Designer.Controls
ReDim newControlNames(1 To targetControls.Count) As String
ReDim oldControlNames(1 To targetControls.Count) As String
Dim targetControl As Control
For Each targetControl In targetControls
 controlIndex = controlIndex + 1
 oldControlNames(controlIndex) = targetControl.name
 newControlNames(controlIndex) = oldControlNames(controlIndex) & "NEW"
 targetControl.name = newControlNames(controlIndex)
Next targetControl

• Raystafarian: Thanks for the designer information, I was struggling a bit with that as I've never used the .designer property before.