Redesigning an Excel VBA Solution for .NET Using Visual Studio 2005 Tools for Office

Article
05/23/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Dale Oberg, ArtinSoft.com

Revised: January 2006

Applies to: Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Visual Basic for Applications, Microsoft Visual Basic 2005, Microsoft Office Excel 2003

Summary: Learn about redesigning an Excel VBA solution to take full advantage of Visual Studio 2005 Tools for Office and the .NET Framework. This article looks at the rearchitecture of the Trey Research Internet Information Services (IIS) Log Analyzer solution. (22 printed pages)

Download OfficeVSTOExcelVBARearchitecture.msi.

Contents

Overview

Setup Requirements

IIS Log System Overview

Trey Research IIS Log Analyzer Solution Overview

Why Redesign the IIS Log Analysis Solution?

Rearchitecting VBA-Style Code to Work Better with .NET Memory Management

Rearchitecting ADO Code to Use ADO.NET

Rearchitecting Error Handling

Controlling Implicit Conversions with Option Strict

Using Operators

Rearchitecting to Use Early Binding

Rearchitecting to Use Native .NET Framework Library Calls

Rearchitecting to Use Native .NET Framework Calls

Redesigning the User Interface

Code Security

Conclusion

Additional Resources

About the Author

Overview

Microsoft Visual Basic for Applications (VBA) is the programming environment and language for Microsoft Office that has been available for several years. Microsoft Office 2003 has a new environment for building applications called Microsoft Visual Studio Tools for the Microsoft Office System, which is based on Microsoft .NET.

Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003 offered many advantages over VBA, but Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office) enhances these features for even more user productivity.

This article analyzes many solution rearchitecture issues that you may face when rearchitecting a VBA solution to Visual Studio 2005 Tools for Office using the Visual Basic language. There are two previous articles in this series: Convert VBA Code to Visual Basic When Migrating to Visual Studio 2005 Tools for Office, which focuses on many low-level coding issues for migrating from VBA to Visual Basic 2005, and Migrating Excel VBA Solutions to Visual Studio 2005 Tools for Office, which focuses on migrating a VBA solution directly to Visual Studio 2005 Tools for Office.

The migrated Trey Research Internet Information Services (IIS) Log Analyzer Visual Studio 2005 Tools for Office solution was a direct migration of the VBA solution. This article takes the Trey Research IIS Log Analyzer solution and further enhances it to take full advantage of Visual Studio 2005 Tools for Office and the Microsoft .NET Framework.

Setup Requirements

To use the solution accompanying this article you must have the following software installed:

Microsoft Visual Studio 2005 Tools for the Microsoft Office System
Microsoft Office Excel 2003 or Microsoft Office Professional Edition 2003 SP1 (complete installation)

Installation stepsH8

Install Microsoft Visual Studio 2005 Tools for Office.
Perform a complete installation of Microsoft Office Professional Edition 2003.

Note

Note Only a complete installation includes the primary interop assemblies.
Download and install the package associated with this article.

Create the database to store the log informationH8

Note

Important If you have already created the TreyResearchIISLog database in Microsoft SQL Server, then the following steps for creating the database are not necessary.

On the Start menu, click Run.
Type cmd, and then click OK to open a command prompt window.

Change to the directory where the solution is installed, for example:

cd %userprofile%\My Documents\Visual Studio 2005\Projects\Excel VBA Rearchitecture Sample

Execute the script to create the TreyResearchIISLog SQL Server sample database:
```
CreateIISLogDatabase.bat ServerName[\Instance]
```
If your server is not (local), then replace ServerName and Instance in the command with the name of your server and instance. For example, with Microsoft SQL Server 2005 Express, the default name and instance is .\SQLExpress.
```
CreateIISLogDatabase.bat .\SQLExpress
```

Run the VBA solutionH8

Browse to the folder where you installed the solution.

By default, the path to the folder is My Documents\Visual Studio 2005\Projects\Excel VBA Rearchitecture Sample.
Open the VBAIISLogAnalysis folder, and then double-click IISLogAnalysis.xls to open the VBA solution in Excel.
If your SQL Server is not (local), select the Setup worksheet, and then change the name of the data source in cell B9.

Note

Note The default name and instance for SQL Server 2005 Express is .\SQLExpress.
On the IIS Log Analysis worksheet, press Ctrl + R to display the Report Criteria form.
Click Add IIS Log File to Database.
Browse to select the sample log file TreyResearchIISLog.log, and then click Open.

The sample log file is installed by default to My Documents\Visual Studio 2005\Projects\Excel VBA Rearchitecture Sample. If the import is successful, you receive a message indicating the number of imported rows.
Make your report criteria selections, and then click Run Report to show the analysis of the log data.

Run the redesigned Visual Studio 2005 Tools for Office solutionH8

Browse to the folder where you installed the solution.

The solution is installed by default to My Documents\Visual Studio 2005\Projects\Excel VBA Rearchitecture Sample.
Open the VSTOIISLogAnalysis folder, and then double-click IISLogAnalysis.sln to open the solution in Visual Studio 2005 Tools for Office.
If your SQL Server is not (local), then in Solution Explorer, right-click Sheet2.vb and select View Designer, change the name of the data source in cell B8, and then select the IIS Log Analysis worksheet tab.

Note

The default name and instance for SQL Server 2005 Express is .\SQLExpress.
Press F5 to build and run the solution.
If you have not already imported an IIS log file, then click Add IIS Log File to Database in the actions pane, browse to select the sample log file TreyResearchIISLog.log, and then click Open.

By default, the file is installed to My Documents\Visual Studio 2005\Projects\Excel VBA Rearchitecture Sample. If the import is successful, you receive a message indicating the number of imported rows.
Make your report criteria selections in the actions pane, and then click Run Report to show the analysis of the log data.

IIS Log System Overview

Internet Information Services (IIS) can log information about each hit on a Web site. You turn logging on for a Web site in the Internet Information Services management console. You can choose from several variations of the IIS log file. The Trey Research IIS Log Analyzer solution supports the default Microsoft IIS Log File Format. For more information on IIS log file formats, see IIS Log File Formats.

The Microsoft IIS Log File Format is a comma-delimited text file in which each line has the following format:

Client IP, Username, Hit Date, Hit Time, Service Instance, Computer Name, Server IP, Time Taken, Bytes Sent, Bytes Received, Service Status Code, Windows Status Code, Request Type, Target URL, Parameters

A single line of an IIS log file might look like the following:

172.16.255.255, anonymous, 03/20/04, 23:58:11, MSFTPSVC, SALES1, 172.16.255.255, 60, 275, 0, 0, 0, PASS, /Intro.htm, -,

To turn logging on for a specific Web site

On the Start menu, click Control Panel, point to Administrative Tools, and then double-click the Internet Information Services file to view the Internet Information Services management console.

Note

If the Internet Information Services file does not exist, then it must be installed.
Right-click the Web site for which you wish to enable logging, and click Properties.
In the Properties dialog box, click the Web Site tab, and then select the Enable Logging check box.
In the Active Log Format list, select Microsoft IIS Log File Format, and click OK.

Trey Research IIS Log Analyzer Solution Overview

The Trey Research IIS Log Analyzer solution is a Microsoft Office Excel-based VBA program that acts as a reporting analysis tool for IIS logs. The program has a Report Criteria form to specify the criteria to use to import and analyze the data that is retrieved from the database and copied into the Excel workbook. The solution creates a bar chart at the top of the Excel worksheet to graph the top seven rows in the worksheet report cells, as illustrated in Figure 1.

Figure 1. VBA Trey Research IIS Log Analyzer Excel workbook

The solution can import an existing IIS log file in the Microsoft IIS Log File Format.

To import a log fileH9

Open the Trey Research IIS Log Analyzer solution.
Press CTRL+R to open the Report Criteria form, as shown in Figure 2.

Figure 2. VBA Report Criteria user form
On the form, click Add IIS Log File To Database, and then select the log file to import it.

All the imported log files are added to one SQL Server database table.

After you import IIS log files into the solution, you can run various reports from the Report Criteria form, and the results are copied to the worksheet. The graph at the top of the worksheet is updated to reflect the new data.

The main purpose of this solution is to show how to migrate a VBA application from VBA to Visual Studio 2005 Tools for Office and to provide a simplified, real-world example of analyzing IIS logs. However, you can enhance the features of the solution and use it as a base for building a custom, in-house IIS log analysis tool to view your Web sites' log files in any manner you wish. For example, one enhancement to the solution could be to acquire an IP demographic database and provide demographic reports for all of the hits on your Web sites.

Why Redesign the IIS Log Analysis Solution?

Visual Studio 2005 Tools for Office used together with the .NET Framework creates a rich environment that provides a wealth of functionality for application programmers. Directly migrating the IIS Log Analyzer application to Visual Studio 2005 Tools for Office offers the many advantages of running the solution in the new environment. Rearchitecting the IIS Log Analyzer application at the code level and the user-interface level can help you to better use the Visual Studio 2005 Tools for Office programming environment, providing many benefits to end users and application developers.

Some of the architecture changes that can be made at the code level are described in the following sections.

Changing Visual Basic Code to Work Better with .NET Memory ManagementH8

The VBA runtime uses a reference-based memory management scheme. This means that each object in memory contains a counter which tracks how many objects are referencing it. The common language runtime (CLR) uses a garbage collection memory management scheme, which analyzes objects in memory to determine if any references exist to specific objects. VBA code that was written for a reference-based memory management scheme can be optimized to work better with a garbage collection memory management scheme. When VBA code is directly migrated to Visual Studio 2005 Tools for Office, it often still uses syntax that was a result of working in the VBA memory management mechanism.

Using ADO.NETH8

ADO.NET is the managed .NET library for accessing databases, and is completely integrated into the .NET Framework. ADO.NET is written in managed code so there is no overhead of COM interop when calling it. One of the most useful features of ADO.NET is the support of DataSets. DataSets contain the results of database queries and are easily integrated into Microsoft Windows Forms for displaying interactively to the user. For example, the DataGridView Windows Forms control can accept a DataSet for its DataSource property and automatically display the contents of one of the DataTables in the DataSet.

