BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Change Capital Letters to Small (Upper, lower, Proper Case conversion using Excel/VBA)

This post contains VBA macro example on how to convert the words, phrase or sentence from 'lower' to 'UPPER'(Capital letters) 'Proper' or 'Sentence' case or vice-versa using Microsoft Visual Basic for Applications in Microsoft Excel. We will loop through all cells using FOR-NEXT and will set the case as required.

If you are new to the VBA macros in MS Excel, follow these instructions:

  1. Create a new or blank Excel workbook.
  2. Press ALT+F11 to open the Visual Basic Editor in MS Excel.
  3. Go to 'Insert' in the Menu --> Select 'Module'.
  4. Copy and Paste any or all of the following macro in the new module sheet.

15 Excel Pivot Table Interview Questions with Answers


Going with the belief that you are aware of or have working knowledge on Pivot Tables, I've prepared a set of questions which might help you in your upcoming Excel Interviews. Please do not take it as the only guide to crack the interviews, instead treat this as refresher before you go for interview. As I had already mentioned in 3 things to remember before you go for an Interview, always remember technical attitude matters more than the technical knowledge.

This set of Oral questions on Excel Pivot Table will help both the Interviewer and the Interviewee (You). Do not forget to read a comprehensive collection of 50 Excel VBA Oral Interview Questions.

Remove Color of all Sheet's tabs in Microsoft Excel

This post contains VBA macro example on how to remove the color of sheet tabs using Microsoft Visual Basic for Applications in Microsoft Excel. We will loop through all worksheets using FOR-NEXT and will set the sheet's tab color to None. If you are new to the VBA macros in MS Excel, follow these instructions:
  1. Create a new or blank Excel workbook.
  2. Press ALT+F11 to open the Visual Basic Editor in MS Excel.
  3. Go to 'Insert' in the Menu --> Select 'Module'.
  4. Copy and Paste any or all of the following macro in the new module sheet.

Close all Saved Workbooks in Excel Application

Imagine you're working on some important excel files and gradually your taskbar is full of open workbooks. Some have similar names (not same ofcourse) and now you want to close all the saved workbooks and want unsaved workbooks to remain open. This macro will definitely solve your purpose. This is a kind of macro, one could save in a personal workbook and assign it to a 'Shortcut key'.

If you are new to the VBA macros in MS Excel, follow these instructions:
  1. Create a new or blank Excel workbook.
  2. Press ALT+F11 to open the Visual Basic Editor in MS Excel.
  3. Go to Menu --> Select 'Insert' --> Select 'Module'.
  4. Copy and Paste any or all of the following macro in the new module sheet.

Delete 'n' characters from a range of cells in MS Excel

This post contains VBA macro examples on how to manipulate text strings using the Left, Right and Len functions in Microsoft Visual Basic for Applications in Microsoft Excel to delete some part of the text strings. If you are new to the VBA macros in MS Excel, follow these instructions:
  1. Create a new or blank Excel workbook.
  2. Press ALT+F11 to open the Visual Basic Editor in MS Excel.
  3. Go to 'Insert' in the Menu --> Select 'Module'.
  4. Copy and Paste any or all of the following macro in the new module sheet.

MS Excel Formulas: CHAR/CODE - Text Functions

CHAR function: Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. (Source: Microsoft Office Help)

For example: CHAR(65) would return the alphabet A, similarly CHAR(10) would return new-line.


Some important ASCII codes helpful in MS Excel:
9     -      Horizontal Tab
10   -     New Line character
11   -    Vertical Tab
32   -    Space
33 to 41 - Special Characters above numbers on keyboard i.e. !, @, # .... to ... *, ( and ).
48 to 57 - Numbers 0 to 9
65 to 90 - Capital Letters A to Z
97 to 122 - Small Letters a to z
153   -   Trademark symbol TM 
169   -   Copyright Symbol ©

Currency Symbols:
¤ - Generic Currency comes from CHAR(164)
$ - Dollar comes from CHAR(36)
£ - Pound comes from CHAR(163)
¥ -  Yen comes from CHAR(165)
€  - Euro symbol comes from CHAR(128)
¢  - Cent symbol comes from CHAR(162)

Please note that 
i) CHR function in VBA is equivalent to CHAR function in MS Excel. The behavious of CHR function in VBA is analogous to CHAR function in MS Excel.
ii) CODE function in MS Excel is inverse of CHAR function. For eg. CODE("A") would return 65. It means CODE(CHAR(100)) = 100.

Let's move on to some important examples to illustrate the extensive use of CHAR function in MS Excel:

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 Row
e.g. $A$1
means Whether you use auto-fill across column or across row, the reference won't be changed.

