Microsoft Excel and VBA help

VBA code, extract Number from Text

noreply@blogger.com (Sarfaraz Ahmed) — Tue, 18 Aug 2009 18:57:00 +0000

During this post I will try to share VBA/Macro code to extract number from the text. Also, we will discuss the code to help you in understanding how to code in VBA. After all the aim is to make you enable write VBA code. Though in most of my post I write this at the end. This time I am advising you in middle to subscribe yourself to this blog. Also, your comments motivates us. Let's move ahead with first understanding the application/scenario under which you can use this code. Take an example where you Microsoft Excel file where you have series of number where text is between number and you are only interested in numbers and not next. If the text is on fixed place you can remove that using LEFT() , RIGHT() or MIDDLE() function. But if the numbers and text are placed in cell where you...

Visit blog for more details

VBA function to Concatenate

noreply@blogger.com (Sarfaraz Ahmed) — Thu, 06 Aug 2009 20:16:00 +0000

In this post, let's take a look at VBA code/function to concatenate string. Like in Microsoft Excel concatenate function you cant provide function with range. So you have to provide function with each cell at a time which is time consuming. The best solution to help VBA Code Function VBAConcatenate(target As Range, delimiter As String) As String Dim str As String For Each c In target str = str + delimiter + c Next c If delimiter = "" Then VBAConcatenate = str Else VBAConcatenate = Right(str, Len(str) - 1) End If End Function VBAConcatenate require following parameters Target: Target is range of cell you wish to...

Visit blog for more details

Book Mark Add-in

noreply@blogger.com (Sarfaraz Ahmed) — Tue, 04 Aug 2009 07:05:00 +0000

Huge reports with more than 5 sheets are very difficult to browse. Also, in spite of making very professional report you will it confusing. So, solution to avoid confusion is to add book mark sheet with link to each Microsoft Excel worksheet. This worked in my case with large number of reports. But the process is very hectic. Like adding a shape, adding text to shape and than linking it to right worksheets which no one would like to do for each reports you make. Hence, I developed an MS Excel add-in which will automatically does these all for you. Take a look at the video below how it works. How to use Bookmark Add-ins This add-ins cost only 5$. If you like to receive this add-in click on button below to pay via Paypal. We will send you add-ins via email. Instructions to...

Visit blog for more details

VBA Code to delete worksheets

noreply@blogger.com (Sarfaraz Ahmed) — Wed, 29 Jul 2009 17:43:00 +0000

In my last post we have seen how to add worksheets to Microsoft Excel workbook using VBA code. In this post we will delete the worksheet. Now, you have to be very careful while deleting worksheet because you won’t be able to recover any worksheet that deleted by using this VBA/Macro code. I have written two different set of codes. First one will delete active sheet while other one will delete based on sheet name provided by end user. I have purposely kept the prompt so that you don’t end up loosing any data while testing code. So, let’s move ahead with first set of VBA/Macro code. Sub DeleteActiveSheet() ' deletes active worksheet in the active workbook On Error Resume Next Dim str As String str = ActiveSheet.Name Sheets(str).Delete End Sub Warning: The...

Visit blog for more details

VBA code to Add worksheet

noreply@blogger.com (Sarfaraz Ahmed) — Tue, 28 Jul 2009 13:28:00 +0000

Today we will discuss a different method in VBA to add worksheet in MS Excel workbook. Now when I say different method I mean ways of adding worksheet based on your requirement. Let’s moved ahead and start with simple piece of code to complex one in steps and based on scenario. Scenario 1: Add worksheet with default name. Sub Addsheet() Worksheets.Add End Sub Note: The above code will add a worksheet before active worksheet. Scenario 2: Add worksheet with specific name. Sub Addsheet() Worksheets.Add().Name = "Sarfaraz" End Sub Scenario 3: Add multiple worksheets. Sub Addsheet3() 'Add Multiple worksheets Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4 End Sub Scenario 4: Add worksheets at...

Visit blog for more details

Sequence formula

