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


Comments

  • how to mark a particular cell to display converted data

    Posted by vishu on 08/06/2012 01:50pm

    Hi if i intend to use it for invoicing purpose how can i mark a particular cell to display the converted value???? Also if you can suggest how to mark as locked (protected only non blank cell in xls)

    Reply
  • Skip This code

    Posted by CGraaff on 07/23/2004 12:12pm

    Is there anyway to modify this code?  If not then I would skip this download. 
    
    Unfortunately in the Num2str function of the Num2str.dll parameter list the (number) is set as an integer and subsequently limited to VB's integer range and rounds up for anything with a decimal value greater than .50.
    
    For example if I pass 999.51 it will return 1000.  
    
    If I pass it a value greater then 32767 a numeric overflow error occurs.  
    
    Twelve and forty are misspelled and it's not entirely in English...  
    
    I would skip on this code and definetly NOT outsource my programming to India!!!

    Reply
  • Good Yaar

    Posted by Legacy on 06/02/2003 12:00am

    Originally posted by: Raj

    Good Yaar

    Reply
  • It is great

    Posted by Legacy on 03/30/2003 12:00am

    Originally posted by: AI Wei

    The program is great. However, it can't spell out the sen portion. FOr example: 400.45. It only displays four hundred. Instead of four hundred fourty five sen.

    Is there any ways to enhance it?

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

  • Wednesday, September 24, 2014 8:00 AM - 9:00 AM PDT According to a recent Forrester Research report, many companies are choosing low-code platforms over traditional programming platforms, due to the speed with which low-code apps can be assembled and tested. With customer-facing applications on the rise, traditional programming platforms simply can't keep up with the "short schedules and rapid change cycles" required to develop these applications. Check out this upcoming webinar and join Clay Richardson from …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds