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

Friday, February 12, 2010

Excel Sort Dates by Birthday

Today's post is in response to comment by Donna.

Problem on hand is to sort data based on birthday of a person. If you sort it on the date of birth it has year also and you will not be able to sort them by month and date.

One of the possible solution is to drop the Year and then sort them. You will need to add one more column to your data as sort key and use following formula considering that you have Date of Birth in Cell "B2"

=TEXT(B2,"MMDD") will convert 02-Dec-50 as 1202 and 24-Sep-89 as 0924. Now if you sort your data based on new column "Sort Key" on ascending manner, you will get 24-Sep-89 before 02-Dec-50

excel-sort-dates-by-birthday


You will be able to get this result only if the date of birth in your data is a real date. In case not you will need to convert text date to real dates

Use following formula to know Birthday during current year

=--TEXT(B2,"DD/MMM")

You will get error in case birthday is 29-Feb and current year is not a leap year. I suppose this is correct as person will not have birthday every year.

There are couple of other solutions to the sort dates on birthday , you can share one by way of comments if you know

Monday, February 1, 2010

Excel Formula : Sum top 5 in unsorted range

Today I am sharing a formula to sum top 5 values in an unsorted range.

=SUMPRODUCT(LARGE(Data,ROW(1:5)))

See the screen cast below to know secret behind this formula.

Photobucket

Actually we are using array formula to find top 5 values , then summing them to find the sum of top 5 values in unsorted range.

From the screen cast you can see that

1. We are entering this formula using sumproduct, which is shortcut to enter array formulas in excel.
2. Data is a named range A3 to B36
3. We are using excel function Large to find Top values.
4. Row(1:5) - is is shortcut to create an array of numbers {1,2,3,4,5} as Kth position

This leads to find top 5 values in unsorted range and then summing them.

Food for thought :

Just change Row(1:5) to Row(1:10) you will be able to find Top 10 values.

Or Change Large with small to find out bottom 5 values.

Download Excel file with formula to sum top 5 values in an unsorted range

to play furhter with this formula