Yogesh Gupta's Excel Tips

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.003+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.003+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.002+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.011+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.004+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.002+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.001+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.006+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.003+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.003+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
.

Excel Dates : Leap Year or Not a Leap Year

2009-10-18T07:18:00.004+05:30

Excel follows Gregorian calendar which was first established in 1582 by Pope Gregory XIII.

To determine whether a year is a leap year, follow these steps:


  1. If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5. 
  2. If the year is evenly divisible by 100, go to step 3. Otherwise, go to step 4. 
  3. If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5. 
  4. The year is a leap year (it has 366 days). 
  5. The year is not a leap year (it has 365 days).

Following formula gives the results based on above steps. This considers that you have stored year in Cell A1 of your worksheet

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"Leap Year", "NOT a Leap Year")

Since excel has all these calculations built in , you can just test the last day of the February month and decide based on day value. If it is 29 then Leap Year else NOT a Leap Year.

Following formula gives the results based on above steps. This considers that you have stored year in Cell A1 of your worksheet

=IF(DAY(DATE(A1,3,0))=29,"Leap Year","NOT a Leap Year")

First Formula returns 1900 as "NOT a Leap Year" but second Formula will return it as "Leap Year". This is due to bug in excel which considers 1900 as a Leap Year though it was not a leap year.

This is why I will recommend to use second Formula in excel to determine a Leap Year since it takes care of any bug that exists in excel

VBA Function for Leap Year Test:


Public Function IsLeapYear(ByVal YY As Long) As Boolean
    IsLeapYear = Day(DateSerial(YY, 3, 0)) = 29
End Function

Leap Year , leap year test , leap year check , check leap year , Leap Year in Excel

Excel Convert Text to Date

2009-10-12T15:57:00.006+05:30

Many of us get into a situation when the dates in our data are coded as text. Excel does not recognise such text entries as date and we can not use them for any calculation purpose. However you can easily covert them to date by the help of excel formula.

You may have a situation where the text of the date is actually as date but being a text string it is not recognised as date by excel. It could be 12/Oct/2009 in text string. You can easily convert them using Datevalue formula. Assuming you have text in cell A2, the formula you need to enter is =DATEVALUE(A2)

However many time we get data stored like 20091012. Here October 12, 2009 has been stored as First 4 digits as Year, next 2 Digits as Month, next 2 Digits as Day. It becomes little tricky but it can also be converted to date using combination of Mid and Date function of excel.

Assuming you have date stored as 20091012 in cell A3 of you worksheet, you can convert it to date using =DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))

Logic behind this formula is devide the text into Year , Month and Day separatly. This is done by using MID function of excel. Once you are able to break text string into three different components of Date. You put these in the Date Function of Excel. Syntex for Excel Date function is DATE(year, month, day).

MID(A3,1,4) = 2009 - This Year Value in Text String
MID(A3,5,2) = 10 - This is Month Value in Text String
MID(A3,7,2) = 12 - This is Day Value in Text String

This is converted into Grand Formula = DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))

If you see the formula I have just brokenup the text string using MID in combination with Date function.

See picture below to understand it better.

convert text to date in excel , excel convert text to date, convert text to date, date values

Excel Macros : Log User Activity to Log Sheet

2009-09-27T12:55:00.012+05:30

We all store Excel workbooks on shared drives. While those files are accessed by many users but there is no track about who did what at various points of time. However this problem can be resolved with the help of VBA. A VBA programme with following capability can help us to get the log of user activity.

 
     1. Add sheet named Log in case it is does not exist in the file.
     2. Record user activity based on events to Log sheet.
     3. Along with recording a event, it records user information and time of event.
     4. Manage log size and removes old entries while creating space for new entries.
     5. User can view the log but can not make changes to the log.

Following code has all these capabilities. Macro given below records user activity based on event information passed to it by another macro. You will need to copy this code to regular VBA module of your workbook


Sub Elog(Evnt As String)
    
    '***************************************************************************************************
    '* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 27, 2009 *
    '***************************************************************************************************
    
    Application.ScreenUpdating = False
    Dim cRecord As Long
    cSheet = ActiveSheet.Name
    
    If SheetExists("Log") = False Then
        Sheets.Add.Name = "Log"
        Sheets("Log").Select
        ActiveSheet.Protect "Pswd", UserInterfaceOnly:=True
    End If
    
        Sheets("Log").Visible = True
        Sheets("Log").Select
        ActiveSheet.Protect "Pswd", UserInterfaceOnly:=True
        
        cRecord = Range("A1")
    If cRecord <= 2 Then
        cRecord = 3
        Range("A2").Value = "Event"
        Range("B2").Value = "User Name"
        Range("C2").Value = "Domain"
        Range("D2").Value = "Computer"
        Range("E2").Value = "Date and Time"
    End If
    
    If Len(Evnt) < 25 Then Evnt = Application.Rept(" ", 25 - Len(Evnt)) & Evnt
    
    Range("A" & cRecord).Value = Evnt
    Range("B" & cRecord).Value = Environ("UserName")
    Range("C" & cRecord).Value = Environ("USERDOMAIN")
    Range("D" & cRecord).Value = Environ("COMPUTERNAME")
    Range("E" & cRecord).Value = Now()
    cRecord = cRecord + 1
    
    If cRecord > 20002 Then
        Range("A3:A5002").Select
        dRows = Selection.Rows.Count
        Selection.EntireRow.Delete
        cRecord = cRecord - dRows
    End If
    
    Range("A1") = cRecord
    Columns.AutoFit
    Sheets(cSheet).Select
    Sheets("Log").Visible = xlVeryHidden
    Application.ScreenUpdating = True
    
End Sub
Function SheetExists(SheetName As String) As Boolean
    On Error GoTo SheetDoesnotExit
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
SheetDoesnotExit:
        SheetExists = False
End Function
Sub ViewLog()
    Sheets("Log").Visible = True
    Sheets("Log").Select
End Sub
Sub HideLog()
    Sheets("Log").Visible = xlVeryHidden
End Sub

Following macros record events like

"Open" , "Save" and "Print"

and pass on the information to above macro to record user activity.

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


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Evnt As String
Evnt = "Print"
Call Elog(Evnt)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Evnt As String
Evnt = "Save"
Call Elog(Evnt)
End Sub

Private Sub Workbook_Open()
Dim Evnt As String
Evnt = "Open"
Call Elog(Evnt)
End Sub

Since this code will record user activity only in case macros are enabled, do not forget to force users to enable macros while useing the particular file.

Download file having these macros to record user activity to a log sheet. Check how it works

record user activity , track user activity, logging user activity, log user activity , monitor user activity

Excel Macros : Check Mark Symbols with Double Click

2009-09-22T21:16:00.006+05:30

I am sharing with you very interesting trick to insert check mark symbols / to change check mark symbols with the double click in excel.

We can use Wingding font check boxes and change them with the double click with the help of macro

You can add as many of them by just defining the range in your macro. Values can be changed by just a double click.

