ExceLiTems.com (Ashish Jain)

How to use MS Excel Macro Recorder ?

noreply@blogger.com (Ashish Jain) — Fri, 13 Jan 2012 23:27:00 PST

What is a macro?
A macro is a set of computer instructions that you can record and associate with a shortcut key combination or a macro name. Then, when you press the shortcut key combination or click the macro name, your computer program carries out the instructions of the macro. This saves you time by replacing an often-used, sometimes lengthy series of actions with a shorter action.

How to record a macro?

Let's record a macro to understand it and learn to save a lot of time for other activities.

Step 01: Go to Developer Tab and hit 'Record Macro' in the code section.

Step 02: In the record macro window, provide these details:

Macro Name: Provide a macro name without spaces. Use underscore(_) or hyphen (-) instead to make it more meaningful and readable.

Shortcut Key (Optional): Provide a shortcut key to recall/run the macro, whenever you need. Esp. if you are going to use this macro frequently.

Store macro in: Either This Workbook or Personal Workbook. If you store in 'This Workbook', the macro is available for use with in this workbook only, so you can use it if you need that other users to whom you are sending this workbook can also use it but if you want to use this macro only on your system on several workbooks for personal purposes then choose 'Personal Workbook' option.

Description (Optional): It will help you and your fellow users to understand the purpose of macros in future.

Step 03: Perform your actions which you want macro to do in future. For eg. Select, color, delete, adjust row/column height or width, enter some text, sort a table etc etc.

Step 04: Stop the macro by hitting 'Stop Recording' button.

How to use the recorded macro?

Let's learn to use the recorded macros and save our time for some fun at work.

Step 01: Go to Developer Tab and hit 'Macros' or alternatively, press 'ALT + F8' combination to view the macros.

Step 02: Select your macro and hit the 'Run' button.

That's it. Isn't it simple ? From beginners to advanced Excel and VBA users still use the macro recorder but each user have different purpose to use this awesome feature of Microsoft Excel.

MS Excel & VBA: Contact for Corporate Trainings

noreply@blogger.com (Ashish Jain) — Fri, 02 Sep 2011 00:07:00 PDT

What makes us one of the best Corporate Excel Trainer?

1. Our Course Content (as given below)
2. Microsoft Certified Professionals / Microsoft awarded "Most Valuable Professional"
3. Training exposure to all industries. viz. Electronics to Finance.
4. Ready spreadsheets for hands on experience.
5. Step-wise example workbooks.
6. Understanding of real needs.
7. Quality assessment
8. Relevant examples.

We're flexible with training hours and course content. Please go through our standard course content given below and feel free to contact us anytime for your queries and confirmations.

Call us: 09999-40-48-43 / 09968-615-632
E-Mail: training@OpenExcel.com

MS Excel 2007/2010

Beginner

Content:
Introduction to Excel
Open, Close and Save
Manipulating Text
Styling and Formatting
Playing with Numbers
Date and Time Operations
Dealing Hyperlinks
Introduce Formulas
Duration: 08 Hrs

Intermediate

Content:
Formulas
Functions
Sum and Count
Daily use functions
Named Ranges
Filter and Sort
Printing
Import Text files
Organizing Data
Duration: 20 Hrs

Advanced

Content:
Pivots tables
Charts
Subtotals
Goal seek
Scenarios
Data Consolidation
Group and Outline
Security and Protection
LOOKUP Functions
Duration: 12 Hrs

Visual Basic for Automation (VBA)

Beginner

Content:
Introduction to VBA
Introduction to VBE
Creating Sub routine
Creating Function
Excel Object Model
Using EOM
Using Macro recorder
Examples
Exercises
Duration: 08 Hrs

Intermediate

Content:
Variables and Constants
Conditional statements
Loop statements
Error handling
Debugging
Using Excel Functions
Event Macros
Using Forms
Interactive Charts
Dealing with Pivots
Examples
Exercises
Duration: 20 Hrs

Advanced

Content:
Connect to Web
Class Modules
Connect to SQL/Access
Using Windows API
Commandbars/Toolbars
RibbonX
Examples
Exercises
Duration: 16 Hrs

Visual Studio Tools for Office (VSTO)

Beginner

Content:
Introduction to VSTO
New Features
Running VBA project with VSTO
Variables and Constants
Conditional statements
Loop statements
Make and Run First Project
Examples
Exercises
Duration: 04 Hrs

Intermediate

Content:
Create VSTO add-in
Create and Use Dlls
Error handling
Debugging
Using Excel Functions
Event Macros
Using Forms
Charts and Pivots
Security
Examples
Exercises
Duration: 16 Hrs

Advanced

Content:
Connect to Web
ODBC Connection
Using Windows API
RibbonX
Crystal Reports
Examples
Exercises
Duration: 20 Hrs

We need Work ! We want to help you ! Let's Connect :)

noreply@blogger.com (Ashish Jain) — Thu, 01 Sep 2011 23:45:00 PDT

- Do you have work and running short of time?
- Looking for expertise in the dashboard automation, Excel, Report making or any Excel/VBA work?
- Looking for some affordable source to get work done?
- Tired of extending deadlines?
- Never find anyone achieving expectations?
- Your solution provider never talked about new ideas?

If you answer any of the above questions in YES, then you seriously need to think about EXCEL ITEMS.

The team at EXCEL ITEMS comprises of Commerce Graduates, Science Graduates with specialization in Mathematics and Microsoft awarded "Most Valuable Professional" with total experience of 17 years.

Our industry experience ranges from Health care to Information Technology, Finance to Insurance or Capital markets. Our products can take any form exceeding customer expectations. For eg. we can integrate different platforms and technologies like MS Office, Java, HTML, XML, .NET, SQL to make the product more robust, usable and fast.

We understand the value of your hard-earned money and hence we charge as low as $5 per hour.

We may not be good in marketing ourselves but surely, we are good in our services and in our work. If you are interested or have any queries, drop us a mail on work@openexcel.com. Let's Connect :)

15 Essential MS Excel VBA Puzzles for Interview

noreply@blogger.com (Ashish Jain) — Tue, 30 Aug 2011 17:56:00 PDT

Thanks for the humongous response to our 50 Excel VBA Oral Interview questions and 3 Things to remember before you go for an Excel Interview. This really motivated us to bring this article for you. We all Excel lovers and learners are climbing a virtual staircase day-by-day, taking us to somewhere top of the world. Be confident, help others, share your experience and learn new knowledge and experiences out of it.

In recent times, we have seen a great demand for Business Analysts, Data Analysts and other jobs which require good skills in Excel and VBA. Excel is used in every company and at home, while beginner misunderstoods it as a simple tool, amateurs, professionals and experts are still learning it, like you, me and everyone else and diving in the ocean of Excel to find new pearls everyday.

In this article, we've presented some VBA Macro programs which are generally asked in VBA Interviews to check the skills of job aspirants. These VBA Interview questions ranges from logical problems, charts, pivots, event macros to creation of UDFs.

Tip for Interviewers: You must do slight change to these questions, as many interviewee knows this website. Wish you all the best in selecting BEST candidate.
Tip for Interviewee: Read my tip to interviewers and be ready. Wish you all the best in your future endeavours.

Logical VBA Interview questions

Input a number between 1 to 10 to create pyramid.
   Description:
          i. Warn user, if he enters anything except a number between 1 to 10.
          ii. Pyramid must look like as presented in image below.
          iii. Create a single procedure to achieve this.

Input an alphabet and based on its alphabetic position(A=1, B=2 etc.) create a square grid.
   Description:
          i. Warn user, if he enters anything except an alphabet from A to Z.
          ii. Square grid must look like as presented in image below.
          iii. Create a single procedure to achieve this.

Charts VBA Interview questions

Change the number format of axes and labels of all charts in workbook.
   Description:
          i. Number format of axes should be like 10,20, 30 i.e. no decimals.
          ii. Number format of labels should be like 12.1,15.6, 25.5 i.e. one place of decimal.
          iii. Create a single procedure to achieve this.

Resize and rotate all charts by 90 degree in clock-wise direction in the given workbook.
   Description:
          i. Resize all charts of the workbook to the dimensions of chart named as "Main_Chart" in first sheet.
          ii. Then, rotate all charts by 90 degree in clockwise direction.
          iii. Create a single procedure to achieve this.

Run Charts Slideshow in Excel.
   Description:
          i. Provide option to go on next chart or exit the slideshow.
          ii. Charts must not be displaced from their initial position by the end of slideshow.
          iii. Size of charts must not be changed from their initial size by the end of slideshow.

Pivots VBA Interview questions

Write a VBA macro to remove and add an existing calculated field from pivot.
   Description:
          i. Remove and add the existing calculated field with prompts informing the respective operations.
          ii. Perform your operation on provided pivot table and calculated formula field.
          iii. Create any number of procedures to achieve this.

Count the number of Pivots and Charts in Active workbook.
   Description:
          i. Count all the pivot tables and charts of each worksheet in active workbook.
          ii. Create a separate sheet for result with number of charts and pivots in each worksheet.
          iii. Create a single procedure to achieve this.

Record the user actions on Pivot Table in the provided cells.
   Description:
          i. Fill the yellow cells in the provided worksheet with changed filter name and time.
          ii. Create a single event procedure to achieve this.

General VBA Interview questions

Write a VBA macro to import all Tables from provided Access Database to Excel worksheets.
   Description:
          i. Write the VBA macro as if access database and excel file resides in same directory always.
          ii. Access Database is a mdb file i.e. Office 2003 format.
          iii. Create a single procedure to achieve this.

Create Access Database from provided Excel Workbook.
   Description:
          i. Each sheet of provided Excel file must represent separate table in Access database.
          ii. Access database should be of Office 2003 format i.e. a .mdb file.
          iii. Create a single procedure to achieve this.

Create User Defined Function to validate email addresses.
   Description:
          i. To validate remember it should check exact one @ character.
   ii. It must only contain alphanumeric set, underscore(_),hyphen or dash(-) and period/dot(.)
   iii. Leftmost and Rightmost character should not be .
   iv. Period/dot (.) must appear at least once after the @.
   v. There should be either 2 or 3 characters(eg. com, net, org, in, us, uk etc) after the last period (.)
   vi. There must be at least one alphanumeric character before @
   vii. Create a single UDF procedure to achieve this.
   viii. Test your UDF function against the provided dummy e-mail addresses list.

Create a workbook which a user can update and save but not be able to save the updates.
   Description:
          i. Do not apply worksheet or workbook protection.
          ii. If macros enabled, a user can update the workbook and save it.
          iii. But in reality, workbook is not saved and even do not warn or prompt anything.
   iv. Create a single procedure to achieve this.

Create a UDF to extract first set of alphabets from alphanumeric strings/data.
   Description:
          i. Extract first set of alphabets from required list.
          ii. For eg. extract eXceL from eXceL123iTems.
          iii. Create a single procedure to achieve this.

When SEARCH button is clicked,search selected text on Bing in Internet Explorer.
   Description:
          i. Warn user, if the selection is a blank cell.
          ii. Warn user, if the selection is not a range or a single Cell.
   iii. Create a single procedure to achieve this.

Write an Excel VBA macro to select the text files through a window and delete them.
   Description:
          i. Provide file selection window, in which he can see only text files of a folder.
          ii. Prompt user for confirmation before actual deletion.
          iii. Create a single procedure to achieve this.

Relative and Absolute References in Excel Formulas

noreply@blogger.com (Ashish Jain) — Sun, 28 Aug 2011 21:11:00 PDT

When the reference is made to the Absolut Vodka, it's called absolute reference and when the reference is made to some family member, it's called Relative reference.. Hahhahaa...Just kidding..On serious note, absolute and relative reference is related to Excel Formulas and that's why it's here. Whenever we drag formulas or use auto-fill option to quickly fill the formulas, the reference of formulas may change depending on their type.
What are the types of references?1. Absolute Column/Absolute Rowe.g. $A$1means Whether you use auto-fill across column or across row, the reference won't be changed.
2. Absolute Column/Relative Rowe.g. $A1means When you use auto-fill across column, it will remain column A but when you auto-fill across row, the reference will change to $A2, $A3, $A4 and so on.
3. Relative Column/Absolute Rowe.g. A$1means When you use auto-fill across row, it will remain row 1 but when you auto-fill across column, the reference will change to B$1, C$1, D$1 and so on.
4. Relative Column/Relative Rowe.g. A1means When you use auto-fill across column, the reference will change to B1, C1, D1 and so on and when you auto-fill across row, the reference will change to A2, A3, A4 and so on.How to change the reference?1. Prefix with $ manually.2. Right after entering the formula use F4 to toggle between the four kinds of references. (recommended)
Where to use?You can use it in formula or function in Excel spreadsheet, rest depends on your logic.
Video Example:

Bonus Tip: When you move formula (Cut+Paste), reference is preserved irrespective of absolute and relative reference but when formula is copied (Copy+Paste), reference is adjusted of 'Relative Referenced Cells'.

Online MS Excel Training: Quicker Excel

noreply@blogger.com (Ashish Jain) — Sat, 27 Aug 2011 10:31:00 PDT

For Whom?
This MS Excel training is not for beginners, professional or expert users but for all EXCEL users.

What is it?
This will not teach you MS Excel but take you one-level up.

How can I attend it?
1. Please join the presentation on http://www.teamviewer.com/download/version_6x/TeamViewerQJ.exe
2. Use the following data to log in:
ID: 875 681 537Password: 4076

When it is?
Delhi,India: 05:30 PM - 07:30 PM
Shanghai, China: 08:00 PM - 10:00 PM
London, UK: 01:00 PM - 03:00 PM
New York, US: 06:00 AM - 08:00 AM

Cost:
This time, it's free. Not sure, about next time.

Split the Text with Newline Characters (Alt + Enter)

noreply@blogger.com (Ashish Jain) — Sat, 27 Aug 2011 04:35:00 PDT

Splitting the text is a common problem though the kind of split and degree of split may vary problem to problem. There are several ways to solve the split problem depending upon the problem like using formulas, macros or manually. Similarly, would you like to split horizontally or vertically. See a similar situation below with problem (left) and the solution (right). If, you came across such problem usually or find it interesting then go ahead.

What can we do now?

1. Use Formulas : Bit difficult and may not be robust.

2. Data -> Text to Columns : It won't work with new line character, moreover, it split horizontally only.

3. VBA Macros : Yes, absolutely correct.

Click here to download the Split Text Tool or ....

Sub Split_Text()

Dim splitVals As Variant

'Clearing destination to remove old results.

Sheet2.Range("A2:B65536").ClearContents

'Reading Input

For Each mycell In Sheet1.Range("B2:B" & Sheet1.Range("B65536").End(xlUp).Row).Cells

'Chr(10) or vbLf is the newline character.

splitVals = Split(mycell.Value, vbLf)

For i = 0 To UBound(splitVals)

'Writing Output to the Destination

Sheet2.Range("B65536").End(xlUp).Offset(1, 0).Value = splitVals(i)

Sheet2.Range("B65536").End(xlUp).Offset(0, -1).Value = mycell.Offset(0, -1).Value

End Sub

Monthly Calendar Planner Template

noreply@blogger.com (Ashish Jain) — Sat, 25 Jun 2011 13:08:00 PDT

Tonight, I tried my hands on calendars and planners and sure, I come up with an elegant calendar which can be used as planner too but agree many of you won't consider it as a perfect one.

So, I'll refine it more but instead of directly jumping to that, I advise Excel users and my readers to go through this version and see, how it is prepared. There is so much learning in it, you'll surely get benefit from it.

Watch this Calendar Dashboard for:

Named Ranges
Range Linked Picture (Copy Range --> Paste as Picture Link, also known as Camera)
Conditional Formatting
Arrays
Combo Box (Active X Control)

Click here to download the Monthly Calendar Dashboard.

Sort TCP/IP Address or Delimited Numbers

noreply@blogger.com (Ashish Jain) — Thu, 23 Jun 2011 23:48:00 PDT

Are you a reporting analyst in IT firm, network engineer, networking analyst or any one who works on network data? If yes, then you ever tried to sort TCP/IP Addresses, I'm sure something like red list in this figure, would have happened and you must have desired the green list. This is the issue of sorting with delimited numbers like 1.1.2, 1.1.1, 1.1.10 which results in 1.1.1, 1.1.10, 1.1.2.

Generally, computer and languages on which computers operate treat them as string literals and sorting is based on their ASCII character number. So, the solution is to normalize them, sort them and denormalize back in their original form.

This is how, the given VBA macro works. Hope, you understand it easily and leave any questions for clarifications in the comments section.

Sub TCP_IP_Sort()
Dim totalcells As Long, ix As Long
Dim p1 As Byte, p2 As Byte, p3 As Byte, px As Byte
Dim i1 As Byte, i2 As Byte, i3 As Byte, i4 As Byte

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

On Error Resume Next

'Part 01 - Converts 127.0.0.1 to 127.000.000.001 form
totalcells = Selection.count
For ix = 1 To totalcells
actualValue = Selection.Item(ix)
p1 = 0
p2 = 0
p3 = 0
For px = 2 To Len(actualValue)
If Mid(actualValue, px, 1) = "." Then
If p1 = 0 Then
p1 = px
ElseIf p2 = 0 Then p2 = px
ElseIf p3 = 0 Then p3 = px
End If
End If
Next px
Selection.Item(ix).Value = Right("00000" & Mid(actualValue, 1, p1 - 1), 3) _
& "." & Right("00000" & Mid(actualValue, p1 + 1, p2 - p1 - 1), 3) _
& "." & Right("00000" & Mid(actualValue, p2 + 1, p3 - p2 - 1), 3) _
& "." & Right("00000" & Mid(actualValue, p3 + 1), 3)
Next ix

'Part 02 - Sort the normalized form.
Dim xlWbk As Workbook, xlSht As Worksheet, cRng As String
Set xlWbk = ActiveWorkbook
Set xlSht = ActiveSheet
cRng = Selection.Cells.Address(0, 0)

xlSht.Sort.SortFields.Clear
xlSht.Sort.SortFields.Add Key:=Range(cRng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With xlSht.Sort
.SetRange Range(cRng)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'Part 03 - Convert 127.000.000.001 (normalized form) to 127.0.0.1 (actual form)
For ix = 1 To totalcells
actualValue = Selection.Item(ix)
p1 = 0
p2 = 0
p3 = 0
For px = 2 To Len(actualValue)
If Mid(actualValue, px, 1) = "." Then
If p1 = 0 Then
p1 = px
ElseIf p2 = 0 Then p2 = px
ElseIf p3 = 0 Then p3 = px
End If
End If
Next px
i1 = Mid(actualValue, 1, p1 - 1) + 0
i2 = Mid(actualValue, p1 + 1, p2 - p1 - 1) + 0
i3 = Mid(actualValue, p2 + 1, p3 - p2 - 1) + 0
i4 = Mid(actualValue, p3 + 1, Len(actualValue) - p3) + 0
Selection.Item(ix).Value = i1 & "." & i2 & "." & i3 & "." & i4
Next ix

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

On Error GoTo 0
End Sub

Load Array from Sheet Range

noreply@blogger.com (Ashish Jain) — Thu, 02 Jun 2011 02:45:00 PDT

The post starts with a bad news, I had a severe car accident causing 6 fractures in 5 ribs around 10 days before. I express my sincere THANKS to God, Family and my well wishers, everything is fine now... though I'm on bed rest but can't resist myself posting on the blog.

This post is about a VBA trick can be considered as short tip for my fellow readers... If you frequently use arrays and wants to load the values dynamically from a particular range then this VBA macro will be very handy to you.

Sub Load_Array_Dynamically_From_Range()

    Dim arrRng()
    Dim Rng As Range

    x = 0

    For Each Rng In Range("A1:A5")
        ReDim Preserve arrRng(x)
        arrRng(x) = Rng.Value
        x = x + 1
    Next Rng

    For Each Item In arrRng
        Debug.Print Item
    Next

End Sub

Analyzing Date and Time Related Data in Excel

noreply@blogger.com (Ashish Jain) — Thu, 24 Mar 2011 04:58:00 PDT

Date and time based data is a very common feature of many Microsoft Excel spreadsheets. Your time-related information might include project management deadlines, meeting start and end times, admission dates to the ER and so on. Sometimes it gets complicated and confusing when it comes to analyzing this data, however...

In this article I will show you an example of how one can analyze time related data in Microsoft Excel spreadsheets while avoiding resorting to the confusing date and time math!

Working Example
Let's take the ER appointment example to demonstrate how Excel can analyze this data.

We will make a table with a list of daily ER admissions, with the following columns:
- Admission Id (a running number)
- Patient Name
- Problem (fever, head wound, etc)
- Time admitted
- Time released

To analyze this data we would need the following formulas:
- How many patients were admitted.
- How many patients are admitted in the evening versus the morning.

Our first task is to count the number of people admitted with various problems. This is easy enough, we simply use COUNTIF.

=COUNTIF(C$2:C$101,G2)
This counts occurrences in a given range that match the problem name found in column G.

We can then extend this to discover the number of people who were admitted in the AM or PM with the COUNTIFS function:

=COUNTIFS(C$2:C$101,G2,$D$2:$D$101,">=12:00 PM")
So this counts those where the problem matches AND where the admittance time is greater or equal to 12:00 PM. Easy!

Summary
Hopefully this simple example will demonstrate that while time math is tricky and confusing sometimes to do manually, it is very easy and straightforward when you get Excel to do the heavy lifting!

About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter.

Create Excel Dashboard using OFFSET, SUM and MATCH functions

noreply@blogger.com (Ashish Jain) — Sun, 06 Mar 2011 08:42:00 PST

As we learned in our previous article on OFFSET function, when we increase either the row height or column width in the offset function "=OFFSET(A1,2,0,1,1)" to more than 1, the reference is converted to a range from a single cell value. Now this range can be provided to various formulas where it will be used as Dynamic Range.

Now we'll try to understand how to use OFFSET function in conjunction with SUM Function. Before we begin, let's have a look at a dashboard where we're effectively using SUM Function in conjunction with OFFSET and MATCH function.

Unwinding the Nested Formula and Conditional Formatting used in the Sample Dashboard

FORMULA USED:
=SUM(OFFSET(B6,0,0,1,MATCH($B$3,$B$5:$M$5,0)))

1. MATCH Function
Syntax: MATCH(lookup_value,lookup_array,match_type)
Definition: It returns the relative position of an item in an array that matches a specified value in a specified order. It is used instead of the LOOKUP functions when we need the position of an item in a range instead of the item itself. (Source: Excel Help on Match Function)

Lookup_value is the value you use to find the value you want in a table.

Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
If match_type is omitted, it is assumed to be 1.

IN OUR EXAMPLE, we're using MATCH($B$3,$B$5:$M$5,0), which as per the definition of MATCH function returns the index number / position of cell B3 in the array B5:M5. So, if you'll choose APR, it will return 1, in case of AUG, it will return 5 and so on....Please note, we choose 0 for the exact match as well.

2. OFFSET Function
Here we used Type-IV OFFSET Function which takes single cell as input but returns a range of single/multiple cells depending on the return value of MATCH function.
In our example, we're using OFFSET(B6,0,0,1,MATCH($B$3,$B$5:$M$5,0)). So, if we choose B3 as APR, it will be OFFSET(B6,0,0,1,1) which means B6, similarly if we choose B3 as OCT, it will be OFFSET(B6,0,0,1,7) which means B6:H6.
Read the full article on OFFSET Function here.

3. SUM Function
Definition: Adds all the numbers in a range of cells.
Syntax: SUM(number1,number2, ...)
Number1, number2, ... are 1 to 255 arguments for which you want the total value or sum.
Remarks

Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. So, "5" will become 5, TRUE will become 1 and False will become 0.
If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
Arguments that are error values or text that cannot be translated into numbers cause errors. If there is any #NA, #VALUE etc. error is present in the reference range, it will return the same error ignoring every other thing present in the reference range. (Source: MS Excel Help on SUM Function)

In our Sample dashboard, we are using SUM(OFFSET(B6,0,0,1,MATCH($B$3,$B$5:$M$5,0))), so it will return the cumulative sum of the sales of Wine up to the selected month in cell B3.

4. Conditional Formatting
Formula Used: =IF(B$5<=$B$3,1,0)
Note: The game of this conditional formatting lies in what is absolute reference and what is relative reference.
Applied on: =$B$6:$M$11

Click here to DOWNLOAD THE DASHBOARD. Happy Excel Dashboarding :)

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Are you going for an interview ?
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions

