Evaluate() an array

**blueiris8** · 08-29-2012, 02:33 PM

I've created dynamic formulas in excel. Unfortunately, it creates the formula as a string. To get around this I created a function (Evalu) that using the Evaluate() function to give me the answer. This currently works fine.

However, the problem I now is how to apply this to an array. My formula currently evaluates to
=evaluS({"100*100+0";"50*10+0";"0*0+0"})
before it breaks. I'm trying to get to the result of 10500 by adding each array element together. Notice that each element in the array is a string, that I want to get evaluated as a formula.

To do this, I create the evaluS function:

Function EvaluS(S() As String) As String
Dim i As Long
Dim A As Integer
Dim Arr As String

For i = LBound(S) To UBound(S)
Arr = S(i)
A = A + Evaluate(Arr)
Next
EvaluS = A
End Function

Clearly this approach isn't working very well, and since I'm a VBA expert, I was hoping someone could show me the error of my ways.

**Søren Larsen** · 08-29-2012, 02:35 PM

This is a duplicate post; the original post is found here:
http://www.excelforum.com/excel-prog...-an-array.html

**Kyle123** · 08-29-2012, 02:36 PM

Hi,

Please don't post the same question twice, this is a duplication of http://www.excelforum.com/excel-prog...-an-array.html

Also, please try not to cross post, if you do, it is considered polite to include a link to where else you have asked the question: http://www.mrexcel.com/forum/excel-q...ate-array.html

**blueiris8** · 08-29-2012, 03:00 PM

I was able to solve this with some help:

Function EvaluS(S())
Dim i As Long
For i = LBound(S) To UBound(S)
EvaluS = EvaluS + Evaluate(S(i))
Next i
End Function

**Cutter** · 08-29-2012, 08:01 PM

Hello blueiris8, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following. Thanks.

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.