如何在模块中创建、修改、查找、替换代码 -张志专栏

第一步：在一个新mdb文件中，手动建立一模块，命名为：Create Code，复制下面的代码到模块中：
Sub TestOpenDatabase()
    Dim DB As DAO.Database
    Set DB = CurrentDb
    MsgBox "The Database " & DB.Name & " opened successfully!"
    DB.Close
End Sub

第二步：手动建立另一模块，命名为：CodeMaker，复制下面的代码到模块中：
注意：必须引用DAO3.6
Option Explicit
Dim MyModule As Module

Sub MakeCode()

   Dim strIndent As String, strText As String

   ' Create 4 spaces for code indent.
   strIndent = "    "

   ' Build a string variable with the code to be written
   ' to the new module.
   strText = "Sub TestOpenDatabase()" & vbCrLf
   strText = strText & strIndent & "Dim DB As DAO.Database" & vbCrLf
   strText = strText & strIndent & "Set DB = CurrentDB" & vbCrLf
   strText = strText & strIndent & "MsgBox ""The Database "" & " & _
      "DB.Name & "
   strText = strText & strIndent & strIndent & """ opened " & _
      "successfully!""" & vbCrLf
   strText = strText & strIndent & "DB.Close" & vbCrLf
   strText = strText & "End Sub"

   ' Create a new Module.
   Application.RunCommand acCmdNewObjectModule

   ' Set MyModule to be the new Module Object.
    Set MyModule = Application.Modules.Item(Application.Modules.Count - 1)
   ' Insert the code string into the new module.
   MyModule.InsertText strText

   ' Save, close, and rename the new Module as "Created Code."

   DoCmd.Save acModule, MyModule

   DoCmd.Close acModule, MyModule, acSaveYes

   DoCmd.Rename "Created Code", acModule, MyModule

End Sub

第三步：保存模块代码

第四步：运行MakeCode子程序，将创建一个新模块Created Code,其代码与第一步的Create Code模块是一样的.

以上实现的是创建一个已有的模块。

同理，用下面的三个子程序实现搜索、替换、修改模块代码：
Sub SearchCode()
'搜索模块中的代码
Dim StartLine As Long, StartColumn As Long
Dim EndLine As Long, EndColumn As Long

' Open the Module you want to modify.
DoCmd.OpenModule "Created Code"

' Set the Created Code Modules as the Object.
Set MyModule = Application.Modules("Created Code")

' Search for string "DB.Close".
If MyModule.Find("DB.Close", StartLine, StartColumn, _
EndLine, EndColumn) Then

     ' If string is found, insert new line of code using the same
     ' column indent.
     MyModule.InsertLines StartLine + 1, _
        String(StartColumn - 1, " ") & "Set DB = Nothing"
Else
     MsgBox "Text not found."
End If

' Save and close the module.
DoCmd.Save acModule, MyModule
DoCmd.Close acModule, MyModule

End Sub

Sub ReplaceCode()
'替换模块中的代码
Dim StartLine As Long, StartColumn As Long
Dim EndLine As Long, EndColumn As Long
' Open the Module you want to modify.
DoCmd.OpenModule "Created Code"

' Set the Created Code Modules as the Object.
Set MyModule = Application.Modules("Created Code")

' Search for string "Set DB =".
If MyModule.Find("Set DB =", StartLine, StartColumn, EndLine, _
   EndColumn) Then

' If string is found, insert new line of code using the same
' column indent.
MyModule.ReplaceLine StartLine, String(StartColumn - 1, " ") & _
     "Set DB = DBEngine.OpenDatabase(""C:\Program Files\" & _
     "Microsoft Office\"" & _" _
     & vbCrLf & "        ""Office\Samples\Inventry.mdb"")"

Else
MsgBox "Text not found."
End If

' Save and close the module.
DoCmd.Save acModule, MyModule
DoCmd.Close acModule, MyModule, acSaveYes

End Sub

此示例使用Find方法来查找字符串"Inventry.mdb"，并将其替换为"Northwind.mdb"。

Sub ModifyCode()

'修改模块中的代码
   Dim StartLine As Long, StartColumn As Long
   Dim EndLine As Long, EndColumn As Long
   Dim strLine As String, strNewLine As String
   Dim intChr As Integer, intBefore As Integer, intAfter As Integer
   Dim strLeft As String, strRight As String
   Dim strSearchText As String, strNewText

' The string you are searching for is:
strSearchText = "Inventry.mdb"

' The replacement string is:
strNewText = "Northwind.mdb"

' Open the Module you want to modify.
DoCmd.OpenModule "Created Code"

' Set the Created Code Modules as the Object.
Set MyModule = Application.Modules("Created Code")

   ' Search for string.
   If MyModule.Find(strSearchText, StartLine, StartColumn, EndLine, _
      EndColumn) Then

' Store text of line containing string.
strLine = MyModule.Lines(StartLine, Abs(EndLine - StartLine) + 1)

' Determine length of line.
intChr = Len(strLine)

' Determine number of characters preceding search text.
intBefore = StartColumn - 1

' Determine number of characters following search text.
intAfter = intChr - CInt(EndColumn - 1)

' Store characters to left of search text.
strLeft = Left$(strLine, intBefore)

' Store characters to right of search text.
strRight = Right$(strLine, intAfter)

' Construct string with replacement text.
strNewLine = strLeft & strNewText & strRight

' Replace the original line.
MyModule.ReplaceLine StartLine, strNewLine

   Else
      MsgBox "Text not found."
   End If

   ' Save and close the module.
   DoCmd.Save acModule, MyModule
   DoCmd.Close acModule, MyModule, acSaveYes

End Sub