Topics

91

Shortcut Key / Microsoft Windows shortcut keys

« on: September 04, 2012, 05:00:09 PM »

elow is a listing of all the major Windows shortcut keys and the versions of Microsoft Windows they are supported in. See the computer shortcut page if you are looking for other shortcut keys or the how to navigate Windows using a keyboard page for steps on using the keyboard to navigate Windows.
Shortcut Keys   3.x   9x   ME   NT   2K   XP   Vista   7   Description
Alt + Tab   X   X   X   X   X   X   X   X   Switch between open applications.
Alt + Shift + Tab   X   X   X   X   X   X   X   X   Switch backwards between open applications.
Alt + double-click      X   X   X   X   X   X   X   Display the properties of the object you double-click on. For example, doing this on a file would display its properties.
Ctrl + Tab   X   X   X   X   X   X   X   X   Switches between program groups or document windows in applications that support this feature.
Ctrl + Shift + Tab   X   X   X   X   X   X   X   X   Same as above but backwards.
Alt + Print Screen   X   X   X   X   X   X   X   X   Create a screen shot only for the program you are currently in.
Ctrl + Print Screen                   X   X   X   X   Creates a screen shot of the entire screen
Ctrl + Alt + Del   X   X   X   X   X   X   X   X   Reboot the computer and brings up the Windows task manager.
Ctrl + Shift + Esc                   X   X   X   X   Immediately bring up the Windows task manager.
Ctrl + Esc   X   X   X   X   X   X   X   X   Bring up the Windows Start menu. In Windows 3.x this would bring up the Task Manager.
Alt + Esc      X   X   X   X   X   X   X   Switch Between open applications on taskbar.
F1   X   X   X   X   X   X   X   X   Activates help for current open application.
F2   X   X   X   X   X   X   X   X   Renames selected Icon.
F3      X   X   X   X   X   X   X   Starts find from desktop.
F4      X   X   X   X   X   X   X   Opens the drive selection when browsing.
F5      X   X   X   X   X   X   X   Refresh Contents to where you were on the page.
Ctrl + F5                           X   X   Refreshes page to the beginning of the page.
F10   X   X   X   X   X   X   X   X   Activates menu bar.
Shift + F10      X   X   X   X   X   X   X   Simulates right-click on selected item.
F4      X   X   X   X   X   X   X   Select a different location to browse in the Windows Explorer toolbar.
Alt + F4   X   X   X   X   X   X   X   X   Closes Current open program.
Ctrl + F4   X   X   X   X   X   X   X   X   Closes Window in Program.
F6      X   X   X   X   X   X   X   Move cursor to different Windows Explorer pane.
Alt + Space bar   X   X   X   X   X   X   X   X   Drops down the window control menu.
Ctrl + (the '+' key on the keypad)         X   X   X   X   X   X   Automatically adjust the widths of all the columns in Windows explorer
Alt + Enter      X   X   X   X   X   X   X   Opens properties window of selected icon or program.
Alt + Space bar       X   X   X   X   X   X   X   Open the control menu for the current window open.
Shift + Del       X   X   X   X   X   X   X   Delete programs/files without throwing them into the recycle bin.
Holding Shift   X   X   X   X   X   X   X   X   Boot Safe Mode or by pass system files as the computer is booting.
Holding Shift      X   X   X   X   X   X   X   When putting in an audio CD, will prevent CD Player from playing.
Enter   X   X   X   X   X   X   X   X   Activates the highlighted program.
Alt + Down arrow      X   X   X   X   X   X   X   Display all available options on drop down menu.
* (on the keypad)      X   X   X   X   X   X   X   Expands all folders on the currently selected folder or drive in Windows Explorer.
+ (on the keypad)      X   X   X   X   X   X   X   Expands only the currently selected folder in Windows Explorer.
- (on the keypad)      X   X   X   X   X   X   X   Collapses the currently selected folder in Windows Explorer.

Windows key keyboard shortcuts

Below is a listing of Windows keys that can be used on computers running a Microsoft Windows operating system and using a keyboard with a Windows key. In the below list of shortcuts, the Windows key is represented by "WINKEY".
Shortcut Keys   Description
WINKEY   Pressing the Windows key alone will open Start.
WINKEY + F1   Opens the Microsoft Windows help and support center.
WINKEY + F3   Opens the Advanced find window in Microsoft Outlook.
WINKEY + D    Brings the desktop to the top of all other windows.
WINKEY + M   Minimizes all windows.
WINKEY + SHIFT + M   Undo the minimize done by WINKEY + M and WINKEY + D.
WINKEY + E   Open Microsoft Explorer.
WINKEY + Tab   Cycle through open programs through the taskbar.
WINKEY + F   Display the Windows Search or Find feature.
WINKEY + CTRL + F    Display the search for computers window.
WINKEY + R   Open the run window.
WINKEY + Pause / Break key    Open the System Properties window.
WINKEY + U   Open Utility Manager.
WINKEY + L   Lock the computer and switch users if needed (Windows XP and above only).
WINKEY + P   Quickly change between monitor display types. (Windows 7 only)
WINKEY + LEFT ARROW   Shrinks the window to 1/2 screen on the left side for side by side viewing. (Windows 7 only)
WINKEY + RIGHT ARROW   Shrinks the window to 1/2 screen on the right side for side by side viewing. (Windows 7 only)
WINKEY + UP ARROW   When in the side by side viewing mode, this shortcut takes the screen back to full size. (Windows 7 only)
WINKEY + DOWN ARROW   Minimizes the screen.  Also, when in the side by side viewing mode, this shortcut takes the screen back to a minimized size. (Windows 7 only)

92

Shortcut Key / Apple Macintosh shortcut keys

« on: September 04, 2012, 04:59:26 PM »

