Rekha Rekha - 7 months ago 31
Vb.net Question

How to fix late binding error in the function I have to find last row in an Excel column?

I have a function to find last row in a specific column in excel.

Below is the code


Private Function FindLastRowInColumn(ByVal XlWorkSheet As Excel.Worksheet, ByVal ColumnName As String) As Long
Dim LastRow As Long
With XlWorkSheet
LastRow = .Cells(.Rows.Count, ColumnName).End(Excel.XlDirection.xlUp).Row
End With
Return LastRow
End Function


I am getting error on the line


LastRow = .Cells(.Rows.Count, ColumnName).End(Excel.XlDirection.xlUp).Row


Error: Option Strict On disallows late binding.


How can I fix this error without turning off
Option Strict
?

I managed to fix the issue with the recommendations from jmcilhinney.
Below is the code that worked for me.


Public Function FindLastRowInColumn(ByVal XlWorkSheet As Excel.Worksheet, ByVal ColumnName As String) As Long
Dim LastRow As Long
With XlWorkSheet
LastRow = CType(.Cells(.Rows.Count, ColumnName), Excel.Range).End(Excel.XlDirection.xlUp).Row
End With
Return LastRow
End Function

Answer

First determine which member is late-bound. That's easy by simply typing the line out again and noting which member is not suggested by Intellisense. You will then need to determine what type the member belongs to and, assuming that it's possible, cast your reference to the object as that type. For instance, this is late binding:

Dim obj As Object = "Hello World"
Dim length As Integer = obj.Length

obj is an Object reference and Length is not a member of type Object. It is a member of type String though, and the object referred to by obj is a String so it can be cast as that type to achieve early binding:

Dim obj As Object = "Hello World"
Dim length As Integer = CStr(obj).Length
Comments