Using .NET Structured Exception HandlingH8

Although both VBA and Visual Basic support the On Error Goto Label error handling mechanism, it is often better to use .NET structured exception handling in Visual Basic. Microsoft .NET exception handling is built into the CLR and is the exception-handling mechanism used by the entire .NET Framework.

Turning On Option StrictH8

Visual Basic has an Option Strict compiler switch that can be set to On or Off. When Option Strict is on, stricter type checking is performed at compile time, which can alleviate a significant amount of run-time conversion errors.

Use Early BindingH9

Although Visual Basic supports both early and late bindings of data types, it is more optimal to convert data types to use early binding whenever possible. This adds better compile-time type checking and increases run-time performance.

Using Native .NET Framework Library CallsH8

When migrating a VBA solution to Visual Studio 2005 Tools for Office, you can use the same VBA library calls for a lot of code. It is frequently more optimal to translate these calls to native .NET Framework library calls, for performance improvements and for better integration into the .NET Framework.

Using Native .NET Library ClassesH8

Calls to Microsoft Win32 can be made from both VBA and Visual Basic solutions. Although this is allowed, the .NET Framework provides a significant number of functions that provide similar or identical functionality. For better integration with the .NET Framework, it is optimal to call the native .NET library, rather than Win32 functions. Native .NET library calls are less error-prone to code because more strict type checking is performed than with direct Win32 calls.

Some of the architecture changes that can be made at the user-interface level are described in the following sections.

Using the Actions Pane to Display Additional InformationH8

The actions pane allows additional information to be displayed in a Microsoft Word or Excel document while the user is interacting with the document. It can be very useful in certain circumstances, to assist the user in making decisions or seeing detailed information that would otherwise require a modal pop-up window, if the task pane was not available.

Taking Advantage of Windows Forms ControlsH8

Visual Studio 2005 Tools for Office provides a rich set of Windows Forms controls that were not available previously in VBA. Some Windows Forms controls, such as the Menu and MenuBar controls, can be set up to have the Office 2003 look and feel. There are also a many third-party components available for .NET, if specialized controls are necessary.

Rearchitecting VBA-Style Code to Work Better with .NET Memory Management

VBA solutions often have lines of code that are similar to the following:

[VBA]
Set myObject = Nothing

One of the main purposes for setting an Object variable to Nothing is to decrement the reference counter to an object, so that the VBA memory manager reclaims the memory when the reference count is zero. However, in Visual Basic, this line of code may not perform any meaningful purpose because the CLR has a garbage collection memory management system and not a reference counting memory management system. Removing lines of code like the one above may be the most optimal way to code in Visual Basic. The VBA reference counting memory management sometimes had problems with circular references, unlike Visual Basic memory management, which should never have such problems.

Visual Basic disposes of objects by using a separate execution thread in which the garbage collector runs. Under specific circumstances, like when available memory is low, the garbage collector temporarily stops the main execution thread and analyzes the managed memory in the current program. Any objects that are no longer used are finalized and their memory is freed. The CLR has a sophisticated mechanism for heap management and garbage collection that is beyond the scope of this paper. For more details, see Garbage Collection: Automatic Memory Management in the Microsoft .NET Framework and Garbage Collection — Part 2: Automatic Memory Management in the Microsoft .NET Framework.

What an application programmer should know is that all Visual Basic objects have a Finalize method such as the following:

Protected Overrides Sub Finalize()
    ' Perform any final cleanup code here
End Sub

The Finalize method is called a non-deterministic destructor because you do not know exactly when it will be called by the garbage collection thread. Sometimes it does not matter when the Finalize method is called because the resources that the object holds just need to be freed before the program ends. However, there are often times when you want to immediately free resources that an object holds but not free its actual memory. The following Dispose method can be called by the main application thread on any object that implements the IDisposable interface:

Public Overloads Sub Dispose()
    MyBase.Dispose()
    fileStream.Close()
End Sub

In this specific instance, the fileStream object is closed to immediately free up any file handles that the fileStream object has open. Even after Dispose is called, the actual memory of the object may not be freed until the garbage collector frees it.

The following code is a recommended pattern to follow when creating your own classes that derive from IDisposable. For example, if you build your own Windows Forms control, the pattern you could use would be:

Public Class MyTextBox
    Inherits System.Windows.Forms.TextBox

    Public Sub New()
    End Sub

    Protected Overrides Sub Finalize()
        DoDispose(False)
    End Sub

    Public Overloads Sub Dispose()
        MyBase.Dispose()
        DoDispose(True)
    End Sub

    Private Sub DoDispose(ByVal isDisposing As Boolean)
        If isDisposing = True Then
            GC.SuppressFinalize(Me)
        End If
        ' Free resources here
    End Sub
End Class

Rearchitecting ADO Code to Use ADO.NET

Microsoft has created several database access libraries over the past several years including ODBC, DAO, RDO, and ADO. The newest database access library is called ADO.NET and is written entirely in managed Microsoft .NET code. ADO.NET is a large subject and many authors have written about it. This article focuses on giving an overview of ADO.NET and provides a comparative analysis between ADO and ADO.NET.

The intention of ADO was to provide database access in an always-connected state. Although ADO does supply a means for disconnected recordsets, the vast majority of VBA programmers use ADO-connected recordsets. The disconnected recordsets that ADO does provide are not integrated into the .NET Framework as well as the native ADO.NET classes. Connected recordsets work well on an internal company network where everyone has direct access to the company database. But, for Internet-based programs, always-connected programming models are not very useful. ADO.NET was designed with Internet applications in mind, where a user needs to get access to data and only needs to open a database connection temporarily, work with it from the client, and then send any changes back to the server. This is often referred to as a "disconnected model" because it uses database connections for only short periods of time. The disconnected model is much more scalable and flexible because a database connection is not needed for each user but can instead be shared among users.

Following is a list of classes available in ADO.NET for SQL Server data providers. (Other providers are also available for other databases, with almost identical classes available. For example, the OLEDB Data provider has classes called OleDbConnection, OleDbCommand, OleDbParameter, OleDbDataAdapter, and OleDbDataReader.)

SqlConnection. Usethis class to obtain a connection to a database.
SqlCommand. Use this class to access data by storing a string that tells the data source what to do. A command can execute SQL statements or call stored procedures.
SqlParameter. Use this class to send parameters to stored procedures or SQL commands.
SqlDataAdapter. Thisclass is a link between a data source and data objects.
SqlDataReader. Use thisclass for forward-only, read-only access to a database and when you need maximum performance.
DataSet. An in-memory collection of DataTables. The DataSet has many of the attributes of an in-memory database.
DataTable. Stores all of the records obtained from a database query as a collection of DataRows. Also contains a collection of DataColumns, which define each column in the DataTable.
DataRelation. Use this class to create a link between two DataTables in a DataSet.
DataRow. A row in a DataTable. A DataRow is similar to the concept of a record in a database table.
DataColumn. A column in a DataTable. A DataColumn is similar to the concept of a field in a database table.

How to Migrate ADO Code to ADO.NETH8

In the migrated IIS Log Analyzer solution there is a function called CreateDBTableArray, which returns a generic Object type. The actual value returned is a two-dimensional array initially created by calling the GetRows method of a recordset.

[Visual Basic migrated code]
Private Function CreateDBTableArray(ByRef sqlString As String, 
Optional ByRef sqlString2 As String = "") As Object

When we rearchitected this function to utilize ADO.NET, we changed the Object return type to be an ADO.NET DataSet, as illustrated in the following code.

[Visual Basic rearchitected code]
Private Function CreateDataSet(ByRef sqlString As String, 
Optional ByRef sqlString2 As String = "") As DataSet

A DataSet can contain multiple DataTables, but this function only adds one DataTable to the DataSet.

An ADO connection can be directly converted to be an ADO.NET connection. For example, the following code:

[Visual Basic migrated code]
Dim connect As New ADODB.Connection
connect.connectionString = conString
connect.Open()

Can be converted to:

[Visual Basic rearchitected code]
Dim connect As New SqlConnection
connect.ConnectionString = conString

The connection string also needs to be changed using the ADO.NET format, rather than the ADO format. For example, this ADO connection string:

"Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TreyResearchIISLog;Data Source=(local)"

Can be changed to this ADO.NET connection string:

"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TreyResearchIISLog;Data Source=(local)"

The following code, from the migrated IIS Log Analyzer solution, was converted to the equivalent statements in ADO.NET. Note that the rearchitected code is declaring a DataSet rather than an ADODB.Recordset and that only one command variable is needed in the converted code because ADO.NET can append SQL Strings, which was not possible in the code using ADO.

[Visual Basic migrated code]
Dim cmdCommand As New ADODB.Command
Dim cmdCommand2 As New ADODB.Command
Dim recSet As New ADODB.Recordset
cmdCommand.ActiveConnection = connect
cmdCommand.CommandText = sqlString
cmdCommand.CommandType = ADODB.CommandTypeEnum.adCmdText

[Visual Basic rearchitected code]
Dim cmdCommand As New SqlCommand
Dim logDataSet As New DataSet
Dim dataAdapter As New SqlDataAdapter
cmdCommand.Connection = connect
cmdCommand.CommandText = sqlString

Executing an ADO command with two SQL statements requires more code than the ADO.NET equivalent. This is because it is necessary to create two ADODB.Command objects but only one ADO.NET Command object is required. The ADO.NET Command object allows the two SQL strings to be concatenated and executed with only one call to the database.

[Visual Basic migrated code]
If sqlString2 <> "" Then
   cmdCommand.Execute()
   cmdCommand2.ActiveConnection = connect
   cmdCommand2.CommandType = ADODB.CommandTypeEnum.adCmdText
   cmdCommand2.CommandText = sqlString2
   recSet = cmdCommand2.Execute()
Else
   recSet = cmdCommand.Execute()
End If
Dim rowArray As Object = recSet.GetRows()

[Visual Basic rearchitected code]
If sqlString2 <> "" Then
    cmdCommand.CommandText = cmdCommand.CommandText & "; " & sqlString2
