Hi Nova!

First of all I should recommend you "Numerical Recipes" (NR) which is one of the most used books on my desk.

See:

http://www.nrbook.com/a/bookfpdf.php or:

http://www.fizyka.umk.pl/nrbook/bookcpdf.html
Q1

You need first to find out the accuracy with the double type of visual basic.

You can do a simple test like calculating ((1+1E-15) - 1).

You will see that ((1+1E-16) - 1) returns zero, which gives you and idea of the precision.

All commercial numerical libraries need the values of some "machine constant" to run properly or optimally. See chapters 1.2 and 20.1 in NR.

The accuracy can be improved by several ways but there are always limitations.

For example, reducing the integration step will often improve the accuracy, but if the step is smaller than some limit it will lose any sense for many reasons (computation time, value smaller than the smallest number that can be represented ...)

However, it is possible to go very far in precision since it is only a matter of computer resource and programming.

One of the programming methods is called "Arbitray precision computing". You can read about that in NR chap 20.6. In this case, you don't rely anymore on pre-defined type of your programming language. A "multiprecision library" has been developped for excel, you can find it there:

http://digilander.libero.it/foxes/SoftwareDownload.htm (same people as clsMathParser)

Q2

If you want a minimal level of wuality in your program, put this statement at the beginning of each module:

And correct your code any time VB complains.

This will at least make thing clear in your program, but in VB this will also often dramatically accelerate your code. I have experienced that recently: by Typing each variables I improved significiantly the speed of my code (say x2), then using "VB Watch" I could find out many other optimisations and my code finally was running 10 times faster.

When I said dirty programming, this is FIRST OF ALL because nothing was dim-ed !!!

The second reason was that the code was not much re-usable, with little abstraction. this is typically what one does to get quick result, but such code is short-lived usually.

For you problem, I consider that writing an integration routine specically working for string-defined function is nonsense and dirty. The fast that your need is like that does not mean that you need to implement it literally in this way. And this is really the most dirty aspect.

Actually, you can find a lot of integration routine on the web. Some have been used since very long and improved. Writing such an algortihm makes sense only for an exercice or for creating a new algorithm. Therefore you should reuse an existing algorithm as much as possible. Integration routines are essentially numerical algorithm (but it could be symbolic too, like with Mathematica or Mapple or Maxima, ...). If you approach is numerical, then use tested numerical integration routines and design the interface you need.

This brings us to the topic of "how to pass a function as argument to a routine".

This was already solved by Fortran in the 60's.

Since then, the techniques have evolved enormously.

It is clear that today, a function must been seen as an object, define in VB in a Class module.

Typically a function is object that needs variable and that can at least return a value.

You can pass this object ot you integration routine.

The clsMathparser that I mentioned is precisely an implementation of a "function" based on string definitions.

You could already improve your code by "encapsulating" the "Evaluate" thing in your code.

This means removing this excel-specific stuff from you integration routine.

Put something more general in your integration routine.

Later, if you want to work with the "Evaluate" from excel creat a small class module for that.

But, if you like the clsMathParser I mentioned, you should be able to use it without changing your integration routine at all.

That would be an exercice about polymorphism, I think this is the name.

Q3

No sure about the meaning of your question.

Always type your variable.

If you do not, the compiler work will be delayed till run-time, and will even be much less efficient.

Q4

Of course!

You should never rely on any excel-things unless the goal is totally excel-thing.

An integration routine is totally unrelated to excel, therefore don't rely on anything from excel.

Of course, at some point, your stuff need to run within excel (this is requested), then you need to create the needed interface.

Again, the "Evaluate" function is an example: don't use it in your integration routine, instead use a standard way or a more general way of using/defining the function to integrate. Later, when using your coe within excel, add a small amount of code to work with the excel function "evaluate" if you need it or if you want it.

Sorry for being long and dirty.

Let me stress again, that I would personally never use the "Evaluate" function from Excel.

This is for the reasons I explained already, but also for other problem I encoutered with localisation. I did once an xl program that worked fine for my EU collegues but not anymore in the US! Simply because of the representaion of numbers. It made absolutely no sense to remedy that. Instead I went back to my real requirements. This is only one of numerous reasons why I finally opted for clsMathParser.

Another reason is the speed when the same function must be evaluated repeatedly, which is your case too.

Q5

Analyse the timing with "VB Watch" or with a self made tool (inserting time spies).

http://www.aivosto.com/vbwatch.html
Unfortunately, this is not available for Excel-VBA, I believe.

But if you own VB6, your could try most of your stuff in VB6. Excel is not really needed.

Q6

Many possibilities probably.

Maybe start a timer that will intervene in time.