none
Using range value as formula in For Each loop RRS feed

  • Question

  • I've searched around but can't seem to find a good way to do this... Basically, what I want is for a user to be able to enter a string into a cell - for example 2x^2-10 and use that as a formula in a For Each loop... What I'm trying to do is for each chart in a worksheet, I want to move the Rotation (x) and the Elevation (Y) along a path using that formula. Using this formula as an example, it would move the first chart at a rotation of 1 and an elevation of -8, then the next chart would move/rotate 2 and -2, the next chart 3 and 8, etc. This code does nothing at all, but I thought it might at least provide a starting point. I cannot figure out how to get the range Value of "2x^2-10" to be a formula and not a string. Any thoughts would be appreciated!

    'Dim strArc As
    Dim x As Double
    'Dim y As Double
    Dim Shape As ChartObject

    For Each Shape In ActiveSheet.ChartObjects
        x = x + 1
        strArc = (Range("Arc").Formula)
        y = strArc  '2 * x ^ 2 - 10
        Shape.Activate
        strChartName = Shape.Name
        
        With ActiveSheet.ChartObjects(strChartName).Chart
            .Rotation = .Rotation + x
            .Elevation = .Elevation + y
            DoEvents
        End With

    Next Shape

    Wednesday, March 2, 2016 7:15 PM

Answers

  • Basically, what I want is for a user to be able to enter a string into a cell - for example 2x^2-10 and use that as a formula in a For Each loop...

    To get a formula as string, modify and evaluate it leads to tons of code, because you have to check syntax errors, etc. etc.

    A simple and easy way is to use named cells and a real formula. As you can see here I've named cell B2 as "x" and C2 as "y":


    It doesn't matter which cells you use, the benefit of named cells is that you can use the names in your code:

    Sub Test()
      Dim CO As ChartObject
    
      For Each CO In ActiveSheet.ChartObjects
        Range("x") = Range("x") + 1
        With CO.Chart
          .Rotation = .Rotation + Range("x")
          .Elevation = .Elevation + Range("y")
        End With
      Next
    End Sub

    Andreas.

     
    • Proposed as answer by André Santo Tuesday, March 8, 2016 1:10 PM
    • Marked as answer by REmmons88 Tuesday, March 8, 2016 5:07 PM
    Thursday, March 3, 2016 11:05 AM

All replies

  • Hi, REmmons88

    According to your description, please correct me if I have any misunderstandings on your question, it seems that you want to convert this "string formula" into a real formula and calculate its value, so I suggest that you could refer to below code:

    Sub ConvertFomular()
         Dim strFomular As String
         x = 10
         strFomular = "2 *" & x & "^ 2 - 10"
         Debug.Print Eval(strFomular)
    End Sub
    
    Function Eval(Ref As String)
        Application.Volatile
        Eval = Evaluate(Ref)
    End Function
    

    • Proposed as answer by André Santo Tuesday, March 8, 2016 1:10 PM
    Thursday, March 3, 2016 7:14 AM
  • Basically, what I want is for a user to be able to enter a string into a cell - for example 2x^2-10 and use that as a formula in a For Each loop...

    To get a formula as string, modify and evaluate it leads to tons of code, because you have to check syntax errors, etc. etc.

    A simple and easy way is to use named cells and a real formula. As you can see here I've named cell B2 as "x" and C2 as "y":


    It doesn't matter which cells you use, the benefit of named cells is that you can use the names in your code:

    Sub Test()
      Dim CO As ChartObject
    
      For Each CO In ActiveSheet.ChartObjects
        Range("x") = Range("x") + 1
        With CO.Chart
          .Rotation = .Rotation + Range("x")
          .Elevation = .Elevation + Range("y")
        End With
      Next
    End Sub

    Andreas.

     
    • Proposed as answer by André Santo Tuesday, March 8, 2016 1:10 PM
    • Marked as answer by REmmons88 Tuesday, March 8, 2016 5:07 PM
    Thursday, March 3, 2016 11:05 AM
  • This worked beautifully, thank you so much!  For the sake of my users, I wanted them to see the formula in the cell they're using rather then the value so I also added a function that would take whatever they type (for example: x*2) and turn it into a formula in a hidden cell and named that cell y.  And with that change, I got exactly what I was looking for!  Again, thanks for your help!
    Tuesday, March 8, 2016 5:11 PM