Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function behaviour?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function behaviour?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function behaviour?
"Don Guillett" wrote in news:esSdsqn7EHA.1400
@TK2MSFTNGP11.phx.gbl: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function behaviour?
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) -- Regards, Tom Ogilvy "Ron" wrote in message 0.131... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function behaviour?
glad to help
-- Don Guillett SalesAid Software "Ron" wrote in message 10.201... "Don Guillett" wrote in news:esSdsqn7EHA.1400 @TK2MSFTNGP11.phx.gbl: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function behaviour?
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" wrote in message 0.131... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function behaviour?
"Bernard Liengme" wrote in
: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function behaviour?
Dim dblVar as Double
dblVar = cdbl(Range("Q3").Value) -- Regards, Tom Ogilvy "Ron" wrote in message 10.201... "Bernard Liengme" wrote in : 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Anomolous behaviour of slope function. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Behaviour of VBA ROUND function | Excel Programming | |||
Adding a custom function to the default excel function list | Excel Programming | |||
Key behaviour | Excel Programming |