trjhtr

I am used to accessing items from an array, returned by the Split function, directly. This is lazy, I know. Usually, I know the element I want and I say something like:

Debug.Print Split(href, "/")(0)

Questions:

1) Can I do something similar to access the UBound of the array returned?

2) What is the "best practice" way of doing things and why?

Code:

I wrote the following but it looks messy.

Sub Testing()

 Dim href As String
 Dim fileName As String

 href = "https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/01/20180111-AmbSYS-post-ARP-month-of-December-2017v2.xlsx"

 fileName = Trim$(Split(href, "/")(UBound(Split(href, "/"))))

End Sub

I saw from here that I can also do:

Debug.Print Split(href, "/")(Len(href) - Len(Replace(href, "/", "")))

Again, messy.

I know that I can assign to an array variable and then access the UBound that way. It looks tidier but is essentially the same thing; i.e.

Dim myArr() As String

myArr() = Split(href, "/")
fileName = myArr(UBound(myArr)) 

Abstract the implementation into a function. This makes it easy to catch mistakes if you want to use this capability in more than one location. GetUpperBoundElemenentFromDelimitedString takes the required arguments you give it and returns the UBound result. The name may be cumbersome but typing getup and then pressing Ctrl+J will 'List Properties/Methods', the same going to Edit and choosing that option.

Public Function GetUpperBoundElemenentFromDelimitedString(ByVal inputValue As String, ByVal delimiter As String, Optional ByVal compare As VbCompareMethod = VbCompareMethod.vbTextCompare) As String
 Dim temp As Variant
 temp = Split(inputValue, delimiter, compare:=compare)
 GetUpperBoundElemenentFromDelimitedString = temp(UBound(temp))
End Function

Now when you are using it the descriptive name lets you immediately know what's happening.

Public Sub Foo()
 Dim bar As String
 bar = "This,is,going,to,return,an,element,at,a,specific,position."
 Debug.Print GetUpperBoundElemenentFromDelimitedString(bar, ",")
End Sub

The same could be done for a specific position

Public Function GetArrayElemenentFromDelimitedString(ByVal inputValue As String, ByVal delimiter As String, ByVal zeroBasedPosition As Long, Optional ByVal compare As VbCompareMethod = VbCompareMethod.vbTextCompare) As String
 GetArrayElemenentFromDelimitedString = Split(inputValue, delimiter, compare:=compare)(zeroBasedPosition)
End Function

If you need to use the Array then then this is the best practice.

Dim FileName As String, myArr() As String
myArr() = Split(href, "/")
FileName = myArr(UBound(myArr))

Here are several different ways of returning the substring after the last delimiter and the time that it takes to run each 1 million times:

FileName = Right(href, Len(href) - InStrRev(href, "/")) ' 1.25 seconds
FileName = Mid(href, InStrRev(href, "/") + 1) ' 1.29 seconds
FileName = Split(href, "/")(UBound(Split(href, "/"))) '8.93 seconds
myArr() = Split(href, "/"):FileName = myArr(UBound(myArr)) ' 8.71 seconds
FileName = Split(href, "/")(Len(href) - Len(Replace(href, "/", "")))' 9.1 seconds

Notice that InstrRev is almost 7 times faster than using the Split methods.