Note that not all of the below shortcut keys will work in all versions of Mac OS.
Open Apple + Down    Opens the selected icon.
Shift + Click   Select the icon and add it to the set of those selected.
Tab   Highlight the next icon inside the folder, in alphabetical order.
Shift + Tab   Highlight the previous icon inside the folder, in alphabetical order.
Left arrow   Used when viewing by icon to select the icon to the left of the one highlighted.
Right arrow   Used when viewing by icon to select icon to the right of the one highlighted.
Up arrow   Used to select the icon above the one currently highlighted.
Down arrow   Used to select the icon below the one currently highlighted.
Open Apple + ?   Mac help
Open Apple + E   Eject
Open Apple + Shift + Up Arrow    Used to direct the input focus to the desktop level.
Open Apple + M   Minimize window
Open Apple + N   New finder window
Open Apple + Shift + N   New folder
Open Apple + W   Close the current window.
Open Apple + C   Copy the elected item to the clipboard.
Open Apple + X    Cut the selected item.
Open Apple + V   Paste item from the clipboard
Open Apple + L   Make alias
Open Apple + R   Show original item
Open Apple + T   Add to favorites
Open Apple + O   Open the selected icon.
Open Apple + F   Display the find dialog box.
Open Apple + G   Repeat the last find operation.
Open Apple + Shift + G   Takes a snapshot of the screen and saves it to a PICT file.

93

Shortcut Key / Unix and Linux shortcut keys

« on: September 04, 2012, 04:59:02 PM »

Keyboard shortcut keys

CTRL + B   Moves the cursor backward one character.
CTRL + C   Cancels the currently running command.
CTRL + D   Logs out of the current session.
CTRL + F   Moves the cursor forward one character.
CTRL + H   Erase one character. Similar to pressing backspace.
CTRL + P   Paste previous line(s).
CTRL + S   Stops all output on screen (XOFF).
CTRL + Q   Turns all output stopped on screen back on (XON).
CTRL + U   Erases the complete line.
CTRL + W   Deletes the last word typed in. For example, if you typed 'mv file1 file2' this shortcut would delete file2.
CTRL + Z   Cancels current operation, moves back a directory or takes the current operation and moves it to the background. See bg command for additional information about background.

Command line shortcuts

In addition to the below command line shortcuts, it is also helpful to use the alias command that allows you to specify a keyword for frequently used commands or mistakes.

~   Moves to the user's home directory.
!!   Repeats the line last entered at the shell. See history command for previous commands.
!$   Repeats the last argument for the command last used. See history command for previous commands.
reset   Resets the terminal if terminal screen is not displaying correctly.
shutdown -h now   Remotely or locally shuts the system down.

94

Shortcut Key / Top 10 keyboard shortcuts everyone should know

« on: September 04, 2012, 04:58:32 PM »

Using keyboard shortcuts can greatly increase your productivity, reduce repetitive strain, and help keep you focused. For example, highlighting text with the keyboard and pressing Ctrl + C is much faster than taking your hand from the keyboard, highlighting the text using the mouse, clicking copy from the file menu, and then putting your hand back in place on the keyboard. Below are our top 10 keyboard shortcuts we recommend everyone memorize and use.

Ctrl + C or Ctrl + Insert

Copy the highlighted text or selected item.

Ctrl + V or Shift + Insert

Paste the text or object that's in the clipboard.

Ctrl + Z and Ctrl + Y

Undo any change. For example, if you cut text, pressing this will undo it. This can also often be pressed multiple times to undo multiple changes. Pressing Ctrl + Y would redo the undo.

Ctrl + F

Open the Find in any program. This includes your Internet browser to find text on the current page.

Alt + Tab or Alt + Esc

Quickly switch between open programs moving forward.

Tip: Press Ctrl + Tab to switch between tabs in a program.

Tip: Adding the Shift key to Alt + Tab or Ctrl + Tab will move backwards. For example, if you are pressing Alt + Tab and pass the program you want to switch to, press Alt + Shift + Tab to move backwards to that program.

Tip: Windows Vista and 7 users can also press the Windows Key + Tab to switch through open programs in a full screenshot of the Window.

Ctrl + Back space and Ctrl + Left or Right arrow

Pressing Ctrl + Backspace will delete a full word at a time instead of a single character.

Holding down the Ctrl key while pressing the left or right arrow will move the cursor one word at a time instead of one character at a time. If you wanted to highlight one word at a time you can hold down Ctrl + Shift and then press the left or right arrow key to move one word at a time in that direction while highlighting each word.

Ctrl + S

While working on a document or other file in almost every program pressing Ctrl + S will save that file. This shortcut key should be used frequently anytime you're working on anything important.

Ctrl + Home or Ctrl + End

Move the cursor to the beginning or end of a document.

Ctrl + P

Print the page being viewed. For example, the document in Microsoft Word or the web page in your Internet browser.

Page Up, Space bar, and Page Down

Pressing either the page up or page down key will move that page one page at a time in that direction. When browsing the Internet pressing the space bar will also move the page down one page at a time. If you press Shift and the Space bar the page will go up a page at a time.

95

Shortcut Key / What are the F1 through F12 keys?

« on: September 04, 2012, 04:58:07 PM »

Commonly known as "function keys", F1 through F12 may have a variety of different uses or no use at all. Depending on the installed operating system and the software program currently open will change how each of these keys operate. A program is capable of not only using each of the function keys, but also combining the function keys with the ALT or CTRL key, for example, Microsoft Windows users can press ALT + F4 to close the program currently active.

Keyboard function keys

Below is a short-listing of some of the common functions of the functions keys on computers running Microsoft Windows. As mentioned above not all programs support function keys and may perform different tasks then those mentioned below. If you are looking for more specific shortcut keys and function key examples we suggest seeing our shortcut key page.

F1

    Almost always used as the help key, almost every program will open the help screen when this key is pressed.
    Enter CMOS Setup.
    Windows Key + F1 would open the Microsoft Windows help and support center.
    Open the Task Pane.

F2

    In Windows commonly used to rename a highlighted icon or file.
    Alt + Ctrl + F2 opens document window in Microsoft Word.
    Ctrl + F2 displays the print preview window in Microsoft Word.
    Enter CMOS Setup.

F3

    Often opens a search feature for many programs including Microsoft Windows.
    In MS-DOS or Windows command line F3 will repeat the last command.
    Shift + F3 will change the text in Microsoft Word from upper to lower case or a capital letter at the beginning of every word.

