Professional Excel Development: Excel and VB.NET

This chapter is from the book 

Visual Basic.NET

With VS.NET we can create Web applications, server applications, database applications, console applications, Windows desktop applications, setup and deployment projects, and much more. VS.NET ships with the following programming languages: Visual C#, VB.NET, and Visual C++.

VB.NET is distributed in all VS.NET packages as well as in a standalone version. However, not all capabilities are present in all distributions. You need to select the version of VB.NET that fits your requirements best. Table 24-1 shows the capabilities related to Excel development and the distributions in which they are available.

Table 24-1. Available Tools in Different Versions of VS.NET

	VB.NET Express	VS.NET Standard	VS.NET Professional
Automate Excel	3	3	3
Shared Add-in Template (To create managed COM add-ins with.)		3	3
Office templates			3
Visual Studio Tools for Office System (VSTO)			3

For a full comparison among the versions, see http://msdn.microsoft.com/en-us/vs2008/products/cc149003.aspx. If you just want to try out VB.NET you can download the free Express Edition from Microsoft's Web site. VS.NET Professional is required if you plan to develop managed COM add-ins and VSTO solutions. It is also required to follow the discussions here and in the next two chapters.

VB.NET was the first version of VB that broke backward compatibility badly enough that you could not even open a project created in an earlier version of VB. If you have non-trivial Classic VB projects that you would like to transfer to VB.NET, the best choice is to create them from scratch in VB.NET. Microsoft has some tools to ease the transition, but for larger VB projects they cannot do all the work. On the other hand, you may also consider keeping your Classic VB solutions for as long as it is still possible to run them on the Windows versions your solution targets. VB.NET is the first BASIC language version that fully supports object oriented programming (OOP). It means that with VB.NET we can fully utilize encapsulation, inheritance, and polymorphism.

Code that targets the .NET runtime is described as managed code while code that cannot be hosted by the .NET runtime is described as unmanaged code. Assemblies are the binary units (*.DLL or *.EXE) that contain the managed code. Since it is common that one .NET assembly contains only one binary unit, it is safe to refer to .NET-based DLL files as assemblies.

The Visual Studio IDE

The Visual Studio IDE (VS IDE) is shared by all .NET programming languages. The VS IDE is a complex development environment, even for developers who are very familiar with the Classic VB IDE. Figure 24-1 shows the VS IDE with a simple VB.NET Windows Forms project open.

When you first run VS.NET, you are prompted to select a development category for VS.NET to use in customizing the environment. If your previous experience is with Classic VB or VBA, you will probably want to allow VB.NET to be your first choice of programming language. In this case, choose the Visual Basic Development Settings. The VS IDE is also highly customizable by the user, but before beginning to customize it you should learn the basics using the default configuration.

General Configuration of the VS IDE

After launching the VS IDE, you should change some general configuration settings for the development environment. Start by selecting Tools > Options... from the menu. This displays the Options dialog shown in Figure 24-2.

The Options dialog organizes its settings in a tree view on the left side. The VB Defaults section under Projects and Solutions contains four of the more important settings for VB.NET development. We recommend that you set them exactly as shown in Figure 24-2. A detailed description of each setting follows:

• Option Explicit—Determines whether VB.NET requires us to declare all variables before using them.
• Option Strict—Turning on this setting disallows late binding (to improve performance), implicit data type conversion, and provides strong typing (strict use of type rules with no exceptions).
• Option Compare—Specifies the default method used for string comparisons. It can either be Binary (case-sensitive) or Text (case-insensitive). The default value is Binary, which provides the same text comparison behavior as Classic VB. See Chapter 3, "Excel and VBA Development Best Practices," for more information.
• Option Infer—When this setting is turned on it allows us to omit the data type when declaring a variable and instead let VB.NET identify ("infer") the data type. Listing 24-1 shows a simple example. The right-hand value tells the compiler the data type is an Integer. Declaring a variable and giving it a value at the same time in this manner is fully supported in VB.NET.

Listing 24-1. Omitting the Data Type When Declaring a Variable

Dim iCountRows = 225

