Change VBA coding with VBA Coding

Question

I need to change a constant, that references a web address. Our ADI templates have a new server path and it would be easier to update the code than download all new templates. How can I update the below:

Const SERVLET_PATH = "http://webaddress.com"

to

Const SERVLET_PATH = "http://webaddress1.com"

Is this even possible?

? elaborate please? do you have access to the code? do you know roughly how it works? you should be able to just change the address just as you have done in your question — , Oct 22 '14 at 14:20
I can do that, but I want to create a workbook with a push button macro that updates the server path, when they have the old workbooks open. I need a tool that's user friendly and simple. Also, I'd like to learn how to do this because I know I'm going to run into this issue again. I'm deploying Oracle and we're going to have multiple server updates, or so I'm told. — Josh Johnson, Oct 22 '14 at 14:26
that sounds like a completely different way to broad to answer and possibly off-topic for SO question.. sorry — , Oct 22 '14 at 14:31
If it changes, it's not constant, so don't make it a constant. You should store the value in a configuration file or the registry. Then you can build a UI to change the value. — Dick Kusleika, Oct 22 '14 at 15:37
OP is asking how to update "legacy" code in existing workbooks, to correct for a change in an otherwise "fixed" URL. Seems like the VBE examples here would be what they need: http://www.cpearson.com/excel/vbe.aspx Note that if you're asking end-users to do this they must check the option Trust Center >> MacroSettings >> "Trust access to the VBA project object model" — Tim Williams, Oct 22 '14 at 16:44
I disagree. Programmers should change constants, not users. If you want to update legacy code, then get rid of the constants and do it right. IMO, of course. — Dick Kusleika, Oct 22 '14 at 18:01
I'm dealing with journal entry workbooks created by Oracle. I'm trying to update their constants as easily as possible. Typically I'm dealing with people that have never even heard of VBA or opened a VB Editor. Tim, I really appreciate you pointing me to the link. I'm trying to make sense of it. Sorry for the duplicate question. — Josh Johnson, Oct 22 '14 at 18:25
@DickKusleika - we're in agreement as far as it's best to work to avoid avoid this situation in the first place. However, the OP has a problem they need to fix, and does not seem to be an "advanced" VBA user, so a pragmatic approach might be sufficient in this case. As you note however, it is not "best practice". — Tim Williams, Oct 22 '14 at 19:44

score 4 · Accepted Answer · edited May 08 '18 at 21:49

First, you need to add a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library.

Click Tools>>References>> Microsoft Visual Basic for Applications Extensibility 5.3

vba project references

Then you need to open the workbook containing the module you want to update. This means that you cannot make this update while users have the workbook open.

Workbook.Open filePath

Next, you'll need to loop through each code module in the workbook, looking for the constant you're looking to change.

Sub replaceConstant()
    Dim project As VBIDE.VBProject
    For Each project In Application.VBE.VBProjects

        Dim codeMod As VBIDE.CodeModule
        Dim component As VBIDE.VBComponent

        For Each component In project.VBComponents

            If component.Name <> "TheVeryUniqueNameOfTheCodeModuleWhereThisCodeResides" Then

                Set codeMod = component.CodeModule

                Dim startline As Long
                startline = 1 'find takes startline in byref and uses it as an output parameter.

                codeMod.Find Target:="Const SERVLET_PATH = ""http://webaddress.com""", _
                    startline:=startline, startcolumn:=1, endline:=codeMod.CountOfLines, endcolumn:=1

                codeMod.ReplaceLine startline, "Const SERVLET_PATH = ""http://webaddress1.com"""


            End If
        Next component

    Next project
End Sub

The above code works because:

The Find method accepts ByRef Long parameters. Upon input, these parameters specify the range of lines and column to search. On output, these values will point to the found text.

CPearson.com - Programming the VBA Editor

Of course, you'll need to do this for every workbook you want to change. This could take some time.

Further Resources:

Change VBA coding with VBA Coding

1 Answers1

Linked