CODEWITHSUNDEEP
vbaexcel
alex
alex

Reputation: 113

How to fill an array with strings in VBA and get its lengh?

How to fill an array with strings in VBA and get its lengh?

For example two cells might contain this info:

A1: "test 1" A2: "test 2"

Dim example As String
Dim arreglito() As String

example = Range("A2").Value


arreglito(0) = example
example= Range("A1").Value
arreglito(1)= example 
MsgBox arreglito(0)

subscript out of range

Dim example As String
Dim arreglito() As Variant

example = Range("A2").Value

arreglito(0) = example
MsgBox arreglito(0)

subscript out of range

Upvotes: 0

Views: 4333

Answers (2)

QHarr
QHarr

Reputation: 84465

Here is a method of adding a single column range from the worksheet to a string array (transpose may have some size restrictions. 2^16 is it?).

Have used a line by Flephal to get the range into a string array in one step.

Sub AddToArray()

Dim arreglito() As String

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Worksheets("MySheet") 'change as appropriate

Dim srcRange As Range
Set srcRange = ws.Range("A1:A3")

arreglito = Split(Join(Application.Transpose(srcRange), "#"), "#")

MsgBox UBound(arreglito) + 1

End Sub

For more than one column transfer via a variant array:

Sub AddToArray2()

    Dim arreglito() As String
    Dim sourceArr()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("MySheet") 'change as appropriate

    Dim srcRange As Range
    sourceArr = ws.Range("A1:C3")

    ReDim arreglito(1 To UBound(sourceArr, 1), 1 To UBound(sourceArr, 2))

    Dim x As Long
    Dim y As Long

    For x = LBound(sourceArr, 1) To UBound(sourceArr, 1)
        For y = LBound(sourceArr, 2) To UBound(sourceArr, 2)
            arreglito(x, y) = CStr(sourceArr(x, y))
        Next y
    Next x

    MsgBox UBound(arreglito, 1) & " x " & UBound(arreglito, 2)

End Sub

Upvotes: 2

storm_88
storm_88

Reputation: 92

you can read entire excel range to array, its much faster than reading data from range cell by cell.

    Sub testRerad()

        Dim arr As Variant 'no brackets needed, I prefer to use variant
        Dim numOfRows As Long, numOfCols As Long
        arr = Sheets(1).Cells(1).Resize(10, 1).value 'arr will contain data from range A1:A10

        'or
        arr = Sheets(1).Range("A1").CurrentRegion.value 'arr will contain data from all continous data startig with A1

        'get dimensions
        numOfRows = UBound(a)
        numOfCols = UBound(a, 2)

    End Sub

be warned that this will always create multidimensional array (even if only 1 column) with dimensions 1 to y, 1 to x

Upvotes: 1

Related Questions