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 data types.


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.



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 


Arrays

In VBA you can have Option Base 1
In C# (and VB.Net) this is not supported andall arrays must start at 0



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


Named Ranges

You cannot use array syntax to access the named ranges

myWbk.Names.Item(rangeName) 
// the following will not work
myWbk.Names[rangeName]

Inconsistency - Automatic data type conversion

some data types 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


Application Object

In VBA there is an implicit reference to Application
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();
 }



© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext