Triggering TransactionScope from Excel VBA?

Question

I have some existing C # code that I would like to open through com interop so that it can be called from Excel VBA. Since I will be doing a lot of nested batch update operations, I need to support transactions and minimize the refactoring required at the C # level, I would like to use the TransactionScope approach:

Implementing an Implicit Transaction Using a Transactional Area
http://msdn.microsoft.com/en-us/library/ms172152.aspx

The TransactionScope class provides an easy way to mark the block of code involved in a transaction without requiring you to interact with the transaction itself. A transaction area can select and manage an external transaction automatically. Due to its ease of use and efficiency, it is recommended that you use the TransactionScope Class when developing a transaction application.

In addition, you do not need to explicitly request resources using the deal. Any System.Transactions resource manager (such as SQL Server 2005) can detect the existence of an external transaction created by the scope and automatically terminating.

My question is: is it possible to initiate a TransactionScope in VBA code (or call the aC # method through COM interaction to instantiate and return a TransactionScope object), and then go on to call various other C # objects through COM Interop, which everyone will automatically participate in the transaction with one root?

Answer 1

I do not see a problem with your approach. You do not have using in VBA, so you have to simulate it

• using an error handler and
• very carefully so as not to leave the method without using TransactionScope in one way or another.

For example, let's say that in your C # application you provide the following methods:

 public TransactionScope BeginTransactionScope() { return new TransactionScope(); } public void CommitTransactionScope(TransactionScope scope) { scope.Complete(); scope.Dispose(); // simulates leaving the using { ... } scope } public void RollbackTransactionScope(TransactionScope scope) { scope.Dispose(); // simulates leaving the using { ... } scope } // Does some work using Connections and maybe nested scopes public void DoSomeWork1() { ... } public void DoSomeWork2() { ... } public void DoSomeWork3() { ... } 

You compile your C # DLL and expose it to Excel VBA through COM interoperability. In VBA, you run the methods as follows:

 Private Sub MySub() On Error Goto MyErrorHandler ... Dim scope As Object Set scope = myCsObject.BeginTransactionScope() ... myCsObject.DoSomeWork1 ... myCsObject.DoSomeWork2 ... myCsObject.DoSomeWork3 ... myCsObject.CommitTransactionScope scope Set scope = Nothing ... Exit Sub MyErrorHandler: If Not (scope Is Nothing) Then myCsObject.RollbackTransactionScope scope End If ' Remainder of your error handler goes here ... End Sub 

Note that any data access that you perform with VBA code (between DoSomeWork calls) is outside the scope of the transaction, because TransactionScope is not compatible with classic ADO or DAO.