option strict on disallows late binding

I have an application that if I leave the option strict off the application works fine, but I would like to have it on.  A certain part of my code has this error message, and I am having a hard time correcting the code.  Any suggestions?
 Dim BlankCell As Excel.Range

 '<---Find blank cells--->'
        BlankCell = oWS.Columns(1).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(2).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(3).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(4).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(5).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(6).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

Open in new window

cmedTechnical AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Try this code you are missing the setting of oWS as a worksheet and also cannot use Nothing but vbEmpty instead like below.

Sub test()
Dim BlankCell As Excel.Range
Set oWS = ActiveSheet

 '<---Find blank cells--->'
        Set BlankCell = oWS.Columns(1).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(2).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(3).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(4).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(5).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(6).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
End Sub

Open in new window


gowflow
0
gowflowCommented:
And here is an other way of doing it

Sub FindBlanks()
Dim BlankCell As Range
Dim WS As Worksheet

Set WS = ActiveSheet

For Each BlankCell In WS.UsedRange.SpecialCells(xlCellTypeBlanks)
    Debug.Print BlankCell.Address
Next BlankCell
End Sub

Open in new window


This will list all addresses of blank cells in the usedrange of the current worksheet.

gowflow
0
cmedTechnical AnalystAuthor Commented:
@gowflow

when i replace my code with yours, it still has the same error message.

