data from a formula direcly to a variable

[yaniv.dg]

hi all,
i'm using formulaarray but i would like to save time and direcly the
data from the formula to transfer to variable instead on having in on a
cell,
it a metter of saving time and machine time
is there an option for this?
this is my code:
xlApp.Range("S" & row).Select
Selection.FormulaArray =" "

how can i direcly transfer the data to a variable?

 

[Bob Phillips]

Get rid of the selects

xlApp.Range("S" & row).FormulaArray =" "



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[yaniv.dg]

hi bob,
i dont understand how it can be helpfull for me.
the range is set for the cell anyway,this way i need to update all my
..formulaarray it sensless,
if i'm doing select range at start i dont need to update where am i

 

[Bob Phillips]

You talked about saving time and machine time. Selects are very slow, so
removing them is faster.

You can either anchor yourself in a range

With xlApp.Range("S" & row)

and thenreference from that point

.FormulaArray =" "

or set a range variable

Set rngBase = xlApp.Range("S" & row)

and use this

rngBase.FormulaArray =" "

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[yaniv.dg]

hi bob,
yes thats true,i wanted to save time and this is one option
but another thing,i dont want to have the data on the screen becaouse
it takes time too.
do you think there is an option to transfer the value from the formula
to a variable?
i believe that will save a massive machine time

 

[Dave Peterson]

Maybe...

yourvariable = application.evaluate("yourarrayformula")

 

[Bob Phillips]

Another thing to do is turn off screenupdating and automatic calculation

xlApp.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and at the end reset them

Application.Calculation = xlCalculationAutomatic
xlApp.ScreenUpdating = True

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[yaniv.dg]

hi dave,
can you give me more live example,becaouse its not seems to be working
for me

 

[Bob Phillips]

Application evaluate does what it says, it evaluates the formula and returns
the results. By using

yourvariable = application.evaluate("yourarrayformula")

the result is assigned to yourvariable. So you need to replace
"yourarrayformula" with yourformula, the one you didn't want to assign as a
formula to the cell.

Not much else to say about it.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

hi dave,
can you give me more live example,becaouse its not seems to be working
for me