2. Absolute Column/Relative Row
e.g. $A1
means 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 Row
e.g. A$1
means 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 Row
e.g. A1
means 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. 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.


Image:Replace Characters of Filenames ina directory



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"

Validate E-mail Addresses

Problem: Imagine you've a large database of email-addresses or you copied a web page containing e-mail addresses and other unnecessary text and you want to highlight valid or extract valid E-mail addresses from them.

Solution:
Rules:
i) As per the problem, designing a function(UDF) would be the best approach as it can be used in worksheet as well as sub-routines.
ii) Since it's a email address, @ must appear exactly once.
iii) It must only contain alphanumeric set, underscore(_),hyphen or dash(-) and period/dot(.)
iv) Leftmost and Rightmost character should not be .
v) Period/dot (.) must appear at least once after the @.
vi) There should be either 2 or 3 characters(eg. com, net, org, in, us, uk etc) after the last period (.)
vii) There must be at least one alphanumeric character before @

Excel Formulas & Functions: Array Formulas or CSE Formulas

The examples of Array (or CSE) formulas below are based on an awesome article of Microsoft Office Official Website - Click here to see the article

What is an ARRAY?
An array is a collection of items. In Excel, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You cannot create three-dimensional arrays or array formulas in Excel.

An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. An array formula that resides in multiple cells is called a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.

Excel Formula and Functions: SUBSTITUTE

ABOUT: SUBSTITUTE function substitutes(replaces) old_text with new_text. When you want to replace a certain text in a text string. SUBSTITUTE is different from REPLACE Function which replace any text (not specified string) which occurs at specific location. SUBSTITUTE finds the old_text and replace with new_text while REPLACE finds the position and number of characters to be replaced.

SYNTAX:
=SUBSTITUTE(OriginalText, TextToRemove, TextToInsert, instance_num)

OriginalText  → The Text or the reference to a cell containing text for which you want to substitute characters or text string.

TextToRemove → The text to be replaced with TextToInsert.

TextToInsert → The New Text - The Text you want instead of TextToRemove.

instance_num→ (It is Optional) - It specifies which occurence of TextToRemove you want to substitute with TextToInsert. If you specify instance_num, then only that instance of TextToRemove will be replaced. If it is omitted all instances of TextToRemove will be substituted with TextToInsert.

How to Embed Flash in Excel (Office Applications)



Query Source : Excel Macros Google Group
Solution Type : Flash and MS Excel
Query by        : Mahesh Parab
Solution by     : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)


Query / Problem:
Dear All, Any one know the way how to embed a flash project into Excel ?

Solution:

1. Select the sheet in which you want to embed the flash project.

2. Go to View -> Toolbars -> Control Toolbox and click on the icon (More Controls in the last) which looks like a little hammer.

3. Select "Shockwave Flash Object" from the alphabetical list.

Export Excel 2007 Charts to Powerpoint 2007 Slides

Description: This excel macro will export all the charts from the activesheet of excel workbook to a new powerpoint presentation. If there are 16 charts, then they'll be exported to 4 slides (4 Charts per slide.).

Difference between SUMIF and SUMIFS Function: Excel Formulas & Functions


1. SUMIFS is available only in MS Excel 2007 while SUMIF is available in both .i.e Excel 2003 and Excel 2007.

2. SUMIF is used to add a single continuous range based on single specified range with a single criteria but SUMIFS can be applied over multiple continuous range of same size and shape on multiple specified range equal to number of criteria applied.

Calculate Business Working Days between two dates (Excluding Saturdays and Sundays)

    Function GetWorkDays(ByVal StartDate As Long, ByVal EndDate As Long) As Long
        ' Returns the count of days between StartDate - EndDate minus Saturdays and Sundays
        Dim d As Long, dCount As Long
        For d = StartDate To EndDate
            If Weekday(d, vbMonday) < 6 Then
                dCount = dCount + 1
            End If
        Next d
        GetWorkDays = dCount
    End Function


   Sub CalculateBusinessDays()
   'This module will Calculate Business Working Days between two Dates given in below format.
   'ColA     Col B     ColC
   'SDate    eDate     Result(Empty Cell)
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            If IsDate(MyCell.Value) And IsDate(MyCell.Offset(0, 1).Value) And IsEmpty(MyCell.Offset(0, 2)) Then
                MyCell.Offset(0, 2).Value = GetWorkDays(MyCell.Value, MyCell.Offset(0, 1).Value)
            End If
        Next
    End Sub