noreply@blogger.com (Sarfaraz Ahmed) — Mon, 27 Jul 2009 04:23:00 +0000

Many a times you must want MS Excel to sequence the entry automatically. But thats not one of the features of MS Excel. So what you do is you enter the list than you type 1 in the top most row and drag it till last row. isn't it? This is most common practice I have seen around. Today here I will share to tricks on sequence. In first sequence type, we will count the based on number of rows filled without checking if the value is duplicate with respect to previous row. Formula: =IF(B6="","",COUNTA($B$6:B6)) In the above example, we are considering that we are sequencing based on column B starting from 6th row. The formula check if the value is present in B column and then counta find the count of non-blank from column B6 to respective row and returns count....

Visit blog for more details

Trunc function to remove decimal

noreply@blogger.com (Sarfaraz Ahmed) — Thu, 23 Jul 2009 18:41:00 +0000

This function remove the decimal part of number. I find this function useful only when decimal precision is not important. Like you only want to consider 2 decimal places and want to completely ignore the numbers beyond 2 decimal places or so. The most important thing about Trunc function is it does not round off, it completely removes decimal. Syntax: Trunc(Number, Precision) We will take few number for example to explain you how Trunc functions works. Like in all other post, I have enclosed image with example. Click on image below to view enlarge Also, if you face issue with replicating this example on MS Excel than download the MS Excel file with example. I am sure you will use this function in your day to work life. Download Example of Trunc Thank you for...

Visit blog for more details

Links for 2009-07-21 [del.icio.us]

Wed, 22 Jul 2009 00:00:00 PDT

MS Excel Workday function

Upper function to change case

noreply@blogger.com (Sarfaraz Ahmed) — Wed, 22 Jul 2009 06:37:00 +0000

This post is for beginners of MS Excel. This function convert all the characters to upper case(capitals). It is very useful when you have a data which is not in uniform case. Like data received from Data entry operators. Syntax: =Upper(Text) Attached below is snapshot with Upper function as example. Take a look, I am sure this will help you. Click on image below to view enlarge There are other function in MS Excel which help you with cases are Lower(), Proper(). Also, the Upper function is very useful when you want to compare text from two cells. Since Exact() function is case sensitive it treats 'a' and 'A' as two different letters. I have enclosed the link to my earlier post where I have demonstrated to find cases using exact function Check which CASE is used...

Visit blog for more details

Workday function

noreply@blogger.com (Sarfaraz Ahmed) — Tue, 21 Jul 2009 04:07:00 +0000

Workday function is used to find either past or future date before/after the given number of business days. The function only consider working/business days which mean while arriving to date it excludes Weekly off's and holidays. What I like most about this function is you can also provide it with holiday list you don;t want to be considered. Now if you are thinking what will be the application of this function than let me tell you whenever I decide any dead line for project I use this function to calculate date. This exclude all Weekends and holidays list which I provide to function. Also, this can be use to calculate delivery date or number of days of work performed. Let's take a look at the syntax of formula below. =Workday(StartDate, Days, Holidays) StartDate is the...

Visit blog for more details

VBA code, count using color

noreply@blogger.com (Sarfaraz Ahmed) — Wed, 15 Jul 2009 17:55:00 +0000

This is really funny thing in MS Excel I discovered today. Yes, a VBA code to count cells using colors. The intention behind is this post is to share VBA code and help you move ahead with VBA learning. This function is written by me. As you go below you will find formula syntax, vba code and add-ins. So, you can use add-ins on your system without replicating code and you can use this add-in all in open MS Excel file. Syntax: =CountColor(TargetRange,CellwithColor) TargetRange: Range of cells which you wanted to count under which you want to count cells using criteria as another formatted cell. CellwithColor: Single cell which filled with color which you want to count from TargetRange. Note: It wont count cells that are formatted conditionally. It may sound complex, but...

Visit blog for more details

Year Function

noreply@blogger.com (Sarfaraz Ahmed) — Tue, 07 Jul 2009 18:03:00 +0000

