Go Premium for a chance to win a PS4. Enter to Win


Starting Excel via CreateObject

Posted on 2001-06-21
Medium Priority
Last Modified: 2012-06-22
I want to start up and display an instance of Excel 97 from my VB 5.0 project. Should be easy, right? I've been trying to use the same Automation concept with Excel that has always worked OK with Word 97, like this:

   (Reference to MS Excel 8.0 Library established first)
   Dim xl As Excel.Application
   Set xl = CreateObject("Excel.Application")
   xl.Visible = True
   'More code to manipulate xl . . .
   Set xl = Nothing
PROBLEM: The code stops on the "CreateObject" line with, "Run-Time Error 429: ActiveX component can't create object."

BUT, if I change the Set Statement to use the New keyword instead of CreateObject:

     Set xl = New Excel.Application

then Excel starts, but minimized (I have to use xl.WindowState=xlNormal to get it displayed. Seems strange, but I could live with it).

Anybody got any ideas why the CreateObject approach is not working with Excel? Microsoft dox say it outta work.
Question by:JimKirby
LVL 16

Expert Comment

ID: 6215574
I did paste your code directly on a form and works without any problem.
Do you have Excel installed on machine that has this behaviour?
LVL 18

Accepted Solution

bobbit31 earned 800 total points
ID: 6215603
it works for me...

search your registry for "Excel.Application"

you might also want to try:


Dim xl as new excel.application is early binding


dim xl as object

set xl = CreateObject(...) is late binding

the difference is:

Late Binding
When you declare a variable As Object or As Variant, Visual Basic cannot determine at compile time what sort of object reference the variable will contain. Therefore, Visual Basic must use late binding to determine at run time whether the actual object has the properties and methods you call using the variable.

Note   Late binding is also used for variables declared As Form or As Control.

Each time you invoke a property or method with late binding, Visual Basic passes the member name to the GetIDsOfNames method of the object?s IDispatch interface. GetIDsOfNames returns the dispatch ID, or DispID, of the member. Visual Basic invokes the member by passing the DispID to the Invoke method of the IDispatch interface.

For an out-of-process component, this means an extra cross-process method call, essentially doubling the call overhead.

Note   You cannot call the methods of the IDispatch interface yourself, because this interface is marked hidden and restricted in the Visual Basic type library.

Early Binding
If Visual Basic can tell at compile time what object a property or method belongs to, it can look up the DispID or vtable address of the member in the type library. There?s no need to call GetIDsOfNames.

When you declare a variable of a specific class ? for example, As Widget ? the variable can only contain a reference to an object of that class. Visual Basic can use early binding for any property or method calls made using that variable.

---> This is the recommended way to declare object variables in Visual Basic components and applications.

Important   Whether early or late binding is used depends entirely on the way variables are declared. It has nothing to do with the way objects are created.

Tip   Early binding dramatically reduces the time required to set or retrieve a property value, because call overhead is a significant fraction of the total call time.

so basically, it's recommended that you use:

dim xl as new excel.application

good luck


Expert Comment

ID: 6215849
so basically, it's recommended that you use:

dim xl as new excel.application

its better to use

dim xl as excel.application
set xl = new excell.application

if you just use the as new excell.application the app has to check each time any funciton or property is used for xl.

it is equivalient to writing

if xl = nothing then
   make new xl
   run property
   run property
this happens each time a property or function is used of the xl object.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 6215858
from msdn
Dim tdfOrders As New TableDef
tdfOrders.Name = "Orders"

Note   Using variables declared using the New keyword can slow your application. Every time Visual Basic encounters a variable declared using New, it must test whether or not an object reference has already been assigned to the variable.

For example, you could assign a reference to an Excel object to either of the following variables:

Dim xlApp1 As Excel.Application
Set xlApp1 = New Excel.Application

Dim xlApp2 As Object
Set xlApp2 = CreateObject("Excel.Application")

Code that uses variable xlApp1 is early-bound and will execute faster than code that uses variable xlApp2, which is late-bound.

Expert Comment

ID: 6215865
Did u referenced the Excel Object Library, that should do it.

LVL 16

Expert Comment

ID: 6216277
i am with jrspano:

its better to use

dim xl as excel.application
set xl = new excel.application


Author Comment

ID: 6216523
Thanks, Bobbit31! Excel is in my Registry as "Excel.Application.8".  Everything runs OK with the ".8" added.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

916 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