Calc Basic: how to split string into array of each character?

[raretrack]

Hi

I’m starting to migrate from Excel VBA to Calc Basic. I’ve looked through various help articles but don’t seem to quite be able to convert this Sub from VBA to Basic.

The Sub simply takes a character string from a textbox and displays a message splitting them up, e.g. “abcd” would display as “1:a, 2:b, 3:c, 4:d”.

This is my code (with the commented out line showing what the old VBA code). I’ve tried various different things for the UserChars row but whateve I try it doesn’t populate the array at all.

Sub CharExtract()

Dim CharCount As Long
Dim i As Integer
Dim Result As String
Dim UserChars() As String
Dim UserString As String

UserString = Trim(InputBox("Type or paste data here", "Character Extractor"))

If UserString = "" Then Exit Sub

'UserChars = Split(StrConv(UserString, vbUnicode), Chr(0))
UserChars = Split(UserString,Chr(0),Len(UserString))

CharCount = UBound(UserChars) - LBound(UserChars)

For i = LBound(UserChars) To UBound(UserChars) - 1
  Result = Result & vbCrLf & i + 1 & ": " & vbTab & UserChars(i)
 Next i

Msgbox Result, 64, "Character Extractor"

End Sub

Hope someone may be able to help me, so thanks in advance.

[Lupp]

Suggested code:

Function stringToCharacterArray(pIn As string)
REM If used in Calc the function will return an array as gotten from a row.
Dim out() As String REM Empty array signalling empty input string. (Err. 538 in Calc) 
u = Len(pIn) - 1
If u>=0 Then 
 Redim out(0 To u) As String
 For j = 0 To u
  out(j) = Mid(pIn, j + 1, 1)
 Next j
End If
stringToCharacterArray = out
End Function  

The function needs to be used under array-evaluation. If direct output to a sheet is made you get the well known problems with array-output: A range will be locked for the output, and changes in the input will not adapt that range. Calling the function from a Sub you can catch any errors easily by the code of that Sub.

[raretrack]

Thanks very much, Lupp, that works brilliantly! (I can’t upvote as I’m still a newbie, otherwise I would do…)

[Lupp]

No matter.

[lonk]

It works well.

Option Explicit	
Sub CharExtract()
	Dim i 				As Integer
	Dim Result	 		As String
	Dim UserChars()		As String
	Dim UserString 		As String
	UserString = Trim(InputBox("Type or paste data here", "Character Extractor"))
	If UserString = "" Then Exit Sub
	UserChars() = stringToCharacterArray(UserString)
	For i = LBound(UserChars) To UBound(UserChars) - 1
		Result = Result & UserChars(i) & Chr(13)
	Next i
	Msgbox Result, 64, "Character Extractor"
End Sub
Function stringToCharacterArray(pIn As string)
REM If used in Calc the function will return an array as gotten from a row.
	Dim out() As String REM Empty array signalling empty input string. (Err. 538 in Calc)
	Dim u&, j&
	u = Len(pIn) - 1
	If u >= 0 Then 
		Redim out(0 To u) As String
		For j = 0 To u
			out(j) = Mid(pIn, j + 1, 1)
		Next j
	End If
	stringToCharacterArray = out
End Function