使用代码删除代码 [英] Using code to delete code

问题描述

我已经对此进行了搜索，但找到了多种解决方案，但没有一个对我有用.我倾向于相信这是因为我在Excel中使用VBA进行编程时所​​掌握的知识和能力有限.如果这是已经在此处发布的问题的重复问题，我深表歉意.

I have googled this and found multiple solutions for this but none of them have worked for me. I am inclined to believe this is because of my limited knowledge and ability when it comes to programming in VBA in Excel. If this is a repeat question of questions already posted here I apologize.

问题:

我有一个在打开Excel Workbook时执行的代码.执行的代码将工作簿另存为新工作簿，并位于单元格中的值名称下.我需要在Excel Workbook打开时执行的代码不要在已创建的新副本上运行.我能想到的最好的方法是删除在Excel Workbook打开时执行的部分或全部代码.我以前见过解决方案，我知道有解决方案，但是我不知道如何在程序中正确实现代码，这样它才能起作用，我什至不知道找到的代码是否可以工作，这就是为什么这里没有提供.这是我到目前为止所拥有的代码，并带有注释，这些注释解释了我在代码中的需求.任何帮助都将是巨大的.

I have a code that executes on open of Excel Workbook. The code that executes saves the workbook as a new workbook under the name of a value located in a cell. I need the code that executes on open of Excel Workbook not to run on the new copy that has been created. The best way that I can think of to go about this would be to have a code that deletes part of or all of the code that executes on open of Excel Workbook. I have seen solutions for this before, I know that a solution exists but I do not know how to properly implement the code into my program so it works, I don't even know if the code I found will work which is why I have not provided it here. Here is the code that I have so far with comments explaining what I need in the code. Any help with this would be huge.

谢谢

Private Sub Workbook_Open()

 'Saves filename as value of C10 

Dim newFile As String, fName As String

fName = Range("C10").Value

newFile = fName & " " & Range("E9").Value

ChDir Range("A8")
ActiveWorkbook.SaveCopyAs Filename:=newFile

'Need code to delete a line of code or all code to prevent the code above from executing when the new Worksheet that has been created is opened.

ActiveWorkbook.Close False
End Sub

推荐答案

好，此代码将删除该事件.您保存了工作簿的副本，所以我的想法是

Well, this code will delete the event. You save a copy of your workbook, so my idea is

1. 你做什么都可以做
2. 删除Workbook_open宏
3. 保存到新工作簿中(宏的该部分不会出现)
4. 请勿在原始工作簿中进行更改，因此Workbook_Open宏将始终是安全的.

1. you do whatever you do
2. Delete the Workbook_open Macro
3. Save to a new workbook (that part of macro won't be)
4. DO NOT SAVE CHANGES IN YOUR ORIGINAL WORKBOOK, so the Workbook_Open macro will be always secure.

无论如何，请先对其进行几次测试以确保其正常工作.用VBA本身更改VBA代码会产生奇怪的东西.

Anyways, test it first several times to make sure it works. Altering VBA code with VBA itself can produce weird things somethimes.

希望这会有所帮助.

所有功劳归于 OZGRIZ

此外，确保您签入Excel选项->受信任的站点->受信任站点的设置->宏设置->检查对Visual Basic编辑器的信任访问权限

Sub DeleteWorkbookEventCode()

''Needs Reference Set To _

    '"Microsoft Visual Basic For Applications Extensibility"

'Tools>References.

'Also, make sure you check in Excel Options->Trusted Sites->Settings of Trusted Sites-> Macros Setting-> Check Trust access to Visual Basic Editor

Dim i As Integer
Dim MyStart, MyEnd As Integer

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

MyStart = 0

    For i = 1 To .CountOfLines Step 1
        If .Lines(i, 1) = "Private Sub Workbook_Open()" Then MyStart = i

        If .Lines(i, 1) = "End Sub" And MyStart > 0 Then
            MyEnd = i
            Exit For
        End If
    Next i

    .DeleteLines MyStart, (MyEnd - MyStart) + 1

End With

End Sub

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