Thursday, March 4, 2010

Excel VBA : Deploy Macros Found Elsewhere

Today I am sharing various ways to add macros found elsewhere to a Excel file where that macro is to be deployed. Will be covering 6 ways to add macro code to your excel file.

1. Excel VBA : Add code to a Regular Module
2. Excel VBA : Add code to WorkBook Module
3. Excel VBA : Add code to Worksheet Module
4. Excel VBA : Copy Module from Existing Workbook
5. Excel VBA : Copy Forms from Existing Workbook
6. Excel VBA : Import code from .bas file

Excel VBA : Add code to a Regular Module

-Copy code from the source
-Open the workbook where you need to deploy this code
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Click Insert > Module (Shortcut ALT+I+M)
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is

See screencast below for Alternate method using right click menu through mouse.
Excel VBA : Add code to a Regular Module

Excel VBA : Add code to WorkBook Module

Some macros are event based macros and get activated as soon as particular event happens. Events which are universally applicable to the entire workbook are called workbook events. These macros will run once a particular event happens in a particular workbook. Typical example of such macros are Auto Open / Auto Close macros

-Copy code from the source
-Open the workbook where you need to deploy this
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Double Click on ThisWorkbook object
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is

See screencast below
Excel VBA : Add code to WorkBook Module

Excel VBA : Add code to Worksheet Module

Some of event based macros get activated as soon as particular event happens on a particular worksheet within the workbook. These are the macros which will run on a event in particular worksheet. Typical example of such macros are Worksheet Select / Worksheet Change macros

-Copy code from the source
-Open the workbook where you need to deploy this
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Double Click on Worksheet object (Choose particular worksheet where you want to deploy the code)
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is

See screencast below
Excel VBA : Add code to Worksheet Module

Excel VBA : Copy Module from Existing Workbook

You may have the code in existing workbook (Source) with you or recently downloaded from Internet. Following steps will deploy it to new workbook (Target)
-Open both the Workbooks (Source and Target)
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Select the source module
-Drag it to the Target workbook

See screencast below
Excel VBA : Copy Module from Existing Workbook

Excel VBA : Copy Forms from Existing Workbook

Steps and requirement is similar to copy code from existing workbook

-Open both the Workbooks (Source and Target)
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Select the Form to be copied
-Drag it to the Target workbook

See screencast below
Excel VBA : Copy Forms from Existing Workbook

Excel VBA : Import code from .bas file

Some time you may get code in downloadable file with extention BAS. This is visual basic file .bas , you can import this to your workbook.

-Open your Workbook
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Click File > Import (CTRL+M)
-Choose downloaded VB file (*.BAS , *.FRM, *.CLS)
-Click Open

See screencast below
Excel VBA : Import code from .bas file

