Convert VBA Code to Visual Basic When Migrating to Visual Studio 2005 Tools for Office

Article
05/23/2014

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

Dale Oberg, ArtinSoft.com

Revised: January 2006

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

Summary: Convert code from Visual Basic for Applications to Visual Basic when migrating to Microsoft Visual Studio 2005 Tools for the Microsoft Office System. This document highlights the reasons to migrate, the major code conversion issues, and additional resources for further details. (24 printed pages)

Contents

Introduction to Visual Studio 2005 Tools for Office
Why Migrate to Visual Studio 2005 Tools for Office?
Converting VBA Code to Visual Basic Code in Visual Studio 2005 Tools for Office
Conclusion
Additional Resources
About the Author

Introduction to Visual Studio 2005 Tools for Office

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

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 enhances these features for even more user productivity.

This article reviews detailed coding issues that you must address when migrating VBA code to Microsoft Visual Basic and Visual Studio 2005 Tools for Office. Two more articles in this series, Migrating Excel VBA Solutions to Visual Studio 2005 Tools for Office and Redesigning an Excel VBA Solution for .NET Using Visual Studio 2005 Tools for Office, focus on migrating complete projects from VBA to Visual Studio 2005 Tools for Office and also discuss architecture issues you should consider for VBA projects to take full advantage of Visual Studio 2005 Tools for Office.

Why Migrate to Visual Studio 2005 Tools for Office?

You gain many benefits when you migrate from VBA to Visual Studio 2005 Tools for Office, for example:

Improved development productivity. One reason to migrate from VBA to Visual Studio 2005 Tools for Office is to gain access to the integrated development environment between Visual Studio 2005 Tools for Office and Microsoft Visual Studio 2005. Visual Studio is one of the richest and most sophisticated development environments available, increasing coding and testing productivity.

Improved security model. Managed solutions benefit from the security model built into the common language runtime (CLR).

Easier deployment and versioning. Managed solutions allow more flexible deployment options and simplified deployment and updates.

Added functionality from the.NET Framework. Having access to the Base Class Libraries of the CLR allows you to use its new features such as XML readers, ADO.NET and simplified consumption of Web services.

Improved code reuse. Visual Basic is a fully object-oriented language and facilitates code reuse.

Easier integration. The.NET Framework facilitates integration with other applications using Web services.

Converting VBA Code to Visual Basic Code in Visual Studio 2005 Tools for Office

The following is a partial list of differences between VBA code and Visual Basic in Visual Studio 2005 Tools for Office.

Data Types

Some data types, such as Variant, Integer, Long, Currency and others, were changed or are obsolete in Visual Basic. For example, an Integer in VBA is equivalent to a Short in Visual Basic. Also, a Long in VBA is equivalent to an Integer in Visual Basic.

Fixed-Length Strings

Due to changes that allow Visual Basic arrays and structures (formerly known as user-defined types) to be fully compatible with other Visual Studio languages, the language no longer supports fixed-length strings. If a fixed-length string does not need an exact length, you can convert it to a regular Visual Basic String, as shown in the following code:

[VBA]
Dim MyFixedLengthString As String * 100

[Visual Basic]
Dim MyFixedLengthString As String

However, if a fixed-length string is required to be a specific byte length, then you can convert it to an array of bytes, as shown in the following code:

[Visual Basic]
Dim MyFixedLengthString(100) As Byte

Literal Values Instead of Windows Constants

When you use literal values instead of Microsoft Windows–based defined constants, you must do more work when migrating to Visual Studio 2005 Tools for Office. In the situation where literal values are used, you must determine the constant based on Windows, and then use the equivalent constant based on Visual Studio 2005 Tools for Office. For example:

[VBA]
btnRecalculate.MousePointer = 10  ' This is the literal constant for vbUpArrow

[Visual Basic]
btnRecalculate.Cursor = System.Windows.Forms.Cursors.UpArrow

Enumerations

In Visual Basic, you must fully qualify enumeration constants. When converting your VBA code, you must add the fully qualified enumeration name to your enumeration constants. For example:

[VBA]
Application.ActiveDocument.Paragraphs(1).Alignment = wdAlignParagraphRight

[Visual Basic]
ThisApplication.ActiveDocument.Paragraphs(1).Alignment = _
   Word.WdParagraphAlignment.wdAlignParagraphRight

Arrays

