Microsoft Excel command - Data Table (Two Variable)

Double Variable Data Tables

This tutorial assumes that you have read the earlier one and are comfortable with creating a single variable data table.

Create a new excel file as shown in the screenshot above. In cell A5,

enter the formula =B1 * (1 + B2) ^ B3.

As you can see, the $5000 invested at 7.5% for 5 years will give $7,178.15.

A two variable data table gives you results by varying two different variables in the equation. In our case, the equation is that of compound interest i.e.

Formula for calculating compound interest:

A = P * (1 + r/n) ^ nt

Where:

· P = principal amount (initial investment)

· r = annual interest rate (as a decimal)

· n = number of times the interest is compounded per year

· t = number of years

· A = amount after time t

We will be varying the annual interest rate and the number of years to find the varying results.

After you have created the file according to the screenshot above, select the data range A5:F10.

In Excel 2007, go to Data > What-If Analysis > Data Table. In Excel 2003, the menu path is Data > Table or you can use the shortcut key Alt + D + T in this order.

This wills popup a window where you will be asked to enter Row Input Cell and the Column Input Cell. Select the Column Input Cell as $B$2 and the Row Input Cell as$B$3 and hit OK.

The cells will be populated as shown in the screenshot below. As usual, you can choose to format the same currency.

How is it works?

Cells B6:F10 hold the formula {=TABLE(B3,B2)}.

Here, the values B4 to F4 are substituted for B3 in the formula in cell A5 and values A6 to A10 are substituted for B2 in the formula in cell A5 and the results are then populated accordingly.

Hence, for 1 year at 8%, the amount is $5,400, for 3 years at 10%; the amount is $6,655.00 and so on.

Microsoft Excel command - Data Table (Single Variable)

Single Variable Data Tables

Data Tables is also an advanced topic in Microsoft Excel that falls under the category of What-If Analysis. What-If or Sensitivity Analysis is carried out to study the variation of the output to changes in the input variable.

Consider a case of compound interest, where you invest a certain amount of money in a bank deposit and the amount is compounded every year.

Formula for calculating compound interest:

A = P * (1 + r/n) ^ nt

Where:

· P = principal amount (initial investment)

· r = annual interest rate (as a decimal)

· n = number of times the interest is compounded per year

· t = number of years

· A = amount after time t

Now, if suppose we want to see what the final amount will be at different interests rates, we can quickly use a data table for the same.

Excel Help describes a data table as:

“A data table is a range of cells that shows how changing one or two variables in your formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet. “

Without wasting any more time on descriptions, let’s get down to creating a data table for our compound interest example above.

Open a new excel file and enter the following as given in the screenshot below:

The cell B5 has the formula =B1 * (1 + B2) ^ B3

As you can see, the $5000 invested at 7.5% for 5 years will give $7,178.15.

Now, we will create a data table to see what amount we will receive by changing the interest rate.

Fill cells A6 to A10 with different interest rates. I've filled it with values from 6% to 10%. Now, select the cells A5:B10.

In Excel 2007, go to Data > What-If Analysis > Data Table. In Excel 2003, the menu path is Data > Table or you can use the shortcut key Alt + D + T in this order.

This wills popup a window where you will be asked to enter Row Input Cell and the Column Input Cell. Select the Column Input Cell as $B$2 and leave the Row Input Cell blank and hit OK.

The values will be filled in as shown below. You can choose to format them as currency, but that I have left it up to you.

How is it Works?

Well, we did get the results, but the question remains on what just happened.

To understand this simply, the Column Input Cell is the variable whose value we are going to vary. If supposing the data was presented horizontally instead of vertically which is our case, then you would need to select it as a Row Input Cell.

The values in cells A6 to A10 are then passed to the formula in cell B5 and the corresponding results are populated in cells B6 to B10.

If you look at cells B6 to B10, they contain the formula {=TABLE(,B2)}

Can you try creating a Data Table using a Row Input Cell? Why don't you post your solution below or on your blog and post a link below in the comments area.

Visual Basic File System : FileSystemObject

