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

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" <(E-Mail Removed)> wrote in message
news:F7111551-0D45-4C2B-AF8F-(E-Mail Removed)...
> 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
>
>

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

"Bernie Deitrick" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:F7111551-0D45-4C2B-AF8F-(E-Mail Removed)...
> > 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
> >
> >
>
>
>