Year function is used to extract year from Date. It’s very useful when you want to summarize data at year level or you want to apply filter on year. This function in combination with other function is very useful in calculating past/future dates. I will share few examples of Year function in near future. Formula: =Year(Date) Click on image below to view enlarge Example1: Find the current year. Formula: Year(Today()) Click on image below to view enlarge. If you face any issue with replicating this example in MS Excel or understanding this function. I would suggest you to download Year function Excel file enclosed below Year Function Example Kindly let me know your view and experiences about using Year function. For free updates...

Visit blog for more details

YearFrac Function

noreply@blogger.com (Sarfaraz Ahmed) — Mon, 06 Jul 2009 18:43:00 +0000

Yearfrac functions returns the portion of years between dates. The most recommended format of return is percentage. Like if you take Jan 01, 2009 as start and Jun 30, 2009 as end, YearFrac will return 0.49 which is nothing but 49% of year. Formula: Year(StartDate, EndDate, Basis) StartDate: StartDate is date as the name suggest is beginning date which you want to be considered for calculation. Like, if you want to calculate fraction of year starting from day one of year than start date will be 01/01/2009. EndDate: Similar as above will be last date till which you want to find the fraction of year. Basis: There are five options available for choose based on which you will receive the results. Each Type is mentioned below. 0: US Calendar style where 30...

Visit blog for more details

Format Painter

noreply@blogger.com (Sarfaraz Ahmed) — Mon, 06 Jul 2009 17:42:00 +0000

Format painter is very useful tool when you only wanted to move format of one cell to another or range on worksheet. Using format painter is easiest way to replicate format on the worksheet. is a format painter icon which you will find on Standard toolbar. Follow the steps to use format painter to move format. Select the cell of which you want to copy the format. Click on the icon on Standard toolbar. The moment you click on Format painter icon, you will see that your cursor has changed to + paintbrush icon. Now you can apply the format by clicking on single cell or by dragging it on range of cell. However, if you want to apply the format to disjoint/non contiguous cells than steps will be bit different. Follow the steps to use format painter on...

Visit blog for more details

Countif with wildcard criteria

noreply@blogger.com (Sarfaraz Ahmed) — Tue, 30 Jun 2009 20:06:00 +0000

Here we will learn to use wildcard * in Countif function. Task is find count of all cell which starts with A or any character. There may be many ways which I may not be aware of, here I am posting something I tried and it worked. If you are not comfortable with Countif than this post is not for you. I would suggest you to visit my Countif Function. Syntax for countif function is as follows : Countif(Range, Criteria) Example: We wanted to find count of names starting with A and Sa from the list of names in A column of sheet. Suppose the names are in A2 to A10 range. Formula Countif(A1:A10, "A*") will return the count of all names starting with A and Countif(A1:A10, "Sa*") Click on image to view enlarge. Note: Criteria provided in countif is...

Visit blog for more details

Add-in and macro to convert seconds to hh:mm:ss

noreply@blogger.com (Sarfaraz Ahmed) — Fri, 26 Jun 2009 04:07:00 +0000

This add-in and macro will help you in converting seconds to hh:mm:ss. I have put add-ins for those who just wants to use the add-ins and macro for those who wants to understand this trick. This is very useful in BPO/Contact Center where you have source data in seconds and you have to represents data in hh:mm:ss seconds. Mentioned below is macro code Sub convert_hh_mms_ss() Dim i As Double Dim x As Integer Dim y As Long x = 1 y = 1 For Each c In Selection i = c / 86400 Selection.Cells(x, y) = i Selection.Cells(x, y).NumberFormat = "[h]:mm:ss;@" x = x + 1 Next c End Sub Logic: c is each...

Visit blog for more details

Remove password

noreply@blogger.com (Sarfaraz Ahmed) — Tue, 23 Jun 2009 04:19:00 +0000

Few days back I wish to remove the password from protected file. So, I started hunting for password removers for MS Excel in Google. I found a unique utility to quickly remove the password which I wanted to share with you all. The reason I am posting this here is there are many such utility available and few of them are paid as well. Also, I have already tested it by putting strong passwords. Download Password remover Add-in This utility is in the form of Add-in. So, click on the link below to follow the installation instruction. How to install MS Excel add-ins I would like to convey the special thanks to the developer. Kindly visit them as well http://www.straxx.com/excel/password.html Kindly let us know your comments about your experience about using this...

Visit blog for more details

VBA to close excel.exe

noreply@blogger.com (Sarfaraz Ahmed) — Sun, 21 Jun 2009 19:53:00 +0000

Closing all orphan Excel.exe process I came across this issue while I was generating reports on SQL server using MS Excel. Here we use to generate reports using MS Excel macro. However this macro's where executed via DTS in SQL. At times if any error occurs, the DTS use to close down leaving excel.exe running as orphan object. This not only sometimes prevent other excel objects but also slow downs your server performance. To tackle this we used the following code to close all MS Excel objects before opening new object. Sub Close_Excel() Dim strClsExl As String strClsExl = "TASKKILL /F /IM Excel.exe" Shell strClsExl, vbHide End Sub Warning: Kindly save all your work before trying above code as this will close all MS Excel instances running on computer...

Visit blog for more details

Weekday function

noreply@blogger.com (Sarfaraz Ahmed) — Mon, 15 Jun 2009 03:22:00 +0000

This function is used to find day of week from Date. Also, you can decide which day of week you want to begin the week. Like for different organizations have different week beginning and ending. We will first understand syntax and than move on two unique use of Weekday function. Syntax: = Weekday(DateEntered, WeekStart) WeekStart =1 sets Sunday as start that is 1 and Saturday as end which will7 WeekStart = 2 sets Monday as start that is 1 and Sunday as end which will be 7 WeekStart = 3 sets Tuesday as start that is 1 and Monday as end If you don't provide any WeekStart then by default MS Excel assume it to 1. Take a look at image below with weekday formula. Click on image below to view enlarge Example 1: We want to find out day...

Visit blog for more details

INT function

noreply@blogger.com (Sarfaraz Ahmed) — Sat, 16 May 2009 04:49:00 +0000

INT rounds of the number to lowest and nearest whole number. INT function does not require any special formatting. Syntax: INT(Number) Number Integer Formula 3.5 3 =INT(3.5) 7.2 7 =INT(7.2) 15.99 15 =INT(15.99) -22.7925 -23 =INT(-22.7925) Example: A competitive exams require the 18 years of age to appear in exams. Here is example on finding candidate with age over 18. Click on image to view enlarge I have uploaded the MS Excel file with INT function and INT function example to ensure that we don't miss anything with respect to basic understanding of INT...

Visit blog for more details

Rand and Randbetween function to generate random numbers

noreply@blogger.com (Sarfaraz Ahmed) — Fri, 15 May 2009 16:41:00 +0000

Rand and Randbetween is used to generate random numbers. It is very useful for people like me who post on blog. This help me in generating random data which I use in post on blog. Randbetween is available in MS Excel 2007 while RAND function is available in MS Excel 2007/2003/XP/2000. Randbetween function has an edge over Rand function as it can generate numbers between lower bound and upper bound and gives user a control to over generation of random numbers. Rand() Function Syntax: Rand() RAND() function does not require any special formatting. However it returns number in decimal or in others words it returns you number between 0 to 1. Take a look at the image below with example of RAND() function. Click on image to view enlarge Randbetween() function ...

Visit blog for more details

Product function

noreply@blogger.com (Sarfaraz Ahmed) — Tue, 12 May 2009 18:54:00 +0000

This is simple multiplication function. Very useful in almost all worksheet. However, we prefer to use other alternative to multiply numbers. The alternative to Production function is using cell reference in following manner: A1*B1*C1 which is equivalent to Product(A1,B1,C1) Syntax =Product(number1, number2, number3........,number30) =Product(Rangeofnumbers) =Product(number, Rangeofnumbers) Click on image below You can download the file with Product formula example. I am sure this will help you in learning this function. Download Product Example File Please do let me know your comments about my attempt to help beginners with Product function. Also, you can subscribe via email We assure you knowledge, not SPAM!

