Custom Function behaviour?

[Ron]

Hi all,

I've wrote a custom function, which attempting to see a value and check
what it is, then grab another value and make the [custom function] cell
value the same.

However the function cell doesn't seem to update itself, I have to
double click the cell and click off it to get it to calculate/update.

Function OddsEx() is in cell S3

here's the function..

Function OddsEx()
If Range("q3").Value = "1/1" Then
OddsEx = Range("ab6").Value
ElseIf Range("q3").Value = "21/20" Then
OddsEx = Range("ab7").Value
ElseIf Range("q3").Value = "11/10" Then
OddsEx = Range("ab8").Value
ElseIf Range("q3").Value = "10/9" Then
OddsEx = Range("ab9").Value
ElseIf Range("q3").Value = "6/5" Then
OddsEx = Range("ab10").Value
Else: OddsEx = 99.98
End If
End Function


Cell Q3 has a drop down list of the possibles, and depending on the
selection i.e. "21/20" it goes and gets the decimal value from a list in
cells AB6 thru AB10, the function OddsEx() should be the value
indicated.

It is, but like I say it needs me to double click the cell containing
the custom function and then click off it to update this value.

If any kind person could explain where I'm going wrong and why?

I don't mind ellaborate explanations rather than a quick 'fix' because
I'm teaching myself VBA and want/need to know why I've gone wrong here.

Thanks

Ron

 

[Don Guillett]

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

 

[Ron]

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

Once again you come to my rescue Don, can't thank you enough.

I'll now go off and research 'application.volatile'

Thanks again.

Ron

 

[Tom Ogilvy]

Function OddsEx()

What would make the above function be recalculated? Excel recalculates
functions when a cell on which they depend is changed. It looks only at the
arguments of the function to determine dependencies and your function has
none. If this is appropriate, then you need to make it volatile - a
function that is recalculated each time the sheet is calculated regardless
of dependcies. You do that by doing this

Function OddsEx()
Application.Volatile
' exisiting code
End Function


otherwise, you would modify it to take an argument - something like this
(dependent on Q3)

Function OddsEx(rng as Range)
If rng.Value = "1/1" Then
OddsEx = Range("ab6").Value
ElseIf rng.Value = "21/20" Then
OddsEx = Range("ab7").Value
ElseIf rng.Value = "11/10" Then
OddsEx = Range("ab8").Value
ElseIf rng.Value = "10/9" Then
OddsEx = Range("ab9").Value
ElseIf rng.Value = "6/5" Then
OddsEx = Range("ab10").Value
Else: OddsEx = 99.98
End If
End Function


usage:
=OddsEx(Q3)

 

[Don Guillett]

glad to help

 

[Bernard Liengme]

Hi Ron,
Now that problem is cleared up you might want to read VBA Help to learn
about the Select Case function - so much more elegant than a bunch of
ElseIf's

Bernard

 

[Ron]

Hi Ron,
Now that problem is cleared up you might want to read VBA Help to learn
about the Select Case function - so much more elegant than a bunch of
ElseIf's

Bernard

Hi guys, thanks for the help & tuition.

Bernard, I knew there would be several ways to do this, but at my stage of
learning I was more familiar with the if/then statements.

I did try to get it all in a few lines by assigning variables to the
relevant values, but I got stuck on the fact that the fractions that were
being taken from the cell Q3 were formatted as text and I couldn't get them
to be read as a fraction, and then as a number in the variable.

I could do this by plonking the value of the fraction in a spare cell in
the sheet and then grabbing that for use in the function, but I'm at a loss
as to changing the format of a variable within the function, if this is at
all possible.

The above paragraph probably makes little sense to others who read this,
but I know what I'm on about

Thanks guys,

Ron

 

[Tom Ogilvy]

Dim dblVar as Double
dblVar = cdbl(Range("Q3").Value)