In both Visual Basic and VBA, the default lower bound of an array dimension is 0. In VBA, you can use Option Base to change the lower value to another number, which most often is 1. Visual Basic does not support the Option Base statement.

One way to migrate arrays with an Option Base 1 is to add 1 to the dimension of the array and then the target code contains an extra slot at index 0. In most circumstances, .NET Framework–based code causes some minor memory waste but continues to work.

Here is an example of some code using such an array:

[VBA]
Option Base 1
Private Sub Test()
   Dim myArr(10) As Integer   ' array is indexed 1..10
   addOne myArr
End Sub
Sub addOne(arr)
   For i = 1 To 10
      arr(i) = arr(i) + 1
   Next i
End Function

You could convert this to Visual Basic as shown in the following code:

[Visual Basic]
Private Sub Test()
' array is indexed 0 to 10 and has an unused element at 0.
   Dim myArr(11) As Short   
   addOne(myArr)
End Sub
Sub addOne(ByRef arr() As Short)
   Dim i As Integer
   For i = 1 To 10
      arr(i) = arr(i) + 1
   Next i
End Sub

The only situation where this does not work is when the VBA code depends on the lower bound of the array being 1 instead of 0. For example, the code may use the function LBOUND to determine the first index of the array or may use a combination of the LBOUND and UBOUND functions to determine the size of the array.

Of course the other option to migrate arrays that use Option Base is to rework all the array indexing values so that they are zero-based. This leads to the most efficient use of memory, but may require more work and care when migrating.

Another problem with arrays is that in Visual Basic you can no longer use ReDim to re-dimension an array that was not previously dimensioned with Dim. In Visual Basic, you encounter an additional problem with ReDim when you re-declare an array with a new rank. For example, the following code works in VBA, but you cannot easily migrate it to Visual Basic:

[VBA]
Private Sub Form_Load()
   Dim a1(10,10)
   a1(2, 2) = 5
   ReDim a1(10, 10, 10)
   a1(9, 9, 9) = 44
End Sub

One possible way to migrate this code to Visual Basic is to declare a new array and then copy the contents of the old array to the new array, as shown in the following code:

[Visual Basic]
Private Sub Form_Load()
   Dim a1(10, 10) As Integer
   a1(2, 2) = 5
   Dim a2(10, 10, 10)
   For Dimension1 As Integer = 0 To a1.GetLength(0) - 1
      For Dimension2 As Integer = 0 To a1.GetLength(1) - 1
         a2(Dimension1, Dimension2, 0 ) = a1(Dimension1, Dimension2)
      Next
   Next
   a2(9, 9, 9) = 44
End Sub

Dates

You can use VBA dates as integers. They can be combined with other values using numerical operators. You can add or subtract a date with an integer and the result is another date variable. All other usages of dates operated with numbers produce numerical values.

The following is an example of VBA code from a Microsoft Office Excel 2003 workbook with two named ranges, SuperconductorBeginDate and SuperconductorEndDate. The example shows how you can combine dates and integers.

' VBA code
Private Sub CalculateAverageDate()
   Dim currentProjectBeginDate As Date
   Dim currentProjectEndDate As Date
   currentProjectBeginDate = Range("SuperconductorBeginDate").Value
   currentProjectEndDate = Range("SuperconductorEndDate").Value
   averageDate = AvgDate(currentProjectBeginDate, currentProjectEndDate)
End Sub

Function AvgDate(dateBegin, dateEnd)
   numDays = dateEnd - dateBegin   ' difference between two dates in days
   If numDays > 21 Then
      ' calculate midpoint between two dates
      dateBegin = dateBegin + (dateEnd - dateBegin) / 2
   Else
      dateBegin = dateBegin + 7   ' calculate a week from this date
   End If
   AvgDate = dateBegin
End Function

When migrating code, you need to consider all these types of uses of the Date variable. You need to recognize expressions that VBA interprets as implicit Date operations and convert them into explicitly-cast operations needed in Visual Basic using the FromOADate and ToOADate methods. You could migrate this code as follows.

[Visual Basic]
Private Sub CalculateAverageDate()
   Dim averageDate As Date   ' New type declaration added
   Dim currentProjectBeginDate As Date
   Dim currentProjectEndDate As Date
   currentProjectBeginDate = Range("SuperconductorBeginDate").Value
   currentProjectEndDate = Range("SuperconductorEndDate").Value
   averageDate = AvgDate(currentProjectBeginDate, currentProjectEndDate)
End Sub