You will further need to do data validation for that range so that user does not enter any other value by mistake.

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


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'*************************************************************************************************************************
'* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 16, 2009                      *
'*************************************************************************************************************************
    
    If Not Intersect(Target, Range("G4:G14")) Is Nothing Then ' You can Change the range here
        
        Cancel = True
        
        With Target
            .Font.Name = "Wingdings"
            .Font.Size = 20
            .HorizontalAlignment = xlCenter
        End With
      
        If Target.Value = "þ" Then
            Target.Value = "ý"
            
        Else
            Target.Value = "þ"
        End If
    
    End If
    
End Sub

Download file having trick to insert /change wingdings check mark symbols with double click

Make sure that you have enabled macros before using this file.

This trick can be used to choose any values you want change with the double click and is not limited only to check boxes in wingdings. See the attached file for Yes/No as another example.

Symbol Check Mark , Symbols Check Mark , Type Check Mark , Wingding Font Check Mark , Wingding Check Mark , Wingdings Check Mark , Check Mark Symbol , Check Mark Symbols , Check Symbol , Change values with double click in excel

Excel Macro : File open VBA

2009-09-16T14:50:00.007+05:30

Before opening a excel file using VBA, you need to test particular file is open or not in excel. This is required as it can result into a error if particular file is already open.

You need to go step by step to open the file 
    1. Check if excel file is open 
    2. Open that file using VBA , This should be done only in case you know the full path for the particular file 
    3. In case you do not know the full path , it is advisable to give excel file open dialog and let user open the file.

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

Sub Open_File_after_IsOpen_or_Not()

'*************************************************************************************************************
'* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 16, 2009          *
'* Macro modified on September 19, 2009 based on inputs from Mr. Augusto as per comment left by him          *
'* Mr. Augusto added FileExist function though his comment and included in this Updated Macro by Yogesh Gupta*
'*************************************************************************************************************

Dim Myworkbook As String

Myworkbook = "FileName.xls" ' Replace the workbook name here

If Isopen(Myworkbook) = "Not Open" Then ' In case workbook is not open
  
    If FileExist(Myworkbook) = True Then ' In case workbook exist
    
        Workbooks.Open Filename:=ThisWorkbook.Path & "\" & Myworkbook
    
    Else

        Call Open_new_file ' let user choose and open the file
    
    End If

Else
    
    Application.Workbooks(Myworkbook).Activate

End If

End Sub

Sub Open_new_file()

    NewWorkbook = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
    If NewWorkbook = False Then
        Exit Sub
    Else
        Workbooks.Open Filename:=NewWorkbook
    End If

End Sub

Public Function Isopen(Myworkbook As String)

    On Error Resume Next
    Set wBook = Workbooks(Myworkbook)
    If wBook Is Nothing Then
        Isopen = "Not Open"
        Exit Function
    End If

End Function
Public Function FileExist(Myworkbook As String) As Boolean

Dim nfile

On Error GoTo FileExist_err
nfile = FreeFile
Open Myworkbook For Input As nfile
Close nfile

FileExist = True

Exit Function
FileExist_err:
FileExist = False
End Function

Download file having File open Macro with test if particular workbook is open or not

File open VBA , VBA open files , File Open Macro , Excel File open VBA , VBA Excel Workbook Open , VBA Excel Open Workbook , VBA open Workbook , Test particular file is open or not in excel , Check if file is open - Excel

Excel Macros : Remove Duplicates Excel

2009-09-09T21:14:00.019+05:30

Excel 2007 has built in command to remove duplicates, however earlier versions of excel does not have built in command. You need to use VBA routine to do the job.

Use following code to Identify duplicate values in a column and Remove Rows having Duplicate Values

After identifying the duplicate values system will give you warning and ask for confirmation to delete the entire row(s) having duplicate values. You can choose No in case you do not want to delete them.

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


Sub Find_RemoveDuplicates()

    '****************************************************************************************************
    '* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 9, 2009  *
    '* Macro Modified on September 13, 2009 for not deleting empty cells within duplicate values column *
    '****************************************************************************************************

     
    Dim cRow            As Long
    Dim lRow            As Long
    Dim sCell           As Range
    Dim D               As Long
        
    lRow = GetLastRowWithData
    
    On Error Resume Next
    Set sCell = Application.InputBox(Prompt:= _
                "Select Starting Row of Column with Duplicate values.", _
                    Title:="Select Column", Type:=8)
    
    On Error GoTo 0
    If sCell Is Nothing Then
    Exit Sub
    End If
    
    sCell.Select
    
    If Selection.Cells.Count > 1 Then
        MsgBox ("Pls select Single Cell only")
        Exit Sub
    
    Else
    
        dCol = ActiveCell.Column
        sRow = ActiveCell.Row
            
        For cRow = lRow To sRow Step -1
            If IsEmpty(Cells(cRow, dCol)) = False Then
                If WorksheetFunction.CountIf(Range(sCell, Cells(cRow, dCol)), Cells(cRow, dCol).Text) > 1 Then
                    Cells(cRow, dCol).Interior.Color = 255  'This Marks the duplicate values as Red
                    D = D + 1
                End If
            End If
        Next cRow
    
        If D = 0 Then
    
            MsgBox ("No Duplicate Values Found")
    
        Else
    
            caution = MsgBox(D & " Duplicate entries selected and marked RED" & vbCrLf & _
            "Do you want to delete them? " & vbCrLf & _
            "Entire Row for the marked entries will be deleted. " & vbCrLf & _
            "Do you want to Continue?", vbYesNo, "Confirmation")
    
            If caution = vbYes Then
    
                For cRow = lRow To sRow Step -1
                    If IsEmpty(Cells(cRow, dCol)) = False Then
                        If WorksheetFunction.CountIf(Range(sCell, Cells(cRow, dCol)), Cells(cRow, dCol).Text) > 1 Then
                            Cells(cRow, dCol).EntireRow.Delete  ' This deletes the entire row
                        End If
                    End If
                Next cRow
    
            End If
        End If
    End If
    
End Sub

'********************************************************************
'* UDF to Get Last Row with Data on worksheet                       *
'********************************************************************

Public Function GetLastRowWithData() As Long

    Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long

    Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
    lLastDataRow = ExcelLastCell.Row
    lRow = ExcelLastCell.Row

    Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop

    lLastDataRow = lRow
    GetLastRowWithData = lLastDataRow

End Function

Download file having Macro to Find and Remove Duplicates in Excel.

Above works well in case you are trying to clean a data table on the sheet, however if you have lots of other data also on the sheet, you should try the Find and Remove duplicates within Range

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


