[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

Bulk changing of VBA code

Is there a way of Finding and Replacing multiple lines in VBA code?

For example, I have some code which is used by every form in my database. I now need to change this code, which is several lines long, but using Find and Replace in VBA editor only allows me to search for one line of code.
0
rick_danger
Asked:
rick_danger
  • 6
  • 5
  • 4
  • +2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Must have ... best ever ... I use it daily (hourly) ...

http://www.rickworld.com/products.html#Find%20and%20Replace%209.0

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And ... this is actually free ... IF ... you are only doing VBA, this has killer Find & Replace:


http://www.mztools.com/v3/mztools3.aspx

I use it daily also ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Note ... MZTools ONLY searches VBA code.

Rick's F&R will search the entire DB for code references ... indispensable tool ... both are really.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rick_dangerAuthor Commented:
Thanks DBMX - I downloaded the free version, but can't see how to Find and Replace multiple lines
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
First you do the Find ... , then Enter in your Replace line ... then Replace All

mx find
0
 
rick_dangerAuthor Commented:
With Rick's, if I want to replace this code:
    With ctl
            .BackColor = Forms![Switchboard]![control_back_colour]
            .ForeColor = Forms![Switchboard]!control_fore_colour
            .FontSize = Forms![Switchboard]![font_size]
     End With


with this code
ChangeColors Me

I don't see how I can do it. You don't seem to be able to enter multiple lines.
0
 
hnasrCommented:
No tool is perfect. You are limited to one line string within 255 characters.
0
 
rick_dangerAuthor Commented:
Agreed, but it's the one thing I asked for!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't know of any tool that will allow you to replace blocks of code in that fashion. I've only knows the ones that mx suggests, which can replace a single line with another single line.

You might try building up the line using vbCrLf in your Search. for example:

With ctl vbCrLf .BackColor = Forms![Switchboard]![control_back_colour] vbCrLf .ForeColor etc etc

I doubt that would work, but it's worth a try.

Or you could export all your objects as Text files, use something like Word or another text search tool to find the lines, and the Import them back in. To do that, you can use the SaveAsText and LoadFromText functions. See this posting for code to do that:

http://www.access-programmers.co.uk/forums/archive/index.php/t-99179.html

Or item #4 here:

http://granite.ab.ca/access/corruption/corruptobjects.htm

Be SURE to backup your database before doing this. Changing VBA code and re-importing could be troublesome, to say the least.
0
 
aikimarkCommented:
After starting Word, please try the following:
1. open the code window for a form
2. make sure your view of the code is continuous and not routine isolated
3. Ctrl+A
4. Ctrl+C
5. switch to Word
6. Ctrl+H
in the first textbox, paste:
With ctl^p^w.BackColor = Forms![Switchboard]![control_back_colour]^p^w.ForeColor = Forms![Switchboard]!control_fore_colour^p^w.FontSize = Forms![Switchboard]![font_size]^p^wEnd With^p

In the second textbox, paste:
ChangeColors Me^p

Click the Replace All button.

7. Select all the text in the Word document, copy and paste it into your VB code window and do a compile.

==========
If that works, then it will be a choice of manually replicating that for all objects/modules that have the text to be replaced or trying to automate the process or some hybrid (manual/automated) that can leverage the technology at your disposal.

Since this might be a one-time process, I wouldn't wait for the fully automated process.
0
 
hnasrCommented:
"Agreed, but it's the one thing I asked for! "

It was a good question, so it deserved spending some time on it.
Expect a solution shortly. Testing.
0
 
hnasrCommented:
Try this sample database. Run form ae
Inspect module13.
Click Button Find Then Replace
Inspect Module13 to see changes.

variable naming is different, but for me was required to follow logic and avoid confusion.
str1StartLine  is not a string, but string 1 start line and is of Type Long.
strString as a string.

To add multiple lines, set strString = "lineOfCode" & vbCrLf & "lineOfCode"

Idea:
Find first and last lines of block of code to replace.
Delete block.
Insert new text in place, either 1 line or more .
findThenReplace.accdb
0
 
aikimarkCommented:
If you have a programmable mouse or have installed an applet that allows you to record keyboard actions, you can quickly move through your code, replacing the text.

1. copy the "    ChangeColors Me" text to your clipboard
2. In the code window issue a find for the first line "With ctl" within the entire database project (not module level and not procedure level)
3. if the found string is the start of what needs to be replaced, play the macro that will do the following:
Home
Shift+(Down+Down+Down+Down+Down)
Ctrl+V

4. F3 to repeat the find command and replay the macro as needed.

step 3 Note: The Shift key is to be held down during the five down arrow key presses.
0
 
rick_dangerAuthor Commented:
hnasr
I think I understand what you are trying to achieve, but you seem to be assuming that the string will be in an Access module. But I need it to go through the VBA code for each of the forms, and I don't think your code does that.

I am grateful for your effort though, so if it isn't much more difficult could you please get your code to do that
0
 
aikimarkCommented:
@rick_danger

Did you try either of my suggestions?
0
 
rick_dangerAuthor Commented:
aikimark
I did not try your suggestion as it seems to operate on a form-by-form basis, and I need it to go through all of the forms automatically. If I have misunderstood, then please give me some more guidance.

Thanks
0
 
hnasrCommented:
rick_danger,

You are asking for a complete project. Each question should concentrate on one issue.

My comment answered the major issue, "I now need to change this code, which is several lines long", Some effort from your side is required, looping through modules and form modules and execute the code.

If more help is required in that, then I suggest grading and closing this question, open a new question asking for the extra issues. You need to upload the latest solution, so experts can modify.
0
 
aikimarkCommented:
create a module named CodeChangeExclude

Option Explicit

Public Sub ChangeCode()
    Dim strCode As String
    'Dim strLines() As String
    Dim lngLineCount As Long
    Dim varCM As Variant
    Dim colVBComps As New Collection
    Const cFindWhat As String = "    With Me.C001" & vbCrLf & "        .BackColor = vbYellow" & vbCrLf & "    End With"
    Const cReplaceWith As String = "    SetYellow C001"
    
    For Each varCM In Application.VBE.ActiveVBProject.VBComponents
        If varCM.Name = "CodeChangeExclude" Then
        Else
            colVBComps.Add varCM.Name
        End If
    Next
    
    For Each varCM In colVBComps
        
        lngLineCount = Application.VBE.ActiveVBProject.VBComponents(varCM).CodeModule.CountOfLines
        strCode = Application.VBE.ActiveVBProject.VBComponents(varCM).CodeModule.Lines(1, lngLineCount)
        If InStr(strCode, cFindWhat) <> 0 Then
            strCode = Replace(strCode, cFindWhat, cReplaceWith)
            Application.VBE.ActiveVBProject.VBComponents(varCM).CodeModule.DeleteLines 1, lngLineCount
            Application.VBE.ActiveVBProject.VBComponents(varCM).CodeModule.AddFromString strCode
        End If
    
    Next
End Sub

Open in new window


Change the cFindWhat string expression to your multi-line string

Run the ChangeCode code

Check the results in the affected components (compile the database and look at the code)

Close the database, saving everything.
0
 
rick_dangerAuthor Commented:
hnasr
Whilst I appreciate your help, and am impressed by your solution, my question does state that I need it to change the code for every form. So I don't agree that your code answered the major issue.
0
 
aikimarkCommented:
I failed to instruct you that the ChangeCode() routine should be placed in the CodeChangeExclude module.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now