Purpose :
The FileSystemObject object allows you to work with drives, folders and files programmatically just as you can in the windows explorer interactively. you can copy, move and create file and folder; get information about the drives , folders and files; and so on. FileSystemObject gives you direct access to the windows explorer functionality with better performance than the standard file system access in visual basic and see the object list.

Object of the FileSystemObject Model

Drive : Gathers information about drives attached to the system, such as how much room is available.
Folder : Creates , deletes , or moves folders , plus queries the system as to their names pates and so on.
Files : Creates , deletes or moves files , pluse queries the system as to their names, paths and so on.
FileSystemObject : Creates, deletes , gains information about and generally manipulates drives folders and files . this is he main object of the group.
TextStream : Reads and writes text files.

• Use the create object method, or dimension a variable as a File System object object to create a File system object object
• Use to appropriate method on the newly created object.
• Access the object’s properties.

Creating a File System Object

Dimension a variable as type FileSystemObject object.
Dim fso As New FileSystemObject

Use the CreateObject nethod to create a FileSystemObject object
Set fso = CreateObject ( “ Scripting.FileSystemObject “ )

Getting Drive information:
- Total size of the drive in bytes ( TotalSize property )
- How much space is available on the drive in bytes ( AvailableSpace and FreeSpace properties )
- What letter is assigned to the drive ( DriveLetter property )
- What type of drive it is, such as removable, fixed, network, CD-ROM, or RAM disk ( DriveType property )
- Drive’s serial number ( SerialNumber property )
- Type of file system the drive use, such as FAT, FAT32, NTFS and so on ( FileSystem property )
- Whether a drive is available for use (IsReady property)
- Name of the share and /or volume ( ShareName and VolumeName properties )
- Path or root folder of the drive (path and RootFolder properties)

Project>References>Microsoft Scripting Runtime

Option Explicit
Private Sub Command1_Click()
Dim fso As New FileSystemObject, drv As Drive, s As String
Dim t As String
Set drv = fso.GetDrive(fso.GetDriveName ( " c : " ) )
s = "Drive " & drv.DriveLetter & " . "
s = s & "Total Space : " & FormatNumber(drv.TotalSize / 1024, 0 )
s = s & " kb " & vbCrLf
s = s & drv.VolumeName & vbCrLf
s = s & " Free Space : " & FormatNumber(drv.FreeSpace / 1024, 0 )
s = s & " kb " & vbCrLf
'
Select Case drv.DriveType
Case 0: t = "Unknown"
Case 1: t = "Removable"
Case 2: t = "Fixed"
Case 3: t = "Network"
Case 4: t = "CD-ROM"
Case 5: t = "RAM Disk"
End Select
'
s = s & t & " Drive " & vbCrLf
s = s & drv.SerialNumber & vbCrLf
s = s & drv.FileSystem & vbCrLf
s = s & drv.Path & vbCrLf
s = s & drv.RootFolder & vbCrLf
MsgBox s
End Sub

Download Vb Program

http://vbcode.in/vbf/FSO.zip

Working with Folder

-Create a folder : FileSystemObject.CreateFolder
-Delete a folder : Folder.Delete or FileSystemObject.DeleteFolder
-Move a folder : Folder.Move or FileSystemObject.MoveFolder
-Copy a folder : Folder.Copy or FileSystemObject.CopyFolder
-Retrieve the name of a folder : Folder.Name
-Find out whether a folder exists on a drive : FileSystemObject.FolderExists
-Get an instance of an existing folder object : FileSystemObject.GetFolder
-Find out the name of a folder’s parent folder : FileSystemObject.GetParentFolderName
-Find out the Path of System folder : FileSystemObject.GetSpecialFolder

Option Explicit
'Project>References>Microsoft Scripting Runtime
Private Sub Command1_Click()
Dim fso As New FileSystemObject, fldr As Folder, s As String
Set fldr = fso.GetFolder ( " c:\a\b " )
Debug.Print "Folder Name : " & fldr.Name
Debug.Print "Contained on drive : " & fldr.Drive
If fldr.IsRootFolder = True Then
Debug.Print " This folder is a root folder"
Else
Debug.Print " This folder isn't a root folder "
End If
Debug.Print fldr.ParentFolder
Debug.Print fldr.Path
Debug.Print FormatNumber(fldr.Size / 1024, 0) & " KB "
Debug.Print fldr.DateCreated & " Folder Created Date "
Debug.Print fldr.DateLastModified & " Folder Last Modified Date "
Debug.Print fldr.DateLastAccessed & " Folder Last Accessed Date "
End Sub

