Install add-ins

Add-ins are basically xla(MS Office 2003) and xlam(MS Office  2007) with built in user define functions or macros.  Add-ins can be created by recording macro or writing macro/function in MS Excel workbook and save the workbook using Save as option.  When you click on save as option, you will find xla option under ‘Save as type’ drop down.  Once you click on xla or xlam option you will find that this file automatically saves in an add-in folder which is located under following path.

C:\Documents and Settings\<>\Application Data\Microsoft\AddIns

So, in case if you receive or download any add-ins then you should follow the steps below to install add-ins on your computer.  For example I have enclosed the add-ins n2t which has function to convert numeric to text (123 – One Hundred and twenty three)

For MS Office 2003

1. Copy the add-in in the following folder
   
2. C:\Documents and Settings\<>\Application Data\Microsoft\AddIns
   
3. Open  MS Excel, click on tools and click on add-ins 
   
4. Check the box adjacent to N2t.
   
5. After you install follow the steps to check if add-ins installed properly.
   
6. Type =n2t(A1) in the B1 cell and type 123 in A1. N2t will return  One Hundred and twenty three
   

For MS Office 2007

1. Copy the add-in in the following folder
2. C:\Documents and Settings\<>\Application Data\Microsoft\AddIns
   
3. Click on MS Office icon on the top right hand side corner. 
   
4. Click on Excel options button
   
5. Select the add-ins  and click on Go button at the bottom
   
6. Check the box adjacent to N2t.
   
7. After you install follow the steps to check if add-ins installed properly.
   
8. Type =n2t(A1) in the B1 cell and type 123 in A1. N2t will return  One Hundred and twenty three
   

For practice, please click on link below to download sample add-in. 

Click here to download sample add-ins

Read more on this article...

How to open xlsx file in MS Office 2003 or earlier version

The new version of MS Excel which is Microsoft Excel 2007 saves the file in xlsx format. If you have MS Excel 2003 than you won’t be able to open these files. Hence, you need Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats.

Enclosed below is link to download file convertor.

FileFormatConverters

For more information about the Compatibility Pack, see Knowledge Base article 924074 on Official Microsoft website.

I am sure this will definitely resolve your problem of opening files saved MS Office 2007 version. If you find this information useful, please leave your comments/feedback.

Note: We are not associated with Microsoft in any manner. This blog is just an effort to make people realize the potential of Microsoft Excel and share my knowledge

Please do let me know your comments about my attempt to help for people struggling with xlsx files.  Also, you can subscribe via email to receive updates

We assure you knowledge, not SPAM!

Read more on this article...

Find color pattern of cell and filter using Color

Now many of you think what is use of finding color of cell. How that will be helpful. Let me start you explaining use of finding color of cell. Like MS Office 2007 you can filter using color. However, same is not available in previous version of MS Excel. So, you can find the color of cell as numeric value and using filter on this column to filter all the values having same format. Isn’t it a good idea?

You just need one line of Code to get the color of cell. So, you can write that as function your MS Excel workbook or you can write a add-in and save on your computer.  So, you can use this formula across all MS Excel workbook. I will provide another post on creating add-ins. Here, we will use the function to find color. Also, each color will be returned as number which can be used to filter

Mention below is piece of code to find the number for color used in cell.

Function findcolor(target As Range) As Integer

findcolor = target.Interior.ColorIndex

End Function

Enclosed screenshot is below show how to use the findcolor formula on worksheet after you include in MS Excel workbook.

Click on image to view it large

If you still have doubts, I have uploaded the file which you can download. If you like the above post, please post your comments.

Click here to download  MS Excel find color Example

We assure you knowledge, not SPAM!

Read more on this article...

How to calculate Exact Age

There are many alternate methods to calculate exact age in MS Excel. In this post, i will try my best to explore Datedif function and using Datedif to find age.

Datedif require three parameters like past date, current date, type of answer you need. Like whether you need answers in day, month, year, days excluding year, months excluding year and days excluding months. This may sound little confusing now. But do not worry. By the time, you will complete reading this post. I am sure you will learn one more function of MS Excel.

