Converting Values from Numeric Currency to Word Currency in Microsoft Excel

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