VBA & Macro Problems

How to Convert Numbers to UK Pounds and Pence in Excel VBA

WPS Content Manager Oct 9, 2026 869 views

Question details

The user needs to convert numerical values into written UK currency words (Pounds and Pence) using VBA, because Excel lacks a built-in function for this.

Convert Numbers to UK Pounds and Pence in Excel VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating financial reports, invoices, or automated receipts where numbers must be written out in UK English currency words.
Observed behavior: Excel does not have a native SpellNumber function, requiring users to implement and modify custom VBA examples to accommodate UK terminology instead of US Dollars.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that you save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to preserve the VBA code.

Solution 1Recommended

Create and Adapt a SpellNumberUK VBA Function

Implement a custom VBA macro and modify the standard US Dollar script to use UK currency terms like Pounds, Pound, Pence, and Penny.

Since Excel does not feature a built-in SpellNumber function for UK currency, you must adapt standard VBA code blocks originally designed for Dollars and Cents. Helper functions such as GetHundreds, GetTens, and GetDigit usually remain unchanged, but the primary function logic requires targeted text replacements.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Insert a New Module

Click on 'Insert' in the top menu bar and select 'Module'. This will create a blank code window for your new function.

Paste and Modify the Code

Paste your standard SpellNumber VBA code into the module. Carefully locate the text strings containing 'Dollars', 'Dollar', 'Cents', and 'Cent'. Replace these with 'Pounds', 'Pound', 'Pence', and 'Penny' respectively. You can rename the main function to 'SpellNumberUK'.

Check for Syntax Errors

Review the pasted VBA code for corrupted formatting, such as smart quotation marks, broken comparison operators, or invalid ampersands. Replace any smart quotes with standard straight quotes.

Apply the Formula

Close the VBA editor and return to your worksheet. Type `=SpellNumberUK(A2)` in a cell to convert the numeric value inside cell A2 into UK Pounds and Pence.

Create and Adapt a SpellNumberUK VBA Function

Macro Security: You may need to adjust your Macro Security settings in the Trust Center (File > Options > Trust Center) to allow custom VBA functions to run properly.

Advanced Macro Support

Easily Manage VBA Macros with WPS Office

WPS Office provides excellent support for macros and custom VBA scripts, allowing you to easily run and manage custom functions like SpellNumberUK for your financial documents.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the Options menu, and ensure the Developer tab is visible on your ribbon.
2. Launch the Macro Editor: Navigate to the Developer tab and click on 'Macros' or press ALT + F11 to open the VBA editor.
3. Add the UK SpellNumber Script: Insert a new module, paste your modified UK currency VBA code, and close the editor.
4. Use the Custom Function: Type `=SpellNumberUK(A1)` in any cell to instantly convert the number into Pounds and Pence.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) formats and standard VBA scripts.Built-in Developer tab designed for intuitive macro management and coding.Highly efficient for processing complex financial templates and automated invoices.Lightweight architecture ensures fast performance even with heavy macros.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the =SpellNumber() formula return a #NAME? error?

This error appears because SpellNumber is not a default, built-in function in Excel. You must manually add the VBA macro code to a module in your workbook before the formula can be recognized.

Can I simply use the standard US Dollar SpellNumber code for UK currency?

Not without modification. You need to edit the VBA code to replace references to 'Dollars' and 'Cents' with 'Pounds' and 'Pence', and account for singular forms like 'Pound' and 'Penny'.

Do I need to save the file in a specific format after adding the VBA code?

Yes, you must save your file as an Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, the VBA code will be stripped out and lost.

How do I fix corrupted quotation marks in VBA code copied from the internet?

When copying code from web pages, straight quotes are often replaced by smart (curly) quotes, which VBA does not recognize. Open the VBA editor, press CTRL + H to open the Replace tool, and replace all smart quotes with standard straight quotes.

Excel & Data Problems