Private Sub Command2_Click()
Dim fso As New FileSystemObject
fso.CreateFolder ( " c:\1 " )
Debug.Print "Basename = " & fso.GetBaseName( " c:\1 " )
fso.DeleteFolder ( " c:\1 " )
End Sub

Download Vb Program

http://vbcode.in/vbf/FSOfolder.zip

.

Working with Files

You can work with files in Visual Basic by the new using the new object-oriented FSO object. Such as Copy, Delete, Move, and open As Text Stream; others; or by using the older existing Functions, such as open, close, file Copy, Get Attar, and so on. Note that you can move copy Or delete files regardless of their file type. There are two major a categories of file manipulation.

• Creating adding, or removing data; and reading files
• Moving, copying and deleting

Using the File System object to create a file or manipulate the data contained in an Existing is as simple as using the correct method.

Copy File : Copies one or more file from location to another

Delete File : Deletes a specified file

Move File : Move one or more files from one location to another

OpensTextStream : Open a specified file and returns a TextStrem object that can be Used to read from, write to, or append to the file

Read Line : Reads an entire line (up to, but not including, the newline character) From a Tex Stream file and return s the resulting

ReadAll : Reads an entire Text stream file and returns the resulting string

WrightBlankLine : Writes a specified number of newline characters to a text stream file

Write Line : Writes a specified number of newline character to a Texts tram file

Create Text File : Creates a specified filename and returns a Text Stream object that can Be used to read from or write to file.

Option Explicit

'here i am created folder "a" in C:\ drive

'and under folder "a" also created folder "b"

Private Sub Command1_Click()

Dim fso As New FileSystemObject, txtfile As TextStream

Set txtfile = fso.CreateTextFile( "c:\testfile.txt", True )

txtfile.Write ( "This is a test. " )

txtfile.WriteLine ( " Texting 1, 2, 3." )

txtfile.WriteBlankLines (3)

txtfile.Close

End Sub

Private Sub Command2_Click()

Dim fso As New FileSystemObject

Dim fil1 As File, ts As TextStream

Dim s As String

Set fil1 = fso.GetFile( "c:\testfile.txt" )

'

Set ts = fil1.OpenAsTextStream(ForReading)

s = ts.ReadLine

MsgBox s

ts.Close

'

Set ts = fil1.OpenAsTextStream(ForWriting)

ts.Write ("Hello Samrudhi")

ts.WriteLine (s)

ts.Close

'

Set ts = fil1.OpenAsTextStream(ForReading)

s = ts.ReadLine

MsgBox s

ts.Close

End Sub

Private Sub Command3_Click()

Dim fso As New FileSystemObject, ts As TextStream

Dim fil1 As File, fil2 As File

Set fil1 = fso.GetFile( "c:\testfile.txt" )

fil1.Move ( "c:\a\testfile.txt" )

MsgBox "File moving in folder C:\a "

fil1.Copy ( "c:\a\b\testfile.txt" )

MsgBox "File copy in folder C:\a\b "

Set fil1 = fso.GetFile ( "c:\a\testfile.txt" )

Set fil2 = fso.GetFile ( "c:\a\b\testfile.txt" )

fil1.Delete

fil2.Delete

MsgBox " both file is delete"

End Sub

Download Vb Program

http://vbcode.in/vbf/FSOfile.zip

.

Microsoft Excel command - Goal Seek

Goal Seek is used when you know what answer you want, but don't know the exact figure to input for that answer. For example, you're quite certain that 8 multiplied by something equals 56. You just not sure what that missing number is. Is it 8 multiplied by 6? Or Is it 8 multiplied by 7? Goal Seek will tell you the answer.

We'll test that example out right now. So start a new spreadsheet, and create one the same as in the image below:

