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]

How are you creating the dynamic formulas and what are they supposed to calculate?

 

[Andrew Poulsom]

See if this helps you:

Sub Test()
    Const StrIn As String = "100*100+0;50*10+0;0*0+0"
    MsgBox EvaluS(StrIn)
End Sub

Function EvaluS(S As String)
    Dim Arr As Variant
    Dim i As Long
    Arr = Split(S, ";")
    For i = LBound(Arr) To UBound(Arr)
        EvaluS = EvaluS + Evaluate("=" & Arr(i))
    Next i
End Function

 

[Norie]

If you separate each item with a comma this will work.

' example of use =EvaluS("100*100+0","50*10+0","0*0+0")

Function EvaluS(ParamArray S()) As Variant
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

 

[blueiris8]

See if this helps you:

Sub Test()
    Const StrIn As String = "100*100+0;50*10+0;0*0+0"
    MsgBox EvaluS(StrIn)
End Sub

Function EvaluS(S As String)
    Dim Arr As Variant
    Dim i As Long
    Arr = Split(S, ";")
    For i = LBound(Arr) To UBound(Arr)
        EvaluS = EvaluS + Evaluate("=" & Arr(i))
    Next i
End Function

That didn't do anything. I'm still getting Value error.

 

[blueiris8]

If you separate each item with a comma this will work.

' example of use =EvaluS("100*100+0","50*10+0","0*0+0")

Function EvaluS(ParamArray S()) As Variant
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

I'm not putting in the ';', excell is. My formula to build a formula is:
=evaluS(D6:D10&VLOOKUP($E2,EForm!$B$2:$H$107,5,FALSE)&E6:E10&VLOOKUP($E2,EForm!$B$2:$H$107,7,FALSE))

Where the Vlookups return a "*", and "+0".

 

[blueiris8]

How are you creating the dynamic formulas and what are they supposed to calculate?

I'm actually building an expense calculator (for over hundred different expenses), where each expense has a different formula, some use *, some +, some /, etc... So the user selects the expense and then enters the values and the dynamic formula just magically works for them. This works well when they are entering one value at a time, but then you have multiple business units entering values against a single expense, I need to sum up each of the of calculations.

Hope that makes sense...

 

[Norie]

Can you post some data for that formula?

I've tried to mock some up but it's not coming up with {100*100+0;50*10+0;0*0+0}.

By the way what is the purpose of this?

You could use SUMPRODUCT for it.


=SUMPRODUCT({100,50,0},{100, 10, 0})+0

 

[blueiris8]

Can you post some data for that formula?

I've tried to mock some up but it's not coming up with {100*100+0;50*10+0;0*0+0}.

By the way what is the purpose of this?

You could use SUMPRODUCT for it.


=SUMPRODUCT({100,50,0},{100, 10, 0})+0

Can't use Sumproduct, because the formula is not always *.

 

[blueiris8]

I would attach a sample, but I'm not sure how to.