Sub Find_RemoveDuplicatesWithinRange()

    '***************************************************************************************************
    '* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 13, 2009 *
    '***************************************************************************************************
    
    Dim cRow            As Long
    Dim sRow            As Long
    Dim lRow            As Long
    Dim sCol            As Long
    Dim lCol            As Long
    Dim dColumn         As Long
    Dim D               As Long
    Dim sCell           As Range
    Dim dRange          As Range

        
    On Error Resume Next
    
    Set dRange = Application.InputBox(Prompt:= _
                "Select Data Range with Duplicate values.", _
                    Default:=Selection.Address, Title:="Select Range", Type:=8)
    
    If dRange Is Nothing Then
    Exit Sub
    End If
    
    With dRange
        sRow = Cells(.Row, .Column).Row
        sCol = Cells(.Row, .Column).Column
        lCol = Cells(.Row, .Column + .Columns.Count - 1).Column
        lRow = Cells(.Row + .Rows.Count - 1, .Column + .Columns.Count - 1).Row
    End With
    
    dColumn = 0
    
    Do While dColumn = 0
       
    Set sCell = Application.InputBox(Prompt:= _
                "Select Column with Duplicate values.", _
                    Title:="Select Column", Type:=8)
    
    On Error GoTo 0
    
    If sCell Is Nothing Then
    Exit Sub
    End If
   
   
    If sCell.Columns.Count > 1 Then
        
        MsgBox ("Pls select Single Column only")
        
    Else
    
        dCol = sCell.Column
        If dCol < sCol Then
            MsgBox ("Select Column within earlier Range")
        ElseIf dCol > lCol Then
            MsgBox ("Select Column within earlier Range")
        Else
            dColumn = 1
        End If
        
    End If
    
    Loop
            
        For cRow = lRow To sRow Step -1
            If IsEmpty(Cells(cRow, dCol)) = False Then
                If WorksheetFunction.CountIf(Range(Cells(sRow, dCol), Cells(cRow, dCol)), Cells(cRow, dCol).Text) > 1 Then
                    Range(Cells(cRow, sCol), Cells(cRow, lCol)).Select
                    Selection.Interior.Color = 255
                    D = D + 1
                End If
            End If
        Next cRow
    
        Cells(sRow, sCol).Select
        
        If D = 0 Then
    
            MsgBox ("No Duplicate Values Found")
    
        Else
    
            caution = MsgBox(D & " Duplicate entries selected and marked RED" & vbCrLf & _
            "Do you want to delete them? " & vbCrLf & _
            "Marked entries will be deleted and data will move up " & vbCrLf & _
            "Do you want to Continue?", vbYesNo, "Confirmation")
    
            If caution = vbYes Then
                
                For cRow = lRow To sRow Step -1
                    If IsEmpty(Cells(cRow, dCol)) = False Then
                        If WorksheetFunction.CountIf(Range(Cells(sRow, dCol), Cells(cRow, dCol)), Cells(cRow, dCol).Text) > 1 Then
                            Range(Cells(cRow, sCol), Cells(cRow, lCol)).Select
                            Selection.Delete Shift:=xlUp
                        End If
                    End If
                Next cRow
                Cells(sRow, sCol).Select
            End If
        End If

End Sub

Download file having macro to find and remove duplicates within a range

Delete Duplicates , Delete Duplicate , Remove Duplicates , Remove Duplicate , Find Duplicates , Find Duplicate , Excel Delete Duplicates , Remove Duplicates Excel , Remove Duplicate Excel

Excel Macros : Find Last Row

2009-09-04T13:56:00.010+05:30

Many times we need to find out the last row of excel sheet which is used by data. This has many uses such as to

Add new column to the data only up to the last used row of exiting dateAdd new row to the data immediately after the last row so as you do not over write the exiting data.

See the code below for the doing the activities as listed above.

Sub LastRowOfData()
    
    '***************************************************************************************************
    '* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 4, 2009 *
    '***************************************************************************************************
    
    
    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If
    LastRow = GetLastRowWithData        ' Getting the Row number
    
    Range("C3").Select                  ' Select the header row of the data in new column
    ActiveCell.FormulaR1C1 = "Col 2"    ' Add new column header
    Range("C4").Select                  ' Select the starting column where you need to add data
    ActiveCell.FormulaR1C1 = "XYZ"      ' Add Data Value or Formula
    Range("C4:C" & LastRow).Select      ' This will select the column from starting row to Last row of data
    Selection.FillDown                  ' This will fill the data downwards from First row i.e. Formula or value added by you
    
    Range("B" & LastRow + 1).Select     ' This selects the starting cell of row next to last row of exiting data
    
    
End Sub

'********************************************************************
'* UDF to Get Last Row with Data on worksheet                       *
'********************************************************************
Public Function GetLastRowWithData() As Long

    Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long

    Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
    lLastDataRow = ExcelLastCell.Row
    lRow = ExcelLastCell.Row
    '
    Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    '
    lLastDataRow = lRow

    GetLastRowWithData = lLastDataRow

End Function

Function GetLastRowWithData gives inaccurate results if you have active filters on the worksheet. That is why I have included code to remove active filters from the worksheet before using this function to get last row of data.

Download sample file to check how it works.

There is one more option which gives you satisfactory results

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Take care to remove filters before using above code

There are some more options to find last row however are not very reliable as they give you last row of the worksheet irrespective of fact that row could be a empty row.

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
or
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

Select Last Row , Find Last Row , VBA Last Row , VBA Find Last Row , Excel Last Row

Hardware Locking for excel workbook with VBA

2009-07-25T11:47:00.013+05:30

Many time we desire that our workbook is locked to work with a particular machine. This can be achieved through Hardware Locking of your workbook using VBA. Some of the avaiable solutions will require you to buy external harware dongles and will cost you good amount of money.

However if you can lock your workbook with the existing hardware of the machine , you will not need external hardware dongles.

if you use following Excel function to get Mother Board Serial Number, you can compare the current machines mother board serial number with the desired one. If you know that the desired serial number does not match with current machine you know that there is security voilation and you can close the workbook through VBA. You will need to copy this code to regular VBA module of your workbook

Excel Function to Get Mother Board Serial Number

Public Function MBSerialNumber() As String
Dim objs As Object                                                                                                                     
Dim obj As Object
Dim WMI As Object
Dim sAns As String
Set WMI = GetObject("WinMgmts:")
Set objs = WMI.InstancesOf("Win32_BaseBoard")
For Each obj In objs
    sAns = sAns & obj.SerialNumber
    If sAns < objs.Count Then sAns = sAns & ","
Next
MBSerialNumber = sAns

End Function

After you have placed above function in VBA module. You will need to copy this code to ThisWorkbook section of your workbook . This is autoopen macro which compares the MBSerialNumber of current machine with the desired MBSerialNumber. In case it does not mactch, it will close activeworkbook.

Private Sub Workbook_Open()
    Set RMBSN = Sheets(1).Range("C4") ' This is whare you have already stored required MBSerialNumber
    If MBSerialNumber <> RMBSN Then   ' Checking if current machine serial number is matching with required
        MsgBox ("Data Security failier, This workbook will close") ' In case it does not match workbook will be closed
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    End If
End Sub

Download Excel file with macro for Hardware Locking for Excel Workbooks

and try to open it on your machine and see how it works. This is locked version and you will not be able to open it on your machine.

