用vba删除一行excel vba

Question

我现在想知道是否可以删除一行vba而不会弹出错误1004。我不希望用户必须进入vba项目对象模型的设置和信任访问。这可能吗？ 当前的代码（如果不相信自己选择的错误发生）： ActiveWorkbook.Application.VBE.ActiveVBProject.VBComponents("Sheet1").CodeModule.DeleteLines 170, 1

Answer 1

我不希望用户必须进入设置和信任对VBA项目对象模型。这可能吗？

不，这是一项安全功能。使用VBA以编程方式切换此安全设置是不可能的。

每Microsoft（强调）：

此设置是为开发人员和用于故意锁定 或允许来自任何 自动化客户端VBA对象模型编程访问。换句话说，它为 代码编写了一个安全选项，用于自动执行Office程序，而 以编程方式处理Microsoft Visual Basic的 应用程序（VBA）环境和对象模型。这是每个用户 和每个应用程序设置，默认情况下拒绝访问。这种安全选项使未经授权的程序难以构建可能损害最终用户系统的“自我复制”代码。 对于任何 自动化客户端，要能够以编程方式访问VBA对象模型 ，运行代码的用户必须明确授予 访问权限。要打开访问权限，请选中复选框。

Answer 2

你可以尝试像下面的代码，看看它是否适合您的需要：

Option Explicit 

Sub CheckTrustAccess_toVBAProjModule() 

' display Windows Version installed on this PC 
' Win7.  (=6.1, 64bit) 
' Win8  (=6.2, 64bit) 
' Win8.1  (=6.3*) 
' Win10  (=10.0*) 
' 
' MsgBox "Windows Version is: " & getVersion 

Dim TrustAccess_VBAProjModule_Path   As String 
Dim TrustAccess_VBAProjModule    As Integer 

' ----- first check if "Trust Access to the VBA Project module is on ------ 
TrustAccess_VBAProjModule_Path = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Security\AccessVBOM" 

' check if Trust Access to the VBA Projct Module in the Registry is 1 or 0 
TrustAccess_VBAProjModule = CInt(RegKeyRead(TrustAccess_VBAProjModule_Path)) 

If TrustAccess_VBAProjModule = 1 Then 
    ' run your code here ... 


Else ' need to auto-click the "v" by modifying the registry settings 

    ' loop until Trust Access to the VBA Projct Module in the Registry is 1 
    ' it might take time to modify, so use this instead of timers 
    While TrustAccess_VBAProjModule = 0 
     Call RegKeySave(TrustAccess_VBAProjModule_Path, "1") 

     TrustAccess_VBAProjModule = CInt(RegKeyRead(TrustAccess_VBAProjModule_Path)) 
    Wend 
    MsgBox "Initiated VBA settings, please run the Export again", vbInformation 
End If 

End Sub 

'=========================================================== 

Function RegKeyRead(i_RegKey As String) As String 

' reads the value for the registry key i_RegKey 
' if the key cannot be found, the return value is "" 
' Link : http://vba-corner.livejournal.com/3054.html 

Dim myWS As Object 

On Error Resume Next 
'access Windows scripting 
Set myWS = CreateObject("WScript.Shell") 
'read key from registry 
RegKeyRead = myWS.RegRead(i_RegKey) 

End Function 

'=========================================================== 

Sub RegKeySave(i_RegKey As String, i_Value As String, Optional i_Type As String = "REG_DWORD") 

' sets the registry key i_RegKey to the value i_Value with type i_Type 
' if i_Type is omitted, the value will be saved as string 
' if i_RegKey wasn't found, a new registry key will be created 
' Link : http://vba-corner.livejournal.com/3054.html 

Dim myWS As Object 

'access Windows scripting 
Set myWS = CreateObject("WScript.Shell") 
'write registry key 
myWS.RegWrite i_RegKey, i_Value, i_Type 

End Sub