Convert code to Late Binding

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
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.

VBA Code:
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,012
Office Version
  1. 365
Platform
  1. Windows
I can see nothing in your code that requires early or late binding.
 

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,012
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 21, 2011
Messages
394

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,769
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
As far as I can see, in your code above all you need to change is this:

VBA Code:
Dim Wordapp As Word.Application

to this:

VBA Code:
Dim Wordapp As Object
 

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,769
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,403
Messages
5,641,936
Members
417,247
Latest member
Chitaah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top