Redesigning an Excel VBA Solution for .NET Using Visual Studio 2005 Tools for Office
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
Published: September 2005
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)
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.
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)
Install Microsoft Visual Studio 2005 Tools for Office.
Perform a complete installation of Microsoft Office Professional Edition 2003.
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
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:
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.
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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
Visual Basic structured exception handling
Error message description
Method where the exception occurred
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.
Visual Basic exception property
The stack trace of the functions called before the exception was thrown
Application name where the exception occurred
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.
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.
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.
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.
File.GetCreationTime(), File.GetLastAccessTime(), File.GetLastWriteTime()
FormatCurrency, FormatDateTime, FormatNumber, FormatPercent
stringVar.IndexOf(), stringVar.StartsWith(), stringVar.EndsWith()
Array.GetLowerBound (Dim -1)
Val( integerValueString )
Val( doubleValueString )
System.Convert.ToDouble( doubleValueString )
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.
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 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.
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.
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
Do not store data source connection information on a worksheet
For more information about code security, visit the Microsoft Security Developer Center.
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.
Visual Studio 2005 Tools for Office
Migrating VBA Code
Microsoft Office Object Models
Office Developer Center
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 firstname.lastname@example.org.