Select Case is similar to if....Then .....Else ,except that you can choose from several condition values.
The general form is
Select Case expression
Case Value1
one or more VB statements for action1
Case Value2
one or more VB statements for action2
.....
.....
Case Valuem
one or more VB statements for actionm
[case else
one or more statements for action otherwise]
End Select
You can have as many case lines as you like and you can have more than one value on each line. You can even have a range of values by using To.
Example:
In the following example, the variable n takes as its value the content of the active cell. Control the passes to the corresponding Case statement.
Note the use of To in one of the case statements.
n = Activecell
select case n
case 12
statementA
case 24, 36
statementB
case 48 To 96
statementC
....
....
Case Else
statementD
end select
Else statement is a replacement of if statement when we need to be right more if statement.....
Thursday, December 17, 2009
IF statement in VBA
If statement is basically a single condition base statement. If statement is basically known as
If....then statement
Syntax:
If condition Then statement1 [Else statement2]
If the condition is satisfied (true then statement1 is carried out otherwise control passes to statement2.
Usually you will need to use the block statement. If.....then ....else which does not restrict you to one line of code. This has the form.
Example:
This macro looks at the value in A1. If it is 100 it enters the text Full marks in B1. If the value in A1 is not 100, the macro enters No in B1.
Sub fullmarks()
sheets("sheet1").select
cells(1,1).select
If activecell = 100 Then
cells(1,2).value = "No"
End if
end sub
Note: You could omit the line Cells(1,1).Select and change the following line to if cells(1,1) = 100 then.
If....then statement
Syntax:
If condition Then statement1 [Else statement2]
If the condition is satisfied (true then statement1 is carried out otherwise control passes to statement2.
Usually you will need to use the block statement. If.....then ....else which does not restrict you to one line of code. This has the form.
Example:
This macro looks at the value in A1. If it is 100 it enters the text Full marks in B1. If the value in A1 is not 100, the macro enters No in B1.
Sub fullmarks()
sheets("sheet1").select
cells(1,1).select
If activecell = 100 Then
cells(1,2).value = "No"
End if
end sub
Note: You could omit the line Cells(1,1).Select and change the following line to if cells(1,1) = 100 then.
Monday, November 16, 2009
Writing your own macros
When you recorded a macro, Excel created a module, added it to your workbook and wrote your recorded actions in a procedure belonging to that module.
When you want to write your own code in a new workbook you have to add a module to the workbook. The interface for macro development is called the Visual Basic Integrated Development Environment (IDE). Macro modules are displayed in the IDE instead of as a sheet in a workbook (as in versions before Excel 97).
How to Write Macro
Open a New Workbook.
For Run your macro goto Run menu in Microsoft Visual Basic Window or press F5
When you want to write your own code in a new workbook you have to add a module to the workbook. The interface for macro development is called the Visual Basic Integrated Development Environment (IDE). Macro modules are displayed in the IDE instead of as a sheet in a workbook (as in versions before Excel 97).
How to Write Macro
Open a New Workbook.
- Click on the New button on the toolbar (or select New from the file menu and click OK)
- Then in the new workbook from the Tools menu, select Macro and then Visual Basic Editor.
- From the Insert menu in the Microsoft Visual Basic window. select Module.
- You can change the name of this module. In the Properties window, beside(Name). select the name Module1, and change it to Experimenting.
- Now Type sub myfirsttest and press Enter.. Note how the () and End sub are filled in automatically.
- Now type step by step instruction between the sub and end sub.
Sub is called procedure you also can use function for it but both are use for different purposes .
If you have a little bit of knowledge about Visual Basic it will help you on understanding and to help VBA code.
For Run your macro goto Run menu in Microsoft Visual Basic Window or press F5
Sunday, November 15, 2009
Simple Input and Output with VBA
You already know how to get input from the user through the use of the Value property of a spreadsheet cell. Apart from that, you can also generate output for the user through the spreadsheet. However there may be times when you want something more dynamic and dramatic way to interact with the user than using a spreadsheet cell. The most common method for gathering input from the user and sending output back is the InputBox() and MsgBox() functions.
Inputbox
The inputbox function is used for prompt and take user response.It uses for take the user response before program execute.
Figure below shows the dialog box.
Now the how you can display a box like this which can get the user input
So following is the code
Sub inputboxexample()
dim myname as string
myname = inputbox("Enter Your Name")
End Sub
Syntax for Inputbox is following
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
Now let suppose we want to take a user input by the help of input box and want to print it on active workbook sheet1 in A1 cell
So the code is following.
sub inputboxexample
dim myname as string
myname = inputbox("Enter Your Name","Experiment")
activeworkbook.sheet1.cells(1,1).value = myname
End Sub
Inputbox is help a programmer in many things it can be use for design a beautiful program. And show it drammatically.
Msgbox()
So friend when a user think about a good program. He only think about its dramatically look. Means how the program took input from user. and how it shows the result output.
So for output we can use the msgbox().
For the following output we must be write a code and the code is following
Sub msgboxexample()
Dim myname As String
myname = InputBox("Enter Your Name")
MsgBox "So Your Name is " & myname
End Sub
and when the program prompt for Enter Your Name, write RED DEVIL on the box and press enter.
The syntax of the msgbox()function is following
Msgbox(Prompt, [Button As vbMsgBoxStyle = vbOkOnly],[Title],[Help File],[Context]) As vbmsgboxresult
So friend now you can use inputbox and msgbox function for give your program a drammatically look.
Remember one thing "imagination is more important than knowldge"
Inputbox
The inputbox function is used for prompt and take user response.It uses for take the user response before program execute.
Figure below shows the dialog box.
Now the how you can display a box like this which can get the user input
So following is the code
Sub inputboxexample()
dim myname as string
myname = inputbox("Enter Your Name")
End Sub
Syntax for Inputbox is following
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
Now let suppose we want to take a user input by the help of input box and want to print it on active workbook sheet1 in A1 cell
So the code is following.
sub inputboxexample
dim myname as string
myname = inputbox("Enter Your Name","Experiment")
activeworkbook.sheet1.cells(1,1).value = myname
End Sub
Inputbox is help a programmer in many things it can be use for design a beautiful program. And show it drammatically.
Msgbox()
So friend when a user think about a good program. He only think about its dramatically look. Means how the program took input from user. and how it shows the result output.
So for output we can use the msgbox().
For the following output we must be write a code and the code is following
Sub msgboxexample()
Dim myname As String
myname = InputBox("Enter Your Name")
MsgBox "So Your Name is " & myname
End Sub
and when the program prompt for Enter Your Name, write RED DEVIL on the box and press enter.
The syntax of the msgbox()function is following
Msgbox(Prompt, [Button As vbMsgBoxStyle = vbOkOnly],[Title],[Help File],[Context]) As vbmsgboxresult
So friend now you can use inputbox and msgbox function for give your program a drammatically look.
Remember one thing "imagination is more important than knowldge"
VBA grammar.......(Excel Macro) Part II
Today we are going to discuss about few more important things............
Variables
Just as in other programming languages. You can use variables. You do not have to declare variables. Visual Basic will automatically create storage for a variable the first time you use it.
Automatically created variables are of type Variant and can contain any type of data---strings, numbers, boolen values, errors, arrays or objects.
For example, the following statement assigns the value 34 to the variable x.
x = 34
Now you may be ask when with out declaration we can use a variable the what is the need for declare it.
So the answer for safety reason we declare variables and its data types.
Declaration habits of variables is help us when we write a long program.
Using Dim
Declaring a variable is to tell the computer to reserve space in memory for later use. As a result your VBA code run much more efficiently. To declare a variable use a Dim (short for Dimension) statement.
Syntax:
Dim var_name as data_type
Data Types
When a data type is declared it means that you have define the kind of value that may be stored within the memory allocated for a variable.
There are many types of data types. which are following
Common Math Operator Used in VBA
Addition +
Subtraction -
Multiplication *
Division /
Exponential ^
Few Examples
Sub example()
Dim myint as integer
myint = 5
end sub
sub example()
Dim marks, c,d
Set marks = Range("A1:B10")
d=0
For Each c in marks
if c.value < 40 then
d = d+1
end if
Next c
end sub
Constants
Values that don't change should be set up as constants rather than variables. Tis prevents them being changed by accident.
The line Const pi as double = 3.14159
will create the constant pi that can then be used in an expression such as
rtangle = pi/2
Arrays
An array is a collection of variables that have a common name. For example, we refer a specific variable in the array by using the array name and an index number.
Arrays contain a sequence of variables. Each one is called an element of the array and is identified by an index number.
Dim can be used to declare an array without giving it any values.
a) Declaring arrays
Before you can use an array, you must declare it like a variable in VBA with a Dim or a Public statement. By default, VBA takes 0 as the lower index. You need to specify the number of elements in the array by specifying the first index number and the last index number. The following example shows how to declare the number of days in a week.
Dim NumDays(1 to 7) As Integer
When you declare an array, you can choose to specify only the upper index.
As VBA assumes that 0 is the lower index, the following code both declare the same 100 element array:
Dim MyArray(0 to 99) As Integer
Dim MyArray(99) As Integer
If you want VBA to assume that 1 is the lower index for your arrays, your need to add the following code in the Declarations section of your module:
Option Base 1
We assume the the lower index is 1 from now. The array code above, will now represent a 99 element array
b) Assigning values to elements in an array
Suppose we have an one-dimensional array called MyArray and we wish to assign the value 5 to the 3rd index slot, use the following
MyArray(3) = 5
c) Multidimensional arrays
We have look at one-dimensional arrays. Incredibly, VBA allow you to have as many as 60 dimensions. The following example declares a 50-integer array with two dimensions:
Dim MyArray(1 to 5, 1 to 5) As Integer
You can think of this array as occupying a 5-x-5 matrix. If we want to assign the value 5 to the entry in the 2nd row and 5th column of the array, use
MyArray(2, 5) = 5
In other word, the integer value 5 is stored in (row 2, column 5) of the matrix MyArray
For a three-dimensional array, think of it as a cube. Visualizing an array of more than three dimensions is more difficult. Well you don’t worry about the fourth dimension and beyond as I bet you’ll never use it.
d) Dynamic arrays
So far, we have been declaring arrays with a fixed number of elements. If you wish to declare an array without specifying its size, use
Dim MyArray() As Integer
This is how to create dynamic arrays. Notice a dynamic array has a blank set of parentheses:
To resize the array, you must use the ReDim statement to tell VBA how many elements the array has. Most of the time, the number of elements in the array is determined while your code is running. The ReDim statement can be used any number of times, to change the array’s size as often as you need.
For example if you need to resize MyArray to a 10 x 10 matrix then you
ReDim MyArray(10,10) As Integer
Now you have change the number of elements in a dynamic array. When you redimension an array by using ReDim, you clear all the values currently stored in the array elements.
Using Set
Although Most methods return values some. You cannot assign an object to a variable using an equals sign. Instead, you should use the Set statement, for example,
Set rangeoffset = Range("C1:C5").offset(1,1)
Variables
Just as in other programming languages. You can use variables. You do not have to declare variables. Visual Basic will automatically create storage for a variable the first time you use it.
Automatically created variables are of type Variant and can contain any type of data---strings, numbers, boolen values, errors, arrays or objects.
For example, the following statement assigns the value 34 to the variable x.
x = 34
Now you may be ask when with out declaration we can use a variable the what is the need for declare it.
So the answer for safety reason we declare variables and its data types.
Declaration habits of variables is help us when we write a long program.
Using Dim
Declaring a variable is to tell the computer to reserve space in memory for later use. As a result your VBA code run much more efficiently. To declare a variable use a Dim (short for Dimension) statement.
Syntax:
Dim var_name as data_type
Data Types
When a data type is declared it means that you have define the kind of value that may be stored within the memory allocated for a variable.
There are many types of data types. which are following
Common Math Operator Used in VBA
Addition +
Subtraction -
Multiplication *
Division /
Exponential ^
Few Examples
Sub example()
Dim myint as integer
myint = 5
end sub
sub example()
Dim marks, c,d
Set marks = Range("A1:B10")
d=0
For Each c in marks
if c.value < 40 then
d = d+1
end if
Next c
end sub
Constants
Values that don't change should be set up as constants rather than variables. Tis prevents them being changed by accident.
The line Const pi as double = 3.14159
will create the constant pi that can then be used in an expression such as
rtangle = pi/2
Arrays
An array is a collection of variables that have a common name. For example, we refer a specific variable in the array by using the array name and an index number.
Arrays contain a sequence of variables. Each one is called an element of the array and is identified by an index number.
Dim can be used to declare an array without giving it any values.
a) Declaring arrays
Before you can use an array, you must declare it like a variable in VBA with a Dim or a Public statement. By default, VBA takes 0 as the lower index. You need to specify the number of elements in the array by specifying the first index number and the last index number. The following example shows how to declare the number of days in a week.
Dim NumDays(1 to 7) As Integer
When you declare an array, you can choose to specify only the upper index.
As VBA assumes that 0 is the lower index, the following code both declare the same 100 element array:
Dim MyArray(0 to 99) As Integer
Dim MyArray(99) As Integer
If you want VBA to assume that 1 is the lower index for your arrays, your need to add the following code in the Declarations section of your module:
Option Base 1
We assume the the lower index is 1 from now. The array code above, will now represent a 99 element array
b) Assigning values to elements in an array
Suppose we have an one-dimensional array called MyArray and we wish to assign the value 5 to the 3rd index slot, use the following
MyArray(3) = 5
c) Multidimensional arrays
We have look at one-dimensional arrays. Incredibly, VBA allow you to have as many as 60 dimensions. The following example declares a 50-integer array with two dimensions:
Dim MyArray(1 to 5, 1 to 5) As Integer
You can think of this array as occupying a 5-x-5 matrix. If we want to assign the value 5 to the entry in the 2nd row and 5th column of the array, use
MyArray(2, 5) = 5
In other word, the integer value 5 is stored in (row 2, column 5) of the matrix MyArray
For a three-dimensional array, think of it as a cube. Visualizing an array of more than three dimensions is more difficult. Well you don’t worry about the fourth dimension and beyond as I bet you’ll never use it.
d) Dynamic arrays
So far, we have been declaring arrays with a fixed number of elements. If you wish to declare an array without specifying its size, use
Dim MyArray() As Integer
This is how to create dynamic arrays. Notice a dynamic array has a blank set of parentheses:
To resize the array, you must use the ReDim statement to tell VBA how many elements the array has. Most of the time, the number of elements in the array is determined while your code is running. The ReDim statement can be used any number of times, to change the array’s size as often as you need.
For example if you need to resize MyArray to a 10 x 10 matrix then you
ReDim MyArray(10,10) As Integer
Now you have change the number of elements in a dynamic array. When you redimension an array by using ReDim, you clear all the values currently stored in the array elements.
Using Set
Although Most methods return values some. You cannot assign an object to a variable using an equals sign. Instead, you should use the Set statement, for example,
Set rangeoffset = Range("C1:C5").offset(1,1)
Saturday, November 14, 2009
VBA grammar.......(Excel Macro) Part I
Today we are going to discuss about Visual Basic Grammar. I called it VBA Grammar.
Objects:-
Visual Basic is an object-oriented language. This means that all the items in Excel are thought of as objects. There are more than a hundred of them.
Few Examples of objects are:
The Excel application(the largest object)
A Workbook
A worksheet
A Range
A Chart
A object is just like as noun. A object can contain other objects.
Few Examples.
Application.Workbooks("myexcel.xls")
The above line refer the workbook by name.
Application.workbooks("myexcel.xls").worksheets("sheet1").range("A2")
The above line refer the range of the sheet1 from the workbook "myexcel.xls".
Sheets("Mysheet") refers to the sheet called Mysheet.
Charts(1) refers to the first chart sheet on the tab bar.
Methods:-
Objects have methods that perform actions on them.
Methods are just like the verb of English Grammar.
We can think about it just like
Noun.Verb
Object.Method
If you were considering the Range object, then examples of methods would be:
Activate
Clear
Copy
Cut
Delete
Select
Few Examples
Sheet1.select
Range("B#").select
Selection.copy
Properties
Each object has its own characteristics. In general, properties control the appearance of objects.
Properties are just like Adjective of English Grammar.
Noun.Adjective = value
Object.Property = value
One thing must be remember that each object has its own set of methods and properties.
Few Examples
Range("C3").ColumnWidth = 14
sets the column width of cell C3 to 14.(Excel's default column width is 8.43 characters.)
Range("A1").columnwidth = Range("C3").columnwidth + 5
takes the value of the column width of cell C3. Adds 5 to it. and then assigns that value to the column width of cell A1.
So this three are the most important things in VBA Grammar.
Object
Methods
Property
And the other important things which are left for discuss they are
Variables
Dim
Constants
Arrays
Using Set
Objects:-
Visual Basic is an object-oriented language. This means that all the items in Excel are thought of as objects. There are more than a hundred of them.
Few Examples of objects are:
The Excel application(the largest object)
A Workbook
A worksheet
A Range
A Chart
A object is just like as noun. A object can contain other objects.
Few Examples.
Application.Workbooks("myexcel.xls")
The above line refer the workbook by name.
Application.workbooks("myexcel.xls").worksheets("sheet1").range("A2")
The above line refer the range of the sheet1 from the workbook "myexcel.xls".
Sheets("Mysheet") refers to the sheet called Mysheet.
Charts(1) refers to the first chart sheet on the tab bar.
Methods:-
Objects have methods that perform actions on them.
Methods are just like the verb of English Grammar.
We can think about it just like
Noun.Verb
Object.Method
If you were considering the Range object, then examples of methods would be:
Activate
Clear
Copy
Cut
Delete
Select
Few Examples
Sheet1.select
Range("B#").select
Selection.copy
Properties
Each object has its own characteristics. In general, properties control the appearance of objects.
Properties are just like Adjective of English Grammar.
Noun.Adjective = value
Object.Property = value
One thing must be remember that each object has its own set of methods and properties.
Few Examples
Range("C3").ColumnWidth = 14
sets the column width of cell C3 to 14.(Excel's default column width is 8.43 characters.)
Range("A1").columnwidth = Range("C3").columnwidth + 5
takes the value of the column width of cell C3. Adds 5 to it. and then assigns that value to the column width of cell A1.
So this three are the most important things in VBA Grammar.
Object
Methods
Property
And the other important things which are left for discuss they are
Variables
Dim
Constants
Arrays
Using Set
Indroduction with Macro in Excel (VBA)
In Excel, you can automate tasks by using macros. A macro is a set of instructions that tells Excel what to do. These commands are written in a computer programming language called Visual Basic for Applications (VBA).
Macros are basically of two types.
1) Recording Macros
2) Creating Macros
Means who don't know anything about Visual Basic he also can record macros for there daily use.
And also who have knowledge of Visual Basic they can write code (Instructions) to tell Excel what to do.
In this blog all of you can find the tricks with macro and a lot of codes for designing macros.
Macros are basically of two types.
1) Recording Macros
2) Creating Macros
Means who don't know anything about Visual Basic he also can record macros for there daily use.
And also who have knowledge of Visual Basic they can write code (Instructions) to tell Excel what to do.
In this blog all of you can find the tricks with macro and a lot of codes for designing macros.
Friday, November 13, 2009
Drawback of Microsoft 1900 Date system.
Today we are going to discuss about the drawbacks of Microsoft 1900 date system.
Microsoft Excel uses two different date systems: the 1900 date system and the 1904 date system. By default Microsoft Excel use the 1900 date system. Microsoft use 1900 date system because the 1900 date system allows greater compatibility between Microsoft Excel and other spreadsheets program such as Lotus 1-2-3.
Drawback of Microsoft 1900 Date system.
The 1900 date system suppose that the year 1900 is a leap year where it is false. due to this every time when we calculate date differences it show 1 day more than actual differences.
Click here for show the actual 1900 calender.
click here for show the Microsoft 1900 date system calendar.
Note:- The 1900 date system suppose that 1st Jan 1900 is the 1 day and the 1904 date system suppose that 1st Jan 1904 is the 1 day. The differences between both date system is 1462 days.
Microsoft Excel uses two different date systems: the 1900 date system and the 1904 date system. By default Microsoft Excel use the 1900 date system. Microsoft use 1900 date system because the 1900 date system allows greater compatibility between Microsoft Excel and other spreadsheets program such as Lotus 1-2-3.
Drawback of Microsoft 1900 Date system.
The 1900 date system suppose that the year 1900 is a leap year where it is false. due to this every time when we calculate date differences it show 1 day more than actual differences.
Click here for show the actual 1900 calender.
click here for show the Microsoft 1900 date system calendar.
Note:- The 1900 date system suppose that 1st Jan 1900 is the 1 day and the 1904 date system suppose that 1st Jan 1904 is the 1 day. The differences between both date system is 1462 days.
Thursday, November 12, 2009
HOW EXCEL CALCULATE DATE AND TIME
Do you know how excel understand date and time system..........
It is very easy and simple process.
By default excel use the 1900 date system. excel does not calculate any date before the 1900 date system. okay now what is the process. Excel count the date difference from 1st Jan 1900. That means 1st Jan 1900 is the first day according to excel and 2nd Jan 1900 is the 2nd day and so on.
Example:- Let if you entered 7th nov 1983 then the serial value of date will be 30627
Now you can use the datedif function in excel to calculate the differences between two day.
So I hope now you understand that how excel understand the date. In which process excel understand date the process is called serial value of date.
Now you must be want to know how excel understand the value of time.
So it is also simple. Excel understand time as decimal fractions. That means 24:00 or 12:00 AM excel assumed that time as whole number 1.
Example if the time is 18:00 then its value is 0.75.
if the time is 15:00 then its value is 0.625
So now you understand how excel see date and time.
Here we see some example:
If Date and time is 7th Nov 2009 and the time is 20:30 then the serial value is 40124.85417
So my dear friends now tell me if the date and time is 7th Nov 2009 and the time is 24:00 what is the serial value in excel.
If you have any question or comments please send it to me..............
Have a good day friend.
It is very easy and simple process.
By default excel use the 1900 date system. excel does not calculate any date before the 1900 date system. okay now what is the process. Excel count the date difference from 1st Jan 1900. That means 1st Jan 1900 is the first day according to excel and 2nd Jan 1900 is the 2nd day and so on.
Example:- Let if you entered 7th nov 1983 then the serial value of date will be 30627
Now you can use the datedif function in excel to calculate the differences between two day.
So I hope now you understand that how excel understand the date. In which process excel understand date the process is called serial value of date.
Now you must be want to know how excel understand the value of time.
So it is also simple. Excel understand time as decimal fractions. That means 24:00 or 12:00 AM excel assumed that time as whole number 1.
Example if the time is 18:00 then its value is 0.75.
if the time is 15:00 then its value is 0.625
So now you understand how excel see date and time.
Here we see some example:
If Date and time is 7th Nov 2009 and the time is 20:30 then the serial value is 40124.85417
So my dear friends now tell me if the date and time is 7th Nov 2009 and the time is 24:00 what is the serial value in excel.
If you have any question or comments please send it to me..............
Have a good day friend.
Monday, November 9, 2009
Examples of formulas.....................
Hi..............friends...................
Today I am going to share few links with u.
All those links included examples of formulas.........
Every link is important according to my view.....
The more you learn the more you get..........
So the links are bellow..........
http://office.microsoft.com/en-us/excel/HP052001271033.aspx
http://www.cpearson.com/excel/excelF.htm
http://www.educationonlineforcomputers.com/blogs/post/microsoft_office_training_tutorial/574/187-Excel-Formula-Tutorials-and-Examples
http://www.xlfdic.com/
http://www.free-training-tutorial.com/formulas.html
http://office.tizag.com/excelTutorial/excelformulas.php
so thats it..................a lot of links are available...............but you have to learn and search for it.........
Remember one important thing friends...........
A forumla is nothing more than an equation that you write up. In Excel a typical formula might contain cells, constants, and even functions. Here is an example Excel formula that we have labeled for your understanding.
Today I am going to share few links with u.
All those links included examples of formulas.........
Every link is important according to my view.....
The more you learn the more you get..........
So the links are bellow..........
http://office.microsoft.com/en-us/excel/HP052001271033.aspx
http://www.cpearson.com/excel/excelF.htm
http://www.educationonlineforcomputers.com/blogs/post/microsoft_office_training_tutorial/574/187-Excel-Formula-Tutorials-and-Examples
http://www.xlfdic.com/
http://www.free-training-tutorial.com/formulas.html
http://office.tizag.com/excelTutorial/excelformulas.php
so thats it..................a lot of links are available...............but you have to learn and search for it.........
Remember one important thing friends...........
A forumla is nothing more than an equation that you write up. In Excel a typical formula might contain cells, constants, and even functions. Here is an example Excel formula that we have labeled for your understanding.
Subscribe to:
Posts (Atom)
