Get the value before the ubound each time

Question

Dim txt As String
Dim i As Integer
Dim reference As Variant
Dim d As Integer

d = Worksheets("Sheet1").cells(Rows.Count, "a").End(xlUp).Row
txt = cells(3, 4).Value
reference = Split(txt, " ")

For i = 0 To UBound(reference)
    cells(d + 1, [4]).Value = reference(i)
Next

txt = cells(3, 4).Value
reference = Split(txt, " ")
cells(d + 1, [12]).Value = reference(3)

Hi, im trying to pick the reference before the ubound value each time, and the copy to the reference to the last line. I got this code to work when its the 4th part of the string but im trying to always pick the value before the ubound. Is it possible to do UBOUND -1. or do i have to go another way around this. thanks max

Answer 1

There are basically 2 ways to pick the prelast value.

Option 1 - Using Ubound():

Sub TestMe()

    Dim reference As String
    reference = "Stack Overflow is my favourite VBA site!"
    
    Dim splitted As Variant
    splitted = Split(reference)
    
    Debug.Print splitted(UBound(splitted) - 1)
        
End Sub

Option 2 - Using predefined function for array length and removing 2 from it:

Calling it this way:

Debug.Print splitted(GetArrayLength(splitted) - 2)

The function:

Private Function GetArrayLength(myArray As Variant) As Long
    
    If IsEmpty(myArray) Then
        GetArrayLength = 0
    Else
        GetArrayLength = UBound(myArray) - LBound(myArray) + 1
    End If
    
End Function

The function is a bit better, because it checks for empty arrays.