Using Evaluate() in an Array

[blueiris8]

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.

 

[Norie]

You can use SUMPRODUCT for this calculation - I have no idea what other calculations you have.

By the way, why not do the whole thing in code?

Then you could use VBA's decision structures to decide which calculation to do.

 

[blueiris8]

You can use SUMPRODUCT for this calculation - I have no idea what other calculations you have.

By the way, why not do the whole thing in code?

Then you could use VBA's decision structures to decide which calculation to do.

Because my strength is not in VBA. If I tried to do the whole thing in code, then I would just have asked an even longer question.

 

[blueiris8]

Here is a sample file:
http://dl.dropbox.com/u/101475263/ArraySample.xlsm

 

[Norie]

You weren't passing a string to the function that's why it failed.

Try this.

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

By the way, isn't this method going too make it pretty hard for someone to understand what's going on in the spreadsheet.

 

[blueiris8]

You weren't passing a string to the function that's why it failed.

Try this.

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

By the way, isn't this method going too make it pretty hard for someone to understand what's going on in the spreadsheet.

Still no luck. I was able to switch out the ';' for ',' by transposing the array (vertical to horizontal), but it doesn't help much.

 

[blueiris8]

You weren't passing a string to the function that's why it failed.

Try this.

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

By the way, isn't this method going too make it pretty hard for someone to understand what's going on in the spreadsheet.

Actually, this does work. I had two spreadsheets open and was playing in the wrong spreadsheet. Thank you.

So now, how do I mark this as solved?

 

[blueiris8]

I was also able to simplify this by transposing array:

=evaluS(TRANSPOSE($D6:$D10&VLOOKUP($E2,EForm!$B$2:$H$107,5,FALSE)&E6:E10&VLOOKUP($E2,EForm!$B$2:$H$107,7,FALSE)))

VBA:

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

 

[Norie]

But you've made the formula more complicated.

 

[blueiris8]

But you've made the formula more complicated.

True. I'll actually probably get rid of the transpose, so I don't have use it everytime I use the function. Either way, the "="& in the original isn't necessary. Evaluate() automatically assumes the =.

Thank you for all of your help.

 

[Norie]

I know that and was wondering why you had the "=" in the code, but thought I better not remove it.