How do you use " (double quotes) in a formula in OpenOffice VBA? For instance with Replace()

Question

I am looking to replace double quotes, ", with backslash escaped double quotes - \".

I read online that you can use double quotes in VBA, if you use two double quotes inside the main double quotes. But, this didn't seem to work for me. For instance I tried the following code:

Function ADDSLASHES(InputString As String)
    NewString = Replace(InputString, "\", "\\")
    NewString = Replace(NewString, "'", "\'")
    NewString = Replace(NewString, """", "\""")
    ADDSLASHES = NewString
End Function

When I tested it, this function successfully substituted the single backslash and the single quotes, but not the doule quotes.

I also read that you can use CHR(34), and elsewhere to use CHR(147). But this too didn't work. I tried the following lines:

NewString = Replace(NewString, CHR(34), "\"+CHR(34))
NewString = Replace(NewString, CHR(147), "\"+CHR(147))

But testing it out with a cell that had double quotes did not work. Am I doing something wrong? How might I use double quotes with the Replace() function?

Answer 1

When I entered a"b in a cell, Calc converted it to a right double quote, not a left one. Adding this line made it work:

NewString = Replace(NewString, CHR(148), "\"+CHR(148))

Be aware that x94 (decimal 148) is an extended ASCII encoded character, which is something I would avoid at all costs. It's strongly recommended to only use the first 128 characters as ASCII and to use Unicode for everything else.

The Unicode value for a right double quotation mark is U+201D. Sadly, apparently LibreOffice Basic does not have a native way to work with such values. There is ChrW but that requires the VBA compatibility option. Another method is to call the UNICODE() spreadsheet function from Basic, but that is cumbersome.

My preference: Don't use Basic for anything important. LibreOffice macros can be written in Python instead, which has strong Unicode support.

EDIT:

One thing I forgot to mention yesterday: Select a quotation mark in the formula bar and press Alt+x to find out what it really is. This will convert it to the Unicode value and then back again.

EDIT 2:

That's correct—Alt+X only works in LibreOffice, not AOO. Also for some reason, the extended ASCII code above doesn't seem to work in AOO. Maybe that's not a bad thing. Anyway, here is the Unicode spreadsheet function access approach, and it works for me in both AOO and LO.

fa = createUnoService("com.sun.star.sheet.FunctionAccess")
ch = fa.CallFunction("UNICHAR", Array(CLng("&H201D"))
NewString = Replace(NewString, ch, "\"+ch)

If this doesn't work, then you probably have something else in the cell. To figure out what it is, you could install LibreOffice. Or there are lots of other ways; most often I use GVim text editor. Also I just now googled and found https://www.branah.com/unicode-converter where you can paste some text and see the actual UTF-16 hexadecimal values.