title: Format a cell as arbitrary currency regardless of locale, using VBA
body: <p>This is really bugging me as it seems pretty illogical the way it's working.</p>
  
  <p>I have a macro to format a cell as a currency using a bit of code to obtain the currency symbol.</p>
  
  <p>Here is the code involved: </p>
  
  <pre><code>Dim sym As String
  sym = reportConstants(ISOcode)
      'Just use the ISO code if there isn't a symbol available
      If sym = "" Then
      sym = ISOcode
      End If
  With range(.Offset(0, 3), .Offset(3, 3))
  .NumberFormat = sym &amp; "#,##0;(" &amp; sym &amp; "#,##0)"
  Debug.Print sym &amp; "#,##0;(" &amp; sym &amp; "#,##0)"
  End With
  </code></pre>
  
  <p><code>reportConstants</code> is a dictionary object with currency symbols defined as strings. E.g. <code>reportConstants("USD") = "$"</code>. This is defined earlier in the macro.</p>
  
  <p>When the macro runs it gets the ISO code and should then format the cell with the corresponding currency symbol.</p>
  
  <p>When I run it in one instance the ISO code is "USD" - so <code>sym</code> is defined as <code>"$"</code> - but it still formats the cell with a pound sign (£). When I <code>debug.print</code> the format cell string it shows <code>$#,##0;($#,##0)</code> so, as long as I got my syntax correct, it should use a dollar sign in the cell. But it uses a £ sign instead. (I am running a UK version of excel so it may be defaulting to £-sign, but why?)</p>
  
  <p>Any help greatly appreciated.</p>