Thread: Objects in Excel

Hello,

I have inherited a small project where we handle inventory data in
excel. The way it's done now is simple: Use fixed columns and import
data about the products from database 1, then loop through the colums
and import details about the products from database 2 and so on.

Problem is that the size and complexity has grown over the years, so
now the macros is full of special cases and is slow and error-prone. I
would like to have the products in some data structure instead of
importing them directly into an excel sheet.

If I was to rewrite it in C++ I would know how to do:
1. Make the products ojects.
2. Put pointers to the objects in a vector that holds info about all
products.
3. Make a map or something that links unique product id to pointers to
the products.

Then I could easily add new products, look up the old and so on.

I know that I can make objects in Excel VBA, but does VBA OOP have any
serious limitations compared to C++ (or C#, or Java...)? What about
performance?

Best Regards
Fredrik

Sounds like someone should be using a database to me!

MH


<ffyring@kth.se> wrote in message
news:1151265998.116965.269730@m73g2000cwd.googlegroups.com...
> Hello,
>
> I have inherited a small project where we handle inventory data in
> excel. The way it's done now is simple: Use fixed columns and import
> data about the products from database 1, then loop through the colums
> and import details about the products from database 2 and so on.
>
> Problem is that the size and complexity has grown over the years, so
> now the macros is full of special cases and is slow and error-prone. I
> would like to have the products in some data structure instead of
> importing them directly into an excel sheet.
>
> If I was to rewrite it in C++ I would know how to do:
> 1. Make the products ojects.
> 2. Put pointers to the objects in a vector that holds info about all
> products.
> 3. Make a map or something that links unique product id to pointers to
> the products.
>
> Then I could easily add new products, look up the old and so on.
>
> I know that I can make objects in Excel VBA, but does VBA OOP have any
> serious limitations compared to C++ (or C#, or Java...)? What about
> performance?
>
> Best Regards
> Fredrik
>

VBA is not really a true OO programming language in the same way as Java or
C++. No overloading, no real inheritance (one level only) and so on. But
let's not get into a flame war - I am not interested on starting one.

The real rate determining step in the process you are describing would
appear to be database access. So, whichever language you use as the top of
the stack (VBA or C++) if you use OLEDB/ADO you'll not really see much
difference in performance if the program is manipulating data within Excel.

I would expect that it may be better to write this as a series of stored
procedures running on the database - link database 1 to database 2 and do all
the heavy lifting on the database server, but I cannot definitively state
that as I am not aware of all of the details.






--
www.alignment-systems.com


"MH" wrote:

> Sounds like someone should be using a database to me!
>
> MH
>
>
> <ffyring@kth.se> wrote in message
> news:1151265998.116965.269730@m73g2000cwd.googlegroups.com...
> > Hello,
> >
> > I have inherited a small project where we handle inventory data in
> > excel. The way it's done now is simple: Use fixed columns and import
> > data about the products from database 1, then loop through the colums
> > and import details about the products from database 2 and so on.
> >
> > Problem is that the size and complexity has grown over the years, so
> > now the macros is full of special cases and is slow and error-prone. I
> > would like to have the products in some data structure instead of
> > importing them directly into an excel sheet.
> >
> > If I was to rewrite it in C++ I would know how to do:
> > 1. Make the products ojects.
> > 2. Put pointers to the objects in a vector that holds info about all
> > products.
> > 3. Make a map or something that links unique product id to pointers to
> > the products.
> >
> > Then I could easily add new products, look up the old and so on.
> >
> > I know that I can make objects in Excel VBA, but does VBA OOP have any
> > serious limitations compared to C++ (or C#, or Java...)? What about
> > performance?
> >
> > Best Regards
> > Fredrik
> >
>
>
>