Excel Date & Time Functions

Sub Time_Now()
Dim s As String
s = Now()
Cells(1, 1) = s
End Sub

Loop Structures

For ... Next 

Use For ... Next loop if the number of loops is already defined and known.  A For ... Next loop uses a counter variable that increases or decreases in value during each iteration of the loop.  This loop structure is being used the most for our examples on this site.  

Here is an example of the For ... Next loop:

    For i = 1 to 10
        Cells(i, 1) = i
    Next i

    

In this example, i is the counter variable from 1 to 10.  The looping process will send value to the first column of the active sheet and print i (which is 1 to 10) to row 1 to 10 of that column.

Note that the counter variable, by default, increases by an increment of 1


For ... Next Loop With Step

You can use the Step Keyword to sepcify a different increment for the counter variable.

For example:

    For i = 1 to 10 Step 2
        Cells(i, 1) = i
    Next i

This looping process will print values with an increment of 2 on row 1, 3, 5, 7 and 9 on column one.

    

You can also have decrement in the loop by assign a negative value afte the Step keyword.

For example:

    For i = 10 to 1 Step -2
        Cells(i, 1) = i
    Next i

This looping process will print values with an increment of -2 starts from 10 on row  10, 8, 6, 4 and 2 on column one.

    

Do While ... Loop

You can use the Do While ... Loop to test a condition at the start of the loop.  It will run the loop as long as the condition is ture and stops when the condition becomes false.  For Example:

   i = 1
    Do While i =< 10
        Cells(i, 1) = i
        i = i + 1
    Loop

This looping process yields the same result as in the For ... Next structures example.

One thing to be caution is that sometimes the loop might be a infinite loop.  And it happens when the condition never beomes false.  In such case, you can stop the loop by press [ESC] or [CTRL] +[BREAK]


Do Until ... Loop 

You can test the condition at the beginning of the loop and then run the loop until the test condition becomes true.

Example:

    i = 1
    Do Until i = 11
        Cells(i, 1) = i
        i = i + 1    
    Loop

This looping process yields the same result as in the For ... Next structures example

Do ... Loop WhileWhen you want to make sure that the loop will run at least once, you can put the test at the end of loop.  The loop will stop when the condition becomes false.   (compare this loop structure to the Do ... While Loop.)

For Example:
    

   i = 1
    Do 
        Cells(i, 1) = i
        i  = i + 1
    Loop While i < 11

This looping process yields the same result as in the For ... Next structures example


Do ... Loop Until

This loop structure, like the Do ... Loop While, makes sure that the loop will run at least once, you can put the test at the end of loop.  The loop will stop when the condition becomes true.   (compare this loop structure to the Do ... Until Loop.)

For Example:
    

   i = 1
    Do 
        Cells(i, 1) = i
        i  = i + 1
    Loop Until i = 11

  
This looping process yields the same result as in the For ... Next structures example.

Select Case

Select Case statement is an alternative to the ElseIf statement.  This method is more efficient and readable in coding the the If ... Then ... ElseIf statment. 

Example:

    Select Case Grade
        Case Is >= 90
            LetterGrade = "A"
        Case Is >= 80
            LetterGrade = "B"
        Case Is >= 70
            LetterGrade = "C"
        Case Is >= 60
            LetterGrade = "D"
        Case Else
            LetterGrade = "Sorry"
    End Select

IF ... Then ... Else and IF ... Then ... ElseIf

The If ... Then ... Else statement is used to define two blocks of conditions - true and false.

Example:
    

     If Age >=22 Then
        Drink = "Yes"
    Else
        Drink = "No"

    End If

Note that End If statement is needed in this case as well since there is more than one block of statements 

The IF ... Then ... ElseIf is used to test additional conditions without using new If ... Then statements.

For Example:

   If Age >= 18 and Age < 22 Then
        Msgbox "You can vote"
    ElseIf Age >=22 and Age < 62 Then
        Msgbox "You can drink and vote"
    ElseIf Age >=62 Then
        Msgbox "You are eligible to apply for Social Security Benefit"
    Else
        Msgbox "You cannot drink or vote"
    End If

Note that the last condition under Else is, implicitly, Age < 18

IF and Select Case

Decision Structures - IF and Select Case IF ... Then Statement The IF ... Then is a single condition and run a single statement or a block of statement. Example, the following statement set variable Status to "Adult" if the statement is true:         If Age >= 18 Then Status = "Adult" You can also use multiple-line block in the If statement as followed:         If Ago >= 18 Then              Status = "Adult"             Vote = "Yes"         End If

VBA Keyboard Shortcuts

