Excel 2000: I'm currently using a spreadsheet with so many formulas on it
that it's limiting the amount of data I can use it to process. If I program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?

Ed,

Maybe, but probably not. If you have a lot of rows of data leading to your
results then some preprocessing in a database (Access say) might help.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Excel 2000: I'm currently using a spreadsheet with so many formulas on it
that it's limiting the amount of data I can use it to process. If I program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?

Chris,

That's tough - most of the formulas are seriously complex and although I'm
no authority on access, I've been told it doesn't have quite the same level
of functionality as excel. Is there any other way to outsource the
calculations?

Cheers,

Ed.

"Chris Marlow" wrote:

Ed,

Maybe, but probably not. If you have a lot of rows of data leading to your
results then some preprocessing in a database (Access say) might help.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Excel 2000: I'm currently using a spreadsheet with so many formulas on it
that it's limiting the amount of data I can use it to process. If I program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?

Hi Ed,

One way round this, which is fairly easy if your formulae are repetitive
(e.g. certain columns in a table always have the same formula in them, which
you autofill to the bottom etc) is to set the macro to place the formulae,
then copy/pastespecial (values only) the column's results.

eg: put a formulae in cell G2, autofill to the bottom of the table, then
select this range, copy & paste values. This will mean that the formulae are
replaced with their result, reducing the resource requirement for the sheet.

The downside of this is that the macro will require re-running each time you
change any of the values upon which these cells depend. Additionally, if
you're using sheets which are not tabulated, you'd be reduced to entering the
formulae for each of these cells in the code, then copy/pasting each one.
This would be quite laborious at a guess, though the resource saving might be
worth it.

Hope this helps!
DS

"Ed" wrote:

Chris,

That's tough - most of the formulas are seriously complex and although I'm
no authority on access, I've been told it doesn't have quite the same level
of functionality as excel. Is there any other way to outsource the
calculations?

Cheers,

Ed.

"Chris Marlow" wrote:

Ed,

Maybe, but probably not. If you have a lot of rows of data leading to your
results then some preprocessing in a database (Access say) might help.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Excel 2000: I'm currently using a spreadsheet with so many formulas on it
that it's limiting the amount of data I can use it to process. If I program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?

Ed,

Thinking on I was probably a bit hasty. But the things that you would do
would cause the spreadsheet to lose its 'nature' as a spreadsheet. By that I
mean the idea of a spreadsheet is that when you change a cell the changes
propagate throughout the workbook as it recalculates. I'd say you would have
to 'batch up' some of the tasks your spreadsheet does, thus losing that
flexibility. I see it a lot the 'paste special' crowd who then wonder how
they ever got thier results.

If you had a bit of your spreadsheet where you were, say, heavily using
vlookups to match data then this may be better done in a database (which
after all has a relational engine which tries to optimise how it joins data).

I'd say data manipulation wise databases can do everything spreadsheets can.
It is just the display that is different. An issue I faced is where I had a
financial analytics package that only exposed itself to Excel. If you have an
issue like that then you are stuck with Excel.

There may well be some things you can do to optimise your formulae, eg
things like VLOOKUP & MATCH can cause issues, I'd google & see if you can
find some FAQ/hints & tips pages.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Chris,

That's tough - most of the formulas are seriously complex and although I'm
no authority on access, I've been told it doesn't have quite the same level
of functionality as excel. Is there any other way to outsource the
calculations?

Cheers,

Ed.

"Chris Marlow" wrote:

Ed,

Maybe, but probably not. If you have a lot of rows of data leading to your
results then some preprocessing in a database (Access say) might help.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Excel 2000: I'm currently using a spreadsheet with so many formulas on it
that it's limiting the amount of data I can use it to process. If I program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?

Chris,

Thanks - I've got a couple of vlookups that I can economise on, but the
rest... well - I'll figure something out. I gather there's a way to outsource
calculations to sql from Excel 2003, so maybe I'll be able to upgrade and go
down that route.

DS - Thanks for the plan, but I need to maintain the audit trail sadly.

Cheers,

Ed.

"Chris Marlow" wrote:

Ed,