or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP OFFSET Custom Format Array Formula

ADD ERROR TRAP: Wrap your Formulas with IFERROR or ISERROR

noreply@blogger.com (Ashish Jain) — Fri, 04 Mar 2011 19:19:00 PST

'To Err is Human' might not be the sole reason behind IFERROR and ISERROR formulas but enough to justify them. So, we all does make errors and so does our logics and programs. Sometimes, we intentionally do it and sometimes, we're left to do it with no other option. So what?

So, if you're human and an Excel user, you'll need these functions almost regularly depending on your excel versions. IFERROR is introduced with MS Excel 2007 and ISERROR was used in Excel 2003 and prior versions.

In this tutorial, you'll find some basic information on IFERROR and ISERROR and some quick VBA macros at the end to wrap all your excel formulas with IFERROR or ISERROR at one go.

IFERROR of Excel 2007 is a drastic user-friendly and performance improvement over ISERROR of Excel 2003 though both have some difference between them.
IFERROR is replacement of common use of ISERROR function in conjunction with IF function.

IFERROR
Syntax: IFERROR(arg1, arg2)
Meaning 01: arg1 is a formula which may return an error or valid value and arg2 is the value the IFERROR function must return if arg1 returns an error.
Meaning 02: If arg1 is error then return arg2 else arg1. See IF(ISERROR(arg1),arg2,arg1) below.

ISERROR
Syntax: ISERROR(arg1)
Meaning: If arg1 is an error then return TRUE else FALSE.

IF(ISERROR(arg1),arg2,arg1)
Meaning: If arg1 is error then return arg2 else arg1.

However, IFERROR(arg1, arg2) is replacement of IF(ISERROR(arg1),arg2,arg1) but ISERROR is inevitable due to:
1. making MS Excel models compatible to Excel 2003 and Excel 2007.
2. the requirement of IF(ISERROR(arg1),arg2,arg3) instead of IF(ISERROR(arg1),arg2,arg1)
Please notice arg3 in the second reason.

Now, how you'll wrap all the existing formulas with IFERROR or IF(ISERROR) formulas at one go? Simple, read the following VBA macros, understand them and then use them as per your requirements. Don't miss the caution at the end.

Excel 2007

Wrap Selected Formulas
Sub Add_IFERROR_Selection()
Dim myCell As Range
   For Each myCell In Selection.Cells
   If myCell.HasFormula And Not myCell.HasArray Then
   myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
   End If
   Next
End Sub

Wrap all formulas in Activesheet
Sub Add_IFERROR_Activesheet()
Dim myCell As Range
   Cells.SpecialCells(xlCellTypeFormulas, 23).Select
   For Each myCell In Selection.Cells
   If myCell.HasFormula And Not myCell.HasArray Then
   myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
   End If
   Next
End Sub

Excel 2003

Wrap Selected Formulas
Sub Add_IFISERROR_Selection()
Dim myCell As Range
Dim cFrm As String
   Cells.SpecialCells(xlCellTypeFormulas, 23).Select
   For Each myCell In Selection.Cells
   If myCell.HasFormula And Not myCell.HasArray Then
   cFrm = Right(myCell.Formula, Len(myCell.Formula) - 1)
   myCell.Formula = "=IF(ISERROR(" & cFrm & "),0," & cFrm & ")"
   End If
   Next
End Sub

Wrap all formulas in Activesheet
Sub Add_IFISERROR_Sheet()
Dim myCell As Range
Dim cFrm As String
   Cells.SpecialCells(xlCellTypeFormulas, 23).Select
   For Each myCell In Selection.Cells
   If myCell.HasFormula And Not myCell.HasArray Then
   cFrm = Right(myCell.Formula, Len(myCell.Formula) - 1)
   myCell.Formula = "=IF(ISERROR(" & cFrm & "),0," & cFrm & ")"
   End If
   Next
End Sub

Caution:
1. Change the red highlighted 0 with the replacement you would like to have instead of 0.
2. These macros do not check if existing formulas are wrapped with iferror or iserror.
3. Make your selection wisely, you may not need to wrap all formulas with Error trap.

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP OFFSET Custom Format Array Formula

How to Prevent Blank Cells from plotting in Excel Charts?

noreply@blogger.com (Ashish Jain) — Mon, 17 Jan 2011 06:43:00 PST

How many times have you created a chart in Excel and seen something like this?

That dip on the graph is because somewhere in your worksheet you have a blank cell. As well as being annoying to you as the chart creator, it is also misleading to anyone who tries to interpret what your chart is trying to tell them!
The obvious solution might have been to delete rows, or to put some values in the blank cells to pad out the data.
However this will end up being even worse... Either omit important information, or visualize incorrect data that wasn't there to begin with.

How can we create a chart that is accurate but takes into account those rogue cells?

Creating a Non-Continuous Line Graph

What we need is a chart like this one below...

Here are the instructions:

Make sure the graph type is Line and not Stacked Line
Select the chart
In the chart menu click on: Design -> Select Data
In the dialog that comes up, click the 'hidden and empty cells' button
Then select 'gaps', and click OK.

There, isn't it easy once you see how it is done?

About the author of this article
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP OFFSET Custom Format Array Formula

How to send Lotus Notes e-mail using Excel VBA ?

noreply@blogger.com (Ashish Jain) — Mon, 17 Jan 2011 05:31:00 PST

There are times when as VBA programmers/Analyst we require to send e-mails after our analysis or dashboard development. This is very trivial that when a dashboard is completed, it is required to send to our seniors or the required recipients. So, there are times, when we've set the VBA program to automate the dashboard development and then we need to send that dashboard by attaching it to mail manually. Did this ever happened that you developed the dashboard and forgot to send to the requested/desired persons? Oops !

Now this article will help us in learning the automation of e-mailing the dashboard (or any file) using Lotus Notes and VBA. This is a referential program and we might need to change the appropriate variables in the program to make it run at our end.

With the introduction of Lotus Notes and Domino Release 5.0.2b, we now have the ability to manipulate the Domino object model via COM. As a result, we can use VBA to take advantage of Lotus/Domino services and databases.

To do so, however, we'll need Lotus Notes client version 5.02.b (or higher), Domino Designer client, or Domino Server. As with most Visual Basic object libraries, the programs need not be running to use them. Lotus has plans to make this runtime package distributable independent of its full software installation. Notice that before we can access any of the objects within the Notes Session, we must initialize a session first.

Anyway, let's directly do the practical which involves two simple steps:

1. Set a reference to "Lotus Notes Automation Classes". In the code window, go to Tool --> References --> Select 'Lotus Notes Automation Classes".

2. Use the following Visual Basic code to send a Notes e-mail message. The code includes examples of code to include an attachment and to save the sent message, which are both optional and can be removed if desired. Don't forgot to replace the underlined phrases with your ones.

Sub Send_Email_via_Lotus_Notes()
   Dim Maildb As Object
   Dim MailDoc As Object
   Dim Body As Object
   Dim Session As Object
   'Start a session of Lotus Notes
   Set Session = CreateObject("Lotus.NotesSession")
   'This line prompts for password of current ID noted in Notes.INI
   Call Session.Initialize
   'or use below to provide password of the current ID (to avoid Password prompt)
   'Call Session.Initialize("<password>")
   'Open the Mail Database of your Lotus Notes
   Set Maildb = Session.GETDATABASE("", "D:\Notes\data\Mail\eXceLiTems.nsf")
   If Not Maildb.IsOpen = True Then Call Maildb.Open
   'Create the Mail Document
   Set MailDoc = Maildb.CREATEDOCUMENT
   Call MailDoc.REPLACEITEMVALUE("Form", "Memo")
   'Set the Recipient of the mail
   Call MailDoc.REPLACEITEMVALUE("SendTo", "Ashish Jain")
   'Set subject of the mail
   Call MailDoc.REPLACEITEMVALUE("Subject", "Subject Text")
   'Create and set the Body content of the mail
   Set Body = MailDoc.CREATERICHTEXTITEM("Body")
   Call Body.APPENDTEXT("Body text here")
   'Example to create an attachment (optional)
   Call Body.ADDNEWLINE(2)
   Call Body.EMBEDOBJECT(1454, "", "C:\dummy.txt", "Attachment")
   'Example to save the message (optional) in Sent items
   MailDoc.SAVEMESSAGEONSEND = True
   'Send the document
   'Gets the mail to appear in the Sent items folder
   Call MailDoc.REPLACEITEMVALUE("PostedDate", Now())
   Call MailDoc.SEND(False)
   'Clean Up the Object variables - Recover memory
   Set Maildb = Nothing
   Set MailDoc = Nothing
   Set Body = Nothing
   Set Session = Nothing
