10 Tips for Migrating VBA to Visual Basic Using Visual Studio Tools for Office Second Edition (Part 2 of 2)

Article
05/23/2014

Summary: Read 10 tips about how to migrate Microsoft Visual Basic for Applications code to Microsoft Visual Basic code by using Microsoft Visual Studio 2005 Tools Second Edition for the Microsoft Office system. (16 printed pages)

Ken Getz, MCW Technologies, LLC

Jan Fransen, MCW Technologies, LLC

November 2007

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007, Microsoft Office Word 2007, Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office System

Contents

Tip #6: Know When to Keep Using VBA Constructs
Tip #7: Learn the .NET Framework
Tip #8: Use the .NET Framework to Write Readable and Consistent Code
Tip #9: Learning New Ways to Work with Data
Tip #10: Learn About Security Policies
Conclusion
About the Authors
Additional Resources

Read part one: 10 Tips for Migrating VBA to Visual Basic Using Visual Studio Tools for Office Second Edition (Part 1 of 2)

Tip #6: Know When to Keep Using VBA Constructs

After you learn the Microsoft .NET Framework, you may want to convert all of your Microsoft Visual Basic for Applications (VBA) code so that it uses .NET Framework methods. You might think that the .NET Framework methods are faster than the equivalent VBA code. That is not always true. In some cases, there is no significant difference. It may be better from a development standpoint to continue to use the technique you know best rather than changing just for the sake of change. In some cases, the VBA code is more efficient than the .NET Framework equivalent. In a few cases, VBA provides functionality that the .NET Framework does not have.

The .NET Framework provides a base set of classes that many programming languages can use. That is why it is easy, in general, to convert Microsoft Visual Basic code to Microsoft Visual C# code and back—whether you use Visual Basic or C#, the code you write uses .NET Framework classes, and those classes are the same across different languages. Each language provides its own extensions, including Visual Basic. To keep Visual Basic backward compatible with VBA, it includes methods (such as the Len method and the Mid method) that C# does not have. Some of these VBA methods perform better than their .NET Framework equivalents, because of the way they are implemented in Visual Basic. For more information, see the article Visual Basic .NET Internals.

As a VBA developer migrating to the .NET Framework, you have the best of both worlds. Because the Visual Basic language includes all of the date-handling functions that VBA has, you can continue to use familiar techniques and combine them with APIs such as System.DateTime and System.TimeSpan that the .NET Framework provides. For example, you can use either the VBA Month function, or the .NET Framework function System.DateTime.Month because both return the same value, given the same input.

In VBA, you can use the DateSerial function to generate a date/time value given integers representing the specific year, month, and date, like the following.

Dim dateValue1 As Date = DateSerial(2007, 8, 12)
' or
Dim dateValue2 As DateTime = DateSerial(2007, 8, 12)

Note
Visual Basic treats the VBA Date type and the .NET Framework System.DateTime structure interchangeably.

You can get the same result in Visual Basic by passing the necessary information to the constructor of the DateTime structure, as follows.

Dim dateValue As New DateTime(2007, 8. 12)

Some VBA method names conflict with .NET Framework classes. If you need to disambiguate the VBA references, add the full class name. This works because you can access VBA functionality by using Visual Basic classes. For example, the following code does not compile in Visual Basic.

Dim birthDay As Integer = Day(birthDate)

You can fix the code by explicitly referencing the DateAndTime class, as follows.

Dim birthDay As Integer = DateAndTime.Day(birthDate)

If you find that some date calculations are easier in VBA, you can save some time by not converting your code. However, if you need to convert your code to C# in the future, you should replace VBA methods calls with methods provided by the .NET Framework.

Take the time to compare the Visual Basic DateAndTime class to the DateTime and TimeSpan structures in the .NET Framework. The DateAndTime class may be more familiar, but the .NET Framework versions have more features. If you do not learn the .NET Framework versions now, continue to use VBA techniques that are available in Visual Basic. Note the following differences.

