How to convert string type array to variant type array - Excel VBA
<p>I am attempting to load formulae stored in a tab-delimited text file into a range in a worksheet. I have used the function <code>Split(Expression As String, Delimiter)</code> to correctly load each line in turn into a 1D array, but have run into problems concerning the array type returned.</p>

<p>The Split function only returns string type arrays, and I need a variant type array to set the range to. This is because setting the formulae of cells using a string type array causes the cell values to be set to the raw text, even if the strings begin with an equals sign.</p>

<pre><code>'Example code to demonstrate the problem:
Sub Tester()
    Dim StringArr(1 To 3) As String
    StringArr(1) = "= 1"
    StringArr(2) = "= 2"
    StringArr(3) = "= 3"
    Range("Sheet1!$A$1:$C$1").Formula = StringArr
    'Cells display raw string until edited manually

    Dim VariantArr(1 To 3) As Variant
    VariantArr(1) = "= 1"
    VariantArr(2) = "= 2"
    VariantArr(3) = "= 3"
    Range("Sheet1!$A$2:$C$2").Formula = VariantArr
    'Cells display formula result correctly
End Sub
</code></pre>

<p>Resulting output:</p>

<p><a href="https://i.stack.imgur.com/cehdm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cehdm.png" alt="output"></a></p>

<p>I would like to know if there is a way to convert the array returned from the <code>Split</code> function to a variant type array, preferably without loops. I am aware that I could set each cell formula individually within a loop, but I am trying to keep it as efficient and neat as possible.</p>

<p>I am using Microsoft Excel for Mac 2011, Version 14.5.5. VBA is driving me up the wall.</p>