End Sub

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

How to Shrink or Reduce the size of Excel Workbook without losing data?

Learn: VLOOKUP OFFSET Custom Format Array Formula

How to use OFFSET Function? (with Examples)

noreply@blogger.com (Ashish Jain) — Fri, 07 Jan 2011 11:21:00 PST

The OFFSET function is a kind of lookup function in Excel that allows us to find a value in a table based on a particular reference point. This is different from Offset function used in VBA. In VBA, we can only refer a single cell from another cell but when used as Excel formula, it becomes one of the most important to learn. It is used in conjunction with named ranges, charts(to make them dynamic), Sum formula, SUMIF formula, Pivot Tables (to make source range dynamic), VLookup Formula, Array formulas etc.

My next 4-5 posts will concentrate on full use of OFFSET function and I promise you to make expert in all tricks related to OFFSET Function.

You tell the OFFSET function how far to move from the initial reference point (in terms of columns and rows) in order to find the required value. OFFSET function depending on the inputs may return a single cell or a bunch of cells (Range array). We talked a lot about OFFSET function, now let's have a look on it. i.e. How it works? What are its input and output options? etc etc.

Syntax of the OFFSET Function:

OFFSET(initial reference, # of rows(to move), # of cols(to move), height, width)

* initial reference – The cell or group of cells that you would like to use as the initial reference for the OFFSET function. However, you can choose any reference cell you like but generally corner cells are taken in real world examples.

* # of rows – This is a numeric value(strictly integer) that represents the number of rows you want to move to find your OFFSET function value. If you are moving down one or more rows, this value should be positive. If moving up, then the value will be negative.

* # of cols – Exactly, like the rows argument, this is a numeric value that represents the number of columns you want to move to find the function result. If moving to the right, this value should be positive. Negative values represent moving to the left.

* height(optional) – This value is optional and denotes the number of rows you want included in the OFFSET function result. 1 by default, if skipped.

* width(optional) – This value is optional as well and denotes the number of columns you want to include in the OFFSET function result. 1 by default, if skipped.

If any one of the height and width is more than 1, then Offset function must be used in conjunction with named range (to make source data of charts, pivots etc. dynamic) or array formulas because then it will not return a single cell but a bunch of cells.

Some Basic Examples you can use:-
Type - I:
       Initial Reference (Single Cell)
       Return Value (Single Cell)
1. OFFSET(E5,1,3)   - It says go one row down(Row 6) and 3 column right(Column H).
2. OFFSET(E5,1,-3) - It says go one row down(Row 6) and 3 column left(Column B).
3. OFFSET(E5,-1,3) - It says go one row up(Row 4) and 3 column right(Column H).
4. OFFSET(E5,-1,-3) - It says go one row up(Row 4) and 3 column left(Column B).
It will return Error #REF!, if you try to use it like this:-
5. OFFSET(E5,-5,3)   - It says go five row up (Row 0, which don't exists) and 3 column right(Column H).
6. OFFSET(E5,1,-5)   - It says go one row down (Row 6) and 5 column left(before Column A, which don't exist).
So, keep this in mind when using the offset function, we can use it in your logic or take care of not using it.

Type - II:
       Initial Reference (Multiple Cells)
       Return Value (Single Cell)
7. OFFSET(E5:E15,-1,3)   - It says go one row up (Row 4 to Row14) and 3 column right (Column H). It says H4 to H14 but as single cells needs to be return - It will return value of H4. As mentioned before, if height and width omitted, they're taken as 1. So it's equivalent to OFFSET(E5:E15,-1,3,1,1)

Type - III: (Using Offset as Array Formulas / Using Offset for Dynamic ranges by Named Range)
       Initial Reference (Multiple Cells)
       Return Value (Multiple Cells)
Whenever a formula return multiple cells, it becomes mandatory to use array formulas else the formula will return #Value! error. The example given here cannot be used in excel worksheet without array. These examples set base for our coming posts on using OFFSET in conjunction arrays and named ranges. Excel users must understand here what offset function returns with respective inputs rather than trying them in worksheet unlike before.
8. OFFSET(E5:E15,0,2,11,1) will return G5:G15 as it says go nowhere row wise and 2 column right (Column G) then take 11 rows starting G5 and 1 column which means G5:G15.
9. OFFSET(E5:E15,0,-2,11,1) will return C5:C15 as it says go nowhere row wise and 2 column left (Column C) then take 11 rows starting C5 and 1 column which means C5:C15.
10. OFFSET(E5:E15,-2,-2,11,1) will return C3:C13 as it says go 2 rows up and 2 column left (Column C) then take 11 rows starting C3 and 1 column which means C3:C13.
11. OFFSET(E5:E15,-2,-2,5,1) will return C3:C7 as it says go 2 rows up and 2 column left (Column C) then take 5 rows starting C3 and 1 column which means C3:C7.
12. OFFSET(E5:E15,2,3,4,5) will return H7:L10 as it says go 2 rows down(7) and 3 columns right (Column H) then take 4 rows and 5 columns starting H7 which means H7:L10.
Now we'll see same examples, if height and width skipped for them.
13. OFFSET(E5:G15,0,5) will return J5:L15 as it says go nowhere row-wise and 5 columns right (i.e.Column J to Column L) which means J5 to L15. As height and width skipped, they will be calculated from initial reference which says 11 rows as height and 5 columns as width.
14. OFFSET(E5:G15,-2,-2) will return C3:E13 as it says go 2 rows up(So instead of 5 to 15, it will be 3 to 13) and 2 column left (i.e.Column C to Column E) which means C3:E13. As height and width skipped, they will be calculated from initial reference which says 11 rows as height and 3 columns as width.
So, in case of initial reference with multiple cells, height and width (if skipped) don't necessarily be 1, by default.

Type - IV: (Using Offset for Dynamic ranges by Named Range / Using Offset as Array Formulas)
       Initial Reference (Single Cell)
       Return Value (Multiple Cells)
It works same as Type III and no explanation required here except the fact that it becomes mandatory here to give either height or width or both in order to return multiple cells.
15. OFFSET(E5,0,2,11,1) will return G5:G15 as it says go nowhere row wise and 2 column right (Column G) then take 11 rows starting G5 and 1 column which means G5:G15
16. OFFSET(E5,0,-2,11,1) will return C5:C15 as it says go nowhere row wise and 2 column left (Column C) then take 11 rows starting C5 and 1 column which means C5:C15.
17. OFFSET(E5,-2,-2,11,1) will return C3:C13 as it says go 2 rows up and 2 column left (Column C) then take 11 rows starting C3 and 1 column which means C3:C13.
18. OFFSET(E5,-2,-2,5,1) will return C3:C7 as it says go 2 rows up and 2 column left (Column C) then take 5 rows starting C3 and 1 column which means C3:C7.
19. OFFSET(E5,2,3,4,5) will return H7:L10 as it says go 2 rows down(7) and 3 columns right (Column H) then take 4 rows and 5 columns starting H7 which means H7:L10.

With these examples, I finish this post here. Stay tuned to eXceLiTems as we'll move to create dynamic charts and pivots with the help of OFFSET Function in coming posts. We'll use it in array, sum function, subtotal function and much more ....

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP OFFSET Custom Format Array Formula

Using Custom Format for Numbers, Dates and Text

noreply@blogger.com (Ashish Jain) — Tue, 04 Jan 2011 11:30:00 PST

What is 'Custom Format' ?: Microsoft Office Excel provides many built-in number formats, but in some cases they do not meet our needs, we can customize a built-in number format to create our own.

Why 'Custom Format' and not 'Conditional Formatting'?: because to cure 'Common Cold' we go to doctors not surgeons. Exactly, when there is a simple and robust way then why to go otherwise. There are many benefits of using custom format including these:
1. Less overhead than Conditional Formatting.
2. Values can be used easily in formulas (Less manipulation required).
3. Charts looks better with custom colored labels/axis.
4. Works on all versions of Excel.
and many more which you can figure out using your innovative mind after reading this article.

How to apply 'Custom Format'?
Step 1: Open 'Format Cells' dialog box using any of your favorite options:
        i)   Ctrl + 1
        ii)  Right Click --> 'Format Cells'
        iii) Home --> Numbers (Bottom right arrow) - See the pic

Step 2: Now click on 'Custom' and start typing your 'Custom Format' code.

'Custom Format' coding: A number format can be divided in 4 parts by semicolons. Each part is an individual number format. The first applies to positive numbers, second to negative numbers, third to zeros, and the fourth to text (This is by default or we can define this too).
So we can represent the custom format like this.

Positive Numbers ; Negative Numbers ; Zeroes ; TextOf course we can skip whatever we want to as per the requirements and use it like any of this:
Positive Numbers;;Zeroes;Text
Positive Numbers;Negative Numbers;;Text
Positive Numbers;Negative Numbers;Zeroes
etc etc.
Even we don't have to include all semicolons. If we specify only 2 code sections for our custom number format, the first section is used for +ve numbers and zeros, and the second section is used for -ve numbers. If we specify only one code section then it is used for all numbers.
Whenever we skip custom format for a particular type, General format is assumed for the same. This technique comes very handy when we want to hide the values with no data in charts. We can use <any format>;;; to achieve this. For e.g.
General;;;
0%;;;
#.##;;; etc. etc.

Examples:
Let's move with some examples of various data. Essentially, examples makes this subject simple.
I - BASIC EXAMPLES

COMMENTS	TO DISPLAY	AS	USE THIS
Leading Zeros	26	0026	0000
Phone Number	9999404843	999-940-4843	000-000-0000
Day of the Date	26/05/1984	Saturday	dddd
Month of the Date	26/05/1984	May	mmmm
Comma Place holder	23456789	23,456,789	#, ###
Currency	2605.5	€ 2,605.50	€ #,###.00

II - INTERMEDIATE EXAMPLES

COMMENTS	TO DISPLAY	AS	USE THIS
Decimal	2.1	02.10	00.00
Positive (Green)	2605	2,605	[Green]$#,##0;[Red]$#,##0
Negative (Red)	-1984	-1,984	[Green]$#,##0;[Red]$#,##0
Trailing Dots	eXceLitems	eXceLiTems..........	@*.
Scientific Notation	123450	12.345E+04	##.000E+00
		123.450E+03	###.000E+00
		1.235E+05	0.000E+00
Round Off to 1000th	26051984	26052	#,
Align Decimals	26.051	26.051	???.???
	512.65	512.65
	3.6	3.6

III - ADVANCED EXAMPLES

COMMENTS	TO DISPLAY	AS	USE THIS
Prefix Text	12	EMP0012	"EMP" 0000
Suffix Text	125	1,250 lbs.	#,##0 "lbs."
Textual Currency	$5.75	5 dollars and .75 cents	0 "dollars and" .#0" cents"
Positive (Green)	512	512.00	[Green]#,##0.00; [Red](#,##0.00);[Blue]"Zero"
0 as Zero (Blue)	0	Zero
Negative (Red)	-652	(652)
Varying Decimal Digits	.023	.023	[RED][<0.1]0.000; [Blue][<1]0.00;[Green]0.0,
	.23	.23
	2.3	2.3
