Array Size Returned by Split Function - Excel VBA Array Size Returned by Split Function - Excel VBA vba vba

Array Size Returned by Split Function - Excel VBA


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 WithEnd 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)


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

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


Dim first as integerDim last as integerDim arr() as stringDim lengthOfArray as integer'split your data and save it in arr'first = LBound(arr)last = UBound(arr)lengthOfArray = last - firstmsgbox lengthOfArray 'This will display the length of the array'


matomo