VBA调用指定模块宏并执行至指定代码行后终止运行的实现方法

Got it, let's figure out how to make Module1.Test stop exactly at line 900 when you call it. Here are two solid approaches you can use in VBA:

Approach 1: Dynamically Set a Breakpoint Before Running the Macro #

This method lets you control the stop line without modifying your original Test macro, but it requires enabling access to the VBA project object model.

Step 1: Enable Required Permissions #

First, you need to allow VBA to modify the project structure:

• Open Excel > File > Options > Trust Center > Trust Center Settings > Macro Settings
• Check "Trust access to the VBA project object model" and click OK

Step 2: Use This Code to Call and Stop at Line 900 #

Sub RunTestAndPauseAtLine900()
    Dim vbProject As VBIDE.VBProject
    Dim targetModule As VBIDE.VBComponent
    Dim codeModule As VBIDE.CodeModule
    Const stopLine As Long = 900 ' Set your target line number here
    
    ' Reference the project and module containing your Test macro
    Set vbProject = ThisWorkbook.VBProject
    Set targetModule = vbProject.VBComponents("Module1")
    Set codeModule = targetModule.CodeModule
    
    ' Clear existing breakpoints in Module1 to avoid conflicts
    codeModule.ClearBreakpoints
    
    ' Add a breakpoint at the specified line
    codeModule.AddFromFileBreakpoint stopLine
    
    ' Call the Test macro
    Call Module1.Test
    
    ' Optional: Clear the breakpoint after execution (cleanup)
    codeModule.ClearBreakpoints
End Sub

When you run RunTestAndPauseAtLine900, it'll automatically set a breakpoint at line 900 in Module1, execute Test, and pause exactly when it hits that line.

Approach 2: Add Conditional Stop Logic Directly in Test #

If you don't want to mess with project permissions, you can modify the original Test macro to include a conditional stop. This is simpler and doesn't require extra settings.

Step 1: Modify the Test Macro #

Update your Test macro to accept an optional parameter that triggers the stop:

Sub Test(Optional pauseAtLine900 As Boolean = False)
    ' --- Your existing Test macro code here ---
    
    ' Place this code EXACTLY at or right before line 900
    If pauseAtLine900 Then
        Stop ' This will pause execution immediately
        ' Alternative: Use Debug.Assert False if you want to continue with a click in the debugger
    End If
    
    ' --- The rest of your Test macro code here ---
End Sub

Step 2: Call the Macro with the Stop Trigger #

Now you can call Test and tell it to pause at line 900 like this:

Call Module1.Test(True)

If you want to run Test normally without pausing, just use Call Module1.Test as before.

Quick Notes #

• Approach 1 is great if you can't or don't want to edit the original Test macro.
• Approach 2 is more lightweight and avoids permission issues, but requires modifying your existing macro.

内容的提问来源于stack exchange，提问作者johns90