title: Replace parts of string formula with relevant values
body: <p>I have a table (<code>tbl_Composites</code>) which contains formulas that are used to calculate performance scores. e.g. </p>
  
  <pre><code>     KPI       |           FORMULA                | 
  --------------------------------------------------
  Productivity   |  [ItemsCompleted]/[LoggedInTime] | 
  SalesConversion|  [Sales]+[Leads]/[TotalCalls]    | 
  </code></pre>
  
  <p>There is another table (<code>tbl_BaseData</code>) which has a column which contains these metrics and the associated value for each person</p>
  
  <pre><code> SCOREDATE | USERID  |    METRIC       | VALUE | 
  ------------------------------------------------
  01/03/2017 | 20511   | ItemsCompleted  |  50   | 
  01/03/2017 | 20511   | LoggedInTime    |  320  | 
  01/03/2017 | 20630   | Sales           |  8    | 
  01/03/2017 | 20630   | Leads           |  3    | 
  01/03/2017 | 20630   | TotalCalls      |  25   | 
  </code></pre>
  
  <p>I am trying to write a VBA function that will take the parameters of the Date, UserID and the KPI and then get the relevant metric values for that KPI formula and calculate the result.</p>
  
  <p>I have something I've managed to put together but it's not very efficient and it doesn't work if there is no record for one of the metrics (e.g someone took calls but made no sales).</p>
  
  <pre class="lang-vba prettyprint-override"><code>Function CalulateFormula(strFormula As String, userID As String, scoreDate As Date) As String
  Dim Metric As String, valueList As String
  Dim i As Integer
  
  For i = 1 To Len(strFormula)
      If Mid(strFormula, i, 1) = "[" Then
          Metric = Mid(strFormula, i + 1, InStr(i + 1, strFormula, "]") - i - 1)
          valueList = valueList &amp; "'" &amp; Metric &amp; "', "
          CalulateFormula = CalulateFormula &amp; Metric
          i = InStr(i + 1, strFormula, "]")
      Else
          CalulateFormula = CalulateFormula &amp; Mid(strFormula, i, 1)
      End If
  Next i
  
  valueList = Left(valueList, Len(valueList) - 2)
  
  CalulateFormula = EvaluateSQL(CalulateFormula, valueList, userID, scoreDate)
  
  End Function
  </code></pre>
  
  <p>This will build the formula string and populate a list of the metrics that it will need to find. It then calls another function as below, which runs some SQL to get the metric values and evaluate the formula.</p>
  
  <pre class="lang-vba prettyprint-override"><code>Function EvaluateSQL(CalulateFormula As String, valueList As String, userID     As String, scoreDate As Date) As String
  Dim strSQL As String
  Dim rs As DAO.Recordset
  
      strSQL = "SELECT Metric, metricValue " &amp; _
           "FROM tbl_BaseData " &amp; _
           "WHERE metric in (" &amp; valueList &amp; ") AND userID = '" &amp; userID &amp; "' AND     scoreDate = #" &amp; Format(scoreDate, "mm/dd/yyyy") &amp; "# "
  
  Set rs = CurrentDb.OpenRecordset(strSQL)
  
  Do Until rs.EOF
      CalulateFormula = Replace(CalulateFormula, rs!Metric, rs!metricValue)
  rs.MoveNext
  Loop
  EvaluateSQL = Eval(CalulateFormula)
  End Function
  </code></pre>
  
  <p>This seems like quite a roundabout way of doing things and I'm wondering if I need to approach the problem from a completely different angle. Could anyone advise me if there is a simpler / more efficient way that will also be able to handle null / missing values?</p>