Elapsed Time (Hours & Minutes)	Formula with Time difference	1:02	[h]:mm
Elapsed Time (Mins. & Secs.)		62:05	[mm]:ss
Elapsed Time (Secs.&Hundredths)		3725.62	[ss].00
Calculate Kilo(10³)	100000	100K	[>=1000]0,"K"
Calculate Mega(10⁶)	5000000000	5000K	[>=1000000]0,,"M";[>=1000]0,"K";0
Align Fractions	6.25	6 1/4	# ???/???
	5.43	5 43/100
	7.75	7 3/4

By now either you have completely understood the Number/Date/Text Formatting using CUSTOM Format or not. In any case I'm sure that you'll be looking for more detailed information. So we will now move on to theory of 'Custom' format.

Before that, I've some exercise questions for you - if you can do them you are genius dude. Try on your own and devote sometime before you google them. This will also help you learn limited theory (which you actually need rather than whole post).

COMMENTS	TO DISPLAY	AS	USE WHAT
Negative Number	-123000	(123K)	?
Date	26/05/1984	Sat, May '84
Currency	6.56	£7
Labels	25	25 feet
Round off to 1000000th	987654321	988

I'm waiting for your responses in comments ;)

So here is some theory for you:

Character	Used as/for
0 (Zero)	This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 2.6, and you want it to be displayed as 2.60, use the format #.00
# (Hash)	This digit placeholder follows the same rules as the 0 (zero). However, Excel does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.3 in the cell, the number 8.3 is displayed and 2.657 is displayed as 2.66
? (Ques. Mark)	This digit placeholder follows the same rules as the 0 (zero). However, Excel adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, see above(Last Intermediate Example)
. (dot/period)	This digit placeholder displays the decimal point in a number.
Color Codes	These are the only approved colour codes. [BLACK] or [Green] or [White] or [Blue] or [Magenta] or [Yellow] or [Cyan] or [Red]
Specify Conditions	To specify number formats that will be applied only if a number meets a condition that you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, see 2nd intermediate or 5th, 6th, 10th and 11th advanced examples.
E (E+/E-)	Exponential Notation/Scientific Notation: Displays a number in scientific (exponential) format. Excel displays a number to the right of the "E" or "e" that corresponds to the number of places that the decimal point was moved. For example see 5th Intermediate example.

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP OFFSET Custom Format Array Formula

17 ways to Optimize VBA Code for FASTER Macros

noreply@blogger.com (Ashish Jain) — Fri, 24 Dec 2010 10:37:00 PST

On the eve before Christmas, I'm writing this article on VBA code optimization for my blog readers as a gift. It contains both the knowledge and good practices which if followed and used effectively then it might put you in elite category of excellent VBA programmers. VBA programs are known to save a lot of time and now this post is to save further time. Read them carefully and open new pathways of innovation in creating excel dashboards, reports and automation. Wish my readers a very MerRy ChRisTMaS !

Here is a summary of the article:
1. Analyze the Logic                                          2. Turn off ScreenUpdating
3. Turn off 'Automatic Calculations'                   4. Disable Events
5. Hide Page breaks                                           6. Use 'WITH' statement
7. Use vbNullString instead of ""                         8. Release memory of Object variables
9. Reduce the number of lines using colon(:)     10. Prefer constants
11. Avoid Unnecessary Copy and Paste               12. Clear the Clipboard after Paste
13. Avoid 'Macro Recorder' style code.               14. Use 'For Each' than 'Indexed For'
15. Use 'Early Binding' rather 'Late Binding'         16. Avoid using Variant
17. Use Worksheet Functions wherever applicable

Do read full article to understand the logic behind them.

Analyze the logic: Before optimizing the syntax, pay more attention in optimizing the logic. Without a good logic, a good written VBA macro program has no value. So streamline your program logic and get the best performance of macros.

Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.

The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes, and message boxes. You can increase the speed of some procedures by keeping screen updating turned off. You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error. Refer MSDN for details.

Turn off automatic calculations: Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated. You may turn off the automatic calculation using
Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation
Now, whenever due to the program logic(that due to macros dependent on existing formulas) you need to calculate the formulas, you may use the following code accordingly.
ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet
Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current application.

Stop Events: Use Application.EnableEvents to tell VBA processor whether to fire events or not. We rarely fire an event for each cell we're changing via code. Hence, turning off events will speed up our VBA code performance.

Hide Page Breaks:When we run a Microsoft VBA macro in a later version of Microsoft Excel, the macro may take longer to complete than it does in earlier versions of Excel. For example, a macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel. This problem may occur if the following conditions are true:

* The VBA macro modifies the properties of many rows or columns.
* An operation has been performed that forced Excel to calculate page breaks. Excel calculates page breaks when we perform any of the following operations:
o We display a print preview of your worksheet.
o In Microsoft Office Excel 2003 and in earlier versions of Excel, we click Page Setup on the File menu.
o We modify any of the properties of the PageSetup object in a VBA macro.
* In Excel 2003 and in earlier versions of Excel, we selected the Page breaks check box on the View tab of the Options dialog box.

Solution: is to disable Page breaks using ActiveSheet.DisplayPageBreaks = False
Refer to this Microsoft article for more details - http://support.microsoft.com/kb/199505

Use 'WITH' statement when working with objects: If we have to access an object's properties and methods in several lines, we must avoid using object's name or fully qualified object path again and again. It is annoying for VBA processor as it needs to fully qualify the object each time. (Isn't it annoying for us too when some work or something is told us again and again? Got it Guys !

SLOW MACRO	FAST MACRO
Sheets(1).Range("A1:E1").Font.Italic = True Sheets(1).Range("A1:E1").Font.Interior.Color = vbRed Sheets(1).Range("A1:E1").MergeCells = True	With Sheets(1).Range("A1:E1") .Font.Italic = True .Font.Interior.Color = vbRed .MergeCells = True End With

The point here to understand is minimum qualifying of an object by VBA processor. i.e. using minimum dots/periods(.) in the code. This concept tells us to use [A1] rather than Range("A1") and Range("StockRange")(3,4) rather than Range("StockRange").Cells(3,4)

Use vbNullString instead of ""(2 double quotes) : vbNullString is slightly faster than "", since vbNullString is not actually a string, but a constant set to 0 bytes, whereas "" is a string consuming at least 4-6 bytes for just existence.
For example: Instead of strVariable = "", use strVariable = vbNullString.

Release memory from object variables: Whenever we create an object in VBA, we actually create two things -- an object, and a pointer (called an object reference). We might say, "VB does not use pointers", but it is not true. "VB does not let you manipulate pointers" is more precise. Behind the scenes, VB still makes extensive use of pointers. To destroy an object in VB, you set it to Nothing. But wait a minute. If all we ever use are object pointers, how can we set the object itself to Nothing? The answer is: We can't.
When we set a reference to Nothing, something called the garbage collector kicks in. This little piece of software tries to determine whether or not an object should be destroyed. There are many ways to implement a garbage collector, but Visual Basic uses what is called the reference count method.
When VB interprets the last line(where we generally sets our objects to Nothing), it will remove the existing reference. At this point, if the object has no more references, the garbage collector will destroy the object and deallocate all its resources. If any other references point to the same object, the object will not be destroyed.

Reduce the number of Lines: Avoid multiple statements especially when they can be clubbed into one line. For example - See these 2 macros

SLOW MACRO

    With Selection
        .WrapText = True
        .ShrinkToFit = False

End With

FAST MACRO

With Selection
.WrapText = True: .ShrinkToFit = False

End With

As you can see, you can club multiple statements into one using colon character(:). When you do this with multiple statements, it will decrease the readability but will increase the speed.
Compiler Logic: When we save the macro, it is virtually compiled and unlike it's human readable form as we saw in VBE(Visual Basic Editor), keywords(the dark blue words which you cannot use as variable) are saved as three-byte token which are faster to process as machine understand them better and variables, comments and literal strings which are not either keyword or directive are saved "as is". However VBA compiler tokenizes the word but it does not compress the lines and each line is maintained as is ending with the 'Carriage Return'. When the VBA macro is executed, VBA processor fetched single line at a time. The tokens of the fetched line saved by virtual compiler are now interpreted and executed then next line is fetched and so on. When we combine multiple lines using colon into one line, we're reducing the number of fetch cycles the processor must go through.
Our Suggestion: This change will bring minor difference in time due to faster processors today. Moreover, you cannot have more than 255 characters in a line and you won't be able to debug your code using F8 efficiently. So it's a kind of useless, there is no reason to trade-off with readability with such a minor change in time.

Declare variable as Variable and constant as Constant: Seems, obvious ! But many of us don't follow it. Like
      Dim Pi As Double
      Pi = 3.14159
instead use
      Const Pi As Double
      Pi = 3.14159
Since, its value is never changed so it will be evaluated once during compilation unlike variable which are evaluated many times during the run-time.

Avoid Unnecessary Copy and Paste: Follow this table rules:

Instead of	Use this:
Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").PasteSpecial Application.CutCopyMode = False 'Clear Clipboard	'Bypass the Clipboard Sheet1.Range("A1:A200").Copy Destination:= Sheet2.Range("B1")
Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").PasteSpecial xlPasteValues Application.CutCopyMode=False 'Clear Clipboard	'Bypass the Clipboard if only values are required Sheet2.Range("B1:B200").Value = Sheet1.Range("A1:A200").Value
Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").PasteSpecial xlPasteFormulas Application.CutCopyMode=False 'Clear Clipboard	'Bypass the Clipboard if only formulas are required Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula 'Same can be done with FormulaR1C1 and Array Formulas.

Use Worksheet Functions rather developing own logic: By using Application.WorkSheetFunction, we tell VBA processor to use native code rather than interpreted code as VBA understands the worksheet functions better than your algorithm. So, for example use
      mProduct = Application.WorkSheetFunction.Product(Range("C5:C10"))
rather than defining your own logic like this:
      mProduct = 1
      For i = 5 to 10
            mProduct = mProduct * Cells(3,i)

Use 'For Each' rather than 'indexed For': We can avoid using Indexed For when looping through collections. For example, take the code just before this tip. It can be modified to:
For Each myCell in Range("C5:C10")
mProduct = mProduct * myCell.Value

This is in relation to qualifying object again and again as using "WITH" statements.

Avoid using 'Macro Recorder' style code:Ya, the code will look genius and eventually perform like Genius too ! You'll better catch it with example, so use:
      [A1].Interior.Color = vbRed
rather than
      Range("A1").Select
      Selection.Interior.Color = vbRed
Using too many Select and Selection effects the performance drastically. Ask yourself why to go in Cell and then change the properties? or rather ask why to go pizza shop when you can enjoy it at your home ;)

Avoid using Variant and Object in declaration statements: Think about better logic and get rid of them. i.e. do not use Dim i As Variant or Dim mCell As Object. By trying to be specific,we will save a lot of system memory this way, particularly in case of large projects. We may not remember which has been declared variant above and misuse a variable assigning any value to it which will be type-casted without errors. A variant's descriptor is 16 bytes long while double is 8 bytes, long is 4 bytes and integer is 2 bytes. Hence use Variant cautiously. As an example, use:
      Dim i As Long rather than Dim i As Variant
