MS Excel & VBA Macros

Using Custom Format for Numbers, Dates and Text

2011-01-05T22:57:00.000+05:30

Click here to read full article on 'Custom' Format.

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.

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

Click here to read full article on 'Custom' Format including intermediate and advanced examples with theory and logic.

SHRINK REDUCE EXCEL FILE SIZE

2010-12-31T00:11:00.000+05:30

If minor or little changes in your excel workbook bloated its size to 200% and above then it's a serious issue but not worry issue as it has solution.

Understand the problem: 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 on eXceLiTems.com.

Read full article on reducing excel workbook size using VBA

3 Things to remember before Excel VBA Job Interview

2010-12-29T23:56:00.001+05:30

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

If a good interviewer will conduct an interview, he will not probe your technical knowledge only but also how much logical and reasoning thinking you possess along with some programming etiquette. You must remember these 3 things are interviewed in a good Excel and VBA job interview for Analyst position:
1. General Programming Attitude
2. Knowledge of Excel Object Model
3. Excel Knowledge (Data Validation, Subtotal, Formulas etc.)

Read the full article here written by Ashish Jain on eXceLiTems.com

50 Excel VBA Oral Interview Questions

2010-12-28T23:50:00.000+05:30

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'

Click here to read all the 50 questions and answers.

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

Click here to read all the 50 questions and answers.

Excel 2003 Style Menu in Excel 2007/2010

2010-12-27T23:21:00.000+05:30

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.

Click here to create Excel 2003 Style Menu in Excel 2007/2010

17 ways to Optimize VBA Code for FASTER Macros

2010-12-27T00:01:00.000+05:30

Here is a summary of the article:
Do read full article to understand the logic behind them.
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.

VBA - Creating PowerPoint Presentation

2008-08-07T17:31:00.002+05:30

Most often we will come across a scenario where powerpoint slides need to be created automatically.

Here is a sample & simple code to do that. This code is created using VBA (Excel 2000)

Sub Create_PowerPoint_Slides()

On Error GoTo Err_PPT

Dim oPA As PowerPoint.Application
Dim oPP As PowerPoint.Presentation
Dim oPS As PowerPoint.Slide
Dim oShape As PowerPoint.Shape
Dim sPath As String
Dim sFile As String
Dim i1 As Integer

sPath = "C:\"
sFile = "MyfileName"

Set oPA = New PowerPoint.Application
oPA.Visible = msoTrue

Set oPP = oPA.Presentations.Add(msoTrue)

For i1 = 1 To 10
oPP.Slides.Add 1, ppLayoutBlank
Next i1