Before you can use Goal Seek, Excel needs certain things from you. First it needs some sort of formula to work with. In the image above we have the simple formula =B1 * B2. We've put this in cell B3. But the answer is wrong for us. We had a Goal of 56 (8 times something). We want to know which number you have to multiply 8 by in order to get the answer 56. We tried 8 times 6, and that gave the answer of 48. So we have to try again.

Instead of us puzzling the answer out, we can let Goal Seek handle it. So do the following:

• From the Excel menu bar, click on Tools
• From the drop down menu, click on Goal Seek
• A dialogue box pops up like the one below

The dialogue box needs a little explaining. "Set cell" is the answer you're looking for, this is the Goal. Set cell needs a formula or function to work with. Our formula is in cell B3, so if your "Set cell" text box does not say B3, click inside it and type B3.

"To Value" is the actual answer you're looking for. With "Set cell", you're just telling Excel where the formula is. With "To Value" you have to tell Excel what answer you're looking for. We wanted an answer of 56 for our formula. So click inside the "To Value" text box and type 56.

"By Changing Cell" is the missing bit. This is the part of the formula that needs to change in order to get the answer you want. In our formula we have an 8 and a 6. Clearly, the 6 is the number that has to go. So the cell that needs to change is B2. So go ahead and enter B2 in the "By Changing Cell" text box. Your dialogue box should now look like this:

Click OK when your dialogue box looks like the one above. Excel will then Set the cell B3 to the Value of 56, and change the figure in cell B2. You'll also get a dialogue box like the one below:

Click OK on the dialogue box. Your new spreadsheet will look like this one:

So Goal Seek has given us the answer we wanted: it is 7 that when times by 8 equals 56.

Download Visual Basic Project work like this Goal Seek

http://vbcode.in/excel/goalseek.zip

Please sagest me for more improvement in visual basic goal seek program

.

Visual Basic File System Statement : SetAttr

Purpose : The SetAttr Statement sets the attribut information for DOD files.

Syntax :
SetAttr filename$ , attrbuteBits%

SetAttr "MYFILE.TMP" , vbReadOnly , vbSystem

All file , directories and volumes have an attribute byte associated with then. This information indicates whether the filename is normal, read-only, hidden, system a volume label, a directory, or has been modified since the last backup.

Constants used with the SetAttr function
Constant : Value : Description
vbNormal : 0 : Normal
vbReadOnly : 1 : Read-Only
vbHidden : 2 : Hidden
vbSystem : 4 : System file
vbDirectory : 16 : Directory
vbArchive : 32 : File has changed since last backup

Visual Basic File System Statement : RMDir

Purpose : The RMDir statement removes a subdirectory from a disk.

Syntax :
RmDir dirname$

dirname$ must be in the format of
[ dirve: ] [\] dir [ \ subdir ] [\ subdir ] .......

RmDir "TEST"
RmDir "\TEST"
RmDir "D:\TEST"

Visual Basic File System Statement : Name

Purpose : The Name statement renames a file or directory or moves a file to another directory.

Syntax :
Name oldname As newname

Name "Test_1.Dat " As " Test_2.Dat "

in the example changes the name of the file Test_1.Date in the default directory to Test_2.Dat.

Visual Basic File System Statement : MKDir

Purpose : the MKDir statement creates a subdirectory on the specified drive.

Syntax :
MKDir dirname$

dirname$ must be in format of
[drive:][ \ ] dir [ \ subdir ] [ \ subdir ] ...

MKDir "TEST"
MKDir "\TEST"
MKDir "D:\TEST"

the first example creates the subdirectory TEST below the defult directory on the default drive.
the second line creates the subdirectory TEST below the root directory of the default drive.
and third example creates the subdirectory TEST below the root directory of the D drive.

Visual Basic File System Statement : Kill

Purpose : The Kill Statement deletes the specified file from the disk.

Syntax :
Kill filename$

Kill File1.List ( File1.ListIndex )

in this example erases the file that is currently selected in the FileListBox named File1.

Visual Basic File System Function : GetAttr

Purpose : The GetAttr function lets you determine a file , directory or volume label's attributes.

Syntax :
GetAttr ( fileName$ )

if GetAttr ( fileName$ ) And vbHidden Then checkHidden.Value=CHECKED

Constants used with the GetAttr function
Constant : Value : Description
vbNormal : 0 : Normal
vbReadOnly : 1 : Read-Only
vbHidden : 2 : Hidden
vbSystem : 4 : System file
vbDirectory : 16 : Directory
vbArchive : 32 : File has changed since last backup

Visual Basic File System Function : FileLen

Purpose : the FileLen function returns the total length of a file in bytes.

Syntax :
FileLen ( filename$ )

totalBytes = FileLen ( Dir ( ) )

It return a long integer indicating the total number of bytes in the file.

Visual Basic File System Function : FileDateTime

Purpose : The FileDateTime function returns a date that indicates the date and time a file was created or last modified.

Syntax :
FileDateTime ( filename$

lastSaved = CVDate ( fileDateTime ( "DATAFILE.MDB" )

Usethe FileDateTime function to datermine when a DOS file was created or last saved. The function returns a date.

Visual Basic File System Statement : FileCopy

Purpose : Use the FileCopy statement to copy a file. this is similer to the DOS copy command.

Syntax :
FileCopy source$ , dest$

FileCopy " temp.tem" , dataFileName

This command copies a xopies a DOS file. the source$ file can be opened for read-only access but must not be opened for write access. You can specify a drive and directory in either of the filenames, but you cannot specify any wildcards. The sample syntax copies the file TEMP.TEM to the filename specifed by the variable dataFileName

Visual Basic File System Function : Dir, Dir$

Purpose : The Dir and Dir$ function return a filename that matches the supplied pattern, file attribute or volume label of a drive . the Dir$ function is retained of backward compatibility.

Syntax :
For the first call to Dir

Dir [ ( pattern$ [ , Attributes ] ) ]

For each successive call for the same pattern :

Dir

Temp$ = Dir ( "*.DOC" )
Do Until Temp$ = ""
i=i+1
Docs ( i ) = Temp$
Temp$ = Dir
Loop
Temp$ = Dir ( "*.*" , vbHidden )
Temp$ = Dir ( "c:\ " , vbDirectory )

Constants used with the Dir function
Constant : Value : Description
vbNormal : 0 : Normal
vbHidden : 2 : Hidden
vbSystem : 4 : System file
vbVolume : 8 : Volume label;
vbDirectory : 16 : Directory

Visual Basic File System Function : CurDir, CurDir$

Purpose : The CurDir$ or CurDir function returns the current default directory for the specified drive.

Syntax :
CurDir [ ( drive$ ) ]
CurDir$ [ ( drive$ ) ]

Default$ = CurDir
A$ = CurDir ( " C " )
CDirectory = CurDir ( " C: " )

Visual Basic File System Statement : ChDriver

Purpose : The ChDrive statement changes the current default drive.

Syntax :
ChDrive Drive$

ChDrive "A"
ChDirive "C:"

Visual Basic File System Statement : ChDir

Purpose : The ChDir statement changes the current working directory on the specified drive.

Syntax :
ChDir path$

path$ must be a string in the format of
[drive:] [\] dir [\subdir] [\subdir...]

ChDir "D:\MYDIR"
ChDir "\MAIN\MAILBOX"
ChDir "\\MySERVER\MAIN\MAILBOX"

The ChDir statement can affect the operation of file -related commands
Path$ is not longer then 128 characters

Visual Basic Date and Time Function : TimeValue

Purpose : The TimeValue function converts a time in the form of a string into a Visual Basic Date Data type.

Syntax :
timeValure ( timestring$ )

timestring$ : Text string to convert that is an acceptable time value

Examples of valid timeString value
timestring$
3:05
3:05:23
03:05:23
15:05
3:03 pm
3:05 AM
October 6, 2010 3:05 am

Option Explicit
Private Sub Command1_Click()
Dim EndTime, FirstTime, T
Dim Seconds$
Start:
On Error GoTo NotTime
If Text1.Text = "" Or Text2.Text = "" Then
MsgBox "Please Enter a tim in each box."
Exit Sub
End If
FirstTime = TimeValue(Text1.Text)
EndTime = TimeValue(Text2.Text)
Text1.Text = Str$(FirstTime)
Text2.Text = Str$(EndTime)
T = EndTime - FirstTime
Seconds$ = Str$(Int(T * 86400))
Label1.Caption = Seconds$ + " seconds "
Exit Sub

NotTime:
MsgBox "Please Enter a value Time."
Text1.Text = ""
Text2.Text = ""
Resume Start
End Sub

Visual Basic Date and Time Function : TimeSerial

Purpose : The TimeSerial function converts the values of an indicated time to a Visual Basic Date Data type

Syntax :
TimeSerial ( hour% , minute% , second% )

hour% : A number or expression that evaluates to a number between 0 and 23 inclusive
minute% : A number or expression that evaluates to a number between 0 and 59 inclusive
second% : A number or expression that evaluates to a number between 0 and 59 inclusive

Option Explicit

Private Sub Command1_Click()

Dim BeginHour%, EndHour%, BeginMinute%, EndMinute%

Dim BeginSecond%, EndSecond%

Dim EndTime, BeginTime

Start:

On Error GoTo NotTime

If Text1.Text = "" Then GoTo EnterTime

BeginTime = TimeValue(Text1.Text)

BeginHour = Hour(BeginTime)

EndHour = Val(Text2.Text)

BeginMinute = Minute(BeginTime)

EndMinute = Val(Text3.Text)

BeginSecond = Second(BeginTime)

EndSecond = Val(Text4.Text)

EndTime = TimeSerial(BeginHour + EndHour, _

BeginMinute + EndMinute, _

BeginSecond + EndSecond)

Label1.Caption = Format$(EndTime, "hh:mm:ss")

Exit Sub

EnterTime:

MsgBox "Please Enter a value in each box."

Exit Sub

NotTime:

If BeginSecond + EndSecond > 60 Then

EndSecond = EndSecond - 60

EndMinute = EndMinute + 1

ElseIf BeginMinute + EndMinute > 60 Then

EndMinute = EndMinute - 60

EndHour = EndHour + 1

ElseIf BeginHour + EndHour > 24 Then

EndHour = EndHour - 24

Else

MsgBox "Please Enter a value Time."

Text1.Text = ""

End If

End Sub

Visual Basic Date and Time Event : Timer

Purpose : The Timer event contains the actions that take place when a time equal to the interval value of the timer control has elapsed. this event triggers every time the interval of time elapses until the timer control is disabled . A timer control is disabled by setting its Enabled property to False , by setting its Interval property to 0 , or by unloading the form.

Syntax :
Sub Name_Timer ( [ Index As Integer ] )

Name : Name property of the timer
Index : Serves as a reference for the part of a control array

Option Explicit
Dim x%, y%, Radius%
Const pi = 3.14159265

Private Sub Timer1_Timer()
Cls
Static num As Double
FillColor = QBColor(1)
FillStyle = 1
x% = ScaleWidth / 2
y% = ScaleHeight / 2
Radius% = ScaleWidth / 4
Circle (x%, y%), Radius%, num, -6.283
num = num - ((2 * pi) / 60)
Text1.Text = Str$(num)
If Abs(num) >= 6.238 Then
num = 0
Timer1.Enabled = 0
Cls
End If
End Sub

Visual Basic Date and Time Statement : Time , Time$

Purpose : The Time and Time$ statements enable the user to change the system time of a computer within a Visual Basic application.

Syntax :
Time = expression$
Time$ = timestring$

timestring$ : Time in string format hh:mm:ss
expression$ : Acceptable time format ( more general than timestring$ )

timestring$ : Value
hh : A number between 00 and 23 inclusive
mm : A number between 00 and 59 inclusive
ss : A number between 00 and 59 inclusive

expression$
2:21
2:21 PM
14:21
October 4, 2010 10:11 am

Option Explicit

Private Sub cmdClose_Click()

End

End Sub

Public Sub ErrMsgBox(str)

MsgBox str

End Sub

Private Sub cmdOk_Click()

Dim intHours As Integer, intMinutes As Integer, intSeconds As Integer

intHours = Val(txtHours.Text)

intMinutes = Val(txtMinutes.Text)

intSeconds = Val(txtSeconds.Text)

If Len(LTrim(txtHours.Text)) > 0 Then

If intHours "Less Then" 0 Or intHours > 23 Then

ErrMsgBox "Hours are not in an acceptable format!"

txtHours.SetFocus

Exit Sub

End If

If intMinutes "Less Then" 0 Or intMinutes > 59 Then

ErrMsgBox "Minutes are not in an acceptable format!"

txtMinutes.SetFocus

Exit Sub

End If

If intSeconds "Less Then" 0 Or intSeconds > 59 Then

ErrMsgBox "Second are not in an acceptable format!"

txtSeconds.SetFocus

Exit Sub

End If

txtTime.Text = Trim(intHours) & " : " & Trim(intMinutes) & " : " & _

Trim(intSeconds)

Time = txtTime.Text

Exit Sub

Else

On Error GoTo BadTime

End If

BadTime:

ErrMsgBox "That time is not in an acceptable format!"

End Sub

Note : Less Then use sing

Visual Basic Date and Time Function : Time , Time$

Purpose : The Time function returns the current system time as a date data type . the Time$ function returns your computer's systems time as a string.

Syntax :
Time
Time$

Return values of the Time and Time$ functions
Time : Description
Hour(hh) : A number between 00 and 23 inclusive
Minute (mm) : A number between 00 and 59 inclusive
Second (ss) : A number between between 00 and 59 inclusive

Option Explicit
Private Sub cmdClose_Click()
If tmrStopWatch.Enabled = False Then
tmrStopWatch.Enabled = True
cmdClose.Caption = "Close"
Else
tmrStopWatch.Enabled = False
cmdClose.Caption = "Start"
End If
End Sub

Private Sub Form_Load()
tmrStopWatch.Interval = 500
End Sub

Private Sub tmrStopWatch_Timer()
tmrStopWatch.Interval = 500
txtNow(0).Text = Time
txtNow(1).Text = Time$
txtNow(2).Text = Format(Time, "hh:mm")
End Sub

Visual Basic Date and Time Function : Interval

Purpose : The Interval property of a timer control indicates the length of time to wait before processing the Timer event. This property can be changed at design time or runtime. Each timer control's Interval property is independent of the Interval properties of other timer controls on the same form.

Syntax :
[ forrm! ].Name.Interval = milliseconds&

Form : Name property of the form
Name : Name property of the timer control being affected
milliseconds& : Amount of time that must pass between triggerings of the Timer event ( as a long integer )

Setting of the Interval property
Value : Effect
0 : Disables the timer (default)
1-65535 : Milliseconds between triggerings ; 1000 = 1 second

The Timer control's Enabled property determines whether the control responds to the passage of time. Set Enabled to False to turn a Timer control off, and to True to turn it on. When a Timer control is enabled, its countdown always starts from the value of its Interval property setting.

Option Explicit
Private Sub Form_Load()
Timer1.Interval = 900 ' Set interval.
HScroll1.Min = 100 ' Set minimum.
HScroll1.Max = 900 ' Set maximum.
End Sub
Private Sub HScroll1_Change()
' Set interval according to scroll bar value.
Timer1.Interval = 1000 - HScroll1.Value
End Sub
Private Sub Timer1_Timer()
' Switch BackColor between red and blue.
If Picture1.BackColor = RGB(255, 0, 0) Then
Picture1.BackColor = RGB(0, 0, 255)
Else
Picture1.BackColor = RGB(255, 0, 0)
End If
End Sub

Visual Basic Date and Time Function : Hour , Minute , Second

Purpose : The Hour , Minute and Second function return an integer representing the hour, minute , second portions of a date value. The Hour function returns an integer that is between 0 (12:00am) and 23 (11:00pm) inclusive . The Minute and second function s return an integer between 0 and 59 representing the minute or second portion of the date value.

Syntax :
Hour ( expression )
Minute ( expression )
Second ( expression )

expression : Variant representing a date and time or a number that can be converted to a serial number

Option Explicit

Private Sub Form_Load()
Timer1.Interval = 1000
End Sub

Private Sub Timer1_Timer()
Dim NormalTime%
Dim DayTime$, Current$

Timer1.Interval = 1000
If Hour(Now) > 12 Then
DayTime$ = " PM "
NormalTime% = Hour(Now)
Else
DayTime$ = " AM "
NormalTime% = Hour(Now)
End If

Current$ = Str$(NormalTime) + " : "
Current$ = Current$ + Str$(Minute(Now)) + " : "
Current$ = Current$ + Str$(Second(Now))
Current$ = Current$ + DayTime$
Text1.Text = Current$
End Sub

Visual Basic Date and Time Function : Weekday , Month , Day , Year

Purpose : The Weekday , Month , Day and Year function return an integer value representing the day of the week , the month , the day , or the year , respectively . These values can be used to construct or format a bate in usual terms , such as Thursday, December 15, 1997

Syntax :
Month ( expression$ )
Day ( expression$ )
Year ( expression$ )
Weekday ( expression$ , [ firstdayof week ] )

expression$ : Date variant to display in month, day , year or weekday format
firstdayofweek : Aconstant that specifies the first day of the week . omitted , vbsunday is assumed.

Private Sub Form_Load()
Dim CMonth%, CDay%, CYear%, CWeek%
Dim Yr$, M$, W$
CMonth% = Month(Now)
CDay% = Day(Now)
CYear% = Year(Now)
CWeek% = Weekday(Now)
If CYear% "Less Then" 2000 Or CYear > 1899 Then
Yr$ = Format(CYear%, "yy")
Else
Yr$ = Str$(CYear%)
End If
Select Case CMonth%
Case 1: M$ = "Q1 January "
Case 2: M$ = "Q1 Febuary "
Case 3: M$ = "Q1 March "
Case 4: M$ = "Q2 April "
Case 5: M$ = "Q2 May "
Case 6: M$ = "Q2 June "
Case 7: M$ = "Q3 July "
Case 8: M$ = "Q3 August "
Case 9: M$ = "Q3 September "
Case 10: M$ = "Q4 October "
Case 11: M$ = "Q4 November "
Case 12: M$ = "Q4 December "
End Select
Select Case CWeek%
Case vbSunday: W$ = "Sunday "
Case vbMonday: W$ = "Monday "
Case vbTuesday: W$ = "Tuesday "
Case vbWednesday: W$ = "Wednesday "
Case vbThursday: W$ = "Thursday "
Case vbFriday: W$ = "Friday "
Case vbSaturday : W$ = "Saturday "
End Select
Text1.Text = W$ & " , " & M$ & Str(CDay%) & " , " & Str$(CYear%)
End Sub

Note : Less Then use sing

Visual Basic Date and Time Function : DatePart

Purpose : Returns a Variant (Integer) containing the specified part of a given date.

Syntax :
DatePart (interval, date [ , firstdayofweek [ , firstweekofyear ] ] )

Interval : that is the interval of time you want to return.
date : value that you want to evaluate.
Firstdayofweek : A constant that specifies the first day of the week. If not specified, Sunday is assumed.
Firstweekofyear : A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

You can use the DatePart function to evaluate a date and return a specific interval of time. For example, you might use DatePart to calculate the day of the week or the current hour.

Dim TheDate As Date ' Declare variables.
Dim Msg
TheDate = InputBox ("Enter a date:")
Msg = "Quarter: " & DatePart("q", TheDate)
MsgBox Msg

Visual Basic Date and Time Function : DateDiff

Purpose : The DateDiff function returns a Long data type the number of time intervals between two specified dates

Syntax :
DateDiff ( interval , date1 , date2 [ , firstdayofweek [ , firstweekofyear ] ] )

Interval : that is the interval of time you use to calculate the difference between date1 and date2.
date1, date2 : Two dates you want to use in the calculation.
Firstdayofweek : A constant that specifies the first day of the week. If not specified, Sunday is assumed.
Firstweekofyear : A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Dim TheDate As Date ' Declare variables.
Dim Msg
TheDate = InputBox ("Enter a date")
Msg = "Days from today: " & DateDiff("d", Now, TheDate)
MsgBox Msg