Similarly use:
      Dim mCell As Range 'or
      Dim mSheet As Worksheet
rather than
      Dim mCell As Object 'or
      Dim mSheet As Object

Declare OLE objects directly: Declaring and defining the OLE objects in declaration statement is called 'Early Binding' but declaring an object and defining it later is called 'Late Binding'. Always prefer 'Early Binding' over 'Late Binding'. Now for example use:
      Dim oXL As Excel.Application
rather than
      Dim oXL As Object
      Set oXL = CreateObject("Excel.Application")

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP OFFSET Custom Format Array Formula

Excel 2003 Style Menu in Excel 2007/2010

noreply@blogger.com (Ashish Jain) — Mon, 20 Dec 2010 02:27:00 PST

I really don't want to write this article as I'm in love with new interface of Excel 2007 and wants my blog readers and every excel user to understand the new Ribbon Menu structure than to go back and use Excel 2003 style menu in Excel 2007. However I don't want to write but I'm not writing this article on a gun-point. Lolzzzzzzzz... The objective is to help those users who want to remain stick with legacy programs just because they don't understand new and improved systems. So, that they can move to new systems, explore it, gradually learn it and hence praise it. Second reason is that such utilities are available @ cost of $20-$50 over the internet which my dear readers and excel users do not need to pay anyway.

Excel 2003 Style Menu in Excel 2007

In my total experience with Excel and Excel users, I encountered many peoples (major of them Senior Managers and Managers) who resist Excel 2007 primarily due to its new ribbon structure. They were habitual to 2003 and before menu style and found it hard to locate their favorite commands. Anyway, let's come to technical part now.

Excel 2003 Menu

Excel 2007 introduced ribbon structure which is carried forward in Excel 2010 too and hence will continue to be part of Excel in future. This ribbon can be modified by any developer with the knowledge of XML and Ribbon key elements. However, Microsoft still kept the old command bars and command buttons, somewhere inside Excel 2007 and 2010 too.

Excel 2007 Ribbon Menu Structure

If you want to see the old command buttons and want to use them in Excel 2007/2010. Click on MS Office Button(2007) or File(2010) and go to Excel Options --> Customize --> 'Choose commands from' --> All Commands. Either find and select the options you want or run the macro below which will create the exact 2003 style menu in Add-ins tab of Excel 2007.

Sub Show_Excel_2003_Style_Menu()
   On Error Resume Next
   Dim cmdBar As CommandBar
   Dim cmdBarCtrl As CommandBarControl
   Dim sMenuName As String
   Dim sToolbarName As String
   Dim iMenu As Integer

   sMenuName = "Excel 2003 Style Menu"
   sToolbarName = "Excel 2003 Style Toolbar"
   CommandBars(sMenuName).Delete
   CommandBars(sToolbarName).Delete
   'Code upto here can be used to hide/delete Excel 2003
   'style menu from Excel 2007/2010.

   Set cmdBar = CommandBars.Add(sMenuName, , , True)

   'This will create Excel 2003 style menu bar in Excel 2007/2010
   With cmdBar
   .Visible = True
   For iMenu = 1 To 10
   Set cmdBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30001 + iMenu)
   Next iMenu
   Set cmdBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30022) 'Chart
   Set cmdBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30177) 'AutoShapes
   End With

   Set cmdBar = CommandBars.Add(sToolbarName, , , True)
   With cmdBar
   .Visible = True
   With .Controls
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=2520) 'New
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=23) 'Open
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=3) 'Save
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=4) 'Print
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=109) 'Print Preview
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=2) 'Spelling
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=21) 'Cut
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=19) 'Copy
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=22) 'Paste
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=108) 'Format Painter
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=210) 'Sort Ascending
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=211) 'Sort Descending
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=984) 'Help
   Set cmdBarCtrl = .Add(Type:=msoControlComboBox, ID:=1728) 'Font
   Set cmdBarCtrl = .Add(Type:=msoControlComboBox, ID:=1731) 'Font Size
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=113) 'Bold
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=114) 'Italic
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=115) 'Underline
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=120) 'Align Left
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=122) 'Center
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=121) 'Align Right
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=402) 'Merge and Center
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=395) 'Accounting Number Format
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=396) 'Percent Style
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=397) 'Comma Style
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=398) 'Increase Decimal
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=399) 'Decrease Decimal
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=3162) 'Decrease Indent
   Set cmdBarCtrl = .Add(Type:=msoControlButton, ID:=3161) 'Increase Indent
   End With
   End With
   Set cmdBar = Nothing
   Set cmdBarCtrl = Nothing
   On Error GoTo 0
End Sub

If you don't know or understand the VBA macros, download the add-in here.

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP OFFSET Custom Format Array Formula

Show Grid on Colored Range (Apply Border)

noreply@blogger.com (Ashish Jain) — Thu, 09 Dec 2010 01:35:00 PST

Whenever we apply background color to a range in Excel, we lost our gridlines. This macro, which is just a trick by applying colored border to the range will make it different i.e. Colored Range with Grid Lines. For eg. see the image below:

Sub Show_Grid_on_Color_Range()
   With Selection
      Selection.BorderAround xlContinuous, xlThin, , RGB(192, 192, 192)
      If .Columns.Count> 1 Then
         With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = RGB(192, 192, 192)
         End With
      End If
      If .Rows.Count> 1 Then
         With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = RGB(192, 192, 192)
         End With
      End If
   End With
End Sub

Replace Characters in the Filenames of a Directory

noreply@blogger.com (Ashish Jain) — Wed, 08 Dec 2010 03:43:00 PST

Friends, this is such a simple problem and hence the solution that I don't need to provide long texts explaining that what it does. Simply, see the figure below and you'll understand quite well that what would the macro below will do or how you can enhance it your way. In a single line, I would still say IT REPLACES CHARACTERS/STRINGS in all filenames present in the provided directory. Just a time saver and good to understand usage of DIR function.

Function Replace_Filename_Character(ByVal Path As String, _
            ByVal OldChr As String, ByVal NewChr As String)

    Dim FileName As String
    'Input Validation
    'Trailing backslash (\) is a must
    If Right(Path, 1) <> "\" Then Path = Path & "\"
    
    'Directory must exist and should not be empty.
    If Len(Dir(Path)) = 0 Then
        Replace_Filename_Character = "No files found."
        Exit Function
    'Old character and New character must not be empty or null strings.
    ElseIf Trim(OldChr) = "" And OldChr <> " " Then
        Replace_Filename_Character = "Invalid Old Character."
        Exit Function
    ElseIf Trim(NewChr) = "" And NewChr <> " " Then
        Replace_Filename_Character = "Invalid New Character."
        Exit Function
    End If
    
    FileName = Dir(Path & "*.*") 'Use *.xl* for Excel and *.doc for Word files
    Do While FileName <> ""    
        Name Path & FileName As Path & Replace(FileName, OldChr, NewChr)
        FileName = Dir
    Loop
    Replace_Filename_Character = "Ok"
End Function


'Subroutine to explain the above function's usage as per the above figure.

Sub Function_Sample_Usage()
    Dim lResult As String
    
    lResult = Replace_Filename_Character("D:\test", " ", "_")
    Debug.Print lResult 'Returns Ok
    
    lResult = Replace_Filename_Character("D:\test", " ", "_")
    Debug.Print lResult 'Returns Ok though no spaces in filenames now
    
    lResult = Replace_Filename_Character("D:\test\", "", "_")
    Debug.Print lResult 'Returns 'Invalid Old Character'
    
    lResult = Replace_Filename_Character("D:\test\Test01", " ", "_")
    Debug.Print lResult 'Returns 'No files found' as invalid directory provided.
End Sub

Square Cells or Create Graph Cells with VBA

noreply@blogger.com (Ashish Jain) — Mon, 06 Dec 2010 03:22:00 PST

The grid cells in Excel appears to be rectangular by default. In case you would like to convert them into squares as we have graph cells(See image below) then you need to write a macro for that. Column default width is 8.43 points spanning 64 pixels and Row height is 15 points spanning 20 pixels.

The height and width may appear weird to you as Row height is less than Column width but value is almost double. Let's get clear on this first.

Fact 01: One unit of column width is equal to the width of one character in the Normal style.
Fact 02: For proportional fonts, the width of the character 0 (zero) is used.
Fact 03: If all columns in the range have the same width, the ColumnWidth property returns the width.
Fact 04: If columns in the range have different widths, this property returns Null.

Solution: To achieve our results, we need to proportionate pixels on the basis of points. Simply, we can divide ColumnWidth(pixels) by Width of Columns(points) and multiplying the result with Height of Row(points) and this will be equal to new ColumnWidth(Pixels). Programmatically, we can sum up all of this as follows:

Sub Create_Graph_Cells()
'aka Square the Cells or Convert sheet to Grid

'This macro will convert ActiveSheet/Selected Columns to Graph cells

'based on First Cell dimensions.
    Dim i As Integer
    Dim rng As Range
    If MsgBox("Would you like to Square Cells of Whole Worksheet?", _
              vbYesNo, "Selected Columns or Whole Worksheet?") = vbYes Then
        Set rng = ActiveSheet.Cells
    Else
        Set rng = Selection
    End If
    For i = 1 To 4
        With rng
            .Columns.ColumnWidth = _
            .Columns("A").ColumnWidth / .Columns("A").Width * _
            .Rows(1).Height
        End With
    Next
End Sub

3 Things to remember before you go for Excel VBA Job Interview

noreply@blogger.com (Ashish Jain) — Sun, 05 Dec 2010 10:29:00 PST

Like giving interview is a skill, taking interview is another great skill. I've taken many in the past and given more than I've taken and here I'm sharing the content from my experience. Your interview experience may be totally different than this but you must be aware of it and should not miss some common things mentioned here. These are my personal views and have nothing to do any of my past or present employers.

If I will conduct an interview, I'll not probe your technical knowledge only but also how much logical and reasoning thinking you possess along with some programming etiquette.

First interviewer sees your general attitude towards programming and will try to calm down your blood pressure. While it seems very easy this is the biggest hurdle. He may give you a very easy program to write like sorting an array, coloring the sheet tabs, etc. The objective is not to see your technical knowledge but your programming behavior. Such programs are so obvious and easy that expectations are not about writing the program but to see comments, indentation, using meaningful variable names, declaring variables(which one don't needs to but considered a great practice), using Option Explicit, Debugging style etc. To know that good you're able to communicate your logic with other developers. This is very helpful for the team and is considered a good practice with fruits returned in long term.

Secondly, he will try to test your knowledge of Excel Object Model. They may ask you to sort and filter a range, or clean out duplicate data from a list, consolidate data from multiple files, import data from MS access databases etc . He will look for knowledge of and ability to work with fundamental Excel objects such as Workbook, Worksheet, Range etc; also he will definitely want to see that you neither use "Macro Recorder" nor macro recorder style code using Select and Activate everywhere.

Thirdly he would be interested in your excel knowledge as well as how VBA works. Like pivots, charts, UDF, volatile functions(Can you write code that runs every time the worksheet changes?”) etc. For list of 50 such ORAL Interview VBA questions, click here.

Lastly, the questions would be job specific that if its a consulting firm questions will revolve around statistical models, SAS etc, in case of bank - financial dashboards,KPI etc. and in case of capacity management or databases they'll revolve around ADO, Basic SQL etc.

Good interviewers put “Knowledge of Excel” above “Knowledge of Excel VBA” as in their opinion, the latter is more straightforward and easier to learn but it’s harder to find people who are good at writing efficient Excel formulas(esp. Array Formulas) as often formulas can yield a far more efficient solution – faster by several orders of magnitude.

Excel VBA is a very vast field but interview questions revolve around common things. Interviewer may ask some stupid or Out-of-the-box question to worry you a little, may give you too less time to write a macro or do anything to worry about. YOU DON'T NEED TO WORRY, it's ok if you don't know answers to some questions. Maintain NEVER GIVE UP attitude and YOU'LL SUCCEED.

WISH YOU ALL THE BEST !

JOBS for Analysts (INDIA) - Excel, Access, VBA, SQL MIS JOBS

noreply@blogger.com (Ashish Jain) — Sat, 04 Dec 2010 15:29:00 PST

Company Name:	AMWAY
Role:	Sales Plan Administration Reports to Associate Manager. You have to analyse data which comes from distributor ,marketing and sales ,promotion for their future plans. Ensure timely compliance of all awards & rewards applications. Implement and support long term company strategies of growth through plan specifics.
Location:	Noida, UP, India
Eligibility:	Bachelor's degree with 2-4 yrs. of experience. MS Excel, Access and VBA
Contact/Source:	Sharamistha (Velocity Consultants) - resume@hkbgroup.com - +91 - 11 - 46472223

Company Name:	Irevna - A Division of Crisil
Role:	VBA Developer Building complex reports with multiple data points. It will involve building databases in access and generating reports using SQL queries and VBA codes. It will also involve building macros in excel. Maintaining and Enhancing existing Databases and templates. Job involves time critical delivery and crunch time support
Location:	Chennai, India
Eligibility:	Bachelor's degree with 2-4 yrs. of experience. MS Excel and VBA. SQL and Finance industry experience preferred.
Contact/Source:	Ashwath Mahadev Subramanian, HR Irevna (044-42263245) - ashwathms@irevna.com

3. Deadline: Dec. 13, 2010 (Interview on Dec.14, 2010)

Company Name:	Genpact
Role:	Reporting Digitization 1) Develop advanced MS Excel / MS Access based models. 2) Enhance and Maintain existing models within the process. 3) Developing customized reports from the model databases. 4) Develop process / software documents for existing and new models. 5) Digitize reports using VBA and macros.
Location:	Gurgaon, Haryana, India
Eligibility:	Excel/VBA/Access - Experience reqd. but not mentioned.
Contact/Source:	Indrani (Ikya HCS Consultants) - indrani@ikyaglobal.com - 080-6713 1051

