Migrating Excel VBA Solutions to 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

Revised: January 2006

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

Summary: Convert solutions from Microsoft Visual Basic for Applications (VBA) to Microsoft Visual Studio 2005 Tools for Office and Microsoft Visual Basic 2005. Migrate the Trey Research IIS Log Analyzer solution from VBA to Visual Studio 2005 Tools for Office to highlight issues you may encounter when migrating your projects. (22 printed pages)

Download the OfficeVSTOExcelVBAMigration.msi sample file.

Contents

• Overview

• Visual Studio 2005 Tools for Office Security Overview

• IIS Log System Overview

• Trey Research IIS Log Analyzer Solution Overview

• Migrating Projects from VBA to Visual Studio 2005 Tools for Office

• Migrating Excel Worksheets from VBA to Visual Studio 2005 Tools for Office

• Migrating User Interfaces from VBA to Visual Studio 2005 Tools for Office

• Migrating Business Logic Code from VBA to Visual Studio 2005 Tools for Office

• Migrating ADO Code from VBA to Visual Studio 2005 Tools for Office

• 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 Editions have a new environment for building applications, called Visual Studio Tools for Office, which is based on the Microsoft .NET Framework.

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

This article analyzes many solution migration issues that you may face when using the Visual Basic language to directly migrate a VBA solution to Visual Studio 2005 Tools for Office. The previous article in this series, Convert VBA Code to Visual Basic When Migrating to Visual Studio 2005 Tools for Office, focuses on many low-level coding issues for migrating from VBA to Microsoft Visual Basic. The next article in this series, Redesigning an Excel VBA Solution for .NET Using Visual Studio 2005 Tools for Office, focuses on restructuring the Visual Studio 2005 Tools for Office solution presented in this article to take advantage of the rich set of features offered in Visual Studio 2005 Tools for Office.

The VBA solution, Trey Research IIS Log Analyzer, analyzes Microsoft Internet Information Services (IIS) log files. Trey Research IIS Log Analyzer imports text-based IIS log files into a Microsoft SQL Server database, which is then queried from Microsoft Office Excel 2003. SQL Server is accessed by using Microsoft ActiveX Data Objects (ADO). A form-based query system interacts with the user and calls the business logic code. The results are stored in an Excel worksheet along with a chart. The business logic code contains most of the program logic needed to acquire data and does not contain any user interface (UI) code.

We at ArtinSoft migrated the Trey Research IIS Log Analyzer solution to Visual Studio 2005 Tools for Office to take advantage of Visual Basic 2005 and .NET Framework security. We migrated the VBA form to a Microsoft Windows Form and all VBA ADO code to ADO code in Visual Studio 2005 Tools for Office. The business logic code is in a separate class in the solution.

The next article in this series explains how to do the following:

• Restructure the ADO code to use ADO.NET.

• Take advantage of functionality in the .NET Framework.

Visual Studio 2005 Tools for Office Security Overview

Macro viruses written in VBA can be a serious security risk for Microsoft Office Word and Excel documents. Visual Studio 2005 Tools for Office implements new security mechanisms that significantly enhance the security of Word and Excel documents. In VBA, macros are stored inside the Office document. This is a security risk because it is difficult to determine where the code is coming from and what it does. In Visual Studio 2005 Tools for Office, macro code is located outside of the Word or Excel document, in an assembly that can be code-signed by the author. When an Office application is introduced into an enterprise environment, the assembly can be located on a read-only, shared network drive on the local network. The benefit of this deployment mechanism is that you can tightly control all Visual Studio 2005 Tools for Office code and verify it for authenticity before running it. Another management benefit of this deployment mechanism is that you can update the code separately from the Word or Excel document.

Whenever you create or open a Visual Studio 2005 Tools for Office solution for the first time, the dialog box in Figure 1 appears, as yet another security measure.

Figure 1. Security dialog in Visual Studio 2005 Tools for Office

