Array Size Returned by Split Function - Excel VBA

Question

Here is what I have done so far.

Sub MP_division()

Worksheets(3).Activate    
Dim last_cell As Integer    
Dim platforms() As String    
Dim arr_size As Integer  

platforms = Split(Cells(2, 47), ", ")    
last_cell = Mid(Range("A1048576").End(xlUp).Address, 4)    
arr_size = len(platforms) // is there something like this?    
For x = 1 To last_cell    
    For y = 1 To arr_size 
        //do something        
    Next        
Next   
End Sub

My question is, how may I get the array size(arr_size) that is returned by the split function, so that I could use in my for loop? Thank you.

Does this answer your question? [Get length of array?](https://stackoverflow.com/questions/30574814/get-length-of-array) — chaotic3quilibrium, Aug 02 '21 at 12:56

score 17 · Answer 1 · edited May 23 '17 at 11:54

Consider abound using LBound and Ubound:

Sub MP_division()
    Dim last_cell As Long
    Dim platforms() As String
    Dim x As Long, y As Integer

    With ThisWorkbook.Worksheets(3)
        platforms = Split(.Cells(2, 47), ", ")
        last_cell = .Cells(.Rows.Count, "A").End(xlUp).Row
        For x = 1 To last_cell
            For y = LBound(platforms) To UBound(platforms)
                'do something
            Next
        Next
    End With
End Sub

Btw, Split always returns zero-based array (starts from 0, but not from 1). Thats why I recomend you to use both, Ubound and Lbound.

One more thing, I've changed Dim last_cell As Integer to Dim last_cell As Long, since max value of Integer only 32767 and if the last row would be greater than 32767, you would get an error with Integer.

And avoid using Select/Active statements (it's about Worksheets(3).Activate)

score 6 · Answer 2 · answered Nov 29 '16 at 18:45

6

VBA LBound and UBound return the first and the last array position, so the correct answer is:

size = UBound(myArray) - LBound(myArray) + 1

answered Nov 29 '16 at 18:45

farope

61
1
2

score 4 · Answer 3 · edited Apr 28 '16 at 06:38

4

Dim first as integer
Dim last as integer
Dim arr() as string
Dim lengthOfArray as integer

'split your data and save it in arr'

first = LBound(arr)
last = UBound(arr)

lengthOfArray = last - first

msgbox lengthOfArray 'This will display the length of the array'

edited Apr 28 '16 at 06:38

Vinoth Krishnan

2,925
6
29
34

answered Apr 28 '16 at 06:36

Shubham Arya

49
1

1

can you please explain more detail about your answer ? – Mostafiz Apr 28 '16 at 06:58

Array Size Returned by Split Function - Excel VBA

3 Answers3