Functions - Automatic Update

S

serdar

Hi,

I have a function,

myfunction( )

which searchs for a string in a specific worksheet

W

and returns a cell's value

v

The problem is, when v is changed the cell that uses myfunction( ) is not
updated automatically.

I know this is the way functions work and maybe I'll need a different
approach. Hoping I'm clear enough, what to do?

(I may try forcing the worksheet to recalculate (how to do that btw?) but
looking for a better way.)

Thanks.
 
H

Haldun Alay

Use

Application.Volatile True

as a first line in your function.

Function MyFunction()
Application.Volatile True
....your code

End Function
--
Haldun Alay
"serdar" <[email protected]>, haber iletisinde þunlarý yazdý:[email protected]...
Hi,

I have a function,

myfunction( )

which searchs for a string in a specific worksheet

W

and returns a cell's value

v

The problem is, when v is changed the cell that uses myfunction( ) is not
updated automatically.

I know this is the way functions work and maybe I'll need a different
approach. Hoping I'm clear enough, what to do?

(I may try forcing the worksheet to recalculate (how to do that btw?) but
looking for a better way.)

Thanks.
 
S

serdar

Excellent!

(Teþekkür ederim :) )

"Haldun Alay" <[email protected]>, haber iletisinde þunlarý
yazdý:[email protected]...
Use

Application.Volatile True

as a first line in your function.

Function MyFunction()
Application.Volatile True
....your code

End Function
--
Haldun Alay
"serdar" <[email protected]>, haber iletisinde þunlarý
yazdý:[email protected]...
Hi,

I have a function,

myfunction( )

which searchs for a string in a specific worksheet

W

and returns a cell's value

v

The problem is, when v is changed the cell that uses myfunction( ) is not
updated automatically.

I know this is the way functions work and maybe I'll need a different
approach. Hoping I'm clear enough, what to do?

(I may try forcing the worksheet to recalculate (how to do that btw?) but
looking for a better way.)

Thanks.
 
D

Dave Peterson

Just to add ...

If you use application.volatile in your code, then your formula will recalculate
when ever excel recalculates.

It might be a better idea to be more specific in your function.

Pass it the range that could change.

function myfunction(rng1 as range, rng2 as range) as variant
....
end function

Now excel knows to reevaluate your function when something in rng1 or rng2
changes.

===
If you decide to use the application.volatile approach, remember to recalculate
before you trust the function result--it could be waiting for the next
calculation and may be incorrect for the current data.
 
S

serdar

That's even better, thanks.

haber iletisinde sunlari said:
Just to add ...

If you use application.volatile in your code, then your formula will
recalculate
when ever excel recalculates.

It might be a better idea to be more specific in your function.

Pass it the range that could change.

function myfunction(rng1 as range, rng2 as range) as variant
...
end function

Now excel knows to reevaluate your function when something in rng1 or rng2
changes.

===
If you decide to use the application.volatile approach, remember to
recalculate
before you trust the function result--it could be waiting for the next
calculation and may be incorrect for the current data.
Click to expand...
 
S

serdar

I used a different approach, let me know what do you think.

Since the constants are in a seperate worksheet (W) and nearly all
worksheets use the data in W;

Private Sub Worksheet_Change(ByVal Target As Range)

Application.CalculateFull

End Sub
 
D

Dave Peterson

I'd rather pass it the info the function needs.

If cells change as the result of formulas, then the worksheet_change event won't
fire.

And I would assume that most changes made to the worksheet really wouldn't cause
your function to recalculate.

And sometimes running macros (even event macros) will kill the edit|undo stack.
(This didn't happen in a small test of your code in xl2003 for me, though.)
 
S

serdar

You're right, and passing the range is a brilliant way to solve my problem.
My only concern is that the function will be used by a novice user and,

myfunction(string1 as string, range1 as range)

=myfunction(A1;'worksheet_name'!D:D)

would not be clean enough for him to use, than simply

=myfunction(A1)


It is even hard for him to understand the purpose of the semicolon or the
option to use a string instead of a cell reference as

=myfunction('value of A1 here')

Anyway, this is the level of the user, but as I type this, I decided to use
the straight forward way you suggested. Finally, yes the undo stack was a
problem for me in the past as well, good point again!

I really appreciate your help,

Thanks.
 
D

Dave Peterson

If you changed any cell in 'worksheet_name'!d:d, then you'd want your function
to recalc. It sounds to me to be more of a training issue.

Personally, I think you can either treat your users as people who are unwilling
to learn (and they won't!) or you could tell them what's expected.

You may be pleasantly surprised when they learn something from your function and
can implement it in another function (like =vlookup()).

Don't sell them short.
 
Top