VBA... Split -or- STRIKE!

[CubeE101]

With Excel VBA...

Can you use a method such as Split to split a variable into an array...

  Dim Temp as String, Temp2() as String
  Temp = "I|Wanna|Be|An|Array"
  Temp2 = Split(Temp,"|")

Then the array would look like this...
Temp2(0) = "I"
Temp2(1) = "Wanna"
Temp2(2) = "Be"
Temp2(3) = "An"
Temp2(4) = "Array"

I am assuming VBA does not support Split like VB does...(since It gives me an error)

Any Idea or Suggestions?
Or do I gotta do this MANUALLY :-( ??? Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[strongm]

Just write your own Split function... (if search was up you'd find at least a couple of versions available in this forum)

 

[DrJavaJoe]

Here's one.
faq705-1823

 

[CubeE101]

strongm,
Yeah I already made my own... I was hoping for one of your Script calls...

Here is my code...

Function Split(Temp As String, Delim As String) As Variant
  Dim I As Integer, N As Integer, M As String, II As Integer
  N = 0
  For I = 1 To Len(Temp)
    If Mid(Temp, I, 1) = Delim Then N = N + 1
  Next
  ReDim OutAry(N) As String
  For I = 0 To N 
    Do
      II = II + 1
      M = Mid(Temp, II, 1)
      If M <> Delim And II <= Len(Temp) Then OutAry(I) = OutAry(I) + M
    Loop Until M = Delim Or II > Len(Temp)
  Next
  Split = OutAry
End Function

Params:
Temp - The String to split
Delim - The Delimeter used

Returns:
OutAry - the return (Output) Array

Variables:
I - main Index
II - Sub-Index
N - Array Bounds (Number of indexs)
M - the Mid(...) character

It IS functional...

If anyone has any other solutions (such as script calls...)or other suggestions, please let me know... Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[FRoeCassNet]

I believe split() was added to VBA in the office 2000 version.

 

[CubeE101]

I still have 97 at work... :-(

Thnx though... Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[strongm]

Unfortunately Cube, your function doesn't work if the delimiter is more than a single character. And, as recently illustrated in this forum, the delimiter can be multicharacter...And, on top of that, you're being deliberately obtuse with your variable naming...

 

[SemperFiDownUnda]

"obtuse"
/me makes a note to use cool words like that
brings the intellect of the boards from Geek smart to proper smart I think
8)

 

[CubeE101]

switch M = Mid(Temp, II, 1)
to M = Mid(Temp, II, Len(Delim))

and If Mid(Temp, I, 1) = Delim Then N = N + 1
to If Mid(Temp, I, Len(Delim)) = Delim Then N = N + 1

and add this line II = II + len(delim) - 1
after Loop Until M = Delim Or II > Len(Temp)

And it should work fine with multi-char delimeters
...multichar split...

Function Split(Temp As String, Delim As String) As Variant
Dim I As Integer, N As Integer, M As String, II As Integer
N = 0
For I = 1 To Len(Temp)
If Mid(Temp, I, 1) = Delim Then N = N + 1
Next
ReDim OutAry(N) As String
For I = 0 To N
Do
II = II + 1
M = M = Mid(Temp, II, Len(Delim))
If M <> Delim And II <= Len(Temp) Then OutAry(I) = OutAry(I) + M
Loop Until M = Delim Or II > Len(Temp)
II = II + len(delim) - 1
Next
Split = OutAry
End Function

But what I needed it for was a single &quot;|&quot; delimeter...
So the first one worked fine...

That Obtuse statement was pretty Acute ;-)
from skewed to obtuse all in one day...
Is that considered to be an achievment?

However... I see you are still Parrallel with my concept of variable naming conventions, Yet I am Perpendicular with typing so when it comes to typing less ... it is quite Coincident that I like to make variables shorter.
Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[CajunCenturion]

CubeE101 - your code, as posted in the previous post contains a logic error in one location -- If Mid(Temp, I, 1) = Delim Then N = N + 1 -- and will also generate a Type Mismatch Error in another -- M = M = Mid(Temp, II, Len(Delim)) .

It is also incredibly inefficient, for example, you are calling the Len function three tems with Temp as the arguement, with two of those calls being inside of a loop.

IMHO, If you spent more time on producing quality code rather than eliminating keystrokes, you (and you customers) might be more pleased with the results. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CubeE101]

that was at 3am this morning... have you EVER heard of a typo... (that's usually what happens that late)....

If you look at the note diectly above the code...
I'm sure you will figure that out...

... I REALLY don't even care about multicharacter delimeters...

you can use a replace call to replace multichar's with one char... then split with the 1 char delimeter

The SingleChar Split above works fine and thats all I need...

I didn't say it was optimized... I really don't care...
It only gets used once in my program...

If I need to speed it up and make it more efficient later, I will do so at that time... Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[TheTuna]

he he he ... I know all about those late night typo's Cubert!

Keep in mind that as a developer, sometimes we see things that we MIGHT classify as a minor problem and in our ever present need to show how brilliant we are, we offer to help out and end up POINTING out what we see. Then, we do it ourselves, because nobody else is going to know!

Your response really made me laugh though! Tuna - It's fat free until you add the Mayo!