Convert code to Late Binding

[zoog25]

Hello All,

I'm still new to coding in the sense that i never really understood the concept of Early Binding vs Late Binding. Anyways while researching this process, it appears that my current project would need be done in Late Binding language because 1) Several people will be using the coding, 2) I don't have access to their computers to set their reference library, 3) There is probably several different versions of excel running. With that last one, I believe I'm using the earliest version that this place has so i'm assuming that others will have new versions, especially since the company is switching out computers for newer ones. Anyways I'm having issues with trying to figure out how to use late binding so i have a sub that i'm using to start the process of late binding. Can someone please help me figure out how to convert it to late bind. That way i can see what i need to do and then use it to convert the rest of the code i have. Thank you for any help.

Sub LSscript(rw As Long)
'Disable other sheet Events
Application.EnableEvents = False

Dim TPws As Worksheet, FMws As Worksheet
Dim lrow As Long, AGRexist As Long, C As Long
Set TPws = ThisWorkbook.Worksheets("Tract Parcels")
Set FMws = ThisWorkbook.Worksheets("Final Map")

'Find last row in NOC Log
lrow = TPws.Cells(Rows.Count, 4).End(xlUp).Row

AGRexist = FDLS(rw, lrow)

Select Case AGRexist
    Case Empty, ""
        lrow = lrow + 1
        TPws.Cells(lrow, "B").Value = FMws.Cells(rw, "B").Value
        TPws.Cells(lrow, "G").Value = FMws.Cells(rw, "C").Value
        TPws.Cells(lrow, "H").Value = FMws.Cells(rw, "D").Value
        TPws.Cells(lrow, "I").Value = FMws.Cells(rw, "E").Value
        TPws.Cells(lrow, "D").Value = "LS AGR"  
        TPws.Cells(lrow, "J").Value = FMws.Cells(rw, "H").Value    
        If FMws.Cells(rw, "I").Value = "N/A" Then   
            TPws.Cells(lrow, "K").Value = "N/A"
        Else
            TPws.Cells(lrow, "K").Value = FMws.Cells(rw, "I").Value
        End If
        
        C = 20
        Do While C < 35
            TPws.Cells(lrow, C).Value = "N/A"
            C = C + 1
        Loop
    Case Else
        MsgBox ("Note an entry already exists so no new entry will be made.")
End Select

Done:

Application.EnableEvents = True
End Sub

 

[Fluff]

I can see nothing in your code that requires early or late binding.

 

[zoog25]

Thank you Fluff so if the coding remains in excel then you don't need to worry about early / late binding issues. So it's just when you are dealing with communication between programs. If so then the following code would need late bind.

Sub NOCSTD(rw As Long)

'Disable other sheet Events
Application.EnableEvents = False

Dim Wordapp As Word.Application
    Dim Location As Variant
    Dim lrow As Long
    Dim NOCdate As Long, Deve As String, TPr As Long, Contact As Long
    Dim TPws As Worksheet, NOCws As Worksheet, CONws As Worksheet
    Set TPws = ThisWorkbook.Worksheets("Tract Parcels")
    Set NOCws = ThisWorkbook.Worksheets("NOC")
    Set CONws = ThisWorkbook.Worksheets("Contact")
    Set Wordapp = CreateObject("Word.Application")


    Dim wFile As String
    wFile = ActiveWorkbook.Path & "\NOC Templates\NOC Template.docx"
    If Dir(wFile) <> "" Then
        Wordapp.Documents.Open (wFile)
        Wordapp.Visible = True
    Else
        MsgBox "File does not exists"
        GoTo Finaled
    End If

'NOC Completion Date (Input Fill date of NOC Items Completed Date with current of processing the NOC)
TPr = TPFD(rw) 'Function to find Tract entry in Sheet 6 (Tract Parcels)
If TPr = Empty Then
    MsgBox ("Tract/Parcel Map # is not found under Tract Parcels Log. Please verify that map information is correct.")
    GoTo Finaled
Else
    TPws.Cells(TPr, "Z").Value = Format(Now(), "mmmm dd, yyyy")
End If

'Project Entry
Select Case NOCws.Cells(rw, "F")
    Case Is < 10000
        Select Case NOCws.Cells(rw, "H")
            Case Empty, "", "N/A"
                Wordapp.ActiveDocument.FormFields("TXProject").Result = "Tract " & NOCws.Cells(rw, "F").Value
            Case Else
                Wordapp.ActiveDocument.FormFields("TXProject").Result = "Tract " & NOCws.Cells(rw, "F").Value & " " & NOCws.Cells(rw, "G").Value & " " & NOCws.Cells(rw, "H").Value
        End Select
    Case Else
        Select Case NOCws.Cells(rw, "H")
            Case Empty, "", "N/A"
                Wordapp.ActiveDocument.FormFields("TXProject").Result = "Parcel Map " & NOCws.Cells(rw, "F").Value
            Case Else
                Wordapp.ActiveDocument.FormFields("TXProject").Result = "Parcel Map " & NOCws.Cells(rw, "F").Value & " " & NOCws.Cells(rw, "G").Value & " " & NOCws.Cells(rw, "H").Value
        End Select
End Select

Location = InputBox("Provide the site location of this project. See your NOVUS Description.", "Tract / Parcel Location")

Wordapp.ActiveDocument.FormFields("TXLocation").Result = Location

Deve = NOCws.Cells(rw, "J").Value
Wordapp.ActiveDocument.FormFields("TXDeveloper").Result = Deve

Contact = NOCcontFD(Deve)

If Not Contact = Empty Then
    Wordapp.ActiveDocument.FormFields("TXAddress").Result = CONws.Cells(Contact, "G").Value
    Wordapp.ActiveDocument.FormFields("TXCSZ").Result = CONws.Cells(Contact, "H").Value & ", " & CONws.Cells(Contact, "I").Value & " " & CONws.Cells(Contact, "J").Value
End If

Wordapp.ActiveDocument.FormFields("TXAgrSt").Result = "Improvement Agreement #" & NOCws.Cells(rw, "D").Value

Wordapp.ActiveDocument.FormFields("TXCompletionDate").Result = Format(NOCws.Cells(rw, "L").Value, "mmmm dd, yyyy")

MsgBox ("Please review and save NOC. When ready to proceed, press the 'OK'")

Finaled:

Application.EnableEvents = True

End Sub

 

[Fluff]

if the coding remains in excel then you don't need to worry about early / late binding issues

No, if you are referring to anything that is not part of the default object library, then you need to consider whether to use early/late binding.

 

[zoog25]

Thank you for helping me Fluff. So lets say that by default or something. None of the object libraries are selected. So I'm working with three different programs, excel, word, and outlook. How do i say generate the required late binding for those. In the case i sent above, the two that i'm working with is excel and word.

 

[RoryA]

As far as I can see, in your code above all you need to change is this:

Dim Wordapp As Word.Application

to this:

Dim Wordapp As Object

 

[zoog25]

Thank you RoryA. I'll make the change.

So if i get this straight, it's only in this case the word.application that is need. None of the other dims will be an issue.

 

[RoryA]

The rest are all data types, which can’t be late bound, or Excel objects which don’t need late binding for code in Excel.

 

[zoog25]

Thank you RoryA.