Results 1 to 9 of 9

Convert Cell Content into VBA Code

This is a discussion on Convert Cell Content into VBA Code within the Excel Questions forums, part of the Question Forums category; Hi, I have a function where I wish to define according to content entered into a cell. In its current ...

  1. #1
    New Member
    Join Date
    Sep 2013
    Posts
    8

    Default Convert Cell Content into VBA Code

    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

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,269

    Default Re: Convert Cell Content into VBA Code

    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.

  3. #3
    New Member
    Join Date
    Sep 2013
    Posts
    8

    Default Re: Convert Cell Content into VBA Code

    Quote Originally Posted by Norie View Post
    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

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,269

    Default Re: Convert Cell Content into VBA Code

    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.

  5. #5
    New Member
    Join Date
    Sep 2013
    Posts
    8

    Default Re: Convert Cell Content into VBA Code

    Quote Originally Posted by Norie View Post
    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

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,269

    Default Re: Convert Cell Content into VBA Code

    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.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    17,206

    Default Re: Convert Cell Content into VBA Code

    Quote Originally Posted by alienczf View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  8. #8
    New Member
    Join Date
    Sep 2013
    Posts
    8

    Default Re: Convert Cell Content into VBA Code

    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.

  9. #9
    New Member
    Join Date
    Sep 2013
    Posts
    8

    Default Re: Convert Cell Content into VBA Code

    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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com