Set oPS = oPP.Slides(1)
Set oShape = oPS.Shapes.AddTextbox(msoTextOrientationHorizontal, 140#, 246#, 400#, 36#)
oShape.TextFrame.WordWrap = msoTrue

oShape.TextFrame.TextRange.Text = "Comments For File : " & sFile
With oShape
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.RGB = RGB(204, 255, 255)
.Line.Weight = 3#
.Line.Visible = msoTrue
.Line.ForeColor.SchemeColor = ppForeground
.Line.BackColor.RGB = RGB(255, 255, 255)
End With

oPP.SaveAs sPath & sFile & ".ppt"
oPP.Close
oPA.Quit

If Not oPS Is Nothing Then Set oPS = Nothing
If Not oPP Is Nothing Then Set oPP = Nothing
If Not oPA Is Nothing Then Set oPA = Nothing

Err_PPT:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
Resume Next
End If

End Sub

MCA Percentage Calculator (IP University)

2008-06-22T09:28:00.003+05:30

.

Click here to Calculate your Cumulative Percentage
(Only for IP University MCA Students)

*Note : Please download the VBA tool and fill the boxes with your marks to calculate your final cumulative percentage.

.

Stock Market Portfolio Tracker in MS Excel

2007-09-30T17:30:00.000+05:30

This excel file will download stock prices from yahoo server.

This portfolio tracker download stock prices automatically from Yahoo server. We just need to open the file & update your stock codes & click the download button

It is very user friendly.Just read the instructions in "ReadMe.

In “Portfolio” sheet we can track all details like purchase price, current price
Previous Closing price, Change in %, Volume, Day H, Day L, 52 H, 52 L, Date, Time

Download

Two tips for giving your Excel worksheets a professional look

2007-07-28T15:23:00.001+05:30

Excel provides several underline formats to give your spreadsheets a professional look and to make them easier to read at a glance. For example, labels and values are often underlined to distinguish them from the rest of the data. To access these formats, click on the cell you want formatted and press [Ctrl]1. Click on the Font tab and then click on the drop-down arrow of the Underline combo box. This box offers four different types of line formatting. While the Single and Double format are appropriate for text labels, they are not appropriate for indicating totals and subtotals. For Totals, you should choose the Double Accounting format; for Subtotals, choose the Single Accounting format.

Another way you can make your worksheets look more professional is to remove zero values. For example, you've just copied a formula down a column, and now you have blocks of cells containing 0.00 or 0.00%. You could go back and delete the formulas from those particular cells, but an easier method would be to change the worksheet to avoid displaying or printing the zero values. To do so, follow these steps:

Go to Tools | Options.
Click the View tab.
Under the Window Options section, clear the Zero Values check box.
Click OK.

10 simple tips for working more efficiently in Microsoft Excel

2007-07-28T15:17:00.000+05:30

#1: Exploit defined names

Defined names aren't just for ranges. You can use a defined name to define a constant value, such as a discount amount. Use the feature as you normally would, entering the literal value or expression that evaluates to the desired value into the named cell. For instance, select a cell and choose Name from the Insert menu. Then, select Define. Enter the descriptive name Discount and click OK. Now, in the same cell, enter the actual discount amount, say 3 percent (just enter .03). Now, you can use the defined name, Discount, in your formulas instead of entering the literal value .03. For instance, Excel would use .03 for Discount when evaluating the following formula:

=TotalPrice - (TotalPrice * Discount)

This quick tip has two benefits: It makes updating much simpler, as you can quickly change the value in Discount and Excel will automatically update all dependent formulas. And it eliminates data entry errors.

#2: Quick copy to noncontiguous cells

Copying data or a formula is simple. You just drag the source cell's fill handle and Excel copies the data or formula from the source cell to the cells you select using the fill handle. But copying isn't always a nice, neat, contiguous package. Sometimes you need to copy data or a formula to a series of noncontiguous cells. You could paste the source data into each individual cell, but that's the hard way. Instead, you can copy data into a noncontiguous block.

First, copy the source data. Then, hold down the Ctrl key while you click each cell in the noncontiguous destination range. Once you've highlighted each target cell, press Ctrl+V, and Excel will copy the source data into each of the highlighted cells. Formulas copied this way obey referencing rules, in regard to absolute and relative addresses.

An alternate method is to right-click in the cell that contains data you want to copy and choose Copy from the resulting submenu. Then, right-click a destination cell and choose Paste. At this point, the source cell is still highlighted, which means you can copy the contents again. Right-click another destination cell and choose Paste. Continuing selecting destination cells until you've completed the copy task. Press Esc to clear the selection of the source cell.

#3: Customize defaults

Excel uses template files to control default settings in new workbooks and sheets. For most of us, the settings are adequate. However, if you find yourself resetting the same defaults for each new workbook or sheet, consider changing the defaults permanently.

To change default settings for a workbook, open Book.xlt, make changes, and then save the file. Don't change the file's name; you're just updating it. (It's a good idea to create a copy of the original Book.xlt so you can revert to Excel's original settings if necessary. Name the copy BookOriginalSettings.xlt or something similarly descriptive.) After changing Book.xlt, all new workbooks will use the custom settings you applied. To change a sheet's default settings, open Sheet.xlt, make the necessary changes, and save it.

If you don't have one or both files, simply create your own. Just be sure to save them in Excel's XLStart folder (\Program Files\Microsoft Office\XLStart).

#4: Enter repetitive data quickly

Tip #2 shows you how to copy existing data into noncontiguous cells. You can also use this technique to enter data into a series of noncontiguous cells. Hold down the Ctrl key and click all the cells into which you want to enter data. Then, type the text you want to enter and press Ctrl+Enter. Excel will enter the typed text into all of the cells in the noncontiguous selection.

#5: Create custom lists

Most of us work with sets of data that seem to repeat themselves throughout our projects. That means we can enter the same values in numerous spots. If you frequently enter the same dataset, consider creating a custom list. To do so, choose Options from the Tools menu and then click the Custom Lists tab. In the List Entries control, enter each item in the list, one entry per line, in the order in which you want it to appear. When you've completed the list, click Add. Excel will copy the list to the Custom Lists control. Click OK to close the Options dialog. To enter the list, select a cell and enter any name in the list. Then use the fill handle to complete the list.

If you want a partial list, enter the item you want to begin with and then pull down the fill handle. Excel will fill in the remaining names.

If the list already exists in the sheet, you don't have to retype it to create a custom list. Simply select the list before choosing Options from the Tools menu. Then, click Import on the Custom Lists tab.

#6: Customize movement

By default, the cell pointer moves down when you press Enter. Selecting the cell immediately below the current one won't always be what you need. For instance, some people enter data from column to column. You could press the Right Arrow key instead of Enter, but out of habit, most of us reach for Enter. Even if you can retrain yourself (or users) to use the arrow keys, they're far enough away from the main keys to slow down data entry.

Fortunately, you can change the cell pointer's default direction. Chose Options from the Tools menu and then click the Edit tab. Select the Move Selection After Enter check box (if necessary) and then choose a direction from the option's drop-down list. For instance, to move from column to column, you might choose Right instead of Down.

While entering data, you can temporarily force the cell pointer to move in the opposite direction by holding down the Shift key while you press Enter.

#7: Hide everything but the working area

You usually hide a column or row to conceal or protect data and formulas. You can also hide unused regions of a sheet to keep users from exploiting unused areas or to help keep them on task by not allowing them to wander. By hiding unused rows and columns, you present a sheet that focuses on just the work area.

To hide unused rows, select the row beneath the sheet's last row. (Select the row header to select the entire row.) Next, press Ctrl+Shift+Down Arrow to select every row between the selected row and the bottom of the sheet. Then, choose Row from the Format menu and select Hide. Repeat this process to hide unused columns, only select the column header in the first empty column. Press Ctrl+Shift+Right Arrow and then choose Column from the Format menu instead of Row.

Before you hide anything, make sure you don't inadvertently hide an obscure area by pressing Ctrl+End to find the last cell in the sheet's used range. Unhide the rows and columns by selecting the entire sheet. Then, select Row or Column from the Format menu, and choose Unhide.

#8: View formulas, or not, quickly

You probably know that you can view all the formulas in a sheet by choosing Options from the Tools menu and selecting Formulas on the View tab. Doing so displays formulas instead of their evaluated results. But there's a quicker way. Press Ctrl+~ (the tilde character to the left of the number 1 on your keyboard). The keyboard combination toggles between formulas and normal view. When you're finished viewing the formulas, simply press Ctrl+~ again to return to normal view.

#9: Identify printed sheets

Printing a sheet is a common task. Some users find it useful to print the name of the workbook in the header or footer. In Excel 2003, you can accomplish this by choosing Page Setup from the File menu and clicking the Header/Footer tab. Then, choose the appropriate item from the Header control's drop-down list. Versions prior to 2003 can use the following VBA procedure to print the full file's pathname:

Sub FormatHeader()
 With ThisWorkbook
   ThisWorkbook.Worksheets(sheetname)PageSetup.LeftHeader = .FullName
 End With
End Sub

where sheetname is the sheet's name as a string value. To make the procedure more dynamic, use ActiveSheet.Name instead. That way you can run it against any sheet in the workbook.

#10: Speed up calculation time

How, when, and what Excel calculates is a huge subject. In general, cell references and calculation operations are the main performance vampires. Reasonable formulas and even lots of data don't usually slow things down. Complex formulas and repetitive references are the real culprits. Here are a few basic guidelines that should help you avoid calculation bottlenecks:

Avoid complex and array formulas. Use more rows and columns to store intermediate values and use fewer complex calculations.
Reduce the number of references in each formula to the bare minimum. Copied formulas are notorious for repeating references and calculations. Move repeated calculations to a cell and reference that cell in the original formula. (See Tip #1 for an alternate suggestion.)
Always use the most efficient function possible: Sort data before performing lookups; minimize the number of cells in SUM and SUMIF; replace a slow array with a user-defined function, and so on.
Avoid volatile functions if possible. Excel recalculates these functions with each recalculation, even if nothing has changed. Too many volatile functions (RAND(), NOW(), TODAY(), and so on) can slow things down.

Various amazing Calculators made in Excel

2007-05-06T09:33:00.000+05:30

1. The Ultimate Financial Calculator Download
Investment Valuation
Rates of Return
Financial Statements
Capital Budgeting
Time Value of Money
Loans and Leasing

2. EMI Calculator Download
Calculate EMI of any kind of Loan

3. Budget Planner Download
Create a quick and easy budget(Daily,Monthly,Yearly)

4.Amortisation Download
This calculator will give you a break - up of your Interest and Principal every month.

Selecting only blank cells

2007-04-21T15:58:00.000+05:30

This is one of the questions colleagues at work are often asking me. The need arises usually when you are importing data from the mainframe to Excel.
Many times you are getting something like this:

If you want to produce a Pivot Table from this data, you will have to fill the blank cells in column A:

values in cells A3 and A4 must be "Peter"
values in cells A6 and A7 will must be "Paul"
values in cells A9 and A10 must be "Mary"

This can be done manually in our example. But suppose you have, let's say, 2,000 records in your table. In that case you can take advantage from the "Go To - Special" feature in Excel.

How?

First select the whole relevant range (the column range, in our case A1:A10). Then press "Ctrl + G" or F5. You will get this:

Hit the "Special" button and mark "Blanks"

and hit "OK"

Now enter "=" (the equal sign) in cell A3 (the first blank cell in the selected range), , click cell A2 (the cell above the first blank cell), and hit "Ctrl + Enter". You will get this:

This technique can be useful in many other cases. For example, if you want to delete a whole line when some cell is blank (like in our example), you can select all the blank cells using F5, then press the right button in the mouse, from the pop-up menu select "Delete --> Entire Row" and press "OK". All the rows with a blank cell in the selected range will be deleted.

How to find where the two curves cross

2007-04-21T15:46:00.000+05:30

Sachin, a colleague at work, came to me with this question: How can I find the value of the point where to lines cross in a graph?
Excel don't seem to have a build-in procedure to find such a value. The workaround I suggest is to use the Solver Add In.

Let's look at this example:

The formulas to produce the graph are:

in cells B2 to B10: =3*A2+25
in cells C2 to C10: =A2^2+2*A2+1

Obviously Y1 crosses Y2 somewhere between X=4 and X=6. In order to find the exact value of X we'll use the Solver (in the menu bar: Tools-->Solver. If you can't see it, then you have to install the Add In).

Before opening the Solver copy the formulae in cell B10 and C10 to cells B12 and C12.

In the "set target cells" window write (or select) B12.

In the "by changing cells"window write or select A12.

In the "subject to constraints" window add two constraints:

B12 = C12 (the answer to our question)
A12>=0 (in order to get a positive value)

Now, your sheet will look like this:

Hit the "Solve" button, and you will get this:

CONVERTER FROM XL TO EXE

2007-04-11T21:58:00.000+05:30

This utility converts Excel files from the XL format to an EXE format (convert XLS to EXE, convert XLA to EXE, convert Excel to EXE). Like XL files, the EXE files created are not independent of Excel, but they allow you to define Excel's properties before opening, and improve the portability and functionality of your workbooks.

The conversion, moreover to maintain the original format features, inclusive for editing thru the Excel, adds others functionalities where the autocompression and the control over the Excel highlight. So, by be specialized in Excel and VBA, can be more advantageous than ZIP files, ZIP autoextracts and program installation utilities.

Download Converter

Tested in the Microsoft Excel for Windows versions 97, 2000, 2002(XP), 2003, and 2007.

EXE format features:

- Can be read and write (Changes done in an EXE file opening in the Excel to write are saved in the proper EXE file);
- When saving changes, backups are created. Optionally, you can specify whether to overwrite or not overwrite previous backups (This makes possible more restoration levels than using XL format);
- Can be read-only, very read-only, doesn’t permit ‘Save’ and ‘Save as’ too;
- Changes can be saved to a new XL file, leaving the EXE unaltered (Good to be used like a template);
- Changes can be saved to multiple XL files with an incrementing number (Good to be used like an invoice template);
- Can be opened in the Excel without macro alert or with a custom alert (Good to work with known files containing macros using the Excel in medium or high security level);
- Can show, without text size limit, an alert before opening in the Excel (Good to installation alerts, copyright, guidance etc.);
- Can require password-to-open so safe that only can be cracked by attempts (brute force) same knowing the converter source code. Then, if you put a password and forget it, no-one, neither this utility author, can help you;
- There are four levels of autocompression: none, normal (faster), medium (slower), and high (slower). Normal is equivalent to a normal zip file. This makes large files more portable, mainly when they need protection, because protected files in XL format are of low compression. In a test with a simple workbook of 5 Mb, the ZIP compressed only 14% and this got 74% because the cryptography is done after the compression;
- Can be attached one or more text or binary files (Good to be used as installer file);
- All attached XL files can be opened too at start and controlled as one whole like a 'workbook' of workbooks;
- Can be opened in an exclusive Excel window (exclusive Excel instance) refusing, while open, any try to share with other strange workbook;
- Can be forced the closure of running Excel instances at start up. This is helpful to use the EXE as an installer for a COM add-in;
- When opened in a separate instance, it can start Excel in automation mode, very automation mode; Excel will run totally clean without XLSTARTs, add-Ins, and COM add-ins too. This is better than /automation Excel command line option that doesn’t avoid COM add-ins;
- When opened in a separate instance, it can control the Excel window style (maximize, minimize, normal, or hidden). These four last features are good to be used when your workbook is a dictator application;
- You can avoid the original converted EXE file from being renamed;
- All the above features can be configured when you convert;
- The conversion can be done thru the utility interface or by command line;
- The command line can contain all of the options, alert text, and file names and paths to attach. You can generate long and complex command lines from the user interface and send them to the clipboard to be pasted anywhere, such as in an argument of VB or VBA Shell function or in the Windows Run dialog box;
- You can convert back to XL format using Excel's Save As command if not read-only;
- When convert, it creates a backup of the original XL format file without overwriting previous one;
- Files in this format can function as EXE in CD Autorun;
- The conversion only adds about 129Kb which, depending on the file size, can be regained during autocompression;
- The converter and any converted files are standalone in the presence of the Office library, no extra library is necessary and neither changes in your Excel or Windows setting.

Learn Lookups through videos

2007-04-01T18:24:00.000+05:30

Learn VLookup

Watch all excel tricks on Videos.

2007-04-01T18:19:00.000+05:30

little_more_excel_in_videos

Sybex - Mastering Excel 2003 Programming with VBA

2007-04-01T18:06:00.000+05:30

Sybex - Mastering Excel 2003 Programming with VBA

Excel VBA Programming Book for beginner and intermediate

Fractions of Seconds

2007-03-28T22:44:00.001+05:30

Fractions of Seconds

from Daily Dose of Excel by Dick Kusleika

Over on Swimming Splits, Olly asks:

I am a rowing coach and am putting the data that my crew gives me from their training sessions into a spreadsheet in the mm:ss.00 format. However, if I make a mistake inputing the data e.g type 1:23.03 when it should be 1:23.30 something weird happens.

When I click in the cell, the value rounds up or down depending so 1:23.30 will round to 1:23.00. This will have a massive impact on the results that I am looking at. Is this a common occurrence in excel or is there something that I am doing wrong or should be pressing?

Good question. I'm not sure that I know this answer definitively, but I'll tell you what I think based on what I see.

Dates and times are strange beasts in Excel. I should say that they are a strange beast because they are the same thing. As you probably already know, dates are stored as the number of days from a particular date, usually 31-Dec-1899. That means that 01-Jan-1900 is stored as 1 and 04-March-2007 is stored as 39,145. Similarly, times are stored as fractions of a day. While dates are integers, times of day are the fractions between the integers. Today may be 39,145, but today at 8:00AM is 39145.3333333. It's only 8:00AM and already a third of the day is gone.

This method is great for adding, subtracting, and generally doing other math operations on dates. What it's not good for is understandability by most humans. Excel attempts to bridge that gap by displaying dates and times as dates and times rather than these cryptic numbers. Note that when you enter 0:01.03 in a cell, the formula bar displays 12:00:01 AM. Usually the formula bar will tell you the truth regardless of how you've formatted the cells, but not in the case of dates and times. Like an over-protective mother, it shields you from the harsh realities. The noteworthy aspect of this example is that there are no hundredths in the formula bar. The hundredths are still stored, but they aren't displayed in the formula bar.

If you were to edit the cell, say, by pressing F2 and Enter you lose the hundredths. Pressing Enter is the same as entering 12:00:01 AM into the cell, which contains no hundredths. To change the cell from 3 hundredths to 3 tenths, follow these steps: F2 to edit the cell, backspace three times to remove the AM and the preceding space, type .3, press Enter. I don't know of any way to get the hundredths to display in the formula bar.

It seems rather easy for Microsoft to have done this differently. If a user enters fractions of seconds, display fractions of seconds. Otherwise display it as it is now. There doesn't seem to be a trade-off here that I can see (other than coding time by developers).

Calendar for 178 Years

2007-03-27T20:37:00.000+05:30

Calendar_for_178_years

Click above to download

World cup Cricket Tracker in Excel

2007-03-20T22:05:00.000+05:30

Track the world cup from this Excel Sheet.
It is Unique and Rarely available.

Schedular_cum_Result tracker

Magic Spread Sheet

2007-03-18T14:22:00.000+05:30

Magic SpreadSheet

This Magic Spread Sheet contains following MAGICS:
1)Disappearing the Card
2)Secret Number
3)Magic Eight Ball