However you can down load Unlocked version of Hardware Locking for Excel Workbooks.

Excel Macros : VBA Expiry date for Excel Workbook

2009-07-18T21:39:00.011+05:30

Many times we have a situation where we need to force users to use updated file and stop using the old version of worksheet. The only way to do so is to put expiry date for your workbook. User will not be able to work upon the file once the expiry date is crossed and will be forced to look for updated version.

You will need to copy this code to ThisWorkbook section of your workbook .

Private Sub Workbook_Open()
    Dim Edate As Date
    Edate = Format("31/08/2009", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")
        ActiveWorkbook.Close
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If
End Sub

Once the expiry date is crossed, user will get message and workbook will be closed by macro
Once the expiry date in with next thirty days, user will get warning message about expiry date and number of days left.

Download file containing Workbook Expiry Date Macro

Excel Functions : Convert Numbers into Words

2009-07-02T15:09:00.072+05:30

Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. However there are customised functions available on the Internet. One such solution is available at Allexperts.com.

Display Numbers to Text.

You need to copy this to your regular macro module. Once you have added it to your file you can use function SpellNumbers to convert any number into words easily as you use any other function of excel.

Function available at the net covers USD as currency, whereas I needed it in Indian Rupees. I have modified this to give results in any currency. The revised version gives me results as shown in the screen cast below.

Download excel file having this user defined function to convert numbers to words

Make sure that you enable macros to use this function. In case macros are disabled this function will not work in downloaded file

Syntex for the modified UDF is :-
SpellCurr(MyNumber, MyCurrency, MyCurrencyPlace, MyCurrencyDecimals, MyCurrencyDecimalsPlace)

where
MyNumber = Numeric Value you need to convert into words
MyCurrency = Name of your Currency - i.e. Dollar for USA
MyCurrencyPlace = Prefix or Suffix the currency, use "P" for Prefix and "S" for Suffix
MyCurrencyDecimals = Name of your Currency Decimals - i.e. Cent for USA
MyCurrencyDecimalsPlace = Prefix or Suffix the currency decimals, use "P" for Prefix and "S" for Suffix

Modified code given below for those who want to use it. Currency inputs are optional and you will not need to input currency details in case you are using it for Indian Currency. Still this can be used for any currency provided you give currency inputs.

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

Function SpellCurr(ByVal MyNumber, _
Optional MyCurrency As String = "Rupee", _
Optional MyCurrencyPlace As String = "P", _
Optional MyCurrencyDecimals As String = "Paisa", _
Optional MyCurrencyDecimalsPlace As String = "S")

'*****************************************************************************************************************
'* Based on SpellNumbers UDF by Microsoft, Which handles only Dollars as currency                                *
'* UDF modfied by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on July 21, 2009                      *
'* UDF modified on September 04, 2009 to make currency inputs optional, by default it will use Indian Currency   *
'* This modified UDF can be used for any currency in case you provide for currency inputs                        *
'* User can define the Prefix and Sufix place for Currency and CurrencyDecimals                                  *
'* MyNumber = Numeric Value you need to convert into words                                                       *
'* MyCurrency = Name of your Currency - i.e. Dollar for USA                                                      *
'* MyCurrencyPlace = Prefix or Suffix the currency, use "P" for Prefix and "S" for Suffix                        *
'* MyCurrencyDecimals = Name of your Currency Decimals - i.e. Cent for USA                                       *
'* MyCurrencyDecimalsPlace = Prefix or Suffix the currency decimals, use "P" for Prefix and "S" for Suffix       *
'*****************************************************************************************************************

          Dim Rupees, Paisa, Temp
          Dim DecimalPlace, Count
    
          ReDim Place(9) As String
          Place(2) = " Thousand "
          Place(3) = " Million "
          Place(4) = " Billion "
          Place(5) = " Trillion "
      
          'String representation of amount.
          MyNumber = Trim(Str(MyNumber))
      
          'Position of decimal place 0 if none.
          DecimalPlace = InStr(MyNumber, ".")
      
          ' Convert Paisa and set MyNumber to Rupee amount.
          If DecimalPlace > 0 Then
              Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
              MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
          End If

          Count = 1

          Do While MyNumber <> ""
              Temp = GetHundreds(Right(MyNumber, 3))
              If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
              If Len(MyNumber) > 3 Then
                  MyNumber = Left(MyNumber, Len(MyNumber) - 3)
              Else
                  MyNumber = ""
              End If
              Count = Count + 1

          Loop
        
            If MyCurrencyPlace = "P" Then
                Select Case Rupees
                    Case ""
                        Rupees = MyCurrency & "s" & " Zero"
                    Case "One"
                        Rupees = MyCurrency & " One"
                    Case Else
                        Rupees = MyCurrency & "s " & Rupees
                End Select
            Else
                Select Case Rupees
                    Case ""
                        Rupees = "Zero " & MyCurrency & "s"
                    Case "One"
                        Rupees = "One " & MyCurrency
                    Case Else
                        Rupees = Rupees & " " & MyCurrency & "s"
                End Select
            End If
        
          If MyCurrencyDecimalsPlace = "S" Then
                Select Case Paisa
                    Case ""
                        Paisa = " Only"
                    Case "One"
                        Paisa = " and One " & MyCurrencyDecimals & " Only"
                    Case Else
                        Paisa = " and " & Paisa & " " & MyCurrencyDecimals & "s Only"
                End Select
          Else
                Select Case Paisa
                    Case ""
                        Paisa = " Only"
                    Case "One"
                        Paisa = " and " & MyCurrencyDecimals & " One " & " Only"
                    Case Else
                        Paisa = " and " & MyCurrencyDecimals & "s " & Paisa & " Only"
                End Select
          End If
      
          SpellCurr = Rupees & Paisa

      End Function

      '*******************************************
      ' Converts a number from 100-999 into text *
      '*******************************************
  
      Function GetHundreds(ByVal MyNumber)
          Dim Result As String
          If Val(MyNumber) = 0 Then Exit Function
          MyNumber = Right("000" & MyNumber, 3)
          ' Convert the hundreds place.
          If Mid(MyNumber, 1, 1) <> "0" Then
              Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
          End If

          ' Convert the tens and ones place.
          If Mid(MyNumber, 2, 1) <> "0" Then
              Result = Result & GetTens(Mid(MyNumber, 2))
          Else
              Result = Result & GetDigit(Mid(MyNumber, 3))
          End If
          GetHundreds = Result
      End Function

      '*********************************************
      ' Converts a number from 10 to 99 into text. *
      '*********************************************
     Function GetTens(TensText)

          Dim Result As String
          Result = ""           ' Null out the temporary function value.
          If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
              Select Case Val(TensText)
                  Case 10: Result = "Ten"
                  Case 11: Result = "Eleven"
                  Case 12: Result = "Twelve"
                  Case 13: Result = "Thirteen"
                  Case 14: Result = "Fourteen"
                  Case 15: Result = "Fifteen"
                  Case 16: Result = "Sixteen"
                  Case 17: Result = "Seventeen"
                  Case 18: Result = "Eighteen"
                  Case 19: Result = "Nineteen"
                  Case Else
              End Select
          Else                                 ' If value between 20-99...
              Select Case Val(Left(TensText, 1))
                  Case 2: Result = "Twenty "
                  Case 3: Result = "Thirty "
                  Case 4: Result = "Forty "
                  Case 5: Result = "Fifty "
                  Case 6: Result = "Sixty "
                  Case 7: Result = "Seventy "
                  Case 8: Result = "Eighty "
                  Case 9: Result = "Ninety "
                  Case Else
              End Select

              Result = Result & GetDigit _
                  (Right(TensText, 1))  ' Retrieve ones place.
          End If
          GetTens = Result
      End Function

      '*******************************************
      ' Converts a number from 1 to 9 into text. *
      '*******************************************
 
      Function GetDigit(Digit)
          Select Case Val(Digit)
              Case 1: GetDigit = "One"
              Case 2: GetDigit = "Two"
              Case 3: GetDigit = "Three"
              Case 4: GetDigit = "Four"
              Case 5: GetDigit = "Five"
              Case 6: GetDigit = "Six"
              Case 7: GetDigit = "Seven"
              Case 8: GetDigit = "Eight"
              Case 9: GetDigit = "Nine"
              Case Else: GetDigit = ""
          End Select
      End Function

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

Macros in Excel : Selecting Multiple Ranges in Excel VBA

2009-05-10T20:13:00.011+05:30

Selecting multiple ranges in Excel VBA helps your code to work faster. You can select multiple ranges in single line code and perform the action you want to perform.

Normally we code a range by writing it within "" as under

Range("A1:A10").Select

However if you put , between multiple ranges within the same line of the code you can select multiple ranges as given under

Range("A1:A10,D1:D10,F1:F10").Select

How to use Multiple Ranges in Excel VBA

Following macro code will explain you how to use multiple ranges in Excel VBA

Sub Multiple_ranges()
Range("A1:A10").Select                                                                                                       
MsgBox ("Single Range Selected")
Range("A1:A10,D1:D10,F1:F10").Select
MsgBox ("Multiple Ranges Selected")
Selection.Copy
Range("A11").Select
ActiveSheet.Paste
End Sub

In the above macro we have selected range three different ranges

A1:A10
D1:D10
F1:F10

After selection we have copied the contents to Range A11. Another option for doing this was to copy the contents one by one for each of the range. However copy and paste is only one of the example you can use this for any desired action by you.

You can combine multiple ranges into one Range object using the Union method.

The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C3:D4, and then formats the combined ranges as bold.


Sub MultipleRange()
    Dim r1, r2, myMultipleRange As Range
    Set r1 = Sheets("Sheet1").Range("A1:B2")
    Set r2 = Sheets("Sheet1").Range("C3:D4")
    Set myMultipleRange = Union(r1, r2)
    myMultipleRange.Font.Bold = True
End Sub

Macros in Excel : Forcing Users to Enable Macros

2009-05-03T21:30:00.013+05:30

Many times we face issue of disable macros situation while the user is using our project. Since the macros are disabled, there is no way that the macros in your project will work the dessired way. However you can force your users to enable macros while working with your files. Simplest way is to hide the main sheet in your project using xlVeryHidden property in your macro. The workseets hidden by using this method can be made visible only by using VBA. The unhide workseet dialog box does not display the names of sheets hidden by using VBA with xlVeryHidden property.

You need to have one sheet which displays message that macros are disabled and needs to be enabled to use this file. Once the macros are enabled, the message sheets can be hidden and data sheets can be made visible. You can copy the following code to Thisworkbook section of your VBA project.

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
    Sheets("Msg").Visible = True
    Sheets("Data").Visible = xlVeryHidden

End Sub

Private Sub Workbook_Open()

    Sheets("Data").Visible = True
    Sheets("Msg").Visible = xlVeryHidden

End Sub

First macro code makes Msg Sheet visible and hides Data Sheet. This macros runs on even of closing the workbook. Second macro makes Data Sheet Visible and hides Msg Sheet. This macro runs on even of opening of workbook.

You can add following line to first macro to compulsoraly save the workbook before closure. However use this only if you are sure that it is okay to save the file before closing.

   ActiveWorkbook.Save

In case you have multiple sheets and you want to hide all the sheets except the Msg sheet, use following code in ThisWorkbook Module. Make sure that the Msg sheet is first sheet of your workbook. Following code will keep first sheet visible on close and Unhide all other sheets and hide first sheet on open.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
On Error Resume Next
myCount = Application.Sheets.Count

Sheets(1).Visible = True
Range("A1").Select
For i = 2 To myCount
Sheets(i).Visible = xlVeryHidden
If i = myCount Then
End If
Next i
ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()

Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
On Error Resume Next

myCount = Application.Sheets.Count
For i = 2 To myCount
Sheets(i).Visible = True
If i = myCount Then
Sheets(1).Visible = xlVeryHidden
End If
Next i

End Sub

Download file with Macro code to Force users to Enable Macros

Code above is simplest way to force normal users to enable macros and make your project file work the way you want them to. However the advanced users can always get into your project by using VBA routine stored in onother file. This can be done by following the steps as given below.

1. Open the project file while the Macros are disabled.
2. Encable the Macros.
3. Unhide the hidden sheets in project file by macros code in another file

There are ways and means to overcome this situation also, but a advanced user will find a way to get into your project if they realy want to. So I feel there is no need to complecate the code for keeping them away. You can always make normal users to use your project file the way you want them to by using simpe code given above

Macros in Excel : Learn Excel VBA : Variables

2009-04-28T21:18:00.005+05:30

This is continuation of my earlier posts on

   Macros in Excel : Learn Excel VBA : Objects 
   Macros in Excel : Learn Excel VBA : Methods
   Macros in Excel : Learn Excel VBA : Properties

It is not necessary to declare a variable in VBA, Visual Basic automatically creates storage for a variable on first use in the code. Automatically created variables are of type variant and can contain any type of data. It can be strings, boolean values, object , arrays or numbers.

Simple statement such as given below creates a variable and assigns value to it

 Myvariable = 28

Similarly you can use different variables in calculation to create another variable for your VBA code. Following code will explain the use of Variables in Excel VBA

Sub Explain_Variables()

Var_A = 10
Range("A4").Select ' Value of Cell A4 is 5
Var_B = ActiveCell

Var_Total = Var_A + Var_B

Range("A5") = Var_Total

End Sub

This code explains that

1. Var_A is declared as constant number in the code itself.
2. Var_B is declared as value in ActiveCell i.e. Cell A4 in this case with Value as number 5.
3. We have used Var_A and Var_B to calculated Var_Total
4. We have used Var_Total to update the value in Cell A5, the result of this code is number 15 in Cell A5

Macros in Excel : Auto Open Macro / Auto Run Macro

2009-04-16T22:08:00.011+05:30

Many times we want certain task to be executed when you open a particular file. This can be achieved by auto open macro or auto run macro. This is the macro which executes itself when you open a excel file.

There are two methods to do this

Method 1. Auto open macro in VBA Project Module

In this case you add your macro code to VBA project module and it gets executed when you open the file. When you go to your VBA editor, you select a module as highlighted in the picture and copy following code to have auto run macro.

Private Sub Auto_Open()

MsgBox "This is auto open macro in Module"

End Sub

Method 2. Auto open macro in ThisWorkbook Section

In this method you add your code to Thisworkbook Section of your excel file. double click on highlighted potion in your VBA editor and paste following code

Private Sub Workbook_Open()

MsgBox "This is auto open macro in This workbook"

End Sub

You can replace the message box line with your code / action you want your macro to do. I have used this as example to keep my macro code short for better understanding.

Auto open Macro , Excel auto open macro , Auto run macro , Auto open excel , VBA excel workbook open

Macros in Excel : Disable Right Click Menu

2009-04-15T09:03:00.007+05:30

Many times we need to disable right click menu in our workbook. We can achieve this by using Marcos in Excel. You need to add the Macro code to This workbook section in your VBA project. When you go to your Visual Basic Editor you can see the ThisWorkbook on the left hand side of your VBA Project.

You need to double click on highlighed portion and copy the following code. Since these macros has Private word before Sub, no one will be able to see this macros from View Macro menu.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"For this file:", 16, ""
End Sub

Macros in Excel : Disable Cut Copy Paste

2009-04-14T19:32:00.015+05:30

Many times we need to disable Cut Copy and Paste in our workbook. You can achieve this by using Marcos in Excel. You need to add the Macro code to This workbook section in your VBA project. When you go to your Visual Basic Editor you can see the ThisWorkbook on the left hand side of your VBA Project.

You need to double click on highlighed portion and copy the following code. This code also disables the rightclick menu. Since these macros has Private word before Sub, no one will be able to see these macros from View Macro menu.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
With Application
.CellDragAndDrop = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
.CutCopyMode = False
End With
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=19) 'copy
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
    Ctrl.Enabled = True