Explicit methods to add time intervals, like AddDays, AddHours, AddMilliseconds, AddMinutes, AddMonths, AddSeconds, AddTicks, and AddYears. Much of this functionality is available in VBA, using the DateAdd method, but not all.
The DaysInMonth method, which returns the number of days in a specified month and year. Calculating this value in VBA requires more effort.
The IsDaylightSavingTime method, which indicates whether the specified date is within the daylight savings time range for the current time zone.
The IsLeapYear method, which returns a Boolean value indicating whether the specified year is a leap year.

The .NET Framework has more date and time features than VBA, and it is worth investigating what is available. Some tasks may be easier using VBA, while other tasks may only be possible using the methods provided by the .NET Framework. For example, the .NET Framework has the System.TimeSpan type, which you can use to work with elapsed times. If you need a data structure that can store information about a length of time, use System.TimeSpan. VBA does not have similar functionality.

Late Binding and Visual Basic

Visual Basic provides a feature that C# does not: late binding. This feature allows you to declare a variable as an object, and then treat the variable as if it were of a specific class. To use this feature, you must turn Option Strict off for the code file (or the entire project, but that is not recommended). Late binding indicates that the Visual Basic compiler does not determine the type of the variable at compile time, as it normally would. The type of the variable is not determined until run time. This can cause run-time errors, if your code is incorrect; but the compiler does not complain at compile time. In its simplest form, late binding allows you to write code as follows.

Dim x As Object
x = "Hello"
MsgBox(x & " is " & x.Length & " characters long.")

Even though the variable x is defined as an object type, the code retrieves the value of the Length property, treating the variable as if it were a String type. The compiler does not complain, and the code runs fine.

This technique is useful when you are programming with the Microsoft Office Word 2007 object model. Specifically, the Dialog object in Word changes its shape depending on which specific dialog box you use. Different dialog boxes provide different properties, and the compiler cannot determine which of the dialog boxes you write code for. By using late binding, you can effectively indicate to the compiler, “Look, I know you can’t tell what I want until run time. Trust me. I know what I’m doing.” The only problem is that you need to know what you are doing.