End If
cmdCommand.CommandType = CommandType.Text
dataAdapter.SelectCommand = cmdCommand
dataAdapter.Fill(logDataSet)

To check to see if no rows were returned from the database query, the ADO code checks the EOF property in the returned Recordset, but the ADO.NET code checks the number of rows in the first DataTable in the DataSet. The following code illustrates how the code is converted from ADO to ADO.NET.

[Visual Basic migrated code]
' No rows found
If recSet.EOF = True Then
' This is set to nothing. Not for memory management reasons, but so that
' the Nothing value can be checked in the calling function
   CreateDBTableArray = Nothing 
   connect.Close()
   connect = Nothing
   Exit Function
End If

[Visual Basic rearchitected code]
' No rows found
If logDataSet.Tables("IISLogQueryResults").Rows.Count = 0 Then
' This is set to nothing. Not for memory management reasons but so that
' the Nothing value can be checked in the calling function
   CreateDataSet = Nothing
   Exit Function
End If

ADO.NET DataSets automatically place all the column names in each DataTable, which is very convenient. The array returned from the GetRows function in the Recordset does not have column names in the array; so, the array needs to be ReDimmed to be one row longer and the column names are then placed in the first row. Therefore, the following ADO code is not needed in the ADO.NET solution.

[Visual Basic migrated code]
ReDim Preserve rowArray(UBound(rowArray, 1), UBound(rowArray, 2) + 1)

' Move all the rows down 1 to make room for field names
For Column As Object = 0 To UBound(rowArray, 1)
For Row As Object = UBound(rowArray, 2) To 1 Step -1
       rowArray(Column, Row) = rowArray(Column, Row - 1)
   Next Row
Next Column

' Add field names
For Column As Object = 0 To recSet.Fields.Count - 1
rowArray(Column, 0) = recSet.Fields(Column).Name
Next Column

ADO connections need to be programmically closed after they are no longer needed. The Fill function of the ADO.NET DataAdapter closes the connection to the database by default. For example:

[Visual Basic migrated code]
CreateDBTableArray = rowArray
connect.Close()

[Visual Basic rearchitected code]
CreateDataSet = logDataSet

Table 1 displays the fundamental ADO to ADO.NET SQL Server data provider mappings, which can be used for rearchitecting your own solutions.

Table 1. ADO to ADO.NET SQL Server data provider mappings

ADO	ADO.NET
Connection	SqlConnection
Command	SqlCommand
Command.Parameters	SqlParameters
Recordset	DataTable, DataReader
Fields Collection	DataColumn
ConnectionString	ConnectionString
Command.CommandText	CommandText
Command.CommandType	CommandType
CreateParameter()	CreateParameter(), Parameters.Add()
Execute()	ExecuteQuery(), ExecuteReader()
RecordCount	DataTable.Rows.Count
Recordset.Fields(indexValue)	DataRow(indexValue)
Recordset.Fields(FieldName)	DataRow(FieldName)

Rearchitecting Error Handling

Error handling in VBA code is usually done with On Error GoTo Label constructs, similar to the following:

[Visual Basic migrated code]
On Error Goto ErrorHappened
    GetRecordsFromDatabase
    Goto NoError:
ErrorHappened:
    MsgBox( "Error in getting records ")
    CloseDatabase
    Exit Sub
NoError:
CloseDatabase

There are a few variations of how to implement error handling in VBA code, but the above construct is common. Although Visual Basic supports the On Error syntax, it is often better to convert your VBA error handling to Visual Basic structured exception handling. The VBA code shown just previously could be converted to:

[Visual Basic rearchitected code]
Try
    GetRecordsFromDatabase
Catch ex As Exception
    MsgBox( "Error in getting records: " + ex.Message )
Finally
    CloseDatabase

The rearchitected Visual Basic code is shorter and has a better structure. When you use On Error and GoTo code in Visual Basic, it can become unwieldy.

Table 2 displays mappings for converting from standard VBA error handling to structured exception handling in Visual Basic.

Table 2. VBA and Visual Basic exception handling mapping

VBA	Visual Basic structured exception handling	Description
Err.Description	ex.Message	Error message description
Err.Source	ex.TargetSite	Method where the exception occurred
Err.HelpFile	ex.HelpLink	Location of Help information for this exception

VBA has Err.Number (the error number) and Err.HelpContext (the number of the help file); neither has an equivalent in Visual Basic.

Table 3 displays some of the properties available in .NET structured exception handling that do not have equivalents in standard Visual Basic exception handling.

Table 3. Visual Basic exception properties with no equivalent VBA-style language mapping

Visual Basic exception property	Description
ex.StackTrace	The stack trace of the functions called before the exception was thrown
ex.Source	Application name where the exception occurred
ex.InnerException	Nested exception

Controlling Implicit Conversions with Option Strict

Option Strict is a setting in Visual Basic that lets you control whether implicit conversions are allowed. When Option Strict is set to On, implicit conversions are not allowed. When it is set to Off, they are allowed.

For example, the following code compiles with no problems because implicit conversions from Long to Integer are allowed with Option Strict set to Off.

