Yogesh Gupta's Excel Tips

Excel Addins : Spell Currency Excel Addin

2012-02-13T22:51:00.019+05:30

I am sharing Excel Addin for converting Numbers to Words. I keep getting mails / comments where people are not able to install the VBA code shared in my earlier post

Excel Functions : Convert Numbers to Words.

This Addin will make it easier to use. This Addin can be installed on any computer having Excel 2007 and above versions. Once Installed you can use SpellCurr() formula in any excel file on that computer.

You need to download and open Addin file on your machine. This will install the Addin automatically to your machine.

However if you send file to another person you need to send the Addin along and ask that person to install it on his/her machine so that your formula can work there.

Download SpellCurr Excel Addin for converting numbers to words - Excel 2007 Version

Download SpellCurr Excel Addin for converting numbers to words - Excel 2003 Version

Numbers to Words , Convert Number to Words , Convert Number to Word , Number to Words , Number to Word , Number to Text , Number in Words , Number to Letters , Convert Number to Text , VBA Number to Text , Number to Text Function , Numeric to Text

Excel Macros : Send Email with VBA

2010-07-24T23:24:00.001+05:30

The Code I am sharing with you today is capable of sending active sheet of your workbook through outlook mail to email ID specified by you. You will not get any warning messages and the email will be sent using outlook profile available on your machine.

This code saves active sheet to temp file and then email it to the recipient. Once the mail has been sent the temp file is deleted by the code.

You will need to copy this code to regular VBA module of your workbook


Sub EmailActiveSheetWithOutlook()
    
    Dim oApp, oMail As Object, _
    tWB, cWB As Workbook, _
    FileName, FilePath As String

    Application.ScreenUpdating = False

'Set email id here, it may be a range in case you have email id on your worksheet
    
    Mailid = "yogesh@yogeshguptaonline.com"
    
'Write your email message body here , add more lines using & vbLf _ at the end of each line
    
    Body = "Please find enclosed " & vbLf _
    & vbLf _
    & "Thanks & Regards"
    
   
'Copy Active Sheet and save it to a temporary file

    Set cWB = ActiveWorkbook
    ActiveSheet.Copy
    
    Set tWB = ActiveWorkbook
    FileName = "Temp.xls" 'You can define the name
    FilePath = Environ("TEMP")
    
    On Error Resume Next
    Kill FilePath & "\" & FileName
    On Error GoTo 0
    Application.DisplayAlerts = False
    tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=56
    Application.DisplayAlerts = True
     
'Sending email through outlook

    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        .To = Mailid
        .Subject = "Update Message Subject here"
        .Body = Body
        .Attachments.Add tWB.FullName
        .send
    End With
     
'Delete the temporary file and restore screen updating
    
    tWB.ChangeFileAccess Mode:=xlReadOnly
    Kill tWB.FullName
    tWB.Close SaveChanges:=False
    cWB.Activate
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    
End Sub

Download file with VBA code to send active sheet through outlook

Excel Formula : Calculating Age

2010-05-31T21:16:00.000+05:30

Today I am sharing with a long formula for calculating age based in Date of Birth as input.

