PDA

View Full Version : [SOLVED] How to Get Array Length...



ayrus
09-14-2018, 01:10 AM
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
09-14-2018, 01:48 AM
Ubound (mytext)
should do it. You may have to add 1 because the array might be zero based.

ayrus
09-14-2018, 03:09 AM
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
09-14-2018, 07:07 AM
Try this



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


(Not tested)

snb
09-14-2018, 07:39 AM
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
09-14-2018, 07:53 AM
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
09-17-2018, 01:36 AM
Thank you all for your time and responses.. Much appreciated..

ayrus
09-17-2018, 01:41 AM
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
09-17-2018, 06:30 AM
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
09-17-2018, 10:25 PM
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
09-18-2018, 07:23 AM
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

22892

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
09-24-2018, 10:03 PM
Many thanks Paul, it has been great help...