When working with VB.NET solutions (a solution can contain one or more projects), these settings can be overridden at the code module level. This means, for example, that if we really need to use late binding in one code module we can modify the Option Strict setting at the top of that code module. Listing 24-2 shows how to turn off the Option Strict setting and also change comparisons to Text.

Listing 24-2. Changing Settings at the Code Module Level

Option Compare Text
Option Strict Off

Adding line numbers to your code can make many development tasks easier, the debugging process in particular. To activate this option, expand the Text Editor section in the Options dialog and select the Basic section below it. Check the option Line numbers and then close the dialog.

Next we make screentips and keyboard shortcuts available in the IDE. Choose Tools > Customize... from the menu. This displays the Customize dialog shown in Figure 24-3. Check the two options Show ScreenTips on toolbars and Show shortcut keys in ScreenTips and then close the dialog.

The final setting is to make various docked windows in the IDE hide themselves when they are not being used. This provides us with a workspace that is not cluttered with open windows not relevant to the current context.

1. Click on the window you want to hide so it gets the focus.
2. On the Window menu click on the option Auto Hide or click on the pushpin icon on the title bar for the window.
3. Repeat these steps for every window that you want to auto hide.

When an auto-hidden window loses focus, it automatically slides back to its tab on the edge of the IDE.

Creating a VB.NET Solution

We create a new VB.NET project by selecting the File > New Project... from the menu. This displays the New Project dialog shown in Figure 24-4.

Since we are creating a Windows based-solution, select Windows in the Project types section and then select the Windows Forms Application template. We also select the version of .NET Framework to target using the combo box in the upper-right corner. Next, enter the name "First Application" in the Name box. By default, the solution name is the same as the name entered in the Name box, as shown in Figure 24-4. The solution name is also used to name the main folder of the project. Finally, click the OK button to create the solution.

The Solution Explorer window provides the workspace for working with files and projects inside VB.NET solutions. Figure 24-5 shows the workspace for our solution. A single Windows Form has been added to the solution and we have right-clicked on the form to display the shortcut menu containing the various actions available to perform on that object.

Windows Forms are the basic building block of many solutions. They provide us with a graphical user interface to which we can add controls. Windows Forms and all other Windows controls are contained in the System.Windows.Forms namespace. Windows Forms are in many ways identical to their counterpart Forms in Classic VB but are more modern and offer more properties to work with.VB.NET provides a large number of Windows controls for various purposes. However, use the new controls with good judgment. They exist to create a friendly user interface, not confuse the user.

Although VB.NET is designed to use Windows Forms controls, we can still use ActiveX controls. Therefore, if we own expensive third-party ActiveX controls, we can still use them in VB.NET. To add a control to a Windows Form, click the control's icon in the Toolbox and then drag and drop over the area on the form where you want the control to be placed. For our solution we add a label control, combo box, and two buttons to the Windows Form and resize the form itself. Figure 24-6 shows how the final Windows Form looks.

Before we add code to the Windows Form, we set the tab order for the controls. Select View > Tab Order from the menu. The tab order for each control is now displayed visually on the form. Clicking on a control's tab number increases the number. Change the tab order so that it matches the order shown in Figure 24-7. To exit the tab order view, select View > Tab Order from the menu again.

As a final step, we add code to the solution. Select View > Code from the menu. This opens the class module for the Windows Form. The first event we use is the Load event of the Windows Form. This is created by first selecting (Form1 Events) from the combo box in the upper-left corner of the module and then selecting Load from the combo box in the upper-right corner of the module. Listing 24-3 shows the code in the Load event.

Listing 24-3. The Code for the Load Event of the Windows Form

Private Sub Form1_Load(ByVal sender As Object, _
                         ByVal e As System.EventArgs) _
                         Handles Me.Load

        'Create and populate the array with names.
        Dim sArrNames() As String = {"Rob Bovey", _
                                       "Stephen Bullen", _
                                       "John Green", _
                                       "Dennis Wallentin"}
        With Me
            'The caption of the Form.
            .Text = "First Application"

            'The captions of the label and button controls.
            .Label1.Text = "Select the name:"
            .Button1.Text = "&Show value"
            .Button2.Text = "&Close"

            'Populate the combobox control with the list
            'of names.
            With .ComboBox1
                .DataSource = sArrNames
                .SelectedIndex = -1
            End With

        End With