F4

    Open find window.
    Repeat the last action performed (Word 2000+)
    Alt + F4 will close the program currently active in Microsoft Windows.
    Ctrl + F4 will close the open window within the current active window in Microsoft Windows.

F5

    In all modern Internet browsers pressing F5 will refresh or reload the page or document window.
    Open the find, replace, and go to window in Microsoft Word.
    Starts a slideshow in PowerPoint.

F6

    Move the cursor to the Address bar in Internet Explorer and Mozilla Firefox.
    Ctrl + Shift + F6 opens to another open Microsoft Word document.

F7

    Commonly used to spell check and grammar check a document in Microsoft programs such as Microsoft Word, Outlook, etc.
    Shift + F7 runs a Thesaurus check on the word highlighted.
    Turns on Caret browsing in Mozilla Firefox.

F8

    Function key used to enter the Windows startup menu, commonly used to access Windows Safe Mode.

F9

    Opens the Measurements toolbar in Quark 5.0.

F10

    In Microsoft Windows activates the menu bar of an open application.
    Shift + F10 is the same as right-clicking on a highlighted icon, file, or Internet link.
    Access the hidden recovery partition on HP and Sony computers.
    Enter CMOS Setup.

F11

    Full-screen mode in all modern Internet browsers.
    Ctrl + F11 as computer is starting to access the hidden recovery partition on many Dell computers.
    Access the hidden recovery partition on eMachines, Gateway, and Lenovo computers.

F12

    Open the Save as window in Microsoft Word.
    Shift + F12 save the Microsoft Word document.
    Ctrl + Shift + F12 prints a document in Microsoft Word.
    Preview a page in Microsoft Expression Web
    Open Firebug.

Tip: Earlier IBM computers also had F13 - F24 on the keyboards. However, because these keyboards are no longer used they are not listed on this page.

96

Shortcut Key / Shortcut keys ABCs

« on: September 04, 2012, 04:57:27 PM »

Shortcut keys help provide an easier and usually quicker method of navigating and using computer software programs. Shortcut keys are commonly accessed by using the Alt (on IBM compatible computers), command key (on Apple computers), Ctrl, or Shift in conjunction with a single letter. The de facto standard for listing a shortcut is listing the modifier key, a plus symbol, and the single character. In other words, "ALT+S" is telling you to press the Alt key and while continuing to hold the Alt key, press the S key to perform the shortcut.

In addition to the shortcuts listed on this page, users can find the shortcut keys to their most popular program by looking for underlined letters in their menus. For example, in the picture to the right you'll can notice that the "F" in File has been underlined. This means you can press the Alt key and F to access the File menu. Note: Some programs require the user press and hold ALT to see the underlined characters. Finally, as can also be seen some of the common features such as Open (Ctrl+O) and Save (Ctrl+S) have shortcut keys assigned to them.

As you begin to memorize shortcut keys, you'll notice that many applications share the same shortcut keys. We have the most commonly shared shortcut keys in the below basic PC shortcut keys section.

Tip: Users outside the United States or users using a foreign copy of a Microsoft Windows or Microsoft application may not be able to get all the below shortcut keys to perform the function listed below.

Basic PC shortcut keys

The below basic shortcut keys are a listing of shortcut keys that will work with almost all IBM compatible computers and software programs. It is highly recommended that all users keep a good reference of the below shortcut keys or try to memorize the below keys. Doing so will dramatically increase your productivity.
Shortcut Keys   Description
Alt + F   File menu options in current program.
Alt + E   Edit options in current program
F1   Universal Help in almost every Windows program.
Ctrl + A   Select all text.
Ctrl + X   Cut selected item.
Shift + Del   Cut selected item.
Ctrl + C   Copy selected item.
Ctrl + Ins   Copy selected item
Ctrl + V   Paste
Shift + Ins   Paste
Home   Goes to beginning of current line.
Ctrl + Home   Goes to beginning of document.
End   Goes to end of current line.
Ctrl + End   Goes to end of document.
Shift + Home   Highlights from current position to beginning of line.
Shift + End   Highlights from current position to end of line.
Ctrl + Left arrow   Moves one word to the left at a time.
Ctrl + Right arrow    Moves one word to the right at a time.

97

Microsoft Office Excel / Using Loops in VBA in Microsoft Excel

« on: August 15, 2012, 06:03:34 PM »

The purpose of a loop is to get Excel to repeat a piece of code a certain number of times. How many times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this for as many times as there are rows of data).

Loops can be constructed many different ways to suit different circumstances. Often the same result can be obtained in different ways to suit your personal preferences. These exercises demonstrate a selection of different ways to use loops.

There are two basic kinds of loops, both of which are demonstrated here: Do…Loop and For…Next loops. The code to be repeated is placed between the key words.

Open the workbook VBA02-Loops.xls and take a look at the four worksheets. Each contains two columns of numbers (columns A and B). The requirement is to calculate an average for the numbers in each row using a VBA macro.

Now open the Visual Basic Editor (Alt+F11) and take a look at the code in Module1.  You will see a number of different macros. In the following exercises, first run the macro then come and read the code and figure out how it did what it did.

You can run the macros either from the Visual Basic Editor by placing your cursor in the macro and pressing the F5 key, or from Excel by opening the Macros dialog box (ALT+F8) choosing the macro to run and clicking Run. It is best to run these macros from Excel so you can watch them as they work.
Exercise 1: Do… Loop Until…

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

On Sheet1 select cell C2 and run the macro Loop1.

Here's the code:

Sub Loop1()

' This loop runs until there is nothing in the next column

    Do

    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"

    ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

This macro places a formula into the active cell, and moves into the next cell down. It uses Loop Until to tell Excel to keep repeating the code until the cell in the adjacent column (column D) is empty. In other words, it will keep on repeating as long as there is something in column D.

Delete the data from cells C2:C20 and ready for the next exercise
Exercise 2: Do While… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

On Sheet1 select cell C2 and run the macro Loop2

Here's the code

Sub Loop2()

' This loop runs as long as there is something in the next column

    Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"

    ActiveCell.Offset(1, 0).Select

    Loop

End Sub

