title: Removing characters with .Replace in VBA for Excel
body: <p>The following function was given to me via an answer that I asked earlier today.</p>
  
  <p>What I'm trying to do is to remove a character from a string in Excel using VBA. However, whenever the function runs, it ends up erasing the value stored and returning a <code>#!VALUE</code> error. I cannot seem to figure out what is going on. Anyone mind explaining an alternative:</p>
  
  <pre><code>Function ReplaceAccentedCharacters(S As String) As String
      Dim I As Long
  
  With WorksheetFunction
  For I = 1 To Len(S)
      Select Case Asc(Mid(S, I, 1))
          ' Extraneous coding removed. Leaving the examples which 
          ' do work and the one that is causing the problem.
          Case 32
              S = .Replace(S, I, 1, "-")
          Case 94
              S = .Replace(S, I, 1, "/")
  
          ' This is the coding that is generating the error.
          Case 34
              S = .Replace(S, I, 1, "")
          End Select
  
      Next I
  
  End With
  
  ReplaceAccentedCharacters = S
  
  End Function
  </code></pre>
  
  <p>When the string contains a <code>"</code> (or character code 34 in Decimal, 22 in Hexadecimal... I used both) it is supposed to remove the quotation mark. However, instead, Excel ignores it, and still returns the <code>"</code> mark anyway.</p>
  
  <p>I then tried to go ahead and replace the <code>.Replace()</code> clause with another value.</p>
  
  <pre><code>        Case 34
              S = .Replace(S, I, 1, "/")
          End Select
  </code></pre>
  
  <p>Using the code above, the script indeed does replace the " with a /. </p>
  
  <p>I ended up finding the following example here in Stack Overflow:
  <a href="https://stackoverflow.com/a/7386565/692250">https://stackoverflow.com/a/7386565/692250</a></p>
  
  <p>And in the answer given, I see the same exact code example similar to the one that I gave and nothing. Excel is still ignoring the quotation mark. I even went so far as to expand the definition with curly braces and still did not get anything.</p>