Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Late Binding & Formatting Excel from VBA

Posted on 2007-07-29
Medium Priority
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

ID: 19588907
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

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

Accepted Solution

harfang earned 336 total points
ID: 19589144
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!
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 332 total points
ID: 19589853
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 ]
[ fanpages ] earned 332 total points
ID: 19591174

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"
[ http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21565906.html ]

"I need a way to get the name of a workbook without the file extension and it must be very robust"
[ http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21760433.html ]

And a more detailed overview, here:

"Early vs. Late Binding"
[ http://peltiertech.com/Excel/EarlyLateBinding.html ]



Expert Comment

ID: 20072419
Forced accept.

EE Admin

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

580 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