' Improved function declaration
Function AvgDate(ByRef dateBegin As Date, ByRef dateEnd As Date) As Date
   Dim numDays As Double     ' New type declaration added
   ' Compute the difference between two dates in days
   numDays = dateBegin.ToOADate - dateEnd.ToOADate
   If numDays > 21 Then
      ' Calculate midpoint between two dates
      dateBegin = System.Date.FromOADate(dateBegin.ToOADate + _
         System.Date.FromOADate(dateEnd.ToOADate - _
         dateBegin.ToOADate).ToOADate / 2)
   Else
      ' Calculate a week from this date
      dateBegin = System.Date.FromOADate(dateBegin.ToOADate + 7)
   End If
   AvgDate = dateBegin
End Function

Scope

Visual Basic now supports scoping of variables. In VBA, any variable declared anywhere in a method is accessible from anywhere in the method and is instantiated at the beginning of the method regardless of where it is declared. What this means is that all local variables in VBA have method scope. In Visual Basic, however, a variable is declared inside of a For or While loop for example, you can only access it within the scope of the For or While loop. For example:

[VBA]
Public Function GetSumTotal(ByVal LastNum As Integer)
Dim X As Integer
   For X = 1 To LastNum
      ' Total is instantiated when the function is called
      Dim Total As Integer  
      Total = Total + X
   Next X
   ' This will not generate an error because Total is in scope
   GetSumTotal = Total
End Function

' Visual Basic code
Public Function GetSumTotal(ByVal LastNum As Integer)
   Dim X As Integer
   ' The Total declaration has been moved to function scope
   Dim Total As Integer
   For X = 1 To LastNum
Total = Total + X
   Next X
   GetSumTotal = Total
End Function

Use of Parentheses in Procedure Calls

It is sometimes confusing to determine when to use, or not to use, parentheses ( ) to enclose argument lists with a VBA subroutine or function.

For example, subroutines or functions that do not receive arguments in VBA do not require parentheses. Otherwise, parentheses are required in the declaration. When calling a subroutine, you can either call the subroutine directly by name, or you may call it by using the Call statement. When calling a subroutine directly, you never use parentheses. When using the Call statement to call a function that takes arguments, you must use parentheses. When using the Call statement to call a function that does not take arguments, you do not use parentheses. The rules differ when working with functions and when assigning the result of a function call to a variable.

In Visual Basic, parentheses are required when passing parameters in a method call. In addition, statements containing function calls with parentheses are evaluated differently from statements containing function calls without parentheses. This is for two reasons:

Function parameters are evaluated differently.
The use of parentheses also determines whether or not the function returns a value.

The following table shows examples of calling subroutines or functions with and without arguments.

Table 1. VBA and Visual Basic subroutine and function calling syntax

Description	VBA code	Visual Basic code
Subroutine call without arguments	ProcessPayments	ProcessPayments()
Subroutine call without arguments but with Call statement	Call ProcessPayments	Call ProcessPayments()
Subroutine call with argument(s)	ProcessClient "Akers, Kim"	ProcessClient("Akers, Kim")
Subroutine call with argument(s) with Call statement	Call ProcessClient("Akers, Kim")	Call ProcessClient("Akers, Kim")
Function call with argument(s) and assignment of result	TotalPaymentAmount = GetTotalPaymentAmount("Akers, Kim")	TotalPaymentAmount = GetTotalPaymentAmount("Akers, Kim")
Function call with argument(s) without assignment	GetTotalPaymentAmount "Akers, Kim" OR GetTotalPaymentAmount("Akers, Kim")	GetTotalPaymentAmount("Akers, Kim")

Parameter Passing

In VBA, by default you pass parameters by reference. In Visual Basic, by default you pass parameters by value.

For example, the following code shows some functions with ByRef and ByVal parameters:

[VBA]
Sub myMethodByRef1(name)  ' parameter passed by reference
   name = "myMethodByRef1"  ' original argument changed
End Sub

Sub myMethodByRef2(ByRef name)  ' parameter passed by reference
   name = "myMethodByRef2"  ' original argument changed
End Sub

Sub myMethodByVal(ByVal name)  ' parameter passed by value
   name = "myMethodByVal"  ' change has no effect on original argument
End Sub

Private Sub Button_Click()
   Dim value
   value = "Nothing"
   myMethodByRef1 value
   MsgBox (value)  ' Displays "myMethodByRef1"
   myMethodByRef2 value
   MsgBox (value)  ' Displays "myMethodByRef2"
   myMethodByVal value
   MsgBox (value)  ' Displays "myMethodByRef2"
