|
发表于 2013-7-23 13:53
|
显示全部楼层
本楼为最佳答案
- Sub 清除代码和模块()
- '开启VBA模型访问信任
- Call TrustVBA
-
- '清除指定工作簿的代码和模块
- Call clearModule(ThisWorkbook.Name)
- End Sub
- Sub clearModule(strWorkbook As String)
- Dim vbe As Object
- Dim vbc As Object
- Dim wb As Workbook
-
- On Error Resume Next
-
- Set wb = Workbooks(strWorkbook)
-
- If wb Is Nothing Then
- MsgBox "找到不指定的工作簿窗口,请确认是否有打开"
- Exit Sub
- End If
-
- If wb.HasVBProject Then
- '检测是否有保护
- If wb.VBProject.Protection Then
- MsgBox strWorkbook & " 有VBA密码保护,请先取消密码保护", vbCritical + vbokok
- Set wb = Nothing
- Exit Sub
- End If
-
- Set vbe = wb.VBProject.VBComponents
-
- For Each vbc In vbe
- Select Case vbc.Type
- Case 1, 2, 3
- vbe.Remove vbc
- Case 100
- With vbc.CodeModule
- .DeleteLines 1, .CountOfLines
- End With
- End Select
- Next
- End If
- End Sub
- Function TrustVBA(Optional ByVal KeyValue = 1)
- Dim strKey1 As String, strKey2 As String, strKey3 As String, strKey4 As String
- Dim KeyValue1, KeyValue2
- Dim strVersion As String
- On Error Resume Next
- strVersion = Application.Version
- strKey1 = "HKEY_CURRENT_USER\Software\Microsoft\Office" & strVersion & "\Excel\Security\AccessVBOM"
- strKey2 = "HKEY_CURRENT_USER\Software\Microsoft\Office" & strVersion & "\Excel\Security\Level"
- strKey3 = "HKEY_LOCAL_MACHINE\Software\Microsoft\Office" & strVersion & "\Excel\Security\AccessVBOM"
- strKey4 = "HKEY_LOCAL_MACHINE\Software\Microsoft\Office" & strVersion & "\Excel\Security\Level"
- 'AccessVBOM 允许访问VBA对象
- Call WriteReg(strKey1, KeyValue, "REG_DWORD")
- Call WriteReg(strKey2, KeyValue, "REG_DWORD")
- Call WriteReg(strKey3, KeyValue, "REG_DWORD")
- Call WriteReg(strKey4, KeyValue, "REG_DWORD")
- End Function
- Sub WriteReg(strkey As String, Value As Variant, ValueType As String)
- Dim objWshell As Object
- Set objWshell = CreateObject("WScript.Shell")
- If ValueType = "" Then
- objWshell.RegWrite strkey, Value
- Else
- objWshell.RegWrite strkey, Value, ValueType
- End If
- Set objWshell = Nothing
- End Sub
复制代码 |
|