55 comments:

  1. Excellent Job... but how do you do it on Excel for Mac? regards, Amin. My email: amintera@gmail.com

    ReplyDelete
  2. Hi Sir,
    This is jacintha. Excellent works done. but i have not able convert numbers into words. kindly help me ASAP. my email id is Jacintham@shoppersstop.com

    ReplyDelete
  3. Hi Sir,

    i have visited your site & read all the procedures for converting numbers in to words in excel.

    But i have failed to do it on my own, even after the following procedures.

    Kindly help me in this.

    My Email ID - Vishwasrao.vishal@gmail.com
    or vsvishwasrao@gmail.com

    Vishal

    ReplyDelete
    Replies
    1. Hi Sir,

      i have visited your site & read all the procedures for converting numbers in to words in excel.

      But i have failed to do it on my own, even after the following procedures.Becouse error comes like function not defined etc .plz give me the correct code for it

      Kindly help me in this.

      My Email ID - bikashsingh143@gmail.com

      Delete
    2. Please help me with this formula as m not getting how to do it in my excell

      Delete
  4. Sir,
    How can we make this function to work in all worksheets of excel.

    ReplyDelete
  5. i am not able to do converting number to words can u please help me my mail id is navagevinay@gmail.com

    ReplyDelete
  6. dear sir,

    How can we make this function to work in all worksheets of excel.

    kindly help me ASAP.
    mukesh.dabhani.4u@gmail.com

    ReplyDelete
  7. i am not able to do converting number to words can u please help me my mail id is
    vijay.chaudhari@ibibo.com

    ReplyDelete
  8. i am not able to do converting number to words can u please help me my mail id is :
    cute.miao@hotmail.com

    ReplyDelete
  9. There appears to be some issue with 64bit Excel... upon loading up the downloaded example I'm presented with a Compile Error...

    "The code in this project must be updates for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."

    ReplyDelete
  10. Hi sir,

    its best......its working......but in some numbers its not correct..for example--- 100,250.00
    convert in -Ruppees One Lakh Thousand Two Hundreds Fifty Only.

    plz help me on this error....
    my id is---momin.rauf@gmail.com

    ReplyDelete
  11. Thank you very much.
    Now i want to conver Rs.100000(One lakh only)
    how can i?

    ReplyDelete
  12. hi,

    i read all web article ,but i failed to convert digits into words. so how can we do this. i follow each step & information given in this web but unfortunately i failed.
    Any body help me plz e-mail me, my email address as given below:-
    gulfraz_tcs@hotmail.com
    Any body help me... plz ... plz... awaiting for response.

    ReplyDelete
  13. hi sir,

    i'm working in Govt. sector in A.P. thank u for helping us in excel. i have tried Ur VB module code to convert number to currency but unable to run that. so please send me the excel file with that function to convert number to currency.

    thank you!

    ReplyDelete
  14. Hi sir,

    Realy good and faboulas am using well

    ReplyDelete
  15. How can we add spellcurr function without adding as it as a macro. As there is a sum function , can it be aailable to all excel work books

    ReplyDelete
  16. i am not able to do converting number to words IN MS OFFICE 2003

    can u please help me my mail id is

    SERVICEADMIN@TEJASWIMOTORS.NET

    ReplyDelete
  17. dear sir
    this is excellent, but i do for one time,
    i want to do this any time when i open excel worksheet but it failed to run this formula if i do this job again that time is OK. please give me a solution my mailid is narotttamsaha2000@gmail.com
    I waiting for your reply
    Thanks,

    ReplyDelete
  18. when i enter whole in module there is problem with please sathya.patro@gmail.com.

    please send the programme step by step.

    pls...............
    regards,
    Sathya

    ReplyDelete
  19. hi yogesh
    m using spell currency but every time i open the particular file its shows #NAME? error and again i have to repeat it the same process to copy the module and paste it but yeah 1 thing it prompts me 1 message which read:- the following features cannot be save in macro-free workbooks :VB project
    to save file with these features click no and the choose a macro -enabled file type in file type list
    to continue saving as a macro - free workbook click yes"

    Please guide for the same and enable

    ReplyDelete
  20. Its really good, but it is giving value in millions not in lacs

    ReplyDelete
  21. Its really good, but it is giving value in millions not in lacs

    ReplyDelete
  22. hi, after using VB it is working when the sheet is opened, and it is not working after the Excel Book is closed and reopened.
    Plese guide me how to get this permanent

    ReplyDelete
  23. sir i cant convert number above 1 lakh its nt working for amount above one lakh

    ReplyDelete
  24. sir, thanks!
    this is a excellent job.

    ReplyDelete
  25. namaste sir,
    I wish to add this code permanent in ms excel.
    how we can do it?
    my email id is sikander.mca@gmail.com

    ReplyDelete
    Replies
    1. copy the code from this site and open an excel press alt+f11 insert a module and paste this code in that module saveas your excel file as excel add-in mention in save type: now map your add-in where this file is located in your drive. after that now use the formula by typing =spellcurr(a1)

      Delete
  26. Swetha

    Sir I wish to add this code in Excel Program in my system.
    Kindly help me.
    My E-mail ID is-swetha98457@gmail.com

    ReplyDelete
    Replies
    1. copy the code from this site and open an excel press alt+f11 insert a module and paste this code in that module save excel file on your drive which you want. now use the formula by typing =spellcurr(a1)

      Delete
  27. copy the code from this site and open an excel press alt+f11 insert a module and paste this code in that module saveas your excel file as excel add-in mention in save type: now map your add-in where this file is located in your drive. after that now use the formula by typing =spellcurr(a1)

    ReplyDelete
  28. thanks... this working fine, but please confirm whether every time we'll have to paste VB??
    Kindly reply me on gagansatija@gmail.com.

    ReplyDelete
  29. Dear Sir Iam Sending In One Excel File Iam Not Able To Acess Number To Word Please Help Me My Mail Id Is santosh1133@gmail.com

    ReplyDelete
  30. Sir
    I am not able to convert the numaric values in words on excel.So this is to request you to help on my Email.That is
    shishirdixit03@gmail.com
    Shishir

    ReplyDelete
  31. hello can i do this in microsof word??? in need macro to convert numbers to word and the option to changelanguage text my email is evripos@gmail.com

    ReplyDelete
  32. I want to suppress print but not display.. is this possible?

    ReplyDelete

  33. Dear Yogesh Gupta:

    Not support

    Ex.

    100000
    Rupees One Lac Only
    110000
    Rupees One Lac Ten Thousand Only

    ReplyDelete
  34. please give me number in word for ecel file (formula)

    ReplyDelete
  35. please give me rupees in word for Ecel file formula

    ReplyDelete
  36. Dear Sir,

    I am not able to convert the numaric values in words on excel.So this is to request you to help on my Email.That is
    vshinde051@gmail.com

    Vikram shinde.

    ReplyDelete
  37. I am not able to convert the numaric values in words on excel.So this is to request you to help on my Email.That is
    manoj.mapare@gmail.com

    ReplyDelete
  38. i m can not convert amount in word so plz sent me more information on my Email ID: pankajbarot21@yahoo.in

    ReplyDelete
  39. I am not able to convert the numeric value in word format in my excel file. so plz elaborate the same in detail for MS office 2013 or mail me on sgoyal.2907@gmail.com

    ReplyDelete
  40. this is not work in new file of excel

    i have to do same process , means i have to make again this macro

    ReplyDelete
  41. I used your method of hiding cell content only while printing. Its best. But, there is a problem. How to save that code forever? Cause when I close file, the cell content again become printable. Please help me.

    ReplyDelete
  42. I am confused...where can I download a VBA project(date picker) and also the .bas file?

    ReplyDelete
  43. how can i put star mark in amount

    ReplyDelete
  44. how can i in put star mark in amount

    ReplyDelete
  45. Sir
    I am not able to convert the numeric values in words on excel. So this is to request you to help on my Email: jahir113@gmail.com please how can use excel convert the number at Word.
    Jahir from Bangladesh.

    ReplyDelete
  46. Sir, I have selected all duplicate values now i have to delete both values marked red and its duplicate. help me out its urgent

    ReplyDelete
  47. Sir,
    I want to apply SpellIndian formula to all sheet and entire microsoft excel 2010... Pl tell how to apply

    ReplyDelete