That should be:

Dim myarray As Variant

without the ()

If you use Dim myarray() you'll get a type mismatch when you get to
the myarray = Range("myrange").value line.




On May 23, 9:33*am, wrote:
JNW - You may find this thread from Daily Dose of Excel interesting.
It includes a simple VBA lookup function.

http://www.dailydoseofexcel.com/arch...mance-monitor/

To get the data from a worksheet range into an array simply declare
the array as a variant and use = to fill the array:

Dim myarray() as variant
myarray = Range("myrange").value

That will give you a base 1 array with the values from the spreadsheet
range; obviously you don't get the formatting or any other
information.

You access the values using the row and column index numbers:

ArrayValue1 = myarray(1,1) * ' (row index, column index)

so you don't need an INDEX function.

A simple VBA lookup is given in the DDofE link, although I'm sure
there are better examples around.

Also you may find the two post on ranges and arrays from my blog
interesting:

http://newtonexcelbach.wordpress.com...-and-arrays-2/

On May 23, 12:31*am, JNW wrote:



Doug-

Thanks for the information about the slow down with using worksheetfunction.

I don't use it much but when I do it's for either vlookup, match, or index.

Do you know of (or can write) an example of how to replace these
worksheetfunctions with array functionality in vba? *I am not very good with
arrays yet.

Thanks

" wrote:
I haven't found a significant difference in performance with the
different file formats, other than time for opening and saving, which
is much quicker in xlsb than xls.

On a dual core machine recalculation is quicker in2007, but VBA
doesn't use the dual cores,and is slower, sometimes much slower. *In
particular any operation that involves interaction between VB and the
spreadsheet (such as using worksheetfunction) seems to be very much
slower in2007.

More details he
http://newtonexcelbach.wordpress.com...007-performanc....
http://newtonexcelbach.wordpress.com...etfunction-vs-....

The only solution I know is to avoid using worksheetfunction, and work
on arrays inside VB, rather than reading and writing directly to
worksheet ranges.

On May 22, 8:08 am, JNW wrote:
I tried all the extensions below and these are the results using xl2007
xls (original format): 6 min 37 sec
xlsb: 7 min 9 sec
xlsm: 6 min 47 sec
xlsx: 3 min 30 sec

running an xls using 2003 is in the 3-4 minute range. *

Thanks for the suggestions. *I'll have to wait to change the file to an xlsx
until the client gets all their users to xl07.

This begs another question though... why is the xlsx faster than the xlsm
(which I would presume is better for handling macros)? *Do you know why they
made the distinction between all of these different file types? (or where I
can find documentation on that?)

Thanks again. *You've relieved a lot of frustration.

"Nick Hodge" wrote:
You will find calculation faster if you save as an xlsb file as opposed to
xlsx or xlsm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

web:www.excelusergroup.org
web:www.nickhodge.co.uk

"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. *They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? *I
am
still experiencing a 50% slowdown. *I've found that it somehow has
something
to do with calculation. *I have a number of circular references that are
needed in the workbook. *I have iterations on and set to the same setting
as
they were in 2003. *I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? *Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and2007calculation steps?

Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -