Thursday, July 29, 2010
Thursday, February 11, 2010
Freeing Unmanaged Resources
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
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
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
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
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
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
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
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
Sunday, January 31, 2010
Virtual Methods
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
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
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
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
Friday, January 29, 2010
Type Declaration Characters
Data Type Type-Declaration Character
Integer %
Long &
Single !
Double #
Currency @
String $
Regards,
Praveen KVC
30 January 2010
Integer %
Long &
Single !
Double #
Currency @
String $
Regards,
Praveen KVC
30 January 2010
DETERMINING A DATA TYPE
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
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
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
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
Tuesday, January 26, 2010
PivotTable and PivotChart report specifications and limits
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
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
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
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
Monday, January 25, 2010
Calculation specifications and limits
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
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
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
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
Saturday, January 23, 2010
Moving your cursor
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
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
Friday, January 22, 2010
Modeless Forms
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
Sub myForm()
UserForm.show vbModeless
End Sub
Regards,
Praveen KVC
22 January 2010
Thursday, January 21, 2010
Protecting all sheets
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
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
Wednesday, January 20, 2010
Change column order in Pivot Table
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
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
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
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
Monday, January 18, 2010
Saving a file
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
Sub Save()
ActiveWorkbook.Save
End Sub
Sub SaveName()
ActiveWorkbook.SaveAs Filename:="C:\MyFile.xls"
End Sub
Regards,
Praveen KVC
18 January 2010
Saturday, January 16, 2010
Add Worksheets to Excel
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
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
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
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
Thursday, January 14, 2010
VBA & AutoFilters
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
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
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
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
Tuesday, January 12, 2010
Counting Rows & Columns & Sheets
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
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
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
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
Monday, January 11, 2010
Excel VLOOKUP and Index & Match
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.
=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.
Saturday, January 9, 2010
Goto a RANGE
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
Sub GoHere()
Application.Goto Reference:="Sales" OR Range("Sales").Select
End Sub
Regards,
Praveen KVC
10 January 2010
Flickering Screen
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
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
Friday, January 8, 2010
Generate Unique Random Numbers
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
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
Wednesday, January 6, 2010
countif across multiple sheets
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
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
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
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
Monday, January 4, 2010
Delete/ Remove duplicate rows in Excel
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
'
' 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
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
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
Sunday, January 3, 2010
Remove hyperlinks from your work
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
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
Friday, January 1, 2010
Enter repetitive data quickly
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
Regards,
Praveen KVC
2 January 2010
Find the currently active cell
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
Regards,
Praveen KVC
1 January 2010
Subscribe to:
Posts (Atom)