Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

Yes. It has split().

"Gary''s Student" wrote:

Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

I re-read your post and you want to split a string into an array of
characters. My mistake - the split() function in VBA splits only the string
into other strings given a delimiter.


"Gary''s Student" wrote:

Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

MID?

Used like

Dim strVar As String
Dim i As Integer
strVar = "test"
For i = 1 To Len(strVar)
MsgBox Mid(strVar, i, 1)
Next i


HTH,
Bernie
MS Excel MVP


"Gary''s Student" wrote in message
...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

Thank you Bernie.

Your approach works. How can I package it as a function that returns a
zero-based one dimensional array?
--
Gary's Student


"Bernie Deitrick" wrote:

MID?

Used like

Dim strVar As String
Dim i As Integer
strVar = "test"
For i = 1 To Len(strVar)
MsgBox Mid(strVar, i, 1)
Next i


HTH,
Bernie
MS Excel MVP


"Gary''s Student" wrote in message
...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

? Why? Anyway:

Sub TryNow()
Dim strVar As String
Dim myStrArray As Variant
Dim i As Integer
strVar = "test"
myStrArray = Str2Array(strVar)

For i = LBound(myStrArray) To UBound(myStrArray)
MsgBox myStrArray(i)
Next i
End Sub

Function Str2Array(strVar2 As String) As Variant
Dim i As Integer
Dim myArray() As String

ReDim myArray(0 To Len(strVar2) - 1)

For i = 0 To Len(strVar2) - 1
myArray(i) = Mid(strVar2, i + 1, 1)
Next i

Str2Array = myArray
End Function

HTH,
Bernie
MS Excel MVP


"Gary''s Student" wrote in message
...
Thank you Bernie.

Your approach works. How can I package it as a function that returns a
zero-based one dimensional array?
--
Gary's Student


"Bernie Deitrick" wrote:

MID?

Used like

Dim strVar As String
Dim i As Integer
strVar = "test"
For i = 1 To Len(strVar)
MsgBox Mid(strVar, i, 1)
Next i


HTH,
Bernie
MS Excel MVP


"Gary''s Student" wrote in message
...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

Something like this might suit you and could be faster:

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr

btArray = strString
ReDim arr(1 To Len(strString))

For i = 0 To UBound(btArray) Step 2
n = n + 1
arr(n) = btArray(i)
Next

SplitChars = arr

End Function


Sub test()

Dim i As Long
Dim arr

arr = SplitChars("string to test")

For i = LBound(arr) To UBound(arr)
MsgBox arr(i), , Chr(arr(i))
Next

End Sub


RBS

"Gary''s Student" wrote in message
...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

Thanks
--
Gary's Student


"Bernie Deitrick" wrote:

? Why? Anyway:

Sub TryNow()
Dim strVar As String
Dim myStrArray As Variant
Dim i As Integer
strVar = "test"
myStrArray = Str2Array(strVar)

For i = LBound(myStrArray) To UBound(myStrArray)
MsgBox myStrArray(i)
Next i
End Sub

Function Str2Array(strVar2 As String) As Variant
Dim i As Integer
Dim myArray() As String

ReDim myArray(0 To Len(strVar2) - 1)

For i = 0 To Len(strVar2) - 1
myArray(i) = Mid(strVar2, i + 1, 1)
Next i

Str2Array = myArray
End Function

HTH,
Bernie
MS Excel MVP


"Gary''s Student" wrote in message
...
Thank you Bernie.

Your approach works. How can I package it as a function that returns a
zero-based one dimensional array?
--
Gary's Student


"Bernie Deitrick" wrote:

MID?

Used like

Dim strVar As String
Dim i As Integer
strVar = "test"
For i = 1 To Len(strVar)
MsgBox Mid(strVar, i, 1)
Next i


HTH,
Bernie
MS Excel MVP


"Gary''s Student" wrote in message
...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

Or to get a 0-based array:

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr

btArray = strString
ReDim arr(0 To Len(strString) - 1)

For i = 0 To UBound(btArray) Step 2
arr(n) = btArray(i)
n = n + 1
Next

SplitChars = arr

End Function


RBS

"RB Smissaert" wrote in message
...
Something like this might suit you and could be faster:

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr

btArray = strString
ReDim arr(1 To Len(strString))

For i = 0 To UBound(btArray) Step 2
n = n + 1
arr(n) = btArray(i)
Next

SplitChars = arr

End Function