End Sub

In this code, we create a string array, set values for various control properties, and then add the array as a data source for the combo box control. We use a single dimension array to populate the combo box with the list of names. It is a perfectly accepted practice to declare and initialize an array at the same time in VB.NET, as shown in Listing 24-3. When using this approach we do not need to specify the size of the array because this is inferred from the number of items within the scope of the curly brackets.

The next step is to get the selected value from the combo box and display it in a message box. Before doing that we need to import the namespace System.Windows.Forms into the code module, which gives us a shortcut to the .NET MessageBox class. Importing namespaces saves keystrokes each time we refer to objects that are part of the imported namespaces. It also makes our code easier to read and maintain by making it less verbose.

The Imports statements tell the compiler which namespaces the code uses. Usually we first set a reference to a namespace and then we import it to one or more code modules. Here we only do the latter because the System namespace is referenced by default in all new VB.NET solutions. This is because Visual Studio automatically adds a reference to the System namespace when a new VB.NET project is created. At the top of the Form's class module we add the Imports statement shown in Listing 24-4.

Listing 24-4. The Imports Statement

'To use the messagebox object.
Imports System.Windows.Forms

The namespace Microsoft.VisualBasic also belongs to the namespaces that are referenced by default in all new VB.NET solutions. This namespace is also globally imported, which means we do not need to import it into individual code modules to use it. From a practical standpoint this means we can use the well-known MsgBox function instead of its .NET variant. However, in Listing 24-5 we use .NET MessageBox class in the Click event for Button1, which displays the selected name in a message box.

Listing 24-5. Show Selected Name

