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

Question

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

Possible duplicate of [Option Strict On disallows late binding](http://stackoverflow.com/questions/12375405/option-strict-on-disallows-late-binding) — MusicLovingIndianGirl, Apr 28 '16 at 07:05
to fine last row `XlWorkSheet.Range.UsedRange.Rows.Count` may help you. But it has some conditions. — Yog, Apr 28 '16 at 08:28

score 3 · Accepted Answer · answered Apr 28 '16 at 07:43

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

Thanks a lot. I managed to fix the issue. I have updated the question with the solution that worked for me. — , Apr 28 '16 at 10:02

score -2 · Answer 2 · answered Apr 28 '16 at 07:22

-2

Maybe writing

Option Strict Off

at the very beginning of the file? (before imports)

answered Apr 28 '16 at 07:22

shadow

1,883
1
16
24

The OP has clearly mentioned she doesn't want to turn it to OFF – MusicLovingIndianGirl Apr 28 '16 at 07:24
My bad. I didn't see that. – shadow Apr 28 '16 at 08:20

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

2 Answers2