===================================================
Lesson 1: Basic Changes in VB.Net Automation versus prior VB 6.0
-------------------------------------------------------------------
There are a number of fundamental changes to be observed when Automating in VB.Net versus the previous VB 6.0 / 5.0 / 4.0 versions. Here we will cover a few of the most important:
(1) There is no 'Set' Keyword used in VB.Net. In VB.Net you omit its usage where it would have been used previously in VB 6.0.
(2) The tricky part about Automating an Office program such as Excel, Word or Access is that you need to declare each sub-object as its own, separate variable.
For example, instead of the following code
Code:
oWB = oExcel.Workbooks.Add()
when Automating from VB.Net, you are required to break it down as follows:
Code:
dim oWorkbooks as Excel.Workbooks
oWorkbooks = oExcel.Workbooks
oWB = oWorkbooks.Add()
While the first line of code looks cleaner and is a perfectly valid construction 99% of the time when using VB.Net, using such code for COM Automation will not allow your referenced Application Object to quit properly when done. (For more information on this, please see examples used in Lesson 2, below, as well as
MSKB - 317109.)
(3) 'Cleanup' is also more involved when using Automation in .Net than it was in VB 6.0 or even for most other areas of .Net programming. To properly release your object references when using COM Automation in .Net, you must explicitly call System.Runtime.InteropServices.Marshal.ReleaseComObject() for each object. For example:
Code:
oWB.Close(SaveChanges:=False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB)
oWB = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
However, using the following routine can be of great help in keeping your code manageable:
Code:
Private Sub NAR(ByVal o As Object)
' This sub cleanly releases the COM Automation Object.
' Source: http://support.microsoft.com/?kbid=317109
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub
The above Sub NAR() then allows us to reduce our cleanup proceedure to:
Code:
oWB.Close(SaveChanges:=False)
NAR(oWB)
oExcel.Quit()
NAR(oExcel)
The NAR() routine can be found in the
MKBA - 317109, with special thanks to Wamphyri for pointing it out.
(4) Error Handling is substantially changed in .Net. So much so that a full discussion would require it's own, separate lesson. (That is, not here.)
For the squeamish, the older VB6 Error Handling constructs still apply; that is, you can still use 'On Error Goto
<linelabel>' and 'On Error Resume Next' as well as the 'Resume' and 'Resume Next' statements. However, it is well worth the investment to learn the new constructs using 'Try...End Try' along with the 'Catch' and 'Finally' keywords.
In the Sub NAR(), above, the 'Try..End Try' is bracketing the error handling. If any error occurs within the 'Try..Catch' block, then code execution immediately jumps to the 'Catch' statement -- which in this case is empty, that is, nothing to execute. The code after the 'Finally' statement
always executes
regardless of whether an error occurred or not within the 'Try..Catch' section. So for the Sub NAR(), the 'Finally' statement is ensuring that
always occurs.
For basic info on Error Handling in VB.Net, see MSDN help on the
Try..Catch..Finally Statements.
(5) For the most part, 'Default' properties such as the .Value property of an Excel.Range object must now be explicitly stated as 'Range.Value' at all times. The only Default Properties that can still be accessed in 'Default Mode' are those that require parameters. For example:
Code:
Dim X As Integer
X = oExcel.ActiveSheet.Range("A1") ' < -- Run-Time Error!
X = oExcel.ActiveSheet.Range("A1").Value ' <-- Correct.
The first line fails because 'X' is being set as a Reference to the Range("A1"). In VB6 the compiler would assume that you *meant* to use Range("A1").Value, due to the lack of the 'Set' keyword. However, in VB.Net, there is no 'Set' Keyword and so 'X' is referencing the Range("A1") object itself. Since 'X' is an Integer, and cannot hold a Range object, an error is raised. (Or to use .Net terminology, "an exception is thrown".)
The 2nd line in the above is fine, for the X is being assigned to the Range.Value. In short, always be explicit with your 'Default' properties & methods if they don't take a parameter, for such 'Default' members are not recognized in .Net.
Note the following, however:
Code:
Dim oWS as Excel.Worksheet
oWS = oExcel.Worksheets.Item("Sheet1") ' <-- OK.
oWS = oExcel.Worksheets("Sheet1") ' <-- Also OK!
The 2nd version, above, is utilizing the Excel.Worksheets default .Item() property in 'Default Mode'. This is permitted because Worksheets.Item() takes a parameter, while the Excel.Range's default .Value property does not.
There is no ambiguity for the compiler here, which knows that the Default .Item() property is being called, and the oWS object reference is being set to it.
(6) Parentheses are now required when passing parameters to functions and subs at all times. In VB 6.0, one would usually call the MsgBox function in the following manner:
However, in VB.Net, the parentheses are required:
Code:
MessageBox.Show("Hello")
And, yes, the MsgBox command has been updated with VB.Net’s newer MessageBox object. The older MsgBox does still work (if you insist), but it is not recommended. VB.Net will run much more efficiently if you avoid using legacy commands. In short, use the MessageBox -- you'll like it, even if it does have some extra letters.
Good luck out there!
,
Mike
VB.Net Office Integration FAQ
Linear Mode
