Learning Thai With Excel on Mac

Since moving to Bangkok, my excuse for never properly (or even partially) learning a foreign language has been removed. So of course there had to be some way to manage my vocabulary lists etc. OK, so chuck them into Excel, but not very exciting or interactive. But then I discovered the iOS voice synthesiser. With a simple command at the Bash terminal you can make it speak in a varieties of tongues. For example

~$ say -v Kanya Hello World

Will repeat "Hello World" in a Thai accent. But then you also enter

~$ say -v Kayna สวัสดีโลก

to read out Thai script.

This opens up all sorts of possibilities for language learning by combining the above commands with lists of vocabulary managed in Excel.  Download it, using the links below, to see it in action (note, your virus checker may complain as this is an Excel file with VBA macros in it).

  • If using Excel 2011 (version 14.0 and later) click here to download. For this version, just download the file and use it straight away.

  • If using Excel 2016 (version 15.0 and later) click here to download. In Excel 2016, Microsoft/Apple have tightened the security of macros and the ability of them to interact with the operating system. Thus, you need to download this AppleScript file and copy it to: 
    /Users/<your-user-name>/Library/Application Scripts/com.microsoft.Excel/
    Make sure to spell the folder names exactly as specified. You will need to use Finder to create the above folder if it does not already exit. Apple makes your life difficult in this regard, as they don't really like users interacting with the filing system directly. In Finder select the 'Preferences' menu option, then select 'Sidebar' and make sure your local hard disk is ticked in the 'Devices' section. You should then be able to click 'Macintosh HD' in Finder to see its contents. Sometimes the Library folder is hidden. In this case, select the Finder 'View' menu option, then select 'Show View Options'. There should be a tick box at the bottom which shows the Library folder. This behaviour has been changed recently in the latest version of MacOS / OSX (currently Sierra), so you may need to search a bit.
    Note that when you use the spreadsheet for the first time it will probably ask for permission to write to certain folders. This is because the spreadsheet needs to create a command file in order to execute the 'say' command.

One of the problems with creating the Excel spreadsheet was the rather poor support for Unicode in the Mac version. Something which might not trouble the average English speaking monoglot. In order to successfully script the data from the Excel cells to the voice synthesiser, I had to knock up a UTF-8 encoding routine. It seems that although Excel strings are 2 bytes long (so, at best only partial support for Unicode, as some codepoints are >16 bits), various other parts of the system, including piping output via the MacScript command is still in ASCII land.

Here is the code of the UTF-8 function:

Private Function UTF8Encode(b() As Byte) As Byte()
' Input:
' b - byte array containing the word or phrase
' Output:
' Byte(), UTF8 byte array ready for writing to file     ' Function to convert a Unicode Byte array into a byte array that can be written to create a UTF8 Encoded file.     ' Note the function supports the one, two and three byte UTF8 forms.     ' Note: the MS VBA documentation is confusing. It says the String types only supports single byte charset     '           however, thankfully, it does in fact contain 2 byte Unicode values.     ' Wrote this routine as last resort, tried many ways to get unicode chars to a file or to a shell script call     ' but this was the only way could get to work.     ' RT Perkin     ' 30/10/2015          Dim b1, b2, b3 As Byte             ' UTF8 encoded bytes     Dim u1, u2 As Byte                  ' Unicode input bytes     Dim out As New Collection       ' Collection to build output array     Dim i, j As Integer     Dim unicode As Long          If UBound(b) <= 0 Then         Exit Function     End If                 For i = 0 To UBound(b) Step 2         u1 = b(i)         u2 = b(i + 1)         unicode = u2 * 256 + u1                  If unicode < &H80 Then             ' Boils down to ASCII, one byte UTF-8             out.Add (u1)         ElseIf unicode < &H800 Then             ' Two byte UTF-8             b1 = &H80 Or (&H3F And u1)             b2 = &HC0 Or (Int(u1 / 64)) Or ((&H7 And u2) * 4)             out.Add (b2) ' Add most significant byte first             out.Add (b1)         ElseIf unicode < &H10000 Then             ' Three byte UTF-8             ' Thai chars are in this range             b1 = &H80 Or (&H3F And u1)             b2 = &H80 Or (Int(u1 / 64)) Or ((&HF And u2) * 4)             b3 = &HE0 Or (Int(u2 / 16))             out.Add (b3) ' Add most significant byte first             out.Add (b2)             out.Add (b1)         Else             ' This case wont arise as VBA strings are 2 byte. Which makes some Unicode codepoints uncodeable.         End If                      Next     Dim outBytes() As Byte     ReDim outBytes(1 To out.Count)     For j = 1 To out.Count         outBytes(j) = CByte(out.Item(j))     Next          UTF8Encode = outBytes      End Function

 

And here is the code for the speech routine (Excel 2011 version):

Private Function Speak(word As String, voice As String, speed As Long, out As String) As Boolean
' Input:
' word - word to say
' voice - the voice name for the 'say' command
' speed - the speed of playback, 0 for default
' out - if present, the name of a file to store the audio file (rather the playing the audio)
' Output:
' Boolean, success indicator
' Function to use Mac's 'say' command to speak a word or phrase.
' This works by generating a script file containing the 'say' command, and then executing the script.
' It was not possible to directly execute the 'say' command becuase the MacScript (or something in the pipe)
' does not handle Unicode, so for Thai, this does not work.
' RT Perkin
' 31/10/2015


Dim b() As Byte
Dim UTF() As Byte
Dim retCode As String
Dim speedCmd As String
Dim outCmd As String
Speak = True

If voice = "" Then
MsgBox "Enter a voice name on the #TOOLS tab before using the speech function", vbCritical
Speak = False
Exit Function
End If

' Convert it to UTF8
b = EscapeWord(word)
UTF = UTF8Encode(b)

' Delete any previous file, as file is not replaced on Binary write
On Error Resume Next ' Disable, as will raise error if file absent
retCode = MacScript("do shell script ""rm /Users/Shared/UTF8TalkFile.sh""")
On Error GoTo 0

' Check parms
If speed <> 0 Then
speedCmd = " -r " & speed
End If
If out <> "" Then
outCmd = " -o " & out
End If

' Write the text to a command script
' Note: the Open command seems to require the old Mac colon separator for path names
Open "Macintosh HD:Users:Shared:UTF8TalkFile.sh" For Binary As #1
Put #1, , "say -v " & voice & speedCmd & outCmd & " "
Put #1, , UTF
Close #1

' Use MacScript to execute the BASH script containing created above
' First set execute permissions.
retCode = MacScript("do shell script ""chmod +x /Users/Shared/UTF8TalkFile.sh""")
' Note that some strings will confuse the 'say' command. Catch errors.
On Error GoTo BadInput
retCode = MacScript("do shell script ""/Users/Shared/UTF8TalkFile.sh""")
Exit Function

BadInput:
MsgBox "Illegal characters in input, eg brackets etc", vbExclamation
Speak = False
End Function