Section 5.19.Migrate to .NET | Excel 2003 Programming: A Developers Notebook (Developers Notebook)

5.19. Migrate to .NET

If you are an experienced Visual Basic programmer, you've got a good start on learning VB.NET. However, there are significant language differences so be prepared for a learning curve and don't expect to be able to cut and paste code from a VBA project into VB.NET and have the code run.

Note: .NET isn't an allor- nothing proposition. Most businesses will migrate from COM to .NET over a long period of time. In fact, COM will probably never completely go away. Keep those copies of VB 6.0 around!

Existing VBA code may provide a template for VB.NET code, but VB.NET is really a different language from VBA. There are large as well as subtle differences. If you are new to VB.NET, you will save a great deal of time by buying and reading one of the many books on VB.NET. One of the best, in my opinion, is Programming Microsoft VB.NET Version 2003 by Francesco Balenaa book that I was proud to work on as technical editor.

The following sections list a few recommendations that may make your transition easier.

5.19.1. What you lose: edit and continue

You can't change code while debugging VB.NET applications and then continue execution using those changes. .NET applications must be recompiled each time they are run. This is cumbersome when loading and unloading especially large applications such as Excel, so it is a good idea to have the basic .NET programming techniques down cold before tackling an Excel .NET project.

A good way to learn .NET programming techniques is to start with any of the many tutorials that come with the .NET Framework or to start with Console or Windows Forms projects. Trying to learn .NET while working with Excel can be frustrating.

Tip: The next version of VB.NET (now in beta) provides Edit and Continue.

5.19.2. Be explicit!

I've already mentioned that .NET doesn't support VBA's concept of a default property. If you are going to set the value of an object, you must use the Value property (or its equivalent).

Being explicit also applies to object references. It is much easier to program in .NET if you are using a specific object type, such as Worksheet, rather than the generic Object type. Using the specific object enables Intellisense and autocomplete features of .NET and helps detect inadvertent errors, such as incorrect variable assignments.

In many cases, Excel methods return generic Object types, which should be converted to the expected, more specific type. Use CType to perform this conversion, but be sure to check if the object can be converted before performing the conversion. For example, the following code checks if the passed-in argument Sh is a Worksheet before performing the conversion:

    Private Sub ThisWorkbook_SheetActivate(ByVal Sh As Object) _      Handles ThisWorkbook.SheetActivate        If Sh.Type = Excel.XlSheetType.xlWorksheet Then _          ActiveWorksheet = CType(Sh, Excel.Worksheet)    End Sub

Trying to convert an object to an incompatible type causes a runtime error.

In .NET everything is an object, even simple types like strings and integers are their own classes derived from .NET's base Object type. At first this might seem cumbersome, but the consistency and logic of this approach pay huge dividends.

5.19.3. Pass arguments by value

By default in VBA, procedures pass arguments by reference. The default in .NET is to pass arguments by value. If you cut and paste code from VBA, .NET will add ByVal to unqualified argument definitions, thus changing how the arguments are passed.

5.19.4. Collections start at zero

The index of the first element of any .NET collection is zero. In Excel, the first element of any collection is 1.

5.19.5. Data access is through ADO.NET

.NET provides access to databases, XML data, and in-memory data tables through ADO.NET, which is significantly different from prior data access techniques. Backward compatibility is provided for ADO data binding, but the best advice here is to pick up a good book on the subject and start learning.

5.19.6. What about...

To learn how to	Look here
Convert VBA code to .NET	Search msdn.microsoft.com for "Converting Code from VBA to VB.NET"