-----Original Message-----
Technically, this is not true. Excel isn't started when the Dim
statement is processed. The Excel application isn't actually
started until the first reference in code (not a Dim statement)
to the object is encountered. Using the New keyword creates what
is called an "auto-instancing" variable. When the VBA code is
compiled, VBA actually creates code that, if it were written in
VBA, would look like
If XLApp Is Nothing Then
Set XLApp = New Excel.Application
End If
It creates this code just before *every* reference to XLApp. The
generally prefered method is to avoid auto-instancing variables
and use Set/New to create the instance of the object a the
appropriate point in your code.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Rather than give you a straightforward answer ("MY"
answer), I will try to help you decide how to handle it
yourself (but will also give my recommendations).
Basically, here's what happens:
At the point you create an Excel.Application object, it is
the same thing as starting up a new Excel session, i.e. it
is as if you had gone to your Start menu and chosen to
start Excel. The only difference is it happens behind the
scenes and, also, is invisible for now. You would see an
EXCEL process in the Windows Task Manager but will not see
anything on screen until you set the Visible property to
True.
You can start Excel when you first start running your
module by using the line:
Dim XLApp as New Excel.Application
This not only defines XLApp as an Excel.Application object
but creates a new instance of it; i.e. starts it.
Alternatively, you can hold off on starting Excel until
you actually need to use it by defining the object
variable and then, later in code, actually activating it:
Dim XLApp as Excel.Application
....
....
Set XLApp = New Excel.Application
Why would it make a difference? Well, first, at the point
where you create you actually call on Excel to start it
will take some time: it has to read your hard drive and
load and initialize Excel. Also, if you do this inside a
loop, with all that Excel starting up and shutting down,
you can end up slowing down your computer - and if you
happen to forget to close the session you run the risk of
having multiple invisible Excel sessions running in the
background!!! But then, Excel will use some overhead -
memory and processor cycles - so you may want to hold off
on opening it until you really have to.
In short, it is up to you where to actually put the
New.Excel.Application declaration that will start Excel -
just activate it sometime before you need to use it and
make sure you are not inadvertently doing it inside a loop!
Once you have that Excel application you can either create
a new workbook (by the Set XLBook = XLApp.Workbooks.Add
statement) or you can open an existing file (with Set
XLBook = XLApp.Workbooks.Open "C:\Book1.xls". This is no
different than what you would do if you were manually
controlling Excel, you are just doing it through code.
Since it sounds like you will be using the same workbook
structure over and over, the Open option seems best to me
(otherwise you need to create your workbook with all its
cell values and formats and formulas through code every
time you run your module). Also, rather than repeatedly
opening and closing Excel for every new 'dummy' value, it
probably makes more sense to create your Excel session att
he beginning of the module, then just set it visible or
invisible as needed while the rest of your code runs.
Once you have your Excel session and workbook open you can
do pretty much anything you can do with Excel in VBA, with
all the same Objects and Methods, by referring to XLApp or
XLBook. So this is where you can set the value of the
cell in the workbook to be equal to your 'dummy' and do
any further processing you want to do. At this stage you
probably also want to make the session visible to the
user, so set XLApp.Visible to True and there will be the
Excel session you created.
Finally, when everything is done you need to clean things
up. This means:
1) Close your workbook and save it if necessary
2) Exit the Excel session and clear Excel out of your
computer's memory
3) Any object variables you declared (e.g. XLApp, XLBook)
have memory and resources assigned to them; clean this up
by setting them equal to "Nothing."
If you don't do this housekeeping it may not be
noticeable, but it is at the least poor practice and
wasteful of computer resources - at worst you can crash
your system.
So, finally, my suggestions:
- use the Dim XLApp as New Excel.Application at the
beginning of your code
- also at the beginning of your code, after all variables
are defined, open the PRE-BUILT workbook "C:\Book1.xls"
(you will need to set this up and have it saved before
running your code). This would be the line Set XLBook =
XLApp.WOrkbooks.Open "C:\Book1.xls"
- Set XLApp visible at the point you want the user to
become aware of it
- Now all you need to do is to grab your 'dummy' variable
at the appropriate point (as you suggest, just before the
Select Case might be good) and put it in the desired cell.
- As soon as possible, i.e. as soon as your need for Excel
is gone, close everything and set the variables to Nothing
as described above.
I hope that does it...
K Dales
.