One new Learning per day

2010-07-29T07:05:00.000-07:00

Freeing Unmanaged Resources

2010-02-11T03:24:00.000-08:00

The garbage collector does not know how to free unmanaged resources (such as file handles, network connections, and database connections). When defining a class, you can use two mechanisms to automate the freeing of unmanaged resources

Declaring a destructor (or finalizer) as a member of your class

Implementing the System.IDisposable interface in your class

Destructors
Destructors are called before an object is destroyed by the garbage collector. When you define a destructor in C#, what is emitted into the assembly by the compiler is actually a method called Finalize().
class MyClass
{
~MyClass()
{
// destructor implementation
}
}

The following example shows the C# code equivalent to the IL that the compiler would generate for the~MyClass destructor:

protected override void Finalize()
{
try
{
// destructor implementation
}
finally
{
base.Finalize();
}
}

C# destructors are non deterministic
When a C++ object is destroyed, its destructor runs immediately. Here it depends on GC. Another problem with C# destructors is that the implementation of a destructor delays the final removal of an object from memory.Objects that do not have a destructor get removed from memory in one pass of the garbage collector, but objects that have destructors require two passes to be destroyed:
The first one calls the destructor without removing the object, and the second actually deletes the object.

The IDisposable Interface
In C#, the recommended alternative to using a destructor is using the System.IDisposable interface
The implementation of Dispose() should explicitly free all unmanaged resources used directly by an object
Suppose you have a class named ResourceGobbler, which relies on the use of some external resource and implements IDisposable

ResourceGobbler theInstance = new ResourceGobbler();

// do your processing

theInstance.Dispose();

Unfortunately, this code fails to free the resources consumed by theInstance if an exception occurs during processing

ResourceGobbler theInstance = null;

try
{
theInstance = new ResourceGobbler();

// do your processing
}
finally
{
if (theInstance != null) theInstance.Dispose();
}

C# offers a syntax that you can use to guarantee that Dispose() will automatically be called against an object that implements
IDisposable when its reference goes out of scope
using (ResourceGobbler theInstance = new ResourceGobbler())
{
// do your processing
}

variable to be scoped
when that variable goes out of scope, its Dispose() method will be called automatically, even if an exception occurs.

Implementing IDisposable and a Destructor
The execution of a destructor is enforced by the runtime but is nondeterministic and places an unacceptable overhead on the runtime because of the way garbage collection works.
The IDisposable interface provides a mechanism that allows users of a class to control when resources are freed, but requires discipline to ensure that Dispose() is called.

Regards,
Praveen KVC
5 February 2010

Garbage Collection

2010-02-11T03:20:00.000-08:00

The lives of the heap-based objects are not coupled to the scope of the individual stack-based variables that reference them. When the garbage collector runs, it will remove all those objects from the heap that are no longer referenced. As soon as the garbage collector has freed up all the objects it can, it compacts the heap by moving all remaining objects to form one contiguous block of memory. when the objects are moved about, all the references to those objects need to be updated with the correct new addresses, but the garbage collector handles that too.

Generally, the garbage collector runs when the .NET runtime determines that a garbage collection is required. You can force the garbage collector to run at a certain point in your code by calling System.GC.Collect().The System.GC class is a .NET class that represents the garbage collector, and the Collect() method initiates a garbage collection. The GC class is intended for rare situations in which you know that it's a good time to call the garbage collector; for example, if you have just dereferenced a large number of objects in your code. However, the logic of the garbage collector does not guarantee that all unreferenced objects will be removed from the heap in a single garbage collection pass.

Regards,
Praveen KVC
4 February 2010

Memory Management for Reference Data Types

2010-02-11T03:11:00.000-08:00

Although the stack gives very high performance, it is not flexible enough to be used for all variables. The requirement that the lifetimes of variables must be nested is too restrictive for many purposes. Often, you will want to use a method to allocate memory to store some data and be able to keep that data available long after that method has exited. This possibility exists whenever storage space is requested with the new operator — as is the case for all reference types. That's where the managed heap comes in.

The managed heap (or heap for short) is just another area of memory from the process's available 4GB. The following code demonstrates how the heap works and how memory is allocated for reference data types:

void DoWork()
{
Customer arabel;
arabel = new Customer();
Customer mrJones = new Nevermore60Customer();
}

This code assumes the existence of two classes, Customer and Nevermore60Customer

First, you declare a Customer reference called arabel. The space for this will be allocated on the stack, but remember that this is only a reference, not an actual Customer object. The arabel reference takes up 4 bytes, enough space to hold the address at which a Customer object will be stored. (You need 4 bytes to represent a memory address as an integer value between 0 and 4GB.)

The next line