Its Amazing.

To convert numbers into words(Rs.)

2007-03-18T14:17:00.000+05:30

Numbers+in+Words

This worksheet helps to convert numbers into words.

MS Excel Keyboard Shortcuts

2007-03-18T12:58:00.000+05:30

Navigating in Excel Shortcut

Switch between Worksheets CTRL-PageUp/CTRL-PageDown

Switch between Workbooks CTRL-Tab

Move one character up, down, left, or right. Arrow keys

Go to end of a contiguous range CTRL-Arrow Keys

Select a cell range SHIFT+Arrow keys

Highlight a contiguous range SHIFT-CTRL-Arrow Keys

Select entire worksheet CTRL+A

Move to the beginning of the line. HOME

Go To F5

Move a Sheet/Copy a Sheet Alt-E-M

Change Zoom Sizing Alt-V-Z

Entering and editing data Shortcut

Complete a cell entry and select…

...the cell below. ENTER

...the previous cell above. SHIFT+ENTER

...the next cell to the right. TAB

...the previous cell to the left. SHIFT+TAB

Delete cell and then get inside the cell BACKSPACE

Delete cell/selection. DELETE

Edit inside a cell (edit cell mode) F2

Once inside edit cell mode (F2)…

...Start a new line in the same cell. ALT+ENTER