The common language runtime (CLR) and the .NET Framework also provide a security mechanism called code access security. Code access security can put limitations on whether code can run under various conditions. Code access security is a fairly complex subject, but having an overview of this security mechanism can be very useful in implementing basic security in Visual Studio 2005 Tools for Office applications.

In code access security, policy maps evidence onto permissions. Evidence consists of the following types of information:

• The zone where the assembly came from, usually Internet zone, Intranet zone, or My Computer zone

• The specific URL of the assembly

• Information about who wrote the assembly, usually determined by the strong name or the Publisher certificate of the assembly

In code access security, there are four policy levels:

• Enterprise

• Machine

• User

• Application

Each policy level determines which evidence is associated with each set of permissions. The security system grants permission only if it is granted by all four policy levels.

Full Trust permissions grant permission to do anything the current user has authority to do. Partial Trust permissions grant permission to use part of the hard disk space, produce user interfaces, and access the current user name. Partial Trust does not allow changes to files, environment variables, or registry keys. No Trust permissions means the code cannot run and cannot access anything.

The default Enterprise policy level is Full Trust. The default Machine policy level is No Trust, but it has subcategories for other zones that do grant trust. These zones and their policies are as follows:

• Local machine zone, which is granted Full Trust

• Internet zone, which is granted Partial Trust

• Intranet zone, which is granted Partial Trust

The default User policy level is Full Trust, and the default Application policy is Full Trust. For more information on code access security and security in Visual Studio 2005 Tools for Office, see Eric Lippert - Discussing Visual Studio Tools for Office.

Because Visual Studio 2005 Tools for Office applications often need to do such tasks as saving a document to disk to an arbitrary location, they always need Full Trust permissions to run. By default, a Visual Studio 2005 Tools for Office assembly does not have permissions to run if it is loaded from the Intranet or Internet zone. The user must explicitly change the security policy to run these applications from the Intranet or Internet zone. This is because Visual Studio 2005 Tools for Office is designed to emphasize security over the convenience of default deployment.

Setup Requirements

In order to use this solution you must have the following software components 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 Steps

• 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.

To create the database to store the log information

1. On the Start menu, click Run.

2. Type cmd, and then click OK to open a command prompt window.

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

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

4. 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
   

To run the VBA solution

1. 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 Migration Sample.

2. Open the VBAIISLogAnalysis folder, and then double-click IISLogAnalysis.xls to open the VBA solution in Excel.

3. 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.

4. On the IIS Log Analysis worksheet, press Ctrl + R to display the Report Criteria form.

5. Click Add IIS Log File to Database.

6. 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 Migration Sample. If the import is successful, you receive a message indicating the number of imported rows.

7. Make your report criteria selections, and then click Run Report to show the analysis of the log data.

To run the Visual Studio 2005 Tools for Office solution

1. Browse to the folder where you installed the solution.

   The solution is installed by default to My Documents\Visual Studio 2005\Projects\Excel VBA Migration Sample.

2. Open the VSTOIISLogAnalysis folder, and then double-click IISLogAnalysis.sln to open the solution in Visual Studio 2005 Tools for Office.

3. 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 B9, and then select the IIS Log Analysis worksheet tab.

Note The default name and instance for SQL Server 2005 Express is .\SQLExpress.

4. Press F5 to build and run the solution.

5. Click the Report Criteria Form button to display the Report Criteria form.

6. Click Add IIS Log File to Database.

7. 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 Migration Sample. If the import is successful, you receive a message indicating the number of imported rows.

8. Make your report criteria selections, 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

1. 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.

2. Right-click the Web site for which you wish to enable logging, and click Properties.

3. In the Properties dialog box, click the Web Site tab, and then select the Enable Logging check box.

4. 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 an 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 2.

Figure 2. 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 file, open the Trey Research IIS Log Analyzer solution and press CTRL+R to open the Report Criteria form shown in Figure 3.

Figure 3. VBA Report Criteria user form

On the form, click Add IIS Log File To Database, and select the log file to import. 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.

Migrating Projects from VBA to Visual Studio 2005 Tools for Office