Company Name:	Hewitt Associates
Role:	VBA Developer Knowledge of XML and MS Excel and working exp. in XML and Excel is a plus. Knowledge of internet protocols is a plus. Working experience in a web application is a plus. Working experience in VB Macros and event handling is a plus. Knowledge and/or working experience in MSP (Microsoft Project) is a plus.
Location:	Gurgaon, Haryana, Delhi
Eligibility:	0.6 - 3 Years Experience. Excel and VBA is a must.
Contact/Source:	Mamta (Jobline Consultants) - 7503101684 - manisha.prasad@hewitt.com, mamta@jobconnect.co.in,

Company Name:	Annik Technologies ( www.anniksystems.com)
Role:	VBA Programmer (Contract-to-Hire basis for 12 months.) 1.Having Strong Knowledge (Expertise) of VBA Programming. 2.Expertise in following technologies: MS Word, Excel, Power Point and MS Access. 3.Proficient in VB programming. 4.Proficient in Excel and Power Point charts preparation using VBA programming.
Location:	Gurgaon, Haryana, Delhi
Eligibility:	Open to work on Shift Basis. Exp: 3-4 Years,
Contact/Source:	Archie Gautam (Kelly Services) - 9711987805

50 Excel VBA Oral Interview Questions

noreply@blogger.com (Ashish Jain) — Sat, 04 Dec 2010 11:32:00 PST

These Excel VBA Interview questions are being posted keeping in mind that reader is aware of working with VBA, have some programming and MS Excel background and is aware of terminologies. This question bank is helpful for both Interviewee and Interviewer as it provides a quick channel of questions and answers covering major topics of Excel and VBA.

If you're looking for a job in MIS/Automation/Dashboard creation etc. as a Business Analyst, Senior Analyst, Associate Analyst, etc involving MS Excel, MS Access, VBA, SQL, Cognos, ASP.NET etc then Click here on 'Excel VBA Job Postings'

Ques 01. What is the difference between ByVal and ByRef and which is default ?

Solution: ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure.
ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained.
ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference.

Ques 02. What is the meaning of Option Explicit and Option Base?

Solution: Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0.

Ques 03. What are various data type and their size?

Solution:
i) The Boolean data type has only two states, True and False. These types of variables are stored as 16-bit (2 Byte) numbers, and are usually used for flags.
ii) The Byte data type is an 8-bit variable which can store value from 0 to 255.
iii) The Double data type is a 64-bit floating point number used when high accuracy is needed.
iv) The Integer data type is a 16-bit number which can range from -32768 to 32767. Integers should be used when you are working with values that can not contain fractional numbers. In case, you're working over 32767 rows use Long as data type.
v) The Long data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647.
vi) The Single data type is a 32-bit number ranging from -3.402823e38 to -1.401298e-45 for negative values and from 1.401298e-45 to 3.402823e38 for positive values. When you need fractional numbers within this range, this is the data type to use.
vii) The String data type is usually used as a variable-length type of variable. A variable-length string can contain up to approximately 2 billion characters. Each character has a value ranging from 0 to 255 based on the ASCII character set.

Ques 04. Difference between ActiveWorkbook and ThisWorkbook.

Solution: ThisWorkbook refers to the workbook where code is being written while ActiveWorkbook refers to the workbook which is in active state with active window. In case of only one workbook open, ActiveWorkbook is same as ThisWorkbook.

Ques 05. Code to find a Last used Row in a column or Last used column of a Row.

Solution: Last Row in a column can be find using End(xlUp) and Last Column in a row can be find using End(xlToLeft). For e.g. Range("A1048576").End(xlUp).Row gives last used row of Column A.

Ques 06. Difference between ActiveX and Form Controls.

Solution:
i) Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel.
ii) The Forms controls aren’t very complicated, and they have been part of Excel for longer (they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet.

Ques 07. What is the difference b/w Functions and Subroutines?

Solution:
i) Subroutines never return a value but functions does return values.
ii) A function could not change the values of actual arguments whereas a subroutine could change them.

Ques 08. How to debug a VBA code?

Solution: Using Breakpoints(F9), Step-by-step execution (F8), Debug.Print & Immediate Window and Watch window.

Ques 09. Draw basic Excel Object Model.

Solution: Application --> Workbooks --> Worksheets --> Range / Chart

Ques 10. What are properties, methods, events and objects?

Solution: For details click here --> http://msdn.microsoft.com/en-us/library/ms172576%28VS.80%29.aspx
All the controls in the ToolBox except the Pointer are objects in Visual Basic. These objects have associated properties, methods and events.
A property is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. or sometimes the way in which it behaves.
A method is an action that can be performed on objects. For example, a cat is an object. Its properties might include long white hair, blue eyes, 3 pounds weight etc. A complete definition of cat must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a cat's methods might be move, jump, play, breath etc.
Visual Basic programs are built around events. Events are various things that can happen in a program. Let us consider a TextBox control and a few of its associated events to understand the concept of event driven programming. The TextBox control supports various events such as Change, Click, MouseMove and many more that will be listed in the Properties dropdown list in the code window for the TextBox control. We will look into a few of them as given below.
* The code entered in the Change event fires when there is a change in the contents of the TextBox
* The Click event fires when the TextBox control is clicked.
* The MouseMove event fires when the mouse is moved over the TextBox

Ques 11. How to hide a worksheet so that a user cannot unhide it?

Solution: Use Sheet's visible property and set it to xlSheetVeryHidden . For eg. Sheets(1).Visible = xlSheetVeryHidden will super hide the first worksheet of the workbook.

Ques 12. Union is used for _____________ ?

Solution: To unite the different ranges depending on the logic. It is similar to set union, here range works as set. For eg. Set nrange = Union(rng1,rng2)

Ques 13. Which are the 2 macro languages and which do you use ?

Solution: XLM (used in Excel 97 or before) and VBA(used for 2000 and after). Obviously, VBA is in use these days.

Ques 14. Can you lock cells such that only specific users can modify them?

Solution: There is a option "Allow users to edit ranges" can be used for this purpose.

Ques 15. How can you add a drop-down list to a cell so the user can choose a value from the list?

Solution: Using 'Data Validation'.

Ques 16. How can you increase the number of rows in a worksheet?

Solution: No one can't. They're fixed as 65536(2^16) in Excel 2003 or before and 1048576(2^20) in Excel 2007 & Excel 2010.

Ques 17. How can you increase the number of columns in a worksheet?

Solution: No one can't. They're fixed as 256(2^8) in Excel 2003 or before and 16384(2^14) in Excel 2007 & Excel 2010.

Ques. 18. How will you distribute a workbook such that it can't be copied using macro or anything?

Solution: We can create a workbook which cannot be modified but can not create a workbook which can't be copied.(It depends on system security, it has nothing to do with Excel or VBA)

Ques 19. Your colleague created a dashboard and when you enter a value, it appears with two decimal places. For example, when you enter 265 it shows up as 2.65. What's wrong?

Solution: By chance Excel's fixed-decimal mode was turned on. To return to normal,
Excel 2003 -->
Click Tools and then Options to display the Options dialog box.
Then click the Edit tab and remove the check mark from the "Fixed decimal " option.
Excel 2007 -->
Click Office button on Top-Left corner and click 'Excel Options'.
Go to Advanced and Uncheck 'Automatically insert a decimal point' option.
Excel 2010 -->
Click File button on Top-Left corner and click 'Excel Options'.
Go to Advanced and Uncheck 'Automatically insert a decimal point' option.

Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to keep the fixed-decimal mode turned off.

Ques 20. How can you prevent a user for adding or deleting sheets?

Solution: You need to protect the workbook's structure.
Excel 2003 -->
Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked.
Excel 2007/2010 -->
Go to Review --> Click 'Protect Workbook' --> Click 'Protect Structure and Windows'

If you specify a password, that password will be required to unprotect the workbook. When a workbook's structure is protected, the user may not:
         * Add/Delete a sheet
         * Hide/Unhide a sheet
         * Rename a sheet
         * Move a sheet

Ques 21. What types of workbook protection are available?

Solution: Excel provides three ways to protect a workbook:
         * Require a password to open the workbook
         * Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
         * Prevent users from changing the size or position of windows

