The other day I posted about a Google Apps Script version of a Pinyin converter. I usually create google apps script versions of VBA functions, but this time, I’m creating a VBA version from GAS. You can find the functions and example data shared in this Google Spreadsheet, and download the Excel version in pinyin.xlsm from the Downloads section of this site.

What is pinyin

Pinyin is a system for representing Chinese phonetically, and is used to romanize chinese script for publication or learning, and in particular for entry with a standard keyboard. You will notice in the example below that many of the vowels are marked with ‘diacritics’. These are markers used to indicate the ‘tone’ to apply to the syllable

Wǒ dǎsuan cānjiā wǎngqiú bǐsài, kěshì bùzhīdào yīnggāi xiān zhǎo shéi shāngliang.

Coloring

There are coloring systems used to make the tones of the syllables more obvious.

Wǒ dǎsuan cānjiā wǎngqiú sài, shì zhīdào yīnggāi xiān zhǎo shéi shāngliang.

Entering the tone markers on a keyboard would be a challenge, so another method is to use tone numbers, 1-4, each of which represent one of the possible tones of mandarin. Here’s the same thing using tone numbers.

Wo3 da3suan can1jia1 wang3qiu2 bi3sai4, ke3shi4 bu4zhi1dao4 ying1gai1 xian1 zhao3 shei2 shang1liang.

Excel implementation

This is very similar to the GAS implementation described here, with the two main functions being

' accents a pinyin string with optional html colorizing
' @param {string} inputString the string to be converted
' @param {number=} optColoringType - 0 = no coloring(default), 1 = return html with coloring options given
' @param {string=} optColors - a comma separated string of colors to use for tones 1-5  (5=no tone)- a default will be used if omitted
' return {string} the converted string (including colorization if required)
'
Public Function pinyinToToneMarks(inputString As String, _
    Optional optColoringType As Long = COLORTYPENONE, Optional optColors As String = vbNullString) As String
  
  pinyinToToneMarks = pinyin(inputString, optColoringType, optColors, False)
End Function

and

'
' replaces accents in pinyin string with tone numbers and adds optional html colorizing
' @param {string} inputString the string to be converted
' @param {number} optColoringType - 0 = no coloring(default), 1 = return html with coloring options given
' @param {string} optColors - a comma separated string of colors to use for tones 1-5  (5=no tone)- a default will be used if omitted
' return {string} the converted string (including colorization if required)
'
Public Function pinyinToToneNumbers(inputString As String, _
    Optional optColoringType As Long = COLORTYPENONE, Optional optColors As String = vbNullString) As String
    
    pinyinToToneNumbers = pinyin(inputString, optColoringType, optColors, True)
End Function

They are designed to be used as custom functions – typically called something like =pinyinToToneMarks(A2)

The code

It was a little more complicated to implement this in VBA, since regex is heavily used for parsing and VBA is not so good as javaScript for that or object handling. The code is in pinyin.xlsm in the Downloads section or you can pull it directly in your project using gistThat

Here is the reference to load directly using gistThat
gtDoit “6415598”, True

For help and more information join our forum, follow the blog, follow me on twitter