arabel = new Customer();
does several things. First, it allocates memory on the heap to store a Customer object (a real object, not just an address). Then it sets the value of the variable arabel to the address of the memory it has allocated to the new Customer object. (It also calls the appropriate Customer() constructor to initialize the fields in the class instance, but we won't worry about that here.)

The Customer instance is not placed on the stack — it is placed on the heap. In this example, you don't know precisely how many bytes a Customer object occupies, but assume for the sake of argument it is 32. These 32 bytes contain the instance fields of Customer as well as some information that .NET uses to identify and manage its class instances.

When a reference variable goes out of scope, it is removed from the stack, but the data for a referenced object is still sitting on the heap. The data will remain on the heap until either the program terminates, or the garbage collector removes it, which will only happen when it is no longer referenced by any variables.

Regards,
Praveen KVC
3 February 2010

Memory Management for Value Data Types

2010-02-11T03:07:00.000-08:00

Windows uses a system known as virtual addressing, in which the mapping from the memory address seen by your program to the actual location in hardware memory is entirely managed by Windows. The result of this is that each process on a 32-bit processor sees 4GB of available memory, irrespective of how much hardware memory you actually have in your computer (on 64-bit processors this number will be greater). This 4GB of memory contains everything that is part of the program, including the executable code, any DLLs loaded by the code, and the contents of all variables used when the program runs. This 4GB of memory is known as the virtual address space or virtual memory. For convenience in this chapter it is referred to simply as memory.

Somewhere inside a process's virtual memory is an area known as the stack. The stack stores value data types that are not members of objects. In addition, when you call a method, the stack is used to hold a copy of any parameters passed to the method. To understand how the stack works, you need to understand the importance of variable scope in C#. It is always the case that if a variable a goes into scope before variable b, then b will go out of scope first. Look at this code:

{
int a;
// do something
{
int b;
// do something else
}
}

First, a gets declared. Then, inside the inner code block, b gets declared. Then the inner code block terminates and b goes out of scope; then a goes out of scope. So, the lifetime of b is entirely contained within the lifetime of a. The idea that you always deallocate variables in the reverse order to how you allocate them is crucial to the way the stack works.

Regards,
Praveen KVC
2 February 2010

Virtual Methods

2010-01-31T20:59:00.000-08:00

By declaring a base class function as virtual, you allow the function to be overridden in any derived classes:

class MyBaseClass
{
public virtual string VirtualMethod()
{
return "This method is virtual and defined in MyBaseClass";
}
}

It is also permitted to declare a property as virtual. For a virtual or overridden property, the syntax is the same as for a non-virtual property, with the exception of the keyword virtual, which is added to the definition. The syntax looks like this:

public virtual string ForeName
{
get { return fName;}
set { fName = value;}
}
private string foreName;
For simplicity, the following discussion focuses mainly on methods, but it applies equally well to properties.

The concepts behind virtual functions in C# are identical to standard OOP concepts. You can override a virtual function in a derived class, and when the method is called, the appropriate method for the type of object is invoked. In C#, functions are not virtual by default, but (aside from constructors) can be explicitly declared as virtual. This follows the C++ methodology: for performance reasons, functions are not virtual unless indicated. In Java, by contrast, all functions are virtual. C# differs from C++ syntax, however, because it requires you to declare when a derived class's function overrides another function, using the override keyword:

class MyDerivedClass : MyBaseClass
{
public override string VirtualMethod()
{
return "This method is an override defined in MyDerivedClass";
}
}

This syntax for method overriding removes potential runtime bugs that can easily occur in C++, when a method signature in a derived class unintentionally differs slightly from the base version, resulting in the method failing to override the base version. In C# this is picked up as a compile-time error, because the compiler would see a function marked as override but no base method for it to override.

Neither member fields nor static functions can be declared as virtual. The concept simply wouldn't make sense for any class member other than an instance function member.

Regards,
Praveen KVC
1 February 2010

Implementation Inheritance

2010-01-31T20:57:00.000-08:00

If you want to declare that a class derives from another class, use the following syntax:

class MyDerivedClass : MyBaseClass
{
// functions and data members here
}

Note This syntax is very similar to C++ and Java syntax. However, C++ programmers, who will be used to the concepts of public and private inheritance, should note that C# does not support private inheritance, hence the absence of a public or private qualifier on the base class name. Supporting private inheritance would have complicated the language for very little gain. In practice, private inheritance is used extremely rarely in C++ anyway.

If a class (or a struct) also derives from interfaces, the list of base class and interfaces is separated by commas:

public class MyDerivedClass : MyBaseClass, IInterface1, IInterface2
{
// etc.

For a struct, the syntax is as follows:

public struct MyDerivedStruct : IInterface1, IInterface2
{
// etc.

If you do not specify a base class in a class definition, the C# compiler will assume that System.Object is the base class. Hence, the following two pieces of code yield the same result:

class MyClass : Object // derives from System.Object
{
// etc.
}

and

class MyClass // derives from System.Object
{
// etc.
}

For the sake of simplicity, the second form is more common.

Because C# supports the object keyword, which serves as a pseudonym for the System.Object class, you can also write:

class MyClass : object // derives from System.Object
{
// etc.
}

If you want to reference the Object class, use the object keyword, which is recognized by intelligent editors such as Visual Studio .NET and thus facilitates editing your code.

Regards,
Praveen KVC
31 January 2010

Type Declaration Characters

2010-01-29T22:20:00.000-08:00

Data Type Type-Declaration Character

Integer %

Long &

Single !

Double #

Currency @

String $

Regards,
Praveen KVC
30 January 2010

DETERMINING A DATA TYPE

2010-01-29T22:17:00.000-08:00

You can use the VBA TypeName function to determine the data type of a variable. This example displays the data type of MyVar at each step. You see that it starts out as a string, is then converted to a double, and finally ends up as a string again.

Sub VariantDemo2()
MyVar = "123"
MsgBox TypeName(MyVar)
MyVar = MyVar / 2
MsgBox TypeName(MyVar)
MyVar = "Answer: " & MyVar
MsgBox TypeName(MyVar)
MsgBox MyVar
End Sub

Thanks to VBA, the data type conversion of undeclared variables is automatic. This process might seem like an easy way out, but remember that you sacrifice speed and memory - and you run the risk of errors that you may not even know about.

Regards,
Praveen KVC
29 January 2010

Comments in VBA

2010-01-29T22:10:00.000-08:00

In VBA Comments can be written in two ways:

A comment can start with a single quote (')
eg: 'this is an example
or
It can start with (REM)
eg: REM this is another example

REM cannot come in between the line where as ' can start anywhere in the line.
REM is taken from BASIC Programming.

Also, there is no multiline commnet in VBA. For that we need to use the comment block on the edit toolbar of VBE.

Regards,
Praveen KVC
28 January 2010

PivotTable and PivotChart report specifications and limits

2010-01-26T17:12:00.000-08:00

Feature Maximum limit

PivotTable reports (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) on a sheet Limited by available memory
Unique items per field 1,048,576
Row (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) or column fields (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) in a PivotTable report Limited by available memory
Report filters in a PivotTable report 256 (may be limited by available memory)
Value fields in a PivotTable report 256
Calculated item (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) formulas in a PivotTable report Limited by available memory
Report filters in a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) 256 (may be limited by available memory)
Value fields in a PivotChart report 256
Calculated item formulas in a PivotChart report Limited by available memory
Length of the MDX name for a PivotTable item 32,767
Length for a relational PivotTable string 32,767

Regards,
Praveen KVC
27 January 2010

Charting specifications and limits

2010-01-26T17:09:00.000-08:00

Feature Maximum limit

Charts linked to a worksheet Limited by available memory
Worksheets referred to by a chart 255
Data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in one chart 255
Data points (data points: Individual values that are plotted in a chart. Related data points make up a data series. Data points are represented by bars, columns, lines, slices, dots, and other shapes. These shapes are called data markers.) in a data series for 2-D charts 32,000
Data points in a data series for 3-D charts 4,000
Data points for all data series in one chart 256,000

Regards,
Praveen KVC
26 January 2010

Calculation specifications and limits

2010-01-25T03:17:00.000-08:00

Feature Maximum limit

Number precision 15 digits
Smallest allowed negative number -2.2251E-308
Smallest allowed positive number 2.2251E-308
Largest allowed positive number 9.99999999999999E+307
Largest allowed negative number -9.99999999999999E+307
Largest allowed positive number via formula 1.7976931348623158e+308
Largest allowed negative number via formula -1.7976931348623158e+308
Length of formula contents 8,192 characters
Internal length of formula 16,384 bytes
Iterations 32,767
Worksheet arrays Limited by available memory
Selected ranges 2,048
Arguments in a function 255
Nested levels of functions 64
User defined function categories 255
Number of available worksheet functions 341
Size of the operand stack 1,024
Cross-worksheet dependency 64,000 worksheets that can refer to other sheets
Cross-worksheet array formula dependency Limited by available memory
Area dependency Limited by available memory
Area dependency per worksheet Limited by available memory
Dependency on a single cell 4 billion formulas that can depend on a single cell
Linked cell content length from closed workbooks 32,767
Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered 9999:59:59

Regards,
Praveen KVC
25 January 2010

Worksheet and workbook specifications and limits

2010-01-25T03:10:00.000-08:00

Feature Maximum limit

Open workbooks Limited by available memory and system resources
Worksheet size 1,048,576 rows by 16,384 columns
Column width 255 characters
Row height 409 points
Page breaks 1,026 horizontal and vertical
Total number of characters that a cell can contain 32,767 characters
Characters in a header or footer 255
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook 16 million colors (32 bit with full access to 24 bit color spectrum)
Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) Limited by available memory
Unique cell formats/cell styles 64,000
Fill styles 32
Line weight and styles 16
Unique font types 1,024 global fonts available for use; 512 per workbook
Number formats in a workbook Between 200 and 250, depending on the language version of Excel that you have installed
Names in a workbook Limited by available memory
Windows in a workbook Limited by available memory
Panes in a window 4
Linked sheets Limited by available memory
Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 64 in a single sort; unlimited when using sequential sorts
Undo levels 100
Fields in a data form 32
Workbook parameters 255 parameters per workbook
Filter drop-down lists 10,000

Regards,
Praveen KVC
24 January 2010

Moving your cursor

2010-01-23T02:45:00.000-08:00

Sometimes you need to move your cursor around your worksheet to re-position it before running the next step of a macro. The movement here uses the row, column position method.

Sub Down()
ActiveCell.Offset(1, 0).Select
End Sub
Sub up()
ActiveCell.Offset(-1, 0).Select
End Sub
Sub Right()
ActiveCell.Offset(0, 1).Select
End Sub
Sub Left()
ActiveCell.Offset(0, -1).Select
End Sub

Regards,
Praveen KVC
23 January 2010

Modeless Forms

2010-01-22T05:45:00.000-08:00

Sometimes you want to allow users to be able to switch between your form and your spreadsheet by clicking on either one. All you need to do is set the form property of Show Modal to False or you can try this. However this is only for Excel 2000 & above.

Sub myForm()
UserForm.show vbModeless
End Sub

Regards,
Praveen KVC
22 January 2010

Protecting all sheets

2010-01-21T08:50:00.000-08:00

To protect all the sheets this macro uses all the methods contained in this page (see counting sheets). The If, Then statement is also used here. This tests for a condition and if the condition is TRUE, then the macro continuous the next line of code. In this case it will END the macro. If the condition is NOT TRUE, then it will go to the following line which in this case is to select the next sheet. You will also notice the For, Next statement is also used. This acts as a counter to tell the macro how many loops to run. In this case if there are 3 sheets, the macro will run 3 times protecting all the 3 sheets.

Sub protectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet
For i = 1 To myCount
ActiveSheet.Protect
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Regards,
Praveen KVC
21 January 2010

Change column order in Pivot Table

2010-01-20T09:23:00.000-08:00

To change the column order in Pivot Table, We can right click on th ecolumn and say move right or move left. But, the easiest way to do it is to type the column names int he required positions.

For example, if ODI,T20,Test is the current order, to change it to ODI,Test,T20, we need to change the column name in T20 to Test.

This will automatically move the contents of the column also.

Regards,
Praveen KVC
20 January 2010

Fill Empty Cells in Pivot table

2010-01-20T09:20:00.000-08:00

To fill empty cels in Pivot Table with a desired value,
We need to go to pivot table options and choose -> For Empty Cells Show:

This will show all the empty cells in Pivot as the given value.

Regards,
Praveen KVC
19 January 2010

Saving a file

2010-01-18T10:06:00.000-08:00

There are times you may want a macro to save a file automatically after running a macro. The second macro will save the file with a name called "MyFile". You may specify the path if you need to.

Sub Save()
ActiveWorkbook.Save
End Sub
Sub SaveName()
ActiveWorkbook.SaveAs Filename:="C:\MyFile.xls"
End Sub

Regards,
Praveen KVC
18 January 2010

Add Worksheets to Excel

2010-01-16T21:03:00.000-08:00

Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise), name it "MySheet" and have it become the active sheet, you would use some code like shown below;

Sub AddWorksheet()
Worksheets.Add().Name = "MySheet"
End SubIf

we wanted to add a Worksheet as the last Worksheet and name it "MySheet" we would use;

Sub AddAsLastWorksheet()
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"
End Sub

The Add Method as it applies to the Worksheet Object also has a Before Variant as well as an After Variant. However, we can only nominate a Before or After Variant, or omit the Argument altogether. If we do omit the Before and After Variants Excel places the Worksheet after the current active Sheet.

To add, say, 4 Worksheets we could use the Count Variant;

Sub AddXWorksheets()
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4
End Sub

The only other Variant we can use if desired is the Type Variant. The Type specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet

Regards,
Praveen KVC
17 January 2010

AutoFilter Criteria in Excel VBA

2010-01-16T20:56:00.000-08:00

Ok, last page we looked at how we can set AutoFilters up within an Excel Workbook. In summary, rather than check if AutoFilters are already applied to a specified Worksheet with an IF Statement, then, if they were on and in use (filtered down) we would turn them off and apply to the needed range. If they weren't on then simply apply them to the needed range.

This however was a lot of superfluous code. The easiest and best way is as shown below;

Sub ApplyAutoFilters() With ActiveSheet .AutoFilterMode = False .Range("A1:D1").AutoFilter End With End SubIn the code above we turn off any existing AutoFilters and apply them to the range A1:D1 of the active worksheet.

FILTERING DOWN TO SHOW 1 MATCHING CRITERIA

Let's now look at how we can apply AutoFilters and show only matching criteria. In the examples below I have used a specified Worksheet by referencing its CodeName . It is also based on the data being in the range A1:D100 with A1:D1 being headings:

Name | Age | Date Joined | Department

Sub FilterTo1Criteria() With Sheet1 .AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:=35 End With End SubIn the example below we have filtered our table down to match 1 criteria (Criteria1) on our second heading (Age) to show only those who are 35. If we were to show all those that are 35 or older our Criteria1 would need to be like;

Criteria1:=">=35"

In other words, the criteria and any operators should be passed as text with an equal sign preceding the string.

We can have the filter show only blanks for the specified Field by using: Criteria1:="="

To show all non-blanks we would use: Criteria1:="<>"

XlAutoFilterOperator can be one of these constants

xlAnd default
xlBottom10Items
xlBottom10Percent
xlOr
xlTop10Items
xlTop10Percent

If we wanted to show only those in the Name field whose name Start s with a "D" we would use: Criteria1:="=D*"
To show all names that do not contain a letter "a" we would use: Criteria1:="<>*a*"

In short, the best way to obtain your needed criteria is to simply record a macro filtering your table down and then copy the Criteria1: and the optional Criteria2: code generated.

If desired, for whatever reason, we can have Excel hide the Filter arrow for Field2 (or any Field) by using an additional
argument after Criteria1. That is: ,VisibleDropDown:=False

FILTERING DOWN TO SHOW 2 MATCHING CRITERIA

Let's now expand on the above by filtering down to show 2 criteria.

Sub FilterTo2Criteria() With Sheet1 .AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:=">=35", _ Operator:=xlAnd, Criteria2:="<=45" End With End SubIn the above code we have chosen to show all whose age is between 35 and 45. It's important to note that for the Operator argument we have used xlAnd. If we had used the other choice (XlOr) our results would be that of our original table. That is, all records would show as all people would be either >=35 or <=45.

Sub FilterTo2Fields() With Sheet1 .AutoFilterMode = False With .Range("A1:D1") .AutoFilter .AutoFilter Field:=1, Criteria1:="Dave" .AutoFilter Field:=4, Criteria1:="Lab" End With End With End SubIn the code above we have shown all those with the name "Dave" whose department is "Lab". As you can see from the above code,
We can add more fields, but cannot exceed our total column count of headings. In this case we could use Field 1, 2, 3 and/or 4.

FILTERING DOWN TO SHOW WILDCARDS

The wildcard characters we can use in AutoFilter are the asterisk (*) to represent a string of characters and/or the question mark (?) to represent a single character.

However, what if we need to show data that actually houses the * or ? By the way, if at all possible these characters should not be used on their own.

Sub FilterToShowAsterisk() With Sheet1 .AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=1, Criteria1:="~*" End With End SubAs you can see from the above code, we have told Excel we actually want to filter by the asterisk and not have it seen as a wildcard. The same applies for the question mark. That is: Criteria1:="~?"

Regards,
Praveen KVC
16 January 2010

VBA & AutoFilters

2010-01-14T21:42:00.000-08:00

AutoFilter provides us with a MUCH faster alternative to loops of all kinds.
In the majority of cases it's faster and more efficient to use one of Excel's built in features as apposed to re-inventing the wheel with VBA code. This is why those that have learnt Excel from the ground-up know what native features Excel has to offer. While those only familiar with VB/VBA tend to be the ones who re-invent the wheel.

Ok, the first thing we need to know is how to apply AutoFilter to a range. When we do apply AutoFilter via VBA one SHOULD always turn the off any current filters and remove them completely. Why not check if the AutoFilter is already in place and go from there? The answer is simple, while we can determine if AutoFilter has/is on a specific Worksheet, we cannot guarantee (with extra checking) that it is in use on the range we need! For example, we could use the code below to check.

Sub CheckForAutoFilters()
If ActiveSheet.AutoFilterMode = True Then
MsgBox "They are visible"
Else
MsgBox "They are not visible"
End If
End Sub

From the code above we will know if AutoFilters are visible, but not necessarily in Filter mode (more on that soon). However, we cannot tell if the AutoFilterMode is applied to the correct range. Let's now see how we can determine if the AutoFilters and in use and are being used to filter down.

Sub CheckForAutoFilters2()
With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
MsgBox "They are visible and in use"
ElseIf .AutoFilterMode = True Then
MsgBox "They are visible but not in use"
Else MsgBox "They are not visible or in use"
End If
End With
End Sub

As you can see, we have used the FilterMode Property of the Worksheet to determine whether the AutoFilters are filtering data down. So, in summary, AutoFilterMode tells us if the AutoFilter arrows are visible and FilterMode tells us if they are in use. However, as I mentioned above this does not tell us which range has had AutoFilter applied. So, with this in mind, we are better off simply removing any existing Autofilter and then applying them to our required range. Here is how, assuming we want A1:D1 to have the AutoFilters.

Sub ApplyAutoFilters()
With ActiveSheet
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
End With
End Sub

Another advantage to applying AutoFilter is this manner is that no error occurs if AutoFilterMode is already false. By the way, we cannot use: AutoFilterMode = True to apply AutoFilters. To apply AutoFilter (at this time with no criteria) we would use Range("A1:D1").AutoFilter. If we are to first check the range that AutoFilter is applied to, we would use code like below;

Sub IsAutoFiltersOnRightRange()
With ActiveSheet If .AutoFilterMode = True Then
MsgBox .AutoFilter.Range.Address
Else MsgBox "AutoFilters are not on"
End If
End With
End Sub

In my mind though, this code is superfluous when compared with simply removing and applying AutoFilters. Let's now look at how we apply AutoFilter to a SINGLE cell in a range. If we had our table in the range A1:D200 on the Active sheet and we used the "ApplyAutoFilters" Procedure with .Range("A1").AutoFilter we would likely end up with AutoFilter applied to ALL contiguous headings across row 1. This due to the fact that Excel will detect the contiguous headings across row 1 and assume that we want all headings to have AutoFilters. We can force Excel to not do this by specifying a 2 row single column range. For example;

Sub ApplyAutoFiltersToOneCell()
With ActiveSheet
.AutoFilterMode = False
.Range("A1:A2").AutoFilter
End With
End Sub

Regards,
Praveen KVC
15 January 2010

Calling a Worksheet Function from Visual Basic

2010-01-14T03:33:00.000-08:00

In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object.

The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it's set to range A1:C10 on Sheet1. Another variable, answer, is assigned the result of applying the Min function to myRange. Finally, the value of answer is displayed in a message box.

Sub UseFunction()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer
End Sub
If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. For example, you can use the Match worksheet function to search a range of cells. In a worksheet cell, you would enter a formula such as =MATCH(9,A1:A10,0). However, in a Visual Basic procedure, you would specify a Range object to get the same result.

Sub FindFirst()
myVar = Application.WorksheetFunction _
.Match(9, Worksheets(1).Range("A1:A10"), 0)
MsgBox myVar
End Sub

Regards,
Praveen KVC
14 January 2010

Counting Rows & Columns & Sheets

2010-01-12T22:27:00.000-08:00

When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro will do the trick.

Sub Count()
mycount = Selection.Rows.Count 'Change Rows to Columns to count columns
MsgBox mycount
End Sub

The next macro counts the number of sheets instead. Refer to Protecting all sheets macro which uses this method.

Sub Count2()
mycount = Application.Sheets.Count
MsgBox mycount
End Sub

Regards,
Praveen KVC
13 January 2010

Auto Run

2010-01-12T22:25:00.000-08:00

Making your macros run automatically when opening your workbook. You can either use the Auto Open method or the Workbook Open method. These macros will display the message "Hello" when you open the workbook.

Sub Auto_Open()
Msgbox "Hello"
End Sub

This code would be located in the module. However if you use the second method, the code must be in the workbook (double click "This Workbook" in the explorer window). Click on the drop down list (that says General) and select Workbook. Click on the drop down list (that says declarations) and select Open.

Private Sub Workbook_Open()
Msgbox "Hello"
End Sub

Regards,
Praveen KVC
12 January 2010

Excel VLOOKUP and Index & Match

2010-01-11T09:27:00.000-08:00

One day, you have a situation where you have the employee name, but need the employee number. In the following image, you have a name in A10 and need to find the employee number in B10.

=INDEX(data range, row number, column number)
=MATCH(Value, Single-column data range, FALSE)

=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)

