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.

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, May 29 '16 at 22:43
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, May 29 '16 at 22:53
@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, May 29 '16 at 23:29
By the way, a very excellently prepared question, it hit all the marks. — Scott Craner, May 29 '16 at 23:32
Try to add this line `Range("Sheet1!$A$1:$C$1").Formula = Range("Sheet1!$A$1:$C$1").Formula` after this line `Range("Sheet1!$A$1:$C$1").Formula = StringArr` — Fadi, May 29 '16 at 23:57

score 5 · Accepted Answer · answered Jun 15 '16 at 18:40

5

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:

answered Jun 15 '16 at 18:40

omegastripes

12,351
4
45
96

Strange, I guess it's just a different way of setting the array to itself, and for some reason changing the variable type to variant in the process. I have successfully used this in my project - Cheers! – Geoff Davids Jun 17 '16 at 22:40

score 1 · Answer 2 · answered Jun 14 '16 at 12:43

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

Thank you for your answer, it looks like it would work. However I said in my question that I was aware that I could set each cell formula individually within a loop, but was trying to keep it as efficient and neat as possible (and thus wanting to set each row in one go, without looping through the cells) — Geoff Davids, Jun 17 '16 at 22:03
@GeoffDavids I realize this is a couple of years old, but... Tabias's answer does NOT loop through the cells... and is extremely efficient. — Excel Hero, Mar 01 '19 at 22:45
@ExcelHero Ah yes I can see I mis-read Tabias' answer. My bad! — Geoff Davids, Dec 10 '19 at 10:44

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

2 Answers2

Linked

Related