The alternative to using late binding (and the only option, for C# developers), is to use a technique called reflection by using classes and members in the System.Reflection namespace. This set of classes enables you to retrieve information about objects at run time, and to call methods and retrieve properties dynamically, based on that information. To read more about Office Word dialog boxes and reflection, see Understanding the Word Object Model from a Visual Studio 2005 Developer's Perspective.

Tip #7: Learn the .NET Framework

If you want to take advantage of what the .NET Framework has to offer, you need to learn new ways to do old things. Fortunately, many of the new techniques that you learn require less programming. You should not be discouraged: even though the .NET Framework is large, learning it is worth the effort.

Everything in the .NET Framework is a class or a member of a class. The classes within the framework are grouped into logical groupings called namespaces, which are organized in a hierarchy. The System namespace contains the basic and most important classes, such as Object, Int32, and String. Most of the .NET Framework classes exist in namespaces that lie below the System namespace, such as System.Data, System.IO, and System.Windows.Forms. Each namespace contains a collection of classes, all with unique names. That is, within each namespace, no two classes can have the same name. Across namespaces, however, two classes can have the same name. (You can use the full namespace name to disambiguate references when necessary.)

The best place to learn about the .NET Framework is the reference documentation, which is available in Microsoft Visual Studio. To view the documentation from Visual Studio, on the Help menu, click Contents. On the left of Microsoft Document Explorer, you see the contents, as shown in Figure 1.

Figure 1. Contents of the .NET Framework documentation

Contents of the .NET Framework documentation

If you work on a project that requires localization, you can scroll through the set of classes to find the System.Globalization namespace. Double-click it to display the documentation shown in Figure 2. This documentation is crucial to your understanding of the many classes in the .NET Framework.

Figure 2. Use the .NET Framework documentation to investigate classes and their members

Examine the .NET Framework documentation

Importing a Namespace

To resolve references to specific classes, the Visual Basic compiler needs to find the class. For example, suppose you want to use the DateTimeFormatInfo class in the System.Globalization namespace. If you try to use the class name by itself, you see a compiler error, which is indicated by a wavy underline, as shown in Figure 3.

Figure 3. You need to specify the namespace of the DateTimeFormatInfo class

Specify the namespace for the class

You can use the Error Correction Options smart tag to fix the reference; it suggests that you qualify, or disambiguate, the class name with the namespace name, as follows.

Dim dtf As Globalization.DateTimeFormatInfo

If you create a solution that needs the Globalization class throughout the module or class, you should import the namespace by adding an Imports statement to the top of the class, as follows.

Imports System.Globalization
public class ThisAddIn

Adding the Imports statement to the top of a code file helps the Visual Basic compiler determine namespaces to look in to resolve ambiguous class names. After importing the System.Globalization namespace, you can use DateTimeFormatInfo without specifying the Globalization namespace explicitly. However, if you import two namespaces that each contain classes with the same names, you need to disambiguate the references by using the complete name of the namespace. (This does not occur often within the .NET Framework, but is more likely when you start using modules and namespaces created by others.)

Before you import a namespace, your project must have a reference to the assembly that provides the namespace. It is easy to confuse importing a namespace with setting a reference to the assembly that provides the namespace. Importing is a coding convenience that saves you a lot of typing because you do not have to fully qualify class names in your code. Setting a reference to an assembly makes a namespace available for importing by your project.

As an example, view the Microsoft Office Excel add-in project you created earlier. In the sheetInfoButton Click event handler, you used the class Excel.Worksheet, as follows.

For Each sheet As Excel.Worksheet In Application.Worksheets
  sw.WriteLine(sheet.Name)
Next

The namespace for Excel is Microsoft.Office.Interop.Excel; the Excel in Excel.Worksheet is an alias for the full namespace name. The template for your add-in contains a reference to the Excel namespace. In addition, because your add-in imports the namespace at a project level, you can use its members in your code. If you want to create your own namespace aliases, the syntax looks like the following.

Imports aliasName=NamespaceName

In a real example, the Imports statement looks like the following.

Imports Excel=Microsoft.Office.Interop.Excel

If you use Visual Studio Tools for Office Second Edition to extend a 2007 Office system product, and you want your code to interact with another product, you need to set a reference just as you did when you used VBA. Follow these steps to add a reference to Office Word and an Imports statement that includes an alias for the Word namespace to the Excel 2007 add-in you created earlier.

To add a reference to Word and an Imports statement

Start with the add-in you created earlier, or create an add-in for Excel 2007.
On the Project menu, click ExcelAddIn1 Properties (your project may have a different name than ExcelAddIn1).
In the Properties window, click the References tab. The References page has two parts, References and Imported namespaces. You can use the Imported namespaces part to import a referenced namespace so that it is available throughout your project.
Under the list of references, click Add. Although there is a Microsoft.Office.Interop.Word namespace in the .NET list, select the Microsoft Word 12.0 COM library. After you set the reference, you can use the namespace.
In the Add References dialog box, on the COM page, find and select Microsoft Word 12.0 Object Model.
Click OK to add the reference to the project. The library now appears in the list of references.
In Solution Explorer, open Ribbon1.vb. Notice that there are already several Imports statements in the Declarations section.
Add an Imports statement for the Word namespace, along with an alias for the namespace.
```
Imports Word = Microsoft.Office.Tools.Word
```

Change the OnClick procedure you created earlier so that clicking the button opens an instance of Word.

Public Sub OnClick(ByVal control As Office.IRibbonControl)
    Dim wordApp As New Word.Application
    wordApp.Visible = True
End Sub

Save and run the project.
Click the button in the Excel Add-In Ribbon. Word opens.
Exit Word and Excel.

Shared Members and Instance Members

When you work with the .NET Framework, you need to know what the Shared keyword means. Some members of some classes include this keyword: it means that you can call the member (a property or method) without needing to create an instance of the class. In other words, shared members apply to all instances of the class. Standard members (normally called instance members) apply to a specific instance of the class.

For example, find the System.String class in the .NET Framework documentation. (The String class is within the System namespace.) Figure 4 shows a small subset of the documentation for the methods of the String class. The methods that include a graphical letter S are shared members; they are not instance members.

Figure 4. The letter S indicates shared members

The letter S indicates shared members

To use shared members, you do not need an instance of the class; to use instance members, you do. Therefore, to call the String.Compare method (a shared method), write code like the following (assuming that string1 and string2 are defined as String types).

If String.Compare(string1, string2) = 0 Then
  ' You know the strings are equal.
End If

This code does not create a specific String and call its Compare method—instead, it calls the Compare method of the String class.

For instance members, you must create an instance of the class before using the member. For example, to use the Contains method, you must first create a String instance, give it a value, and call the Contains method of that particular string, like the following (assuming that string1 is a String).

If string1.Contains("Hello") Then
  ' You know string1 contains the text "Hello".
End If

Tip #8: Use the .NET Framework to Write Readable and Consistent Code

The .NET Framework provides more native functionality than VBA does, and its classes and members behave consistently. You can find a namespace, class, and member for practically any task and, because of the consistency of the .NET Framework, the resulting code is easy for others to understand. This tip shows you how to use the .NET Framework to perform file input/output and to retrieve system information, two operations that are harder to perform in VBA.

File Handling

In contrast to VBA, accessing the file system using the .NET Framework is consistent and straightforward. All the classes for reading and writing files are in the System.IO namespace. If you plan to use the System.IO namespace many times in a particular file, we recommend that you add Imports System.IO at the top of the file. The following examples assume you import the System.IO namespace.

Retrieving a List of Files in a Folder

Retrieving a list of files using the .NET Framework is also easy. The System.IO.Directory class returns an array of strings, and you can iterate through the array to handle the list of files. For example, the following code retrieves all the files in the C:\ folder, and displays their contents in the Output window.

For Each fileName As String In Directory.GetFiles("C:\")
  ' Do something with each file name:
  Debug.Print(fileName)
Next

Retrieving the Contents of a File

Using the File class, you can open a file and retrieve its contents into a string. The ReadAllText shared method does this for you. The following example reads all the text from the file C:\Test.txt and displays its contents in the Output window.

Dim contents As String = File.ReadAllText("C:\Test.txt")
Debug.Print(contents)

Writing a Line of Text to a File and Closing the File

Often, you want to log information to a text file. Although you can use the methods in the System.Diagnostics namespace to log information, sometimes it is easier to perform this task yourself. The following example, from a Button control’s Click event handler, writes text to a log file each time you click the button.

File.AppendAllText("C:\Log.txt", _
 String.Format("{0}: Button clicked{1}", 
 Now, Environment.NewLine))

Use the String.Format method to create a template with numbered placeholders. At run time, the method replaces each placeholder with the item from the same position in the list of arguments that follows. In this case, the method replaces the {0} placeholder with the current date and time value, and the {1} placeholder with a NewLine character.

Note
Instead of Environment.NewLine, you can also use the vbCrLf constant in the Microsoft.VisualBasic namespace. We recommend that you become familiar with the contents of the Environment class.

Reading Lines of Text in a File into an Array

Using the File.ReadAllLines method, you can read the contents of a text file. The following example iterates through all the lines in the returned array of strings, displaying each in the Output window.

For Each line As String In File.ReadAllLines("C:\log.txt")
  Debug.Print(line)
Next

System Information

If you have to interact with the environment using VBA, you can use the Win32 API to solve your problems. Because the .NET Framework provides many classes that interact with the environment, you never have to use the Win32 API.

The following code displays useful information about the current environment, without using the Win32 API, in the Output window.

Debug.WriteLine("Current directory: " & _
 System.Environment.CurrentDirectory)
Debug.Write("Logical drives   : ")
For Each drive As String In _
 System.Environment.GetLogicalDrives
  Debug.Write(drive & " ")
Next
' Insert a new line.
Debug.WriteLine("")
Debug.WriteLine("Machine name     : " & _
 System.Environment.MachineName)
Debug.WriteLine("OS Version       : " & _
 System.Environment.OSVersion.VersionString)
Debug.WriteLine("Processor Count  : " & _
 System.Environment.ProcessorCount)
' This is just one of many special directories.
Debug.WriteLine("Desktop Directory: " & _
 System.Environment.SpecialFolder.DesktopDirectory)
Debug.WriteLine("User Domain Name : " & _
 System.Environment.UserDomainName)
Debug.WriteLine("User Name        : " & _
 System.Environment.UserName)
Debug.WriteLine("Framework version: " & _
 System.Environment.Version.ToString())

For more information, see the following references:

What's New in Developer Content discusses how to use the Help files, and how to decipher the object model information.
Language Changes for Visual Basic 6.0 Users.

Tip #9: Learning New Ways to Work with Data

If you have a VBA solution that interacts with data, you probably used either the Data Access Object (DAO) or the ActiveX Data Objects (ADO) to retrieve and update the data. Although Word and Excel try to simplify data access, neither application uses a consistent, simple solution. Although we cannot cover in detail the data API (called ADO.NET) used by the .NET Framework, we can demonstrate an example that shows how easy it is to bind forms to data, and then use that data.

ADO.NET provides classes and members in the System.Data namespace (and subsidiary namespaces) that allow you to easily connect to, retrieve, and update data from OLE DB, Microsoft SQL Server, and other data sources. After you create a connection by using a connection string (similar to connection strings in ADO), you can create command objects that retrieve and update data. You can remain connected to the data source and iterate through all the rows of data, using a SqlDataReader or OleDbDataReader (other data sources provide their own data reader classes). Or you can create and fill a client-side cache for the data, using the DataTable class or the DataSet (a group of related DataTable instances) class. In that case, your code is independent of the original data source.

Although you can write code that manipulates data using the classes and members of the System.Data namespace, you do not need to. The .NET Framework provides superb data binding functionality, and you can access the data without much code. To demonstrate this functionality, use the Excel add-in you created earlier in this article, or create an additional add-in for Excel, and add a custom task pane named CalendarTaskPaneControl (to match the previous example’s demo). See Tip#4: Add Custom Task Panes for help.

Follow these steps to add support for displaying data from the SQL Server Northwind sample database in a custom task pane. To follow through this demo, you need to have Microsoft SQL Server 2000, SQL Server 2005, or SQL Express 2005 installed and configured. In addition, you need to have the Northwind sample database installed. If it is not, download and install the sample from Northwind and Pubs Sample Databases for SQL Server 2000.

Follow these steps to add a data source that refers to the Northwind sample database.

To add a data source that refers to the Northwind sample database

In Visual Studio 2005, click View and then click Server Explorer. If you already have set up a connection to the Northwind sample database, skip to Step 8.
In the Server Explorer window, right-click Data Connections, and click Add Connection.
In the Choose Data Source window, click Microsoft SQL Server. Click Continue.
In the Add Connection dialog box, in the Server name field enter the value .\SQLEXPRESS. (If you are using a different instance of SQL Server, enter the name of the instance here.)
If you already attached the Northwind sample database to your SQL Server instance, in the Select or enter a database name drop-down list, select Northwind, and skip to step 7. If you do not see Northwind in the list, execute the instructions in Step 6.
Select the Attach a database file option, click Browse, and find the NORTHWND.MDF file. Set the Logical Name field to Northwind.
Click Test Connection to verify that you can access the data in the Northwind sample database, and then click OK to dismiss the dialog box. The Server Explorer window now displays a new data connection. If you see a name other than Northwind in the Server Explorer window, you can right-click the connection, click Rename, and enter Northwind as the connection name.
Click Data and then Show Data Sources. Visual Studio 2005 displays the Data Sources window.
Click Data, and then Add New Data Source (or click the link in the Data Sources window to create a data source).
In the Data Source Configuration Wizard, on the Choose a Data Source Type page, select Database, and then click Next.
In the Choose Your Data Connection page, from the drop-down list, select Northwind.

Note

This page includes an option to create a connection—you could use this option rather than creating the connection explicitly, in the previous steps.
Click Next.
Depending on how you configured your data source, you may be asked if you want to copy the attached file into the current project. Although there are benefits to copying the data file locally (data separation, for example), you do not need to copy it for this example. Click No, if prompted.
In the Save the Connection String to the Application Configuration File page, accept the default option, and then click Next.
In the Choose Your Database Objects page, select Categories and Products, and change the DataSet name to NorthwindDataSet. When you are finished, the dialog box should look like Figure 5.
Click Finish. Your project now includes a new item, NorthwindDataSet.xsd, and its supporting files.

Figure 5. Choose Your Database Objects page
Right-click the Products TableAdapter, click Add, and then click Query.
In the Choose a Command Type page, click Next to accept the default option to create a query using a SELECT SQL statement.
In the Choose a Query Type page, click Next to accept the default option to create a SELECT statement that returns rows.

Note
This page includes an option to create a connection—you could use this option rather than creating the connection explicitly, in the previous steps.

In the Specify a SQL SELECT statement page, modify the existing SQL statement so that it looks like the following, adding the WHERE clause.

SELECT ProductID, ProductName, SupplierID, CategoryID, 
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
ReorderLevel, Discontinued FROM dbo.Products 
WHERE CategoryID=@CategoryID

Click Next.
In Choose Methods to Generate, modify the names of the methods to FillByCategoryID and GetDataByCategoryID. Click Finish. Note that Products TableAdapter now displays a second row containing the new queries.
Click File and then click Save All.
Click Window and then click Close All Documents.
In Solution Explorer, double-click CalendarTaskPaneControl.vb, loading it into the designer.
In the Data Sources window, expand the Categories table.
Select the CategoryName field, and from the drop-down list to the right of the field name, select ComboBox (this indicates to Visual Studio what type of control you want to create when you drag the field onto a design surface). Figure 6 shows the drop-down list.

Figure 6. Select the type of control you want to create
From the Data Sources window, drag the CategoryName field to the CalendarTaskPaneControl designer. Place it below any existing controls. You can arrange the controls so that the Label control is above the ComboBox control. You can delete the BindingNavigator that was added when you placed the ComboBox CategoriesBindingNavigator; you do not need it for this example.
Select the combo box, and rest the pointer over the control. A tiny arrow button appears in the upper-right corner of the control. Click the button, and a properties window appears. Set the Data Source property to CategoriesBindingSource as shown in Figure 7.

Figure 7. Set the binding properties of the ComboBox control
Add a Button control to the task pane, and set its name to insertButton. Set its Text property to Insert.
You must add a single line of code to fill the local data cache. To do that, double-click the custom task pane in the designer, which takes you to the Load event handler for the task pane. Add the following line of code to the event handler. This code fills a local DataTable named NorthwindDataSet.Categories with information about all the categories.
```
CategoriesTableAdapter.Fill(NorthwindDataSet.Categories)
```
The Insert button you created inserts a list of products from the selected category into the current worksheet, starting at the current cell. Continue following these steps to add this simple functionality.

Double-click the Insert button, and add the following code to its Click event handler. This code retrieves the Products table adapter (including the query you created previously), executes the query to retrieve a DataTable containing the products matching the selected category, and places the list of products into the worksheet at the current location.

' Retrieve a reference to the Products table adapter.
Dim adapter As New NorthwindDataSetTableAdapters.ProductsTableAdapter

' Retrieve the products associated with the 
' selected category.
Dim products As NorthwindDataSet.ProductsDataTable = _
adapter.GetDataByCategoryID(Me.CategoryNameComboBox.SelectedValue)

'Insert the data into Excel.
Dim rng As Excel.Range = Globals.ThisAddIn.Application.ActiveCell
Dim i As Integer
For Each product As NorthwindDataSet.ProductsRow In products
  rng.Offset(i, 0).Value2 = product.ProductName
  i += 1
Next

Note
You can assign the array directly to an Excel range, rather than posting each value individually. Doing so requires calculating the dimensions of the range first, and then assigning the array into the Value2 property of the range. For this example, doing so creates too much overhead.

Finally, save and run the project. In Excel, in the custom task pane, select a category from the list of categories. Click Insert, and the code inserts a list of products from the selected category into the current cell and the cells below it.

This example requires a small amount of code to perform its work—some situations require even less. If you choose, you can write all the code yourself instead of using the tools Visual Studio and Visual Studio 2005 Tools for Office SE provides. However you choose to approach the problem, ADO.NET provides a wide range of tools, classes, and data-binding support that you can use when building applications in Visual Basic.

For information on ADO.NET, see the following references:

Tip #10: Learn About Security Policies

When you develop VBA solutions, deployment is simple. For document-based solutions, you distribute the document and indicate to the user how to open it. Deploying Microsoft Office add-ins is slightly more complicated because you may need to instruct the user to change the macro security settings for the add-in to work. If you registered the add-in correctly, it should work.

By default, Windows applications based on the .NET Framework have full rights to run on any computer that they are installed on. The same is not true for Visual Studio 2005 Tools for Office SE add-ins, which by default have no trust, and only run on the computer that they were created on. When you create an add-in project, Visual Studio sets up the necessary security to allow the add-in to run on the current computer.

Visual Studio 2005 Tools for Office SE add-ins, which are installed within a Microsoft Office application and can execute unsafe code, require special security rights to run. Specifically, you must create and install a security policy that grants trust to the add-in based on criteria that you specify in the policy. For example, you can grant trust based on a cryptographic key, or on the location of the add-in. You cannot run the add-in without installing a security policy.

Setting up and configuring add-in security is beyond the scope of this article, but you must be aware of .NET Framework security and how it applies to Visual Studio 2005 Tools for Office SE add-ins. When you create an add-in, you need to set aside some time to create and manage security policies.

The Visual Studio 2005 Tools for Office SE project template includes a Setup project, along with your add-in project. You can explicitly build this project when you are ready to deploy your add-in (right-click the project in Solution Explorer and select Build from the context menu to build the Setup executable). After you create the Setup application, you also need to create the security policy for client computers. The following articles describe the process in more detail.

Conclusion

We hope these tips help you use your VBA knowledge as you migrate to Visual Studio 2005 and Visual Studio 2005 Tools for Office SE. The tips discussed here are a good place to start, but nothing beats experience. Take the time to convert an existing VBA customization to Visual Studio 2005 Tools for Office SE, and as you do so, remember the issues discussed in this article. Remember that a customization can contain both VBA and Visual Basic code; when you create your add-in project, convert your VBA code to Visual Basic code one function at a time.

About the Authors

Ken Getz is a developer, writer, and trainer, working as a senior consultant with MCW Technologies, LLC, a Microsoft Solution Provider. Ken co-authored AppDev's C#, ASP.NET, VB.NET, and ADO.NET courseware (see Microsoft Training at AppDev). He has co-authored several technical books for developers, including the best-selling ASP.NET Developer's Jumpstart, the Access Developer's Handbook series, and the VBA Developer's Handbook series. Ken is a technical editor for Advisor Publications' VB.NET Technical Journal, and he is a columnist for both MSDN Magazine and CoDe Magazine. Ken speaks regularly at many industry events, including Advisor Media's Advisor Live events, VSLive, and Microsoft Tech-Ed.

Jan Fransen is a writer, trainer, and developer specializing in Microsoft products. Jan authored AppDev’s Microsoft Visual Studio Tools for Office, Microsoft Office, and Visual Basic for Applications courseware, and co-authored AppDev’s .NET Framework 2.0 courseware (see Microsoft Training at AppDev). She has contributed to books about Microsoft Office, written white papers for publication on MSDN, and created samples designed to help developers get up to speed quickly on new Microsoft products and features.

Additional Resources

To learn about the Visual Studio 2005 user interface, read Don’t Freak Out About Visual Studio. Although the article discusses Visual Studio 2003, much of its content applies to Visual Studio 2005 as well.

To see how to apply these tips in actual applications, see the following white papers:

The following books by members of the Visual Studio 2005 Tools for Office SE team help you transition to the world of managed code and Visual Studio 2005 Tools for Office SE add-ins.

For more information about the Visual Basic language, we recommend the following books: