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

BigBen is right, it's as simple as that (and why not) - you only have to ensure that Ubound(a) > 0, else you will get a "Subscript out of Range" error — FunThomas, Aug 17 '20 at 17:15
Just for curiosity: How did you get the idea to write `[4]` in square brackets - it's very uncommon (and superfluous), I had a question on SO some time ago about the meaning https://stackoverflow.com/q/54579404/7599798 — FunThomas, Aug 17 '20 at 17:17
Thanks BigBen and FunThomas for your responses, The [4] is the column index i know its quite uncommon but i unfortunately slipped in the habit. — Max Murrell, Aug 17 '20 at 17:30
@MaxMurrell - so you are using `[]` because it reminds you of a column? — Vityata, Aug 17 '20 at 17:34
@MaxMurrell - Reading the question from @FunThomas, I see in the comments this - *[] can also be used when accessing a table's column, such as Range("Table[Col1]") – Mistella Feb 7 '19 at 18:13*. So, I understand why you are using it... But... On behalf on every VBA developer, who has "inherited" VBA code and had to understand what is written before rewriting it, please, stop using `[]` for the columns. Thanks :) — Vityata, Aug 17 '20 at 17:40

score 0 · Accepted Answer · answered Aug 17 '20 at 17:33

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.

@MaxMurrell - you are welcome. Additionally, if you are going to split by one space, like this `reference = Split(txt, " ")`, you can shorten the code to this reference = Split(txt)`, VBA would get what you mean. — Vityata, Aug 17 '20 at 17:43

Get the value before the ubound each time

1 Answers1