Private Sub Button1_Click(ByVal sender As System.Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles Button1.Click

        'Make sure that a name has been selected.
        If Me.ComboBox1.SelectedIndex <> -1 Then

            'Show the selected value.
            MessageBox.Show( _
                     text:=Me.ComboBox1.SelectedValue.ToString(), _
                     caption:="First Application")
        End If

End Sub

The final piece of the puzzle is to add a command to close (unload) the Windows Form in the Button2 Click event. Listing 24-6 shows the required code.

Listing 24-6. Unload the Windows Form

Private Sub Button2_Click(ByVal sender As System.Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles Button2.Click
         Me.Close()
End Sub

To begin testing the application, we just have to press the F5 key. Figure 24-8 shows the First Application in action after we select a value in the combo box and then click the Show value button.

Whenever we execute the application in the debugger, the VS IDE creates a number of new files, including an executable file for our application. These files are located in the ..\First Application\bin\Debug folder. A working example of this solution can be found on the companion CD in the \Concepts\Ch24 - Excel & VB.NET\First Application folder. If you just want to run the application without opening it in Visual Studio, the First Application executable file can be found in the \Concepts\Ch24 - Excel & VB.NET\First Application\First Application\bin\Debug folder on the CD.

Structured Exception Handling

When an unexpected condition occurs in managed code, the CLR creates a special object called an exception. The exception object contains properties and methods that give detailed information about the unexpected condition. Because we deal with exceptions rather than errors in .NET development, we use the expression exception handling rather than error handling.

Exception handling covers the techniques used to detect exceptions and take appropriate actions after they are detected. Structured exception handling (SEH), is the term used to describe how we implement exception handling in managed code. Although it is possible to use the Classic VB error handling approach in VB.NET, we strongly encourage the use of SEH because it gives us much better options for dealing with exceptions. SEH consists of the following building blocks:

• Try—We place the code we want to execute in this block. This code may create one or more exceptions.
• Catch—In this block we place the code that handles the exceptions. It is possible to place several Catch blocks within the same structure to handle different types of exceptions. Catch blocks are optional.
• Finally—Code placed in this block always is executed, which makes this block a perfect place for code to clean up and release references to objects like COM objects and ADO.NET objects. This block is also optional.
• End Try—Ends the SEH structure.

Listing 24-7 shows the skeleton structure of SEH in code. When we enter a Try statement in a code module, the VS IDE automatically adds the Catch block and End Try statement. The Finally block must be typed manually.

Listing 24-7. The Building Blocks of SEH

Private Function iDiscount(ByVal iPrice As Integer) As Integer

         Try
             'Do the calculation here.

         Catch ex As Exception
             'In case of any unexpected scenarios take
             'some action here, like a message to the user.

         End Try
End Function

Most of the namespaces in the .NET Framework class library include their own specific exception classes, which make it possible to catch them in separate Catch blocks. All built-in exception classes extend the built-in System.Exception class. Catch blocks are executed (or tested for execution) in the order in which they are coded. .NET works its way through the Catch blocks trying to find a matching exception type. Therefore the preferred approach is to implement the Catch blocks with more specific exception types first, followed by the Catch blocks with the more generic exception types. Listing 24-8 shows an example using two Catch blocks.

Listing 24-8. Using Several Catch Blocks and the Finally Block

        Try

            frmSaveFile = New SaveFileDialog

            With frmSaveFile
                .Filter = "XML File|*.xml"
                .Title = "Save report to XML file"
                .FileName = sFileName
            End With

            dtTable.WriteXml(fileName:=sFileName)

            dtTable.WriteXmlSchema( _
                    fileName:=Strings.Left(sFileName, _
                 Len(sFileName) - 4) & ".xsd")

        Catch XMLexc As Xml.XmlException

            MessageBox.Show(text:=sMESSAGENOTSAVEDXML, _
                              caption:=swsCaption, _
                              buttons:=MessageBoxButtons.OK, _
                              icon:=MessageBoxIcon.Stop)

        Catch COMExc As COMException

            MessageBox.Show(text:= _
                              sERROR_MESSAGE & _
                              sERROR_MESSAGE_EXCEL, _
                              caption:=swsCaption, _
                              buttons:=MessageBoxButtons.OK, _
                              icon:=MessageBoxIcon.Stop)

        Catch Generalexc As Exception

            MessageBox.Show(text:=sMESSAGENOTSAVEDGENERAL, _
                              caption:=swsCaption, _
                              buttons:=MessageBoxButtons.OK, _
                              icon:=MessageBoxIcon.Stop)

        Finally

            frmSaveFile.Dispose()
            frmSaveFile = Nothing

        End Try

The first Catch block handles any XmlException exceptions. The second block catches COM exceptions that might occur when working with COM servers like Excel. The final Catch block is generic and handles all other exceptions. The example also shows how we can use the Finally block to release an object. Listing 24-8 also shows how to use custom error messages to respond to each exception type.

During development we need to see the underlying technical details for all exceptions. In Listing 24-9 the previously customized end user messages have been replaced with the exception object and its method ToString in each Catch block. The ToString method gives a textual summary of the exception. You can also use the GetBaseException method to return the first exception in the chain.

Listing 24-9. Displaying Exception Descriptions

        Catch XMLexc As Xml.XmlException

            MessageBox.Show(XMLexc.ToString())

        Catch COMExc As COMException

            MessageBox.Show(COMExc.ToString())
            MessageBox.Show(COMExc.ErrorCode.ToString())

        Catch Generalexc As Exception

            MessageBox.Show(Generalexc.ToString())

When VB.NET receives an exception from a COM server like Excel, it checks the COM exception code and tries to map that code to one of the .NET exceptions classes. If this fails, which is the most common outcome, VB.NET throws a large and mostly unhelpful HRESULT message like the one shown in Figure 24-9.

The line of code that generates this message is the first MessageBox.Show line under the COM exception block in Listing 24-9. COM exceptions are wrapped into generic COMException objects when .NET does not have a matching exception class for the HRESULT error generated by a COM component.

In SEH, it is possible to exit a Try block with the Exit Try statement. This statement can be placed either in the Try block or in any Catch block. Any code in a Finally block is still executed after the Exit Try statement.

Another option is to use nested Try structures. A nested SEH can be added either to the Try block or to a Catch block. When using nested exception handlers the InnerException property of the exception object becomes very important. It helps us determine the cause of the nested exception and allows us to obtain the chain of exceptions that led to that exception.

We can use the Throw statement to communicate exceptions to the calling code. Throw is usually used within a Catch block only if the exception is to be bubbled up the call stack. A Throw statement causes code execution to be intentionally interrupted. The Throw statement also allows us to create our own exceptions, but this topic is beyond the scope of this chapter.

Modules and Methods, Scope and Visibility

When we make a declaration at the module level (module here stands for module, class, or structure), the access level we choose determines the scope of the thing being declared. In VB.NET we can use the keywords Public and Private, which have the same scopes as in Classic VB, but VB.NET also provides the following additional keywords to specify module scope and visibility:

• Friend—A data member or method (function or subroutine) declared with the Friend modifier can be accessed from any part of the program containing the declaration. This is not a new keyword, as it is also available in Classic VB. However, if we do not explicitly include a scope in our declaration, then the default scope is Friend in VB.NET, while in Classic VB the default scope is Public.
• Protected—Data members or methods declared with Protected scope are only accessible from the module itself or from derived classes.
• Protected Friend—This scope is equivalent to the union of Protected and Friend access. A data member or method declared as Protected Friend is accessible from anywhere in the program in which the declaration occurs, or from any derived class containing the declaration.

Declare Variables and Assign Values

In VB.NET, we declare local variables using the keyword Dim, module-level variables using the keyword Private, solution-level variables using the keyword Friend, and public variables using the keyword Public. All .NET programming languages provide the option to declare variables and assign values to them at the same time.

The first two lines in Listing 24-10 show how we can declare variables and initialize them with values using one line of code. The third line creates three String variables without assigning any values to them. Since they don't have assigned values, these String objects are marked as unused local variables by the VS IDE. This is a result of the Option Strict setting being on. Good coding practice in .NET says that we should always assign known values to variables, even if they initially will not have any "real" values. Lines 4 through 6 show how we can achieve this in practice.

Listing 24-10. Declare Variables and Assign Values to Them

1  Dim sTitle As String = "PETRAS Report Tool"
2  Dim iPrice As Integer = 100

3  Dim sAddress, sCity, sCountry As String

4  Dim sName = String.Empty
5  Dim bReportStatus = Nothing
6  Dim iNumberOfRecords As Integer = Nothing

7  Dim iNumberOfColumns As Integer = dtTable.Columns.Count - 5
8  Dim iNumberOfRows As Integer = dtTable.Rows.Count - 1
9  Dim obDataArr(iNumberOfRows, iNumberOfColumns) As Object

Lines 7 and 8 in Listing 24-10 contain two variables that hold the number of columns and rows of a DataTable (an ADO.NET object covered later in this chapter). These two variables are then used as parameters to dimension the array of data type Object in line 9. The data type Object is the VB.NET counterpart to the data type Variant in Classic VB. An Object array behaves in roughly the same manner as a Variant array.

VB.NET also offers the ability to declare variables anywhere in the code. Listing 24-11 shows an example where we have declared a variable within a Try block in conjunction with a For...Next loop.

Listing 24-11. Block Scope Variable Declaration

        Try
            For iCountRows As Integer = 0 To 9
                'Do the iteration.
            Next iCountRows

        Catch ex As Exception

              MessageBox.Show(ex.ToString())

        End Try

Block scope can also be achieved by declaring variables within With...End With blocks, For...Next blocks, and Do...Loop blocks. In Listing 24-12 we show a variable that is declared in a Do...Loop.

Listing 24-12. Block Scope within a Do...Loop

'Declaration of a variable with
'a block scope of Do...Loop.
Do
    Dim iMonth As Integer = 1
    'Other code goes here...
Loop

However, declaring variables using this method may cause unexpected problems. This is because the scope of variables declared in this manner is limited to the block in which they are declared. This means we cannot access these variables or use them outside that block. Code that uses this method can also be more difficult to debug and maintain. In general we should avoid this approach. Good coding practice suggests that all variables used within a method should be declared at the beginning of that method.

Creating New Instances of Objects

We can create new instances of objects in VB.NET using the same techniques as in Classic VB. The only difference is that we do not use the Set keyword in VB.NET. Listing 24-13 shows two methods of creating objects in VB.NET. The Nothing keyword is a way of telling the system that the variable does not currently have any value but still may use memory.

Listing 24-13. Declare and Instantiate Objects

'The classic approach.
Dim frmSaveDialog As SaveFileDialog = Nothing
frmSaveDialog = New SaveFileDialog

'.NET approach.
Dim frmSaveDialog As New SaveFileDialog

The .NET approach is singled out in the second example in Listing 24-13, which shows that we declare and set the variable to a new instance of the SaveFileDialog class with one line of code. Although the .NET approach may look attractive, we still recommend using the classic approach. This is also outlined as the best practice in Chapter 3.

Using the .NET approach can cause unwanted exceptions because of the block scoping of variables. For example, if we create a new instance of the SaveFileDialog component and we want to trap any exceptions that may occur (or we want to throw an exception), block scoping of the variable itself causes an exception. This is demonstrated in Listing 24-14, where we have declared and instantiated the frmSaveDialog object variable in the Try block. However, because the scope of this variable is limited to the Try block, the VS.IDE displays a compile error for the two lines of code inside the Finally block.

Listing 24-14. Using the .NET Approach

    Sub Show_Save_Dialog()

        Try

            Dim frmSaveDialog As New SaveFileDialog
            frmSaveDialog.ShowDialog()


        Catch ex As Exception

        Finally

            frmSaveDialog.Dispose()
            frmSaveDialog = Nothing

        End Try

    End Sub

To correct this problem, we modify the code to use the classic approach as shown in Listing 24-15. The frmSaveDialog variable can now be seen throughout the Try block, and it traps any exceptions that may occur.

Listing 24-15. Using the Classic Approach

    Sub Show_Save_Dialog()

        Dim frmSaveDialog As SaveFileDialog = Nothing

        Try

            frmSaveDialog = New SaveFileDialog
            frmSaveDialog.ShowDialog()


        Catch ex As Exception

            MessageBox.Show(ex.ToString())

        Finally

            frmSaveDialog.Dispose()
            frmSaveDialog = Nothing

        End Try

    End Sub

Using ByVal or ByRef

Unlike Classic VB, procedure arguments in VB.NET are passed ByVal by default not ByRef. If we do not explicitly specify procedure arguments as either ByVal or ByRef, the VB.NET default is ByVal. However, good practice states that we should always explicitly specify the keyword we want to use.

Using Wizards in VB.NET

Compared to the wizards in Classic VB, the wizards in VB.NET have been significantly improved. New wizards have also been added to the VS IDE. The advantage of using a wizard is that we get the desired result in a fast and reliable way without needing to have a deep understanding of the process. The wizard takes care of the details. The disadvantage of using a wizard is that the wizard works in "black box" mode, which means we do not have much control over the process. Developing real-world applications requires you to be in control and to understand your solutions inside and out. You can explore the wizards in the VS IDE, but for any non-trivial solution you should avoid them.

Data Types in VB.NET

Compared with Classic VB, some data types are new in VB.NET. Table 24-2 shows most of the VB.NET data types but not all of them.

Table 24-2. Data Types in VB.NET

Data Type	Size	Values
Boolean	2 bytes	True or False.
Short	2 bytes	-32,768 to 32,768.
Integer	4 bytes	-2,147,483,648 to 2,147,483,648.
Long	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,808.
Decimal	16 bytes	It provides the greatest number of significant digits for a number.
Double	8 bytes	It provides the largest and smallest possible magnitudes for a number.
String	Variable	A string can hold 0 to 2 billion Unicode characters.
Date	8 bytes	January 1, 0001 0:0:00 to December 31,9999 11:59:59.
Object	4 bytes	Point to any type of data.

As we can see in Table 24-2, the data type Short includes the interval -32,768 to 32,768, and the Integer data type now covers a much greater interval than it does in Classic VB. The Currency data type is no longer available in VB.NET. It has been replaced by the new Decimal data type, which can handle more digits on both sides of the decimal point. The Byte data type from Classic VB has no counterpart in VB.NET. The data type Object is the universal data type in VB.NET, taking the place of the Variant data type in Classic VB.

String Manipulation

As previously mentioned, whenever a new .NET solution is created the namespace Microsoft.VisualBasic is included by default. This provides access to the .NET versions of the well-known string functions in Classic VB. The .NET Framework also provides us with a System.String class to manipulate strings. However, using the old functions has no negative impact on solution performance, so using the old familiar functions is completely acceptable.

Using Arrays in VB.NET

The .NET Framework provides us with powerful new options for creating and using arrays and collections in VB.NET. There are two basic kinds of VB.NET arrays. Arrays that we declare as array variables of a specific data type by using parentheses after the variable name are normal arrays. We can also use the Array class, which provides us with a new array data type that offers methods for managing items in arrays as well manipulating arrays. Arrays in VB.NET inherit from the Array class in the System namespace, so methods of the Array class can also be used with normal arrays.

In this section, we discuss normal arrays along with some methods of the Array class. In VB.NET, all arrays are zero-based. This is important to keep in mind, especially when working with Excel objects or Classic VB code that may have 1-based arrays. We already showed one way to use an array in Listing 24-3, where we used an array to populate a combo box control. In Listing 24-16 we use the same approach to populate a list box control and then add the selected items to an array.

Listing 24-16. Populate an Array with Selected Items from a List Box

Private Sub Button1_Click(ByVal sender As System.Object, _
                            ByVal e As System.EventArgs) _
                            Handles Button1.Click

'Make sure that at least one item is selected.
If Me.ListBox1.SelectedIndex <> -1 Then

   'Grab the number of selected items.
   Dim iCountSelectedItems As Integer = _
       Me.ListBox1.SelectedItems.Count - 1

   'Declare and dimension the one-dimensional array.
   Dim sArrSelectedItems(iCountSelectedItems) As String

   'Populate the array.
   For iCountSelectedItems = 0 To iCountSelectedItems
      sArrSelectedItems(iCountSelectedItems) = _
      Me.ListBox1.SelectedItems(iCountSelectedItems).ToString()
   Next iCountSelectedItems

  'Show the number of items in the array.
   MessageBox.Show(CStr(sArrSelectedItems.GetLength(0)))

   'Show the lower bound of the array.
   MessageBox.Show(CStr(sArrSelectedItems.GetLowerBound(0)))

   'Show the upper bound of the array.
   MessageBox.Show(CStr(sArrSelectedItems.GetUpperBound(0)))

   'Iterate through the array and display each value.
   For iCountSelectedItems = sArrSelectedItems.GetLowerBound(0) _
                            To sArrSelectedItems.GetUpperBound(0)
       MessageBox.Show(text:= _
                  sArrSelectedItems(iCountSelectedItems).ToString())
   Next iCountSelectedItems

End If

End Sub

When working with arrays we should always specify which dimension we are targeting. Since we are working with a one-dimensional array in this example, the dimension we are targeting is zero.

One of the more resource-intensive processes in VB development is redimensioning arrays, so we should always look for ways to reduce or eliminate this process. Listing 24-16 shows how VB.NET allows us to do this easily. We first retrieve the number of selected list items and then declare and dimension the array all at once. Note that in Listing 24-16 we use the GetLowerBound and GetUpperBound methods to return the lower bound and upper bound index values for the array. Both these methods are part of the Array class. In some scenarios we may not know the bounds for an array initially, but we can get the necessary information later. Listing 24-17 shows how we can initialize an array after declaring it.

Listing 24-17. Declare an Array and Initialize It Later

Dim iNumberOfHouses() As Integer
...
...
iNumberOfHouses = New Integer() {10, 15, 20}

Listing 24-16 shows one way to iterate an array, but we could actually enumerate it as shown in Listing 24-18.

Listing 24-18. Enumerating an Array

Dim iNumberOfHouses() As Integer = {10, 15, 20}
Dim iItem As Integer

For Each iItem In iNumberOfHouses
    Debug.WriteLine(iItem)
Next iItem

The Array class also provides methods that allow us to manipulate the items in different ways. Among the more common actions we might want to perform on an array are reversing the order of items in the array, sorting the array, removing items from the array, returning specific array items, and copying items from one array to another. Listing 24-19 shows how to perform these operations using methods of the Array class.

Listing 24-19. Methods of the Array Object

Dim sArrProjects() As String = _
                  {"Upgrade","Investment", "Maintenance"}

Array.Reverse(sArrProjects)

Array.Sort(sArrProjects)

Array.Clear(sArrProjects, 0, 1)

Dim sItem As String = sArrProjects.GetValue(1).ToString()

Dim sArrProjectsCopy(sArrProjects.GetLength(0)) As String

Array.Copy(sArrProjects, sArrProjectsCopy, _
                     sArrProjects.GetLength(0))

The first example shows how to reverse the order of the items in an array. The second example sorts the array in ascending order. The third example shows how to delete the first item from an array. Note that deleting an item from an array in this manner does not resize the array or move any of the other items into new positions in the array.

To get a specific item value from an array you use the GetValue method, as shown in the fourth example. And as the final example shows, we can even copy one array to another using the Copy method. The last argument of this method allows us to specify the number of items to be copied. This can be a good alternative to the redimension approach when resizing an array. In this example we copy all items from the first array into the second array.

Next we demonstrate how to search for a value in an array using the BinarySearch method. This method is useful when you want to determine whether a specific value exists in an array. To use this method the items in the array must be sorted. The result of executing the BinarySearch method is an integer that represents the index number of the value you are searching for within the array. If the result is -1 the value you are searching for does not exist. If the value you are searching for exists more than once within the array, the index number of the last occurrence is returned.

Listing 24-20 shows how to use the BinarySearch method to locate the index number of an item in an array. There are also several other methods of the array object that allow us to find specific items and work with them in various ways.

Listing 24-20. The BinarySearch Method

Dim sArrProjects() As String = _
    {"Upgrade", "Investment", "Maintenance"}

Dim sSearchedValue As String = "Investment"

Array.Sort(sArrProjects)

Dim iSearchedIndex As Integer = _
    Array.BinarySearch(sArrProjects, sSearchedValue)

MessageBox.Show(CStr(iSearchedIndex))

A good alternative to the normal array is the ArrayList class, which is part of the System.Collection namespace. By using this class we can dynamically increase a list, hold several different data types in one list, manipulate the elements in a list, and manipulate ranges of elements in one operation. The ArrayList is something of a hybrid between the Array and Collection objects. In Listing 24-21 we demonstrate the use of an ArrayList object.

Listing 24-21. Working with the ArrayList Object

Dim Arrlst As New ArrayList(7)
Dim oArrlstObject As Object = Nothing

Debug.Print(Arrlst.Capacity.ToString())

With Arrlst
       .Add("Dennis")
       .Add(True)
       .Add(12)
End With

Debug.Print(Arrlst(1).GetType.ToString())

Dim sNames() As String = {"Rob Bovey", _
                            "Stephen Bullen", _
                            "John Green", _
                            "Dennis Wallentin"}

Arrlst.AddRange(sNames)

Arrlst.RemoveRange(0, 3)

Arrlst.TrimToSize()

Debug.Print(Arrlst.Capacity.ToString())

For Each oArrlstObject In Arrlst
      Debug.Print(oArrlstObject.ToString())
Next oArrlstObject

Me.CheckedListBox1.DataSource = Arrlst

We first create a new ArrayList object and dimension it to hold seven items. Expanding an ArrayList is a resource-intensive process, so we want to try and create it with the capacity to hold as many items as we will need. The first debug print command gives us the current capacity of the ArrayList. We then populate the ArrayList object with items that represent different data types, in this case a string value, a boolean value, and an integer value. To verify that the ArrayList actually holds different data types we print the data type of the second item to the Immediate window using the GetType method.

Next we add a range of values to the ArrayList using the AddRange method. Our ArrayList already has the capacity to hold these new items, but if an ArrayList does not have sufficient capacity to hold the number of items being added it automatically expands itself. The RemoveRange method enables us to remove several items at once, so next we use this method to remove the first three items we added to it. At this stage the ArrayList object still has a capacity of seven items, but since we no longer need them all we resize it by using the TrimToSize method. Using the debug print command to check the capacity of the ArrayList after resizing it should show a capacity of four items. Just to check which values the ArrayList now holds we iterate over all its items using a For...Each loop. Finally, the collection of items in the ArrayList is added to a CheckedBoxList control.

In addition to the ArrayList, the .NET Framework provides additional data structures like Stack and Queue. The Stack class is a data structure that allows adding and removing objects from one position only. This position is referred to as the "Top" of the stack. The last object placed on the stack is the first one to be removed. This is a Last In First Out (LIFO) data access method. The Queue class is a data structure that allows us to add objects to the back and remove objects from the front. This is a First in First Out (FIFO) data access method.