Visit blog for more details

Shortcut key to enter Date and Time in worksheet

noreply@blogger.com (Sarfaraz Ahmed) — Mon, 11 May 2009 15:57:00 +0000

Using NOW function refreshes each time when you make entry to worksheet. Hence, I dont prefer to use NOW function. I use shortcut key to enter time or Date to worksheet. This trick is helpful in saving time. For Time Shortcut key: Ctrl + Shift + ; For Date Shortcut key: Ctrl + ; Please do let me know your comments about my attempt to help beginners with Shortcut key to enter date and time. Also, you can subscribe via email We assure you knowledge, not SPAM!

Visit blog for more details

Now function for Time

noreply@blogger.com (Sarfaraz Ahmed) — Mon, 11 May 2009 15:36:00 +0000

Now function is used to enter current date and time. The Now function keeps on updating time whenever you open workbook or when you make entry to worksheet. This function is useful when you want to use current date and time. Formula: Now() Now function returns date and time. However the format of cells plays very important role. Click on the image below to take a look of different cells formatted returns date and time in different date and time formats. In simple words, Now functions returns date and time based on cell format. Click on image to view enlarge For further help, you may download MS Excel file with Now function example. Now Function Example download Please do let me know your comments about my attempt to help beginners with Now...

Visit blog for more details

SUMPRODUCT function

noreply@blogger.com (Sarfaraz Ahmed) — Sat, 02 May 2009 04:20:00 +0000

The SUMPRODUCT in MS Excel is useful when you want a multiple a set of values to another set of value. This formula is very useful in scenario when you want to calculate the total sales value or cost. Let's take look at syntax. Syntax: SUMPRODUCT(array1, array2, array3....) Table Set A Set B a1 b1 a2 b2 a3 b3 a4 b4 . . an bn SUMPRODUCT({a1, a2, a3, a4...an}, {b1, b2, b3, b4...bn}) = a1 X b1+a2 X b2+a3 X b3+a4 X b4....an X bn Click on image below to Enlarge In the above example, we have describe an example to calculate Total sales value and Total Cost value. Also, I have enclosed the link with...

Visit blog for more details

VBA, For Next loop

noreply@blogger.com (Sarfaraz Ahmed) — Wed, 29 Apr 2009 19:18:00 +0000

For next is looping method when you are definite about number of values you want to scan/test. However, you will have to use IF then to test the values. Lets take a look at very basic example of For Next loop and then we will move at little complex examples. Syntax: For counter = startvalue to endvalue Operation you want to perform Next counter Next counter increase the value to loop till counter reaches end value Here you want to just enter random numbers starting from active cell to next 10 row using VBA/Macro code For Next loop Example 1 Sub Fornextloop1() Dim i As Integer 'We will use i as counter For i = 0 To 9 ActiveCell.Offset(i, 0) = WorksheetFunction.RandBetween(25, 250) Next i End Sub ...

Visit blog for more details

Links for 2009-03-24 [del.icio.us]

Wed, 25 Mar 2009 00:00:00 PDT

Shortcut to show all formula
Countif function
Free countif function help
SUMIF function to Summarize data
Calculate End of Month using EOMONTH function

Links for 2009-03-15 [del.icio.us]

Mon, 16 Mar 2009 00:00:00 PDT

Days360 function in MS Excel
Free MS Excel tutorial, MS Excel help
MS Excel, Auto Sum
Free MS Excel help, Auto Sum
Abs Function MS Excel
Free MS Excel help, Macro tutorial

Links for 2009-03-10 [del.icio.us]

Wed, 11 Mar 2009 00:00:00 PDT

Vlookup function to find if value is present in range

Links for 2009-01-29 [del.icio.us]

Fri, 30 Jan 2009 00:00:00 PST