EXCEL As VS.NET Math Engine

[andrew allan]

I was trying to use excel as a math engine in a VB.NET application.

I used the following code snippet

Dim xL As New Excel.Application
Dim B As String

MsgBox(xL.Evaluate(B).ToString)

This works as long as the string value parsed to EXCEL doesn't exceed a
certain length (i.e. the string "AVEARGE({0,1,2......,82,83,84})"
worked. The string "AVEARGE({0,1,2......,82,83,84,85})" fails and gives
me the result -2146826273.

However when the same strings are used in the native EXCEL environment
they seem to work just fine.

I am using Visual Studio.NET and have loaded a references to both the
EXCEL 10.0 and OFFICE 10.0 object libraries.

I need to parse sets of data which may contain upward of 100 points, and
am interested in doing some further statistical manipulation on them
(such as percentiles, standard deviation etc). Does any one have any
suggestions on how I can get over this seeming limited string
caapability.

 

[Rob Bovey]

Hi Andrew,

Instead of trying to evaluate these arrays, why not put the data into a
range of cells on a worksheet and then use that range in your worksheet
function. This will remove any problems you have with too many numbers. For
example, =AVERAGE(A1:A1000) lets you average 1000 values (and that's just a
number I pulled out of the air, you can use much larger ranges than this).
This is really the way Excel is designed to work.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Daniel.M]

Hi Andrew,

You're probably reaching a 256 characters limit for a formula (to be
evaluated by Evaluate).

Why don't you generate your array in the following manner, using
Evaluate()'s array processing ability.

B="AVERAGE(ROW(1:85)-1)"
MsgBox(xL.Evaluate(B).ToString)

As you can see, the array is processed internaly (within the Evaluate).
This is just a hint on how to generate ascending numbers, for the general
rule (using cells to process data, instead of putting all the items into a
long string), follow Rob's answer.

Regards,

Daniel M.