=YEAR(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1900&" Years "&IF(MONTH(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1=0,"",MONTH(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1&" Months ")&IF(DAY(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1=0,"",DAY(DATE(YEAR(TODAY())-YEAR(A1),MONTH(TODAY())-MONTH(A1)+1,DAY(TODAY())-DAY(A1)+1))-1&" Days")

This formula calculates age of a person with the accuracy of exact years , months and days

This considers that the Date of Birth is available at Cell A1 of the worksheet. You will need to replace this

Download excel file containing formula to calculate age based on Date of Birth

Create Range Name in Excel

2010-04-18T14:30:00.004+05:30

Today I am sharing quickest way to create Range Name in Excel. Just 3 easy steps

1. Select the cell(s) you want to name
2. Type Range name in the name box
3. Press Enter

The following is a list of syntax rules that you need to be aware of when you create and edit names.

Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1. Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.

Name length A name can contain up to 255 characters.

Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name

Check out screen cast below to see it happening.

Named ranges is part of defined names in excel. It is just not limited to a cell on the worksheet or range of cells, it can also be a constant or a formula in your workbook.

It has many uses in Excel, some of them are :-

1. Make your formulas much easier to understand.
2. Refer to dynamic ranges in excel by creating dynamic range names
3. Can be used in data validation based on contents on another worksheet
4. Can be used for conditional formatting based on contents on another worksheet.
5. These are best used in Excel Macros. It makes your macros more robust
6. Use them as constant value without referring to contents on your worksheet.

Will be covering some of the topics in my upcoming posts, so stay tuned to learn more uses of defined names in excel.

Add Spin Buttons Active X Excel

2010-04-10T15:21:00.004+05:30

In my previous posts I have shared EMI Calculator with you. This was mode using Excel PMT function and Spin Buttons ActiveX Controls in Excel. In this post I am sharing how to add spin buttons ActiveX controls to excel worksheet.

Step 1 Have Developer Tab visible in your Excel 2007. In case you already have it proceed to step 2
Step 2 Click on Insert under controls group
Step 3 Click on Spin Buttons under ActiveX Controls
Step 4 Draw Spin Button on your worksheet.
Step 5 Rename the Spin Button Object
Step 6 Add VBA code to the Spin Button

Checkout Screen Cast Below

In this screen cast we have added spin button next to loan amount and renamed the spin button object as Loan

Similarly add two more spin buttons next to Interest Rate and Years. Rename them as Interest and Years respectively.

To make these buttons work we need to add VBA code. Separate macros are required for each button that is why we have renamed the spin buttons. It is to give them a name that can be related later on by us when we look at the macro code. Macro code will be using the names we have given to them

Now to add the VBA code , double click on the spin button object, it will open VBA editor. Copy following VBA code to worksheet module

Private Sub Loan_SpinDown()
    With Range("C4")
        .Value = WorksheetFunction.Max(100000, .Value - 100000)
    End With
End Sub

Private Sub Loan_SpinUp()
    With Range("C4")
        .Value = WorksheetFunction.Min(10000000, .Value + 100000)
    End With
End Sub

Private Sub Interest_SpinDown()
    With Range("C5")
        .Value = WorksheetFunction.Max(0.03, .Value - 0.0025)
    End With
End Sub

Private Sub Interest_SpinUp()
    With Range("C5")
        .Value = WorksheetFunction.Min(0.25, .Value + 0.0025)
    End With
End Sub
Private Sub Years_SpinDown()
    With Range("C6")
        .Value = WorksheetFunction.Max(1, .Value - 1)
    End With
End Sub

Private Sub Years_SpinUp()
    With Range("C6")
        .Value = WorksheetFunction.Min(25, .Value + 1)
    End With
End Sub

With this your EMI Calculator with Spin Buttons ActiveX Controls is ready

Visit EMI Calculator page if you want to download excel file complete with macros and Spin Buttons Activex controls

Show Developer Tab in Excel 2007 Ribbon

2010-04-10T13:28:00.003+05:30

In case you are working with ActiveX Controls or Macros in Excel , you need to have Developers Tab visibile in your Excel 2007. When you start using Excel 2007, developers Tab is not visible , however you can set it visible with following steps

Step 1 - Click MS Office Button
Step 2 - Click Excel Options
Step 3 - Click Populor
Step 4 - Tick Show Developer Tab in the Ribbon check box
Step 5 - Click Ok

Excel Formula : PMT Function

2010-04-02T17:49:00.012+05:30

In my previous post I shared EMI Calculator with you. In this post I am sharing how to make EMI Calculator. This has been done using PMT Function in excel to calculate EMI amount and Spin Buttons ActiveX Controls to fine tune the amounts.

Using PMT Function in Excel

PMT Function Calculates the payment for a loan based on constant payments and a constant interest rate. For calculating EMI you need 3 inputs.

Loan Amount , Annual Interest Rate, Loan Period in Years. Using these figures EMI can be calculated with PMT Function in Excel

Syntax : PMT(RATE,NPER,PV,FV,TYPE)

RATE : is the interest rate for the loan. In case you are making monthly payments devide the annual rate by 12 to get monthly rate.

NPER : is the total number of payments for the loan. When payments are made on monthly basis you need to multiply number of years with 12 to arrive at total number of payments.

PV : is the present value, or the total amount that a series of future payments is worth now; also known as the principal. We take this figure as (-) Negative in our formula since it is receipt of funds.

FV : is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. In our formula we omit this figure as we need to reach zero balance at the end of term.

Type : is the number 0 (zero) or 1 and indicates when payments are due. If payments are due at the end of the period use 0 or omit this figure. use 1 if payments are made at the beginning of the period. In our formula we have omitted this.

We have calculated our figures with following formulas

EMI : =PMT(Interst/12,Years*12,-LoanAmt)

Total Amount with Interest - This is total outgo during the loan term. This can be calculated using : = EMI*Years*12

Flat Interest Rate : = (Total Amount with Interest - LoanAmt)/LoanAmt/Years : Many financiers talk about Flat interest rate while landing money. They calculate simple interest on entire loan amount for entire period of the loan. Many people go for the loan without understanding the actual interest rate. Flat interest rate looks very attractive as it is much lower than the actual interest rate which is calculated on reducing balance. You can see the difference in the figures above. So be careful while borrowing on flat interest rate.

In next post I will cover Adding Spin Buttons ActiveX Controls to Worksheet.

EMI Calculator

2010-03-22T09:11:00.007+05:30

My friend is taking a loan to buy a house and he asked me what will be my EMI. He knew following figures and was curious to know his EMI

Loan Amount
Annual Interest Rate
Loan Period - Years

I quickly calculated EMI using PMT function in excel. This triggered me an idea of making a EMI calculator and sharing it with all of you.

Equated Monthly Installments are commonly knows as EMI. As per business dictionary EMI is "Hire purchase, lease, or loan -repayment installments that are constant in amount, and are usually collected in advance as post-dated checks"

Download EMI Calculator Excel file to know your EMI

Input your figures to find out resultant EMI. Use spin buttons to fine tune your results

In next post I will discuss how to make EMI Calculator. This will be about adding Spin Buttons ActiveX controls to worksheet and PMT function

Excel Formula : Subtotal Excel

2010-03-17T18:09:00.003+05:30

Most of the time in our reporting we need to do subtotals followed by Grand total at the end of report. I have seen many people using SUM function for subtotals in between and then adding individual cells for calculating grand total at the end. There is nothing wrong with this method if you use it very carefully so that you do not miss any item while calculating grand total at the end.

However better approach is to use Excel Subtotal function for calculating Subtotal and Grand Totals.

Image above is showing column B with the Subtotal formula and resultant values with this formula. You may notice that when we use subtotal function for calculating Grand Total, it ignores the values resulting from subtotal function within its range. This method ensures that we do not miss any item while doing Grand Totals for our reports thus higher accuracy.

This becomes more helpful when you edit your report after some time to insert another subtotal. You do not need to remember to correct the final formula calculating the Grand Total.

Another advantage of using Subtotal function is that you need not to recalculate your formulas when used with filteres. This function ignores the values hidden by the filters and considers the values in the visible rows only.

Subtotal can be used for various type of calculations based on what do you want this fucntion to do for you. Syntex for this function is

SUBTOTAL(function_num, ref1, ref2, ...)

Following function numbers can be used in Subtotal function

I have used "9" in my example above for sum you can change it as per your required working.

Function_num (Incl hidden values)	Function_num (Excl hidden values)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Format Numbers : Excel Currency Format

2010-03-08T22:08:00.002+05:30

You can add currency symbol to your numbers in excel.

Open Number Format dialog box using Format Cells option or just press Ctrl+1

1. Click Accounting
2. Choose currency symbol from drop down

In case you do not get your currency symbol from drop down, add your symbol using custom formating option in the same dialogbox
1. Click Custom
2. Type - "Rs "#,##0_);[Red]("Rs "#,##0)

I use "Rs " to show numbers in Indian Currency. You can replace it with your symblol.

Pivot Tables : P&L reporting for Multi Location Organisation

2010-03-07T19:11:00.008+05:30

I have recently started a series via guest posts on pointy haired dilbert famous excel blog also known as PHD or Chandoo.org. This blog is run by Chandoo a excel blogger and Microsoft excel MVP. Most of you must be knowing chandoo, those who do not know about him can know more about chandoo.

In this series we are covering on how to manage Profit and Loss (P&L) account reporting for Multi Location organization.

During this series our aim is to learn how we can do our P&L reporting on various dimensions with few clicks.

We are using Pivot Tables for our reporting purpose and will setup P&L report of a Retails chain with multiple locations divided into various regions.

Topics coverted in this series are :

1. Data sheet structure for Preparing P&L using Pivot Tables
2. Preparing P&L Pivot Table using Data sheet
3. Adding Calculated Fields to Pivot Table P&L
4. Exploring Pivot Table P&L Reports
5. Preparing Quarterly and Half yearly P&L using grouping option
6. Budget V/s Actual report using Pivot

Do not think that series is only about the Profit and Loss Account. This series is also about PivotTables. We will cover many of PivotTable tricks during our series. I hope you will be able to use those tricks elsewhere also.

Follow this series on PHD, I am sure that at the end of this series you will be able to do your P&L reporting on various dimentions with just few clicks.

Excel VBA : Deploy Macros Found Elsewhere

2010-03-04T11:52:00.009+05:30

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 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 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 : 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 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 : 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 Sort Dates by Birthday

2010-02-12T07:51:00.005+05:30

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

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

Excel Formula : Sum top 5 in unsorted range

2010-02-01T23:31:00.001+05:30

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.

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

Excel Formula : Closest Match

2010-01-16T22:05:00.002+05:30

Arun Singla one of my blog reader has sent me Excel Formula in response to my previous post Custom Function : Excel Closest Match using VBA.

Formula sent by Arun gives similar results as achieved by Custom Function using VBA. I am sharing the formula with you.

For getting less than equal to value
=IF(COUNTIF(Data,Target)>=1,Target,SMALL(Data,COUNTIF(Data,"<"&Target)))
For getting greater than equal to value
=IF(COUNTIF(Data,Target)>=1,Target,LARGE(Data,COUNTIF(Data,">"&Target)))

In above formula : Data is unsorted data range name, target is the named range having target value.

Now let me explain how does this formula works. I will explain first formula which calculates less than equal to value.

Step 1 - Formula calculates the number of values matching target value using countif function of excel. COUNTIF(Data,Target) gives the total number of values matching exactly with the target. In case you get even one value matching with the target, you return the target as closest match, no need for further working.

Step 2 - In case formula does not get matching value at step 1 , it moves to find the closest match. SMALL(Data,COUNTIF(Data,"<"&Target)) Returns the k-th smallest value in a data set. COUNTIF(Data,"<"&Target) gives us total number of data points less than target. We are using this as k-th smallest value which is closest match less than the target value. I hope this explanation is not confusing to you. Check out the file with this formula to find closest match in excel to understand it better.

Custom Function : Excel Closest Match using VBA

2010-01-14T18:37:00.004+05:30

Some days back I got a mail from Kevin asking for how to get closest match in a list sorted in acending order


Hi Yogesh, is there a way for me to find the
closest match (greater than or equal to) to
a value in a list? The list is sorted in
ascending order and cannot be changed.
Regards,
Kevin

I responded to him with a Custom Function capable to Finding Closest Match as per his requirement. This worked as the list was sorted in ascending order. He sent me a thanks note for this.

However this left certain questions in my mind such as

1. What if the list is not sorted, Custom function should be able to do this in unsorted list.

2. He wanted greater than equal to value, however it should be able to handle less than equal to value also

With these things in my mind I worked out another custom function which works with unsorted data range and is capable of finding both type of closest match in excel.

I am sharing updated Custom Function with all of you. You will need to copy this code to regular VBA module of your workbook


Function cmatch(ByVal n, l As Range, Optional Switch As Integer = 1)
'**************************************************************************************
'* Custom Function by Yogesh Gupta , yogesh@yogeshguptaonline.com                     *
'* Custom Function to find Closest Match in an unsorted data                          *
'* use Switch value as -1 in case you want less than equal to closest match           *
'* Default value for Switch is 1, and will find greater than equal to closest match   *
'**************************************************************************************

If Switch = 1 Then               ' This will get greater than equal to value
        
        a = Application.Max(l)
        If a < n Then            ' Error in case list does not have value greater than or equal to n
            cmatch = "#N/A"
        Else
            For Each c In l
                If c >= n Then
                    If c < a Then a = c
                End If
            Next c
            cmatch = a
        End If
        
ElseIf Switch = -1 Then          ' This will get less than equal to value

        a = Application.Min(l)
        If a > n Then            ' Error in case list does not have value less than or equal to n
            cmatch = "#N/A"
        Else
        
        For Each c In l
            If c <= n Then
                If c > a Then a = c
            End If
        Next c
        
        cmatch = a
        End If
       
Else

        cmatch = "#N/A"            ' Error in case of Invalid Input

End If

End Function

Download Excel file with Custom Function to Find Closest Match to see how it works

Excel Macros : Excel Date Picker

2010-01-01T13:10:00.005+05:30

This is first post of the year 2010, I wish all my blog readers a very happy and prospurous year. Today I am sharing Excel Date Picker Utility with you. Following picture will demonstrate some to the capabilities of this utility.

This utility will help you with following

1. In case you select a cell with date, or select any cell around the cell with a date. It will show floating button on the left side of the cell. If you click on the floating button it will show popup calender.
2. In case the cell already has a date , the calendar will show same date by default, else it will show current date.
3. You can jump the months by click on the month value on the popup calendar.
4. Click on the year will show spin buttons next to it and you can change year easily with spin buttons.
5. When you click on the date in popup calander, it will insert date clicked to the selected cell
6. You can also use Ctrl+Shift+d to show popup calender on your screen.
7. In case you get pop up calander and you do not want to click any date, just press Esc to close the pou up calendar.

This utility could be usefull in following ways

1. Many people end up entering date in text format as some time it is difficult to enter the date in correct computer readable format. With the popup calander, system takes care of the proper date entry.
2. Some times you have to get data from other users and you want them to enter dates in proper format so that rest of the worksheet can use those date inputs. With this utility you can be assured about this issue.

You may figure out many more usage based on your needs. The VBA code for Excel date Picker is available in the downloadable file. You can further modify the code as per your needs.

Now how to use this Excel Date Picker Utility

1. Download file and save it as Excel Addin on your machine.
2. You will need to copy this code to worksheet module of your workbook.

You will need to add this code to show the Floating button. This code will creat the button in case it does not exist on it. All other features like cell menu on right click and popup calender on Ctrl+Shift+d will be available in case you do not add this code by saving download able file as Excel Addin on your machine. I am suggesting this because I still need to learn how to trigger worksheet selection change event with a vba code in addin.

Private Sub FloatingButton_Click()
Calander.Show
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        
    If Application.CutCopyMode = 1 Then
    Exit Sub
    ElseIf Target.Cells.Count > 1 Then
    Exit Sub
    Else
    
        Set r = Target
        Hasdate = 0

        For x = -1 To 1
        For y = -1 To 1

        On Error GoTo Etrap1

        If IsDate(r.Offset(x, y)) Then
            Hasdate = 1
            Exit For
        End If
Rsume1:
        Next y
        If Hasdate = 1 Then Exit For
Rsume:
        Next x
            
            If Hasdate = 1 Then
                
                FloatingButton.Left = r.Offset(0, 1).Left
                FloatingButton.Top = r.Top
                FloatingButton.Visible = True
                
            Else
                
                If Not FloatingButton.Visible = False Then
                FloatingButton.Visible = False
                End If
             
            End If
    
Exit Sub
    
    End If
    
Etrap1:
    If r.Row = 1 Then
        Resume Rsume
    ElseIf r.Column = 1 Then
        Resume Rsume1
    End If


End Sub

Private Sub Worksheet_Activate()
    Dim s As String
    On Error Resume Next
    s = ActiveSheet.FloatingButton.Caption
    If Err.Number <> 0 Then
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=147.75, Top:=34.5, Width:=15.75, Height:= _
        15.75).Select
    
    Selection.Name = "FloatingButton"
    ActiveSheet.OLEObjects("FloatingButton").Object.Caption = ""
    ActiveSheet.OLEObjects("FloatingButton").Object.BackColor = &H80000005
    Range("A1").Select
    FloatingButton.Visible = False
    End If
    On Error GoTo 0

End Sub

However this will not work on the files you send to other users. In that case you need to builtin the entire utility into the file you are sending out.

Following steps will help you to do this.

1. You will need to copy this code to workbook module of your workbook

Private Sub Workbook_Open()
    Dim NewControl As CommandBarControl
    Application.OnKey "+^{D}", "Module1.ShowCalander"
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Show Date Picker").Delete
    On Error GoTo 0
    Set NewControl = Application.CommandBars("Cell").Controls.Add
    With NewControl
        .Caption = "Show Date Picker"
        .OnAction = "Module1.ShowCalander"
        .BeginGroup = True
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Show Date Picker").Delete
End Sub

2. You will need to copy this code to regular VBA module - Module1 of your workbook

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Declare Function DrawMenuBar Lib "user32" (ByVal hwnd As Long) As Long
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Declare Function ReleaseCapture Lib "user32" () As Long
Private Const GWL_STYLE As Long = (-16)
Private wHandle As Long

Sub ShowCalander()
    Calander.Show
End Sub

3. Move form named calander from the downloadable file to your file.

With these steps you can send the file to other users and it will work in similar way as it works on your machine.

In addition you can force users to enable macros while using your file

Download file with Excel Date Picker Utility

Excel Macros : Text to Numbers VBA

2009-12-05T09:59:00.001+05:30

Some times you get data which has some numbers surrounded by lots of text or non text characters or space in between text and numbers. Position of the numbers within text is not same in each data line. In such situation you may not be able to apply standard Excel Formulas to extract number from text. In such scenario you will need to use VBA to Extract Number from String. Today I am sharing with you UDF to perform this function. Just for illustration following table will show you capability of this UDF.

Text Data	Extracted Numeric Value
xusdhd 10005000	10005000
AKSID0100050000 A1	100050000
IU EW KFID100050000 A 1	100050000
10005 0000 A1	100050000
01000 A1 B 12	1000

Above Numeric Values have been extracted using simple formula =Number(B3) , In this case text string is in Cell B3. This formula is not available in standard excel functions. To apply this formula You will need to copy this code to regular VBA module of your workbook


'****************************************************************
'* User Defined Function (UDF) to Get Numeric Value from String *
'****************************************************************
Function Number(ByVal CurrString As String)
    Dim temp As String
    
    temp = Left(CurrString, 1)
    Do While Not IsNumeric(temp)
        If Len(CurrString) <= 1 Then
            Exit Function
        Else
            CurrString = Mid(CurrString, 2)
            temp = Left(CurrString, 1)
        End If
    Loop
    Number = Val(CurrString)
End Function

Download excel file with UDF for Text to Numbers

Excel Formula : Using Vlookup Function

2009-11-22T14:37:00.000+05:30

One Golden Rule while working with Excel Formula's is never enter a hard coded value into Formula. If you can calculate a value with a formula, it should not be hard coded into your formula's

Today we will talk about how this is relevant in using Vlookup Function. We all use Vlookup function in our day to day working with the data in Excel. Given below is the Syntex for Vlookup function just for reference purpose.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Picture on the above is typical data table used by us. Column C to N are hidden in this just to keep the table visible on screen. We need to get the sales value using vlookup formula. Most of the users (including me till some time back) hardcode col_index_num. Most common use of Vlookup Function for getting data from the table like above will be a formula like this.
=VLOOKUP(B13,B3:P10,15,0)

Here lookup_value is given at Cell B13, table_array is a Range B3:P10, col_index_num is 15 as it is 15th Column starting from Column B, range_lookup is 0 as we are looking for exact match.

In above formula the col_index_num is hard coded. Formula will give correct results till you do not insert or delete columns in between. Once you have done so , all your formulas will not update sales figure but reference some other value.

However with the following formula you can overcome this issue. You can calculate the number of columns with the help of Columns function of Excel.
=VLOOKUP(B13,$B$3:$P$10,COLUMNS(B3:P3),0)

COLUMNS(B3:P3) will be calculated as 15 and in case you insert any column between table_array your formula will change to VLOOKUP(B13,$B$3:$Q$10,COLUMNS(B3:Q3),0). Now columns function withing your formula will be calculated as 16 giving you correct value. This makes your Vlookup function dynamic.

Do refer to my earlier tip on Dynamic Range Names to make table_array also dynamic.

You are welcome to share your way of entering Vlookup function by way of comments to this post.

Excel Macro : Email Address Extract from String

2009-11-18T17:05:00.003+05:30

Last week I got a mail from my friend asking for help in extracting email IDs from data available with him. The position of the mail IDs within the text string was not same and he was finding it difficult to us extract email addresses.

I wrote a quick UDF for him which did the job. I am sharing same with you as you may find it use full.

Sample of the data and mail IDs extracted with the UDF is as below

Here is the VBA code to Email Address Extract from String. You will need to copy this code to regular VBA module of your workbook


Function Getmailid(cell As Range) As String

Dim Textstrng As String

Textstrng = cell.Text
Position@ = InStr(1, Textstrng, "@")
EmStart = InStrRev(Textstrng, " ", Position@)
If EmStart = 0 Then EmStart = 1
EmEnd = InStr(Position@, Textstrng, " ")
If EmEnd = 0 Then EmEnd = Len(Textstrng) + 1

mailid = Trim(Mid(Textstrng, EmStart, EmEnd - EmStart))

If Right(mailid, 1) = "." Then
Getmailid = Left(mailid, Len(mailid) - 1)
Else
Getmailid = mailid
End If
End Function

Download file with VBA code to Email Address Extract from Text String

Excel Macro : Hide Cell Content from Printing

2009-11-13T20:08:00.002+05:30

Some times we have some information in worksheet which is we need to input but do not want that information to be printed. With the following macro code you can achieve the desired results

You will need to copy this code to workbook module of your workbook


Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True
    A = Range("A1").NumberFormat
    Range("A1").NumberFormat = ";;;"
    
    Application.Dialogs(xlDialogPrint).Show
     
    Range("A1").NumberFormat = A
    Application.EnableEvents = True

End Sub

As per above code the contents of Range A1 will not be printed, you will need to change this reference to your cell.

Download file with the code to hide cell contents before printing

Excel Formula : Sumif Multiple Criteria

2009-11-06T17:10:00.003+05:30

This is in continuation of my earlier post on Countif Multiple Criteria The same logic can be extended to calculate sum based on multiple criteria.

In case you have Excel 2007, you can use built in function Sumifs . This will let you add multiple conditions.

In case of Excel 2003 or earlier version using Pivot table will get you the results easily but if You don't have liberty to use Pivot, then you will need formula trick. Consider that you have a data table like this and you need to know what is sales in "North" region by sales man "Ram".

Following sumproduct formula will do the calculation for you.

=SUMPRODUCT(($B$13:$B$28="North")*($C$13:$C$28="Ram")*($E$13:$E$28))

Another way is to write an array formula.
=SUM(($B$13:$B$28="North")*($C$13:$C$28="Ram")*($E$13:$E$28))
However this will need to be confirmed with CTRL+Shift+Enter (CSE)

After CSE formula will show {} in formula bar. This will covert it to an array formula. The formula will look like following picture after CSE in formula bar

I suggest you to use Sumproduct formula as you may find it difficult to enter array formula.

If you see the dialog for sumproduct formula entry you will understand the reasons behind this recommendation. Actually sumproduct is a workaround to enter array formula.

If you see above dialog screen , you will notice

1. When you enter ranges, you are actually entering array's.
2. This formula is entered in single array only to get the results.

If you try to use sumproduct as normal formula to calculate sum based on multiple conditions , you will not get the results.

Actually multiple condistional sum can be calculated using arrays only and sumproduct allows you to enter array's easily and you can over come the difficulty of entering array formula.

Download Excel File having Sumif Multiple Criteria Formulas

Excel Number Format : Indian Style Comma Separation

2009-10-30T17:29:00.003+05:30

Yesterday I got a comment asking for custom number format for Indian style comma separation. While I responded to question by providing the custom number format I knew that the answer does not handle the complete range of numbers and can not be applied in all the cases. This forced me to look around for a solution that can be applied for all kind of numbers.

Just for the information of people who are not aware of Indian Style of comma separation, I have produced table below which explains Indian style formatting for various numbers. The basic rule is that first comma separation happens at 3 digits i.e. 1000 then it happens after every 2 digits. Check out table below for better understanding.

Number	Formatted As
10	10.00
100	100.00
1000	1,000.00
10000	10,000.00
100000	1,00,000.00
1000000	10,00,000.00
10000000	1,00,00,000.00
100000000	10,00,00,000.00
1000000000	1,00,00,00,000.00
10000000000	10,00,00,00,000.00
100000000000	1,00,00,00,00,000.00

Soon I realised that there is no single custom format available for handling all kind of number length and we need separate format for it depending upon the number of digits. Doing this manually and accuratly every time is very difficult. This leaves only one option to automate this through a macro. I got a Macro code for doing this and modified and tested that for long numbers.

I am sharing the macro code with you. You will need to select the numbers and run macro to format them as per India style comma sepration.


Sub IndianNumberFormat()
For Each c In Selection
Select Case Abs(c.Value)
Case Is < 100000
c.Cells.NumberFormat = "##,##0.00"
Case Is < 10000000
c.Cells.NumberFormat = "#\,##\,##0.00"
Case Is < 1000000000
c.Cells.NumberFormat = "#\,##\,##\,##0.00"
Case Is < 1000000000
c.Cells.NumberFormat = "#\,##\,##\,##0.00"
Case Is < 100000000000#
c.Cells.NumberFormat = "#\,##\,##\,##\,##0.00"
Case Else
c.Cells.NumberFormat = "#\,##\,##\,##\,##\,##0.00"
End Select
Next c
End Sub

For easy application of this code you need to save it as a excel addin or add it to your personal macro book. Adding a custom toolbar for this code will make it easy to apply. You can chage the number of decimal places by changing the number of zeros at the end of custom number format given above.

Excel Number Format : Telephone numbers with leading + sign

2009-10-27T21:37:00.003+05:30

This is in continuation of my earlier post Custom Formating - Excel Number Format

Excel does not display the leading + sign entered by users, similarly the leading zeros are also not recoganised by excel. However custom formating trick can help you do this. Refer to the following table, where in Phone number 9971112814 has been formated differently to display different formats for the same cell contents.

91 is country code for India, you can change it to whatever code you want to display the number accordingly.

Format	Displayed as
+91 0000000000	+91 9971112814
+91(0)0000000000	+91(0)9971112814
+91(0)000-000-0000	+91(0)997-111-2814
0091 0000000000	0091 9971112814

Excel Formulas : Countif Multiple Criteria

2009-10-23T18:57:00.013+05:30

Many times we need to perform a count based on multiple criteria.

This is very simple if you are using Excel 2007. You have standard excel function Countifs which lets you do the count based on multiple criteria.

But if you are using Excel 2003 this becomes tricky as you do not have any standard function that lets you do this. However you can do it using Pivot Tables. Sometimes you just need the count based on multiple criteria to be put into a report and using Pivot tables for such report may not be a viable option.

In such scenarios you need to go for a workaround using excel formula. Considering you have a data table like this and you want to know the number of customers in North Area serviced by Sales Man by name of Ram.

If you have Excel 2007 then it very simple just use
=COUNTIFS($B$13:$B$28,"North",$C$13:$C$28,"Ram") and it will give you count result 3.

For Excel 2003 users My favorite for such kind of calculation is Sumproduct function and you can do it with the help of following formula.
=SUMPRODUCT(($B$13:$B$28="North")*($C$13:$C$28="Ram"))
This formula will give you result as 3 customers.
You can add as many conditions here but be sure that the height of the range is same for all ranges mentioned in this formula

This actually is a workaround for another method array formula.
=SUM(($B$13:$B$28="North")*($C$13:$C$28="Ram")) confirmed with CTRL+Shift+Enter
Once you confirm this formula with CTRL+Shift+Enter it will add {} to the formula which will be visible in the formula bar only.

Look at the screen cast below to know the difference it make to the normal formula once confirmed as CSE formula. Look for the addtional {} added to formula

Download file having countif multiple conditions formula

You may find it difficult to enter array formula that is why I suggest you to go for SUMPRODUCT method.
To know more about array formulas you can read Introducing array formulas in Excel
.