如何用Excel VBA设置单元格印度卢比(₹)货币格式及解决识别问题

Hey there! Let's walk through how to set up the ₹ currency format in Excel using VBA, and fix that frustrating issue where the symbol turns into a question mark.

Part 1: Applying ₹ Currency Format to Cells #

There are a few straightforward ways to format cells with the Indian Rupee symbol via VBA. Here are the most common methods:

1. Direct Custom Number Format #

You can define a custom number format string that includes the ₹ symbol directly:

' Format a single cell with ₹ and 2 decimal places
Range("A1").NumberFormat = "₹#,##0.00"

' Format a range using the Indian numbering system (lakhs/crores)
Range("A1:A10").NumberFormat = "₹#,##,##,##0.00"

2. Use Excel's Built-in Currency Style (If Locale is Set) #

If your system's regional settings are configured for India, you can use the built-in "Currency" style, which will automatically use the ₹ symbol:

' Apply default currency style (uses system's default currency symbol)
Range("A1").Style = "Currency"

Part 2: Fixing the ₹ → Question Mark Problem #

If the ₹ symbol shows up as a ? after running your code, this is almost always an encoding or font support issue. Try these fixes in order:

1. Use the Unicode Character Code Instead of Direct Typing #

Instead of pasting the ₹ symbol into your code, use its Unicode value (ChrW(&H20B9)). This ensures the symbol is recognized correctly regardless of encoding:

' Define the ₹ symbol using its Unicode code
Dim rupeeSymbol As String
rupeeSymbol = ChrW(&H20B9)

' Apply the format using the Unicode string
Range("A1").NumberFormat = rupeeSymbol & "#,##0.00"

2. Save Your VBA Project with Unicode Support #

Encoding mismatches often happen if your VBA project isn't saved to store Unicode characters:

• Open the VBA Editor (press Alt + F11)
• Go to File > Save [Your Workbook Name]
• Click the Tools dropdown in the save dialog → select General Options
• Check the Save Unicode Data box, then click OK and save the workbook.

3. Ensure the Cell's Font Supports ₹ #

Older or specialized fonts might not include the ₹ symbol. Switch to a modern font like Calibri or Arial that supports it:

With Range("A1")
    .Font.Name = "Calibri" ' Or "Arial", "Segoe UI"
    .NumberFormat = "₹#,##0.00"
End With

4. Verify Your Regional Settings #

Make sure your Windows regional settings are set to India, which helps Excel natively recognize the ₹ symbol:

• Open Control Panel > Region > Formats
• Select English (India) from the dropdown menu
• Go to Additional settings > Currency and confirm the symbol is set to ₹

内容的提问来源于stack exchange，提问作者dhanya