End Sub

When you migrate this code to Visual Basic, you must qualify and type the parameters appropriately, as shown in the following code:

[Visual Basic]
Sub myMethodByRef1(ByRef name_parameter As String) ' parameter passed by reference
   name_parameter = "myMethodByRef1"   ' original argument changed
End Sub

Sub myMethodByRef2(ByRef name_parameter As String) ' parameter passed by reference
   name_parameter = "myMethodByRef2"  ' original argument changed
End Sub

Sub myMethodByVal(ByVal name_parameter As String) ' parameter passed by value
   name_parameter = "myMethodByVal"  ' change has no effect on original argument
End Sub

Private Sub Button_Click()
Dim value As String
value = "Nothing"
   myMethodByRef1(value)
   MsgBox(value)  ' Displays "myMethodByRef1"
   myMethodByRef2(value)
   MsgBox(value)  ' Displays "myMethodByRef2"
   myMethodByVal(value)
   MsgBox(value)  ' Displays "myMethodByRef2", argument will not be changed
End Sub

Default Properties

In VBA, you can use shortcuts when typing code by eliminating the default properties. In Visual Basic, default properties are only supported if the properties take arguments. You can solve this problem in all cases where you use early binding and you know the type of the object used in the assignment by expanding the default property appropriately.

This is possible because these late-binding fixes allow the resolution of the type of an identifier based on its use in the code. After you know the type, you can determine its default property.

The following code reads the value of an Excel range named GrandTotal2005. The default property of a Range object is the Value property so grandTotal is assigned the value of the Range object.

[VBA]
Dim grandTotal
grandTotal = Range("GrandTotal2005") ' Default Property of Range is Value

[Visual Basic]
Dim grandTotal As Single = Range("GrandTotal2005").Value

Assignments

In VBA, you need the Set keyword to distinguish between assignment of an object and assignment of the default property of the object. Since Visual Basic does not support default properties, you do not need the Set keyword and it is no longer supported. When migrating, you should remove the Set keyword. Also, Visual Basic no longer requires the Let keyword. For example:

[VBA]
Dim ParagraphFormat1 As New Word.ParagraphFormat
Dim ParagraphFormat2 As New Word.ParagraphFormat
Dim WordWrapOn As Boolean

Set ParagraphFormat1 = ParagraphFormat2
ParagraphFormat1.WordWrap = False
Let WordWrapOn = ParagraphFormat2.WordWrap  ' Assigns False to WordWrapOn

[Visual Basic]
Dim ParagraphFormat1 As New Microsoft.Office.Interop.Word.ParagraphFormat
Dim ParagraphFormat2 As New Microsoft.Office.Interop.Word.ParagraphFormat
Dim WordWrapOn As Boolean

ParagraphFormat1 = ParagraphFormat2  ' No Set Keyword
ParagraphFormat1.WordWrap = False  ' No Let Keyword
WordWrapOn = ParagraphFormat2.WordWrap  ' Assigns False to WordWrapOn

TypeOf and TypeName Functions

In VBA, the TypeOf function is used in If. . .Then. . .Else statements to determine whether an object reference is of a specified object type. Although this continues to work in Visual Basic, user-defined types, which are now called structures, are not object types and cannot be evaluated by the TypeOf function. For example:

[VBA]
Type Account
   balance As Integer
End Type

Dim clientAccount As Account 
If TypeOf clientAccount Is Account Then 
   ProcessAccount(clientAccount)
End If

[Visual Basic]
Public Structure Account
   Dim balance As Integer
End Structure

Dim clientAccount As Account
' For structure type checking the following syntax can be used
If clientAccount.GetType Is GetType(Account) Then 
   ProcessAccount(clientAccount)
End If

Although the TypeName function is similar, it returns strings instead of object types. The problem is that these strings changed so they may not produce the expected results in Visual Basic. As shown in the following code, the VBA code displays "Workbook" as the TypeName, but the Visual Basic code displays "ThisWorkbook":

[VBA]
Sub TypeOfDemo()
   Set myType = Workbooks(1)
   If TypeOf myType Is Excel.Workbook Then
       MsgBox TypeName(myType)
   End If
End Sub

[Visual Basic]
Sub TypeOfDemo()
   Dim myType As Object
   myType = Globals.ThisWorkbook
   If TypeOf myType Is Excel.Workbook Then
      MsgBox(TypeName(myType))
   End If