Select or Color Alternate Even/Odd Columns

    Sub SelectAlternateOddColumns()
        Dim MyCell As Range
        Dim rNew As Range
        For Each MyCell In ActiveSheet.UsedRange.Cells
            If MyCell.Column Mod 2 = 1 Then
                If rNew Is Nothing Then
                    rNew = MyCell
                Else
                    rNew = oEXA.Union(rNew, MyCell)
                End If
            End If
        Next
        If Not rNew Is Nothing Then
            rNew.EntireColumn.Select
        End If
    End Sub


    Sub SelectAlternateEvenColumns()
        Dim MyCell As Range
        Dim rNew As Range
        For Each MyCell In ActiveSheet.UsedRange.Cells
            If MyCell.Column Mod 2 = 0 Then
                If rNew Is Nothing Then
                    rNew = MyCell
                Else
                    rNew = oEXA.Union(rNew, MyCell)
                End If
            End If
        Next
        If Not rNew Is Nothing Then
            rNew.EntireColumn.Select
        End If
    End Sub

Select or Color Alternate Even/Odd Rows

    Sub SelectAlternateOddRows()
        Dim MyCell As Range
        Dim rNew As Range
        For Each MyCell In ActiveSheet.UsedRange.Cells
            If MyCell.Row Mod 2 = 1 Then
                If rNew Is Nothing Then
                    rNew = MyCell
                Else
                    rNew = oEXA.Union(rNew, MyCell)
                End If
            End If
        Next
        If Not rNew Is Nothing Then
            rNew.EntireRow.Select
        End If
    End Sub

    Sub SelectAlternateEvenRows()
        Dim MyCell As Range
        Dim rNew As Range
        For Each MyCell In ActiveSheet.UsedRange.Cells
            If MyCell.Row Mod 2 = 0 Then
                If rNew Is Nothing Then
                    rNew = MyCell
                Else
                    rNew = oEXA.Union(rNew, MyCell)
                End If
            End If
        Next
        If Not rNew Is Nothing Then
            rNew.EntireRow.Select        End If
    End Sub

Change Polarity or Number Sign

    Sub Change_Number_Sign()
        'This module will change the sign of numbers i.e. (positive to negative) and (negative to positive).
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            If Not (IsEmpty(MyCell.Value)) And IsNumeric(MyCell.Value) Then
                MyCell.Value = MyCell.Value * (-1)
            End If
        Next
    End Sub

Convert Numbers to Text (To Append or Prefix Zero)

    Sub Convert_Numbers_to_Textual_Values()
        'This module will convert numbers to their respective textual values.
        'This is specially required when you want to append zeroes to numbers.
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            If IsNumeric(MyCell.Value) Then
                MyCell.Value = Str(MyCell.Value)
                MyCell.NumberFormat = "@"
            End If
        Next
    End Sub

Convert Numbers appearing as Text to their Values (Number Format) (Remove Green Sign)

    Sub Convert_Textual_Numbers_to_Values()
        'This module will convert numbers appearing as text (Green marked) to their respective values in Number format.
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            MyCell.NumberFormat = "0"
            MyCell.Value = Val(Trim(MyCell.Value))
        Next
    End Sub

Convert Formulas to Values

    Sub Convert_Formulas_to_Values()
        ' This module will replace formulas of selected cells with their corresponding values.
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            MyCell.Copy
            MyCell.PasteSpecial(Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, _
                                SkipBlanks:=False, Transpose:=False)
        Next
    End Sub

SuperScript or Subscript the First or Last Character of Text in Selected Range Cells

    Sub SuperScriptFirstCharacter()
        ' This module will make first character super scripted in the selection.
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            If Len(Trim(MyCell.Value)) > 0 Then
                MyCell.Characters(Start:=1, Length:=1).Font.Superscript = True
            End If
        Next
    End Sub




    Sub SuperScriptLastCharacter()
        ' This module will make last character super scripted in the selection.
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            If Len(Trim(MyCell.Value)) > 0 Then
                MyCell.Characters(Start:=Len(MyCell.Value), Length:=1).Font.Superscript = True
            End If
        Next
    End Sub


    Sub SubScriptFirstCharacter()
        ' This module will make first character subscript in the selection.
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            If Len(Trim(MyCell.Value)) > 0 Then
                MyCell.Characters(Start:=1, Length:=1).Font.Subscript = True
            End If
        Next
    End Sub


    Sub SubScriptLastCharacter()
        ' This module will make last character subscript in the selection.
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            If Len(Trim(MyCell.Value)) > 0 Then
                MyCell.Characters(Start:=Len(MyCell.Value), Length:=1).Font.Subscript = True
            End If
        Next
    End Sub