...Highlight individual characters within cells SHIFT+Arrow keys

...Highlight contiguous string within cells SHIFT+CTRL+Arrow keys

...Delete the preceding character. BACKSPACE

...Delete the character to the right of the insertion point. DELETE

...Cancel a cell entry. ESC

Spell Check. F7

Insert a comment. SHIFT+F2

Fill down. CTRL+D

Fill to the right. CTRL+R

Undo the last action. CTRL+Z

Redo the last action. F4 or CTRL+Y

Hiding / Unhiding Rows and Columns Shortcut

Hide the selected rows. CTRL+9

Unhide any hidden rows within the selection. CTRL+SHIFT+( (opening parenthesis)

Hide the selected columns. CTRL+0 (zero)

Unhide any hidden columns within the selection. CTRL+SHIFT+) (closing parenthesis)

Selecting, grouping, inserting, and deleting cells Shortcut

Highlight Entire Row SHIFT+SPACEBAR

Highlight Entire Column CTRL+SPACEBAR

Group Rows or Columns SHIFT+ALT+RIGHT ARROW KEY

Ungroup Rows or Columns SHIFT+ALT+LEFT ARROW KEY

Clear the contents of the selected cells. DELETE

Delete the selected cells. CTRL+MINUS SIGN

Insert blank cells. CTRL+SHIFT+PLUS SIGN