Earlier I have posted long formula. But, in the course of time I got this new idea which i wish to share with you.

Past Date	Current Date	Interval	Formula pattern	Results
11/24/1978	2/26/2009	D	=DATEDIF("11/24/1978","3/26/2009","D")	11080
11/24/1978	2/26/2009	Y	=DATEDIF("11/24/1978","3/26/2009","Y")	30
11/24/1978	2/26/2009	M	=DATEDIF("11/24/1978","3/26/2009","M")	364
11/24/1978	2/26/2009	YM	=DATEDIF("11/24/1978","3/26/2009","YM")	4
11/24/1978	2/26/2009	YD	=DATEDIF("11/24/1978","3/26/2009","YD")	61
11/24/1978	2/26/2009	MD	=DATEDIF("11/24/1978","3/26/2009","MD")	2

Here is description on interval parameter

D will return number of days between two days.

Y will return number of years between two days.

M will return numbers of months between two days.

YM will return number of months left after subtracting years or months if both dates have difference of less then one year.

YD will return number of days after subtracting years. Like 11/24/1978 and 2/26/2009 has difference of 30 years between 11/24/1978 to 11/24/2008 but there are 61 days between 11/24/2008to 2/26/2009.

MD will return days left after subtracting 11/24/1978 to 02/26/2009

Now lets us e this to calculate age in MS Excel. Suppose we have Date of birth store in A1 cell as shown in the image below. Then formula to calculate age will be as follows.

= DATEDIF(A1, TODAY(), "Y") & " yrs " & DATEDIF(A1,TODAY(),"YM")& " mnths " & DATEDIF(A1,TODAY(),"md") & " dys"

Click on image below to see example

This is very useful in calculating age of person, a ticket pending at customer service or anywhere where you want to track age.

You don't have to pay anything for leaving comments. So, please think about it. Also, you can receive free add-ins, e-books and update via email

We assure you knowledge, not SPAM!

Read more on this article...

Passing values from dynamic range to dynamic array variable

In Microsoft Excel VBA, often we want to pass the range to array. Mentioned below code is simple example. The post will help you in understanding how to declare dynamic array as well. The dynamic array is one where you provide end user to input any number of values. 

Function CheckArray(Target As Range) As String

‘The code will will declare dynamic array

Dim FirstArray() As Variant

Dim cnt As Integer

Dim str As String

cnt = Target.Count

‘The code will fix the range of array based on number

‘of items available in target which is range

ReDim FirstArray(1 To cnt)

i = 1

‘The code below will move the range into array.

‘Also, it combines all the values in range and return them

‘seperated by space like concatenate

Do While i <= cnt

FirstArray(i) = Target.Item(i)

    str = str & " " & FirstArray(i)

    i = i + 1

Loop

CheckArray = str

End Function

I am sure this post has given you clear idea passing range into array variable. Also, you can visit the Microsoft page below which describe array in details. Our aim is to help the blog visitors with practical use. 

http://msdn.microsoft.com/en-us/library/aa164778(office.10).aspx

So far I am attempting to give you basics of VBA. Kindly post your comments.

Function CheckArray(Target As Range) As String

‘The code will will declare dynamic array

Dim FirstArray() As Variant

Dim cnt As Integer

Dim str As String

cnt = Target.Count

‘The code will fix the range of array based on number

‘of items available in target which is range

ReDim FirstArray(1 To cnt)

i = 1

‘The code below will move the range into array.

‘Also, it combines all the values in range and return them

‘seperated by space like concatenate

Do While i <= cnt

FirstArray(i) = Target.Item(i)

    str = str & " " & FirstArray(i)

    i = i + 1

Loop

CheckArray = str

End Function

I am sure this post has given you clear idea passing range into array variable. Also, you can visit the Microsoft page below which describe array in details. Our aim is to help the blog visitors with practical use. 

http://msdn.microsoft.com/en-us/library/aa164778(office.10).aspx

So far I am attempting to give you basics of VBA. Kindly post your comments.

Read more on this article...