Visual Basic for Applications:
Removing quotes from a string

How to:

In VBA, there are multiple approaches to removing quotes from a string. Here’s a straightforward example using the Replace function, which searches for a specific substring (in this case, a quote) within a string and replaces it with another substring (an empty string if removing).

Sub RemoveQuotesExample()
    Dim originalString As String
    originalString = "'This' is a ""test"" string."
    
    ' Remove single quotes
    originalString = Replace(originalString, "'", "")
    
    ' Remove double quotes
    originalString = Replace(originalString, Chr(34), "")
    
    Debug.Print originalString 'Output: This is a test string.
End Sub

Note that for double quotes, we use Chr(34) because a double quote is ASCII character 34. This is necessary since double quotes are also used for denoting string literals in VBA.

For more nuanced scenarios where quotes might be part of necessary formatting (e.g., inside a quoted word), more sophisticated logic, perhaps involving Regex or parsing character by character, might be required.

Deep Dive

VBA, being a staple in automating tasks within the Microsoft Office suite, offers a rich set of string manipulation functions, with Replace being one of the most frequently used. This function, however, only scratches the surface of what can be achieved with VBA in terms of string manipulation.

Historically, VBA adopted from its predecessors an emphasis on simplicity for office automation tasks, hence the straightforward implementation of functions like Replace. However, for modern programming tasks, especially those involving complex string manipulations or sanitations, VBA might show its limitations.

In such cases, programmers might resort to combining VBA with regular expressions (via the VBScript_RegExp_55.RegExp object) for more flexibility and power in parsing and manipulating strings. This approach, however, introduces additional complexity and requires a solid understanding of regex patterns, which might not be suitable for all users.

Despite its limitations, VBA’s Replace function efficiently covers many common scenarios involving the removal of quotes from strings. It serves as a quick and easy solution for most string manipulation needs without diving into the more complex regex territory. For those reaching the limits of what Replace and other basic string functions can do, exploring regex within VBA or considering a more robust language tailored to complex string operations might be the next best steps.