There is currently no automated way to migrate a VBA project to a Visual Studio 2005 Tools for Office project. The best way to migrate a project is to create a project in Visual Studio 2005 Tools for Office that mirrors the project in VBA.

To create a project in Visual Studio 2005 Tools for Office

1. Open Visual Studio 2005.

2. On the File menu, select New.

3. In the New Project dialog box, type the required information (see Figure 4).

Figure 4. Creating a project in Visual Studio 2005 Tools for Office

After you create the basic project, you must migrate several additional elements from the VBA solution to the Visual Studio 2005 Tools for Office solution:

• Excel worksheets

• User forms

• Business logic code

Migrating Excel Worksheets from VBA to Visual Studio 2005 Tools for Office

The easiest way to migrate an Excel worksheet from VBA to Visual Studio 2005 Tools for Office is to create a copy of the worksheet and remove the VBA code from the copy. Then, when you create a Visual Studio 2005 Tools for Office project, in the Select a Document For Your Application dialog box, click Copy an existing document, as shown in Figure 5.

Figure 5. Copying Excel worksheet to new Visual Studio 2005 Tools for Office project

In the Path to the existing document field, type the name of the existing Excel worksheet.

Migrating User Interfaces from VBA to Visual Studio 2005 Tools for Office

You make user interfaces in VBA by using a UserForm, but in Visual Studio 2005 Tools for Office, you use a Windows Form. No automated migration tool is available to help you migrate user interfaces from a UserForm to a Windows Form. The best way to migrate a user interface from VBA is to rewrite it. Visual Studio 2005 Tools for Office has many controls that provide the functionality of most of the controls in VBA, so rewriting an interface is usually straightforward.

Figure 7 shows a migrated Windows Form. To invoke this form, we placed a Windows Form button called Report Criteria Form on the worksheet. You cannot place a control on a worksheet in VBA, but you can with Visual Studio 2005 Tools for Office. Therefore, what formerly was text in the VBA solution is now replaced with this button. Figure 6 shows the migrated Trey Research IIS Log Analyzer worksheet.

Figure 6. Migrated Trey Research IIS Log Analyzer worksheet

The Trey Research IIS Log Analyzer solution has one VBA UserForm, which is shown in Figure 3. The new Visual Studio 2005 Tools for Office Windows Form shown in Figure 7 looks almost identical.

Figure 7. Migrated Report Criteria form

The original VBA UserForm has various types of controls, including labels, calendars, combo boxes, and buttons. All of these controls have very similar equivalents in Visual Studio 2005 Tools for Office. Table 1 shows the control mappings for migrating from VBA to Visual Studio 2005 Tools for Office.

Table 1. Control mappings for IIS Log Analyzer migration

VBA UserForm control	Windows Form control
Label	Label
Calendar	MonthCalendar
ComboBox	ComboBox
CommandButton	Button

We quickly recoded the logic behind the UI by using logic that is almost identical to that of the original VBA solution. During the migration, we changed the UI code in a few places because of differences between the components of VBA and those of Visual Studio 2005 Tools for Office. The following example shows how we migrated the File Open dialog code.