This macro does the same job as the last one using the same parameters but simply expressing them in a different way. Instead of repeating the code Until something occurs, it does something While something is the case. It uses Do While to tell Excel to keep repeating the code while there is something in the adjacent column as opposed to until there is nothing there. The function IsEmpty = False means "Is Not Empty".

Delete the data from cells C2:C20 and ready for the next exercise
Exercise 3: Do While Not… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

On Sheet1 select cell C2 and run the macro Loop3.

Here's the code:

Sub Loop3()

' This loop runs as long as there is something in the next column

    Do While Not IsEmpty(ActiveCell.Offset(0, 1))

    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"

    ActiveCell.Offset(1, 0).Select

    Loop

End Sub

This macro makes exactly the same decision as the last one but just expresses it in a different way. IsEmpty = False means the same as Not IsEmpty. Sometimes you can't say what you want to say one way so VBA often offers an alternative syntax.

Delete the data from cells C2:C20 and ready for the next exercise
Exercise 4: Including an IF statement

The object of this macro is as before, but without replacing any data that may already be there.

Move to Sheet2, select cell C2 and run the macro Loop4.

Here's the code:

Sub Loop4()

' This loop runs as long as there is something in the next column

' It does not calculate an average if there is already something in the cell

    Do

    If IsEmpty(ActiveCell) Then

        ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"

    End If

    ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

The previous macros take no account of any possible contents that might already be in the cells into which it is placing the calculations. This macro uses an IF statement that tells Excel to write the calculation only if the cell is empty. This prevents any existing data from being overwritten. The line telling Excel to move to the next cell is outside the IF statement because it has to do that anyway.
Exercise 5: Avoiding Errors

This macro takes the IF statement a stage further, and doesn't try to calculate an average of cells that are empty.

First, look at the problem. Move to Sheet3, select cell C2 and run the macro Loop4.

Note that because some of the pairs of cells in columns A and B are empty, the =AVERAGE function throws up a #DIV/0 error (the Average function adds the numbers in the cells then divides by the number of numbers - if there aren't any numbers it tries to divide by zero and you can't do that!).

Delete the contents of cells C2:C6 and C12:C20. Select cell C2 and run the macro Loop5.

Here's the code:

Sub Loop5()

' This loop runs as long as there is something in the next column

' It does not calculate an average if there is already something in the cell

' nor if there is no data to average (to avoid #DIV/0 errors).

    Do

    If IsEmpty(ActiveCell) Then

        If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then

            ActiveCell.Value = ""

        Else

            ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"

        End If

    End If

    ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

Note that this time there are no error messages because Excel hasn't tried to calculate averages of numbers that aren't there.

In this macro there is a second IF statement inside the one that tells Excel to do something only if the cell is empty. This second IF statement gives excel a choice. Instead of a simple If there is an If and an Else. Here's how Excel reads its instructions…

"If the cell has already got something in, go to the next cell. But if the cell is empty, look at the corresponding cells in columns A an B and if they are both empty, write nothing (""). Otherwise, write the formula in the cell. Then move on to the next cell."
Exercise 6: For… Next Loop

If you know, or can get VBE to find out, how many times to repeat a block of code you can use a For… Next loop.

Move to Sheet4, select cell C2 and run the macro Loop6.

Here's the code:

Sub Loop6()

' This loop repeats for a fixed number of times determined by the number of rows

' in the range

    Dim i AsInteger

    For i = 1 To Selection.CurrentRegion.Rows.Count - 1

    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"

    ActiveCell.Offset(1, 0).Select

    Next i

End Sub

This macro doesn't make use of an adjacent column of cells like the previous ones have done to know when to stop looping. Instead it counts the number of rows in the current range of data and uses the For… Next method to tell Excel to loop that number of times (minus one, because when VBA counts it starts at zero).
Exercise 7: Getting the Reference From Somewhere Else

Select cell G2 and run the macro Loop7.

Here's the code:

Sub Loop7()

' This loop repeats a fixed number of times getting its reference from elsewhere

    Dim i As Integer

    Dim intRowCount As Integer

    intRowCount = Range("A1").CurrentRegion.Rows.Count - 1

    For i = 1 To intRowCount

    ActiveCell.FormulaR1C1 = "=Average(RC[-5],RC[-6])"

    ActiveCell.Offset(1, 0).Select

    Next i

End Sub

You can get the reference for the number of loops from anywhere. This macro places a set of calculations in column G for a number of times dictated by the number of rows in the block of data starting with cell A1. The For… Next statement has been simplified a bit by first declaring a variable intRowCount and filling it with the appropriate information (how many rows in the block by A1). This variable gets used in the next line instead of a long line of code. This is just another example of doing the same job a different way.

If you wanted to construct a loop that always ran a block of code a fixed number of times, you could simply use an expression like:

            For i = 1 To 23
Exercise 8: About Doing Calculations…

All the previous exercises have placed a calculation into a worksheet cell by actually writing a regular Excel function into the cell (and leaving it there) just as if you had typed it yourself. The syntax for this is:

ActiveCell.FormulaR1C1 = “TYPE YOUR FUNCTION HERE”

These macros have been using:

ActiveCell.FormulaR1C1 = “=Average(RC[-5],RC[-6])”

Because this method actually places a function into the cell rather than a value, their results will change as the cells that they refer to change, just like regular functions – because they are regular functions. The calculating gets done in Excel because all that the macro did was to write the function.

If you prefer, you can get the macro to do the calculating and just write the result into the cell. VBA has its own set of functions, but unfortunately AVERAGE isn’t one of them. However, VBA does support many of the commoner Excel functions with its WorksheetFunction method.

On Sheet1 select cell C2 and run the macro Loop1.

Take a look at the cells you just filled in. Each one contains a function, written by the macro.

Now delete the contents from the cells C2:C20, select cell C2 and run the macro Loop8.

Here’s the code:

Sub Loop8()

    Do

    ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(0, -1).Value, _

         ActiveCell.Offset(0, -2).Value)

    ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

Take a look at the cells you just filled in. This time there’s no function, just the value. All the calculating was done by the macro which then wrote the value into the cell.

98

Microsoft Office Excel / Create User Forms in Microsoft Excel