Next Ctrl

End Sub

Private Sub Workbook_Open()
    On Error Resume Next
    With Application
    .CutCopyMode = False
    .CellDragAndDrop = False
    .OnKey "^c", ""
    .OnKey "^v", ""
    .OnKey "^x", ""
    .OnKey "+{DEL}", ""
    .OnKey "^{INSERT}", ""
    End With
   
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=19) ' Copy
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
        Ctrl.Enabled = False
    Next Ctrl
    
    End Sub

Private Sub Workbook_Activate()
    On Error Resume Next
    With Application
    .CutCopyMode = False
    .CellDragAndDrop = False
    .OnKey "^c", ""
    .OnKey "^v", ""
    .OnKey "^x", ""
    .OnKey "+{DEL}", ""
    .OnKey "^{INSERT}", ""
    End With
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=19) ' Copy
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
        Ctrl.Enabled = False
    Next Ctrl
   
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"For this file:", 16, ""
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
     Application.CutCopyMode = False
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    On Error Resume Next
    With Application
    .CutCopyMode = False
    .CellDragAndDrop = False
    .OnKey "^c", ""
    .OnKey "^v", ""
    .OnKey "^x", ""
    .OnKey "+{DEL}", ""
    .OnKey "^{INSERT}", ""
    End With
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=19) ' Copy
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
        Ctrl.Enabled = False
    Next Ctrl
    
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
With Application
.CellDragAndDrop = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
.CutCopyMode = False
End With
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=19) ' Copy
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
    Ctrl.Enabled = True
