I don't think there is any array  slicing available in VBA, so For loops seem to
be the way to go.

However, I can't think why you'd be moving a million array items around inside
Excel VBA. If you have this amount of data, it really sounds as though you
should be looking at using a fully-compiled language, or redesigning your
application so that you don't need to move the data  around.

What is your application doing?

I am writing a branch and bound algorithm for
a worker-job assignment (100 jobs, 8 workers) problem. Every parent
node give rise to multiple children nodes. These children nodes have
one extra job assignment than the parent node. Therefore, I first
copy assignments (and other related data) of the parent node in the
children node. (Ex: parent node: a(node, 1)=2, a(node, 2)=3, a
(node,3)=8,--, a(node,50)=4 and other related details)

Then I make 1 additional job assignment in the children nodes (node
+j, j=1 to births).
(a(node+j, 1)=2, a(node+j, 2)=3, a(node+j,3)=8, a(node+j,50)=4,
a(node+j, 54)=5)
Many of these children nodes are maintained for quite some time.

This sounds like it could be a job for a tree control... like the one
used for windows explorer. I have code for that somewhere if you
like... may take a couple of days to find it though.

I read somewhere that dynamic arrays can be assigned in VB6.

It's easy to see how the number of operations will grow with requirements of
this size. Does it have to be done in Excel?

Excel VBA should be considered as a support language for manipulating the
associated spreadsheets, not as a programming environment in its own right.
Although it is compiled, it does not compile to object code, merely to an
internal representation, and will always be much slower than a fully-compiled
language.

Under most circumstances the inherent slowness isn't noticeable or at least
isn't too annoying. But you'll soon notice the slowness on compute-intensive
work.

Actually I've done some quite acceptable applications in Excel, Word
and Access VBA. One of the more recent ones was in Word VBA where I
collected a bunch of filenames and massaged them to comply with
certain standards. Nothing to do with word unless you count the
"report"... but it worked and the "client" was pleased.

Rather than saying VBA shouldn't be used for that sort of thing...
which I disagree with... why not turn it around and say that there is
no need to go to a "special" environment to do a particular job.

If the user is happy then what the hell. So it may take a bit of
time... go and get a cup of tea!!! ... can you tell I'm English..
:-)

... And I would be suprised if you haven't used VBA for something that
it "shouldn't be used for"... no such beast I think.

Well, yes. I do lots of things with Excel. I'm currently working on two
investment control systems that use Excel as the front-end to overseas
Sybase databases.

However, I stand by what I said. Excel VBA (and Word VBA, etc) are not
fully-fledged programming environments and in particular do not provide the
optimisations and efficiencies that make handling large amounts or data
efficient.

VBA IS inherently slow. In many cases this isn't noticeable (or isn't a
worry), so go ahead and use it.

However, some jobs do require a special environment. For instance,
compute-intensive jobs do not run efficiently in interpreted or
semi-compiled environments such as VBA.

I think the whole point is that the user isn't happy with the performance,
as indicated in the original question.

As my application need to be coupled
with excel and some of it's facilities, I would have to use it right
now and right now execution time is not too bad (1-2 min).

If later aspects of application required still more computational
effort (and time increased to hours), I would have to switch to fully
programmable language. I am right now dealing with array  of 0.5
million row, 100 columns and copying data  from one row to another
etc. Later application (different) may involve more movement even
though storage requirement will not increase.

How much computational time savings could other language(like C++)
provide? (by factor of 5, 10, 20 or 100)
Is it possible to couple them well with excel allowing user to input
data in excel and see output in excel sheet? Can one write some
aspect of code in vba and one module in C++ and combine them without
much headache? I am just looking for your personal opinion in this
regard.

I wonder if PowerBASIC <http://www.powerbasic.com/products/pbdll32/> would
help? It's my understanding that it can compile a DLL that VBA could
interface with.

I've never done any specific comparisons between VBA and fully-compiled
languages, but I would expect a proper executable would probably run at least 10
times faster  than a semi-compiled language, and 100 times faster than a
fully-interpreted language,

You don't have to go to C++ for full compilation. VB6 will give it to you too,
and you could undoubtedly move your code over to VB with much less pain than to
C++.

OTOH if you go to C++, then its fully object-oriented capabilities might allow a
very different approach to your solution.

As far as running  in another environment but still connecting to the Excel
spreadsheet. Yes you can do it, but you'll then have the overhead of opening
the sheet from the program, and that may also be unacceptable.

500,000 rows by 100 columns is a huge amount of data  to manipulate, and you may
run into performance problems once you start doing too much with it. However,
live with it while you can and get your algorithm thoroughly debugged. The
Excel VBA environment is quite nice to work in - especially if you have two
screens on your computer.

I haven't tried to call externally-compiled computation modules from Excel, but
it should certainly be possible, although cumbersome. Don't worry about it
until you need it.

> The Excel VBA environment is quite nice to work in -
> especially if you have two screens on your computer.
You can say that as many times as you like... and I will agree with
you every one.

> I've never done any specific comparisons between VBA and
> fully-compiled languages, but I would expect a proper
> executable would probably run at least 10 times faster  than
> a semi-compiled language, and 100 times faster than a
> fully-interpreted language,
Depends on the optimisation sometimes.
I've seen optimisation actually increase the executable times over
interpreted languages.. especially those.. like VB... that have been
through the mill and are pretty much optimised themselves. I'm not
decrying that binaries genearally are faster... I'm saying that people
have worked so much on getting it right in some interpreted languages
that the times may be closer than you think... especially because that
work hasn't generally gone into the assembler that is generated.

It's sobering to see the amount of assembler generated by a "visual"
"compiled language" compared with what an assembler programmer can
achieve.

> I haven't tried to call externally-compiled computation
> modules from Excel, but it should certainly be possible,
> although cumbersome. Don't worry about it until you need it.
Neither have I though I look with jealosy on some that I know who have
managed to create DLLs of their code to share.