PDA

View Full Version : Solved: Preserving Values in Custom Functions



holshy
12-09-2008, 11:23 AM
I have a fairly large, complicated custom function that I want to allow the user to control calculation of. Essentially I want the function to read like this:

Public Function FuncName(<arg>)

If not bCalculationOn then
x=[Return the last value calculated]
Else
x=[Calculate the Function]
End If

FuncName=x

End Function
So, what I really need is to figure out what to put in that "[Return the last value calculated]" section. BTW, the function is calculated hundreds of times with different arguments each time so I can't just save the last value off to another variable.

I've attached a simple book for an example. When calculation of the custom function is off, I want the function to return the same thing it returned the last time is was calculated for those arguments.
Any ideas?

Bob Phillips
12-09-2008, 11:51 AM
How is the user going to control it? What will they do to determine its state?

holshy
12-09-2008, 01:33 PM
How is the user going to control it? What will they do to determine its state?The variable bCalculationOn will be set somehow. I haven't decided yet. Probably by a simple macro that just switches True to False and vice versa.

Bob Phillips
12-09-2008, 01:39 PM
Then just declare it as a Public variable in that module.

Kenneth Hobs
12-09-2008, 01:46 PM
I am not sure what you need.

I used a Public variable to store the calculation mode in: http://vbaexpress.com/kb/getarticle.php?kb_id=1035 (http://vbaexpress.com/forum/../kb/getarticle.php?kb_id=1035)

holshy
12-09-2008, 01:53 PM
I want the user to be able to keep the custom function from calculating without having to shut off calculation for the entire workbook.

Please see my edit to the original post.

Bob Phillips
12-09-2008, 02:17 PM
Read the replies!

holshy
12-09-2008, 02:23 PM
Read the replies!I did...
You said to declare "it" as a global variable; I have no idea what "it" is or how that's going to help...
Ken linked to an article he wrote that doesn't seem to have anything to do with custom functions...

I'm not trying to be a dick, but I don't feel like you guys even understand my question.
If you don't that's my fault and I'm sorry, but I'd really appreciate it if you were a little more patient with me. : pray2: I'm not trying to troll.
If you do can you please edit the example workbook so it works and repost it for me?

Kenneth Hobs
12-09-2008, 02:38 PM
Your discussion is too abstract for me to see what you mean. You have to give some real world examples using strings, numbers or whatever.

Bob Phillips
12-09-2008, 02:54 PM
Try this and see if it gets us anywhere.


First, ensure cell A1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.
Then set C6 to the value No.

Next, type this formula into cell F4
=IF(($C$6="No")+(F4>0),F4,funcname(E4))
it should show a 0

Copy F4 down to F33.

Toggle Calculation, and F4:F33 should calculate. Toggle again and it should stay the same.

holshy
12-09-2008, 04:06 PM
Thank you! That does what I want. :clap: Is there any way that it could be written entirely into the VB? The custom function that I'm writing might be imported and used by other people, who might not understand this stuff even as well as I do.:doh:

I had an idea which might work, but I don't know if VBA has this capability. Is there a "system variable" (not sure if that's the right term) that you can use in a custom function, which references the cell that is computing the function?
The reason I think this might exist is the "Target" argument for the Method "Worksheet_Change". So, in the same way that you can read the value of the cell that was changed on a worksheet by using "Target.Value", is there a way to read the value of the cell that contains the custom formula?

Paul_Hossler
12-09-2008, 04:38 PM
Couple of ideas to try ---


Public Function FuncName(arg)
Dim x
Dim hold

hold = Application.Caller.Value

If Not bCalculationOn Then
x = hold
Else
x = Rnd(arg)
End If

FuncName = x

End Function

Public Function WhereAmI() As String
Dim sAddr As String
sAddr = Application.Caller.Address

If sAddr = "$I$20" Then
WhereAmI = "Now in I20"
Else
WhereAmI = "NOT in I20"
End If

End Function


Paul

holshy
12-09-2008, 04:51 PM
Application.Caller.Value :bow:

That's perfect! I owe all three of you guys a :beerchug:. Thank you!