[Visual Basic migrated code]
Option Strict Off

Public Sub TestOptionStrict()
   Dim myInt As Integer
   Dim myLong As Long = 20000000000
   myInt = myLong
End Sub

However, this code will throw an OverflowException during run time. When Option Strict is set to On, the following code will not compile, which assists in catching bugs earlier.

[Visual Basic migrated code] 
Option Strict On
Public Sub TestOptionStrict()
   Dim myInt As Integer
   Dim myLong As Long = 20000000000
   myInt = myLong ' compile error: Option Strict On disallows implicit
   ' conversions from Long to Integer.
End Sub

By default, the Visual Basic Upgrade Tool sets Option Strict to Off. On a case by case basis, you can decide to rework code files for Option Strict to be turned On. The code could be converted to replace the Integer type with a Long type so that no implicit conversion happens, as in the following example:

[Visual Basic rearchitected code] 
Option Strict On
Public Sub TestOptionStrict()
   Dim myLong1 As Long
   Dim myLong2 As Long = 20000000000
   myLong1 = myLong2
End Sub

In the Trey Research IIS Log Analyzer solution, we converted the BusinessLogicLayer.vb class to allow Option Strict to be turned on. In your own projects, you can do the same to take full advantage of compile time type checking.

Using Operators

Visual Basic 2005 has many operators that make coding easier and that can sometimes enhance run-time performance. These operators include the OrElse and AndAlso comparison operators and &=, |=, +=, -=, /=, *= assignment operators.

OrElse and AndAlso are logical comparison operators, which differ from the Or and And operators. The Or and And operators are bitwise operators, which mean that they take both of their operands and perform a bitwise AND or OR on each bit position to produce the result. The OrElse and AndAlso operators check only to see if the operands are True or False and do not do a bitwise comparison. Also, only the first operand is checked every time; the second operand is optionally checked. This leads to higher performance for comparisons, so the OrElse and AndAlso operators should be used wherever logical comparisons are needed. For example, consider the following code:

While inputStream.EndOfStream = False AndAlso logLine.Trim().Length > 0

If inputStream.EndOfStream equals True, the second operand is never analyzed.

The &=, |=, +=, -=, /=, *= assignment operators do not change any functionality of existing code, but they do provide more programmer convenience. For example:

' Migrated Visual Basic Code 
numberLines = numberLines + 1

Has the same functional behavior as the following, which adds 1 to numberLines and stores the result in numberLines:

' Rearchitected Visual Basic Code 
numberLines += 1

This style of operators can also be used with strings. For example:

[Visual Basic migrated code] 
connectionString = connectionString & _
   currentSettingName & "=" & currentSettingValue

Can be restructured to the following:

[Visual Basic rearchitected code] 
connectionString &= currentSettingName & "=" & currentSettingValue

These segments of code are functionally equivalent.

Rearchitecting to Use Early Binding

In VBA, it is common for many programmers to use variables or parameters without declaring them to be a specific data type. When a variable or parameter is declared without a type in VBA it is assigned a Variant type by default and is considered to be "late bound." Consider the following code:

' Late-bound VBA code
Public Function AppendStringNTimes( s, Counter )
    If Counter = 0 Then
        AppendStringNTimes = s
    Else
        AppendStringNTimes = s & AppendStringNTimes( s, Counter - 1 )
    End If
End Function

When this code is directly migrated to Visual Basic, the undeclared variables are declared as Object types, as shown in the following:

' Late-bound Visual Basic code
Public Function AppendStringNTimes( s As Object, Counter As Object )
    If Counter = 0 Then
        AppendStringNTimes = s
    Else
        AppendStringNTimes = s & AppendStringNTimes( s, Counter - 1 )
    End If
End Function

One major issue with using late bound variables is that type checking is delayed until run time, which can produce some unexpected errors. Another major issue with late binding is that performance is somewhat slower because the runtime is continually checking for types. A more optimal way to write code is to use early binding, which is to make sure that all types are explicitly defined, as in the following:

' Early bound Visual Basic code
Public Function AppendStringNTimes( s As String, Counter As Integer )
    If Counter = 0 Then
        AppendStringNTimes = s
    Else
        AppendStringNTimes = s & AppendStringNTimes( s, Counter - 1 )
    End If
End Function

This code has compile-time checking of types and performs better.

Rearchitecting to Use Native .NET Framework Library Calls

The Microsoft.VisualBasic.Compatibility.VB6 namespace in the .NET Framework contains methods that mimic functionality in VBA and Visual Basic 6.0. This style, which is present in VBA and Visual Basic, is hereafter referred to as "VBA-style." Many of the methods in the classes in Microsoft.VisualBasic.Compatibility.VB6 are wrappers around other parts of the .NET Framework. If the Visual Basic Upgrade Tool is used to migrate part of a VBA project to Visual Studio 2005 Tools for Office, then the Visual Basic Upgrade Tool uses the Microsoft.VisualBasic.Compatibility.VB6 namespace for many of the functions that are migrated to Visual Basic code. The Microsoft.VisualBasic.Compatibility.VB6 namespace is a functionally equivalent way to migrate code to Visual Basic, but a more optimal way is to directly use native classes and methods available in the .NET Framework. This provides a small performance improvement and the code is written in a manner that is more consistent with code written explicitly for the .NET Framework. The following tables show many of the function mappings for rearchitecting your code from VBA-style coding to the .NET style of coding. There are many more mapping than these, but these illustrate the most common mappings.

