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.
- You rename
btn_populateList. Now when you click the button it still wants to call
- You rename
listToPopulatebut when you run pre-existing code, it still refers to
inputbox1instead 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
oldName such that it's
oldNameNEW. This was done so I could easily revert the names back using the same procedure with
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