End Sub

Control Structures

In Visual Basic, most of the control structures remain the same as in VBA, except the While statement. In VBA, a While statement ends with the WEnd keyword. In Visual Basic, a While statement ends with an End While statement. Visual Basic no longer supports the GoSub. . .Return and On. . .GoSub statements. In many circumstances, you can replace GoSub statements with new functions. "Fortunately, GoSub, GoTo, and Return statements are used very rarely in VBA applications and VBA developers' guides do not recommend them. Therefore, you probably will not find many GoSub or GoTo statements in your VBA code that you must migrate. Both VBA and Visual Basic support GoTo, which has identical behavior in both environments. However, when creating the label for the GoTo statement, remember that Error is a keyword in Visual Basic and thus cannot be the name of a label. The following example shows how to migrate a GoTo statement with a label called "Error", which is commonly used in VBA code:

[VBA]
Sub GoToDemo()
   Dim Num
   Num = 1
   If Num > 0 Then GoTo Error
   MsgBox Num
   Exit Sub
Error:
   MsgBox "Error!!!"
End Sub

[Visual Basic]
Sub GoToDemo()
   Dim Num As Integer
   Num = 1
   If Num > 0 Then GoTo ErrorLabel
   MsgBox(Num)
   Exit Sub
ErrorLabel:
   MsgBox("Error!!!")
End Sub

Exception Handling

Visual Basic introduces a completely new error handling mechanism called exception handling, which uses the Try, Catch and Finally keywords. Exception handling is the preferred way to handle almost all errors in Visual Basic. For example:

[Visual Basic]
Dim averageCost As Integer

Try
   averageCost = Range("TotalCost").Value / Range("TotalProjects").Value 
Catch ex As Exception
   averageCost = 0
Finally
   ' This code will be executed every time regardless of whether an Exception happens
   RecordAverageCost( averageCost ) 
End Try

This code example tries to calculate the average cost, but if it fails for any reason, then averageCost is set to 0. This code example catches all exceptions but is not always considered the most appropriate coding practice. A better coding practice is to catch only the specific exceptions that you know could be thrown. In this case, if a more general exception is thrown, then it is caught by a higher level catch that may be more able to handle the exception appropriately. The Finally statement is always executed regardless of whether there was an exception thrown or not.

Late Binding and Early Binding

You can declare variables without a type in VBA. All implicitly declared variables are of Variant type. For example:

[VBA]
Dim numParagraphs
' Late binding to a VariantLong type
numParagraphs = Application.ActiveDocument.Paragraphs.Count

Dim numParagraphs2 As Long
' Early binding of numParagraphs2 to a Long type
numParagraphs2 = Application.ActiveDocument.Paragraphs.Count

The variable numParagraphs is late bound to a VariantLong type when Application.ActiveDocument.Paragraphs.Count is assigned. The variable numParagraphs2 is early bound to a Long type when you declare it because the type is known when you create an instance of it. Technically, in Visual Basic, there is no late binding. For example:

[Visual Basic]
Dim numParagraphs  ' Early binding to an Object type
numParagraphs = Me.Paragraphs.Count

Dim numParagraphs2 As Long  ' Early binding to a Long type
numParagraphs2 = Me.Paragraphs.Count

When you compile this code, Visual Basic takes the following actions:

The variable numParagraphs is declared to be an Object type.

The value Me.Paragraphs.Count is placed into an object on the heap, not on the stack.

The numParagraphs variable stores a reference to the object on the heap.

As is the case with numParagraphs2, code using the numParagraphs variable performs slower than if counter were on the stack. Therefore, when migrating VBA value type variables that are late bound, you see a slight performance improvement, if you explicitly declare their types at instantiation. This is one of a few reasons why in both VBA and Visual Basic, you should always type all variables.

Multiple Variable Declarations

In VBA, you can declare variables of different types in the same statement, but you must specify the data type of each variable or it defaults to Variant. The following example shows multiple declarations and their resulting data types:

[VBA]
Dim I, J As Integer  ' I is Variant, J is Integer. 
Dim L As Integer, M As Integer  ' L is Integer, M is Integer.

In Visual Basic, you can declare multiple variables of the same data type without having to repeat the type keyword. You could migrate these declarations as shown in the following code:

[Visual Basic]
Dim I As Object 
Dim J As Short       ' J is Short. 
Dim L, M As Short    ' L is Short, M is Short.

