How to convert string type array to variant type array - Excel VBA

Question

I am attempting to load formulae stored in a tab-delimited text file into a range in a worksheet. I have used the function Split(Expression As String, Delimiter) to correctly load each line in turn into a 1D array, but have run into problems concerning the array type returned.

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.

'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

Resulting output:

I would like to know if there is a way to convert the array returned from the Split 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.

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

Answer 1

You can use WorksheetFunction.Index (or Application.Index) to convert the array of String to array of Variant/String:

Sub Test()

    StringArr = Split("=1 =2 =3")
    VariantArr = WorksheetFunction.Index(StringArr, 1, 0)
    Range("Sheet1!$A$1:$C$1").Formula = StringArr
    Range("Sheet1!$A$2:$C$2").Formula = VariantArr

End Sub

Here are array types:

And expected output:

Answer 2

I created a text file with a few formulas in there. I used the "|" character instead of ",", or tab delimited. You can use the find & replace function in a text editor to replace the four spaces to "|" if you can.

Then I created this code in VBA

Sub loadFormula()


Dim fso As Object, textFile As Object: Set fso = CreateObject("Scripting.FileSystemObject")
Dim textFileStr As String
Dim textFileArr As Variant
Dim outputArr() As Variant
Dim oneRow As Variant
Dim numRows, numColumns As Long

If Dir("C:\Users\dawsong4\Documents\Reports\WIP\formula.txt") = "" Then
    Exit Sub
Else
    potentialFileToLoad = "C:\Users\dawsong4\Documents\Reports\WIP\formula.txt"
End If

Set textFile = fso.OpenTextFile(potentialFileToLoad, 1)
textFileStr = textFile.ReadAll
textFile.Close

Set textFile = Nothing
Set fso = Nothing

textFileArr = Split(textFileStr, Chr(10))
numRows = UBound(textFileArr)
numColumns = UBound(Split(textFileArr(0), "|"))
ReDim outputArr(numRows, numColumns)

For ii = 0 To (numRows - 1)
    oneRow = Split(textFileArr(ii), "|")
    For jj = 0 To numColumns
    outputArr(ii, jj) = oneRow(jj)

    Next jj
Next ii

Worksheets("Data").Range("A2:P1048576").ClearContents
Worksheets("Data").Range("A2").Resize(numRows + 1, numColumns).Value = outputArr

End Sub

And the result in excel was this:

Hope that helps!

P.S. I imagine if you use " " four spaces (however many a tab delimited text file has), instead of "|" it will work the same