data from a formula direcly to a variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

yaniv.dg@gmail.com - 10 Jul 2006 12:42 GMT

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?

Reply to this Message

Bob Phillips - 10 Jul 2006 12:56 GMT

Get rid of the selects

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

Signature

HTH

Bob Phillips

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

> hi all,
> i'm using formulaarray but i would like to save time and direcly the
[quoted text clipped - 7 lines]
>
> how can i direcly transfer the data to a variable?

Reply to this Message

yaniv.dg@gmail.com - 10 Jul 2006 13:50 GMT

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

> Get rid of the selects
>
[quoted text clipped - 18 lines]
> >
> > how can i direcly transfer the data to a variable?

Reply to this Message

Bob Phillips - 10 Jul 2006 14:11 GMT

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 =" "

Signature

HTH

Bob Phillips

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

> hi bob,
> i dont understand how it can be helpfull for me.
[quoted text clipped - 24 lines]
> > >
> > > how can i direcly transfer the data to a variable?

Reply to this Message

yaniv.dg@gmail.com - 10 Jul 2006 14:39 GMT

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

> You talked about saving time and machine time. Selects are very slow, so
> removing them is faster.
[quoted text clipped - 51 lines]
> > > >
> > > > how can i direcly transfer the data to a variable?

Reply to this Message

Dave Peterson - 10 Jul 2006 15:45 GMT

Maybe...

yourvariable = application.evaluate("yourarrayformula")

> hi bob,
> yes thats true,i wanted to save time and this is one option
[quoted text clipped - 59 lines]
> > > > >
> > > > > how can i direcly transfer the data to a variable?

Signature

Dave Peterson

Reply to this Message

Bob Phillips - 10 Jul 2006 17:00 GMT

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

Signature

HTH

Bob Phillips

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

> Maybe...
>
[quoted text clipped - 63 lines]
> > > > > >
> > > > > > how can i direcly transfer the data to a variable?

Reply to this Message

yaniv.dg@gmail.com - 11 Jul 2006 08:18 GMT

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

> Maybe...
>
[quoted text clipped - 63 lines]
> > > > > >
> > > > > > how can i direcly transfer the data to a variable?

Reply to this Message

Bob Phillips - 11 Jul 2006 08:32 GMT

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.

Signature

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
[quoted text clipped - 67 lines]
> > > > > > >
> > > > > > > how can i direcly transfer the data to a variable?

Reply to this Message