Press	To Do This	Press	To Do This
Alt+F11	Open the VBA editor	F1	Get help on any selected item
Alt+F8	Display a list of macros	F2	Display the Object Browser window
Ctrl+Break	Perform an emergency stop of the program	F4	Display the Properties window
Ctrl+G	Display the Immediate window	F5	Start the program
Ctrl+I	List the quick information for the selected element	F7	Display the Code window after selecting a form or control
Ctrl+J	List the properties and methods for an object	F9	Add a breakpoint
Ctrl+R	Display Project Explorer	Shift+F7	Display the form that corresponds to the active Code window
Ctrl+Shift+I	List the parameter information for the selected element	Shift+F9	Add a quick watch for the highlighted text
Ctrl+Shift+J	List the constants associated with an enumeration	Ctrl+Tab	Move to the next Code or UserForm window

VBA's Most Useful Built-in Functions

Function	What It Does
Abs	Returns a number's absolute value
Array	Returns a variant containing an array
Asc	Converts the first character of a string to its ASCII value
Atn	Returns the arctangent of a number
Choose	Returns a value from a list of items
Chr	Converts an ANSI value to a string
Cos	Returns a number's cosine
CurDir	Returns the current path
Date	Returns the current system date
DateAdd	Returns a date to which a specified time interval has been
	added — for example, one month from a particular date
DateDiff	Returns an integer showing the number of specified time intervals between two dates, for example the number of months between now and your birthday
DatePart	Returns an integer containing the specified part of a given
	date — for example, a date's day of the year
DateSerial	Converts a date to a serial number
DateValue	Converts a string to a date
Day	Returns the day of the month from a date value
Dir	Returns the name of a file or directory that matches a pattern
Erl	Returns the line number that caused an error
Err	Returns the error number of an error condition
Error	Returns the error message that corresponds to an error number
Exp	Returns the base of the natural logarithm (e) raised to a power
FileLen	Returns the number of bytes in a file
Fix	Returns a number's integer portion
Format	Displays an expression in a particular format
GetSetting	Returns a value from the Windows registry
Hex	Converts from decimal to hexadecimal
Hour	Returns the hours portion of a time
InputBox	Displays a box to prompt a user for input
InStr	Returns the position of a string within another string
Int	Returns the integer portion of a number
IPmt	Returns the interest payment for an annuity or loan
IsArray	Returns True if a variable is an array
IsDate	Returns True if an expression is a date
IsEmpty	Returns True if a variable has not been initialized
IsError	Returns True if an expression is an error value
IsMissing	Returns True if an optional argument was not passed to a procedure
IsNull	Returns True if an expression contains no valid data
IsNumeric	Returns True if an expression can be evaluated as a number
IsObject	Returns True if an expression references an OLE Automation object
LBound	Returns the smallest subscript for a dimension of an array
LCase	Returns a string converted to lowercase
Left	Returns a specified number of characters from the left of a string
Len	Returns the number of characters in a string
Log	Returns the natural logarithm of a number to base e
LTrim	Returns a copy of a string, with any leading spaces removed
Mid	Returns a specified number of characters from a string
Minute	Returns the minutes portion of a time value
Month	Returns the month from a date value
MsgBox	Displays a message box and (optionally) returns a value
Now	Returns the current system date and time
RGB	Returns a numeric RGB value representing a color
Right	Returns a specified number of characters from the right of a string
Rnd	Returns a random number between 0 and 1
RTrim	Returns a copy of a string, with any trailing spaces removed
Second	Returns the seconds portion of a time value
Sgn	Returns an integer that indicates a number's sign
Shell	Runs an executable program
Sin	Returns a number's sine
Space	Returns a string with a specified number of spaces
Sqr	Returns a number's square root
Str	Returns a string representation of a number
StrComp	Returns a value indicating the result of a string comparison
String	Returns a repeating character or string
Tan	Returns a number's tangent
Time	Returns the current system time
Timer	Returns the number of seconds since midnight
TimeSerial	Returns the time for a specified hour, minute, and second
TimeValue	Converts a string to a time serial number
Trim	Returns a string without leading or trailing spaces
TypeName	Returns a string that describes a variable's data type
UBound	Returns the largest available subscript for an array's dimension
UCase	Converts a string to uppercase
Val	Returns the numbers contained in a string
VarType	Returns a value indicating a variable's subtype
Weekday	Returns a number representing a day of the week
Year	Returns the year from a date value

Unique Data

{=INDEX($A$2:$A$8,MATCH(0,COUNTIF($F$1:F2,$A$2:$A$8),0),1)}

Address Function in Excel

In Excel, the ADDRESS function returns a text representation of a cell address.

The syntax for the ADDRESS function is:

ADDRESS( row, column, [ref_type], [ref_style], [sheet_name] )

row is the row number to use in the cell address.

column is the column number to use in the cell address.

ref_type is optional. It is the type of reference to use. It can be any of the following values:

Value	Explanation
1	Absolute referencing. For example: $A$1
2	Absolute row; relative column. For example: $A1
3	Relative row; absolute column. For example: A$1
4	Relative referencing. For example: A1

