Forcing a variant to be another type

Question

I have the following code:

Dim lRow As Long
Dim c As Variant

lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
c = Application.Transpose(ws.Range("A2:A" & lRow).Value2)

As long as lRow is > 2 then c becomes a Variant/Variant(1 to x) i.e. an array of Variant/String with values from column A - this is what I need!

However, sometimes the lRow is 2 - this means that c becomes just a string (instead of an array with one entry) - this messes up code further down the sub.

Is there a way I can use Application.Transpose(ws.Range("A2:A" & lRow).Value2) to produce an actual array instead of a Variant? Or somehow force c to always be the array?

Or do I just need to do if checks on the type and build more logic into the whole thing?

I tried Dim c() As String but that's not what Transpose produces...

Cristian Buse · Accepted Answer · 2020-10-22T10:17:14.733

2

You should read the range first into a Range variable and then transpose only if it has at least 2 cells:

Dim lRow As Long
Dim c() As Variant
Dim rng As Range

lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("A2:A" & lRow)
If rng.Count > 1 Then
    c = Application.Transpose(rng.Value2)
Else
    ReDim c(1 To 1, 1 To 1)
    c(1, 1) = rng.Value2
End If

Alternatively, you could use a separate function to get the values from a range into an array:

Private Function RangeToArray(ByVal rng As Range) As Variant()
    If rng Is Nothing Then Err.Raise 91, "RangeToArray", "Range not set"
    If rng.Areas.Count > 1 Then Err.Raise 5, "RangeToArray", "Multi-area range"
    
    If rng.Count > 1 Then
        RangeToArray = rng.Value2
    Else
        Dim arr(1 To 1, 1 To 1) As Variant
        arr(1, 1) = rng.Value2
        RangeToArray = arr
    End If
End Function

But note that when applying Transpose to a 2-dimensional array of 1 value it actually converts it to a 1-dimensional array:

Dim lRow As Long
Dim c() As Variant

lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
c = Application.Transpose(RangeToArray(ws.Range("A2:A" & lRow))) 'c is 1-D if range has only 1 cell

So, the first choice is probably better.

Finally, you could use your own version of Transpose. See my array repository

EDIT 1

Use the following method if you only need 1D Arrays. It works with rows and columns.

Private Function OneDRangeTo1DArray(ByVal rng As Range) As Variant()
    Const methodName As String = "OneDRangeTo1DArray"
    
    If rng Is Nothing Then
        Err.Raise 91, methodName, "Range not set"
    ElseIf rng.Areas.Count > 1 Then
        Err.Raise 5, methodName, "Multi-area range"
    ElseIf rng.Rows.Count > 1 And rng.Columns.Count > 1 Then
        Err.Raise 5, methodName, "Expected 1-row or 1-column range"
    End If
    
    Dim arr() As Variant
    
    If rng.Count = 1 Then
        ReDim arr(1 To 1)
        arr(1) = rng.Value2
    Else
        Dim v As Variant
        Dim i As Long
        
        ReDim arr(1 To rng.Count)
        i = 0
        For Each v In rng.Value2
            i = i + 1
            arr(i) = v
        Next v
    End If
    OneDRangeTo1DArray = arr
End Function

edited Oct 22 '20 at 10:17

answered Oct 13 '20 at 09:17

Cristian Buse

4,020
1
13
34

