时间戳

首页 > 解决方案 > 此时无法进入中断模式，同时以编程方式将代码添加到工作表

excel - 此时无法进入中断模式，同时以编程方式将代码添加到工作表

问题描述

我正在使用 VBA 工具，我正在尝试以编程方式将代码添加到工作表中，这是我的代码，

Public Function Add_NodePairingCode(ByRef wb As Workbook)
    Dim xPro1 As VBProject
    Dim xCom1 As VBComponent
    Dim xMod1 As CodeModule
    Dim xLine As Long
    Sheets("Node Pairing").Activate
    Sheets("Node Pairing").Unprotect
    With wb
        Set xPro1 = wb.VBProject
        Set xCom1 = xPro1.VBComponents(ActiveSheet.CodeName)
        Set xMod1 = xCom1.CodeModule
        With xMod1
            xLine = .CreateEventProc("SelectionChange", "WorkSheet") 'Getting error at this line
            xLine = xLine + 1
            .InsertLines xLine, "   Application.DisplayAlerts = False "
            xLine = xLine + 1
            .InsertLines xLine, "   Dim KeyCells As Range "
            xLine = xLine + 1
            .InsertLines xLine, "   Dim WS_Count As Integer"
            xLine = xLine + 1
            .InsertLines xLine, "   Dim I As Integer"
            xLine = xLine + 1
            .InsertLines xLine, "   Dim lnRow As Long, lnCol As Long"
            xLine = xLine + 1
            .InsertLines xLine, "   lnRow = 2 "
            xLine = xLine + 1
            .InsertLines xLine, "   WS_Count = ActiveWorkbook.Worksheets.Count"
             xLine = xLine + 1
            .InsertLines xLine, "   lnCol = Sheets(""Node Pairing"").Cells(lnRow, 1).EntireRow.Find(What:=""Use For Mac"", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column"
            xLine = xLine + 1
            .InsertLines xLine, "   Set KeyCells = Range(Cells(2, lnCol), Cells(2, lnCol).End(xlDown))"
            xLine = xLine + 1
            .InsertLines xLine, "   If Not Application.Intersect(KeyCells, Range(Target.Address)) _"
             xLine = xLine + 1
            .InsertLines xLine, "       Is Nothing Then"
            xLine = xLine + 1
            .InsertLines xLine, "       For I = 1 To WS_Count"
            xLine = xLine + 1
            .InsertLines xLine, "           If ActiveWorkbook.Worksheets(I).Name = ""Mac Table"" Then"
            xLine = xLine + 1

            .InsertLines xLine, "               If MsgBox(""Changing Use For Mac flag, will delete Mac Tables, Do You Want to Continue?"", vbYesNo) = vbYes Then "
            xLine = xLine + 1
            .InsertLines xLine, "                   Sheets(""Mac Table"").Delete"
            xLine = xLine + 1
            .InsertLines xLine, "                   Exit Sub"
            xLine = xLine + 1
            .InsertLines xLine, "               Else"
            xLine = xLine + 1
            .InsertLines xLine, "                   Exit Sub"
            xLine = xLine + 1
            .InsertLines xLine, "               End If"
            xLine = xLine + 1
            .InsertLines xLine, "           End If"
            xLine = xLine + 1
            .InsertLines xLine, "       Next I"
            xLine = xLine + 1
            .InsertLines xLine, "   End If"
            xLine = xLine + 1
            .InsertLines xLine, "   Application.DisplayAlerts = True "
        End With
    End With
End Function

我没有明白我做错了什么，我尝试了所有方法，例如公开函数并执行它，我查看了stackoverflow的解决方案，我可以找到相关的问题，但是找不到解决方案。任何人都可以帮助我

标签： excelvba

解决方案

我经历了所有的可能性，我发现了错误发生的原因，原因是，

当我调用这个特定的函数时，我的 excel 工作表正在计算背景单元格中的一些值（excel 公式），同时这个函数试图在编译模式下自定义工作表代码，因为这个原因我的工具给出了VBA错误Can't Enter Break mode at this time

我发现解决方案是：我在主工作表上添加了单独的按钮，并向该按钮添加了相同的代码（功能）。现在我可以完美地运行我的工具而不会出现任何错误，

希望这会对某人有所帮助:)

推荐阅读