Here is the entire original code
Option Explicit On
Option Strict On
Imports System.IO
Imports Microsoft.Office.Interop
Public Class Form1

    Private Const fileName = "C:\read file\EA report.xlsx"
    'Private Const txtfileName = "C:\read file\EA report.txt"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dtpTermStart.Format = DateTimePickerFormat.Custom
        dtpTermStart.CustomFormat = " "
        dtpTermEnd.Format = DateTimePickerFormat.Custom
        dtpTermEnd.CustomFormat = " "
        dtpFirstDayInReport.Format = DateTimePickerFormat.Custom
        dtpFirstDayInReport.CustomFormat = " "
        dtpLastDayInReport.Format = DateTimePickerFormat.Custom
        dtpLastDayInReport.CustomFormat = " "
    End Sub

    Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermStart.ValueChanged
        dtpTermStart.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker2_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermEnd.ValueChanged
        dtpTermEnd.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker3_ValueChanged(sender As Object, e As EventArgs) Handles dtpFirstDayInReport.ValueChanged
        dtpFirstDayInReport.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker4_ValueChanged(sender As Object, e As EventArgs) Handles dtpLastDayInReport.ValueChanged
        dtpLastDayInReport.CustomFormat = "MM/dd/yy"
    End Sub

    Private Function Error_check() As Boolean
        If dtpFirstDayInReport.Value.Date >= dtpLastDayInReport.Value.Date Then
            MessageBox.Show("Please make sure that the first day in report is before the last day in report!!",
                                "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2)
            Return False
        ElseIf dtpTermStart.Value.Date >= dtpTermEnd.Value.Date Then
            MessageBox.Show("Please make sure that the first day in report is before the last day in report!!",
                                "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2)
            Return False
        
        End If
        Return True
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim oApp As New Excel.Application
        Dim oWBk As Excel.Workbook = oApp.Workbooks.Open(fileName)
        Dim oWS As Excel.Worksheet = DirectCast(oWBk.Worksheets(1), Excel.Worksheet)
        Const xlvalues As Long = -4163
        Const xlWhole = 1

        Dim BlankCell As Excel.Range

        If Error_check() = False Then
            Exit Sub
        End If

        '<---Find blank cells--->'
        BlankCell = oWS.Columns(1).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(2).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(3).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(4).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(5).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(6).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

        oWS.Range(String.Format("B{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yy} {1}", dtpTermStart.Value.Date, cbTermType.SelectedItem.ToString(0))
        oWS.Range(String.Format("B{0}", BlankCell.Row)).Font.Bold = True
        oWS.Range(String.Format("C{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpTermStart.Value.Date)
        oWS.Range(String.Format("D{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpTermEnd.Value.Date)
        oWS.Range(String.Format("E{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpFirstDayInReport.Value.Date)
        oWS.Range(String.Format("E{0}", BlankCell.Row)).Font.Bold = True
        oWS.Range(String.Format("F{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpLastDayInReport.Value.Date)
        oWS.Range(String.Format("F{0}", BlankCell.Row)).Font.Bold = True

        oWS.Columns.AutoFit()

        Dim usedRows As Excel.Range = oWS.UsedRange.Rows
        For Each usedRows In oWS.UsedRange.Rows
            For Each cell As Excel.Range In usedRows.Cells
                RichTextBox1.Text &= cell.Value
            Next

            RichTextBox1.Text &= vbNewLine

        Next

        oWS.SaveAs(fileName)
        oWBk.Close()

    End Sub
End Class

Open in new window

0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

gowflowCommented:
Is this in a module or a in Class Module and I get plenty of errors. Can you post the workbook instead this would save both of us wasted time.

gowflow
0
cmedTechnical AnalystAuthor Commented:
@gowflow

I figured it out after doing some reading.  I just use a conversion type (CType) and it worked just fine.  
Dim BlankCell As Excel.Range
    

        ''<---Find blank cells--->'
        BlankCell = CType(oWS.Columns(1), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(2), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(3), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(4), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(5), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(6), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

Open in new window


Thanks for the help though.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
well don't see how but if you said it is solved then it is solved. The piece of code you posted shows nothing.
gowflow
0
cmedTechnical AnalystAuthor Commented:
instead of coding

 '<---Find blank cells--->'
       BlankCell = oWS.Columns(1).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

I coded

BlankCell = CType(oWS.Columns(1), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

and it works great.
0
Jacques Bourgeois (James Burger)PresidentCommented:
Better than CType, declare your variable as a Worksheet instead of an Object. CType implies a conversion and is slower.

Dim oWS As Excel.Worksheet.
0
cmedTechnical AnalystAuthor Commented:
@James Burger

I declared oWS as a worksheet in my original code.  Do you see anywhere else I could tightened this code?  Thanks in advance.  
Option Explicit On
Option Strict On
Imports System.IO
Imports Microsoft.Office.Interop
Public Class Form1

    Private Const fileName = "C:\read file\EA report.xlsx"
    'Private Const txtfileName = "C:\read file\EA report.txt"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dtpTermStart.Format = DateTimePickerFormat.Custom
        dtpTermStart.CustomFormat = " "
        dtpTermEnd.Format = DateTimePickerFormat.Custom
        dtpTermEnd.CustomFormat = " "
        dtpFirstDayInReport.Format = DateTimePickerFormat.Custom
        dtpFirstDayInReport.CustomFormat = " "
        dtpLastDayInReport.Format = DateTimePickerFormat.Custom
        dtpLastDayInReport.CustomFormat = " "

    End Sub

    Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermStart.ValueChanged
        dtpTermStart.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker2_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermEnd.ValueChanged
        dtpTermEnd.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker3_ValueChanged(sender As Object, e As EventArgs) Handles dtpFirstDayInReport.ValueChanged
        dtpFirstDayInReport.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker4_ValueChanged(sender As Object, e As EventArgs) Handles dtpLastDayInReport.ValueChanged
        dtpLastDayInReport.CustomFormat = "MM/dd/yy"
    End Sub

    Private Function Error_check() As Boolean
        If dtpFirstDayInReport.Value.Date >= dtpLastDayInReport.Value.Date Then
            MessageBox.Show("Please make sure that the first day in report is before the last day in report!!",
                                "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2)
            Return False
        ElseIf dtpTermStart.Value.Date >= dtpTermEnd.Value.Date Then
            MessageBox.Show("Please make sure that the first day in report is before the last day in report!!",
                                "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2)
            Return False
        End If
        Return True
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        If Error_check() = False Then
            Exit Sub
        End If

        Dim d As Date
        d = Date.FromOADate(CDbl(Date.Now.ToOADate()))
        Dim oApp As New Excel.Application
        Dim oWBk As Excel.Workbook = oApp.Workbooks.Open(fileName)
        Dim oWS As Excel.Worksheet = DirectCast(oWBk.Worksheets(1), Excel.Worksheet)
        Dim BlankCell As Excel.Range
        Const xlvalues As Long = -4163
        Const xlWhole = 1

        For i = 1 To oWS.UsedRange.Rows.Count
            If IsDate(oWS.Range(String.Format("E{0}", i)).Value) = True Then
                If d > CDate(oWS.Range(String.Format("F{0}", i)).Value) Then
                    CType(oWS.Rows(i), Excel.Range).Delete()
                End If
            End If
        Next

        ''<---Find blank cells--->'
        BlankCell = CType(oWS.Columns(1), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(2), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(3), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(4), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(5), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(6), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

        oWS.Range(String.Format("B{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yy} {1}", dtpTermStart.Value.Date, cbTermType.SelectedItem.ToString(0))
        oWS.Range(String.Format("B{0}", BlankCell.Row)).Font.Bold = True
        oWS.Range(String.Format("C{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpTermStart.Value.Date)
        oWS.Range(String.Format("D{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpTermEnd.Value.Date)
        oWS.Range(String.Format("E{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpFirstDayInReport.Value.Date)
        oWS.Range(String.Format("E{0}", BlankCell.Row)).Font.Bold = True
        oWS.Range(String.Format("F{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpLastDayInReport.Value.Date)
        oWS.Range(String.Format("F{0}", BlankCell.Row)).Font.Bold = True

        oWS.Columns.AutoFit()
        oWS.SaveAs(fileName)
        oWBk.Close()

        '<---VIEW EXCEL FILE--->
        Dim myConnection As System.Data.OleDb.OleDbConnection
        Dim dataSet As System.Data.DataSet
        Dim myCommand As System.Data.OleDb.OleDbDataAdapter
        Dim path As String = fileName

        myConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
        myCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", myConnection)
        dataSet = New System.Data.DataSet
        myCommand.Fill(dataSet)
        DataGridView1.DataSource = dataSet.Tables(0)
        myConnection.Close()
    End Sub
End Class

Open in new window

0
Jacques Bourgeois (James Burger)PresidentCommented:
Sorry for the incomplete answer, Windows Update crashed my computer while I was writing it. Here is a complete one.

Line 7:
     Does this line compile? Option Strict usually requires that you define the type of constants.
     Private Const fileName As String = "C:\read file\EA report.xlsx"

Line 58:
     You are converting ToOADate and then back FormOADate.
     It's like converting a String to an Integer and then back to a String in the same expression. Useless.
     You do not need to deal with OADate when you are working with Excel through a reference. It's done automatically.
     The following is sufficient:
     d = Date.Now

Line 61:
     No need for the DirectCast, oWBk.Worksheets(1) is already a Worksheet

Lines 75-80:
     Are you trying to find the first column that has a blank cell or any column that has one?
     The way you are doing it, if column 1 has a blank cell and column 3 has a blank cell, you will be left with column 3 as a result.

Lines 75-80:
     I am not sure of that one, but I do not think that looking for Nothing will work. It does not in VBA. Excel cells always have a default value. I would look for an empty string instead.

Line 99:
     You already have the constant, there is no need to assign it to a variable. Simply use the constant.

Lines 103-105
     You have only one table, but use a DataSet. This is a common mistake that is unfortunately repeated everywhere.
     You use a DataSet when you have more than one table, and they are linked together through relations.
     If there is no DataRelation object in the code, there is no need for a DataSet.
     You might want to read my article on the subject.
		Dim dataTable As System.Data.DataTable
		dataTable = New System.Data.DataTable
		myCommand.Fill(dataTable)
		DataGridView1.DataSource = dataTable

Open in new window

   
When you have 4 event methods that have the same code, simply on different controls, you can combine them into only one procedure:
	Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermStart.ValueChanged, dtpTermEnd.ValueChanged, dtpFirstDayInReport.ValueChanged, dtpLastDayInReport.ValueChanged
		DirectCast(sender, DateTimePicker).CustomFormat = "MM/dd/yy"
	End Sub

Open in new window

0
cmedTechnical AnalystAuthor Commented:
@James

LIne 7 - This is just the file that I want the dates from the datetime picker to add to the file
Line 58 - This was just me testing different dates by adding or subtracting from today's date.  The final code before production will be  d = Date.Now
Line 75-80- I am trying to find the first blank cell in each column and then add the dates to the first available blank cell.  The file has already been created and I am allowing the user to add new dates to the file.

Everything else I am going to make the necessary adjustments.  Thanks for your knowledge.
0
cmedTechnical AnalystAuthor Commented:
I figured it out after doing some reading.  I just use a conversion type (CType) and it worked just fine.  

Dim BlankCell As Excel.Range
   

        ''<---Find blank cells--->'
        BlankCell = CType(oWS.Columns(1), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(2), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(3), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(4), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(5), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(6), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.