Variant Arrays

An array whose type is not declared or whose type is Variant can also benefit from late binding fixes when migrating to Visual Basic. The solution is to change the type of arrays to a specific type based on the use of the array.

A problem arises when code inconsistently uses these arrays and stores different types of data in the arrays, as shown in the following code.

[VBA]
Dim A1(10)
A1(1) = Range("WirelessNetworkTitle").Comment 'first element is a String
A1(2) = 1 'second element is an Integer

One way to resolve this is to analyze the class hierarchy that is shared by the types that are stored in the array and choose the most specific super-class that can represent both types. If no super-class exists besides Object, which is the super-class of all classes, then you can determine if you can convert one of the types directly to the other type. For example:

[Visual Basic]
Dim A1(10) As String
A1(1) = Range("WirelessNetworkTitle").Comment 
A1(2) = CStr(1)

Windows APIs

VBA code that calls out to the Win32 API continues to work in Visual Basic, but you may have to change some of the data types. The following code declares an external function, which is called as if it were a normal VBA function. The declaration and the call are the same in Visual Basic. For example:

[VBA]
Declare Function GetTickCount Lib "kernel32" () As Long
Sub APIsDemo()
   L = GetTickCount()
End Sub

[Visual Basic]
' A Long in VBA is an Integer in Visual Basic
Declare Function GetTickCount Lib "kernel32" () As Integer 
Sub APIsDemo()
   Dim L As Integer 
   L = GetTickCount()
End Sub

Although Win32 API calls from VBA should work the same in Visual Basic, it may be optimal to convert many of the Win32 API calls to .NET Framework-based equivalents.

For more information about .NET Framework-based equivalents, see Microsoft Win32 to Microsoft .NET Framework API Map.

Printer Object and Printers Collection

There is no support for the VBA Printer object or Printers Collection objects in Visual Basic. Applications that use these features to print from the code require considerable rework. One way to migrate VBA Printer object to Visual Basic is to use the System.Drawing.Printing Namespace object for printing.

Clipboard

You can map VBA code that reads and write to the clipboard to the My.Computer.Clipboard object in Visual Basic. For example:

[VBA]
Dim tempData As DataObject
Dim clipboardString As String
Set tempData = New DataObject
' Clears the clipboard
tempData.SetText ""
tempData.PutInClipboard
' Puts the text from an Excel cell into the clipboard
clipboardString = Range("WirelessNetworkTitle").Value
tempData.SetText clipboardString
tempData.PutInClipboard
' Gets the text on the clipboard into a string variable
tempData.GetFromClipboard
clipboardString = ""
clipboardString = tempData.GetText

[Visual Basic]
Dim clipboardString As String
' Clears the clipboard
My.Computer.Clipboard.Clear()
' Puts the text from an Excel cell into the clipboard
My.Computer.Clipboard.SetText( _
   Range("WirelessNetworkTitle").Value.ToString())
' Gets the text on the clipboard into a string variable
clipboardString = My.Computer.Clipboard.GetText()

Microsoft Office 2003 Object Model

The Microsoft Office 2003 object model is COM-based and the same COM components are available from either VBA or Visual Basic. However, to access the Microsoft Office 2003 object model from Visual Basic you need to have wrappers in .NET Framework–based code around the COM components, which are then called from your Visual Basic program. Microsoft Office 2003 Editions provide these wrapper classes, called primary interop assemblies, so that you can access the Office object model from .NET Framework–based code. Primary interop assemblies may optionally change how you call COM components to optimize calling from Visual Basic. However, most of the functionality is relatively the same and you can map VBA calls into the Office 2003 object model to equivalent calls in Visual Basic.

VBA Object Model

The VBA object model contains over 350 functions for which there are no primary interop assemblies. You must map these to Visual Basic equivalents. You can migrate most elements of the VBA object model to a Visual Basic equivalent, except those in the following table.

Table 2. VBA object model functions with no Visual Basic equivalent based on the .NET Framework