This fixes the vlookup's issue of travelling to the left.

Regards,
Praveen KVC
11 December 2009.

Goto a RANGE

2010-01-09T21:34:00.000-08:00

To specify a macro to go to a specific range you can use the Goto method. Here I have already named a range in my worksheet called "Sales". You may also use an alternative method ie the Range select method. Naming a range in excel is recommended rather than specifying an absolute cell reference.

Sub GoHere()
Application.Goto Reference:="Sales" OR Range("Sales").Select
End Sub

Regards,
Praveen KVC
10 January 2010

Flickering Screen

2010-01-09T02:02:00.001-08:00

Sometimes when you run a macro, the screen flickers a lot due to the screen updating itself. This slows the macro done especially when the macro has a lot of work to do. You need to include the statement as shown below.

Application.ScreenUpdating = False

You need to set the screen updating back to true at the end of the macro.

Regards,
Praveen KVC
9 January 2010

Generate Unique Random Numbers

2010-01-08T00:39:00.000-08:00

This UDF will generate x unique random numbers between any 2 numbers you specify.

Function RandLotto(Bottom As Integer, Top As Integer, _

Amount As Integer) As String

Dim iArr As Variant

Dim i As Integer

Dim r As Integer

Dim temp As Integer

Application.Volatile

ReDim iArr(Bottom To Top)

