CODEWITHSUNDEEP
vbaexcel
1937827
1937827

Reputation: 97

Euro symbol not showing (VBA)

I'm trying to write an If statement that checks an input (a currency) and then assigns the correct symbol to the dollar amount. My code is below, every currency works except for the euro, which only works if the dollar amount is letters, and not numbers. Am I missing something obvious?

If Note_Currency = "" Then
    Note_Denomination = "$" + Note_Denomination
ElseIf Note_Currency = "EUR" Then
    Note_Denomination = "€" + Note_Denomination
ElseIf Note_Currency = "JPY" Then
    Note_Denomination = "¥" + Note_Denomination
ElseIf Note_Currency = "GBP" Then
    Note_Denomination = "£" + Note_Denomination
Else
    Note_Denomination = "$" + Note_Denomination
End If

Upvotes: 1

Views: 4523

Answers (3)

evandromr
evandromr

Reputation: 11

If someone finds this answer years later, like I did, use chrW(128) instead of chr(), that forces Unicode character. If it matches what Excel expects for a currency format it will still recognize the cell as currency.

For example: Range("A1:A100").NumberFormat = " $" & ChrW(128) & " #,###.00";

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

Okay so I figured it out - kind of. I tried your sub in Excel and the same thing happens for me. Using the symbol and Chr(128) shows just a blank cell.

What I found worked is that if you know your range that will have these numbers, change the cell's format type to Text (@):

Range("A1:A100").NumberFormat = "@"

Then, when you run your code, the Euro symbol shows up if you use Note_Denomination = Chr(128) & Note_Denomination.

SO, it looks like the reason wasn't the code, but the cell formatting.

Upvotes: 2

Charlie
Charlie

Reputation: 2231

It VBA it's a bad idea to concatenate a string and a number using +. Instead you should use &.

http://www.tutorialspoint.com/vba/vba_concatenation_operators.htm

Upvotes: 2

Related Questions