Converting Values from Numeric Currency to Word Currency in Microsoft Excel

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Environment: Visual Basic

Introduction

This project contains three files and a conversion demonstration of numeric currency to string currency with the help of a macro and Visual Basic in Microsoft Excel. No code writing is required, but Num2str.dll is a Class that is created in VB 6.0. After downloading all three files (they’re all in the one zip file), please copy all the files to C:Num2Str folder (create a folder named Num2Str under the C drive before extracting). Now, the following three files are copied to Num2Str folder on your hard drive at C:Num2Str.

• Num2str.dll—the main file contains functions
• ExcelMacro.txt—this text file contains the code for the macro
• Readme.txt—you are currently reading an HTML version of this file

Procedure

1. Open MS Excel and create new WorkBook1.
2. Follow these menu choices:
   MainMenu –>TOOLS –> Micro –> Record New Micro
   MainMenu –>Macro –> Stop Recording Micro
   MainMenu –>Macro –> Visual Basic Editor
3. Now, the workbook contains a macro by the name of Micro1(default). Delete all code data, whatever was written under micro1; the code window should be empty.
4. In VISUAL BASIC EDITOR, choose MainMenu TOOLS –> REFERENCES.
5. In the Reference VBA Project window, follow these steps:
   1. Click Browse.
   2. Select Num2Str.dll (which has copied in your hard disk in folder C:Num2Str).
   3. Click the OK button.

6. Click on Module1 in the Project Explorer.
7. From the Main menu, choose INSERT –>File.
8. Now click on Module1 and there will be coding, as show below:

------------------------------------------------------------
  Dim NS As New Num2Str
  Sub Macro1()
  For Each c In Worksheets("sheet1").Range("A1:A65000")
    If c.Value = " " Then
    c.Value = " "
    Else
    c.Value = NS.Num2Str(c.Value)
    End If
  Next c
  End Sub

------------------------------------------------------------

Note: the scope of the range, which is (A1:A65000), can be changed per your requirements.

9. Choose Main Menu –> File –> Close and Return to Microsoft Excel, or press Alt + Q.
10. Now write any numeric value. Enter any numeric value less then 10000000000 on sheet1 in column A.
11. Choose Main Menu –> Tools –> Micro –> Micros (Alt + F8).

The numeric value will be replaced with its equivalent currency in words.

Downloads

Download source code and demo files – 10 Kb