简 介：如何在模块中创建、修改、查找、替换代码
正 文：
第一步：在一个新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
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