You should be able to use Evaluate but you would need to swap the variables in the formula with values.
By the way, it's not a good idea to use Formula for the name as it's a VBA property.
Hi,
I have a function where I wish to define according to content entered into a cell. In its current form, the function is as the follows i.e.
Public Function f1xy(x, y)
f1xy = y - y ^ 2
End Function
Where as i wish for it to be
Public Function f1xy(x, y)
f1xy = ActiveSheet.Range("F3").Formula
End Function
where cell F3 contains y-y^2 as text. Of course this didn't work
I've tried:
Public Function Formula()
Formula = ActiveSheet.Range("F3").Formula
End Function
Public Function f1xy(x, y)
f1xy = Evaluate(Formula)
End Function
This doesn't work either. Any help will be greatly appreciated
You should be able to use Evaluate but you would need to swap the variables in the formula with values.
By the way, it's not a good idea to use Formula for the name as it's a VBA property.
If posting code please use code tags.
You're right about the naming function as formula.. my bad..
I don't get what you mean by swapping variables in the formula with values. I'm trying to build a spreadsheet that can model the various inputs of x and y according the formula input.
If you mean to change range.formula to range.values it's not working =(
Code:Public Function Derp() Derp = ActiveSheet.Range("F3").Values End Function Public Function f1xy(x, y) f1xy = Evaluate(Derp) End Function
That's not what I meant.
What exactly do you want the UDF to return if you pass it this?
y - y ^ 2
If posting code please use code tags.
I am doing a spreadsheet for different types of approximation methods
Using f1xy = y-y² i am able to propagate different values of the function with a y input.
So I am trying to take the sheet 1 step further to be able to enter the desired formula in a cell, I.e. X + Y without having to change the formula in the vba editor
Thanks again for the help
VBA will see y-y ^ 2 as a string.
To evaluate it you would need to give a value for y.
If posting code please use code tags.
Norie's question has to with the function's argument... the formula from the cell is a text string, so the x and y in that text string are characters that have no relationship to any variables named 'x' and 'y' defined in your code. You can use VB's Replace function to substitute the variable's variable for the letter in the formula text, BUT you have to make sure that your substitution is for the correct thing. For example, if the text in the cell were this...
y + Exp(5 * x)
simply replacing x would not work as the x in the function name "Exp" would also get replaced.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See here.
Thanks again for all the response. I understand the part about the strings now and the suggested solution with Replace function.
I need some extra help as to writing the actual code?
I'm getting a compile error with this, and I'm not sure if this code will actually replace "x" with the x as a variable.Code:Public Function f1xy(x, y) f1xy = ActiveSheet.Range("F3").Formula Replace(ActiveSheet.Range("F3").Formula,"x",x) End Function
Figured it out =) the function worked with
Code:Public Function f1xy(x, y) f1xy = ActiveSheet.Range("F3").Formula f1xy = Replace(f1xy, "x", x) f1xy = Replace(f1xy, "y", y) f1xy = Evaluate(f1xy) End Function
Like this thread? Share it with others