用VBA编码改变VBA编码 [英] Change VBA coding with VBA Coding

问题描述

我需要更改一个常量，引用一个网址。我们的ADI模板有一个新的服务器路径，比下载所有新的模板更容易更新代码。如何更新以下内容：

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"

这是否可能？

推荐答案

首先，您需要添加对Microsoft Visual Basic for Applications可扩展性5.3库的引用。

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

点击工具>>参考>> Microsoft Visual Basic应用程序可扩展性5.3

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

然后你需要打开包含要更新的模块的工作簿。这意味着您无法在用户打开工作簿时进行此更新。

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:

Find方法接受ByRef Long参数。输入后，这些参数指定要搜索的行和列的范围。在输出上，这些值将指向找到的文本。

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 - 编程VBA编辑器

当然，您需要为要更改的每个工作簿执行此操作。这可能需要一些时间。

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

其他资源

• CodeModule Object


• 查找方法


• ReplaceLine方法

• CodeModule Object
• Find Method
• ReplaceLine Method

这篇关于用VBA编码改变VBA编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！