Thinking on I was probably a bit hasty. But the things that you would do
would cause the spreadsheet to lose its 'nature' as a spreadsheet. By that I
mean the idea of a spreadsheet is that when you change a cell the changes
propagate throughout the workbook as it recalculates. I'd say you would have
to 'batch up' some of the tasks your spreadsheet does, thus losing that
flexibility. I see it a lot the 'paste special' crowd who then wonder how
they ever got thier results.

If you had a bit of your spreadsheet where you were, say, heavily using
vlookups to match data then this may be better done in a database (which
after all has a relational engine which tries to optimise how it joins data).

I'd say data manipulation wise databases can do everything spreadsheets can.
It is just the display that is different. An issue I faced is where I had a
financial analytics package that only exposed itself to Excel. If you have an
issue like that then you are stuck with Excel.

There may well be some things you can do to optimise your formulae, eg
things like VLOOKUP & MATCH can cause issues, I'd google & see if you can
find some FAQ/hints & tips pages.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Chris,

That's tough - most of the formulas are seriously complex and although I'm
no authority on access, I've been told it doesn't have quite the same level
of functionality as excel. Is there any other way to outsource the
calculations?

Cheers,

Ed.

"Chris Marlow" wrote:

Ed,

Maybe, but probably not. If you have a lot of rows of data leading to your
results then some preprocessing in a database (Access say) might help.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Excel 2000: I'm currently using a spreadsheet with so many formulas on it
that it's limiting the amount of data I can use it to process. If I program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?

Excel calculates very fast: generally you wont see much improvement in
calculations speed in VB except if you can get to the same result with fewer
calculations.

For information on large Excel workbooks and optimising calculation speed I
suggest you look at my website.

Databases are good at manipulating data, but not much good at doing
calculations:
The problem with SQL-type database calculations is that it is very hard work
to do any kind of complex calculations eg:
- time series calculations
- calculations that reference other rows
- offset calculations (row before or column after kind of thing)

SQL is designed around the idea that all the data you want for the
calculation exists on the row where the result will surface. This makes it
excellent for doing a small number of repeated simple calculations on a
large number of identical rows, but just about impossible for any kind of
model that involves a large number of formulae. So the only answer for these
kinds of calculations in a database is to program the calculations
processing arrays or recordsets in whatever language the DB supports.

So IMHO the best of both worlds is to do the data manipulation in a
database and then calculate the results with a spreadsheet that gets its
data from the DB.


Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com


"Chris Marlow" wrote in message
...
Ed,

Thinking on I was probably a bit hasty. But the things that you would do
would cause the spreadsheet to lose its 'nature' as a spreadsheet. By that
I
mean the idea of a spreadsheet is that when you change a cell the changes
propagate throughout the workbook as it recalculates. I'd say you would
have
to 'batch up' some of the tasks your spreadsheet does, thus losing that
flexibility. I see it a lot the 'paste special' crowd who then wonder how
they ever got thier results.

If you had a bit of your spreadsheet where you were, say, heavily using
vlookups to match data then this may be better done in a database (which
after all has a relational engine which tries to optimise how it joins
data).

I'd say data manipulation wise databases can do everything spreadsheets
can.
It is just the display that is different. An issue I faced is where I had
a
financial analytics package that only exposed itself to Excel. If you have
an
issue like that then you are stuck with Excel.

There may well be some things you can do to optimise your formulae, eg
things like VLOOKUP & MATCH can cause issues, I'd google & see if you can
find some FAQ/hints & tips pages.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Chris,

That's tough - most of the formulas are seriously complex and although
I'm
no authority on access, I've been told it doesn't have quite the same
level
of functionality as excel. Is there any other way to outsource the
calculations?

Cheers,

Ed.

"Chris Marlow" wrote:

Ed,

Maybe, but probably not. If you have a lot of rows of data leading to
your
results then some preprocessing in a database (Access say) might help.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Excel 2000: I'm currently using a spreadsheet with so many formulas
on it
that it's limiting the amount of data I can use it to process. If I
program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?

Charles,

In the spirit of debate I'd take issue with some of what you have said.

If you are looking at the mathematical side fundamentally you can't get away
from complex things being based on doing simple things (add, subtract,
multiply, divide etc). The key is in how your data is presented to the
problem.

