Auto-refreshing results of module-calls

[Sebastian Stormbo]

Hey all, I have for you today a tricky question which perhaps is harder to
pose than answer.
First of all, parden me if I am miss-using any of excel terms; my excel is
in Norwegian, and hence translating is not always correct.

I have a workbook with 4 different sheets. In one of theese sheets the user
is supposed to plot in parameters - approx. 100 different types - and several
charts. After plotting in these parameters, the charts are meant to be
updating themselves while one alters and adapts the parameters in order to
get the desired charts.

Unfortunately, when one alters the parameters that are the arguements for a
function I've defined my self.

Like this:
* You alter the value of Cell B5
* Cell G5 is =Stoppe(A5,B5,1), where "Stoppe is a function I've written in VBA

The Value of G5 is contrary to my desire not updated!

This is how I've defined my function:
Under Modules of the VBAProject of my workbook, in Moduel 1, I've written

Option Explicit
Function Stoppe(Pos As Double, Tog As Integer) As Double

Dim Ja As Boolean
Dim i As Integer

For i = 0 To 7
If Pos = Ark4.Cells(2 * i + 6, (Tog - 1) * 3 + 2) Then
Stoppe = Ark4.Cells(2 * i + 7, (Tog - 1) * 3 + 2)
Ja = True
End If
Next i

If Ja Then
Else
Stoppe = 0
End If
End Function
..

Does anyone know why the cell calling on this function does not update
automatically when it's parameters/arguments are altered?

Thanks to anyone with a piece of advice!

Sebastian

 

[Bernie Deitrick]

Try:

Set your calc mode to automatic Tools / Options / Calculation tab, check "Automatic"
Use Application.Volatile as the first line after your declarations.
Wrap your function call inside a volatile Excel Function, like

=IF(TODAY()=INT(NOW()),stoppe(A1,B1),"")

Of course, you need to translate the first and third into Norwegian....

HTH,
Bernie
MS Excel MVP

 

[Sebastian Stormbo]

thanks, Bernie. I've tried all your steps, but It didnt fix the problem --
they're still not auto-updating. Additionally, it seems to extend the
computing process.

Sebastian