For i = Bottom To Top

iArr(i) = i

Next i

For i = Top To Bottom + 1 Step -1

r = Int(Rnd() * (i - Bottom + 1)) + Bottom

temp = iArr(r)

iArr(r) = iArr(i)

iArr(i) = temp

Next i

For i = Bottom To Bottom + Amount - 1

RandLotto = RandLotto & " " & iArr(i)

Next i

RandLotto = Trim(RandLotto)

End Function

=RandLotto(1,20,8)
This would produce 8 unique random numbers between 1 and 20

Regards,
Praveen KVC
8 January 2010

countif across multiple sheets

2010-01-06T22:21:00.000-08:00

Countif doesnt work across multiple sheets (3d way).
Example: countif(sheet1:sheet3!A1,">0") will result in error.

To fix this, List all the sheet names in a range, say H1:H3
Then write this formula =SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H3&"'!A1"),">0"))

This will work like a charm!!

Regards,
Praveen KVC
6 January 2010

To Capture Last modified date of the file

2010-01-06T13:26:00.000-08:00

Sub test()
Dim fso As Object, myDir As String, fn As String, myFile As String, myDate As Date, maxDate As Date
Set fso = CreateObject("Scripting.FileSystemObject")
myDir = "C:\temp"
fn = Dir(myDir & "\*.csv)
Do While fn <> ""
myDate = fso.GetFile(myDir & "\" & fn).DateLastModified
If maxDate < myDate Then
myFile = fn
maxDate = myDate
End If
fn = Dir()
Loop
MsgBox myDir & "\" & fn & " : " & maxDate
End Sub

Delete/ Remove duplicate rows in Excel

2010-01-04T21:31:00.000-08:00

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Regards,
Praveen KVC
5 January 2010

Pivot Table - Clear Old Items

2010-01-04T00:37:00.000-08:00

Old Items Remain in Pivot Field Dropdowns
The data in the pivot table source may change, and items that were previously in the source are no longer included. For example, some sales reps may leave the company, and the names of their replacements appear in the source table.
Even after you refresh the pivot table, the names of the old sales reps will appear, along with the new names. In the list at right, Cartier has replace Gill, but Gill still appears in the list.
This pivot table tutorial shows how you can clear the old items either manually or programmatically.

Manually Clear Old Items
To manually clear the old items from the list:
If you manually created any groups that include the old items, ungroup those items.
Drag the pivot field out of the pivot table.
On the Pivot toolbar, click the Refresh button
Drag the pivot field back to the pivot table

Change the Retain Items Setting in Excel 2007
To prevent old items from being retained in an Excel 2007 pivot table, you can change an option setting:
Right-click a cell in the pivot table
Click on PivotTable options
Click on the Data tab
In the Retain Items section, select None from the drop down list.
Click OK, then refresh the pivot table.

Programmatically Clear Old Items
In Excel 2002, and later versions, you can programmatically change the pivot table properties, to prevent missing items from appearing, or clear items that have appeared.

Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub

Regards,
Praveen KVC
4 January 2010

Remove hyperlinks from your work

2010-01-03T02:55:00.000-08:00

If Excel has already converted your written URL into a hyperlink, you can cancel it by right-clicking on the offending address and selecting Hyperlink, Remove Hyperlink from the menu that pops up.

OR:

Type the url, press Enter key, and then Ctrl + Z. The text will remain but without a hyperlink.

OR:

When Excel automatically creates the hyperlink, click the small star and select 'Stop automatically creating hyperlinks'

Regards,
Praveen KVC
3 January 2010

Enter repetitive data quickly

2010-01-01T21:25:00.000-08:00

You can use this technique to enter data into a series of noncontiguous/Contiguous 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/Contiguous selection.

Regards,
Praveen KVC
2 January 2010

Find the currently active cell

2010-01-01T21:21:00.000-08:00

If you’ve been scrolling around your spreadsheet and you lose your place, you can jump back to the currently active cell by pressing the [Ctrl] + [Backspace] keys.

Regards,
Praveen KVC
1 January 2010

The Camera Tool in Excel

2009-12-31T06:58:00.000-08:00

Camera: Takes a linked picture of the current selection and pastes it in a new location. The picture is linked by a formula that refers to the copied cells, so it is updated when the copied cells change. Click the Camera button to take the picture, and then click where you want to paste the upper-left corner of the selection.

The Camera Tool in Excel is a very useful tool when it comes to capturing data in Excel that you want to display in another area in Excel or another application. Using copy/paste doesn't always give you the results you want so try the camera tool.
First, add the camera tool to a toolbar in Excel :

1. Choose View...Toolbars, Customize
2. Click on the Commands tab
3. Select Tools from the Categories list and then scroll down the commands list until you located the Camera tool
4. Click and drag the camera onto any toolbar
5. Click on close

Using the Camera Tool

To make a linked Camera picture of a range:

* Select a range
* Press Camera Icon, a + cursor appears
* Move to destination location and click once - the selected range is copied
* Move, manipulate your live picture as desired

Since your picture is linked, any changes in the original cells will appear in your picture.

You can move it anywhere you want, it floats over the rows/columns.

FOR Excel 2007

Click office Button-->Excel Options -->Customize-->Commands not in ribbon--> Camera

Regards,
Praveen KVC
31 December 2009

How to Recover a Corrupt Excel File

2009-12-30T03:36:00.000-08:00

Microsoft has some great tips for recovering data from Excel files that won't open or are otherwise corrupt although they are sometimes a little difficult to try. See the tips section for them. You may want to try them first, or a combination with the list below.
Steps

1.Close down Excel, reopen it and try to open the file again.

2.If this doesn't work, restart the computer, open up Excel and try to open the file again.

3.If this doesn't work, delete the contents of your c:\windows\temp directory then reboot. Try again.

4.If this doesn't work, open Excel in Safe Mode. This will disable VBA and Add-ins. Click on Start then Run then enter (including the quotes and the /s extension): "C:\Program Files\Microsoft Office\Office\excel.exe" /s ["C:\Program Files\Microsoft Office\Office10\excel.exe" /s (for Excel 2002, Office XP, click cancel if the MS Office Installer starts, Excel in safe mode will still start)]. Try to open the file.

5.If this doesn't work, be sure to scan the file for viruses. Make sure your macro heuristics scanning option is turned on. Ask your IT administrator how to do this.

6.If you don't find a virus, find the file in Explorer, right click on it and rename it, replacing the .xls extension with the .doc extension. Try to open it as Word document.

7.If this doesn't work, try to determine if the file is unrecoverable on the disk. Open the file in Explorer and try to copy it to another location. If you can copy the file to another location skip to step 9, if not the actual sectors on the disk may be corrupted. If the data is valuable enough to you, there are several labs that can recover files off damaged disks, you can find a lab here: http://www.disasterrecoverygroup.com/। There are other such services too)। Damaged hard disk file recovery is not for the faint at heart! If you would like to try there are a bunch of free tools starting here:http://www।s2services.com/baddisk.htm. There are several programs you can buy, one for as little as 40$. A good list is here:http://www.webattack.com/shareware/system/swdatarecovery.shtml. The File Recovery program at the top of the list appears to be the best. For recovery from a bad floppy disk download or use a freeware program to recover the file from the floppy. Some freeware for this purpose can be found here:http://www.simtel.net/pub/pd/60018.html and here: http://www.s2services.com/cdzipandfloppyreapir.htm.

8.Try to open any damaged disk recovered file immediately, miracles do happen.

9.If the recovered file won't open or you could copy the file to another location, try to open the file in a more recent version of Excel. As the version numbers increase, their ability to recover corrupt files increases. If this doesn't work or such a version of Excel is not available, see if some other spreadsheet application is and try to open the file in that program.

10.A hopefully exhaustive list of Office Suites is available from Google at: http://directory.google.com/Top/Computers/Software/Office_Suites/. but here is one too: Microsoft Works Spreadsheet, Lotus 123, Quattro Pro, Star Office Calc, ThinkFree Office Calc, Ability Office Spreadsheet, Gobe Productive Spreadsheet, EI Office's Spreadsheet Module, Xoom Office Calc, Open Office Calc (part of the freeware Open Office Suite - similar to Star Office, free business use!) - available at: http://www.openoffice.org/dev_docs/source/1.0.1/index.html, 602 Tab (part of the freeware 602Pro PC Suite 2001, also free for business use!), available at: http://www.software602.com/products/pcs/download.html, Easy Spreadsheet (part of the freeware Easy Office 2001 - $39.95 for business use), available at: http://www.e-press.com/demo_downloads.html, standalone spreadsheets: http://directory.google.com/Top/Computers/Software/Spreadsheets.

11.If Microsoft can't help you (see the tips section) and you've exhausted all the free methods above for damaged disk recovered or copiable files there are a number of excellent commercial applications which will recover your files almost immediately. Try the free demos first (see "External Links" below).

12.If you are adventurous, go into Explorer, locate the file again, and rename it with a .txt extension instead of an .xls extension. Try opening the file, and if Windows says it can't open it Notepad but will open it in WordPad, agree. Be sure to turn on word wrapping in either program, in Notepad it's under the Format menu; in WordPad it's under the View menu, choose Options, choose the Text Tab, and choose Wrap to window. Next look for where your data stops, and is followed by a bunch of spacer characters they are little squares. These actually begin to tell Excel where cells, columns and rows are. When you find your last bit of data, delete the rest of the file. After this rename it back to an .xls extension, try opening the file in the oldest version of a spreadsheet you can find, or one of the freeware spreadsheets programs mentioned in Step 9. You may be able to recover your data in some semblance of a spreadsheet.

Regards,
Praveen KVC
December 30 2009

Hide the Ribbon in Office 2007

2009-12-30T03:34:00.000-08:00

Press Control + F1 and ribbon disappears and press again to reappear ribbon. You can also have it disappear so it will come back with a single click on any tab name. To do this, double click a tab name and the ribbon disappears. Single click a tab name and it reappears - click again on the tab or in the document, and it disappears. Repeat until you're tired of the magic! Double click or Control + F1 to go back to how it was.

Regards,
Praveen KVC
December 29 2009

Find First or Last Populated Column in a sheet

2009-12-27T22:21:00.000-08:00

Code:
Option Explicit

Sub Test_xlFirstLastCols()

' Target Application: MS Excel
' Demonstration: display first and last non-blank columns in the active sheet
' and one target sheet

Dim SheetName As String
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for the active sheet. Since activesheet is assumed if procs are called
' without a passed arguement, use that method here
'
MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
"First non-blank col = " & xlFirstCol() & vbCrLf & _
"Last non-blank col = " & xlLastCol(), vbInformation, _
"Active Sheet Demonstration"
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for "Sheet4". Since this is not the active sheet, the sheet must
' be defined via the passed arguement.
'
SheetName = "Sheet4"
MsgBox "Worksheet name = " & SheetName & vbCrLf & _
"First non-blank col = " & xlFirstCol(SheetName) & vbCrLf & _
"Last non-blank col = " & xlLastCol(SheetName), vbInformation, _
"Passed Sheet Name Demonstration"

End Sub

Function xlFirstCol(Optional WorksheetName As String) As Long

' finds the first populated col in a worksheet

If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlFirstCol = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
xlWhole, xlByColumns, xlNext).Column
If Err <> 0 Then xlFirstCol = 0
End With

End Function

Function xlLastCol(Optional WorksheetName As String) As Long

' finds the last populated col in a worksheet

If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With

End Function

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

In the attached example, there are 5 sheets with varying amounts of data.
Select any of these sheets (or create another sheet)
Go to Tools Macro Macros (or Alt+F8) and double-click on Test_xlFirstLastCols
The results for the active sheet and Sheet4 will be displayed.
Each sheet has a text box with info on what you should expect from the demo. You may also click on any of these text boxes to execute the demo.
(N.B. if you select Sheet4 as the active sheet the same result will be displayed twice.
xlFirstCol and xlLastCol are functions to be called by a higher level procedure, further testing will depend on how the functions are used).

Regards,
Praveen KVC
December 28 2009

Other Shortcut Keys in Excel

2009-12-27T03:52:00.000-08:00

Key Description

ARROW KEYS Move one cell up, down, left, or right in a worksheet.
CTRL+ARROW KEY moves to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells or datasheet borders.) in a worksheet.