Rainbow color the Text

    Dim Colorarr(7) As Short

    Sub ColorArray()
    'Change the color index to be included here.
        Colorarr(0) = 41
        Colorarr(1) = 39
        Colorarr(2) = 17
        Colorarr(3) = 50
        Colorarr(4) = 44
        Colorarr(5) = 46
        Colorarr(6) = 3
    End Sub
    Sub RainBowColors()
        ' This module will apply rainbow font color to all the selected cells.
        Call ColorArray()
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            For i = 1 To Len(MyCell.Value)
                With MyCell.Characters(Start:=i, Length:=1).Font
                    .ColorIndex = Colorarr(i Mod 7)
                End With
            Next
        Next
    End Sub

Extract Alphabets (Remove Digits) from Selected Range

    Public Function ExtractAlpha(ByVal cell As String) As Object
        'This function will extract 1st continuous set of digits.
        Dim i As Long, flag As Long
        flag = 0
        ExtractAlpha = ""
        For i = 1 To Len(cell)
            If (Asc(Mid(cell, i, 1)) >= 65 And Asc(Mid(cell, i, 1)) <= 90) Or (Asc(Mid(cell, i, 1)) >= 97 And Asc(Mid(cell, i, 1)) <= 122) Then
                flag = 1
                ExtractAlpha = ExtractAlpha & Mid(cell, i, 1)
            Else
                If flag = 1 Then Exit Function
            End If
        Next i
    End Function


 Sub Extract_Alpha()
        ' This module will extract alphabets from selected range of cells using above User-Defined function.
       Dim MyCell As Range
            For Each MyCell In Selection.Cells
                MyCell.Value = ExtractAlpha(MyCell.Value)
            Next
        MyCell = Nothing
    End Sub

Extract Digits (Remove Alphabets) from Selected Range

    Public Function ExtractDigits(ByVal cell As String) As Object
        'This function will extract 1st continuous set of digits.
        Dim i As Long, flag As Long
        flag = 0
        ExtractDigits = ""
        For i = 1 To Len(cell)
            If Mid(cell, i, 1) >= "0" And _
               Mid(cell, i, 1) <= "9" Then
                flag = 1
                ExtractDigits = ExtractDigits & Mid(cell, i, 1)
                ExtractDigits = ExtractDigits * 1
            Else
                If flag = 1 Then Exit Function
            End If
        Next i
    End Function

    Sub Extract_Digits()
        ' This module will extract digits from selected range of cells using above User-defined function.         Dim MyCell As Range
        Dim tmpCol As Integer
        Dim flag As Boolean
        tmpCol = Selection.Column
        For Each MyCell In Selection.Cells
                MyCell.Value = ExtractDigits(MyCell.Value)
        Next
        MyCell = Nothing
    End Sub

Reverse the text in selected cells (Mirroring/Plain Encryption of Text)

   Sub Reverse_Text()
        ' This module will reverse the text of selected cells using StrReverse function.
        ' For e.g. Macro will become orcaM
        Dim MyCell As Range
        For Each MyCell In Selection.Cells
            MyCell.Value = StrReverse(MyCell.Value)
        Next
        MyCell = Nothing
    End Sub

Use of Random Function (Rnd) and Select Case statement


Brief Description:
Here, I present the code to use random function in association with the Select Case statement. It use random function to generate a random value of S, H, D, or C for the four card suits (spades hearts diamonds or clubs). When user clicks the button, an input box pops up to accepts a value of S, H, D, C If the guess matches the random number respond with “Good guess”, if not then it respond with “Bad Guess”.

Code:
Sub GuessNumbers()
    Dim myStr As String
    myStr = InputBox("Please enter ""S"", ""H"",""D"" or ""C""", "Make a Guess", "")
    Select Case Rnd(1) * 10 Mod 4
    Case 0
        Select Case UCase(myStr)
            Case "S"
                MsgBox "Good Guess"
            Case "H"
                MsgBox "Bad Guess"
            Case "D"
                MsgBox "Bad Guess"
            Case "C"
                MsgBox "Bad Guess"
        End Select
    Case 1
        Select Case UCase(myStr)
            Case "S"
                MsgBox "Bad Guess"
            Case "H"
                MsgBox "Good Guess"
            Case "D"
                MsgBox "Bad Guess"
            Case "C"
                MsgBox "Bad Guess"
        End Select
    Case 2
        Select Case UCase(myStr)
            Case "S"
                MsgBox "Bad Guess"
            Case "H"
                MsgBox "Bad Guess"
            Case "D"
                MsgBox "Good Guess"
            Case "C"
                MsgBox "Bad Guess"
        End Select
    Case 3
        Select Case UCase(myStr)
            Case "S"
                MsgBox "Bad Guess"
            Case "H"
                MsgBox "Bad Guess"
            Case "D"
                MsgBox "Bad Guess"
            Case "C"
                MsgBox "Good Guess"
        End Select
    End Select