Table 4. File access mappings

VBA-style	.NET Framework
ChDir, ChDrive	Directory.SetCurrentDirectory()
CurDir	Directory.GetCurrentDirectory(), Directory.GetDirectoryRoot()
Dir	File.Exists(), Directory.GetFiles()
FileCopy	File.Copy()
FileDateTime	File.GetCreationTime(), File.GetLastAccessTime(), File.GetLastWriteTime()
FileLen	FileInfo.Length
GetAttr	File.GetAttributes()
Kill	File.Delete()
MkDir	Directory.CreateDirectory()
Name	Directory.Move(), File.Move()
RmDir	Directory.Delete()
SetAttr	File.SetAttributes()

Table 5. String management mappings

VBA-style	.NET Framework
FormatCurrency, FormatDateTime, FormatNumber, FormatPercent	stringVar.Format()
GetChar	stringVar.Chars()
InStr	stringVar.IndexOf(), stringVar.StartsWith(), stringVar.EndsWith()
InStrRev	stringVar.LastIndexOf
Join	stringVar.Join()
LCase	stringVar.ToLower()
Left	stringVar.SubString()
Replace	stringVar.Replace()
Right	stringVar.SubString()
RSet	stringVar.PadLeft()
RTrim	stringVar.TrimEnd()
Split	stringVar.Split()
StrComp	stringVar.Compare()
Trim	stringVar.Trim()
UCase	stringVar.ToUpper()

Table 6. Time mappings

VBA-style	.NET Framework
Time	System.DateTime.Now
Timer	System.DateTime.Now.TimeOfDay.TotalSeconds

Table 7: Array mappings

VBA-style	.NET Framework
UBound(Array)	Array.GetUpperBound (0)
UBound(Array, Dim)	Array.GetUpperBound (Dim-1)
LBound(Array)	Array.GetLowerBound (0)
LBound(Array, Dim)	Array.GetLowerBound (Dim -1)

Table 8: Miscellaneous function mappings

VBA-style	.NET Framework
TypeName(myObject )	myObject.GetType().Name
Val( integerValueString )	System.Convert.ToInt32( integerValueString)
Val( doubleValueString )	System.Convert.ToDouble( doubleValueString )

Rearchitecting to Use Native .NET Framework Calls

Win32 is a 32-bit Windows application programming interface (API). Win32 is made up of functions implemented in dynamically linked libraries (DLLs), which were mainly written in the C programming language. However, the vast majority of Win32 calls use something called the "Pascal calling convention," which is the default when you use a Declare statement. The core DLLs used in Win32 are gdi32.dll, kernel32.dll, and user32.dll. The Win32 API provides access to functionality, including the following:

User interface controls, windows, and message services
Graphics device interface for drawing and printing
Low-level kernel services

VBA code that calls out to the Win32 API continues to work in Visual Basic, but some of the data types may need to be changed. It is better to convert this code to call native .NET libraries when they are available, because direct Win32 calls are more prone to programming errors and type-mismatch errors. The following code shows how to use an external Win32 function, GetTickCount, in VBA and the .NET function in Visual Basic.

[Visual Basic migrated code]
Declare Function GetTickCount Lib "kernel32" () As Integer
Sub APIsDemo()
   Dim tickCount As Integer
   ' Get the number of milliseconds elapsed since the system started
   tickCount = GetTickCount()
End Sub

[Visual Basic rearchitected code]
Sub APIsDemo()
   Dim tickCount As Integer
   ' Get the number of milliseconds elapsed since the system started
   tickCount = System.Environment.TickCount
End Sub

You can convert most Win32 calls to native .NET Framework calls in a similar manner. For a reasonably thorough list of mappings between Win32 and the Microsoft .NET Framework, see: Microsoft Win32 to Microsoft .NET Framework API Map.

Redesigning the User Interface

Visual Studio 2005 Tools for Office offers significantly more user interface (UI) functionality than VBA. A full explanation of all of the capabilities of Windows Forms and the new Windows Forms controls is beyond the scope of this article, but it is worthwhile to investigate them, to utilize them in your own solutions.

Using the Actions Pane in Visual Studio 2005 Tools for OfficeH8

Visual Studio 2005 Tools for Office has the capability to add an actions pane to a Microsoft Office document. The actions pane is a modeless window that is docked to either the top, bottom, left, or right of the Office document. Because it is modeless, it does not interfere with the user interaction of the Office document.

The IIS Log Analyzer solution allows a user to query the IIS Log database and adds the report to a worksheet. The Windows Form used to gather report criteria from the user is modal and it is necessary to click the button on the worksheet to open up the form for every report. The report criteria form is an ideal candidate to have its functionality moved to the actions pane so that it is continually available to the user in a non-modal manner, as illustrated in Figure 3.

