Transform a worksheet to an array containing only strings

Question

I need to extract the data from an excel worksheet to an array that will be used in an application that uses VBScript as scripting language (Quick Test Professional). We can use the following code for that:

' ws must be an object of type Worksheet
Public Function GetArrayFromWorksheet(byref ws)
    GetArrayFromWorksheet = ws.UsedRange.Value
End Function

myArray = GetArrayFromWorksheet(myWorksheet)
MsgBox "The value of cell C2 = " & myArray(2, 3)

All nice and well, but unfortunately the array that gets returned does not only contain the literal text strings, but also primitives of type date, integer, double etc. It happened multiple times that that data got transformed.

[edit] Example: when entering =NOW() in a cell and set the cell formatting to hh:mm makes the displayed value 17:45, the above method retuns a variable of type double and a value like 41194.7400990741

The following solution worked better: I can get the literal text from a cell by using the .Text property, but they only work on one cell and not on a range of cells. I cannot do this at once for an array as I could with the .Value property, so I have to fill the array one cell at a time:

Public Function GetArrayFromWorksheet_2(byref ws)
    Dim range, myArr(), row, col
    Set range = ws.UsedRange

    ' build a new array with the row / column count as upperbound
    ReDim myArr(range.rows.count, range.columns.count)

    For row = 1 to range.rows.count
        For col = 1 to range.columns.count
            myArr(row, col) = range.cells(row, col).text
        Next
    Next

    GetArrayFromWorksheet_2 = myArr
End Function

But ouch... a nested for loop. And yes, on big worksheets there is a significant performance drop noticable.
Does somebody know a better way to do this?

Answer 1

As we covered in the comments, in order to avoid the issue you will need to loop through the array at some point. However, I am posting this because it may give you a significant speed boost depending on the type of data on your worksheet. With 200 cells half being numeric, this was about 38% faster. With 600 cells with the same ratio the improvement was 41%.

By looping through the array itself, and only retrieving the .Text for values interpreted as doubles (numeric), you can see speed improvement if there is a significant amount of non-double data. This will not check .Text for cells with Text, dates formatted as dates, or blank cells.

Public Function GetArrayFromWorksheet_3(ByRef ws)

    Dim range, myArr, row, col
    Set range = ws.UsedRange

    'Copy the values of the range to temporary array
    myArr = range
    'Confirm that an array was returned.
    'Value will not be an array if the used range is only 1 cells
    If IsArray(myArr) Then
        For row = 1 To range.Rows.Count
            For col = 1 To range.Columns.Count
                'Make sure array value is not empty and is numeric
                If Not IsEmpty(myArr(row, col)) And _
                            IsNumeric(myArr(row, col)) Then
                    'Replace numeric value with a string of the text.
                    myArr(row, col) = range.Cells(row, col).Text
                End If
            Next
        Next
    Else
        'Change myArr into an array so you still return an array.
        Dim tempArr(1 To 1, 1 To 1)
        tempArr(1, 1) = myArr
        myArr = tempArr
    End If

    GetArrayFromWorksheet_3 = myArr
End Function

Answer 2

• Copy your worksheet into a new worksheet.
• Copy Paste values to remove formulas
• Do a text to columns for each column, turning each column into Text
• Load your array as you were initially doing
• Delete the new worksheet

Answer 3

You cant do this quickly and easily without looping through the worksheet. If you use the technique above with 2 lines of code it must a variant type array.

I've included a real example from my code that does it in 6 lines because I like to A) work with the worksheet object and B) keep a variable handy with the original last row.

Dim wsKeyword As Worksheet
Set wsKeyword = Sheets("Keywords")

Dim iLastKeywordRow As Long
iLastKeywordRow = wsKeyword.Range("A" & wsKeyword.Rows.Count).End(xlUp).Row

Dim strKeywordsArray As Variant
strKeywordsArray = wsKeyword.Range("A1:N" & iLastKeywordRow).Value

Note your array MUST be a variant to be used this way.

The reason that Variants work like this is that when you create an array of variants, each 'cell' in the array is set to a variant type. Each cell then get's it's variant type set to whatever kind of value is assigned to it. So a variant being assigned a string gets set to variant.string and can now only be used as a string. In your original example it looks like you had time values which were kind of stored as variant.time instead of variant.string.

There are two ways you can approach your original problem 1) loop through and do the process with more control, like the double nested for loop. explained in another answer which gives you complete control 2) store all the data in the array as is and then either re-format it into a second array, or format it as desired text as you use it (both should be faster)