Excel 2007 – Useful Microsoft Office Shortcuts

The basic Microsoft Office document shortcuts

• For a New Document – CTRL+N
• To Open a Previously Saved Document – CTRL+O
• To Print your Document / Open the Print Settings – CTRL+P
• To Save your Document – CTRL+S
• To Close your Document – CTRL+W

Text Adjustments to your Documents

• Cut the Highlighted text – CTRL+X
• Copy the Highlighted text – CTRL+C
• Paste the Highlighted text – CTRL+V
• Select All the Text in the Document – CTRL+A

Change the Highlighted Text to your set Normal Font Style – CTRL+SHIFT+N

• To Change the Font – CTRL+D
• To Insert a Hyperlink – CTRL+K
• To make the Text Bold – CTRL+B
• To make the Text Italic – CTRL+I
• To Underline the Text – CTRL+U
• To Justify the Paragraph – CTRL+J
• To Left Align the Text – CTRL+L
• To Right Align the Text – CTRL+R
• To Centre the Text – CTRL+E

For the minor mistakes that creep into your Document

• To Find something in your Document – CTRL+F
• To Undo – CTRL+Z
• To check your Spelling and Grammar – F7
• To use the Thesaurus – Shift+F7

And if anything goes wrong you can always use

• Help – F1

Quick Zoom, assuming you are using a computer with a mouse and it has a wheel, then you can quickly and easily zoom in and out of a document.

• To Zoom In – CTRL+Scroll up
• To Zoom Out – CTRL+Scroll Down

Website Design and Development

Hello again, it’s been a while since the last blog post so I thought it a good idea to show you what we’d been up to.

We’ve been working on a couple of websites, hope you like our work.

Some of the above are still a work in progress, but we hope they show a few options from the range of styles we have to offer.

Using VBA to get a File’s Last Modified Date

It is sometimes useful to know the date and time a file was last modified.  this is particularly useful if you have a folder full of similar files and need to use the latest copy.

Today the code I’m going to share below enables you to pass a file address into a function which will return the date and time it was last saved.  The example below passes a file address to the function, which returns the modified date, this is then displayed in a message box.

The function requires a reference to Microsoft Scripting Runtime within the VBE.

Sub MyMacroRoutine()
    Dim ModDate As Date
    ModDate = FileLastModDate("C:\YourFileAddressGoesHere")
    MsgBox ModDate
End Sub

Function FileLastModDate(strFileAddress As String) As Date
' In the VBE, set a reference to Microsoft Scripting runtime

    Dim fso As Scripting.FileSystemObject
    Dim fsof As Scripting.File
    Dim strPath As String

    Set fso = New FileSystemObject
    strPath = strFileAddress
    Set fsof = fso.GetFile(strPath)
    With fsof
        FileLastModDate = .DateLastModified
    End With

    Set fso = Nothing
    Set fsof = Nothing

End Function

As always, if you have any questions do let us know via the comments section.

Outlook Email using VBA

The code I’m sharing with you today will enable you to send an email using VBA through  Outlook.  It uses early binding so you will need to use a reference to Outlook.  You may need to look into what version(s) Outlook will be available to your users. 

As well as the subject and message body the routine allows you to specify the To, Cc and Bcc fields within a standard email format. 

The example below shows the code to attach four files using four different formats: Insert the file, insert a shortcut to the file, Embed and OLE.

The code could easily be changed to loop through arrays or collections of values for many of these fields.

Sub SendAnEmailWithOutlook()
'-- Requires a reference to the Microsoft Outlook 8.0 Object Library or higher --

    ' Creates and sends a new e-mail message with Outlook
    Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
    Dim ToContact As Outlook.Recipient

    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        ' message subject
        .Subject = "This where the subject text goes..."

        ' add a recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' add another recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' set latest recipient as CC
        ToContact.Type = olCC
        ' add another recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' set latest recipient as BCC
        ToContact.Type = olBCC

        ' the message text with a line break
        .Body = "email message"

        ' insert attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olByValue, , "filename goes here"
        ' insert shortcut
        .Attachments.Add "C:\FolderName\Filename.txt", olByReference, , "Shortcut to Attachment"
        ' embedded attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olEmbeddeditem, , "Embedded Attachment"
        ' OLE attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olOLE, , "OLE Attachment"

        .OriginatorDeliveryReportRequested = False ' True would be delivery confirmation
        .ReadReceiptRequested = False ' True would be read confirmation
        '.Save      ' saves the message for later editing
        '.Display   ' displays the email message for editing
        .Send       ' sends the e-mail message (puts it in the Outbox)

    End With

    Set ToContact = Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing

End Sub

Hope the text wrapping doesn’t make it too awkward to read.
As always, if you have any questions do let us know via the comments section.

Edit (18-Dec-2010): Due to a ‘technical’ error this blog entry has changed from it’s original version.  The original explained how to send an email through Outlook using late binding, this post will be re-written and posted again soon.  Apologies for any inconvenience this may cause.

An introduction to Excel VBA (Macros)

The purpose of my next few blog posts will be to go over the basics of the Visual Basic for Applications (VBA) programming language that comes within the Microsoft Office suite.  VBA is a programming language aimed at ‘normal’ people, it’s written in a style similar to normal written English. 
Programming with VBA in Excel means that you can instruct Excel to automatically do things that you would normally do manually — saving you time.
This blog post will concentrate on the Visual basic Editor (VBE). The VBE is the tool you will use to write your VBA macro code and create userforms.  Macro code is the instructions you are giving to Excel to follow, userforms are what you use to allow the user to interact with the macro.
Below are some screen shots of the VBE and notes to explain what each of the sections are for.  You can open the VBE by pressing Alt+F11 (this will switch between the VBE and Excel).  In Excel 97 through 2003 you can also use the command bar menu; Tools-Macro-Visual Basic Editor.  In Excel 2007 you will need the ‘Developer’ ribbon turned on, and press the ‘Visual Basic’ button.

The VBE (Visual Basic Editor)

The Project Explorer

The Project Explorer displays all of the workbooks currently open in Excel (including Add-Ins and hidden workbooks).  A tree diagram is used to display the objects under each project (worksheets, userforms, modules and class modules).  Modules contain the macro code.  Class Modules are covered in another blog post which can be found here.

The Code Window

The code window is where the macro code is written / displayed.  All of the objects in your project will have an associated Code window.  To open them double-click the object in the Project Explorer window to bring up the Code Window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project Explorer window. Unless you’ve added some VBA macro code, the Code window will be empty.

The Properties Window

The properties window allows you to amend the properties for all of the objects in your project.  The example above is for a Sheet1, it is more commonly used to edit the properties of userforms and controls (buttons, list boxes, combo boxes etc).  For the worksheet above you can change the visible property, disable cell selection etc.

Excel 2007 Developer Ribbon

To do any sort of VBA coding within Microsoft Excel 2007 it will prove useful to turn on the Developer Ribbon.  You can use Alt+F11 to switch between the VBE and Excel, but having the Developer Ribbon turned on will make life easier.  The Developer Ribbon is like the Visual basic toolbar in Excel 97 through 2003.

To turn on the Developer Ribbon you will need to click the Office button , then select Excel Options, bottom right of the menu.  Within the ‘Popular’ category select the ‘Show Developer tab in the Ribbon’.

Once this is checked you will see the following controls now available within Excel.

[SetupFunctionIDs] [PickPlatform]...?

A company I work for has recently upgrade from MS Office 97 to MS Office 2003.  Yes, I’m aware that even with this upgrade they’ve still moved to a package which Microsoft withdrew support for in 2009.

Anyways, since the upgrade a few of the more advanced users have noticed that every time they open the VBE (Visual Basic Editor) some code is already present in the Immediate Window:

[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] >

I had to do a little searching to remind myself about this and came across a thread on the MrExcel message boards.  The general gist is that it appears if you are using the

(atpvbaen.xla) add-in.  It appears that it is a Microsoft error, apparently it was part of their debugging method to see which routines were running and which weren't (< means starting the sub, and the > means exiting it).

Technorati Tags: VBA,VBE,[SetupFunctionID],Analysis Tookpak - VBA