Figure 3. Solution with actions pane enhancement

Figure 1 illustrates the actions pane in the rearchitected IIS Log Analyzer solution. The easiest way to add multicontrol content to the actions pane is to create an Actions Pane control and then place all of the Windows Forms controls inside of this new Actions Pane control. You can then add this single Actions Pane control to the actions pane with code like the following:

QueryControl = New IISLogQueryControl()
QueryControl.SetThisApplication(Me.Application)
Me.ActionsPane.Controls.Add(QueryControl)
QueryControl.Dock = DockStyle.Fill

In the rearchitected solution, the actions pane is now filled with the controls that were in the report criteria form.

The following links connect to video tutorials made by Kathleen McGrath about how to program the actions pane. They provide a good overview of the subject.

Code Security

The sample code referred to in this article is intended for instructional purposes; it should not be used in deployed solutions without modifications. In particular, you must consider code security.

To illustrate the simplicity of this sample solution, we created a list of potential threats by using the threat modeling process and tools described in the Threat Modeling section of the Microsoft Security Developer Center.

The following are some examples of the identified threats that you should take into consideration before expanding or deploying this solution.

Table 9. Examples of threats

Threat effect	Entry point	Known mitigation
SQL Server access is compromised	SQL Server database	SQL Server access needs to be managed and secured
SQL Server data is compromised	SQL Server database	SQL Server tables need to be secured
Solution points to wrong database	Workbook	Do not store data source connection information on a worksheet

For more information about code security, visit the Microsoft Security Developer Center.

Conclusion

The previous article in this series, Migrating Excel VBA Solutions to Visual Studio 2005 Tools for Office, illustrated that directly migrating a VBA solution to Visual Studio 2005 Tools for Office offers many advantages. This article illustrates that rearchitecting the solution to fully utilize Visual Studio 2005 Tools for Office and the .NET Framework provides even better integration into the .NET Framework, and offers the opportunity to take full advantage of the richness of Visual Studio 2005 Tools for Office. This two-phase process allows for VBA code to be migrated to Visual Studio 2005 Tools for Office and then enhanced in a manageable way, without the complexity of migration and rearchitecture all in one phase.

The Trey Research IIS Log Analyzer solution is now rearchitected to fully utilize Visual Studio 2005 Tools for Office and the .NET Framework. With the foundation of the solution now native to .NET, it can be enhanced with more features written in Visual Basic and the native .NET Framework (and these features will work seamlessly with the rest of the solution). For example, some features that could be added to the Trey Research IIS Log Analyzer include support for more IIS Log file formats or the ability to process IP demographics so that you can know more about the people who access your Web site. With the rich set of features available in Visual Studio 2005 Tools for Office, adding functionality is significantly easier than with VBA.

Additional Resources

Visual Studio 2005 Tools for Office

Migrating VBA Code

VBA

Microsoft ISV Community Center: Visual Basic for Applications

Microsoft Office Object Models

MigratingH0

Microsoft Visual Basic Developer Center: Upgrading to Visual Basic 2005

Office Developer Center

Microsoft Office Developer Center

Code Security

About the Author

Since 1993, ArtinSoft has been helping customers worldwide to leverage and protect their investment in current applications as they evolve to new platforms, primarily the Microsoft .NET Framework. ArtinSoft created the migration products that Microsoft provides in Visual Studio .NET and Visual Studio 2005, and was named as a Microsoft preferred supplier of worldwide upgrade services for customers. Find out more about ArtinSoft at www.artinsoft.com or send an e-mail message to ArtinSoft at info@artinsoft.com.

Redesigning an Excel VBA Solution for .NET Using Visual Studio 2005 Tools for Office

Overview

Setup Requirements

Installation stepsH8

Create the database to store the log informationH8

Run the VBA solutionH8

Run the redesigned Visual Studio 2005 Tools for Office solutionH8

IIS Log System Overview

To turn logging on for a specific Web site

Trey Research IIS Log Analyzer Solution Overview

To import a log fileH9

Why Redesign the IIS Log Analysis Solution?

Changing Visual Basic Code to Work Better with .NET Memory ManagementH8

Using ADO.NETH8

Using .NET Structured Exception HandlingH8

Turning On Option StrictH8

Use Early BindingH9

Using Native .NET Framework Library CallsH8

Using Native .NET Library ClassesH8

Using the Actions Pane to Display Additional InformationH8

Taking Advantage of Windows Forms ControlsH8

Rearchitecting VBA-Style Code to Work Better with .NET Memory Management

Rearchitecting ADO Code to Use ADO.NET

How to Migrate ADO Code to ADO.NETH8

Rearchitecting Error Handling

Controlling Implicit Conversions with Option Strict

Using Operators

Rearchitecting to Use Early Binding

Rearchitecting to Use Native .NET Framework Library Calls

Rearchitecting to Use Native .NET Framework Calls

Redesigning the User Interface

Using the Actions Pane in Visual Studio 2005 Tools for OfficeH8

Code Security

Conclusion

Additional Resources

About the Author

Additional resources