Convert VBA Code to Visual Basic When Migrating 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 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
Microsoft Office Developer Center: Visual Studio Tools for Office Developer Portal
Microsoft Visual Studio 2005 Tools for the Microsoft Office System blog
Visual Studio Tools for the Microsoft Office System: Office Development Samples
Migrating VBA Code
Migrating Excel VBA Solutions to Visual Studio 2005 Tools for Office
Redesigning an Excel VBA Solution for .NET Using Visual Studio 2005 Tools for Office
Migrating a Word VBA Solution to Visual Basic Using Visual Studio 2005 Tools for Office
Converting Microsoft Office VBA Macros to Visual Basic .NET and C#
VBA
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
Primary Interop Assemblies
Migrating
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.