SHIFT+ARROW KEY extends the selection of cells by one cell.

CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell.

LEFT ARROW or RIGHT ARROW selects the menu to the left or right when a menu is visible. When a submenu is open, these arrow keys switch between the main menu and the submenu.

DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open.

In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.

ALT+DOWN ARROW opens a selected drop-down list.

BACKSPACE Deletes one character to the left in the Formula Bar.
Also clears the content of the active cell.

DELETE Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.
In cell editing mode, it deletes the character to the right of the insertion point.

END Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.
Also selects the last command on the menu when a menu or submenu is visible.

CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column.

CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner).

ENTER Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).
In a data form, it moves to the first field in the next record.

Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.

In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).

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

CTRL+ENTER fills the selected cell range with the current entry.

SHIFT+ENTER completes a cell entry and selects the cell above.

ESC Cancels an entry in the cell or Formula Bar.
It also closes an open menu or submenu, dialog box, or message window.

HOME Moves to the beginning of a row in a worksheet.
Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.

Selects the first command on the menu when a menu or submenu is visible.

CTRL+HOME moves to the beginning of a worksheet.

CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.

PAGE DOWN Moves one screen down in a worksheet.
ALT+PAGE DOWN moves one screen to the right in a worksheet.

CTRL+PAGE DOWN moves to the next sheet in a workbook.

CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.

PAGE UP Moves one screen up in a worksheet.
ALT+PAGE UP moves one screen to the left in a worksheet.

CTRL+PAGE UP moves to the previous sheet in a workbook.

CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.

SPACEBAR In a dialog box, performs the action for the selected button, or selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.

SHIFT+SPACEBAR selects an entire row in a worksheet.

CTRL+SHIFT+SPACEBAR selects the entire worksheet.

If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet.
When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.
ALT+SPACEBAR displays the Control menu for the Excel window.

TAB Moves one cell to the right in a worksheet.
Moves between unlocked cells in a protected worksheet.

Moves to the next option or option group in a dialog box.

SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.

CTRL+TAB switches to the next tab in dialog box.

CTRL+SHIFT+TAB switches to the previous tab in a dialog box.

Regards,
Praveen KVC
December 27 2009

Function key shortcuts in Excel

2009-12-26T06:35:00.000-08:00

Key Description
F1 Displays the Help task pane.
CTRL+F1 closes and reopens the current task pane.

ALT+F1 creates a chart of the data in the current range.

ALT+SHIFT+F1 inserts a new worksheet.

F2 Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.
SHIFT+F2 edits a cell comment.

F3 Pastes a defined name into a formula.
SHIFT+F3 displays the Insert Function dialog box.

