0

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
  • 2
    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

2 Answers2

3

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
jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
  • 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
-2

Maybe writing

Option Strict Off 

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

shadow
  • 1,883
  • 1
  • 16
  • 24