VBA object model function	Description
vba._HiddenModule.InputB	For accessing files at binary level
vba._HiddenModule.InputB$	For accessing files at binary level
vba._HiddenModule.ObjPtr	Undocumented functions for working with pointers
vba._HiddenModule.StrPtr	Undocumented functions for working with pointers
vba._HiddenModule.VarPtr	Undocumented functions for working with pointers
vba.Conversion.CVErr	Create user-defined errors
vba.Conversion.MacID	Used on Macintosh computers in place of wildcards
vba.DateTime.Calendar	Used for working with non-Gregorian calendars
VBA.VbCalendar.vbCalGreg	Used for working with non-Gregorian calendars
VBA.VbCalendar.vbCalHijri	Used for working with non-Gregorian calendars
vba.Strings.AscB	Used for manipulating non-Unicode strings
vba.Strings.ChrB	Used for manipulating non-Unicode strings
vba.Strings.ChrB$	Used for manipulating non-Unicode strings
vba.Strings.InStrB	Used for manipulating non-Unicode strings
vba.Strings.LeftB	Used for manipulating non-Unicode strings
vba.Strings.LeftB$	Used for manipulating non-Unicode strings
vba.Strings.LenB	Used for manipulating non-Unicode strings
vba.Strings.MidB	Used for manipulating non-Unicode strings
vba.Strings.MidB$	Used for manipulating non-Unicode strings
vba.Strings.RightB	Used for manipulating non-Unicode strings
vba.Strings.RightB$	Used for manipulating non-Unicode strings
VBA.VbCompareMethod.vbDatabaseCompare	Enum, String Compare, String Functions
VBA.VbFileAttribute.vbAlias	Dir, Get, Set attributes
VBA.VbMsgBoxStyle.vbDefaultButton4	Used to specify that fourth button in a dialog box is default.
VBA.VbStrConv.vbFromUnicode	Used with the StrConv function to convert to and from Unicode
VBA.VbStrConv.vbUnicode	Used with the StrConv function to convert to and from Unicode
VBA.VbVarType.vbDataObject	Objects that do not support the IDispatch interface

App Object

The App object in VBA has no direct equivalent in Visual Basic. You could migrate some App object functionality using other Visual Basic functionality that is similar. For example, you can map the App.LogEvent to the My.Application.Log.WriteEntry method in Visual Basic, as shown in the following example:

[VBA]
App.LogEvent "Error", vbLogEventTypeError
App.LogEvent "Warning", vbLogEventTypeWarning
App.LogEvent "Information", vbLogEventTypeInformation

[Visual Basic]
My.Application.Log.WriteEntry("Error", _
       System.Diagnostics.TraceEventType.Error)
My.Application.Log.WriteEntry("Warning", _
       System.Diagnostics.TraceEventType.Warning)
My.Application.Log.WriteEntry("Information", _
       System.Diagnostics.TraceEventType.Information)

Forms Collection

The Forms Collection object in VBA has no direct equivalent in Visual Basic. However, you can migrate the Forms Collection to the My.Application.Forms object in Visual Basic. For example, the following code migrates part of VBA Forms Collections object to the Visual Basic My.Application.Forms object:

[VBA]
Form2.Show
i = Forms.Count
Forms(1).Caption = "Trey Research Budget Projections"
Forms.Item(1).Caption = "Project Details"

[Visual Basic]
Dim x As Integer
Form2.Show ()
x = My.Application.OpenForms.Count
My.Application.OpenForms.Item(1).Text = "Trey Research Budget Projections"
My.Application.OpenForms.Item(1).Text = "Project Details"

UserForms

Visual Basic does not support the VBA UserForms. You should migrate these to Windows Forms.

The core of a VBA Form is the MSForms type library. The core of a Visual Basic Form is the Visual Basic type library. Both packages are very similar and contain a Control coclass and IControl interface. Label controls, TextBox controls, and even the events are the same for most of the controls in VBA and Visual Studio 2005 Tools for Office.

The names for some controls change in Visual Studio 2005 Tools for Office, but the functionality usually remains the same. You can use the following mappings from VBA to Windows Forms for migrating.

Table 3. Mapping VBA controls to Visual Studio 2005 Tools for Office controls

VBA control	Visual Studio 2005 Tools for Office control
MSForms.Label	System.Windows.Forms.Label
MSForms.TextBox	System.Windows.Forms.TextBox
MSForms.Frame	System.Windows.Forms.GroupBox
MSForms.ComboBox	System.Windows.Forms.ComboBox
MSForms.ListBox	System.Windows.Forms.ListBox
MSForms.CheckBox	System.Windows.Forms.CheckBox
MSForms.OptionButton	System.Windows.Forms.RadioButton
MSForms.ToggleButton	System.Windows.Forms.CheckBox
MSForms.CommandButton	System.Windows.Forms.Button
MSForms.TabStrip	System.Windows.Forms.TabControl
MSForms.MultiPage	System.Windows.Forms.TabControl
MSForms.ScrollBar (Horizontal)	System.Windows.Forms.HscrollBar
MSForms.ScrollBar (Vertical)	System.Windows.Forms.VscrollBar
MSForms.SpinButton	System.Windows.Forms.DomainUpDown
MSForms.Image	System.Windows.Forms.PictureBox
Microsoft.Vbe.Forms.RefEdit	Microsoft.Vbe.Forms.RefEdit