F4 Repeats the last command or action, if possible.
CTRL+F4 closes the selected workbook window.

F5 Displays the Go To dialog box.
CTRL+F5 restores the window size of the selected workbook window.

F6 Switches to the next pane in a worksheet that has been split (Window menu, Split command).
SHIFT+F6 switches to the previous pane in a worksheet that has been split.

CTRL+F6 switches to the next workbook window when more than one workbook window is open.

Note When the task pane is visible, F6 and SHIFT+F6 include that pane when switching between panes.

F7 Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ESC.

F8 Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection.
SHIFT+F8 enables you to add a non-adjacent cell or range to a selection of cells by using the arrow keys.

CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.

ALT+F8 displays the Macro dialog box to run, edit, or delete a macro.

F9 Calculates all worksheets in all open workbooks.
F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value.

SHIFT+F9 calculates the active worksheet.

CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.

CTRL+F9 minimizes a workbook window to an icon.

F10 Selects the menu bar or closes an open menu and submenu at the same time.
SHIFT+F10 displays the shortcut menu for a selected item.

ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.

CTRL+F10 maximizes or restores the selected workbook window.

F11 Creates a chart of the data in the current range.
SHIFT+F11 inserts a new worksheet.

ALT+F11 opens the Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).

ALT+SHIFT+F11 opens the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any script code.

F12 Displays the Save As dialog box.

Regards,
Praveen KVC
December 26 2009

Find the Last used row in a particular sheet

2009-12-24T21:36:00.000-08:00

There are several methods to accomplish this, some more exact than others, but they can all serve your needs.

One common method

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.

Another method to find the last used row in a particular column is:

LastRowColA = Range("A65536").End(xlUp).Row

but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.

A couple extra methods are more reliable.

LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
or
LastRow = ActiveSheet.UsedRange.Rows.Count

This methods can be used on any sheet, not just the active sheet.

Regards,
Praveen KVC
December 25 2009

CTRL combination shortcut keys in Excel

2009-12-23T22:38:00.000-08:00

Key Description
CTRL+( Unhides any hidden rows within the selection.
CTRL+) Unhides any hidden columns within the selection.
CTRL+& Applies the outline border to the selected cells.
CTRL+_ Removes the outline border from the selected cells.
CTRL+~ Applies the General number format.
CTRL+$ Applies the Currency format with two decimal places (negative numbers in parentheses).
CTRL+% Applies the Percentage format with no decimal places.
CTRL+^ Applies the Exponential number format with two decimal places.
CTRL+# Applies the Date format with the day, month, and year.
CTRL+@ Applies the Time format with the hour and minute, and AM or PM.
CTRL+! Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
CTRL+- Displays the Delete dialog box to delete the selected cells.
CTRL+* Selects the current region around the active cell (the data area enclosed by blank rows and blank columns).
In a PivotTable, it selects the entire PivotTable report.