Sub test()

Dim i As Long
Dim arr

arr = SplitChars("string to test")

For i = LBound(arr) To UBound(arr)
MsgBox arr(i), , Chr(arr(i))
Next

End Sub


RBS

"Gary''s Student" wrote in
message ...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

But it seems a function based on a Byte array is not faster then one based
on Mid.
Maybe using Mid$ is a bit faster.
Still if you can do with the character numbers it will be faster.

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr() As String

btArray = strString
ReDim arr(0 To Len(strString) - 1) As String

For i = 0 To UBound(btArray) Step 2
arr(n) = Chr(btArray(i))
n = n + 1
Next

SplitChars = arr

End Function


RBS

"RB Smissaert" wrote in message
...
Or to get a 0-based array:

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr

btArray = strString
ReDim arr(0 To Len(strString) - 1)

For i = 0 To UBound(btArray) Step 2
arr(n) = btArray(i)
n = n + 1
Next

SplitChars = arr

End Function


RBS

"RB Smissaert" wrote in message
...
Something like this might suit you and could be faster:

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr

btArray = strString
ReDim arr(1 To Len(strString))

For i = 0 To UBound(btArray) Step 2
n = n + 1
arr(n) = btArray(i)
Next

SplitChars = arr

End Function


Sub test()

Dim i As Long
Dim arr

arr = SplitChars("string to test")

For i = LBound(arr) To UBound(arr)
MsgBox arr(i), , Chr(arr(i))
Next

End Sub


RBS

"Gary''s Student" wrote in
message ...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to
be
coded?
--
Gary's Student

Thank you very much
--
Gary''s Student


"RB Smissaert" wrote:

But it seems a function based on a Byte array is not faster then one based
on Mid.
Maybe using Mid$ is a bit faster.
Still if you can do with the character numbers it will be faster.

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr() As String

btArray = strString
ReDim arr(0 To Len(strString) - 1) As String

For i = 0 To UBound(btArray) Step 2
arr(n) = Chr(btArray(i))
n = n + 1
Next

SplitChars = arr

End Function


RBS

"RB Smissaert" wrote in message
...
Or to get a 0-based array:

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr

btArray = strString
ReDim arr(0 To Len(strString) - 1)

For i = 0 To UBound(btArray) Step 2
arr(n) = btArray(i)
n = n + 1
Next

SplitChars = arr

End Function


RBS

"RB Smissaert" wrote in message
...
Something like this might suit you and could be faster:

Function SplitChars(strString As String) As Variant

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim arr

btArray = strString
ReDim arr(1 To Len(strString))

For i = 0 To UBound(btArray) Step 2
n = n + 1
arr(n) = btArray(i)
Next

SplitChars = arr

End Function


Sub test()

Dim i As Long
Dim arr

arr = SplitChars("string to test")

For i = LBound(arr) To UBound(arr)
MsgBox arr(i), , Chr(arr(i))
Next

End Sub


RBS

"Gary''s Student" wrote in
message ...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to
be
coded?
--
Gary's Student

...zero-based one dimensional array?

Just to mention another option.
A Regular Expression is zero based also.

Sub Demo()
Dim RE As Object
Dim M As Variant
Dim s As String
Dim J As Long

Set RE = CreateObject("VBScript.RegExp")
s = "abcdef"

RE.Global = True
RE.Pattern = "\w"
Set M = RE.Execute(s)

'// Zero based...
For J = 0 To M.Count - 1
Debug.Print M(J)
Next J
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Gary''s Student" wrote in message
...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

That's an interesting one.
It leaves out spaces though and I can't turn in into a function.

RBS

"Dana DeLouis" wrote in message
...
...zero-based one dimensional array?

Just to mention another option.
A Regular Expression is zero based also.

Sub Demo()
Dim RE As Object
Dim M As Variant
Dim s As String
Dim J As Long

Set RE = CreateObject("VBScript.RegExp")
s = "abcdef"

RE.Global = True
RE.Pattern = "\w"
Set M = RE.Execute(s)

'// Zero based...
For J = 0 To M.Count - 1
Debug.Print M(J)
Next J
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Gary''s Student" wrote in
message ...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

Thank you Dana. This is an extremely interesting approach.

A long time ago, I had a friend named GREP. Perhaps, some time in the
future, I can re-create him in VBA.
--
Gary's Student


"Dana DeLouis" wrote:

...zero-based one dimensional array?

Just to mention another option.
A Regular Expression is zero based also.

Sub Demo()
Dim RE As Object
Dim M As Variant
Dim s As String
Dim J As Long

Set RE = CreateObject("VBScript.RegExp")
s = "abcdef"

RE.Global = True
RE.Pattern = "\w"
Set M = RE.Execute(s)

'// Zero based...
For J = 0 To M.Count - 1
Debug.Print M(J)
Next J
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Gary''s Student" wrote in message
...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to be
coded?
--
Gary's Student

It leaves out spaces though and I can't turn in into a function.

Thanks for the feedback. This may not be the best approach, as I'm not sure
what the op had in mind as a function.
We can make a small change to include spaces as below.
Here are a couple of general ideas. Both test examples print the first and
last character.
However, in the second example, the line "Set RE =" is skipped, thus saving
a little setup time.
Other speed-ups would be to set a vba library reference, and avoid the
CreateObject line.
Again, just some general ideas.

Option Explicit
Public RE As Object

Function RE_Split(s As String) As Variant
If RE Is Nothing Then
Set RE = CreateObject("VBScript.RegExp")
RE.Global = True
'// Any word character or white space character
RE.Pattern = "\w|\s"
End If

If RE.Test(s) Then
Set RE_Split = RE.Execute(s)
End If
End Function

Sub TestIt()
Dim v As Variant
Set v = RE_Split("12 ab")
Debug.Print v(0)
Debug.Print v(4)

Set v = RE_Split("ab xyz")
Debug.Print v(0)
Debug.Print v(v.Count - 1)
End Sub


--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"RB Smissaert" wrote in message
...
That's an interesting one.
It leaves out spaces though and I can't turn in into a function.

RBS

"Dana DeLouis" wrote in message
...
...zero-based one dimensional array?

Just to mention another option.
A Regular Expression is zero based also.

Sub Demo()
Dim RE As Object
Dim M As Variant
Dim s As String
Dim J As Long

Set RE = CreateObject("VBScript.RegExp")
s = "abcdef"

RE.Global = True
RE.Pattern = "\w"
Set M = RE.Execute(s)

'// Zero based...
For J = 0 To M.Count - 1
Debug.Print M(J)
Next J
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Gary''s Student" wrote in
message ...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to
be
coded?
--
Gary's Student

Yes, it works now.
Interesting and neat, but about twice as slow as with Mid or a Byte array.
Setting a reference to RegExp makes it a bit faster, but not much.
Thanks in any case for the tip.

RBS

"Dana DeLouis" wrote in message
...
It leaves out spaces though and I can't turn in into a function.

Thanks for the feedback. This may not be the best approach, as I'm not
sure what the op had in mind as a function.
We can make a small change to include spaces as below.
Here are a couple of general ideas. Both test examples print the first
and last character.
However, in the second example, the line "Set RE =" is skipped, thus
saving a little setup time.
Other speed-ups would be to set a vba library reference, and avoid the
CreateObject line.
Again, just some general ideas.

Option Explicit
Public RE As Object

Function RE_Split(s As String) As Variant
If RE Is Nothing Then
Set RE = CreateObject("VBScript.RegExp")
RE.Global = True
'// Any word character or white space character
RE.Pattern = "\w|\s"
End If

If RE.Test(s) Then
Set RE_Split = RE.Execute(s)
End If
End Function

Sub TestIt()
Dim v As Variant
Set v = RE_Split("12 ab")
Debug.Print v(0)
Debug.Print v(4)

Set v = RE_Split("ab xyz")
Debug.Print v(0)
Debug.Print v(v.Count - 1)
End Sub


--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"RB Smissaert" wrote in message
...
That's an interesting one.
It leaves out spaces though and I can't turn in into a function.

RBS

"Dana DeLouis" wrote in message
...
...zero-based one dimensional array?

Just to mention another option.
A Regular Expression is zero based also.

Sub Demo()
Dim RE As Object
Dim M As Variant
Dim s As String
Dim J As Long

Set RE = CreateObject("VBScript.RegExp")
s = "abcdef"

RE.Global = True
RE.Pattern = "\w"
Set M = RE.Execute(s)

'// Zero based...
For J = 0 To M.Count - 1
Debug.Print M(J)
Next J
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Gary''s Student" wrote in
message ...
Does VBA supply a builtin function like SPLIT() that returns an array
containing the individual characters in a string, or would one have to
be
coded?
--
Gary's Student