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

6
votes

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:

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.

2
arraysexcelvba
You can always put them in one by one or with a loop instead, and it will work; try Range("Sheet1!$A$1").Formula = StringArr(1) I do not know why. - Scott Craner
I think it has to do with cells in excel worksheet being of the variant type and by putting the whole array at once the cells are forced into string types so that it matches the array. This stays that way till they are edited again which then puts them back into the variant type. So another method If the string array is large, would be to loop through the string array and put the values into a variant array then assign. Or if the string array is small then as I said above loop through and assign the formula individually. - Scott Craner
@ScottCraner Yeah, that makes sense - looks like I'll be sticking to one of the methods you suggested for now. Do you know if there's a way to change the type of the array in one go? - Geoff Davids
I don't but I am by no means an expert in arrays. - Scott Craner
By the way, a very excellently prepared question, it hit all the marks. - Scott Craner

2 Answers

3
votes

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:

locals

And expected output:

output

1
votes

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.

Forumulas

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:

result

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