Posts

Showing posts from August, 2012

Excel Date & Time Functions

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

Loop Structures

Image
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 keyw…

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

PressTo Do ThisPressTo Do ThisAlt+F11Open the VBA editorF1Get help on any selected itemAlt+F8Display a list of macrosF2Display the Object Browser windowCtrl+BreakPerform an emergency stop of the programF4Display the Properties windowCtrl+GDisplay the Immediate windowF5Start the programCtrl+IList the quick information for the selected elementF7Display the Code window after selecting a form or controlCtrl+JList the properties and methods for an objectF9Add a breakpointCtrl+RDisplay Project ExplorerShift+F7Display the form that corresponds to the active Code windowCtrl+Shift+IList the parameter information for the selected elementShift+F9Add a quick watch for the highlighted textCtrl+Shift+JList the constants associated with an enumerationCtrl+TabMove 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

Unique Data

Image
{=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: ValueExplanation1Absolute referencing.
For example: $A$1
2Absolute row; relative column.
For example: $A1
3Relative row; absolute column.
For example: A$1
4Relative 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: ValueExplanationTRUEA1 style referencingFALSER1C1 style referencingIf 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 …

Excel Custom Cell Formats

Image
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 formatSelect the cell/s you want to format then open the Format Cells window. The quick way just press CTRL+1Or 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 FormatsText Before FormattingCustom FormatFormatted TextBrackets for negative …