Professional Excel Development: Excel and VB.NET
This chapter is from the book
This chapter is from the book
This chapter is from the book
In 2002, Microsoft released the first version of its development suite Visual Studio.NET (VS.NET) together with the .NET Framework. Since then, Microsoft has released new versions of the Framework and development suite in quick succession. Microsoft has strongly indicated that .NET is the flagship development platform now and for the foreseeable future.
Visual Basic.NET (VB.NET) is part of VS.NET, and despite its similarity in the name with Classic VB (VB6), the two have little in common. VB.NET is the successor to Classic VB and as such it provides the ability to create more technically modern solutions, a large group of new and updated controls, and a new advanced IDE. Moving from Classic VB to VB.NET is a non-trivial process, primarily because VB.NET is based on a new and completely different technology platform.
Excel developers also face the situation where applications created with the new .NET technology need to communicate with applications based on the older COM technology, for example, VB.NET applications communicating with Excel. Because Excel is a COM-based application it cannot communicate directly with code written in .NET.All .NET code that communicates with Excel must cross the .NET COM boundary. This is important to keep in mind because it is a challenge to manage and can have significant performance implications.
In the first part of this chapter, VB.NET is introduced along with the .NET Framework. The second part of this chapter focuses on how we can automate Excel with VB.NET. Finally we cover ADO.NET, which is used to connect to and retrieve data from various data sources. ADO.NET is the successor to classic ADO on the .NET platform.
To provide a better understanding of VB.NET, we develop a practical solution, the PETRAS Report Tool.NET. This solution is a fully functional Windows Forms based reporting tool. It retrieves data from the PETRAS SQL Server database and uses Excel templates to present the reports.
VB.NET, ADO.NET, and the .NET Framework are book-length topics in their own right; what we examine here and in the two following chapters merely scratches the surface. At the end of this chapter you find some recommended books and online resources that provide additional detail on these subjects.
.NET Framework Fundamentals
The .NET Framework is the core of .NET. Before we can develop or run any .NET-based solutions, the Framework must be installed and available. The Framework provides the foundation for all .NET software development. The .NET Framework is also responsible for interoperability between .NET solutions and COM servers and components. This topic is covered later in the chapter. For the purposes of our discussion, we can think of the .NET Framework architecture as consisting of two major parts:
- A huge collection of base class libraries and interfaces—This collection contains all the class libraries and interfaces required for .NET solutions. Namespaces are used to organize these class libraries and interfaces into a hierarchical structure. The namespaces are usually organized by function, and each namespace usually has several child namespaces. Namespaces make it easy to access and use different classes and simplify object references. We discuss namespaces in more detail when presenting VB.NET later in this chapter.
- Common Language Runtime (CLR)—This is the engine of the .NET Framework, and it is responsible for all .NET base services. It controls and monitors all activities of .NET applications, including memory management, thread management, structured exception handling (SEH), garbage collection, and security. It also provides a common data type system (CTS) that defines all .NET data types.
The rapid evolution of the .NET Framework is reflected in the large number of versions available. Different Framework versions can coexist on one computer, and multiple versions of the Framework can be run side-by-side simultaneously on the same computer. However, an application can only use one version of the .NET Framework at any one time. The Framework version that becomes active is determined by which version is required by the .NET-based program that is loaded first. A general recommendation is to only have one version of the Framework installed on a target computer.
Because there are several different Framework versions in common use and we may not be able to control the version available on the computers we target, we need to apply the same strategy to the .NET Framework as we do when targeting multiple Excel versions: Develop against the lowest Framework version we plan to target. Of course there will also be situations that dictate the Framework version we need to target, such as corporate clients who have standardized on a specific version.
As of this writing, the two most common Framework versions are 2.0 and 3.0. Both versions can be used on Windows XP, and version 3.0 is included with Windows Vista and Windows Server 2008. Visual Studio 2008 (VS 2008) includes both of these Framework versions as well as version 3.5. By providing all current Framework versions, VS 2008 makes it easy to select the most appropriate version to use when building our solutions. Versions 3.0 and 3.5 of the .NET Framework are backward compatible in a similar manner as the latest versions of the Excel object libraries.
The .NET Framework can run on all versions of Windows from Windows 98 forward, but to develop .NET-based solutions we need to have Windows 2000 or later. If we plan to target Windows XP or earlier we need to make sure the desired version of the .NET Framework is installed on the target computer, because these Windows versions do not include the Framework preinstalled. All versions of the Framework are available for download from the Microsoft Web site and can be redistributed easily. To avoid confusion, we only use version 2.0 of the .NET Framework in this chapter and the next.