VB6 faster than VBA in Excel?

[Guest]

Hi ,

My program is very slow even if I did everything from the memory
with arrays before sending the result on a worksheet. is it the right way? I
created a button on Worksheet(0) and this button calls the Sub that will
gather 100000 Oracle records for doing calculation.


Do you know if it will be faster by creating a .exe from VB6? of all is the
same when it's done from excel?

Thanks
J

 

[Guest]

VBA and VB6 use the exact same engine. To be exact VBA is the base engine and
was developed first. VB6 uses that engine and bolts on a bunch of extra
functionallity in terms of createing DLL's and such. That being said however
VBA code is compiled into an EXE where as VBA is interpreted (not exactly
true but close enough) so VB will run faster than VBA. If you want you could
create a COM addin from VB6 and have VBA use that (if Excel is your platform
of choice) addin which is compiled to improve your speed.

All of that being said have you considered an Excel pivot table connected
directly to the Oracle database. Assuming you just want aggregation then this
is an excellent choice and the speed is generally speaking very adequate (a
couple of seconds to query the 100K records of data for the pivot table)...

 

[Tim Williams]

Can you just do the calculations in the database?
Do you really need to return all of the records?

Tim

 

[gimme_this_gimme_that]

I'm puzzled what you mean Jim.

SQL Server actually allows you to create pivot tables that designed
for direct access with in Excel. The set up requires the latest
versions of SQL Server and Visual Studio. You create the Pivot Tables
from Visual Studio.

But you're suggesting that Oracle has pivot table objects. That's
*not* what you mean, right? You mean using Excel to query data from
oracle and then to use VBA to write the data to a Worksheet where a
pivot table is from the data in the Worksheet.

 

[Charles Williams]

I think what Jim is talking about is reading the data directly into the
pivot cache using SQL, without ever writing it to a worksheet. (This method
also bypasses the 65K row limit).

 

[Guest]

yes and it's slow! I don't know what to do!
Thanks.

 

[Peter T]

In answer to your subject line - it depends on what the code does. As a
generalization one might expect VB6 to be a bit faster, though with certain
specific tasks and compile optimisations (n/a in vba) VB6 could be very very
significantly faster than VBA.

What's your definition of slow, importing and 'calculating' 100k records
will not be instantaneous.

From the little of what you have described I suspect/guess the main thing
that would speed up your process is to optimise your own code.
way?

Again guessing, doing just that would most probably help, perhaps
dramatically. Finally dump say 10k (of your 100k records) at a time to cells
rather than looping to individual cells, if indeed you need to touch cells
at all.

Why not post the steps you currently do, outline what you start with and the
overall objective.

Regards,
Peter T