Subscript out of Range Error if Null

[gwoodby]

While redoing this function, (not very good i might add) I found out
that it only has the subscript error say if served = vntX(5) was
empty, nothing was returned then it would give me the error, any way
to fix that? also, could you show me a better shorter way to write
this code jsut as an example..


Private Function SplitMe(strToSplit As String, intChoose As Integer)
Dim vntX As Variant
Dim First As String
Dim last As String
Dim mofa As String
Dim child As String
Dim issued As String
Dim served As String
vntX = Split(strToSplit, ",")
First = vntX(0)
last = vntX(1)
mofa = vntX(2)
child = vntX(3)
issued = vntX(4)
served = vntX(5)
If intChoose = 0 Then
SplitMe = First & "," & last
ElseIf intChoose = 1 Then
SplitMe = mofa
ElseIf intChoose = 2 Then
TxtFirstName = First
TxtLastName = last
CBOMoFa = mofa
TxtIssued = issued
TxtServed = served
SplitMe = First & "," & last
End If
End Function

 

[Jim Thomlinson]

There is nothing terribly wrong with what you have. You could avoid assigning
the array items to variables if you want it to be a bit more efficient and
always assign a return type but otherwise...

Private Function SplitMe(strToSplit As String, intChoose As Integer) as String
Dim vntX As Variant

vntX = Split(strToSplit, ",")

If intChoose = 0 Then
SplitMe = vntX(0) & "," & vntX(1)
ElseIf intChoose = 1 Then
SplitMe = vntX(2)
ElseIf intChoose = 2 Then
TxtFirstName = vntX(0)
TxtLastName = vntX(1)
CBOMoFa = vntX(2)
TxtIssued = vntX(4)
if ubound(vntX) = 5 then
TxtServed = vntX(5)
else
TxtServed = "Blank"
endif
SplitMe = vntX(0)& "," & vntX(1)
End If
End Function

 

[gwoodby]

There is nothing terribly wrong with what you have. You could avoid assigning
the array items to variables if you want it to be a bit more efficient and
always assign a return type but otherwise...

Private Function SplitMe(strToSplit As String, intChoose As Integer) as String
Dim vntX As Variant

vntX = Split(strToSplit, ",")

If intChoose = 0 Then
SplitMe = vntX(0) & "," & vntX(1)
ElseIf intChoose = 1 Then
SplitMe = vntX(2)
ElseIf intChoose = 2 Then
TxtFirstName = vntX(0)
TxtLastName = vntX(1)
CBOMoFa = vntX(2)
TxtIssued = vntX(4)
if ubound(vntX) = 5 then
TxtServed = vntX(5)
else
TxtServed = "Blank"
endif
SplitMe = vntX(0)& "," & vntX(1)
End If
End Function

--
HTH...

Jim Thomlinson






- Show quoted text -

Thank you for the tips, they help alot greatly appreciated!