For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation

[vmegha]

Hi,

I have a VBA application that needs to parse data from a string into a
worksheet. I have to consider optimised performance, since the
worksheet contains real-time data.

Is it advisable to keep the data as a Strings..and use the string
manipulation functions for search / find to get my key value pairs ?

OR

Should I create Objects that have the key-value pairs as properties to
set /get from?

Both of them being feasible solutions, which would give me a better
performance? I'm New to VBA and understand from my programming
experience, that both String and Object manipulation could prove heavy
(like in Java - everything is an object) . But not sure, how this is in
VBA's object oriented design.

Please help.

-Megha.

 

[Harald Staff]

Hi Megha

Your question is very general and hard to give an answer to without exact
knopwledge of what your application is ment to do and how.

So here are a few general things:
String variables are like strings in any other language as far as I know;
pointers to strings. Alter a string variable, then another string is created
another place and the variable changes to a pointer there. Modern computers
shouldn't have problems with this unless the workload is enormous.
The maybe fastest "thing" in VBA is a Collection. You can fill collections
with just anything, variables, objects, ..., or mixtures of those, and
access the items with unique string IDs. They are pretty memory consuming
and maybe too liberal to please code purists, but collections are really
extremely fast and therefore very useful.
Excel will recalculate whenever a cell entry is altered. If you are going to
change multiplce cells by code, turn calculation off, change them and turn
calculation back on.
VBA can be as object oriented as you want it to be. You create your own
objects with class modules. Post back if this is unfamiliar to you.

So a wild guess from your question: My first idea would be to use a
collection of custom class objects, unless I was going to perform things
that Excel does better than VBA code does; calculating, sorting, pivoting,
.... then I'd just "remote control" Excel by code.

HTH. Best wishes Harald

 

[vmegha]

Thanks Harald,

I'll can give you some specifics now. The VBA application I'm working
on, fetches data as a string of key value pairs over a Tibco- RV
framework. The string can be read to build a complex data structure

X contains Collection of (Y)
Y contains Collection of (Z)

where each X, Y and Z are class modules...

(A)
So if the number of Y/ Z increase, I'm going to end up creating a lot
of Objects, which are useless, once I write their Values into my
Worksheet. (OBJECT MANIPULATION)

Alternatively I can just have

(B)
X contains Collection of (Y_Z as String) and read out my Key-value
pairs from the Y_Z string and write it to my Worksheet. (STRING
MANIPULATION)

I still need to keep X in Memory... so here's my feel..

With (A) I have more objects in Memory than with (B), I'm wondering how
this will affect the performance of my subsequent function of reading
their Values into the WorkSheet.

And once read from, if my objects are still around, if they'd slow down
my application, since after this I run some calculations and do some
filtering on the Data.

Hope I could be clear. But thanks for your quick reply.

-Megha