VBA assign Formula to Variable then use the Variable to evaluate the Formula

Question

I require some help please. Using Excel 2007. I'm not even sure this can be done. I need to maintain efficiency and speed else the application will not be used.

I have a 'IF' statement to test for a criteria. depending on the results want to assign one of several possible Formulas to a Variable for later use. I then need to have the Variable to Evaluate the Formula so that the result can be stored in a second variable to build an array.

Sample Code:

If (varEmployeeName = "(All)" Or varEmployeeName = "(Multiple Items)") Then
     **varArrayFormula = "Application.WorksheetFunction.SumIfs(wsSumRange, wsLookupRange, wsLookupValue)"**
Else
     **varArrayFormula = "Application.WorksheetFunction.SumIfs(wsSumRange, wsLookupRange, wsLookupValue, wsLookupRange2, varEmployeeName)"**

Then Later on this code will execute:

ReDim varKPIArray(0 To varLastRow) ' creates the array size

For varCol = 10 To 13
    For varRow = 8 To varLastRow
        varDailyKPIHeading = ws.Cells(7, varCol).Value
        Select Case varDailyKPIHeading
            Case "PRODUCTIVITY"
                Set wsSumRange = ThisWorkbook.Worksheets("DailyProductivity").Range("$G:$G")
                Set wsLookupRange = ThisWorkbook.Worksheets("DailyProductivity").Range("$F:$F")
                wsLookupValue = ws.Range("B" & varRow).Value
                **varSumifValue = Application.Evaluate(varArrayFormula)**
                varKPIArray(varRow - 8) = varSumifValue

            Case "HOURS"

The program will then loop through several columns and rows hence why the variables are needed. I have 2 options at this point: 1) is to build an array and then I can paste the array results in the appropriate cells with a second loop. or 2) As I am loop through each cell I Populate the formula results to the cell

Thank you everyone for you help, suggestions and ideas

Answer 1

Use this as your formula strings:

"SumIfs(" & wsSumRange.Address(0,0) & "," & wsLookupRange.Address(0,0) & "," &  wsLookupValue & ")"

And:

"SumIfs(" & wsSumRange.Address(0,0) & "," & wsLookupRange.Address(0,0) & "," &  wsLookupValue & "," &  wsLookupRange2.Address(0,0) & "," &  varEmployeeName & ")"

Answer 2

Thanks to all who gave their suggestions and Help. Ultimately i was not able to get anything to work and ended up designing and rewriting the code. It was hoped that there was a simply solution of applying a Formula with variables to a variable so that one did not have to keep retyping the formula and could just reference the variable that contained the formula. I will post my solution shortly once i have the code finalized and debugged.