VBA中With New MyTestableMacro .Run代码的功能及用法咨询

Great question—this syntax is a bit of a lesser-known shortcut in VBA, and it’s especially handy when you’re working with testable code (like you are with RubberduckVBA). Let’s break down exactly what it does, why it’s useful, and how to get your implementation working.

What This Syntax Actually Does #

This line is a concise way to:

1. Create a new instance of the MyTestableMacro class
2. Call its Run method with the provided stub parameters
3. Automatically clean up the instance once the End With is reached (since no other variable is holding a reference to it)

Without this shortcut, you’d write equivalent code like this:

Dim tempMacro As New MyTestableMacro
tempMacro.Run dataServiceStub, wsServiceStub
Set tempMacro = Nothing ' Explicit cleanup (VBA does this automatically, but it's clear to write)

The With New version just condenses all that into one block—perfect when you only need to call one method on the class and don’t need to keep the instance around.

Why It’s Used with RubberduckVBA #

RubberduckVBA pushes for test-driven development (TDD) and dependency injection, which is exactly what your code snippet is doing:

• dataServiceStub and wsServiceStub are test stubs—fake implementations of your real data/worksheet services, used to isolate the code in MyTestableMacro during testing.
• The With New syntax lets you quickly spin up an instance of your testable macro, pass in the stubs, run the logic, and discard the instance—no extra variable clutter.

Troubleshooting: Why Your Implementation Failed #

If you created the MyTestableMacro class and added a Run method but couldn’t get it to work, you’re likely hitting one of these common issues:

• Your class isn’t publicly accessible: By default, VBA class modules have an Instancing property set to Private, which means you can’t use New on them from a standard module. Fix this: open the class module, go to the Properties window (F4), set Instancing to Public.
• The Run method isn’t public: Make sure your Run sub is declared with Public—if it’s Private, external code (like your standard module) can’t call it.
• Parameter type mismatch: Double-check that the parameters in your Run method match the type of dataServiceStub and wsServiceStub. If you’re using Rubberduck’s interface-based stubs (best practice for testing), your Run method should accept the interface type, not the concrete stub class.

Working Example #

Here’s a minimal, correct setup to match your snippet:

1. Create an interface (for dependency injection, optional but recommended for Rubberduck testing):
   • Add a class module named IDataService, set Instancing to Public, add:

     Public Function GetData() As String
     End Function
     

   • Add another class module named IWorksheetService, set Instancing to Public, add:

     Public Sub WriteData(ByVal content As String)
     End Sub
     

2. Create your stub classes:
   • Add DataServiceStub class, implement IDataService:

     Implements IDataService
     Private Function IDataService_GetData() As String
         IDataService_GetData = "Test data from stub"
     End Function
     

   • Add WorksheetServiceStub class, implement IWorksheetService:

     Implements IWorksheetService
     Private Sub IWorksheetService_WriteData(ByVal content As String)
         Debug.Print "Stub wrote: " & content
     End Sub
     

3. Create MyTestableMacro class:
   • Set Instancing to Public, add:

     Public Sub Run(ByVal dataService As IDataService, ByVal wsService As IWorksheetService)
         Dim data As String
         data = dataService.GetData()
         wsService.WriteData data
     End Sub
     

4. Call it from a standard module:

   Sub TestMacro()
       Dim dataStub As New DataServiceStub
       Dim wsStub As New WorksheetServiceStub
       
       With New MyTestableMacro
           .Run dataStub, wsStub
       End With
   End Sub
   

Run this, and you’ll see the stub output in the Immediate Window (Ctrl+G).

Key Takeaways #

• With New [ClassName] is a VBA shortcut for creating a temporary class instance, using it once, and letting it be cleaned up automatically.
• It’s ideal for testing scenarios (like RubberduckVBA) where you need to quickly execute logic with test stubs without keeping the instance around.
• Always ensure your class is public, your method is public, and parameter types match to avoid runtime errors.

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