VBA Migration

Fully Qualified Enumerations

In VBA enumerations were considered to be global but in .NET they are not. This means that in .NET enumerations must be fully qualified with their full enumeration name.

Selection.Find.Wrap = Word.wdFindWrap.wdFindContinue 

Word uses loosely typed enumerations
Excel and other applications create typed enumerations which are easier to resolve.

In VBA enumeration constants could be replaced with their numerical value equivalent. This is not possible in VB.NET. Any numerical constants have to be replaced with the corresponding enumeration name. In VB.NET enumerations are classified as formal types and can therefore no longer be implicitly converted to different datatypes.

In VBA the only type of enumeration in Integer. In VB.NET because enumerations are actual types you must access them with the fully qualifier name of the enumeration.

Ten Code Conversions for VBA VB and C#

No Default Properties

In VBA you could assume the default property.
In .NET you must be explicit
For example in Excel Range("B3") = "some text"
is shorthand for Range("B3").Value = "some text"
For example in Excel sMyVariable = ActiveCell
is shorthand for sMyVariable = ActiveCell.Value

For example in Word Cell(1,1).Range = "some text"
is shorthand for Cell(1,1).Range.Text = "some text"
For example in Word ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range = ""
is shorthand for ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = ""

This cannot be converted into a Range

CType(Application.Selection, Word.Range) 

You must refer to the Range of the Selection

CType(Application.Selection.Range, Word.Range) 

A new section will not be added.

With Application.ActiveDocument.Sections 
End With

You must refer to the Range of the Last section

With Application.ActiveDocument.Sections 
End With

A new section will not be added.


You must refer to the Text of the Code


Method Calls with Parentheses

Functions and subroutines require parentheses, but methods don't

Call MsgBox("My Message") 

In VBA when you use methods, spaces are required rather than parentheses

MsgBox "My Message" 

In C# parentheses are required when passing parameters to a method call

Implicit References

In VBA there is an implicit reference to Application

Inconsistency - Automatic datatype conversion

some datatypes change automatically, but others do not
The following line is fine is VBA as automatic type conversion happens automatically.

sMyVariable = txtTextBox.Text * txtAnotherTextBox.Text 

Although this is not true for all operators. The following line however will generate a compile error ?

sMyVariable = txtTextBox.Text + txtAnotherTextBox.Text 

Type conversion in this case will have to be explicit

sMyVariable = Val(txtTextBox.Text) + Val(txtAnotherTextBox.Text) 

Inconsistency - Value types without a Set keyword

Some variables have to be initialised with the Set keyword but not all of them.
The following line does not require Set

vVariable = ActiveCell 

but other variables do

Set vWsh = ActiveSheet 


The value of objRange.Value is Nothing when the cell is empty

Dim objRange As Excel.Range 
If objRange.Value Is Nothing Then
'the cell is empty
End If

Before referring to any cell you should call the clsCell.TypeReturn function to check the contents of the cell

If clsCell.TypeReturn(objRange) = "Number" Then 
End If

sText = (string)objCell.Value2  'this works when the cell contains text  
sText = (string)objCell.Value2.ToString() 'this works when the cell doesn't contain text

what if the cell is empty ??

objRange.Value = arArray 

test for different size and make this bullet proof !!
what if cells are formatted as text / values etc

For Each does not work

You must use For Loop instead of For - Each for the following collections:
Workbook.Names Collection
Range.Cells Collection

The following code does not work

Dim objName As Excel.Name 
For Each objName In Workbooks.Names
   objName = objNames.Item(inamescount)

Next objName

Use must use a For Loop instead

Dim objName As Excel.Name 
Dim icount As Integer
For icount = 1 To Workbooks.Names.Count

Next icount

The following code does not work

Dim objCell As Excel.Cell 
For Each objCell In Range.Cells

Next objCell

Use must use a For Loop instead

Dim objCell As Excel.Cell 
Dim icount As Integer
For icount = 1 To Range.Cells.Count
   objcell = CType(objSelection.Item(icellcount), Excel.Range)

Next icount

Integer Division

VB.NET rounds, C# truncates

Dim itotal As Integer = 20; 
Dim ivalue As Integer = 3
Dim iresult As Integer = itotal / ivalue
int itotal = 20; 
int ivalue = 3
int iresult = itotal / ivalue;


ABS - Returns the absolute value of a number.



CINT - Returns a number converted to an integer datatype.



ROUND - Returns a number rounded to a given number of decimal places

System.Math.Round(0.149,2, MidPointRounding.AwayFromZero) 


RND - Returns a random number between 0 and 1


FIX - Returns the integer portion of a number


MOD - Returns the remainder after division


LOG - Returns the natural logarithm of a number


EXP - Returns the base of natural logarithm raised to a power


HEX - Returns the number converted to a hexadecimal



INT - Returns the number rounded down to the nearest integer.

Int(0.9) = 0 


ISNUMERIC - Returns the value indicating if an expression contains a number.

System.Char.IsNumber(sCharacter.Chars(0)) = True 


SQR - Returns the square root of a number.


Application Object

Referring to the Application object:
In Add-ins - cls1Constants.gApplication.ActiveWorkbook
In document level - Globals.ActiveWorkbook

Globals.ThisAddin.Application ?

Globals.Sheet1.Rows.getItem(12,System.Type.Missing) ?

Dim objChartObjects As Excel.ChartObjectsExcel.ChartObjects objChartObjects
objChartObjects = objWsh.ChartObjectsobjChartObjects = (Excel.ChartObjects)objWsh.ChartObjects(System.Type.Missing);


Dim objWkbs As Excel.WorkbooksExcel.Workbooks objWkbs;
objWbks = Application.Workbooksobjwkbs = (Excel.Workbooks)Globals.Workbooks;
Dim objWbk As Excel.WorkbookExcel.Workbook objWbk;
objWbk = Application.Workbooks("Book1.xls")objwkb = Application.Workbooks.get_item("Book1.xls");


Dim objWsh As Excel.WorksheetExcel.Worksheet objWsh;
objWsh = Application.Sheets(1)objWsh = (Excel.Worksheet)Globals.Application.Sheets(1);
objWsh = Application.Sheets("WshName")objWsh = (Excel.Worksheet)Globals.ThisWorkbook.Worksheets.get_Item("WshName");
objWsh = Sheet1objWsh = (Excel.Worksheet)Globals.Sheet1;

Cells & Ranges

Dim objRange As Excel.RangeExcel.Range objRange;
objRange = objWsh.Range("A1")objRange = (Excel.Range)objWsh.get_Range("A1",System.Type.Missing);
objRange = objWsh.Range("NamedRange")objRange = (Excel.Range)objWsh.get_Range("NamedRange",System.Type.Missing);
 objRange = (Excel.Range)objRange.Cells[1,2];
objRange = Range("A1","D4") 
For icellno = 1 To objRange.Cells.Countforeach (Excel.Range objcell in objRange)
Next icellno{
 'the following line does not work when the cell is empty ?
 semptycell = System.Convert.ToString(objCell.Value2);
 'this line works when the cell contains a text string ??
 semptycell = (string)objCell.Value2
 if (semptycell ! Null)
  scellvalue = objCell.Value2.ToString();

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited