Late binding and constants

[Cheer]

I get a run-time error '1004': "Unable to set the Calculation property
of the Application class" when I use the following routine. I did a
?xlCalculationManual in the immediate window, and the value I got back
was "-4135."

Sub AllOff()
Dim ObjXL As Object

Set ObjXL = CreateObject("Excel.Application")
ObjXL.ScreenUpdating = False
ObjXL.DisplayAlerts = False
ObjXL.Calculation = -4135

Set ObjXL = Nothing
End Sub

Any ideas?

 

[Rob Bovey]

I get a run-time error '1004': "Unable to set the Calculation property
of the Application class" when I use the following routine. I did a
?xlCalculationManual in the immediate window, and the value I got back
was "-4135."

Sub AllOff()
Dim ObjXL As Object

Set ObjXL = CreateObject("Excel.Application")
ObjXL.ScreenUpdating = False
ObjXL.DisplayAlerts = False
ObjXL.Calculation = -4135

Set ObjXL = Nothing
End Sub

Any ideas?

You need to have an active workbook before you can set the Calculation
property. Try it like this:

Sub AllOff()
Dim ObjXL As Object
Dim objBook As Object

Set ObjXL = CreateObject("Excel.Application")
Set objBook = ObjXL.Workbooks.Add
ObjXL.ScreenUpdating = False
ObjXL.DisplayAlerts = False
ObjXL.Calculation = -4135

objBook.Close False
ObjXL.Quit
Set ObjXL = Nothing
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Cheer]

You need to have an active workbook before you can set the Calculation
property. Try it like this:

Sub AllOff()
Dim ObjXL As Object
Dim objBook As Object

Set ObjXL = CreateObject("Excel.Application")
Set objBook = ObjXL.Workbooks.Add
ObjXL.ScreenUpdating = False
ObjXL.DisplayAlerts = False
ObjXL.Calculation = -4135

objBook.Close False
ObjXL.Quit
Set ObjXL = Nothing
End Sub

Thanks for your help, Rob. The original purpose of this little
routine, though, was to turn things "off" so that various other
operations would go more quickly. If I already *have* an active
workbook,

a) Why am I creating another instance of Excel and taking the time to
create a workbook in it if my goal is to speed things up? Can I do
something like ActiveWorkbook.Activate instead? And,

b) I'm a little fuzzy on when to use GetObject("Excel.Application")
vs. CreateObject("Excel.Application"). If I already have Excel
running, should I be using "Get" rather than "Create"?

 

[Tom Ogilvy]

When you use CreateObject, people assume you know what you are doing and
that you want to automate excel. You almost never need to do that if you
are programming from within Excel. Excel supports multiple documents, so
you should just open workbooks in the existing instance - same one you are
running in. (so rule of thumb, don't use CreateObject or GetObject unless
you know you need to use them - so you say, "well I don't know" - so don't
use them.).

Application.Calculation = xlManual

Application.Calculation = xlAutomatic

should suffice in your existing code. If you did create a new instance,
that change wouldn't affect the existing instance, so it wouldn't do you any
good anyway.

 

[Cheer]

When you use CreateObject, people assume you know what you are doing and
that you want to automate excel. You almost never need to do that if you
are programming from within Excel. Excel supports multiple documents, so
you should just open workbooks in the existing instance - same one you are
running in. (so rule of thumb, don't use CreateObject or GetObject unless
you know you need to use them - so you say, "well I don't know" - so don't
use them.).

Application.Calculation = xlManual

Application.Calculation = xlAutomatic

should suffice in your existing code. If you did create a new instance,
that change wouldn't affect the existing instance, so it wouldn't do you any
good anyway.

This answer is not up to your usual standard. I asked a legitimate
question about the difference between GetObject and CreateObject. For
those following this thread hoping to learn something, here's the
answer you should have given:

You can test to see if an instance of Excel is already running, and if
so, use GetObject to refer to that instance. If Excel is not running,
then you can start it using CreateObject. In either case, as Rob Bovey
correctly points out, to set the Calculation property, you must have
an active workbook. In the below example, if GetObject was successful,
then you have an active workbook. If it was not successful, you create
a new instance with CreateObject, then create a new workbook (because
CreateObject creates a new instance of Excel, but doesn't add a
workbook to it).

Dim IStartedXL as Boolean

Public Sub DefineExcel()
'
' If running, use it. If not, create it.
'
On Error Resume Next
IStartedXL = False
Set objExcel = GetObject(, "Excel.Application")
On Error GoTo 0
If objExcel Is Nothing Then
Set objExcel = CreateObject("Excel.Application")
IStartedXL = True
Set objWB = objExcel.Workbooks.Add
Else
Set objWB = objExcel.Workbooks.Item(1)
End If
End Sub

Public Sub UndefineExcel()
If IStartedXL Then
objWB.Close False
objExcel.Quit
End If

Set objWB = Nothing
Set objExcel = Nothing
End Sub


I am indeed automating Excel; that's why this thread has the subject
line "Late binding..." Your answer was condescending, probably because
you didn't take the time to fully understand the question. Do yourself
a favor and don't do that.

 

[Peter T]

In the below example, if GetObject was successful,

then you have an active workbook.

Probably but not necessarily. Consider the situation that user has closed
all Workbooks except perhaps hidden Personal. Would still need to check for
a visible active workbook.

Also, do you understand Tom's comments:

As for your other remarks - I can see where Tom was coming from, partly I
suspect on the basis that Rob Bovey had already effectively answered your
question.

It often occurs that questions are not answered to the full understanding of
the OP. If so it's normal to request further clarification, perhaps several
times. "I still don't follow . . . I think you missed my point, . . . etc.
However, I think you missed both Rob and Tom's points!

Regards,
Peter T

 

[Tom Ogilvy]

If you read it as condescending, it wasn't meant to be. We can only go on
what you tell us. You said I already have an activeworkbook - yet you don't
in your initial code that you show - that indicated to me that your program
is operating from excel. It is not unusual for people to attempt to use
createobject from inside Excel and it is usually because they don't
understand that they don't need to. Anyway, If that is not the case, then
tell us from what application you are automating Excel and what you are
trying to accomplish other than turning calculation off in the new instance
of excel.

It was from the context of not needing it inside excel that I said a rule of
thumb is don't use it if you don't know that you need it. Your first
question was not about when to use Get vice create. From your first
question it appeared you didn't need to use either. That was the context of
my answer.
The first sentence was to explain why someone would answer you in the
context of needing to use createobject when in fact the real answer might be
that it doesn't need to be used at all and the answer is as simple as what I
gave. It was not meant to mean that you, Cheers, don't know what you are
doing.

Sorry, but I don't have time to war game every response on how it might be
interpreted. If you want turnkey explanations, perhaps use the help file
more. That is where I learned how to use GetObject and CreateObject and I
believe it does an excellent job. What it may not do as well is say when
you might or might not need either.

so again, what application are you trying to automate Excel from and what
are you trying to do.

I am indeed automating Excel; that's why this thread has the subject
line "Late binding..." Your answer was condescending, probably because
you didn't take the time to fully understand the question.

so what is the key bit of information you provided:

the subject which didn't seem to have anything to do with your post since
you were not using the constant but providing the value of the constant amd
experiencing an error. The fact that you said you already have an
activeworkbook but no evidence of such in your code. the fact that you said
your purpose was to speed up an operation, but there were no operations
going on in your code. The fact that your first question was not about the
choice between GetObject or CreateObject. So when you tell me I didn't take
the time to understand the question, I took a lot of time to try to
understand the question - perhaps I read too much into the diverse clues you
dispersed.

And then again you may say my answer is condescending because you didn't
take the time to try to understand it.

--
Regards,
Tom Ogilvy

 

[Cheer]

Comments inline.

Probably but not necessarily. Consider the situation that user has closed
all Workbooks except perhaps hidden Personal. Would still need to check for
a visible active workbook.

Granted. The requirement that an active workbook exist was the point
Rob Bovey made in his post. In my code I need to make sure objWB
exists before using it. I will add that step.

Also, do you understand Tom's comments:


As for your other remarks - I can see where Tom was coming from, partly I
suspect on the basis that Rob Bovey had already effectively answered your
question.

I got Rob's point. I repeated it in my comments before my sample code
in response to Tom's post. The issue Tom somewhat indelicately took up
was the difference between GetObject and CreateObject. Rob could not
have already "effectively answered" that question because I had not
yet posed it when Rob posted.

It often occurs that questions are not answered to the full understanding of
the OP. If so it's normal to request further clarification, perhaps several
times. "I still don't follow . . . I think you missed my point, . . . etc.
However, I think you missed both Rob and Tom's points!

Look, I'm happy to request further clarification many times when my
point is missed either because I stated it poorly or because another
poster misunderstood. But I fail to understand how anyone learns from
a comment like, "When you use CreateObject, people assume you know
what you are doing..." I asked someone to tell me the difference
between "GetObject" and "CreateObject." What I got back, rudely, was a
dictate about whether I really should be using either of them.

So, I'll say to you: I think you missed my point.

 

[Cheer]

If you read it as condescending, it wasn't meant to be. We can only go on
what you tell us. You said I already have an activeworkbook - yet you don't
in your initial code that you show - that indicated to me that your program
is operating from excel. It is not unusual for people to attempt to use
createobject from inside Excel and it is usually because they don't
understand that they don't need to. Anyway, If that is not the case, then
tell us from what application you are automating Excel and what you are
trying to accomplish other than turning calculation off in the new instance
of excel.

It was from the context of not needing it inside excel that I said a rule of
thumb is don't use it if you don't know that you need it. Your first
question was not about when to use Get vice create. From your first
question it appeared you didn't need to use either. That was the context of
my answer.
The first sentence was to explain why someone would answer you in the
context of needing to use createobject when in fact the real answer might be
that it doesn't need to be used at all and the answer is as simple as what I
gave. It was not meant to mean that you, Cheers, don't know what you are
doing.

Sorry, but I don't have time to war game every response on how it might be
interpreted. If you want turnkey explanations, perhaps use the help file
more. That is where I learned how to use GetObject and CreateObject and I
believe it does an excellent job. What it may not do as well is say when
you might or might not need either.

so again, what application are you trying to automate Excel from and what
are you trying to do.


so what is the key bit of information you provided:

the subject which didn't seem to have anything to do with your post since
you were not using the constant but providing the value of the constant amd
experiencing an error. The fact that you said you already have an
activeworkbook but no evidence of such in your code. the fact that you said
your purpose was to speed up an operation, but there were no operations
going on in your code. The fact that your first question was not about the
choice between GetObject or CreateObject. So when you tell me I didn't take
the time to understand the question, I took a lot of time to try to
understand the question - perhaps I read too much into the diverse clues you
dispersed.

And then again you may say my answer is condescending because you didn't
take the time to try to understand it.

If you really feel that being scolded for an opening statement like,
"When you use CreateObject, people assume you know what you are
doing..." requires you to "wargame" your responses, well so be it. If
you say it wasn't meant condescendingly, I'll take your word for it.

Over the past two days, I've been asking about late binding in the
context of avoiding having to load references programmatically when I
port my add-in to different machines. It is my understanding that if
you use late binding, for whatever reason, you must use the value of
the constant not the constant itself. At the time of my post, I
believed I was making some error in the value of the particular
constant I was using, so my subject actually quite accurately
represented my post.

The routine "AllOff" was designed to turn alerts off and
screenupdating off; very common, as you know, when you're trying to
speed things up. The fact that I didn't articulate which operations I
wanted to speed up is irrelevant.

What I asked, after Rob pointed out that I needed an active workbook,
was the difference between GetObject and CreateObject. What I got
instead was a discussion of how I probably didn't need to use either.
So, OK, perhaps you read my question correctly, but decided not to
answer that question, but rather another one that I didn't ask.

One reason for using late binding and CreateObject is to solve
Automation problems - running instances of Excel from some other
application. Strictly speaking, I'm not doing that; I'm trying to
avoid having to programmatically load libraries like "MSXML v3.0."
when I distribute my add-in. But, the effect is the same: I need an
object representing an (existing, in this case) instance of Excel that
I can then use to refer to child objects, properties, and methods. So,
yes, I get it that under normal circumstances from within Excel I
wouldn't need either Get or Create. But I'm not just going to be using
this on my machine, a point I've been making since bringing up late
binding issues in my first post.

 

[Stephen Bullen]

Hi Cheer,

I'm a little fuzzy on when to use GetObject("Excel.Application")
vs. CreateObject("Excel.Application"). If I already have Excel
running, should I be using "Get" rather than "Create"?

It all depends on what it is you're trying to achieve. If this is a VB
project that needs to automate Excel to do something, it's best to
always use CreateObject, to create your own instance of Excel, which
you can close down when you're finished with it. The main reason for
this is that we never know what state the already-running Excel
application is in. If the user is editing a cell, most calls will fail.
Alternatively, they may be using Excel for a 'Dictator' application,
which will typically remove all Excel's menus and have lots of event
handling to detect (and prevent) unexpected things happening.

On the other hand, if your application is specifically designed to add
some functionality to Excel, it would make sense to want to add it to
the running instance.

It used to be the case that people would recommend using GetObject to
avoid having multiple instances of Excel running on the same machine -
due to memory and processor limitations. In modern PCs, that's no
longer an issue, so I recommend using CreateObject unless you have a
very good reason not to. There's more information about this in our new
book, Professional Excel Development (details available from my web
site).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk