Split string into array of characters?

You can just assign the string to a byte array (the reverse is also possible). The result is 2 numbers for each character, so Xmas converts to a byte array containing {88,0,109,0,97,0,115,0}
or you can use StrConv

Dim bytes() as Byte
bytes = StrConv("Xmas", vbFromUnicode)

which will give you {88,109,97,115} but in that case you cannot assign the byte array back to a string.
You can convert the numbers in the byte array back to characters using the Chr() function

Here's another way to do it in VBA.

Function ConvertToArray(ByVal value As String)
    value = StrConv(value, vbUnicode)
    ConvertToArray = Split(Left(value, Len(value) - 1), vbNullChar)
End Function
Sub example()
    Dim originalString As String
    originalString = "hi there"
    Dim myArray() As String
    myArray = ConvertToArray(originalString)
End Sub

According to this code golfing solution by Gaffi, the following works:

a = Split(StrConv(s, 64), Chr(0))

the problem is that there is no built in method (or at least none of us could find one) to do this in vb. However, there is one to split a string on the spaces, so I just rebuild the string and added in spaces....

Private Function characterArray(ByVal my_string As String) As String()
  'create a temporary string to store a new string of the same characters with spaces
  Dim tempString As String = ""
  'cycle through the characters and rebuild my_string as a string with spaces 
  'and assign the result to tempString.  
  For Each c In my_string
     tempString &= c & " "
  Next
  'return return tempString as a character array.  
  Return tempString.Split()
End Function

To split a string into an array of sub-strings of any desired length:

Function charSplitMulti(s As Variant, splitLen As Long) As Variant
    
        Dim padding As Long: padding = 0
        Dim l As Long: l = 0
        Dim v As Variant
        
        'Pad the string so it divides evenly by
        ' the length of the desired sub-strings
        Do While Len(s) Mod splitLen > 0
            s = s & "x"
            padding = padding + 1
        Loop
        
        'Create an array with sufficient
        ' elements to hold all the sub-strings
        Do Until Len(v) = (Len(s) / splitLen) - 1
            v = v & ","
        Loop
        v = Split(v, ",")
        
        'Populate the array by repeatedly
        ' adding in the first [splitLen]
        ' characters of the string, then
        ' removing them from the string
        Do While Not s Like ""
            v(l) = Mid(s, 1, splitLen)
            s = Right(s, Len(s) - splitLen)
            l = l + 1
        Loop
        
        'Remove any padding characters added at step one
        v(UBound(v)) = Left(v(UBound(v)), Len(v(UBound(v))) - padding)
        
        'Output the array
        charSplitMulti = v
    
    End Function

You can pass the string into it either as a string:

Sub test_charSplitMulti_stringInput()

    Dim s As String: s = "123456789abc"
    Dim subStrLen As Long: subStrLen = 4
    Dim myArray As Variant
    
    myArray = charSplitMulti(s, subStrLen)
    
    For i = 0 To UBound(myArray)
        MsgBox myArray(i)
    Next

End Sub

…or already declard as a variant:

Sub test_charSplitMulti_variantInput()

    Dim s As Variant: s = "123456789abc"
    Dim subStrLen As Long: subStrLen = 5
    
    s = charSplitMulti(s, subStrLen)
    
    For i = 0 To UBound(s)
        MsgBox s(i)
    Next

End Sub

If the length of the desired sub-string doesn't divide equally into the length of the string, the uppermost element of the array will be shorter. (It'll be equal to strLength Mod subStrLength. Which is probably obvious.)

I found that most-often I use it to split a string into single characters, so I added another function, so I can be lazy and not have to pass two variables in that case:

Function charSplit(s As Variant) As Variant

    charSplit = charSplitMulti(s, 1)

End Function

Sub test_charSplit()

    Dim s As String: s = "123456789abc"
    Dim myArray As Variant
    
    myArray = charSplit(s)
    
    For i = 0 To UBound(myArray)
        MsgBox myArray(i)
    Next

End Sub

Try this minicode From Rara:

Function charSplitMulti(TheString As Variant, SplitLen As Long) As Variant
    'Defining a temporary array.
    Dim TmpArray() As String
    'Checking if the SplitLen is not less than one. if so the function returns the whole string without any changing.
    SplitLen = IIf(SplitLen >= 1, SplitLen, Len(TheString))
    'Redefining the temporary array as needed.
    ReDim TmpArray(Len(TheString) \ SplitLen + IIf(Len(TheString) Mod SplitLen <> 0, 1, 0))
    'Splitting the input string.
    For i = 1 To UBound(TmpArray)
        TmpArray(i) = Mid(TheString, (i - 1) * SplitLen + 1, SplitLen)
    Next
    'Outputing the result. 
    charSplitMulti = TmpArray
End Function