Access XL automation: where in VBA to Dim wks as Worksheet

  • Thread starter EagleOne@microsoftdiscussiongroups
  • Start date
E

EagleOne@microsoftdiscussiongroups

2003

Got an error attempting Dim wks as Worksheet

Do I have to wait until the Excel Application is "Set?" then Dim

TIA

EagleOne
 
E

EagleOne

Arvin,

Late binding is a concept about which I have heard. That said, I am in the middle of it, I guess.

OK, is late binding a concept related to - at what point in the code where I should place a Dim
statement because of the respective "libraries" involved in time sequencing?

(I am not even sure of what I just wrote?)
 
D

Douglas J. Steele

Late Binding means you don't need to set a reference to any library, Early
Binding means you have to set a reference to a specific version of Excel (or
Word, or whatever application it is you're trying to automate)

Using Early Binding, you'd usually use code like:

Dim xlApp As Excel.Application

Set xlApp = New Excel.Application

Using Late Binding, you'd use code like:

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")


By not setting a reference to Excel, you gain a little freedom. If you chose
to set a reference to, say, Excel 2003 and you ship your application to
someone who has Excel 2002 or Excel 2007, you run the risk that the
reference you set will break, and nothing in your application will work
properly (not just the Excel-related code). However, you lose a few things.
You don't get Intellisense while you're coding, and you can no longer refer
to the intrinsic Excel constants (xlRight, xlBold, etc.). Some people claim
that the code runs slower, but I've never really noticed it. Yes, the
initial instantiation will be a bit slower (so you don't want to do that
inside a loop), but once it's instantiate, it seems about the same to me.

Tony Toews has an introduction to the topic (plus some good links) at
http://www.granite.ab.ca/access/latebinding.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)
 
E

EagleOne

Excellent response Doug.

Thank you for taking the time and sharing your knowledge! This forum has become noticeably less
arrogant for the newbie like me. In prior years, I that was not the case. MVP's like you make the
difference.
 
Top