Next Ctrl

End Sub

Download file having disable cut copy paste Macro

Macros in Excel : Learn Excel VBA : Properties

2009-04-13T16:18:00.010+05:30

This in continuation of my earlier post on

   Macros in Excel : Learn Excel VBA : Objects
   Macros in Excel : Learn Excel VBA : Methods

Each VBA object has its own properties that controls its appearance. When we talk about range as object typical properties are

   - ColumnWidth
   - RowHeight
   - Font
   - Text
   - Value
   - Formula
   - Borders

Syntax in your macro is

    Object.Property = Value

    Range("A1").RowHeight = 60

    Range("A1").FormulaR1C1 = "=R[2]C+R[3]C"

Following instruction will set the columon width to 25

    Range("A1").ColumnWidth = 25

In case property is on left hand side of = sign it is updated with the new value mentioned on the right hand side of = sign

In case you have property on the right hand side of = sign it means you are reading value from that object. Following statement will set the column width of Cell A2 as 35 considering that Cell A1 has column widht of 25

 Range("A2").ColumnWidth = Range("A1").ColumnWidth + 10

If you are new to Macros in Excel, Please go through my earlier posts on

1. Automating Tasks in Excel : Using Macros in Excel
2. Recording Excel Macros / Writing excel macros (VBA)

Custom Formating - Excel Number Format

2009-04-10T07:57:00.015+05:30

Excel stores numbers as normal numbers only, however you can view them as you want based on how do you format the cells.

If you see there are pre defined number format options available when you choose cell format option, however you can use custom format option if available formats does not server your requirement.

There are four sections of format codes. The sections are separated by semicolons ";" First section defines the formats for positive numbers; Second section defines negative numbers; third section defines how to show zero values; and forth one define how to diplay text. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify only one section, all numbers use that format. If you want to skip a section, include the ending semicolon for that section.

# displays only significant digits and does not display insignificant zeros.

