title: Sum variables in VBA
body: <p>I have found myself into an issue that looked so simple and stupid at the beginning but keeps me struggling to solve it for over 24 hours now.</p>
  
  <p>I have a string (bunch of numbers delimited by |) that I want to be converted into array and then sum some of the array keys depending on the case.</p>
  
  <p>The first issue I have found was the Integer length limitation, I couldn't believe when VBA was unable to return a number higher than 32767 (Then I found longs...). After "solving" that I found that when trying to SUM some 0 values it actually increase my grand total and I can't find any explanation for this.</p>
  
  <p>Below you can see what I have now:</p>
  
  <pre><code>Public Function calcTime(TimeType As String)
  Dim jsSting As String
  Dim strSplit As Variant
  Dim tempTime as Double
  
  jsSting = "100|0|10080|400|0|4320|70|0|1440|30|0|2280|10|0|7400|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|300|0|15855|90|0|1721"
  'Split the string by delimiter
  strSplit = Split(jsSting, "|")
  
  
  Select Case UCase(TimeType)
      Case "TOTAL"
          tempTime = WorksheetFunction.Sum(strSplit(2), strSplit(5), strSplit(8), strSplit(11), strSplit(14), strSplit(17), strSplit(20), strSplit(23), strSplit(26), strSplit(29), strSplit(32), strSplit(35), strSplit(38))
      Case "GROUP1" ' Team 1 + Team2
          tempTime = WorksheetFunction.Sum(strSplit(2), strSplit(5), strSplit(8), strSplit(11), strSplit(14))
      Case "GROUP2" ' Team 1 + Team2 + Team3 
          tempTime = WorksheetFunction.Sum(strSplit(2), strSplit(5), strSplit(8), strSplit(11), strSplit(14), strSplit(38))
      Case "GROUP3" ' Team 5
          tempTime = WorksheetFunction.Sum(strSplit(17), strSplit(20), strSplit(23), strSplit(26))
      Case "GROUP4" ' Team 2
          tempTime = strSplit(14)
      Case "GROUP5" ' Team 6
          tempTime = WorksheetFunction.Sum(strSplit(29), strSplit(32), strSplit(35))
  End Select
  
  Return tempTime
  End Function
  </code></pre>
  
  <p>In this example I have tried to use Excel's SUM function in order to get the desired result but It wasn't a success.</p>
  
  <p>Sticking to the TOTAL case. It sums the following keys - values:</p>
  
  <pre><code>jsString(2)  - 10080
  jsString(5)  - 4320
  jsString(8)  - 1440
  jsString(11) - 2280
  jsString(14) - 7400
  jsString(17) - 0
  jsString(20) - 0
  jsString(23) - 0
  jsString(26) - 0
  jsString(29) - 0
  jsString(32) - 0
  jsString(35) - 15855
  jsString(38) - 0
  </code></pre>
  
  <p>This gives a total of 41375, however, when I do the sum in VBA I get 43096 and I can't understand why. If I remove from the SUM the values with 0 it returns the correct 41k value.</p>
  
  <p>Hope this makes sense and the answer is simple (I am seriously thinking that I've missed something when assigning the data type).</p>
  
  <p>Thank you in advance for your help !</p>