How to use MS Excel Macro Recorder ?

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.

MS Excel & VBA: Contact for Corporate Trainings

---

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 

---

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

 - 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

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.

---

Relative and Absolute References in Excel Formulas

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.

Online MS Excel Training: Quicker Excel

Split the Text with Newline Characters (Alt + Enter)

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 ....

Monthly Calendar Planner Template

---

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:

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

Click here to download the Monthly Calendar Dashboard.

---

Sort TCP/IP Address or Delimited Numbers

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.

---

Load Array from Sheet Range

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

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.

Create Excel Dashboard using OFFSET, SUM and MATCH functions

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.

---

ADD ERROR TRAP: Wrap your Formulas with IFERROR or ISERROR

'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.

How to Prevent Blank Cells from plotting in Excel Charts?

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 to send Lotus Notes e-mail using Excel VBA ?

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:

---

How to use OFFSET Function? (with Examples)

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)

Using Custom Format for Numbers, Dates and Text

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.

17 ways to Optimize VBA Code for FASTER Macros

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.

---

Excel 2003 Style Menu in Excel 2007/2010

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.

---

Show Grid on Colored Range (Apply Border)

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:

---

Replace Characters in the Filenames of a Directory

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.

---

Square Cells or Create Graph Cells with VBA

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.

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

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.

---

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

1.

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

2.

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

50 Excel VBA Oral Interview Questions

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.

Nth VLookup and HLookup Formulas (User Defined Function)

Problem: Many of us know "How to use VLOOKUP formula?" and are always troubled when we have more than 1 value in lookup column with the same name. Since VLOOKUP by default always return the value corresponding to first match.

Now you can use this user defined function to return Nth value in case of duplicate values.

Solution:

1. Copy the following VLOOKUPN or HLOOKUPN as desired in a module.
2. Use these formulas as desired like this:(only for reference)
VLOOKUPN(D1,A1:C500,3,2)

So, it will find second value of D1 in column A and return corresponding value from column C.

---

SHRINK REDUCE EXCEL FILE SIZE

When you received the file, the size was in Kbs or not more than 5-6 Mbs but when you checked it, did very little or negligible things, and saved your workbook, you found out the file size has been bloated to 3 to 100 times. It is possible, it happens in Excel. So don't worry too much.

Source of issue: First understand the difference between 'Excel Default Last Cell' and 'Actual Last Cell'. When you do 'Ctrl+End' to find last cell, you'll reach to 'Excel Default Last Cell' which may be the 'Actual Last Cell' or beyond the 'Actual Last Cell'. The more beyond 'Excel Default Last Cell' would be from 'Actual Last Cell', the more unnecessary size of excel workbook would it be having.

Solution: Delete all rows and columns beyond the 'Actual Last Cell' in every worksheet. If there are too many worksheets and large sets of data, you can use the VBA macro mentioned below.

---

Notifying with Flash Window or Message Beep

To notify the user that some kind of error has occurred or the application needs the user attention, many developers pushes applications to simply produce a sound by using the MessageBeep function or flash the window by using either the FlashWindow or FlashWindowEx function. An application can also use these functions to call attention to an error and then display a message box or an error message containing details about the error.

i. FlashWindow Function - Flashes the specified window one time. It does not change the active state of the window. Flashing a window means changing the appearance of its caption bar as if the window were changing from inactive to active status, or vice versa. (An inactive caption bar changes to an active caption bar; an active caption bar changes to an inactive caption bar.)

Typically, a window is flashed to inform the user that the window requires attention but that it does not currently have the keyboard focus.

ii. MessageBeep Function - Plays a waveform sound. The waveform sound for each sound type is identified by an entry in the registry. After queuing the sound, the MessageBeep function returns control to the calling function and plays the sound asynchronously.

If it cannot play the specified alert sound, MessageBeep attempts to play the system default sound. If it cannot play the system default sound, the function produces a standard beep sound through the computer speaker.

Note: The user can disable the warning beep by using the Sound control panel application.

---

Speed up Excel Start up (FASTER EXCEL)

Ever wondered why your excel startup so slow, even if you're working on the best motherboard and processor available in the market? Tried many things but no success. Try this, although this is one of the trick or might be the only reason but if it is, then you'll save a lot of your time.

One of the expected reason is Excel's toolbar file which tends to grow very big and this toolbar file is loaded when excel starts up. The extension of this excel toolbar file is XLB, default location is "Application Data\Microsoft\Excel\" and normal size is 10 to 90 KB. Problem arises when this size reaches in megaBytes(MB).

Solution: Delete all xlb file from these locations:
Windows 98 Users --> "C:\Windows\Application Data\Microsoft\Excel\excel*.XLB"
Windows 98SE/NT/ME Users --> "C:\Windows\Profiles\USERNAME\Application Data\Microsoft\Excel\excel*.XLB"
Windows 2000/XP Users --> "C:\Documents and Settings\USERNAME\Application Data\Microsoft\Excel\excel*.XLB"
Windows 7 Users --> "C:\Users\USERNAME\AppData\Microsoft\Excel\excel*.XLB"

Assumptions: USERNAME is your system user name and User Profiles or Operating System is installed on drive C. Please change the values accordingly.

WARNING: However, XLB file is re-created automatically but you'll lose all of your toolbar customizations (or say personal menu settings).

---

Schedule a VBA Macro (ALARM Setting)

Heylo, How is your office life going on? I pray for all of you and wish you lots of wealth and a very good health. Coming to our topic, we're actually gonna discuss Application.OnTime Object of Application class.
For details: Please visit Microsoft MSDN here:
http://msdn.microsoft.com/en-us/library/aa195809(office.11).aspx

We used Now + TimeValue(time) in our example to schedule something to be run when a specific amount of time (counting from now) has elapsed as TimeValue(time) is used to schedule something to be run a specific time.

For e.g.
1. This example runs my_Procedure 15 seconds from now.
Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

2. This example runs my_Procedure at 5 P.M.
Application.OnTime TimeValue("17:00:00"), "my_Procedure"