0 (zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.

? adds spaces for insignificant zeros on either side of the decimal point so that decimal points align.

? can also be used for fractions that have varying numbers of digits.

Special Symbol Shortcuts Using the ALT Key

2009-04-08T18:20:00.005+05:30

There are some special symbols which we need to use many times but they are not available on the keyboard. We can type them using ALT key and numeric code for the particular symbol

Pls see the chart for numeric codes for various symbols. If you need to type £ you need to press ALT key and keep it pressed while typing 0163. Release ALT key once you have typed 0163. Immediately on release of ALT key you will see £ sign appearing on your screen. You can follow the same process for getting other symbols by typing the relevant numeric code.

Macros in Excel : Learn Excel VBA : Methods

2009-04-03T13:32:00.020+05:30

This in continution of my earlier post on Macros in Excel : Learn Excel VBA : Objects

Visual Basic is an object oriented language. Objects have methods that perform action on them. In case range is the object you are working upon following methods would perform action on range object

    - Activate
    - Select
    - Cut
    - Copy
    - Clear
    - Delete
    - Paste

Syntex of many Visual Basis statements is

    Objects.Methods

Look at the following code to understand the use of methods in VBA

Sub Explain_Methods()
    Range("C4").Copy
    Range("C8").Select
    ActiveSheet.Paste
   
End Sub

If you are new to Macros in Excel I suggest that you go through earlier posts on Macros
1. Automating Tasks in Excel : Using Macros in Excel
2. Recording Excel Macros / Writing excel macros (VBA)
3. Macros in Excel : Learn Excel VBA : Objects

Macros in Excel : Pivot Table with Dynamic Data Range

2009-03-28T20:05:00.010+05:30

One common issue while creating / recording a macro for Pivot table is the data range of Pivot table gets recorded. Next time when you use the same macro data range for the pivot table will be same as it was at the time of recording of macro. If you do not notice this the report created by macro may represent wrong picture. This may be due to increase in the data size.

However there is a way to overcome this problem by using dynamic data range with the help of Excel Offset Function. Before creating the pivot by macro you need to create dynamic data range using offset function.

Then you create a Pivot with the named range.

See the macro code given below for better understanding

Sub Pivot_with_Dynamic_range()
' This creates Dynamic data range named "PvtData"

ActiveWorkbook.Names.Add Name:="PvtData", RefersToR1C1:= _
"=OFFSET('Data'!R2C1,0,0,COUNTA('Data'!C1),COUNTA('Data'!R2))"

' This creates Pivot using Dynamic data range named "PvtData"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"PvtData").CreatePivotTable TableDestination:="", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False

End Sub

The above code handles the major issue of dynamic data range and will create blank pivot table for your data, you need to add the required fields to complete your report. In this data is available at Sheet named "Data" and starting point of the data is Row 2 Column 1 or Cell "A2". You will need to change these references while using the above code in your macro.

Macro Pivot Table , Pivottable VBA , Pivot Table VBA , Pivot Table Macro , Dynamic Pivot Table , Pivot Table Dynamic Range

Macros in Excel : Fill Blank Cells With Data Above

2009-03-27T18:47:00.009+05:30

In case you need to fill the blank cells within a range with the data above it , use following macro code. Since this works on pre selected range of data, so make sure that you have selected the required data range before running your macro.

Let us take an example. if you want to fill the blank cells with in range "A2:D10", make sure that you have select this range before running the macro.

Sub Fill_Blank_Cells()
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"

End Sub

Please also refer to earlier post on "How to fill blank cells in excel data range with cells above it"

Macros in Excel : Find Blank Cells Macro

2009-03-26T18:36:00.005+05:30

In case you need to select blank cells within range , use following macro code. Since this works on pre selected range of data, so make sure that you have selected the required data range before running your macro.

Let us take an example. if you want to find blank cells with in "A2:D10", make sure that you have select this range before running the macro.


Sub Find_Blank_Cells()

    Selection.SpecialCells(xlCellTypeBlanks).Select

End Sub

Macros in Excel : Learn Excel VBA : Objects

2009-03-25T08:03:00.009+05:30

It is important to understand Visual Basic Grammar to better understand the macros recorded by you. If you are new to Macros in excel I suggest that you go through earlier posts on Macros
1. Automating Tasks in Excel : Using Macros in Excel
2. Recording Excel Macros / Writing excel macros (VBA)

Visual Basic is object oriented language, it means all items in Excel are considered as objects. There are lots of them but following example will help us understand what an Object is.


   - WorkBook
   - WorkSheet
   - Range
   - Chart
   - Legend

In a Macro Range("A1") is an object. An object can contain other objects.
WorkBook is the larger object followed by WorkSheet and Range.


Workbooks("Macros Book.xls").Worksheets("Data").Range("A1")

Code above refers to Cell "A1" on worksheet "Data" in "Macros Book.xls" file. This is long description but can easily be shortened. The worksheet containing the cursor is called Activesheet, similarly workbook containing active sheet is called Activeworkbook


ActiveWorkbook.ActiveSheet.Range("A1")

There is no difference in the previous line and line above in your macro code.

In case you do not refere larger object, Excel Macro will use ActiveWorkBook and ActiveSheet. So if you write Range("A1") it means same if you have your cursor at Worksheet "Data" in "Macros Book.xls" file while running this code.

Table below will explain the use of various objects on Excel VBA


WorkBooks - Referes to all workbooks currently open in Excel
WorkBooks.Item(1) - Referes to first workbook
WorkBooks.(1) - Referes to first workbook
WorkBooks.(Macros Book.xls) - Referes to Macros Book.xls file open

Sheets - Referes to all sheets in workbook both chart sheets and worksheets
Sheets(1) - Referes First sheet on tab bar.
Sheets("Data") - Refer to Sheet called Data

Write Macros in Excel / Clean or Tweak Excel Macro

2009-03-21T11:31:00.015+05:30

I am going to cover how to Write excel macro / Clean or tweak recorded excel macro. Those who are not aware about recording excel macro , I suggest them to go through my earlier post on

1. Recording Excel Macro

When you record a macro it records many actions which are actually not required. Pls go through the macro code given below recorded by record macro function of excel.


Sub MergeCells()
    Range("B4:C4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
End Sub

This code is doing three actions

1.Select dells in range B4:C4
2.Merge selected cells i.e. B4:C4
3.Unmerge selected cells i.e. B4:C4

If you see there is lots of unwanted action recorded by macro recorder. Similar action can be achieved by cleaning up the unwanted stuff. See the code given below which performs the same action.


Sub MergeCells_clean()
    Range("B4:C4").Select
    Selection.Merge
    Selection.UnMerge
End Sub

Once you have recorded your macro , you can lookout for unwanted stuff recorded by it. Cleaning up that will help you understand your macro better and you will be able to maintain your macro for long time.

Similar action can be achieved by two line code as under :-


Sub MergeCells_simple()
    Range("B4:C4").Merge
    Range("B4:C4").UnMerge
End Sub

Macros in Excel : Making your macro run every time

2009-03-18T19:23:00.013+05:30

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

Certain precautions while recording a macro will help you generate a code which will run every time you are using that macro.

One major issue when you download data from source system for further processing is the sheet name. It is different every time you download data. Since the macro recorded by you will record the sheet name as available at the time of recording of macro. Same macro will not work next time as the sheet name for downloaded data will be different.

To make it work you need to change the sheet name to generic name as first step while recording your macro.

See the code below which is recorded to change the sheet name as first step. This helps to record the same name in macro in the next step when you flip through the sheets.


Sub ChangesheetName()
    Sheets("Sheet1").Name = "Data"
    Sheets("Data").Select
End Sub

Once you have recorded above macro, small tweaking will help you to run this macro every time irrespective of the sheet name in downloaded data. You need to go to Visual Basic Editor and change the recorded macro as under

A small change in the first line of the code will help you run the macro without worrying about the sheet name of downloaded data.


Sub ChangesheetName()
    ActiveSheet.Name = "Data"
    Sheets("Data").Select
End Sub

Recording Excel Macro / Writing Excel Macro (VBA)

2009-03-02T19:28:00.012+05:30

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

If you are recording macros for the first time make you "Visual Basic" toolbar visible as it comes handy for recording.

You need to go to "View -> Toolbars -> Visual basic" Once you have made it visible it will look like as seen in picture below:-

Once you have this visible you are ready to record a excel macro.
Click on to start recording.
Once you have clicked , system will prompt with Record Macro dialog box as seen below.

Here the available fields can be changed by user. Macro Name can be any combination of key strokes of your choice. Keep the name you can relate it for job you want your macro to do. Once you click OK button, system is ready to start recording you actions and following toolbar appears on your screen to stop macro recording

Now you perform the task you want you macro to repeat,
Before you record or write a macro, plan the steps and commands you want the macro to perform. If you make a mistake when you record the macro, corrections you make are also recorded. When you record macros, Visual Basic stores each macro in a new module attached to a workbook.

Once you are through with all the steps , you need to stop recording by clicking on . Now your macro has been created by Excel using a programming language called Visual Basic® for Applications (VBA) to record your instructions. You don't have to know anything about programming or VBA to create and use macros.

Automating tasks in Excel : Using Macros in excel

2009-03-01T10:22:00.006+05:30

If you are doing a task again a again in excel you can automate it with a Marco. If you receive a data every day/Week/Month that you analyse the same way every time, you can create series of commands in shape of excel macro and do that task by single command of running that macro and completing your task by single click of mouse.

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

Macros are easy to create: you tell Excel to start recording, perform actions as you normally do, and then tell Excel when you're done. Behind the scenes, Excel uses a programming language called Visual Basic® for Applications (VBA) to record your instructions. You don't have to know anything about programming or VBA to create and use macros that will save you time and make your work easier

Refer to Recording Excel Macro / Writing Excel Macro for further details on Macros in Excel

Custom Formating - Excel Date Format

2009-02-15T18:52:00.009+05:30

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

You can view them as you want based on how do you format the cells. There are predefined date formats available when you choose format cells option. However you can use custom format option to display dates if available formats does not serve your requirement.

You can choose following codes or combination of it to view date in excel as you want. I have used highlighted codes dd mm yyyy with "/" to show today's date as 16/02/2009 in above screen. You can use any of these code singly or jointly with any character in between.

Excel Vlookup function - Entering column number

2009-01-30T20:33:00.012+05:30

Many users find it difficult to count and enter column number in Excel vlookup formula. This looks more difficult when the number of columns in the data range are large and gets complecated in case some of columns are hidden. However Microsoft Excel has inbuilt feature by which it helps you to know the column number. At the time of selecting table array it shows you the number of columns selected. If you notice this at the time of selection, you actualy do not need to count the number of columns. This works even if you have hidden columns in your data. Pls see video for further explanation.

Entering last day of the month in Excel

2009-01-24T19:09:00.009+05:30

Entering a specific date in excel is easy as you know what to enter, however if you are asked to enter last date of the month it becomes little tricky as last date of the month varies between 28 to 31 depending on what year and month it is.

However Excel date mathematics makes it easy. What you need to do is enter the first day of next month and then reduce it by 1, you will get last day of previous month.

Look at following picture to understand it better.

I have used date function to enter Feb 1, 2009 and then reduced the day by 1, other wise you can enter =Date(2009,2,0) also to get same result. after you have added above formula in Cell B4 you can use this formula to enter last day of next months in cell B5 =DATE(YEAR(B4),MONTH(B4)+2,0). You can further copy this down to get next month.

Formating cells in Excel

2009-01-20T20:45:00.006+05:30

Data entered in excel shows differently on your worksheet depending upon the cell format used by you.

January 19, 2009 which is stored by system as 39832 can shown as 19-01-2009 or 19-Jan-2009 or
19-Jan-09. There are many combination that can be achieved by changing the cell format for the cell containing that value.

Similarly you can add currency sign such as Rs / $ / £ / ¥ , it can be any sign of your choice.

Not only this you can actually add color codes the numbers depending upon the value.

You can choose all these options from Format Cell Menu. You need to choose Number tab than category as per requirement of the format.

Most tricky / dynamic category is Custom format at the end of the list. How to use this in more productive way will be covered in later posts on the specific subjects like custom formating dates, custom formating numbers etc in excel.

Excel date mathematics

2009-01-04T17:16:00.007+05:30

Date Add , Date Function , excel date mathematics , Excel Date math , using date , excel date calculate , excel date calculation , excel date function , excel date functions

How Microsoft Excel stores dates and times

2009-01-04T09:46:00.007+05:30

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

Dates and times are values and, therefore, can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

A day equals 1, half a day is .5 and an hour time interval is therefore 1/24=0.041666667

Dates entered without a specified year are assumed to be in the current year

Dates entered with a 2 digit year are stored as pre-2000 dates if the year is between 30 and 99 or as post-2000 dates if the year is between 0 and 29.

Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900 and Microsoft Excel for the Macintosh uses 1904 date system.

Two different date systems got created as 1900 is not a leap year and design of early Macintosh computers did not support dates before Jan 1, 1904. 1900, although divisible by 4, wasn’t a leap year. By using 1904 as a base, they saved a few bytes code, and every byte counted when they tried to squeeze the entire MacOS into 64KiB of ROM…

The following table shows the first date and the last date for each date system and the serial value associated with each date.

To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box. This setting is workbook specific, you can have two workbooks on the same system with 1900 and 1904 date system.

Be careful while changing the date system. The workbooks with actual dates recorded will also shift by 1462 days.

Similarly you may get into trouble when linking two workbooks set with two different date systems. in case you get struck with such situation you can correct this by adding or subtracting 1462 from one of the workbook as the case may be.

Suppose Book1 works on 1900 date system and book2 works on 1904 date system. If you are linking dates from 1904 to 1900 date system workbook, you will add 1462 to all the dates linked from 1904 date system. In a reverse scenario you will subtract 1462.

Dates in Excel , Excel Dates, Excel Date

Defining dynamic ranges / range names in Excel

2008-12-29T05:55:00.002+05:30

Many time we use a range in our working which keeps on changing with addition/deletion of data. Common problem here is that user needs to keep refreshing / redefining the range.

However we can use dynamic range / range names and do away with redefining problem for ever. This is done with the help of OFFSET function of excel.

Offset returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
See the picture on defining dynamic date range name.

Formula used is =OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B:$B),4)

See video for further explanation

Where all excel offset function can be used
You can use excel offset function wherever you use range in your formula. Such as Vlookup, Hlookup, Sum, Min, Max, Pivot tables etc.

Syntax
OFFSET(reference,rows,cols,height,width)
Reference is the reference from which you want to base the offset. Reference must be a reference to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value

Rows is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).

Cols is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).

If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.

Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.

If height or width is omitted, it is assumed to be the same height or width as reference.

How to fill blank cells in excel data range with cell above it

2008-12-25T20:35:00.002+05:30

Many times we need to compile data using pivot table. However when you copy data from a pivot table we find blank cells below a row label and it is big challenge to fill all those blanks with row lables. There is very simple way to fill all blank cells.

Step 1 - Select the entire range you need to fill

Step - 2Edit > Goto > Special > Blanks.

Once you click OK button, excel will select the blank cells.

Step -3 Enter the cell reference of immediate above cell in active cell without changing the selection. Hold control key and press enter

See video attached.