Pivot Table Introduction

2007-03-13T22:26:00.000+05:30

Pivot Table Introduction

Pivot tables are used to summarize huge data in Excel.
You can rotate rows & column to view different reports.

Download above file to learn Pivot Tables.

Notes for Advance Filter in Excel

2007-03-13T22:19:00.000+05:30

Advanced Filter Notes

Complete info on how to use Advance Filter in MS Excel

Excel Keyboard Shortcuts

2007-03-13T22:16:00.000+05:30

Excel Keyboard Shortcuts

Excel keyboard Shortcuts to help you work quickly.

Excel Advanced Report Development

2007-03-10T13:46:00.000+05:30

Excel Advanced Report Development

Download Budget Planner

2007-03-10T13:28:00.000+05:30

Budget_Planner

Can you solve this puzzle??

2007-03-02T22:32:00.000+05:30

Puzzle

Click above to download the puzzle.

Its a challenge

VBA Macros ebook

2007-03-01T21:41:00.000+05:30

It is interactive ebook which will help you in learning & developing Macros.

Click below to download VBA Macros ebook

VBA Macro ebook

EMI Calculator

2007-02-18T17:20:00.000+05:30

Click the link below to download EMI Calculator

EMI_Calculator

For Sudoku Lovers

2007-02-16T16:29:00.000+05:30

Following files uploaded for sudoku lovers

1.Sudoku solver & sudoku Generator

2.Giant sudoku

3.Multiple solution sudoku

Click this link to download all above files :)

Sudoku_all.zip

Excel in MS EXCEL & VBA Macros

2007-02-10T11:03:00.000+05:30

==============================
Excel in MS EXCEL & VBA Macros
==============================

Click this button BELOW to Download Useful Excel Material

Orkut Excel Community