Ques 22. The Font dialog box allows you to select different Fonts, their style, their size, and some other special effects. How do you bring up this Font dialog box?

Solution: Use Application.Dialogs(xlDialogFont).Show or Application.Dialogs(xlDialogFormatFont).Show to load font dialog box from Excel VBA.

Ques 23. What is ADO, OLEDB & ODBC?

Solution:
ADO : ActiveX Data Objects is universal data access framework that encompasses the functionality of DAO.
ODBC : Open Database Connectivity(ODBC) is a windows technology that lets a database client application connect to a external database.
OLEDB : Low level programming interface designed to access a wide variety of data access Object Linking and Embedding (OLE).

Ques 24. How to set the custom paper size in Excel Object through VB?

Solution: Activesheet.PageSetup.PaperSize = xlPaperLetter (Similarly xlPaperA4 or xlPaperLegal etc.)

Ques 25. What is the method for returning more than one values from a function in VB?

Solution: Any of the three methods can be used:
i) Create a class with the properties you require to return and then return the object of the class from the function.
ii) Using ByRef for the values.
iii) Return an array of the values.

Ques 26. Does VBA supports OOP principles?

Solution: Yes because VBA is VB6.0 based which is an Object Based Programming Language and is also known as 'Event Driven Programming' and it supports Polymorphism, Encapsulation and partially Inheritance.

Ques 27. To set the command button for ESC, Which property needs to be changed?

Solution: Set Cancel property of Button to True on the Form.

Ques 28. What is Type Library and its purpose?

Solution: Type libraries are files that explicitly describe some or all of the contents of components. This includes information about the methods properties constants and other members exposed by the component. Development tools such as Visual Basic make use of the information contained in the type library to help you as a developer access and use the component. In addition type libraries provide a convenient way to include a simple level of descriptive documentation for component members. You can use them through 'Tools --> References' in VBE.

Ques 29. How do you use the Getsetting and Savesetting functions to read and write registry settings?

Solution: A computer registry can be used to store configuration settings and application initialization. We can use Getsetting function to read registry settings and save settings function to write registry settings. Application name, section, key, setting, and default are to be specified for registry modifying. It is advisable to know about your computer settings before modifying registry settings.

Ques 30. What is a Variant, what the pros and cons of its use?

Solution: Variant data type is able to hold any other data type, including numbers, strings, dates, and object references. A Variant's descriptor is only 16 bytes long (4 short words for the type, and 2 long words for the data, or data pointer).
Pros: You cannot use Null with any variable type other than Variant.
You don't need to worry about what you have declared a variable as.
When a Variant has been declared but not assigned a value, it contains the special value Empty.
Cons: A developer may not remember and misuse a variable assigning any value to it which will be type-casted without errors.

Ques 31. Give technical reasons which made Microsoft withdraw its support for VBA in Mac?

Solution: The reasons which made Microsoft drop its support to VBA are as follows, Microsoft visual basic relies heavily on machine code which was written for Power PC architecture. Also it would take another two years for developing VBA support for its architecture. It also states that Microsoft will incorporate VBA in the next script of office release for Mac.

Ques 32. What is a volatile function?

Solution: Volatile functions are a type of function that will always recalculate. That means whenever Excel needs to calculate any part of the worksheet, those cells containing volatile functions will also calculate.

Ques 33. Give some examples of Volatile function.

Solution:
Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()
Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()
Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.
A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:
INDEX(), ROWS(), COLUMNS(), AREAS()
and CELL("Filename") IS volatile although a MSKBN article says its not.
One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions:
=SUMIF(A1:A4,">0",B1) is volatile whereas =SUMIF(A1:A4,">0",B1:B4) is not volatile.

Ques 34. How do you make a UDF volatile?

Solution: By adding Application.Volatile statement to it. It must be the first line of your User Defined Function.

Ques 35. Is it possible to apply 'Application.Volatile(False)' to a volatile public function like INDEX and make it not volatile?

Solution: Actually INDEX is not a volatile function, even though some MicroSoft documentation says it is. Anyway no its not possible to apply Application.Volatile(False) to a built-in Excel function except by duplicating what the built-in function does inside a UDF.

Ques 36. What is Excel dependency tree?

Solution: Dependency trees are excel way of minimizing the calculation by tracking what has changed since last calculation. It allows Excel to recalculate only:
        * Formulae/Names that have changed.
        * Formulae containing Volatile Functions
        * Formulae dependent on changed or volatile formulae or cells or names.

Excel determines dependencies by looking at the cells referred to by each formula and by the argument list of each function.
Dependency trees are immediately updated whenever a formula is entered or changed.
To force the dependency trees to be rebuilt and all formulae calculation use CTRL+ALT+SHIFT+F9.

Ques 37. What are keyboard shortcuts and their equivalent VBA methods for Formulae calculation and building Excel dependency trees?

Solution:

Shortcut Combination	VBA Equivalent	Meaning
F9	Application.Calculate	Recalculate
Ctrl+Alt+F9	Application.CalculateFull	Full Calculation
Ctrl+Alt+Shift+F9	Application.CalculateFullRebuild	Rebuild Excel Dependency Tree and Full Calculation
Shift+F9	Sheets(1).Calculate	Calculate Worksheet

Ques 38. What does Range.Dirty used for ?

Solution: To add the specified cells to the list of cells requiring calculation at the next recalculation.

Ques 39. How do you check the Calculation state ?

Solution: Pretty simply, using the Application.CalculationState property which tells if calculation has completed ( xlDone ), is pending ( xlPending) , or is in process ( xlCalculating ).

Ques 40. How do you define Excel Calculation Interruption Key to stop the calculation?

Solution: Using Application.CalculationInterruptKey= XlAnyKey | XLEscKey | XlNokey.
Remember using XlNokey, calculation cannot be interrupted.

Ques 41. What does 'Workbook.ForceFullCalculation' do?

Solution: When this property is set to True, dependencies are not loaded at open, the dependency dirty chain is not updated, and every calculation of the workbook is a full calculation rather than a recalculation.

If you have a workbook that has so many complex dependencies that loading the dependencies at workbook open takes a long time or recalculation takes longer than full calculation, you can use this property to force Excel to skip loading the dependencies and always use full calculation. Also if making a change to the workbook takes a long time in manual mode because of the time taken to dirty all the dependencies of the cell being changed, then setting Workbook.ForceFullCalculation to True will eliminate the delay.

      * Although this is a workbook property the effect is at Application level rather than workbook level.
      * In Excel 2007 setting the property back to False once it has been set to True has no effect on the current Excel session.
      * The property is saved and restored with the workbook.
      * Once this property has been set to True 'Calculate' shows in the status bar and cannot be removed by using calculation keys such as F9.

Ques 42. What are excel dependency tree limits?

Solution: There are two limits to the number of dependencies that Excel versions prior to Excel 2007 can track before it must do full calculations instead of recalculations.
* The number of different areas in a sheet that may have dependencies is limited to 65,536.
* The number of cells that may depend on a single area is limited to 8K.
After the workbook has passed these limits, Excel no longer attempts to recalculate only changed cells. Instead, it recalculates all cells at each calculation.

Ques 43. Mention those conditions when status bar shows CALCULATE.

Solution: There are five known conditions in which the status bar will show CALCULATE:
       * The Calculation Option has been set to Manual and the workbook contains uncalculated formulae. Try setting calculation to Automatic (Tools-->Options-->Calculate). Note that Excel sets the calculation mode from the first workbook opened in a session: when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened.
       * The Iteration Option is turned on and the workbook contains circular references. Check that turning off Iteration (Tools-->Options-->Calculation) and pressing F9 shows "Circular Reference" in the statusbar.
       * You are using Excel 2000 without the SR1 update and have a user-defined function that attempts to define a name and depends on a volatile function: see MSKB Q248179
       * You have hit one of Excels limits for tracking dependencies.
       * You are using Excel 2007 and have set Workbook.ForceFullCalculation to True

Ques 44. What do you know about multi threaded calculation?

Solution: Excel 2007 can split calculation across multiple processors or cores. When Excel 2007 loads a workbook, it determines from the operating system how many processors are available and then creates a separate calculation thread for each processor. These threads can then run in parallel. The beauty of this system is that it scales extremely well with the number of processors.

Most workbooks show a significant improvement in calculation speed on a system with multiple cores. The degree of improvement depends on how many independent calculation trees the workbook contains. If you make a workbook that contains one continuous chain of formulas, it will not show any multithreaded calculation (MTC) performance gain, whereas a workbook that contains several independent chains of formulas will show gains close to the number of processors available.

Ques 45. How can we dial a phone number?

Solution: Shell command present in VBA can be used to start the dialer present in windows operating system. Phone number can be used to connect to your modem. With the use of shell and sendkeys you can dial to your user. Shell starts windows application and sendkeys inform the window to dial according to the keystrokes of the application. A macro can be used to start the cardfile program which activates the auto dialer feature.

Ques 46. What do you know about the interpretation features of VBA?

Solution: VBA is licensed to Microsoft and this compatible with and only Microsoft products. Code written is compiled by an intermediate language called P-code and this is stored in hosting applications such as Excel, Word and Access. The intermediate code is interpreted by a virtual machine. This code and intermediate language is the exclusive right of Microsoft.

Ques 47. Explain about insert module and Goal Seek functions present in VBA?

Solution: The chief use of VBA is to make use of its special function which helps in repeated actions. Goal seek function helps to reduce manual entry of the code each and every time. This solves the problem of repeated function entry by automating functions and actions. Sub routines are inserted into the using the VBA editor and command insert module.

Ques 48. State the difference between Visual Basic, VB Script and Visual Basic for Applications?

Solution: Visual basic is useful if you are planning to develop your programs from scratch.This language helps you in developing Active x controls, exe files, etc.
VB script is a powerful tool, through which you can create small scale applications on web pages, automation applications, etc. Integrated development environment is not present for VB script.
Visual Basic for Applications are very useful in automating your existing application. VB application is useful for developing already existing applications.

Ques 49. Write a macro to select all the non-blank cells of Activesheet ?

Solution:
Sub NonBlankCells()
    On Error Resume Next
    Union(Cells.SpecialCells(xlCellTypeFormulas, 23), Cells.SpecialCells(xlCellTypeConstants, 23)).Select
    If Err.Number <> 0 Then
        Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    Else
        Exit Sub
    End If
    If Err.Number <> 0 Then
        Cells.SpecialCells(xlCellTypeConstants, 23).Select
    Else
        Exit Sub
    End If
    On Error GoTo 0
End Sub

Ques 50. What is the difference between UsedRange and CurrentRegion properties ?
Solution:
i) The current region is a range bounded by any combination of blank rows and blank columns.
This property is useful for many operations that automatically expand the selection to include the entire current region, such as the AutoFormat method. This property cannot be used on a protected worksheet.
The UsedRange property is used to select the range of used cells on a worksheet. It returns a Range object that represents the used range on the specified worksheet.
ii) Every non-blank cell got its CurrentRegion and its keyboard shortcut is Ctrl+Shift+Spacebar.
iii) There can be many current regions but there is only one used range in a worksheet.

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP OFFSET Custom Format Array Formula