CODEWITHSUNDEEP
excelvba
jamheadart
jamheadart

Reputation: 5303

Forcing a variant to be another type

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...

Upvotes: 0

Views: 433

Answers (4)

Cristian Buse
Cristian Buse

Reputation: 4558

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

Upvotes: 2

jamheadart
jamheadart

Reputation: 5303

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

Upvotes: 0

pgSystemTester
pgSystemTester

Reputation: 9907

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

Upvotes: 1

EvR
EvR

Reputation: 3498

another possibilty

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

Upvotes: 1

Related Questions