How to Get Array Length...

[ayrus]

Hi,
I have following text, in Excel in D5 Cell

VB933 + VB19 + BB3


I am not able to get the length of Array; for example, I should get 3 on the sample text for VB933, VB19 & BB3
Not able to get the length of the array using Array.Length; can someone please help!

many thanks

Function SplitValue(ByVal myMCode As String)

    SplitValue = myMCode
    
    ' Remove Blank Spaces
    Dim myMCode2 As String
    myMCode2 = Replace(myMCode, " ", "")
    
    Dim mytext() As String
    mytext() = Split(myMCode2, "+")
    
    Dim myMCodeNos As Integer
    myMCodeNos = mytext.Length
    
    'SplitValue = mytext(1)
    ' SplitValue = myMCode2
    SplitValue = myMCodeNos



End Function

[p45cal]

Ubound (mytext)
should do it. You may have to add 1 because the array might be zero based.

[ayrus]

Many thanks P45Cal; works fine now..

I am using split function for "+", but for a string with "+" at the end, it returns me an extra array item.

For eg: VB933 + VB19 + BB3 +

text(0) = VB933
text(1) = VB19
text(2) = BB3
text(3) = +

Is it possible to remove the array Item containing split character "+"?

Many thanks..

[Paul_Hossler]

Try this

ReDim Preserve text(LBound(text) to Ubound(text)-1)

(Not tested)

[snb]

If '+' is the splitting character, no array item can contain a '+'

Use

c00="+VB933+VB19+BB3"
sn=split(mid(c00,2),"+")

or

c00="VB933+VB19+BB3+"
sn=filter(split(c00 & "~","+"),"~",0)

[p45cal]

text(3) = +

That would surpise me a lot, since splitting, as you have, with '+' should eliminate all '+' characters from the result, it is after all the delimiter.
You might end up with a final "" as the last member of the array.
One way is to test your string for that final '+' character and if present remove it, then do your split:
If Right(myMCode2, 1) = "+" Then myMCode2 = Left(myMCode2, Len(myMCode2) - 1)

ps. Here's a way you might end up with that final "+" in the last member of the array, but it's contrived:

Z = "VB933 + VB19 + BB3 ++"
Z = Replace(Z, " ", "")
y = Split(Z, "+", 4)

[ayrus]

Thank you all for your time and responses.. Much appreciated..

[ayrus]

Hi P45Cal,

Not sure where I have gone wrong.

Following text in a cell :

VB933 + VB19 + BB3 + AAN +

returns 5 Codes instead of 4 (counting text - VB933, VB19, BB3 & AAN), when the string is split using "+"

my sample code

Function SplitValue(ByVal myMCode As String)
     
    ' Remove Blank Spaces
    Dim myMCode2 As String
    myMCode2 = Replace(myMCode, " ", "")
    
    Dim myTextArr() As String
    myTextArr() = Split(myMCode2, "+")
    
    Dim myMCodeNos As Integer
    myMCodeNos = UBound(myTextArr)
    
    ReDim myTextArr(mycodenos)
    SplitValue = myMCodeNos + 1


End Function

Thanks

[Paul_Hossler]

SplitValue2 is an alternative function

Option Explicit


Sub drv()
    MsgBox = SplitValue2(Range("A1"))
End Sub

Function SplitValue2(ByVal myMCode As String) As Long
    Dim myMCode2 As String
    Dim myTextArr As Variant
    
    myMCode2 = Trim(myMCode)
    
    If Right(myMCode2, 1) = "+" Then myMCode2 = Left(myMCode2, Len(myMCode2) - 1)
    
    myTextArr = Split(myMCode2, "+")
    
    SplitValue2 = UBound(myTextArr) - LBound(myTextArr) + 1
End Function




Function SplitValue(ByVal myMCode As String) As Long
     
    ' Remove Blank Spaces
    Dim myMCode2 As String
    myMCode2 = Replace(myMCode, " ", "")    '   if all you want is the length and seperated by +, then no need
    
    Dim myTextArr() As String
    myTextArr() = Split(myMCode2, "+")
    
    Dim myMCodeNos As Integer
    myMCodeNos = UBound(myTextArr)
    
    ReDim myTextArr(myMCodeNos)     '   <<<<< had a typo
    SplitValue = myMCodeNos + 1
End Function

[ayrus]

Many thanks for your response Paul.

Was actually wondering why the split function is returning a "+" at the end; not sure if this VBA bug or some issue with my code.

-Surya

[Paul_Hossler]

Many thanks for your response Paul.

Was actually wondering why the split function is returning a "+" at the end; not sure if this VBA bug or some issue with my code.

-Surya

Capture.JPG

No, VBA is working correctly and the only thing wrong with your code is not handling the implied empty part after the last "+", the '4' below


0 - 1

1 - 2

2 - 3

3 - 4

[ayrus]

Many thanks Paul, it has been great help...