I accept that if you import a spreadsheet into Access (because it has got
big say) & then try to do funky financial maths on it you are likely to hit a
wall. I see this 'accountants folly' on a daily basis (insistance that all of
the data is in one table & then wondering why the DB performs like a dog & is
chewing up server space at a vast rate of knots). You have to think about the
problem & see how it will work.

Where databases can help is in producing 'audit friendly' intermediate steps
that can be independently validated & repeated. Again it comes down to using
the software properly though.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Charles Williams" wrote:

Excel calculates very fast: generally you wont see much improvement in
calculations speed in VB except if you can get to the same result with fewer
calculations.

For information on large Excel workbooks and optimising calculation speed I
suggest you look at my website.

Databases are good at manipulating data, but not much good at doing
calculations:
The problem with SQL-type database calculations is that it is very hard work
to do any kind of complex calculations eg:
- time series calculations
- calculations that reference other rows
- offset calculations (row before or column after kind of thing)

SQL is designed around the idea that all the data you want for the
calculation exists on the row where the result will surface. This makes it
excellent for doing a small number of repeated simple calculations on a
large number of identical rows, but just about impossible for any kind of
model that involves a large number of formulae. So the only answer for these
kinds of calculations in a database is to program the calculations
processing arrays or recordsets in whatever language the DB supports.

So IMHO the best of both worlds is to do the data manipulation in a
database and then calculate the results with a spreadsheet that gets its
data from the DB.


Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com


"Chris Marlow" wrote in message
...
Ed,

Thinking on I was probably a bit hasty. But the things that you would do
would cause the spreadsheet to lose its 'nature' as a spreadsheet. By that
I
mean the idea of a spreadsheet is that when you change a cell the changes
propagate throughout the workbook as it recalculates. I'd say you would
have
to 'batch up' some of the tasks your spreadsheet does, thus losing that
flexibility. I see it a lot the 'paste special' crowd who then wonder how
they ever got thier results.

If you had a bit of your spreadsheet where you were, say, heavily using
vlookups to match data then this may be better done in a database (which
after all has a relational engine which tries to optimise how it joins
data).

I'd say data manipulation wise databases can do everything spreadsheets
can.
It is just the display that is different. An issue I faced is where I had
a
financial analytics package that only exposed itself to Excel. If you have
an
issue like that then you are stuck with Excel.

There may well be some things you can do to optimise your formulae, eg
things like VLOOKUP & MATCH can cause issues, I'd google & see if you can
find some FAQ/hints & tips pages.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Chris,

That's tough - most of the formulas are seriously complex and although
I'm
no authority on access, I've been told it doesn't have quite the same
level
of functionality as excel. Is there any other way to outsource the
calculations?

Cheers,

Ed.

"Chris Marlow" wrote:

Ed,

Maybe, but probably not. If you have a lot of rows of data leading to
your
results then some preprocessing in a database (Access say) might help.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Ed" wrote:

Excel 2000: I'm currently using a spreadsheet with so many formulas
on it
that it's limiting the amount of data I can use it to process. If I
program
the formulas in VB will it increase the calculation capacity of the
spreadsheet I'm using? Or will I have to do something else?

Seems to me there is a large class of calculation problems that do not work
well in DBs, just as there is a large class of data manipulation problems
that do not work well in spreadsheets, not to mention OLAP.

I would be surprised if anyone who has worked extensively with both SS and
DB would disagree.

Of course there are a lot of people using DBs when they should be using SS
and a lot of people using SSs when they should be using DBs, and not nearly
enough people using SSs linked to DB engines!

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

Charles,

Now OLAP is a different kettle of fish ... but to do some serious OLAP you
really want it linked to a database server ROLAP, MOLAP or HOLAP anyone? SQL
Analysis Sevices looked quite handy on SQL Server 2000 & I remember my days
administering a TM1 server fondly (well that is a bit strong) Apart from
where some nugget consultants tried to bolt on some relational data ... it
all comes down to training.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Charles Williams" wrote:

Seems to me there is a large class of calculation problems that do not work
well in DBs, just as there is a large class of data manipulation problems
that do not work well in spreadsheets, not to mention OLAP.

I would be surprised if anyone who has worked extensively with both SS and
DB would disagree.

Of course there are a lot of people using DBs when they should be using SS
and a lot of people using SSs when they should be using DBs, and not nearly
enough people using SSs linked to DB engines!

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com