If this parameter is omitted, the ADDRESS function assumes that the ref_type is set to 1.

ref_style is optional. It is the reference style to use: either A1 or R1C1. It can be any of the following values:

Value	Explanation
TRUE	A1 style referencing
FALSE	R1C1 style referencing

If this parameter is omitted, the ADDRESS function assumes that the ref_style is set to TRUE.

sheet_name is optional. It is the name of the sheet to use in the cell address. If this parameter is omitted, then no sheet name is used in the cell address.

Applies To:

• Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

• Worksheet function (WS)

Worksheet Function Example:

Let's take a look at an example to see how you would use the ADDRESS function in a worksheet:

=ADDRESS(4, 5)	would return "$E$4"
=ADDRESS(4, 5, 1)	would return "$E$4"
=ADDRESS(4, 5, 2)	would return "E$4"
=ADDRESS(4, 5, 3)	would return "$E4"
=ADDRESS(4, 5, 4)	would return "E4"
=ADDRESS(4, 5, 1, TRUE)	would return "$E$4"
=ADDRESS(4, 5, 1, FALSE)	would return "R4C5"
=ADDRESS(4, 5, 1, TRUE, "Sheet1")	would return "Sheet1!$E$4"

Excel Custom Cell Formats

Learning how to customise a cell format in Excel allows you to not only format your data the way you want, but in some instances it can save you time.

Before we dive in you need to know that despite how the text appears after you’ve set your custom cell format, the underlying value is unchanged for the purpose of formulas and calculations.

How to enter a custom cell format

Select the cell/s you want to format then open the Format Cells window.

• The quick way just press CTRL+1
• Or the way most people do it is to right click and select ‘Format Cells’.
• On the Number Tab select Custom from the Category list.

Note: It’s handy to have the text you want to format in the cell before you press CTRL+1 because Excel will give you a sample view of what the text is going to look like in the Format Cells window, so you can see before pressing OK, if it’s what you want.

How to make your cell formats look the way you want

Custom Cell Formats	Text Before Formatting	Custom Format	Formatted Text
Brackets for negative values	-500	#,##0;(#,##0)	(500)
Red and brackets for negative values	-500	#,##0.00;[Red](#,##0.00)	(500.00)
Day of the week in full	27/03/2010	dddd	Saturday
Day, date, month and year	27/03/2010	ddd dd mmm yyyy	Sat 27 Mar 2010
Month	27/03/2010	mmmm	March
Phone Numbers	755551234	00 0000 0000	07 5555 1234
Phone Numbers with Brackets	755551234	(00) 0000 0000	(07) 5555 1234
Fractions	10.5	# ??/??	10  1/2

How to save time with Custom Cell Formats

1)      From time to time I create a reference sheet like a contacts list, an index or even just a list of items like the one below using the custom cell format @*.

Because the text in the first column is often different lengths it can be hard for the eye to follow across.  In these cases I like to use trailing dots to help the reader.

I wouldn’t dream of manually entering the dots but since I can create a custom format it’s worth it, plus I think it looks more elegant that using borders for this purpose as they can get a bit busy.

The custom cell format for trailing dots is @*. When you type in your text Excel will automatically enter the dots to fill to the end of the cell.

Tip: You’re not just limited to dots.  You can have —- or **** or ____ or almost anything you want.  Just replace the dot in the custom format with the character of your choice.

2)      The other custom format I use regularly is prefixing my data with text.  For example, I keep a record of our invoices and instead of typing ‘INV’ before each number I enter I use a custom cell format like this: “INV” 0000

Then when I type in 597 Excel converts it to INV 0597.

Tip: Replace INV with different text to suit your needs.  It might be PO for purchase order, or any other text you can think of.  Or make the text a suffix by changing the custom format to 0000 “INV”.

Remember that even though the text appears to be INV 0597, for the purpose of formulas it’s still just a number 597.

Formatting cells for credit card numbers

You might be thinking you can use a custom format of 0000 0000 0000 0000 for credit card numbers, but you’ll find that it will only work for cards where the last number of the card is a zero! Try it out and see for yourself.

The workaround is to use a formula.  This requires entering the number in one cell, and then in another cell you need to enter the following formula (assuming our credit card number is in cell A1):

=LEFT(A1,4) & ” ” & MID(A1,5,4) & ” ” & MID(A1,9,4) & ” ” & RIGHT(A1,4)

Note: I’ve added spaces in the formula for clarity.

Some explanation:

• The LEFT, MID and RIGHT returns text from a specified position in a cell.
• The ampersands ‘&’ join text together
• The “ “ adds a space between each group of text

Download the quick reference table below and print it off or save it for future use.

Did you like this tutorial or could it have been better? Let me know your thoughts in the comments below.

Share this with your friends and colleagues on Twitter, and Facebook etc. using the shortcuts below.