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:
  9. ------------------------------------------------------------
      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.

  10. Choose Main Menu –> File –> Close and Return to Microsoft Excel, or press Alt + Q.
  11. Now write any numeric value. Enter any numeric value less then 10000000000 on sheet1 in column A.
  12. 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

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read