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)
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.