Functionally, the behavior of most of the controls is the same. However, in the case of the Multipage and SpinButton controls, you may need compatibility classes to achieve the same functionality as in VBA.

Visual Basic no longer supports some events that were supported in VBA Forms; that is, they do not map directly to a Visual Basic event and must be replaced. For example, you may need to replace the Error event present in many VBA controls with another error handling mechanism such as Visual Basic exception handling.

Drag and Drop

The models for drag and drop functionality are significantly different so you must rewrite any code performing drag and drop actions. Every Microsoft .NET-based Windows Forms control has four events that are used for drag and drop: DragDrop, DragEnter, DragLeave, and DragOver. For more information about implementing drag and drop, see Implementing Drag and Drop in Visual Basic .NET.

Dynamic Data Exchange (DDE)

Dynamic Data Exchange (DDE) is a mechanism of exchanging information between applications in VBA. Visual Basic no longer supports DDE methods. You should revise applications that depend on DDE to use another method of inter-application communication or leave them in VBA. Microsoft .NET remoting framework is a popular form of inter-application communication in Visual Basic that you can use to replace DDE functionality.

ActiveX Data Objects, Data Access Objects, and Remote Data Objects

Visual Basic introduced an enhanced version of ActiveX Data Objects (ADO) called ADO.NET that is optimized to work with data in distributed applications and that provides performance improvements over ADO when used in distributed applications. The following codes shows a conversion from ADO to ADO.NET:

[VBA]

Sub Test1()
   Dim con As New ADODB.Connection
   con.ConnectionString = "Provider=SQLOLEDB.1;Integrated" + _
      "Security=SSPI;Persist Security Info=False;Initial" + _
      "Catalog=Northwind"
   con.Open

   Dim com As New ADODB.Command
   com.ActiveConnection = con

   Dim rec As ADODB.Recordset
   com.CommandText = "Select * from Employees"
   Set rec = com.Execute
   While Not rec.EOF
      Dim var As Variant
      var = rec.Fields.Item(1).Value
      'Do Something
      rec.MoveNext
   Wend
End Sub

[Visual Basic]

Sub Test1()
   Dim con As New System.Data.SqlClient.SqlConnection
   con.ConnectionString = "Integrated Security=SSPI;Persist Security" + _ 
      "Info=False;Initial Catalog=Northwind"
   con.Open()

   Dim com As New System.Data.SqlClient.SqlCommand
   com.Connection = con

   Dim rec As System.Data.SqlClient.SqlDataReader
   com.CommandText = "Select * from Employees"
   rec = com.ExecuteReader
   While rec.Read
      Dim var As Object
      var = rec.Item(1)
      'Do Something
   End While
End Sub

You can still use Data Access Object (DAO), Remote Data Objects (RDO), and ADO in code from Visual Basic with some modification. However, Visual Basic does not support DAO and RDO data binding to controls, data controls, or RDO User connection. You must convert the DAO or RDO data binding to ADO before upgrading your project to Visual Basic, if you want to use the ADO data bindings in Windows Forms in the .NET Framework.

For more information about ADO and ADO.NET, see the following articles:

Conclusion

This article discusses many of the issues you may encounter when converting code from VBA to Visual Basic when migrating to Visual Studio 2005 Tools for Office. Migrating to Visual Basic has significant advantages and is worth serious consideration for many VBA projects. However, this process can be meticulous. Automation tools would make this migration process more systematic, less error prone, and quicker.

Additional Resources

For more information, see the following resources:

Visual Studio 2005 Tools for Office

Migrating VBA Code

VBA

Microsoft ISV Community Center: Visual Basic for Applications

Microsoft Office Object Models

Primary Interop Assemblies

Primary Interop Assemblies (PIAs)

Migrating

Microsoft Visual Basic Developer Center: Upgrading to Visual Basic 2005

Office Developer Center

Microsoft Office Developer Center

Code Security

About the Author

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