A string is a BSTR
The following example shows that internally a VBA string is really a BSTR. The example uses the memory accessing functions
GetMemX_ which can be found in the
VBA runtime library.
option explicit
sub main() ' {
dim str as string
dim addr as longPtr
dim strLenBytes as long
dim addrChar as longPtr
dim char as integer
str = "twenty-one characters"
addr = strPtr(str)
'
' The length of the string in bytes is stored in the four bytes
' prior to the string address:
'
strLenBytes = GetMem4_(addr - 4)
debug.print "strLenBytes = " & strLenBytes
'
' Iterating over each character in the string. Since each character is
' two bytes wide, we use "step 2"
'
for addrChar = addr to addr + strLenBytes step 2 ' {
'
' Get the numeric value of a character:
'
char = GetMem2_(addrChar)
'
' Print the character (using chr$()) and its
' numeric value:
'
debug.print "chr$(char) = " & chr$(char) & " | " & char
next addrChar ' }
end sub ' }
Special characters
The following VBA script tries to demonstrate the difficulties in VBA when dealing with «special characters».
The example declares two strings,
kappa and
smiley and tries to assign the respective characters to these strings (
"κ" and
"☺"). It turns out that the
VBA editor is not capable of storing any character. (I
guess that it can only store those of the system default locale). Thus, these characters are assigned a question mark.
It then uses
chrW() to create the desired characters from a Unicdoe
code point. The
ascW function confirms that the correct values were assigned to strings. However, if used with
msgBox or
debug.print, VBA still is not capable of displaying the correct character and still prints the character as question mark.
Next, the example checks if at least these characters can be written into a file. When the file is created with the
open statement, it turns out, it is not possible, the characters are again written as question marks.
option explicit
sub special_characters() ' {
dim kappa as string
dim smiley as string
kappa = "κ"
smiley = "☺"
debug.print("Smiley: " & smiley & " (" & hex(ascW(smiley)) & "), kappa = " & kappa & " (" & hex(ascW(kappa)) & ")")
msgBox ("Smiley: " & smiley & ", kappa = " & kappa)
kappa = chrW(&h03BA) ' Unicode character GREEK SMALL LETTER KAPPA
smiley = chrW(&h263A) ' Unicode character WHITE SMILING FACE
debug.print("Smiley: " & smiley & " (" & hex(ascW(smiley)) & "), kappa = " & kappa & " (" & hex(ascW(kappa)) & ")")
msgBox ("Smiley: " & smiley & ", kappa = " & kappa)
'
' Trying to write a file with the special characters
'
open environ("TEMP") & "\special-characters.txt" for output as #1
print #1, "Kappa: " & kappa
print #1, "Smiley: " & smiley
close #1
'
' Using ADODB to write a file:
'
dim adoStream as new adodb.stream
adoStream.charset = "utf-8"
adoStream.open
adoStream.writeText "Kappa: " & kappa , streamWriteEnum.stWriteLine
adoStream.writeText "Smiley: " & smiley, streamWriteEnum.stWriteLine
adoStream.saveToFile environ("TEMP") & "\special_characters.utf8"
adoStream.close
end sub ' }