[VBA]
logFileName = Application.GetOpenFilename("IIS Log Files (*.log*), _
    *.log", 1, "Import IIS Log File", , False)
If VarType(logFileName) = vbString Then
    If logFileName <> "False" Then
        SetDBConnectionString
        success = BusinessLogicLayer.ImportLogFile(logFileName, numberLines)

[Visual Basic]
ImportLogOpenFileDialog.FileName = ""
ImportLogOpenFileDialog.Filter = _
              "Log files (*.log)|*.log|All files (*.*)|*.*"
ImportLogOpenFileDialog.Title = "Import IIS Log File"
ImportLogOpenFileDialog.ShowDialog()
logFileName = ImportLogOpenFileDialog.FileName.Trim()
If logFileName.Length > 0 Then
    SetDBConnectionString()
    success = BusinessLogicLayer.ImportLogFile(logFileName, numberLines)

In Visual Studio 2005 Tools for Office, we created the ImportLogOpenFileDialog variable by dragging and dropping the OpenFileDialog component from the Toolbox onto the Windows Form.

Another issue is that the Application object is readily available from VBA UserForm code. However, in Visual Studio 2005 Tools for Office, the analogous ThisApplication variable is not directly available from the Windows Form. Consider the following VBA code located in the UserForm:

[VBA]
Private Sub TransferDBArrayToWorkSheet(rowArray)
    Application.Worksheets(1).Range("A23:F5000") = ""
    If IsEmpty(rowArray) Then
        MsgBox "No data matches criteria", , "Warning"
        Application.Worksheets(1).Cells(rowNumber, 1) = _
           "No Data Matches Criteria"
        Exit Sub
    End If
    totalRowNumber = beginRowNumber + UBound(rowArray, 2) + 1  
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "Retrieving IIS Log ..."
    
    Application.Worksheets(1).Cells(rowNumber, 3).Formula = _
        "Percent Of Total"
End Sub

We migrated this VBA code to Visual Studio 2005 Tools for Office, as shown in the following example. Note that we created the SetThisApplication subroutine so that a class variable, called ThisApplication, could be set with the ThisApplication variable available from the Visual Studio 2005 Tools for Office worksheet code.

Public Sub SetThisApplication(ByRef ThisApplication As _
        Microsoft.Office.Interop.Excel.Application)
    ' The Me.ThisApplication is the class level variable which is assigned
    ' the value of ThisApplication
    Me.ThisApplication = ThisApplication
End Sub
Private Sub TransferDBArrayToWorkSheet(ByVal rowArray)
    ThisApplication.Worksheets(1).Range("A23:F5000") = ""
    If IsNothing(rowArray) Then
        MsgBox("No data matches criteria", , "Warning")
        ThisApplication.Worksheets(1).Cells(rowNumber, 1) = _
            "No Data Matches Criteria"
        Exit Sub
    End If
    totalRowNumber = beginRowNumber + UBound(rowArray, 2) + 1
    ThisApplication.ScreenUpdating = False
    ThisApplication.Calculation = _ 
        Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
    ThisApplication.StatusBar = "Retrieving IIS Log ..."
    ThisApplication.Worksheets(1).Cells(rowNumber, 3).Formula = _
        "Percent Of Total"
End Sub

In the migrated code example, the TransferDBArrayToWorkSheet subroutine frequently uses the ThisApplication variable located in the current class to access the Microsoft.Office.Interop.Excel.Application instance.

Another issue illustrated in the migrated code example is that the VBA IsEmpty function is migrated to the Visual Studio 2005 Tools for Office IsNothing function. VBA sets unallocated VBA objects to a value of EMPTY, but sets unallocated COM objects to a value of NOTHING. As you can see, VBA has two mechanisms for setting the value of unallocated objects. Visual Studio 2005 Tools for Office uses only one value for unallocated objects, and it is NOTHING. Therefore, all IsEmpty calls in VBA must be converted to IsNothing in Visual Studio 2005 Tools for Office.

For more information on the IsNothing function, see IsNothing Function.

Migrating Business Logic Code from VBA to Visual Studio 2005 Tools for Office

After you migrate the workbook and the UserForm, you can start to migrate the business logic code by using the Visual Basic 2005 Upgrade Wizard.

The Visual Basic 2005 Upgrade Wizard, which is included in Microsoft Visual Studio 2005, is a tool that upgrades a Microsoft Visual Basic 6.0 project to Microsoft Visual Basic 2005. The user interface for the wizard is integrated into the Visual Studio 2005 environment. There is also a command-line tool available, VBUpgrade.exe, which offers the same functionality. The Visual Basic 2005 Upgrade Wizard is not designed specifically to migrate VBA applications to Visual Studio 2005 Tools for Office, but you can use it to assist with migrating part of a VBA application. The Visual Basic 2005 Upgrade Wizard does not support migrating a VBA UserForm, but business logic code is an ideal candidate for migrating with this tool. This article focuses on how to migrate the BusinessLogicLayer module of the VBA Trey Research IIS Log Analyzer solution to Visual Basic 2005 by using the Visual Basic 2005 Upgrade Wizard.

To migrate the BusinessLogicLayer module, you first save the module to a file on the hard disk. To do this, load the IISLogAnalysis.xls workbook, and on the Tools menu, select Macros, and edit QueryIISLog, which is the only macro in the application. The VBA editor opens, and you see the complete VBA project. Double-click the BusinessLogicLayer module, and on the File menu, select Export to export the file to hard disk.

To use the Visual Basic 2005 Upgrade Wizard

1. In Visual Studio 2005, create a blank Visual Basic 6.0 project. If you do not have Visual Basic 6.0, you cannot run the Visual Basic 2005 Upgrade Wizard.

2. Add the code file that you exported from the Excel workbook to the project.

3. Save and close the project.

4. Open Visual Studio 2005, and on the File menu, point to Open, and click Convert.

5. In the Convert dialog box, click Visual Basic 2005 Upgrade Wizard, as shown in Figure 8.

   Figure 8. Converting code files with the Visual Basic 2005 Upgrade Wizard

   

As an alternative to using the Visual Basic 2005 Upgrade Wizard, you can upgrade Visual Basic 6.0 projects to Visual Basic 2005 by using a command-line tool, Vbupgrade.exe. Use the following syntax to run the command-line tool:

VBUpgrade IISLog.vbp

For more information about upgrading a project with the command-line tool, see Upgrading a Project from the Command Line and Vbupgrade Command-Line Syntax.

Note

Vbupgrade.exe is not in the path by default. You must either add it to the path or switch to the directory where it is installed before running it from the command line. The default installation location in Visual Studio 2005 is C:\Program Files\Microsoft Visual Studio 8\Vb\VBUpgrade.

By default, the output of the Visual Basic 2005 Upgrade Wizard is saved in a subfolder under the source project's directory. The output will be a new Visual Basic project and all of the source code.

The easiest way to add the upgraded business logic code to the project is to copy the .vb file created by the Visual Basic 2005 Upgrade Wizard to the Visual Studio 2005 Tools for Office project directory. After you add the file, right-click the project name and select Add Existing from the context menu. Select the .vb file to add it to the current Visual Studio 2005 Tools for Office project. The migrated code is now included in the project, but you still must do some work on it for it to function properly.

The output code produced by the Visual Basic 2005 Upgrade Wizard usually contains comments that point out errors, warnings, or issues. The Visual Basic 2005 Upgrade Wizard inserts errors, warnings, or issues into the target code to warn the user of potential migration issues.

For example, the following is a warning in BusinessLogicLayer.vb:

'UPGRADE_WARNING: Couldn't resolve default property of object recSet.GetRows(). 
Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

This particular warning does not affect the logic of the code in this solution, so it can be ignored. We left it in the code to illustrate the types of errors, warnings, or issues produced by the Visual Basic 2005 Upgrade Wizard and to illustrate that, in other solutions, you may need to address the issue. In your own solutions, if an error, warning, or issue is insignificant to the functionality of your code, it may be best to remove it for more readable code.

Migration is much easier if a program is organized such that the business logic is separated into distinct modules. Migration is more difficult if the business logic is intermixed with UI code. In these more difficult situations, you can still use the Visual Basic 2005 Upgrade Wizard, but many errors, warnings, or issues are generated for the UI code. You can use Visual Basic code examples from the migrated code to recreate the business logic in the new Visual Studio 2005 Tools for Office project.

All of the business logic for the VBA Trey Research IIS Log Analyzer solution is in a file called BusinessLogicLayer.

Migrating ADO Code from VBA to Visual Studio 2005 Tools for Office

The easiest way to migrate ADO code from VBA to Visual Studio 2005 Tools for Office is to continue to use the ADO library in Visual Studio 2005 Tools for Office. ADO.NET offers many more rich features, but migrated ADO code requires some restructuring. Restructuring the Trey Research IIS Log Analyzer solution to use ADO.NET is the subject of the next article in this series. For this article, we look at the issues associated with migrating ADO directly from VBA to Visual Studio 2005 Tools for Office.

To use ADO, the new Visual Studio 2005 Tools for Office solution must have a reference to the ADO library. To add this reference, in Solution Explorer, right-click the Visual Studio 2005 Tools for Office project, and select Add Reference. Select the COM tab, and choose the latest version of the Microsoft ActiveX Data Access Library.

Most ADO objects, including the frequently used ADODB.Connection, ADODB.Recordset, and ADODB.Command objects, are the same in VBA and Visual Studio 2005 Tools for Office. However, Visual Studio 2005 Tools for Office requires COM interop wrappers for these objects, to allow interoperability between the CLR environment in Visual Studio 2005 Tools for Office and the ADO COM objects. These managed wrapper classes create some subtle differences, which result in some small differences from the original ADO COM objects.

For example, the following code is the CreateDBTableArray function from BusinessLogicLayer. This function can take up to two SQL query strings as parameters and performs a query to SQL Server using ADO. This function takes two SQL strings because, in some cases, a temporary table must be created to do a query, and it takes two SQL statements to execute such a query. The function returns a two-dimensional array containing the rows returned by the query, along with the field names from the query.

[VBA]
Private Function CreateDBTableArray(sqlString As String, Optional _
        sqlString2 As String) As Variant
    Dim connect As ADODB.connection
    Dim recSet As ADODB.Recordset
    Dim cmdCommand As ADODB.Command
    Dim rowArray As Variant
    
    Set connect = New ADODB.connection
    connect.connectionString = conString
    connect.Open
    
    Set cmdCommand = New ADODB.Command
    Set cmdCommand.ActiveConnection = connect
    cmdCommand.CommandText = sqlString
    cmdCommand.CommandType = adCmdText
    cmdCommand.Execute
    
    If sqlString2 <> "" Then
        cmdCommand.CommandText = sqlString2
        cmdCommand.CommandType = adCmdText
        cmdCommand.Execute
    End If
    
    Set recSet = New ADODB.Recordset
    Set recSet.ActiveConnection = connect
    recSet.Open cmdCommand
    
    ' No rows found.
    If recSet.EOF = True Then
        CreateDBTableArray = Empty
        connect.Close
        Set connect = Nothing
        Exit Function
    End If
    
    rowArray = recSet.GetRows()
      
    ReDim Preserve rowArray(UBound(rowArray, 1), UBound(rowArray, 2) + 1)
    
End Function

When we migrate this VBA code by using the Visual Basic 2005 Upgrade Wizard, the following code is produced:

Private Function CreateDBTableArray(ByRef sqlString As String,
          Optional ByRef sqlString2 As String = "") As Object
    
    Dim connect As New ADODB.Connection
    connect.connectionString = conString
    connect.Open()
    
    Dim cmdCommand As New ADODB.Command
    cmdCommand.ActiveConnection = connect
    cmdCommand.CommandText = sqlString
    cmdCommand.CommandType = ADODB.CommandTypeEnum.adCmdText
    cmdCommand.Execute()
    
    If sqlString2 <> "" Then
        cmdCommand.CommandText = sqlString2
        cmdCommand.CommandType = ADODB.CommandTypeEnum.adCmdText
        cmdCommand.Execute()
    End If
    
    Dim recSet As New ADODB.Recordset
    recSet.ActiveConnection = connect
    recSet.Open(cmdCommand)
    
    ' No rows found.
    If recSet.EOF = True Then`
        CreateDBTableArray = Nothing
        connect.Close()
        connect = Nothing
        Exit Function
    End If
    
    'UPGRADE_WARNING: Couldn't resolve default property of object recSet.GetRows(). 
Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
    Dim rowArray As Object = recSet.GetRows()
    
    ReDim Preserve rowArray(UBound(rowArray, 1), UBound(rowArray, 2) + 1)
    
End Function

The warning containing "Couldn't resolve default property" is of little concern for this code, because it does not refer to anything that changes the functionality and logic of the code. This warning is present because of ambiguities that could be present in the resulting code. The Visual Basic 2005 Upgrade Wizard could not fully determine whether a default property exists in the migrated program. The code will not run properly in Visual Studio 2005 Tools for Office because of some subtle differences in how ADO works with the COM interop wrapper classes. To get the migrated business logic code to function in the same manner as the original VBA business logic code, we changed it as follows:

Private Function CreateDBTableArray(ByRef sqlString As String,
        Optional ByRef sqlString2 As String = "") As Object

    Dim connect As New ADODB.Connection
    connect.connectionString = conString
    connect.Open()

    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

    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

    ' No rows found.
    If recSet.EOF = True Then
        CreateDBTableArray = Nothing
        connect.Close()
        connect = Nothing
        Exit Function
    End If

    'UPGRADE_WARNING: Couldn't resolve default property of object
    recSet.GetRows(). Click for more: 'ms-
    help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
    Dim rowArray As Object = recSet.GetRows()

    ReDim Preserve rowArray(UBound(rowArray, 1), UBound(rowArray, 2) + 1)
    ...
End Function 

The changes you must make to the migrated code so that it operates properly in the Visual Studio 2005 Tools for Office version of the function are as follows:

• Original Visual Basic migrated code:

  Dim cmdCommand As New ADODB.Command
  cmdCommand.ActiveConnection = connect
  cmdCommand.CommandText = sqlString
  cmdCommand.CommandType = ADODB.CommandTypeEnum.adCmdText
  cmdCommand.Execute()
  
  If sqlString2 <> "" Then
     cmdCommand.CommandText = sqlString2
     cmdCommand.CommandType = ADODB.CommandTypeEnum.adCmdText
     cmdCommand.Execute()
  End If
  
  Dim recSet As New ADODB.Recordset
  recSet.ActiveConnection = connect
  recSet.Open(cmdCommand)
  

• Migrated Visual Basic code modified for Visual Studio 2005 Tools for Office:

  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
  
  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
  

You must modify the code because of differences in how ADO works in VBA and in Visual Studio 2005 Tools for Office with managed COM wrappers. In the VBA version, this code works properly because the ADODB.Command object closes automatically after the first use of it is executed. In the migrated code, the ADODB.Command object does not close automatically, and a run-time error occurs, stating that the temporary table in the SQL statement cannot be found. Knowing this, the first option seems to be to manually call a Close method for the ADODB.Command object, but a Close function is not available from VBA or Visual Studio 2005 Tools for Office. One way to work around this difference is to use two different ADODB.Command instances with the same connection.

Another issue with the migrated code is that the original code strings, which are concatenated over several lines of code, form one line of code. Although this structure is functionally equivalent, it may be preferable for code readability to split the concatenated string again over multiple lines. For example, the following Visual Studio 2005 Tools for Office code could be manually edited in Visual Studio 2005 Tools for Office for more readability and maintainability.

Public Function GetPageViewsReport(ByRef beginDate As Date,
        ByRef endDate As Date) As Object
    Dim sqlString As String = "Select Convert(Varchar(8),HitDateTime,
112) As [Date], Count(*) As [Number Of Page Views] "
 & "From IISLog " & "Where (HitDateTime >= '" & CStr(beginDate)
 & "' AND " & "HitDateTime <= '" & CStr(endDate) & "') 
AND " & "(SubString(TargetUrl, 
Len(TargetUrl) - 3, 4) = '.htm' " & "Or SubString(TargetUrl, 
Len(TargetUrl) - 4, 5) = '.html' " & "Or SubString(TargetUrl, 
Len(TargetUrl) - 3, 4) = '.asp' " & "Or SubString(TargetUrl, 
Len(TargetUrl) - 4, 5) = '.aspx') " & "Group By 
Convert(Varchar(8),HitDateTime,112) " & "Order By 
Convert(Varchar(8),HitDateTime,112) Desc "
    GetPageViewsReport = CreateDBTableArray(sqlString)
    ChangeDateFormat(GetPageViewsReport, 0)
End Function

This version is more readable and maintainable.

Public Function GetPageViewsReport(ByRef beginDate As Date, ByRef endDate As Date) As Object
    Dim sqlString As String = _
        "Select Convert(Varchar(8),HitDateTime,112) As [Date], " _
        & "Count(*) As [Number Of Page Views] " & "From IISLog " " _
        & "Where (HitDateTime >= '" & CStr(beginDate) _
        & "' AND " & "HitDateTime <= '" & CStr(endDate) _
        & "') AND " + _
        & "(SubString(TargetUrl, Len(TargetUrl) - 3, 4) = '.htm' " _ 
        & "Or SubString(TargetUrl, Len(TargetUrl) - 4, 5) = '.html' " _
        & "Or SubString(TargetUrl, Len(TargetUrl) - 3, 4) = '.asp' " _
        & "Or SubString(TargetUrl, Len(TargetUrl) - 4, 5) = '.aspx') " _
        & "Group By Convert(Varchar(8),HitDateTime,112) " _
        & "Order By Convert(Varchar(8),HitDateTime,112) Desc "
    GetPageViewsReport = CreateDBTableArray(sqlString)
    ChangeDateFormat(GetPageViewsReport, 0)
End Function

Code Security

The sample code referred to in this article is intended for instructional purposes, and 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 2. Examples of threats

Threat effect	Entry point	Known mitigation
SQL Server access is compromised	SQL Server database	SQL Server access must be managed and secured.
SQL Server data is compromised	SQL Server database	SQL Server tables must 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

There are two main ways to migrate a VBA project to Visual Studio 2005 Tools for Office. The first is to directly migrate, with little or no architectural changes, to get the project up and running in Visual Studio 2005 Tools for Office. The second way is to restructure the solution to take advantage of the rich features available in Visual Studio 2005 Tools for Office. This article covers many of the issues that you may encounter when migrating a solution from VBA directly to Visual Studio 2005 Tools for Office. Migrating to Visual Studio 2005 Tools for Office has significant advantages, including full access to the .NET Framework. It is worth consideration for many VBA projects.

You can use the Visual Basic 2005 Upgrade Wizard to migrate some of the code in an original VBA application, but you must manually migrate other code because the Visual Basic 2005 Upgrade Wizard is designed to upgrade Microsoft Visual Basic 6.0 projects to Microsoft Visual Basic. In the next article in this series, we show how to restructure the Trey Research IIS Log Analyzer solution to take full advantage of many of the rich features in Visual Studio 2005 Tools for Office, including ADO.NET and other .NET Framework features.

Additional Resources

Visual Studio 2005 Tools for Office

• Microsoft Office Developer Center: Visual Studio Tools for Office Developer Portal

• Microsoft Visual Studio Developer Center

• Visual Studio Tools for Office documentation

• Microsoft Visual Studio 2005 Tools for the Microsoft Office System blog

• Visual Studio Tools for Office Forum

• Visual Studio Tools for the Microsoft Office System: Office Development Samples

Migrating VBA Code

• Converting Code from VBA to Visual Basic .NET

• Converting Microsoft Office VBA Macros to Visual Basic .NET and C#

• Migrating Word VBA Solutions to Visual Studio Tools for Office

VBA

• Microsoft ISV Community Center: Visual Basic for Applications

Microsoft Office Object Models

• Understanding the Excel Object Model from a Visual Studio 2005 Developer's Perspective

• Understanding the Word Object Model from a Visual Studio 2005 Developer's Perspective

Migrating

• Microsoft Visual Basic Developer Center: Upgrading to Visual Basic 2005

Office Developer Center

• Microsoft Office Developer Center

Code Security

• Writing Secure Code, Second Edition

• Microsoft Security Developer Center

• Security at Home: Protect Your PC

About the Author

Since 1993, ArtinSoft has been helping customers worldwide to use 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 within Visual Studio, and is 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.