End Sub


Delete or Remove Event Macros, VBA Code and Module

Brief Description: 
This macro will delete all event macros, sub routines and user defined functions from Active workbook. This requires reference to "Microsoft Visual Basic for Applications Extensibility 5.3" and trusted access to VBA project model.(Developer --> Macro security --> Trust access to VBA project model).

Code: 
Sub RemoveAllVBACode()
'This code will delete ALL VBA code in a VBProject.
On Error GoTo Errorhand
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
        Set VBProj = ActiveWorkbook.VBProject
        For Each VBComp In VBProj.VBComponents
            If VBComp.Type = vbext_ct_Document Then
                Set CodeMod = VBComp.CodeModule
                With CodeMod
                    .DeleteLines 1, .CountOfLines
                End With
            Else
                VBProj.VBComponents.Remove VBComp
            End If
        Next VBComp
    Exit Sub
Errorhand:
        MsgBox "Go to Developer Tab --> Macro Security and enable ""Trust access to VBA project object model."" and then try again."
On Error GoTo 0
End Sub

Force Save and Unsave of Workbooks


Brief: Sometimes event macros are of great help. These two macros are example of that. While one macro protects a workbook from saving any changes, other macro force changes to be saved, if the former macro is present in the workbook. Both can be considered hacks of excel programming or a trick, but they're always useful and as an excel programmer you should know these two fundas.

Macro #1: Make Workbook unacceptable to any changes.
Pre-requisite: Event Programming, VBA and Excel
Logic: Use of Workbook's Saved property and Workbook's BeforeSave event. As soon as excel encounter any changes, it changes Saved property value to FALSE so when you attempt to save the workbook, excel checks this property value and save the workbook, if it founds to be FALSE but if it is TRUE, then it ignores the operation.
Now we use BeforeSave event and turn this property value to TRUE everytime BeforeSave event fired. So this makes workbook immune to any changes.
Code:
'PLACE THIS CODE IN THISWORKBOOK, NOT IN SHEETS OR MODULE
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'CHEATING APPLICATION BY FORCING SAVED PROPERTY TO BE TRUE
    ThisWorkbook.Saved = True
End Sub


Macro #2. Force changes to be saved in such workbooks (as mentioned above).
Pre-requisite: Event Programming, VBA and Excel
Logic: Disable Events, Save the workbook and then enable events again. Yes, it is that simple and here is its code.
Code:
 Sub ForceSave()
    On Error Resume Next
'THIS WILL DISABLE ALL EVENTS INCLUDING Workbook_BeforeSave.
    Application.EnableEvents = False
'SAVE THE WORKBOOK
    ActiveWorkbook.Save
'ENABLE THE EVENTS AGAIN
    Application.EnableEvents = True
    On Error GoTo 0
End Sub


Note: You fill find workbooks with Macro#1 generally VBE password protected, in such case if you want to use Macro#2, either write it in Personal workbook and assign a shortcut key or assign it to a toolbar button.


Invert the Selection



Code:

Sub InvertSelection()
    Dim rBig As Range
    Dim rSmall As Range
    Dim cell As Range
    Dim rNew As Range
    Set rBig = ActiveSheet.UsedRange
    If TypeName(Selection) = "Range" Then
        Set rBig = Selection.Parent.UsedRange
        Set rSmall = Selection
    End If
    If Not rSmall Is Nothing Then
        For Each cell In rBig.Cells
            If Intersect(cell, rSmall) Is Nothing Then
                If rNew Is Nothing Then
                    Set rNew = cell
                Else
                    Set rNew = Union(rNew, cell)
                End If
            End If
        Next cell
    End If
    If Not rNew Is Nothing Then
        rNew.Select
    End If
End Sub



UnProtect all WorkSheets in the WorkBook



Code:

Sub UnProtectAll()
    Dim wSheet As Worksheet
    Dim Pwd As String
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
    On Error Resume Next
    For Each wSheet In Worksheets
        wSheet.Unprotect Password:=Pwd
    Next wSheet
    If Err <> 0 Then
        MsgBox "You have entered an incorrect password. All worksheets could not " & _
        "be unprotected.", vbCritical, "Incorrect Password"
    End If
    On Error GoTo 0
End Sub