How Excel VBO in 5.0.33 destroyed my weekend (again locale issue)

Some weeks ago I’ve discovered that even if BP was updated on customer environment, business objects were not.
So I decided do update the Excel VBO at the last version: 5.0.33 and I started to see something strange.
When I tried to set a formula in an excel cell the formula did not work anymore, indeed Excel was not able anymore to compute the formulas anymore, moreover i had had similar problems with float values.

1. Static code comparison

I decided do drill down the problem and I started from the static comparison of the code between old code and new code.
At first I noticed the approach is more rational than the previous versions, so well done BP Team, but I also discovered that something changed in the actions below :

Get Worksheet As Collection
Old code

 Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname, False)

 ' Do we have a sheet?
 sheetexists = ws IsNot Nothing
 ' No sheet? No entry.
 If Not sheetexists Then Return

 ws.Activate()
 ws.UsedRange.Select()
 ws.UsedRange.Copy()

 Dim data As String = Clipboard.GetDataObject().GetData(DataFormats.Text, True)
 
 ' The data split into rows
 Dim asRows() As String = Split(data, vbCrLf)
 
 Dim table As New DataTable()
 ' Set a flag indicating the header row
 Dim isHeaderRow As Boolean = True
 
 For Each strRow As String In asRows
  If Not String.IsNullOrEmpty(strRow) Then
  
   Dim fields() As String = Split(strRow, vbTab)
   If isHeaderRow Then
   
    isHeaderRow = False
    For Each field As String in fields
     table.Columns.Add(field)
    Next
    
   Else

    Dim row as DataRow = table.NewRow()
    For i As Integer = 0 To fields.Length - 1
     If i < fields.Length Then
      row(i) = fields(i)
     Else
      row(i) = ""
     End If
    Next I
    table.Rows.Add(row)
    
   End If
    
  End If
  
 Next
 worksheetcollection = table

OOB 5.0.33

  Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname, False)

 ' Do we have a sheet?
 sheetexists = ws IsNot Nothing
 ' No sheet? No entry.
 If Not sheetexists Then Return

 ws.Activate()
 ws.UsedRange.Select()
 ws.UsedRange.Copy()

 Dim data As String = GetClipboardText()
 
 worksheetCollection = ParseDelimSeparatedVariables( _
  data, vbTab, Nothing, True)

Set Cell Value
Old code

GetInstance(handle).ActiveCell.Value = value

OOB 5.0.33

GetInstance(handle).ActiveCell.Value = value
Dim activeCell = GetInstance(handle).ActiveCell
SetProperty(activeCell, "Value", value)

WriteColl
Old code

 ' Get to the cell
 Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname)
 Dim origin as Object = ws.Range(cellref,cellref)
 Dim cell as Object = origin

 Dim colInd as Integer = 0, rowInd as Integer = 0 ' Offsets from the origin cell
 
 ' Deal with the column names first
 If includecolnames Then
  For Each col as DataColumn in collection.Columns
   Try
    cell = origin.Offset(rowInd, colInd)
   Catch ex as Exception ' Hit the edge.
    Exit For
   End Try
   cell.Value = col.ColumnName
   colInd += 1
  Next
  rowInd += 1
 End If
 
 ' Now for the data itself
 For Each row as DataRow in collection.Rows
  colInd = 0
  For Each col as DataColumn in collection.Columns
   Try
    cell = origin.Offset(rowInd, colInd)
   Catch ex as Exception ' Hit the edge.
    Exit For
   End Try
   'MessageBox.Show("RowOffset:" & rowInd & "; ColOffset:" & colInd & "; cell: " & cell.Address(False,False))
   cell.Value = row(col)
   colInd += 1
  Next
  rowInd+=1
 Next

OOB 5.0.33

 Get to the cell
Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)
Dim origin As Object = ws.Range(cellref, cellref)
Dim cell As Object = origin

Dim colInd As Integer = 0, rowInd As Integer = 0 ' Offsets from the origin cell

' Deal with the column names first
If includecolnames Then
 For Each col As DataColumn In Collection.Columns
  Try
   cell = origin.Offset(rowInd, colInd)
  Catch ex As Exception ' Hit the edge.
   Exit For
  End Try
  SetProperty(cell, "Value", col.ColumnName)
  colInd += 1
 Next
 rowInd += 1
End If

' Now for the data itself
For Each row As DataRow In Collection.Rows
 colInd = 0
 For Each col As DataColumn In Collection.Columns
  Try
   cell = origin.Offset(rowInd, colInd)
  Catch ex As Exception ' Hit the edge.
   Exit For
  End Try
  'MessageBox.Show("RowOffset:" & rowInd & "; ColOffset:" & colInd & "; cell: " & cell.Address(False,False))
  SetProperty(cell, "Value", row(col))
  colInd += 1
 Next
 rowInd += 1
Next

I highlighted what I think is the most interesting change: BP decided to use late binding instead of early binding when dealing with read/write operations.
It’s not hard to understand that this seems to be the problem so I tried to discover what are the main differences between early binding in late binding, so I found this article on MSDN which clarifies everything (more or less):
Late binding is still useful in situations where the exact interface of an object is not known at design-time. If your application seeks to talk with multiple unknown servers or needs to invoke functions by name (using the Visual Basic 6.0 CallByName function for example) then you need to use late binding. Late binding is also useful to work around compatibility problems between multiple versions of a component that has improperly modified or adapted its interface between versions 
It’s also true that Microsoft does not encourage this kind of approach, indeed in the same article I read this:
Microsoft Office applications provide a good example of such COM servers. Office applications will typically expand their interfaces to add new functionality or correct previous shortcomings between versions. If you need to automate an Office application, it is recommended that you early bind to the earliest version of the product that you expect could be installed on your client's system. For example, if you need to be able to automate Excel 95, Excel 97, Excel 2000, and Excel 2002, you should use the type library for Excel 95 (XL5en32.olb) to maintain compatibility with all three versions. 

Even if I don’t like this approach because it does not help performances I also don’t understand why it causes the problem I told you before, did you try to wrap reviews issue using visual studio using the involvement code lines from global code of the business objects.

2. Dynamic code comparison

I created a fake process which did read/write on excel and i noticed no regressions in GetWorksheetAsCollection, instead of the other two methods, so i focused on the method below, which is called by WriteColl and SetCellValue and is ultimately responsible of data write in Excel.

Private Sub SetProperty(Instance As Object, Name As String, ParamArray args As Object())
    Dim culture = Thread.CurrentThread.CurrentCulture
    'culture = Globalization.CultureInfo.GetCultureInfo(1033)

    Instance.GetType().InvokeMember(Name, Reflection.BindingFlags.SetProperty, Nothing, Instance, args, culture)
End Sub

I discovered that the problem was regional settings and not the late binding itself, indeedn using these references below i figured out that, when you have to write data on Excel, it's safe to use en-Us locale settings:

1. How to: Make String Literals Region-safe in Excel Using Reflection
2. Globalization and Localization of Excel Solutions

So I decided to retest everything with Visual Studio, consider that my culture is IT

Wrong result using current culture

Right result just forcing none culture

3. Conclusion

After this analysis i discovered that, if you are currently on machines with locale different from en-US, you could have problems with data type different from string or text, so you can apply my solution or rollback just the SetCellValue action to ensure you a better weekend.