title: excel VBA populate listobject with array values depends on datatype of array variant/string
body: <p>I have to manipulate data in Listobjects of Excel and instead of populating columns with loops I am trying to paste the values of an array into a listobject position in one go to speed up things (accessing the list object and cells updating one by one is very slow).</p>
  
  <p>I posted some weeks ago this question in SO:
  <a href="https://stackoverflow.com/questions/57097268/pass-listobject-to-array-type-variable-string-error">Pass ListObject to array. type variable String error</a></p>
  
  <p>Nevertheless I am observing the following:</p>
  
  <pre><code>'variables
  Dim mylistObject As ListObject
      Set mylistObject = ThisWorkbook.Sheets("training").ListObjects(1)
  Dim i As Integer
  
  ' the two arrays to be pasted are defined differently
  Dim theArray() As Variant
      theArray = mylistObject.ListColumns(1).DataBodyRange.value
      ' where column 1 is populated with numbers.
  Dim otherArray() As String
      otherArray = Split("1,2,3,4,5,6,7,8,9", ",")
  
  'lets paste both arrays with insertion point a particular cell (item 5)
  
   ' a) if the two ranges are the same size, i.e. one column copied I can do:
  mylistObject.ListColumns(2).DataBodyRange.value = theArray
  
  ' b) lets paste the two arrays in from item 5 on using resize (note ubound of thearray is 9, i.e. base 1)
  mylistObject.ListColumns(3).DataBodyRange.item(5).Resize(UBound(theArray), 1).value = theArray
  
  ' c) lets paste the otherarray in column 4 (note ubound of thearray is 8, i.e. base 0)
  mylistObject.ListColumns(4).DataBodyRange.item(5).Resize(UBound(otherArray) + 1, 1).value = otherArray
  
  'the classical but slow way to paste value by value is:
  For i = LBound(otherArray) To UBound(otherArray)
      mylistObject.ListColumns(5).DataBodyRange.item(4 + i).value = otherArray(i)
  Next i
  </code></pre>
  
  <p>I get this result (see picture) which is really strange. Why pasting the second array (otherarray) does not work when the array is of the type string.</p>
  
  <p>if you are wondering why I simply dont change the otherarray to variant is because then I can not generate otherarray using split() method.</p>
  
  <p><a href="https://i.stack.imgur.com/IgCwi.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IgCwi.jpg" alt="enter image description here"></a></p>