Convert Cell Content into VBA Code

alienczf

New Member
Joined
Sep 16, 2013
Messages
8
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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.

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
 
Upvote 0
That's not what I meant.

What exactly do you want the UDF to return if you pass it this?

y - y ^ 2
 
Upvote 0
That's not what I meant.

What exactly do you want the UDF to return if you pass it this?

y - y ^ 2

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
 
Upvote 0
VBA will see y-y ^ 2 as a string.

To evaluate it you would need to give a value for y.
 
Upvote 0
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
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.
 
Upvote 0
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?

Code:
Public Function f1xy(x, y)
    
    
    
    f1xy = ActiveSheet.Range("F3").Formula
    Replace(ActiveSheet.Range("F3").Formula,"x",x)
    
    
    
End Function

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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top