|
PureBytes Links
Trading Reference Links
|
<x-html><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 5.50.3825.1300" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT color=#0000ff>
<DIV><FONT color=#000000><FONT size=2><STRONG>OLE between MSK+Excel
(+v.v.) - FAQ's </STRONG>{optimizing OLE links
+ linked Cells + Excel + VB programs'
performances}</FONT></FONT></DIV>
<DIV><FONT color=#000000
size=2>-----------------------------------------------------------------</FONT></FONT><FONT
color=#0000ff><STRONG></DIV></DIV></STRONG></FONT>
<DIV><FONT color=#0000ff size=2><STRONG></STRONG></FONT> </DIV>
<DIV><FONT size=2><FONT color=#0000ff><STRONG>Q12. </STRONG></FONT><FONT
color=#000000>H</FONT>ow to set up Excel and Visual Basic for
Applications so that it will speed up my spreadsheet's internaly
linked Cells</FONT></DIV>
<DIV><FONT size=2> and
my OLE Linked Cell's calculations, and so that the Excel program
will perform optimally?</FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT size=2><STRONG>A</STRONG>. Optimizing
<STRONG>Excel</STRONG></FONT></DIV>
<DIV><FONT size=2><STRONG>B</STRONG>. Optimizing <STRONG>Visual
Basic</STRONG></FONT><FONT size=2></DIV></BLOCKQUOTE></FONT>
<DIV><FONT
size=2>-----------------------------------------------------------------</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2><FONT size=4><A name=CH007H207><STRONG><FONT size=2><FONT
size=4>12A</FONT>. </FONT><U>Optimizing Microsoft
Excel</U></STRONG></A><U><STRONG> Performance</STRONG> </U></FONT><FONT
face=Verdana><FONT face=Arial><U> </U></FONT></DIV></FONT>
<P dir=ltr>There are several techniques you can use to optimize Excel for size
and speed.</P>
<UL dir=ltr>
<LI>
<DIV>In general, size optimizations decrease both the amount of memory
required and the amount of disk space required.</DIV>
<LI>
<DIV>Speed optimizations usually occur when you modify worksheets so that the
recalculation engine in Excel works more efficiently. </DIV></LI></UL>
<P><FONT size=3><B><A name=_Toc364227868>Optimizing for Size</A></B></FONT>
<P><A name=dex56></A>
<P>If a worksheet contains links to large ranges on external documents, it may
require a large amount of disk space and take a long time to open. To prevent
this, clear the <B>Save external link values</B> check box on the
<B>Calculation</B> tab in the <B>Options</B> (Windows) dialog box
(<B>Tools</B> menu). You do not lose the links to the external data, but
clearing this option prevents Excel from saving the value with the linked
worksheet.
<P><FONT size=3><B><A name=_Toc364227869>Optimizing for Speed</A></B></FONT>
<P>To optimize Excel for speed, use the following guidelines:
<P>
<UL type=square>
<P>
<LI>Do not select the <B>Precision as displayed</B> check box on the
<B>Calculation</B> tab in the <B>Options</B> (Windows) dialog box
(<B>Tools</B> menu) unless you really need it.
<P>When <B>Precision as displayed</B> is off, Excel stores the full precision
of a number in memory, and displays only the number of digits specified by the
formatting. When <B>Precision as displayed</B> is on, however, Excel performs
a math operation on every cell, which rounds the number. The operation forces
the precision of the number stored in memory to be equal to the number of
decimal places in the cell number format, slowing calculation speed.
<P></P>
<LI>If you use complicated <B>IF</B> functions on a worksheet, replace them
with <B>VLOOKUP</B> or <B>HLOOKUP</B> functions.
<P>
<P></P>
<LI>Avoid using the functions <B>AREAS</B>, <B>CELL</B>, <B>COLUMNS</B>,
<B>INDEX</B>, <B>INDIRECT</B>, <B>INFO</B>, <B>NOW</B>, <B>OFFSET</B>,
<B>RAND</B>, <B>ROWS</B>, and <B>TODAY</B>.
<P>Formulas that contain these functions, or dependents of those formulas,
must be recalculated every time there is a calculation, because their results
may change even if their precedent cells have not changed. If you must use
these functions, try to avoid having other calculations depend on their
results.
<P></P>
<LI>Avoid using user­defined functions and names that are complex
expressions, which are recalculated more slowly than the equivalent formula in
a cell.
<P></P>
<LI>If your worksheets contain a large number of pictures, you can speed up
scrolling by selecting the <B>Show placeholders</B> option for objects on the
<B>View</B> tab in the <B>Options</B> (Windows) dialog box (<B>Tools</B>
menu).
<P>If users want to switch quickly between viewing graphics and placeholders,
you can assign the following two <STRONG>Visual Basic procedures </STRONG>to
custom buttons.
<P><FONT size=3><PRE><FONT face=Arial>Sub ViewObjectPlaceholders()
ActiveWorkbook.DisplayDrawingObjects = xlPlaceholders
End Sub
</FONT><P><FONT face=Arial></FONT>
<FONT face=Arial>Sub ViewObjects()
ActiveWorkbook.DisplayDrawingObjects = xlAll
End Sub</FONT></P></PRE></FONT>
<P></P>
<LI>If you have large worksheets that take a long time to recalculate,
consider selecting the <B>Manual</B> option on the <B>Calculation</B> tab in
the <B>Options</B> (Windows) dialog box (<B>Tools</B> menu).
<P>Whenever you make a change to the worksheet that necessitates
recalculation, Excel displays the word Calculate in the status bar. You can
continue to change the worksheet, and when you are finished, press F9 to
recalculate manually.
<P></P>
<LI>Set your users' monitors to use only 16 or 256 colors.
<P>For writing reports and working with spreadsheets, users may need only 16
or 256 colors, so they can switch to a video driver that supports a lower
resolution and fewer colors. They can always switch back if there is no
improvement in performance or if their work requires additional video
capabilities.</FONT><FONT size=2></P></LI></UL></FONT>
<DIV><FONT size=2>
<P><FONT size=4><B><FONT size=2><FONT size=4>12B</FONT>. </FONT><U>Optimizing
Visual Basic Performance</U></B><U> </U></FONT>
<P><A name=dex53></A>
<P>In general, you can do more to improve the speed of your code by choosing
more efficient algorithms than by implementing particular coding tricks.
However, certain techniques can help make your code run faster.
<P><FONT size=2><B>*Compile the code in a database application before
distributing it to users</B></FONT>
<P>In the Module window, click <B>Debug</B>, and then click <B>Compile And Save
All Modules</B>.
<P><FONT size=2><B>*Always explicitly declare variables</B></FONT>
<P>You can require that variables be explicitly declared before they are used in
a procedure by selecting the <B>Require Variable Declaration </B>check box under
<B>Coding Options</B> on the <B>Module</B> tab in the <B>Options</B> dialog box
(<B>Tools</B> menu).
<P><FONT size=2><B>*Use the most specific type possible when you declare
variables </B></FONT>
<P>For example, declare a variable that is used to represent a form as type
<B>Form</B> rather than as type <B>Object</B> or <B>Variant</B>. This is
especially important if you are working with Automation objects or separate
instances of Visual Basic. When working with Automation objects, instead of
using a <B>Variant</B> data type or the generic <B>Object</B> data type, declare
objects as they are listed in the <B>Modules/Classes</B> box in the Object
Browser. This ensures that Visual Basic recognizes the specific type of object
you are referencing, allowing the reference to be resolved when you compile.
<P><FONT size=2><B>*Use variables to refer to properties, controls, and data
access objects</B></FONT>
<P>If you refer more than once to the value of a property or control on a form,
or to a data access object or its property, create object variables and refer to
the variables rather than using full identifiers. This approach is especially
effective for speeding up a looping operation on a series of properties,
controls, or objects.
<P><FONT size=2><B>*Use the Me keyword for form references within an event
procedure </B></FONT>
<P>When you make form references within an event procedure, use the <B>Me</B>
object variable to refer to the form. This restricts the search for the form to
the local name space.
<P><FONT size=2><B>*Use the IIf function judiciously</B></FONT>
<P>Avoid using the <B>IIf</B> function if either of the return expressions takes
a long time to evaluate. Access always evaluates both expressions. It is often
more efficient to replace the <B>IIf</B> function with an
<B>If</B>...<B>Then</B>...<B>Else</B> statement block.
<P><FONT size=2><B>*Use string functions when appropriate</B></FONT>
<P>Some functions have two versions, one that returns a <B>Variant</B> data type
(for example, the <B>Str</B> function) and one that returns a <B>String</B> data
type (for example, the <B>Str$</B> function.) When working with variables
declared with the <B>String</B> data type or when writing data directly to
random-access files, use functions with names that end with <B>$</B> if they are
available. This makes your operations run faster, because Access does not need
to perform type conversions. For more information about string functions, see
Access online Help.
<P><FONT size=2><B>*Use the Integer or Long data type for math when the size and
type of numbers permit</B></FONT>
<P>The <B>Variant</B> data type, although more flexible, uses more memory and
processor time as it translates between data types. The following table ranks
the numeric data types by calculation speed.
<P>
<TABLE borderColor=#c0c0c0 cellSpacing=0 cellPadding=4 width="80%" border=1
VALIGN="TOP">
<TBODY>
<TR>
<TD vAlign=bottom width="41%" bgColor=#c0c0c0><B><FONT size=2>Numeric data
type</FONT></B> </TD>
<TD vAlign=bottom width="59%" bgColor=#c0c0c0><B><FONT
size=2>Speed</FONT></B></TD></TR>
<TR>
<TD vAlign=top width="41%"><FONT size=2><B>Integer</B>, <B>Long</B></FONT>
</TD>
<TD vAlign=top width="59%"><FONT size=2>Fastest</FONT></TD></TR>
<TR>
<TD vAlign=top width="41%"><FONT size=2><B>Single</B>,
<B>Double</B></FONT> </TD>
<TD vAlign=top width="59%"><FONT size=2>Next­to­fastest</FONT>
</TD></TR>
<TR>
<TD vAlign=top width="41%"><FONT size=2><B>Currency</B></FONT> </TD>
<TD vAlign=top width="59%"><FONT size=2>Next­to­slowest</FONT>
</TD></TR>
<TR>
<TD vAlign=top width="41%"><FONT size=2><B>Variant</B></FONT> </TD>
<TD vAlign=top width="59%"><FONT size=2>Slowest</FONT></TD></TR>
<P></P></TBODY></TABLE>
<P><FONT size=2><B>*Use dynamic arrays and the Erase or ReDim statement to
reclaim memory</B></FONT>
<P>Consider using dynamic arrays instead of fixed arrays. When you no longer
need the data in a dynamic array, use either the <B>Erase</B> statement or the
<B>ReDim</B> statement with the <B>Preserve</B> keyword to discard unneeded data
and reclaim the memory used by the array. For example, you can reclaim the space
used by a dynamic array with the following code: <FONT size=3><PRE><FONT face=Arial>Erase intArray</FONT></PRE></FONT>While the <B>Erase</B>
statement completely eliminates the array, the <B>ReDim</B> statement used with
the <B>Preserve</B> keyword makes the array smaller without losing its contents.
<FONT face=Arial size=3><PRE>ReDim Preserve intArray(10, conNewUpperBound)</PRE></FONT>
<P>Erasing a fixed­size array does not reclaim the memory for the array; it
simply clears out the values of each element of the array. If each element was a
string, or a <B>Variant</B> data type containing a string or an array, then
erasing the array would reclaim the memory from those strings or <B>Variant</B>
data types, but not the memory for the array itself.
<P><FONT size=2><B>*Replace procedure calls with inline code</B></FONT>
<P>Although using procedures makes your code more modular, performing each
procedure call always involves some additional work and time. If you have a loop
that calls a procedure many times, you can eliminate this overhead by removing
the procedure call and placing the body of the procedure directly inline within
the loop. If you place the same code inline in several loops, however, the
duplicate code increases the size of your application. It also increases the
chance that you will not remember to update each section of duplicate code when
you make changes.
<P><FONT size=2><B>*Use constants whenever possible to make your application run
faster</B></FONT>
<P>Constants also make your code more readable and easier to maintain. If your
code has strings or numbers that do not change, declare them as constants.
Constants are resolved once when your program is compiled, with the appropriate
value written into the code. With variables, however, each time the application
runs and finds a variable, it needs to get the current value of the variable.
Whenever possible, use the intrinsic constants listed in the Object Browser
rather than creating your own.
<P><FONT size=2><B>*Use bookmarks instead of the FindNext method to return to a
particular record</B></FONT>
<P>Using the <B>Bookmark</B> property, you can write a procedure to find a
target record, store its bookmark value in a variable, move to other records,
and return to the original record by referring to the bookmark. For more
information about the <B>Bookmark</B> property, see Access online Help.
<P><FONT size=2><B>*Use the FindRecord and FindNext methods on indexed fields
</B></FONT>
<P>When locating records that satisfy a specified criteria, the
<B>FindRecord</B> and <B>FindNext</B> methods are much more efficient than the
<B>Seek</B> method when used on a field that is indexed.
<P><FONT size=2><B>*Consider reducing the number of procedures and
modules</B></FONT>
<P>While your application runs, each called procedure is placed in its own
public block of memory. Access incurs some overhead in creating and managing
these blocks. You can save some of this overhead by combining short procedures
into larger procedures.
<P><FONT size=2><B>*Organize the modules in an application</B></FONT>
<P>Visual Basic loads modules on demand — that is, it loads a module into
memory only when your code calls one of the procedures in that module. If you
never call a procedure in a particular module, Visual Basic never loads that
module. Placing related procedures in the same module causes Visual Basic to
load modules only as needed.
<P><FONT size=2><B>*Eliminate dead code and unused variables</B></FONT>
<P>As you develop and modify your applications, you may leave behind dead
code — entire procedures that are not called from anywhere in your code.
You may also have declared variables that are no longer used. Review your code
to find and remove unused procedures and variables; for example, <FONT
size=2>Debug.Print</FONT> statements.
<P>To search for references to a particular variable, use the <B>Find</B>
command on the <B>Edit</B> menu. Or, if you have <B>Option Explicit</B>
statements in each of your modules, you can quickly discover whether a variable
is used in your application by removing its declaration and running the
application. If the variable is used, Visual Basic generates an error. If you do
not see an error, the variable was not used.
<P>If your application has places in which the contents of a string variable or
a <B>Variant</B> data type containing a string is not needed, assign a
zero­length string ("") to that variable. If you no longer need an object
variable, set that variable to <B>Nothing</B> to reclaim the memory used by the
object reference.
<P>You can also use compiler directives and conditional compilation to ignore
portions of code based on constant values that you specify.</FONT><FONT
size=2></FONT><BR><FONT size=2>===============================</FONT></P>
<P><FONT size=2>Regards,<BR>Ton Maas<BR></FONT><A
href="mailto:ms-irb@xxxxxxxxxxxxxxxx";><FONT
size=2>ms-irb@xxxxxxxxxxxxxxxx</FONT></A><BR><FONT size=2>Dismiss the ".nospam"
bit (including the dot) when replying.<BR>Homepage </FONT><A
href="http://home.planet.nl/~anthmaas";><FONT
size=2>http://home.planet.nl/~anthmaas</FONT></A></P></DIV></BODY></HTML>
</x-html>From ???@??? Fri Mar 17 16:56:22 2000
Return-Path: <majordom@xxxxxxxxxxxxxxxxxx>
Received: from listserv.equis.com (listserv.equis.com [204.246.137.2])
by purebytes.com (8.9.3/8.9.3) with ESMTP id QAA26795
for <neal@xxxxxxxxxxxxx>; Fri, 17 Mar 2000 16:55:18 -0800
Received: (from majordom@xxxxxxxxx)
by listserv.equis.com (8.8.7/8.8.7) id JAA04129
for metastock-outgoing; Sat, 18 Mar 2000 09:00:07 -0700
X-Authentication-Warning: listserv.equis.com: majordom set sender to owner-metastock@xxxxxxxxxxxxx using -f
Received: from freeze.metastock.com (freeze.metastock.com [204.246.137.5])
by listserv.equis.com (8.8.7/8.8.7) with ESMTP id JAA04123
for <metastock@xxxxxxxxxxxxxxxxxx>; Sat, 18 Mar 2000 09:00:02 -0700
Received: from pierre.equis.com (mailman.equis.com [204.246.137.6])
by freeze.metastock.com (8.8.5/8.8.5) with SMTP id RAA09684
for <metastock@xxxxxxxxxxxxx>; Fri, 17 Mar 2000 17:32:25 -0700 (MST)
Received: from mailman.equis.com by pierre.equis.com
via smtpd (for metastock.com [204.246.137.5]) with SMTP; 18 Mar 2000 00:13:58 UT
Received: by mailman.equis.com with Internet Mail Service (5.0.1460.8)
id <GHSVJJDG>; Fri, 17 Mar 2000 17:22:57 -0700
Message-ID: <24FA77225FA5D111869F0000C025B6F20122E39D@xxxxxxxxxxxxxxxxx>
From: Equis Support <support@xxxxxxxxx>
To: "'metastock@xxxxxxxxxxxxx'" <metastock@xxxxxxxxxxxxx>
Subject: DataOnDemandTM issues
Date: Fri, 17 Mar 2000 17:22:56 -0700
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.0.1460.8)
Content-Type: text/plain
Sender: owner-metastock@xxxxxxxxxxxxx
Precedence: bulk
Reply-To: metastock@xxxxxxxxxxxxx
Status:
There seems to have been some confusion on these subjects:
Question:
Is this a necessary requisite or can we continue to store data on our local
hard drives? Can we continue to use data vendors other than Reuters?
Specifically will the data format required by the new version be made
available to other data vendors and, if so, will the new version accept data
from vendors other than Reuters?
Answer:
You can continue to store data on your local hard drive, as you always have
done in the past. You can still use data vendors other than ReutersDataLink,
such as Telescan, Dial Data, Signal, etc.
Question:
For anyone who uses MS Pro there will be a maintenance release within the
next few weeks. It will contain features which are in the latest MS 7.0 EOD
but not in Pro, such as the DataOnDemandTM for EOD data if using Reuters. I
expressly asked if Pro will accommodate both real-time Data On Demand and
EOD Data On Demand and was told yes.
Answer:
The MetaStock Professional maintenance release will be here next week. When
installing MetaStock Professional with the eSignal option, DataOnDemandTM
works only with eSignal, where you can get real-time and EOD data. When
installing MetaStock Professional with the No Vendor option, DataOnDemandTM
works only with ReutersDataLink. MetaStock EOD version 7.01 DataOnDemandTM
works only with ReutersDataLink's EOD data.
Question:
On the phone Equis Support told me that there would be a MS 7.0 EOD version
compatible with Quote.com's QFeed data stream.
Answer:
Quote.com will be an available data vendor in a future release of MetaStock
Professional, it is not planned for the EOD version.
Thank you,
Lynn
Equis Support
http://www.equis.com/
http://www.equis.com/customer/support/
Please include previous email answers and questions in your response.
Equis and MetaStock and MetaStock Professional are registered trademarks of
Equis International. Achelis Binary Wave, The DownLoader, Expert Advisor,
OptionScope, Quotecenter, and Smart Charts are trademarks of Equis
International.
|