Converting Values from Numeric Currency to Word Currency in Microsoft Excel

Friday Jan 30th 2004 by Yogendra Rawat
Share:

Use three files and a conversion demonstration of numeric currency to string currency with the help of a macro and Visual Basic in Microsoft Excel. (Added: 20-Nov-2002)

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
Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved