Ask Your Question

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

asked 2019-08-07 20:18:32 +0200

raretrack gravatar image


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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-08-07 21:54:31 +0200

Lupp gravatar image

updated 2019-08-07 22:04:23 +0200

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.

edit flag offensive delete link more


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

raretrack gravatar imageraretrack ( 2019-08-07 22:00:10 +0200 )edit

No matter.

Lupp gravatar imageLupp ( 2019-08-07 22:06:08 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-07 20:18:32 +0200

Seen: 76 times

Last updated: Aug 07