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 
   .Add(.Last)
End With

You must refer to the Range of the Last section

With Application.ActiveDocument.Sections 
   .Add(.Last.Range)
End With


A new section will not be added.

objField.Code.ToString.IndexOf 

You must refer to the Text of the Code

objField.Code.Text.ToString.IndexOf 




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 


Range.Value

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;


VBA.Abs

ABS - Returns the absolute value of a number.

Abs(-40) 
System.Math.Abs(-40) 


VBA.CInt

CINT - Returns a number converted to an integer datatype.

CInt(0.2) 
System.Convert.ToInt32(0.2) 


VBA.Round

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

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


VBA.Rnd

RND - Returns a random number between 0 and 1


VBA.Fix

FIX - Returns the integer portion of a number


VBA.Mod

MOD - Returns the remainder after division


VBA.Log

LOG - Returns the natural logarithm of a number


VBA.Exp

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


VBA.Hex

HEX - Returns the number converted to a hexadecimal

Hex(iValue) 
iValue.ToString("X")


VBA.Int

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

Int(0.9) = 0 
System.Math.Floor(0.9) 


VBA.IsNumeric

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

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


VBA.Sqr

SQR - Returns the square root of a number.

Sqr(9) 
System.Math.Sqrt(9) 


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


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



Workbooks

VBAC#
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");




Worksheets

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

VBAC#
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") 
  
objRange.Offset(1,1)objRange.get_Offset(1,1)
objRange.Resize(2,2)objRange.get_Resize(2,2)
  
Cells(1,1)Cells[1,1];
 Cells.get_Item(1,1);
  
For icellno = 1 To objRange.Cells.Countforeach (Excel.Range objcell in objRange)
Next icellno{
 }
  
  
 objCell.Value2.ToString();
 System.Convert.ToInt16(objCell.Value2.ToString());
  
 '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

PrevNext