Late Binding & Formatting Excel from VBA

Posted on 2007-07-29
Last Modified: 2013-11-27
Using Access 2003.

I've pulled some code off of this site and also gotten some specific advice on doing things like underlining cells, making the contents bold and so forth. (I think the last guy gave up on me.)

I tried using code pulled from Excel macros, but got errors which I was told came from the fact that I was using "Late Binding" and that - as a result - the Excel reference library was not visible to my Access application. That same person suggested a set of numbers to use instead of things like Weight(xlThin) but the numbers didn't work either.

It seems to me that if I can figure out to do "Early Binding" - or whatever it really is - I can manipulate the Excel sheets I am populating using code cut out of Excel Macros and save myself alot of headaches. Can anyone tell me how to do that - or, alternatively, what the proper syntax for handling basic excel formatting commands like underline, bold, column width and so forth?

Question by:Buck_Beasom
    LVL 45

    Expert Comment

    Here's a macro that includes those formatting commands:

    Sub Macro1()
        Selection.Font.Underline = xlUnderlineStyleSingle
        Selection.Font.Bold = True
        Columns("A:A").ColumnWidth = 13.88
        Rows("1:1").RowHeight = 22.5
    End Sub

    When you want the VBA for other simple commands, just switch on the macro recorder, make the formatting changes you want, switch off the macro recorder and then look at the VBA.
    LVL 77

    Expert Comment

    macro recorder?
    This ain't XL/Word etc(:-)
    LVL 58

    Accepted Solution

    Early binding means that the compiler is informed on which objects you are manipulating in your code, normally through the use of an "object reference" library.

    Late binding means that your code only knows it manipulates "objects" and will have to figure out at runtime if the object really supports the methods and properties you are using.

    For example:

    Sub LateBound()

        Dim objXL As Object
        Dim objRange As Object

        Set objXL = GetObject(, "Excel.Application")
        Set objRange = objXL.Selection
        objRange.Font.Bold = True

    End Sub

    In the above, VB has no way of knowing in advance that objXL has a property called "Selection" or that objRange has a property called "Font".

    Sub BoundEarly()

        ' Open (Tools | References), find and check "Microsoft Excel ?.?? Object Library"
        ' Then you can do this:

        Dim appXL As Excel.Application
        Dim rngCurrent As Excel.Range

        Set appXL = GetObject(, "Excel.Application")
        Set rngCurrent = appXL.Selection
        rngCurrent.Font.Underlined = True

    End Sub

    Same code, really, but VB will be able to check the use of objects, supply suggestions while you type, and understand the constants that some of these properties use (the xl???? constants).

    When you paste code recorded from Excel, make sure to prefix every line with appXL (if that is your Excel.Application object), or another declared and set xl object (worksheet, range, etc.)

        Selection --> appXL.Selection
        Range("B2") -- appXL.ActiveSheet.Range("B2")

    Good luck!
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    Hello Buck_Beasom,

    Sorry, I haven't given up on you :)

    Markus (aka harfang) gave you a good start on the difference between early and late
    binding.  Early binding's advantages:

    1) It is faster than late binding
    2) It is easier to develop with early binding

    Late binding's advantages:

    1) If you are giving coding advice on Experts Exchange, you do not have to worry about the Asker
    being able to set a reference :)
    2) If the developer and the user may be on different application versions, late binding can be more
    forgiving and accommodating

    A habit I am trying to acquire is to develop using early binding i the development stage, but then
    converting to late binding before I release the app.


    LVL 35

    Assisted Solution

    by:[ fanpages ]

    As you have established, when creating an object you can do this by using "Early Binding" or "Late Binding".

    Adopting a "Late Binding" approach ensures that any differences between the library references for the external object you are attempting to create on your development machine & any of your run-time user machines does not cause any issues, because the validation of the object reference will occur at run-time, rather than with "Early Binding" where the validation occurs within the development environment (when the code is interpreted/compiled).

    For example, if creating an MS-Outlook application object, the version of Outlook on your development machine may be 2003, but a user may only have version 2000.  If you use "Early Binding" you are restricting to the library reference for Outlook 2003 (on your development machine), as the validation of the libraries is undertaken in your development environment.  When a user with Outlook 2000 tries to run the Excel application they will receive a run-time error as the libraries for Outlook 2003 are not present pn their machine (as MS-Outlook 2003 has not been installed previously) & the application will not be able to be run.  Using "Late Binding" will allow the Outlook 2000 user to proceed as the validation of the libraries will occur as they run the application instead.

    FYI: Previous questions I have contributed to on this subject:

    "Redistributing VB6 Excel based application"
    [ ]

    "I need a way to get the name of a workbook without the file extension and it must be very robust"
    [ ]

    And a more detailed overview, here:

    "Early vs. Late Binding"
    [ ]


    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now