CTRL+: Enters the current time.
CTRL+; Enters the current date.
CTRL+` Alternates between displaying cell values and displaying formulas in the worksheet.
CTRL+' Copies a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+" Copies the value from the cell above the active cell into the cell or the Formula Bar.
CTRL++ Displays the Insert dialog box to insert blank cells.
CTRL+1 Displays the Format Cells dialog box.
CTRL+2 Applies or removes bold formatting.
CTRL+3 Applies or removes italic formatting.
CTRL+4 Applies or removes underlining.
CTRL+5 Applies or removes strikethrough.
CTRL+6 Alternates between hiding objects, displaying objects, and displaying placeholders for objects.
CTRL+7 Displays or hides the Standard toolbar.
CTRL+8 Displays or hides the outline symbols.
CTRL+9 Hides the selected rows.
CTRL+0 Hides the selected columns.
CTRL+A Selects the entire worksheet.
If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.

When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog box.

CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.

CTRL+B Applies or removes bold formatting.
CTRL+C Copies the selected cells.
CTRL+C followed by another CTRL+C displays the Microsoft Office Clipboard.

CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F Displays the Find dialog box.
SHIFT+F5 also displays this dialog box, while SHIFT+F4 repeats the last Find action.

CTRL+G Displays the Go To dialog box.
F5 also displays this dialog box.

CTRL+H Displays the Find and Replace dialog box.
CTRL+I Applies or removes italic formatting.
CTRL+K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
CTRL+L Displays the Create List dialog box.
CTRL+N Creates a new, blank file.
CTRL+O Displays the Open dialog box to open or find a file.
CTRL+SHIFT+O selects all cells that contain comments.

CTRL+P Displays the Print dialog box.
CTRL+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+S Saves the active file with its current file name, location, and file format.
CTRL+U Applies or removes underlining.
CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you cut or copied an object, text, or cell contents.
CTRL+W Closes the selected workbook window.
CTRL+X Cuts the selected cells.
CTRL+Y Repeats the last command or action, if possible.
CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry you typed.
CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.

Regards,
Praveen KVC
December 24 2009

convert a numeric value into English words in Excel

2009-12-22T21:29:00.000-08:00

# Start Microsoft Excel.
# Press ALT+F11 to start the Visual Basic Editor.
# On the Insert menu, click Module.
# Type the following code into the module sheet.

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

Regards,
Praveen KVC
December 23 2009

Hide Cell Contents in Excel

2009-12-21T23:27:00.000-08:00

Start Microsoft Excel and open an existing workbook that contains cells you would like to hide the contents of, or start a new workbook and enter some content into a cell that you would like to hide.

Select the cells that contain data you would like to hide. You can select cells by clicking on them and using the SHIFT or CTRL keys on your keyboard to select additional cells.

Choose the "Format" menu and click "Cells" to open the "Format Cells" dialog box.

Click on the "Number" tab in the "Format Cells" dialog box to display the formats that can be applied to Excel cells.

Select "Custom" from the "Category" text box so you can type your own custom formatting to hide your selected cells.

Type 3 semicolons (;;;) in the "Type" text box to tell Excel that you want to hide the cells.

Click the "OK" button to close the "Format Cells" dialog box and hide the selected cells.

Regards,
Praveen KVC
December 22 2009

Lock and Protect Cells Containing Formulas

2009-12-20T20:56:00.000-08:00

This is taken from the Excel Hacks by Orielly. Very useful one to protect only few cells in a worksheet.

When we create a spreadsheet, most of us need to use formulas of some sort. Sometimes, however, you might not want other users to tamper/delete/overtype any formulas you included on your spreadsheet. The easiest and most common way of barring people from playing with your formulas is to protect your worksheet. However, protecting your worksheet doesn't just prevent users from tampering with your formulas, it also stops users from entering anything at all. Sometimes you do not want to go this far.

By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected.

Select all cells on your worksheet, either by pressing Ctrl/&command;-A or by clicking the gray square at the intersecting point of column A and row 1. Then select Format → Cells → Protection and uncheck the Locked checkbox to remove the tick. Click OK.

Now select any single cell, select Edit → Go To... (Ctrl-G or F5), and click Special. You'll see a dialog box such as that in .

Select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK. With only the formula cells selected, select Format → Cells → Protection and check the Locked checkbox to insert a tick. Select OK. Now select Tools → Protection → Protect Worksheet to protect your worksheet and apply a password if required.

The preceding method certainly saves a lot of time and eliminates possible errors locating formulas so that you can protect them. Unfortunately, it can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell. You can overcome this problem in two ways.

The first approach doesn't use worksheet protection at all, and uses data validation instead.

WARNING

Data validation is far from bulletproof when it comes to preventing users from entering nonvalidated data into cells. Users can still paste into a validated cell any data they want and, in doing so, remove the validation from that cell unless the copied cell also contains data validation, in which case this validation would override the original validation.

To see what we mean, select any single cell, select Edit → Go To... (Ctrl-G or F5), and click Special. Now select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK.

With only the Formula cells selected, select the Data → Validation → Settings page tab, select Custom from the Allow: box, and in the Formula box, enter ="", as shown in . Click OK.

This method will prevent a user from accidentally overtyping into any formula cells — although, as stressed in the earlier warning, it is not a fully secure method and should be used only for accidental overtyping, etc. However, the big advantage to using this method is that all of Excel's features are still usable on the worksheet.

The last method also will enable you to use all of Excel's features, but only when you are in a cell that is not locked. To start, ensure that only the cells you want protected are locked and that all other cells are unlocked. Right-click the Sheet Name tab, select View Code from the pop-up menu, and enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect Password:="Secret"
Else
Me.Unprotect Password:="Secret"
End If
End Sub

If no password is used, omit Password:="Secret". If a password is used, change the word Secret to your password. Press Alt/&command;-Q or click the X in the top righthand corner to get back to Excel and save your workbook. Now, each time you select a cell that is locked, your worksheet will automatically protect itself. The moment you select any cell that is not locked, your worksheet will unprotect itself.

WARNING

This hack doesn't work perfectly, though it usually works well enough. The keyword used in the code, Target, will refer only to the cell that is active at the time of selection. For this reason, it is important to note that if a user selects a range of cells (with the active cell being an unlocked cell), it is possible for him to delete the entire selection because the target cell is unlocked and, therefore, the worksheet automatically will unprotect itself.

Regards,
Praveen KVC
December 21 2009

Password Breaker for Excel sheets

2009-12-20T01:55:00.000-08:00

I Got this code from Sharat in a Excel Discussion Forum.

Sub Button2_Click()

' Breaks worksheet and workbook structure passwords.
' probably originator of base code algorithm modified for coverage
' of workbook structure / windows passwords and for multiple passwords
' Reveals hashed passwords NOT original passwords
Const DBLSPACE As String = vbNewLine & vbNewLine
Const AUTHORS As String = DBLSPACE & vbNewLine & _
"Adapted from Bob McCormick base code by" & _
"Norman Harker and JE McGimpsey"
Const HEADER As String = "AllInternalPasswords User Message"
Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"
Const REPBACK As String = DBLSPACE & "Please report failure " & _
"to the microsoft.public.excel.programming newsgroup."
Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _
"now be free of all password protection, so make sure you:" & _
DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _
DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _
DBLSPACE & "Also, remember that the password was " & _
"put there for a reason. Don't stuff up crucial formulas " & _
"or data." & DBLSPACE & "Access and use of some data " & _
"may be an offense. If in doubt, don't."
Const MSGNOPWORDS1 As String = "There were no passwords on " & _
"sheets, or workbook structure or windows." & AUTHORS & VERSION
Const MSGNOPWORDS2 As String = "There was no protection to " & _
"workbook structure or windows." & DBLSPACE & _
"Proceeding to unprotect sheets." & AUTHORS & VERSION
Const MSGTAKETIME As String = "After pressing OK button this " & _
"will take some time." & DBLSPACE & "Amount of time " & _
"depends on how many different passwords, the " & _
"passwords, and your computer's specification." & DBLSPACE & _
"Just be patient! Make me a coffee!" & AUTHORS & VERSION
Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _
"Structure or Windows Password set." & DBLSPACE & _
"The password found was: " & DBLSPACE & "$$" & DBLSPACE & _
"Note it down for potential future use in other workbooks by" & _
"the same person who set this password." & DBLSPACE & _
"Now to check and clear other passwords." & AUTHORS & VERSION
Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _
"password set." & DBLSPACE & "The password found was: " & _
DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _
"future use in other workbooks by same person who " & _
"set this password." & DBLSPACE & "Now to check and clear " & _
"other passwords." & AUTHORS & VERSION
Const MSGONLYONE As String = "Only structure / windows " & _
"protected with the password that was just found." & _
ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean

Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation, HEADER
Else
On Error Resume Next
Do 'dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
With ActiveWorkbook
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND1, _
"$$", PWord1), vbInformation, HEADER
Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND2, _
"$$", PWord1), vbInformation, HEADER
'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub

Regards,
Praveen KVC
December 20 2009