Answered by:
Using range value as formula in For Each loop
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^210 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^210" 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
Answers

Basically, what I want is for a user to be able to enter a string into a cell  for example 2x^210 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
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

Basically, what I want is for a user to be able to enter a string into a cell  for example 2x^210 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

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!