View Single Post
Old 04-13-2004, 07:57 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848

One last caveat is that if you are using event handling via 'WithEvents' variables, things get even more complicated. The reason is that Marshal.ReleaseComObject() and Marshal.FinalReleaseComObject() will not release a COM object that is being referenced via a 'WithEvents' variable. A procedure to handle this is as follows:
  1. Within your Cleanup section, use a temporary variable and set it to the WithEvents variable.
  2. Then set the WithEvents variable to Nothing.
  3. Lastly, call Marshal.ReleaseComObject() or Marshal.FinalReleaseComObject() on the temporary variable.
We can combine these approaches into a single generic routine as follows:
Shared Sub FinalReleaseAnyComObject(Of TComType)(ByRef rcw As TComType) Dim tempVar As TComType = rcw rcw = Nothing Marshal.FinalReleaseComObject(tempVar) End Function
And with it, our cleanup routine can now be called as follows:
' Cleanup: GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() FinalReleaseAnyComObject(oWS) oWB.Close(SaveChanges:=False) FinalReleaseAnyComObject(oWB) oApp.Quit() FinalReleaseAnyComObject(oApp) End Sub
The above looks pretty much the same, except that now we are calling our own custom 'FinalReleaseAnyComObject()' method, which can release COM objects declared 'WithEvents' as well as standard COM objects. Another approach would be to avoid using 'WithEvents' entirely and to instead make use of VB.NET's AddHandler and RemoveHandler keywords for event handling, in which case you should call RemoveHandler before releasing your object.

As you can see, releasing COM objects from .NET is a fairly complex subject. However, you should be able to use the shell provided above, simply filling in the 'Your Code Goes Here' section and possibly tweaking the Cleanup section as needed to accommodate other variables that you'll likely be using.

If you have any questions on this topic or are having trouble getting your MS Office application to release, feel free to post on the .NET Office Automation forum and we'll be glad to help you out.

CType() and Option Strict On

Note that under 'Option Strict On', one may have to make use of the CType() function to cast an object reference to the precise type required. Note the 3rd line in the shell within the first post, which reads:
Dim oWS As Excel.Worksheet = CType(oWB.Worksheets(1), Excel.Worksheet)
This line is declaring the oWS variable to be an Excel.Worksheet and then setting it to reference the first Worksheet in oWB. If 'Option Strict' is not being used, then the use of CType() is not necessary. However, the use of 'Option Strict' is very highly recommended, in which case an occasional CType() will be needed in order to tell the compiler the intended return type.

"Your Code Goes Here"

In the example shown in the first post, the block that reads 'Your Code Goes Here' is where you would execute your commands. Typically one is reading and writing values into a spreadsheet, so a very simple example might look something like the following:
Dim oRng1, oRng2 As Excel.Range oRng1 = oWS.Range("A1") oRng1.Value = "Hello World" oRng2 = oWS.Range("B2:E5") oRng1.Copy(Destination:=oRng2) oWB.SaveAs("C:\My Documents\MyWorkbook.xls") oRng1 = Nothing ' <-- Don't forget! oRng2 = Nothing ' <-- Or Excel will hang...
The code above will place the words "Hello World" in Cell A1 and then copy it to the Range B2:E5. It then saves the workbook as "MyWorkbook.xls" in the "My Documents" folder. Lastly, it makes certain to release the range references by setting the oRng1 and oRng2 variables = Nothing. Remember that GC.Collect() is called only at the end of the routine in the Cleanup section. Alternatively, one could call Marshal.FinalReleaseComObject() or Marshal.ReleaseComObject() on the oRng1 and oRng2 variables.

Note that the above is also careful to call '.Value' explicitly when needing to get or change the value that is held in a Range. If using VBA or VB 6.0, this would not be required, for the default property of the Range object is in fact the '.Value' property. However, .NET does not recognize any default property that does not take a parameter. For example:
oRng1.Value = "Hello World" ' Legal oRng1 = "Hello World" ' Compile-Time error!
The second line in the above would generate a compile-time error; you cannot set a Range = "Hello". This is because "Hello" is a String (not a Range). The correct approach is to explicitly use the '.Value' property, as in:
oRng.Value = "Hello"

Contrast this with the Worksheets collection, which does take a parameter for its default .Item() property:
oWS = oWB.Worksheets.Item("Sheet1") ' Legal oWS = oWB.Worksheets("Sheet1") ' Also Legal!
The last line calling oWS = oWB.Worksheets("Sheet1") is valid because the default property of the Worksheets Collection is the .Item() property, which does take a parameter.

To summarize, when using VB.NET, if the property does not require a parameter (as is the case with Range.Value) then the property must be explicitly stated, even if it is the default property of the COM object in question. But if the default member does does have a required parameter -- as is the case with the Worksheets.Item() property -- then that property can be utilized as the default.

Execution Speed

Execution speed is rarely an issue when automating Microsoft Office programs such as Word, Outlook or PowerPoint, but can be for Excel, which is often doing numerous calculations before posting results.

If one does care about execution speed, then be aware that Automation via the COM Interop can be slow. As with VB 6.0 Automation, your .NET Application will be running out-of-process with respect to the Excel Application. That is, your code and the Excel program are not sharing the same address space. This slows down execution speed with respect to native VBA by a factor of about 50x or so in my testing. In addition, the COM Interop adds overhead, so your Automation code may run a bit slower even than it did with VB 6.0 Automation.

With VB 6.0, execution speed can be addressed by compiling to a COM DLL, which runs within the MS Office Application - not within a separate EXE. This allows your code to run in process and therefore run at native VBA's speed, or even slightly faster. Using .NET can only partially take advantage of this technique. One can create a Managed COM DLL, as described here in Creating Office Managed COM Add-Ins with Visual Studio .NET. However your project is still within the .NET Framework -- although the COM Application thinks that your addin is a true COM DLL.The COM application does not know the difference because the .NET Interop is calling (and/or providing callbacks to) methods that utilize the correct COM interfaces of a standard COM DLL. While this approach by Microsoft is impressive, there is still some degradation in execution speed, although it is not noticeable in most circumstances.

Whether using Automation or a Managed COM DLL, one of the key "tricks" to improving execution speed when calling Excel is, instead of looping through the cells of a Range one-by-one, to instead manipulate an entire array set to (or returned by) the Range.Value property in one shot. Here are a couple of threads discussing this approach:

(1) Iterating the Values in a Range.
(2) Why is this running so slow?

Overall, when automating MS Office programs from a VB.NET application, then your execution speeds should be similar to or perhaps slightly slower than was your speed under VB 6.0 Automation. However, in most cases there will be no noticeable difference and the programmer can benefit greatly by taking advantage of .NET's improved language features.

Deployment Issues

Another issue to consider is deployment. VB.NET can be tricky to implement for MS Office versions below version 11.0 (Office 2003).

Automation of Office 10.0 can be accommodated by downloading the proper Primary Interop Assemblies (PIAs) from Microsoft. See Microsoft Office XP PIAs Are Available for Download. Another link that can be helpful is Office XP Primary Interop Assemblies Known Issues.

For versions 9.0 and below, there is no official support from Microsoft, and it might be best to simply avoid using Office versions below 10.0 when using .NET. However, the intrepid can create a local Interop assembly by using TlbImp.exe and allow for multiple-version compatibility using early binding. There is a good discussion of this here: COM Automation across multiple COM versions (DevCity).

Lastly, the target computer must also have the .NET Framework installed. See the .NET Framework Developer Center for more information.
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Colin Legg; 06-10-2010 at 02:02 PM. Reason: updated
Reply With Quote