« on: August 15, 2012, 06:02:21 PM »

The Course Booking Form

The Course Booking Form is a simple form illustrating the principles of UserForm design and the associated VBA coding.

It uses a selection of controls including text boxes, combo boxes, option buttons grouped in a frame, check boxes and command buttons.

When the user clicks the OK button their input is entered into the next available row on the worksheet.

Description of the Form:

There are two simple text boxes (Name: and Phone:) into which the user can type free text, and two combo boxes (Department and Course) that let the user to pick an item from the list.

There are three option buttons (Introduction, Intermediate and Advanced) grouped in a frame (Level) so that the user can choose only one of the options.

There are two check boxes (Lunch Required and Vegetarian) that, because they are not grouped in a frame, can both be chosen if required. However, if the person making the booking does not want lunch we do not need to know whether or not they are vegetarian. So, the Vegetarian check box is greyed-out until required.

There are three command buttons (OK, Cancel and Clear Form) each of which performs a pre-defined function when clicked.

The Control Properties Settings:

Control
   

Type
   
Property
   

Setting

UserForm
   

UserForm
   

Name
   

frmCourseBooking
       

Caption
   

Course Booking Form

Name
   

Text Box
   

Name
   

txtName

Phone
   

Text Box
   

Name
   

txtPhone

Department
   

Combo Box
   

Name
   

cboDepartment

Course
   

Combo Box
   

Name
   

cboCourse

Level
   

Frame
   

Name
   

fraLevel
       

Caption
   

Level

Introduction
   

Option Button
   

Name
   

optIntroduction

Intermediate
   

Option Button
   

Name
   

optIntermediate

Advanced
   

Option Button
   

Name
   

optAdvanced

Lunch Required
   

Check Box
   

Name
   

chkLunch

Vegetarian
   

Check Box
   

Name
   

chkVegetarian
       

Enabled
   

False

OK
   

Command Button
   

Name
   

cmdOk
         

Caption
   

OK
       

Default
   

True

Cancel
   

Command Button
   

Name
   

cmdCancel
       

Caption
   

Cancel
       

Cancel
   

True

Clear Form
   

Command Button
   

Name
   

cmdClearForm
 
Building the Form

If you want to build the form yourself, simply copy the layout shown in the illustration above. Follow the steps below:

1.       Open the workbook that you want the form to belong in (UserForms like macros have to be attached to a workbook) and switch to the Visual Basic Editor.

2.       In the Visual Basic Editor click the Insert UserForm button (or go to Insert > UserForm).

3.       If the toolbox does not appear by itself (first click the form to make sure it isn’t hiding) click the Toolbox button (or go to View > Toolbox).

4.       To place a control on the form click the appropriate button on the toolbox then click the form. Controls can be moved by dragging them by their edges, or resized by dragging the buttons around their perimeter.

5.       To edit the properties of a control, make sure the chosen control is selected then make the appropriate changes in the Properties window. If you can’t see the properties window go to View > Properties Window.

6.       To remove a control from the form, select it and click the Delete key on your keyboard.

A UserForm will not actually do anything until the code that drives the form and its various controls is created. The next step is to write the code that drives the form itself.
Adding the Code: 1 Initialising the Form
Initialising the Form:
Most forms need some kind of setting up when they open. This may be setting default values, making sure field are empty, or building the lists of combo boxes. This process is called Initialising the Form and it is taken care of by a macro called UserForm_Initialize (in case you are confused by my varying spelling of the word "initialis(z)e", it's because I speak English and VBA speaks American - but don't worry, VBA will spell it for you!). Here's how to build the code to initialise the Course Booking Form:

1.       To view the form’s code window go to View > Code or click F7.

2.       When the code window first opens it contains an empty UserForm_Click() procedure. Use the drop-down lists at the top of the code window to choose UserForm and Initialize. This will create the procedure you need. You can now delete the UserForm_Click() procedure.

3.       Enter the following code into the procedure:

Private Sub UserForm_Initialize()

    txtName.Value = ""

    txtPhone.Value = ""

    With cboDepartment

        .AddItem "Sales"

        .AddItem "Marketing"

        .AddItem "Administration"

        .AddItem "Design"

        .AddItem "Advertising"

        .AddItem "Dispatch"

        .AddItem "Transportation"

    End With

    cboDepartment.Value = ""

    With cboCourse

        .AddItem "Access"

        .AddItem "Excel"

        .AddItem "PowerPoint"

        .AddItem "Word"

        .AddItem "FrontPage"

    End With

    cboCourse.Value = ""

    optIntroduction = True

    chkLunch = False

    chkVegetarian = False

    txtName.SetFocus

End Sub

How the Initialise Code Works:

The purpose of the UserForm_Initialize() procedure is to prepare the form for use, setting the default values for the various controls and creating the lists that the combo boxes will show.

These lines set the contents of the two text boxes to empty:

txtName.Value = ""

txtPhone.Value = ""

Next come the instructions for the combo boxes. First of all the contents of the list are specified, then the initial value of the combo box is set to empty.

With cboDepartment

    .AddItem "Sales"

    .AddItem "Marketing"

    (as many as necessary…)

End With

 cboDepartment.Value = ""

If required an initial choice can be made from the option group, in this case:

optIntroduction = True

Both check boxes are set to False (i.e. no tick). Set to True if you want the check box to appear already ticked:

chkLunch = False

chkVegetarian = False

Finally, The focus is taken to the first text box. This places the users cursor in the text box so that they do not need to click the box before they start to type:

txtName.SetFocus
Adding the Code: 2 Making the Buttons Work

There are three command buttons on the form and each must be powered by its own procedure. Starting with the simple ones…

Coding the Cancel Button:

Earlier, we used the Properties Window to set the Cancel property of the Cancel button to True. When you set the Cancel property of a command button to True, this has the effect of "clicking" that button when the user presses the Esc key on their keyboard. But this alone will not cause anything to happen to the form. You need to create the code for the click event of the button that will, in this case, close the form. Here's how:

1.       With the UserForm open for editing in the Visual Basic Editor, double-click the Cancel button. The form's code window opens with the cmdCancel_Click() procedure ready for editing.

