Alternatives to VBA in Excel

[Thomas Kregeloh]

Hi,
excuse my cross-posting, but I do not know which NG is the appropriate for
my problem.
I wrote an Excel-Macro in VBA which reads a text file, interprets this file
and then does in Excel what has to be done: format cells, put values to it,
storing files, inserting worksheets etc.
This works well, as designed, but is not very fast.
So I look for a way to increase speed.
I have Visual C++ 6 in Visual Studio. Can I rewrite my macro in Visual C++
so that it gets faster? If yes, where do I find "easy to understand"
descriptions how to do this? What do I need additionally?
TIA
Thomas Kregeloh

 

[Tom Ogilvy]

Do you use Select in your vba code? Does the screen flash all over the
place while your code is running?
If so, you might be able to dramatically increase the speed of your macro by
not using select or by setting ScreenUpdating = False and turning
calculation off.

 

[Bob Phillips]

Thomas,

Before you go down the C++ route, why don't you post the code. There are
many techniques to improve performance woithout writing C++ XLLs or DLLs.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Thomas Kregeloh]

Hi Tom,
yes, I use select. I have disabled the screen-update in order to increase
speed.
What's the problem about "select"? I did not yet think about turning
calculation off.
Regards
Thomas

 

[Thomas Kregeloh]

Well, Bob,
I do not dare to put some hundreds loc into this message. It is a system
which can do quite a lot of formatting and manipulating the Excels sheets
and workmaps that itproduces, incl. defining its own subroutines,
administrating variables etc.
Thomas

 

[Bob Phillips]

The problem with select is that it is slow, and if there is a lot of it
going on, it can make quite a performance difference. If on top of that you
move between worksheets, that compounds the problem. For instance, this is
the sort of code that the macro recorder creates

Range("A1").Select
Selection.Value = "abc"

can be more efficiently written as

Range("A1").Value = "abc"

The amount of work removed here, and the CPU cycles, is significant, but the
result is exactly the same.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Thomas Kregeloh]

Hi Bob,
is this also true when screen updating is switched off?
Thomas

 

[Jon Peltier]

Thomas -

Even with screen updating disabled, selecting cells and especially
activating worksheets can slow down the code.

Another big time waster is looping through a worksheet range and adding
cells to a VBA array one by one, then looping through the array to
repopulate the worksheet. Much better is to move the range into a VBA
variant in one step, and move the array back in another step:

dim vArray as Variant
vArray = Worksheets(1).Range("A110").Value

now you can refer to vArray as if it were dimensioned (1 to 10, 1 to 4).
When you are done manipulating the array, or if by now it's in a
different array, you assign the range values to the array:

iRow = ubound(MyArray,1)+1-lbound(MyArray,1)
iCol = ubound(MyArray,2)+1-lbound(MyArray,2)
Worksheets(2).Range("A1").resize(iRow,jCol).value = MyArray

When I first learned this trick on an old 486 machine, it was amazing
how much more quickly it worked. On these newer GHz machines, it's less
of a difference, but still noticeable.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______