refreshing VB function after changing value

  • Thread starter Marcin Zmyslowski
  • Start date
M

Marcin Zmyslowski

I have created a VB function which look like this:
public function function_1 (parameter1 as string, parameter2 as integer) as
string

my form look like this:
A B
1 5 =function_1("03.2004";row())
2 10 =function_1("03.2004";row())

I noticed that when I change the values from cells: A1 and A2, the function
(function_1)
isn`t updated automatically, that`s why this value returned by this
function, is now older/previous
one. How to refresh the value returned by this function automatically after
changing cells A1, A2??

Any help appreciated
Thankx
Best regards
Marcin
 
F

Frank Kabel

Hi
try adding the line
application.volatile
as first line in your function

Note: I'm not so sure why your formula result should change if A1 is
changed (as A1 is not a parameter for your function). You may consider
instead of making your formula volatile to add a range (e.g. A1 in your
example) as parameter for your function. This way Excel would 'know'
that it has to recalculate your function if A1 changes
 
M

Marcin Zmyslowski

Uzytkownik "Frank Kabel said:
Hi
try adding the line
application.volatile
as first line in your function

Note: I'm not so sure why your formula result should change if A1 is
changed (as A1 is not a parameter for your function). You may consider
instead of making your formula volatile to add a range (e.g. A1 in your
example) as parameter for your function. This way Excel would 'know'
that it has to recalculate your function if A1 changes
Click to expand...

My function include among other things such a line of code:

do while counter<31
field=worksheets(parameter1).cells(paramater2,6+counter)

'analisis of field value - the long analsis
loop

(I am writing just from my mind, I could make a mistake with this
description
but the meaning is the same)

I want to sum the values of cells from A7 to AB7, but it is not usual
summing.

I wrote only the simplified example of that what I want, but your answer
with
be useful.

Thankx again
Marcin
 
C

Charles Williams

Hi Marcin,

the reason for trying to avoid using application.Volatile is that it slows
down recalculation of your spreadsheet because it forces excel to always
recalculate your function even when none of the precedents has changed.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
M

Marcin Zmyslowski

U¿ytkownik "Charles Williams said:
Hi Marcin,

the reason for trying to avoid using application.Volatile is that it slows
down recalculation of your spreadsheet because it forces excel to always
recalculate your function even when none of the precedents has changed.
Click to expand...


Yes I know, but I don`t have so many calculations in my spreadsheet, but
I have an another question about this Application.Volatile.
I will have 12 spreadsheets named as from 01.2004. till 12.2004 as
a month numbers. I will have the same functions used in each of these
12 spreadsheets. Can you tell me if I change the value in cell A1 of
spreadsheet for example: "01.2004", will these calculations of these rest
(11)
spreadsheets will be made even if I didn`t change the cells values of these
rest
spreadsheets? Because, if it is, it really make the calculations slowing
down.

Marcin
 
Top