2.       The code for closing a form is very simple.  Add a line of code to the procedure so it looks like this:

Private Sub cmdCancel_Click()

    Unload Me

End Sub

Coding the Clear Form Button:

I added a button to clear the form in case the user wanted to change their mind and reset everything, and to make it easier if they had several bookings to make at one time. All it has to do is run the Initialise procedure again. A macro can be told to run another macro (or series of macros if necessary) by using the Call keyword:

1.       Double-click the Clear Form button. The form's code window opens with the cmdClearForm_Click() procedure ready for editing.

2.       Add a line of code to the procedure so it looks like this:

Private Sub cmdClearForm_Click()

    Call UserForm_Initialize

End Sub

Coding the OK Button:

This is the piece of code that has to do the job of transferring the user's choices and text input on to the worksheet. When we set the Cancel button's Cancel property to True we also set the OK button's Default property to True. This has of clicking the OK button when the user presses the Enter (or Return) key on their keyboard (providing they have not used their Tab key to tab to another button). Here's the code to make the button work:

1.       Double-click the OK button. The form's code window opens with the cmdOK_Click() procedure ready for editing.

2.       Edit the procedure to add the following code:

Private Sub cmdOK_Click()

    ActiveWorkbook.Sheets("Course Bookings").Activate

    Range("A1").Select

    Do

    If IsEmpty(ActiveCell) = FalseThen

        ActiveCell.Offset(1, 0).Select

    End If

    Loop Until IsEmpty(ActiveCell) = True

    ActiveCell.Value = txtName.Value

    ActiveCell.Offset(0, 1) = txtPhone.Value

    ActiveCell.Offset(0, 2) = cboDepartment.Value

    ActiveCell.Offset(0, 3) = cboCourse.Value

    If optIntroduction = True Then

        ActiveCell.Offset(0, 4).Value = "Intro"

    ElseIf optIntermediate = True Then

        ActiveCell.Offset(0, 4).Value = "Intermed"

    Else

        ActiveCell.Offset(0, 4).Value = "Adv"

    End If

    If chkLunch = True Then

        ActiveCell.Offset(0, 5).Value = "Yes"

    Else

        ActiveCell.Offset(0, 5).Value = "No"

    End If

    If chkVegetarian = True Then

        ActiveCell.Offset(0, 6).Value = "Yes"

    Else

        If chkLunch = False Then

        ActiveCell.Offset(0, 6).Value = ""

        Else

        ActiveCell.Offset(0, 6).Value = "No"

        End If

    End If

    Range("A1").Select

End Sub

How the CmdOK_Click code works:

The first two lines make sure that the correct workbook is active and moves the selection to cell A1:

ActiveWorkbook.Sheets("Course Bookings").Activate

Range("A1").Select

The next few lines moves the selection down the worksheet until it finds an empty cell:

Do

If IsEmpty(ActiveCell) = False Then

    ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

The next four lines start to write the contents of the form on to the worksheet, using the active cell (which is in column A) as a reference and moving along the row a cell at a time:

ActiveCell.Value = txtName.Value

ActiveCell.Offset(0, 1) = txtPhone.Value

ActiveCell.Offset(0, 2) = cboDepartment.Value

ActiveCell.Offset(0, 3) = cboCourse.Value

Now we come to the option buttons. These have been placed in a frame on the form so the user can choose only one. An IF statement is used to instruct Excel what to for each option:

If optIntroduction = True Then

    ActiveCell.Offset(0, 4).Value = "Intro"

ElseIf optIntermediate = True Then

    ActiveCell.Offset(0, 4).Value = "Intermed"

Else

    ActiveCell.Offset(0, 4).Value = "Adv"

End If