I think my answer is a little better, but your array repository is awesome! Upvoted! I should post some stuff on GitHub. Right now I just drop stuff every now and then into my pasteBin account and it's a mess: https://pastebin.com/u/PGSystemTester – pgSystemTester Oct 13 '20 at 19:23
@PGSystemTester You are looping through cells one by one and reading each value. That is really slow. Reading the whole range into an array in a single statement is super fast. The author of the question was already reading the entire range in one go. The .Value2 method returns an array if the range has multiple cells or a single value if the range has 1 cell. I basically covered the special case without losing the speed. – Cristian Buse Oct 13 '20 at 21:20
I don't think your code would qualify as "super fast" compared to mine. I think you're confusing *modifying* each cell which would absolutely slow the procedure to a crawl. But pulling the value by cell doesn't cost much. To illustrate, I ran a version of both of our code against a full column of cells. Both procedures took around a second. I think yours might have finished a couple milliseconds ahead, but over a million rows, but not enough to label "Super Slow". Give it a try and if you disagree, hit me back. I like testing different concepts: https://pastebin.com/EQbvDf14 – pgSystemTester Oct 13 '20 at 23:44
@PGSytemTester I have filled the A column with random data (values) and for a million rows my procedure takes 139ms while yours takes 5537ms. See [chart](https://i.stack.imgur.com/r0ssn.png). I have used [CTimer](https://stackoverflow.com/a/198702/8488913) for timing. The difference is 40x. That is significant. Also you are redimming your array without knowing if ```Option Base``` is 0 or 1. You should do ```ReDim c(0 To lRow - 2)``` to be specific. Also you are assuming that all values are ```String```. It's very subjective to say that your code is *a little better*. – Cristian Buse Oct 14 '20 at 09:00
You make a formidable argument and I truly enjoy seeing you shrewd my abstract language with sound logic (I hate when people make abstract statements to me!!). I certainly do agree that 40x faster IS significant! However, when I tried to simulate the difference at that level, I only saw a delta of about 3x as fast (which one could still argue is *significant*). Would you mind sharing your code for your tests? I suspect you're correct and if I can confirm even 20x I'll tip my hat and go review some of your other answers and find a few that I like and upvote. Thanks. – pgSystemTester Oct 20 '20 at 19:52
@PGSystemTester I am glad and appreciate that you are open to test ideas. Obviously, I have excluded the ```lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row``` from testing as we are only interested in the Read speed. See code [here](https://gist.github.com/cristianbuse/9ea5f0ddb92870184eadf2f00ca8085a). If you want better precision you need to download the CTimer class I've mentioned before and run the ```TimeMethods``` method. If you don't care about sub-millisecond precision then use ```TimeMethods2``` which uses the built-in Timer function. Removing ```Transpose``` is even faster. Cheers! – Cristian Buse Oct 21 '20 at 08:54
Thanks Christian, I ended up using a slight variation of your `rng.count` check - I added my answer just for interest. – jamheadart Oct 22 '20 at 09:59

pgSystemTester · Answer 2 · 2020-10-13T22:52:34.700

1

A Variant IS a TYPE (similar to String, Long, Integer, Byte, Double). However, I am guessing you are trying to force a VARIABLE to be a DIFFERENT type (string?) and as part of an ARRAY?

If so, I think this should work for you. It creates an array starting at 0 with a maximum of the last row less two cells. If you wanted to transpose it, or make it multidimensional, just add another layer.

lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row


ReDim c(lRow - 2) As String

'if you need two dimensioanl you could experiment with this:
'ReDim c(lRow - 2, 0) As String

Dim i As Long

For i = 0 To (lRow - 2)

    c(i) = ws.Range("A2").Offset(i, 0).Value2
    'OR if two dimenssional
    'C(i,0) =


Next i

edited Oct 13 '20 at 22:52

answered Oct 13 '20 at 09:08

pgSystemTester

8,979
2
23
49

Yeah I have edited my question to "another" type instead of "a" type. I'm just after the one-d array so I think your answer looks good, I'll try implementing it now. – jamheadart Oct 13 '20 at 09:52
@jamheadart that's great but you're a SO veteran -- please `accept` 1 of these answers ! Obviously, Christian Buse and I are having a friendly nerd battle on the quality of our answers (I'm losing). I still would offer the flimsy/abstract/unprovable argument that my `For Each` cell answer is slightly easier for new coders to comprehend. There's no doubt his runs faster but I'd counter that mine is *fast enough* given that it handled over a million records in less than a second. He says his code runs 40x faster, which is probably true, and indeed much faster than mine..... so...?!? ‍♂️ – pgSystemTester Oct 20 '20 at 20:24
I went for a slight variation of Christian's - basically just wrote a function that checked for the range count and then used set single value or transpose - transpose is definitely quicker than looping although I'm not really dealing with large sets anyway so either would have done to be honest! I prefer the lack of loop though, personal taste :D I upvoted both answers anyway – jamheadart Oct 22 '20 at 09:56

score 1 · Answer 3 · answered Oct 13 '20 at 10:10

1

another possibilty

 c = Application.Index(ws.Range("A2:A" & lRow).Value2, Application.Evaluate("transpose(row(1:" & lRow - 1 & "))"), 1)

answered Oct 13 '20 at 10:10

EvR

3,418
2
13
23

1

I like this as a fancy one-liner but tend to stay away from using functions in a string – jamheadart Oct 22 '20 at 10:00

score 0 · Answer 4 · answered Oct 22 '20 at 09:59

0

Because I was only after a 1D array, I ended up just creating a function to reuse based on Christian Buse's answer:

Public Function GetArrayFromVerticalRange(rng As Range) As Variant
If rng.Count > 1 Then
    GetArrayFromVerticalRange = Application.Transpose(rng.Value2)
Else
    Dim c(0) As Variant: c(0) = rng.Value2
    GetArrayFromVerticalRange = c
End If
End Function

Used "Vertical" term to remind me to only pass in single column ranges! And could create a "Horizontal" version to use the transpose of the transpose

answered Oct 22 '20 at 09:59

jamheadart

5,047
4
32
63

I've edited my answer to cover just 1D arrays (both vertical and horizontal). No need to call Transpose – Cristian Buse Oct 22 '20 at 10:17
Also, ```c(0) = rng.Value2``` will just be a 2D array inside a 1D array of 1 element so clearly not what you need – Cristian Buse Oct 22 '20 at 11:20
I'm confused - `c(0) = rng.value2` when `rng.count` = 1 seems to bring back a 1D array with 1 element to me. And also, transpose on a vert range converts a 2d array to a 1d array of n elements so that's fine? Although I do like how the new method you posted covers both x and y axis with error handling. – jamheadart Oct 22 '20 at 12:04
1

Apologies! I did not read properly. I thought it was ```If rng.Count = 1 Then```. Anyway, the Transpose will still return a 2D array if you transpose a horizontal 1-row range. You should use the version I've added as it also does all the necessary checks and always returns 1D – Cristian Buse Oct 22 '20 at 12:09

Forcing a variant to be another type

4 Answers4