Depends on the formula, but

Results = xlApp.Evaluate(StrFormula)

should work.

The formula must be specific in terms of the ranges it addresses.

Many Worksheetfunctions can be used in VBA directly.


Results=xlapp.Sum(Worksheets("Sheet1").Range("A1:A 200"))

--
Regards,
Tom Ogilvy



Carlos Magalhaes wrote in message
...
Yeah I was HOPING that was not the only option, I really dont want to
write anything to that file I just want to apply the formula get the
data and close the file.

Not possible ??


On Fri, 7 Nov 2003 19:37:24 +0200, "Haldun Alay"
<haldunalayATyahooDOTcom wrote:

Hi,

Use Saved property of workbook. Set it TRUE before closing the workbook.

Finally the answer i was lookign for :-) thank you.

You said depends on the formula, what do I have to watch out for for?
i.e. when will the Evaluate function not run a strFormula i am passing
to it.

At the moment all my formulas have the cell and range ref in them.

Thank you once again

Carlos

On Sat, 8 Nov 2003 10:22:17 -0500, "Tom Ogilvy"
wrote:

Depends on the formula, but

Results = xlApp.Evaluate(StrFormula)

should work.

The formula must be specific in terms of the ranges it addresses.

Many Worksheetfunctions can be used in VBA directly.


Results=xlapp.Sum(Worksheets("Sheet1").Range("A1: A200"))

I can't say there are any specific formulas it will not process - the best
would be to just test it. A formula could be

Sum(A1:A10)

That would default to the activesheet in evaluate which may or may not be
what you intended. I was thinking of things such as this that might be a
stumbling block.

Also, I don't think I have ever tested it with Analysis toolpak functions or
UDF's.

--
Regards,
Tom Ogilvy




Carlos Magalhaes wrote in message
...
Finally the answer i was lookign for :-) thank you.

You said depends on the formula, what do I have to watch out for for?
i.e. when will the Evaluate function not run a strFormula i am passing
to it.

At the moment all my formulas have the cell and range ref in them.

Thank you once again

Carlos

On Sat, 8 Nov 2003 10:22:17 -0500, "Tom Ogilvy"
wrote:

Depends on the formula, but

Results = xlApp.Evaluate(StrFormula)

should work.

The formula must be specific in terms of the ranges it addresses.

Many Worksheetfunctions can be used in VBA directly.


Results=xlapp.Sum(Worksheets("Sheet1").Range("A1: A200"))

Great seems to be workig for all my formulas.


Thanks once again.

Carlos

On Sat, 8 Nov 2003 15:03:54 -0500, "Tom Ogilvy"
wrote:

I can't say there are any specific formulas it will not process - the best
would be to just test it. A formula could be

Sum(A1:A10)

That would default to the activesheet in evaluate which may or may not be
what you intended. I was thinking of things such as this that might be a
stumbling block.

Also, I don't think I have ever tested it with Analysis toolpak functions or
UDF's.

Something interesting:

Using the evaluate function on the worksheet, i pass it a string which
is the actual formula:

=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRUE ,FALSE))

Now the interesting thing is that i am trying to determine the result.
the result can either be ONLY true or FALSE and i am doing this
Where formula = Dim formula as string =
"=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRU E,FALSE))"

Dim Results As Boolean
Results = objSheeti.Evaluate(formula)

Now when the values in the excel spread sheet are correct and the
formula does in actual fact return a true value Results =
objSheeti.Evaluate(formula) works but as soon as the formula evaluates
to FALSE (i.e. it SHOULD be returning a false) i get an Array of
values and not just FALSE.... like when the value is right i just get
true:

i get:

- objSheeti.Evaluate(formula) {System.Array} Object
+ (1,1) False {Boolean} Boolean
FalseString "False" String
TrueString "True" String
- (2,1) False {Boolean} Boolean
FalseString "False" String
TrueString "True" String


Why is it returning this and not just FALSE? Is the formula wrong?
What am i doing wrong , or what is the reason for it returning this
value?

Thank you very much

CM


On Sat, 8 Nov 2003 15:03:54 -0500, "Tom Ogilvy"
wrote:

I can't say there are any specific formulas it will not process - the best
would be to just test it. A formula could be

Sum(A1:A10)

That would default to the activesheet in evaluate which may or may not be
what you intended. I was thinking of things such as this that might be a
stumbling block.

Also, I don't think I have ever tested it with Analysis toolpak functions or
UDF's.

the problem is the isblank(G26:G37)

this returns an array of booleans

Put
=ISBLANK(G26:G37)

in a cell,

then select the IsBlank(G26:G37) and hit F9 and you will see it returns an
array (although the cell will display false since only the first value is
displayed).

You can you

CountBlank

or CountA instead of isblank.

These will return a single value though not boolean.


If(countblank(G26:B37)=12,True,False)

Might do what you want.

--
Regards,
Tom Ogilvy

"Carlos Magalhaes" wrote in message
...
Something interesting:

Using the evaluate function on the worksheet, i pass it a string which
is the actual formula:

=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRUE ,FALSE))

Now the interesting thing is that i am trying to determine the result.
the result can either be ONLY true or FALSE and i am doing this
Where formula = Dim formula as string =
"=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRU E,FALSE))"

Dim Results As Boolean
Results = objSheeti.Evaluate(formula)

Now when the values in the excel spread sheet are correct and the
formula does in actual fact return a true value Results =
objSheeti.Evaluate(formula) works but as soon as the formula evaluates
to FALSE (i.e. it SHOULD be returning a false) i get an Array of
values and not just FALSE.... like when the value is right i just get
true:

i get:

- objSheeti.Evaluate(formula) {System.Array} Object
+ (1,1) False {Boolean} Boolean
FalseString "False" String
TrueString "True" String
- (2,1) False {Boolean} Boolean
FalseString "False" String
TrueString "True" String


Why is it returning this and not just FALSE? Is the formula wrong?
What am i doing wrong , or what is the reason for it returning this
value?

Thank you very much

CM


On Sat, 8 Nov 2003 15:03:54 -0500, "Tom Ogilvy"
wrote:

I can't say there are any specific formulas it will not process - the
best
would be to just test it. A formula could be

Sum(A1:A10)

That would default to the activesheet in evaluate which may or may not be
what you intended. I was thinking of things such as this that might be a
stumbling block.

Also, I don't think I have ever tested it with Analysis toolpak functions
or
UDF's.