VBA IF statements are much easier to manage than Excel's IF function. You can have as many options as you want, just insert an additional ElseIf for each one. If there were only two options, you wouldn't need the ElseIf, just the If and Else would suffice (don't forget - they all need an End If).

There is another IF statement for each check box. For the Lunch Required check box, a tick in the box means "Yes" the person requires lunch, and no tick means "No" they don't.

If chkLunch = True Then

    ActiveCell.Offset(0, 5).Value = "Yes"

Else

    ActiveCell.Offset(0, 5).Value = "No"

End If

We could use a similar IF statement for the Vegetarian check box, but if the person does not require lunch it is irrelevant whether or not they are vegetarian. I any case, it would be wrong to assume that they were not vegetarian simply because they did not require lunch. The IF statement therefore contains a second, nested if statement:

If chkVegetarian = True Then

    ActiveCell.Offset(0, 6).Value = "Yes"

Else

    If chkLunch = False Then

        ActiveCell.Offset(0, 6).Value = ""

    Else

        ActiveCell.Offset(0, 6).Value = "No"

    End If

End If

A tick in the box means "Yes" the person is vegetarian.  If there is no tick in the box, the nested IF statement looks at the Lunch Required check box. If the Lunch Required check box has a tick in it then no tick in the Vegetarian check box means that the person is not vegetarian so it inserts "No" into the cell. However, if the Lunch Required check box does not have a tick in it, then we do not know whether or not the person is vegetarian (it doesn't matter anyway) so the cell is left blank ("").

Finally the selection is taken back to the beginning of the worksheet, ready for the next entry:

Range("A1").Select
Adding the Code 3: Manipulating the Form

Finally, an example of how the controls on a form can be manipulated whilst it is in use. When the control properties were set, the Enabled property of the Vegetarian check box was set to False. When a control is not enabled the user cannot enter a value into it, although it can hold a value that was there already, and VBA can add, remove or change the value.

We don't need to know whether or not the person is vegetarian (even if they are!) if they aren't ordering lunch. So, the Vegetarian check box remains disabled unless a tick is placed in the Lunch Required check box. Then the user is free to tick the Vegetarian check box if they want to. If they tick it we will know that they have answered "Yes" and if they don't we know they have answered "No".

We can toggle the Enabled property from False to True by having a procedure that runs automatically whenever the value of the Lunch Required check box changes. Fortunately, more controls have a  Change procedure and the one we use here is chkLunch_Change(). We'll use this to enable the Vegetarian check box when the Lunch Required check box is ticked, and disable it when the Lunch Required check box is not ticked.

There's just one more thing we need to do. Supposing someone ticked the Lunch Required check box, and also ticked the Vegetarian check box. Then they changed their mind and removed the tick from the Lunch Required check box. The Vegetarian check box would be disabled but the tick that was put in earlier would remain.

An extra line of code can make sure the tick is removed when the box is disabled. Here's the whole thing:

Private Sub chkLunch_Change()

    If chkLunch = True Then

        chkVegetarian.Enabled = True

    Else

        chkVegetarian.Enabled = False

        chkVegetarian = False

    End If

End Sub
Opening the Form

The form is now ready for use so it needs to be opened with a simple macro. That can be attached to a custom toolbar button, a command button drawn on the worksheet, or any graphic (right click the graphic and choose Assign Macro). If necessary, create a new module for the workbook and add this procedure:

Sub OpenCourseBookingForm()

    frmCourseBooking.Show

99

Microsoft Office Excel / The Best Shortcut Keys in Microsoft Excel

« on: August 15, 2012, 06:01:36 PM »

Ctrl+Z   Undo
Ctrl+C

Enter,
Ctrl+V
Ctrl+X   Copy,

Paste,
Multiple Paste,
Cut
Ctrl+F,
Ctrl+H   Find,
Find&Replace
Ctrl+P,
Ctrl+S, Ctrl+F4, Alt+F4   Print,
Save, Close,
Close
Excel
Ctrl+Arrow   Move
to edge of region
Ctrl+*
   Select
current region
Ctrl+A   Select
all cells
Ctrl+Home
Ctrl+End   Select
A1,
Select
last cell in used range
Ctrl+Shift+End   Select
from active cell to last cell in used range.
Ctrl+Shift+Home   Select
from active cell to A1
Ctrl+Page
Down
Ctrl+Page
Up   Move
to the next sheet,
Move
to the previous sheet
Ctrl+Tab   Move
to next open workbook
Ctrl+N   Open
new workbook
Shift+F11   Insert
new worksheet
Shift+F3   Paste
function window
=+FunctionName+Ctrl+A   Insert
new function
Alt+F11   Open
VBE
Ctrl+Shift+Enter   Array
formula
Ctrl+F3,
F3   Define
name, Paste name
Ctrl+Spacebar
Shift+Spacebar   Select
columns, Select rows
Ctrl+1,
Ctrl+B, Ctrl+U   Format
cells, Bold, Underline
Ctrl+;
, Ctrl+shift+:   Current
date, Current time

100

Microsoft Office Excel / Writing Your First VBA Function in Excel

« on: August 15, 2012, 05:59:44 PM »

About User Defined Functions

Excel provides the user with a large collection of ready-made functions, more than enough to satisfy the average user. Many more can be added by installing the various add-ins that are available.

Most calculations can be achieved with what is provided, but it isn't long before you find yourself wishing that there was a function that did a particular job, and you can't find anything suitable in the list. You need a UDF.

A UDF (User Defined Function) is simply a function that you create yourself with VBA. UDFs are often called "Custom Functions". A UDF can remain in a code module attached to a workbook, in which case it will always be available when that workbook is open. Alternatively you can create your own add-in containing one or more functions that you can install into Excel just like a commercial add-in.

UDFs can be accessed by code modules too. Often UDFs are created by developers to work solely within the code of a VBA procedure and the user is never aware of their existence.

Like any function, the UDF can be as simple or as complex as you want. Let's start with an easy one...
A Function to Calculate the Area of a Rectangle

Yes, I know you could do this in your head! The concept is very simple so you can concentrate on the technique.

Suppose you need a function to calculate the area of a rectangle. You look through Excel's collection of functions, but there isn't one suitable. This is the calculation to be done:

AREA = LENGTH x WIDTH

Open a new workbook and then open the Visual Basic Editor (Tools > Macro > Visual Basic Editor or ALT+F11).

101

Microsoft Office Excel / Formulas to calculate Loan payments in Microsoft Excel

« on: August 15, 2012, 05:58:13 PM »

Formulas that calculate loan payments, principal, interest and more are found in the financial category in the Paste Function dialog box. In the screen shot is a list of functions and necessary syntax for loan calculations. See the formulas in the gray cells, and the syntax in rows 12-16.

Syntax for loan calculation functions

Rate - The interest rate per period.

Per - The period for which the interest rate is calculated.

Nper - The total number of payments.

Pv - The present value, the total amount that a series of future payments is worth now.

Type - The timing of the payment, either at the beginning or end of the period. Numbers 0 or 1 represent the payment date. The number 0 represents payment at the end of the period, and the number 1 represents payment at the beginning of the period. The default (empty argument) is 0. The calculation is at the end of the period.

PMT (Rate, Nper, -Loan Amount)

    Calculates the payment for a loan based on constant payments and a constant interest rate.
    Returns the regular monthly payment on the loan (principal + interest) when the interest for each of the monthly payments is constant.
    Example: The principal of a loan is $100,000, and the term of the loan is three years. The monthly payment during the term of the loan is calculated at $3,227; see column B in the figure below.

PPMT (Rate, Which Period, Nper, -Loan Amount)

    Returns the amount on the principal for a given period for a loan based on periodic, constant payments and a constant interest rate.
    Returns the sum of the principal within the monthly payment (the monthly payment is comprised of the principal + interest). See various examples of calculations in column C, rows 8:10, and the formula syntax in rows 14:16.
    IPMT (Rate, Which Period, Nper, -Loan Amount)
    Returns the interest payment for a given period for a loan based on periodic, constant payments and a constant interest rate.
    Returns the amount of the interest within the monthly payment (the monthly payment is comprised of the principal + interest). See the calculation in cell G11 and the formula syntax in G13.

NPER (Rate, Pmt, -Loan Amount)

    Returns the number of loan payments with a constant interest rate. See the formula syntax in D12.

RATE (Nper, Pmt, -Loan Amount)

    Returns the interest rate per period of a loan. RATE is calculated by iteration and can have zero or more solutions.
    Returns the percentage of interest on the loan, when the number of payments is constant.

PV (Rate, Nper, Pmt)

    PV is the present value — the total amount that a series of future payments is worth now.
    Returns the current value for a series of payments with a constant interest rat

102

Microsoft Office Excel / Array variables using VBA in Microsoft Excel

« on: August 15, 2012, 05:55:38 PM »

Static array variables

Insted of using several unique variables to store information, you can use an array variable.
When you know how many elements you need to store in the array, you can use a static array variable like this :

Sub TestStaticArray()
' stores 10 names in the workbook in the array variable MyNames()
Dim MyNames(1 to 10) As String ' declares a static array variable
Dim iCount As Integer
    For iCount = 1 To 10
        MyNames(iCount) = ThisWorkbook.Names(iCount)
    Next iCount
    Erase MyNames() ' deletes the varible contents, free some memory
End Sub

Dynamic array variables

Dynamic array variables are useful when you in advance don't know how many elements that you need to store information about.
You declare dynamic array variables just like a static rray variable, except that you don't give any information about the
array size :

Sub TestDynamicArray()
' stores all names in the workbook in the array variable MyNames()
Dim MyNames() As String ' declares a dynamic array variable
Dim iCount As Integer
Dim Max As Integer
    Max = ThisWorkbook.Names.Count ' finds the maximum array size
    ReDim MyNames(1 to Max) ' declares the array variable with the necessary size
    For iCount = 1 To Max
        MyNames(iCount) = ThisWorkbook.Names(iCount)
    Next iCount
    Erase MyNames() ' deletes the varible contents, free some memory
End Sub

If you know that you will need an array variable with 1000 items, use a static variable.
The downside is that you will use memory for a 1000 items every time, also in the cases that
you only stores information about 10 items. If you use a dynamic array variable you will use the memory more efficient..

Sometimes it's not possible to calculate how large tha array variable will need to be.
In these cases the size of the array variable need to be increased as necessary. When
you use a ReDim-statement to change the array variable size, the variable contents is also erased.
to avoid deleting the variable contents when you redim the array variable you will nedd to use the ReDim Preserve-statement :

Sub GetFileNameList()
' stores all the filenames in the current folder
Dim FolderFiles() As String ' declares a dynamic array variable
Dim tmp As String, fCount As Integer
    fCount = 0
    tmp = Dir("*.*")
    While tmp <> Empty
        fCount = fCount + 1
        ReDim Preserve FolderFiles(1 to fCount)
        ' declares the array variable again (size+1)
        FolderFiles(fCount) = tmp
        tmp = Dir
    Wend
    MsgBox fCount & " filenames are found in the folder " & CurDir
    Erase FolderFiles ' deletes the varible contents, free some memory
End Sub

103

Microsoft Office Excel / Converting Time to Decimal Values

« on: August 15, 2012, 05:54:50 PM »

To convert time to decimal values:

Use the HOUR and MINUTE formulas to extract the hour and minute numbers from the time.
Screenshot // Converting Time to Decimal Values

104

Microsoft Office Excel / Add Combo Box to a Sheet in Microsoft Excel

« on: August 15, 2012, 05:52:43 PM »

What are the Advantages of working with Combo Box?

Choosing an item from a list in the Combo Box returns a number. The number then replaces the use of the Match formula to calculate the column number in the Vlookup formula (read tip) and replaces the use of the Match formula (read tip) to calculate the column number in the Index formula.

Working with the Combo Box

A list of the cell ranges in the sheet is attached to the Combo Box. After an item is selected from the list, the Combo Box enters into the linked cell the ordinal number of the item that is selected on the list.



Example:

The screen shot displays a profit and loss statement as compared to the previous year. Changing the month in the Combo Box will automatically change the display of data according to the selected month.


Step 1: Define names

1.Enter the months list - January-December into the cells A1 to A12 in the new worksheet.

2.Select cells A!:A12, press Ctrl+F3, enter the text MonthsList into the Names In workbook box, and click OK.

3.Select Cell B1, press Ctrl+F3, enter the text MonthNumber into the Names in Workbook box, and click OK.


Step 2: Add a Combo Box to a worksheet

1. Select one of the toolbars, right-click, and select the Forms toolbar.

2. Copy the Combo Box by clicking the Combo Box icon, and then release the mouse. Create a rectangle with the mouse in the worksheet, and then release the mouse.

Step 3: Format the Combo Box

1.Select the Combo Box; right-click; and from the shortcut menu that appears, select Format Control. Then select the Control tab

2.In the Input range box, type the name MonthsList (You cannot press F3 to paste a name with   an object).

3.In the Cell link box, type the name MonthNumber.

4.Click to select the 3-D shading box (more aesthetic).

5.Click OK.

Exit the formatting mode of Combo Box, and select a cell in the sheet. Open the list of items in the Combo Box, and select a month. Note that the new month number is shown in cell B1.


Advantages of working with names in a Combo Box

Attaching a list with Define Names causes your list references to be updated automatically in the Combo Box. Sorting the list in the sheet will automatically sort the list in Combo Box as well.

Deleting a Combo Box

Select the object; right-click; and from the shortcut menu that appears, select Cut.

Adjusting the size of the Combo Box to the cells

Select the Combo Box and right-click; the Combo Box is now in editing mode. Adjust the width or height of the Combo Box by dragging one of the corner boxes; adjust the placement of the Combo Box on the worksheet by clicking anywhere inside the box and dragging it.


Automatic adjustment of the Combo Box

Hold down the Alt key while you click the mouse to adjust the size of the Combo Box.
Screenshot // Add Combo Box to a Sheet in Microsoft Excel

105

Microsoft Office Excel / Square root and cube root in Microsoft Excel

« on: August 15, 2012, 05:48:21 PM »

Getting a root of a number is the same as raising that number to the power of 1/root.

Example:
The square root of x is x^(1/2). The square root of A1 is: =A1^(1/2).
You can also use the built-in worksheet function SQRT: =SQRT(A1)

The cube root of x is x^(1/3). The cube root of A1 is: =A1^(1/3).
You can also use the built-in worksheet function POWER: =POWER(A1;1/3)