MemoryUsed Property

I'd never come across this before and it doesn't appear in the IntelliSense drop-down, but it is quite a neat way of monitoring the current workbook size.

Per the MSDN page here:

This example displays a message box showing the number of bytes that Microsoft Excel is currently using.

MsgBox "Microsoft Excel is currently using " _
                  Application.MemoryUsed & " bytes"

Outlook 2010 Keyboard Shortcut Keys

Below are a few of the shortcut keys I find useful, with a few extras added from a colleague.

• CTRL+1: Switch to Mail.

• CTRL+2: Switch to Calendar.

• CTRL+3: Switch to Contacts.

• CTRL+4: Switch to Tasks.

• CTRL+5: Switch to Notes.

• CTRL+6: Switch to Folder List in Navigation Pane.

• CTRL+7: Switch to Shortcuts.

• CTRL+PERIOD: Switch to next message (with message open).

• CTRL+COMMA: Switch to previous message (with message open).

• CTRL+SHIFT+TAB or SHIFT+TAB: Move between the Navigation Pane, the main Outlook window, the Reading Pane, and the To-Do Bar.

• CTRL+TAB: Move around message header lines in the Navigation Pane or an open message.

• Arrow keys: Move around within the Navigation Pane.

• ALT+B or ALT+LEFT ARROW: Go back to previous view in main Outlook window.

• CTRL+Y: Go to a different folder.

• F3 or CTRL+E: Go to the Search box.

• ALT+UP ARROW or CTRL+COMMA or ALT+PAGE UP: In the Reading Pane, go to the previous message.

• SPACEBAR: In the Reading Pane, page down through text.

• SHIFT+SPACEBAR: In the Reading Pane, page up through text.

• ALT+RIGHT ARROW: Go forward to next view in main Outlook window.

• CTRL+SHIFT+I: Switch to Inbox.

• CTRL+SHIFT+O: Switch to Outbox.

• CTRL+K: Check names.

• ALT+S: Send.

• CTRL+R: Reply to a message.

• CTRL+SHIFT+R: Reply all to a message.

• CTRL+ALT+R: Reply with meeting request.

• CTRL+F: Forward a message.

• CTRL+ ALT+J: Mark a message as not junk.

• CTRL+SHIFT+I: Display blocked external content (in a message).

• CTRL+ SHIFT+S: Post to a folder.

• CTRL+SHIFT+N: Apply Normal style.

• CTRL+M or F9: Check for new messages.

• UP ARROW: Go to the previous message.

• DOWN ARROW: Go to the next message.

• CTRL+N: Create a message (when in Mail).

• CTRL+SHIFT+M: Create a message (from any Outlook view).

• CTRL+O: Open a received message.

• CTRL+SHIFT+D: Delete and Ignore a Conversation.

• CTRL+SHIFT+B: Open the Address Book.

• INSERT: Add a Quick Flag to an unopened message.

• CTRL+SHIFT+G: Display the Flag for Follow Up dialog box.

• CTRL+Q: Mark as read.

• CTRL+U: Mark as unread.

• CTRL+SHIFT+W: Open the Mail Tip in the selected message.

• ALT+ENTER: Show the properties for the selected item.

• CTRL+SHIFT+U: Create a multimedia message.

• CTRL+SHIFT+T: Create a text message.

• CTRL+ALT+M: Mark for Download.

• CTRL+ALT+U: Clear Mark for Download.

• F9: Send and Receive.

• CTRL+B (when a Send/Receive is in progress): Display Send/Receive progress.

• CTRL+SHIFT+D: Dial a new call.

• F3 or CTRL+E: Find a contact or other item (Search).

• F11: Enter a name in the Search Address Books box.

• SHIFT+letter: In Table or List view of contacts, go to first contact that starts with a specific letter.

• F5: Update a list of distribution list members.

• CTRL+Y: Go to a different folder.

• CTRL+SHIFT+B: Open the Address Book.

• CTRL+SHIFT+F: Use Advanced Find.

• CTRL+SHIFT+PERIOD: In an open contact, open the next contact listed.

• F11: Find a contact.

• ESC: Close a contact.

• CTRL+SHIFT+X: Send a fax to the selected contact.

• CTRL+N: Create a new appointment (when in Calendar).

• CTRL+SHIFT+A: Create a new appointment (in any Outlook view).

• CTRL+SHIFT+Q: Create a new meeting request.

• CTRL+F: Forward an appointment or meeting.

• CTRL+R: Reply to a meeting request with a message.

• CTRL+SHIFT+R: Reply All to a meeting request with a message.

• ALT+0: Show 10 days in the calendar.

• ALT+1: Show 1 day in the calendar.

• ALT+2: Show 2 days in the calendar.

• ALT+3: Show 3 days in the calendar.

• ALT+4: Show 4 days in the calendar.

• ALT+5: Show 5 days in the calendar.

• ALT+6: Show 6 days in the calendar.

• ALT+7: Show 7 days in the calendar.

• ALT+8: Show 8 days in the calendar.

• ALT+9: Show 9 days in the calendar.

• CTRL+G: Go to a date.

• ALT+= or CTRL+ALT+4: Switch to Month view.

• CTRL+RIGHT ARROW: Go to the next day.

• ALT+DOWN ARROW: Go to the next week.

• ALT+PAGE DOWN: Go to the next month.

• CTRL+LEFT ARROW: Go to the previous day.

• ALT+UP ARROW: Go to the previous week.

• ALT+PAGE UP: Go to the previous month.

• ALT+HOME: Go to the start of the week.

• ALT+END: Go to the end of the week.

• ALT+MINUS SIGN or CTRL+ALT+3: Switch to Full Week view.

• CTRL+A: Select all contacts.

• CTRL+F: Create a message with selected contact as subject.

• CTRL+J: Create a Journal entry for the selected contact.

• CTRL+N: Create a new contact (when in Contacts).

• CTRL+SHIFT+C: Create a new contact (from any Outlook view).

• CTRL+O: Open a contact form for the selected contact.

• CTRL+SHIFT+L: Create a distribution list.

• CTRL+P: Print.

• CTRL+ALT+2: Switch to Work Week view.

• CTRL+COMMA or CTRL+SHIFT+COMMA: Go to previous appointment.

• CTRL+PERIOD or CTRL+SHIFT+PERIOD: Go to next appointment.

• CTRL+E: Find a message or other item.

• ESC: Clear the search results.

• CTRL+ALT+A: Expand the search to include All Mail Items, All Calendar Items, or All Contact Items, depending on the module you are in.

• CTRL+SHIFT+F: Use Advanced Find.

• CTRL+SHIFT+P: Create a new Search Folder.

• F4: Search for text within an open item.

• CTRL+H: Find and replace text, symbols, or some formatting commands. Works in the Reading Pane on an open item.

• CTRL+ALT+K: Expand search to include items from the current folder.

• CTRL+ALT+Z: Expand search to include subfolders.

If you spot any mistakes, or have any to add let us know through the comments.

Compile Error: "Object Library Invalid..." an UPDATE

I've recently had a reason to go back to this post in June 2011. The article is about EXD files, EXD files cache ActiveX component information making Excel run ever so slightly faster when using them. Problems arise if you upgrade a single Office application which updates background DLLs. If the component changes without removing the EXD file (which is created when the component is first used), then the system will be out of sync.
The office are preparing for an Outlook 2010 upgrade, a few test users started receiving the following error message:

So, referring back to my previous article we fixed the fault on all of the machines.  But... one thing I noticed, which wasn't mentioned in the original article is that you must ensure you have no open instances of Excel when you run the command.  If Excel is open the EXDs will be "in use" and the machine will be unable to delete them.

As a refresher...

To delete the EXD files go to command prompt and type the following DOS commands:

• > CD \Document and Settings
• > DEL /S /A:H /A:-H *.EXD

As always, any questions or comments let me know through the comments section below.

Find and replace VBA code using VBA

It is sometimes necessary to update your code, a quick find and replace (Ctrl+H) will often be enough.  I’d also recommend the MZ Tools VBE toolbar, it has a lot to add to your VBA coding experience, including a very good search tool.

Sometimes the above is not quite want you want.  So below is a simple bit of code you can use to replace one line of code for another.

Public Sub FindAndReplace()
'-----------------------------------------------------------------------------' Procedure : FindAndReplace
' Purpose   : Searches the active workbook for a specific code line and
'             replaces it with a new code line
'-----------------------------------------------------------------------------
    Dim SL As Long, EL As Long, SC As Long, EC As Long
    Dim S As String
    Dim Found As Boolean
    Dim sFind As String
    Dim sReplace As String

    sFind = "my old line of code"
    sReplace = "my new line of code"

    With ActiveWorkbook.VBProject.VBComponents("MyCodeModule").CodeModule
        SL = 1
        SC = 1
        EL = 99999
        EC = 999
        Found = .Find(sFind, SL, SC, EL, EC, True, False, False)
        If Found = True Then
            S = .Lines(SL, 1)
            S = Replace(S, sFind, sReplace)
            .ReplaceLine SL, S
        End If
    End With

End Sub

SL / SC is the start line / start column and EL / EC is the end line / end column.  These can be changed to target code lines within a specific part of your code.

I hope the above proves useful.  As always, any questions let us know via the comments.

Screenshots in Excel

This is quite a useful tip.

You probably already know how to include an entire screen shot within your Excel worksheet (Press “PrintScreen” and paste the screen shot where you want it with Ctrl+V or ‘Paste’). However, if you only want to show a selected region like a small part of the screen in your spreadsheet, try this:

To Copy:
Select the area you wish to copy from your Excel worksheet
If you have Excel 2007, go to “Paste”, then “As Picture”, then “Copy as Picture”
If you have Excel 2010, go to the “Copy” link and click “Copy as Picture”

To Paste:
Ctrl+V or ‘Paste’

Once the ‘image’ is placed within your workbook, you’ll be able to move it and resize it in the same way as you can any other graphic.

If you are copying text/data from the worksheet; When copy/creating the image, after selecting “Paste”->”Copy as Picture” a pop-up will appear (below):

Selecting “As shown on screen” will copy the gridlines as well as the text/data.  Selecting “As shown when printed” will not.

(Remember that adding graphics to the worksheet will increase the size of the file in which the workbook is saved.)

Checking odd and even numbers in VBA

It is sometimes useful to be able to check whether a number is odd or even.

Below is a very simple VBA function that does exactly that:

Function IsOdd(x As Integer) As Boolean
'------------------------------------------------------------------------
' Procedure : IsOdd
' Author : Zypher.co.uk
' Date : 09-Feb-2012
' Purpose : Check whether a value is odd or even
'------------------------------------------------------------------------
   IsOdd = (x Mod 2) <> 0
End Function

As always, I hope it proves useful.

Append multiple text files together using the DOS command prompt

If like me you record log files for some of your Excel files or databases you'll know it's not unusual to have a directory full of log files. If you then decide you want to import these into Excel or a database so you can do some processing on them it's quite a task to open each in turn.  You could create a short macro to do this for you, but there is also another way...

Open the command prompt and remind yourself of what DOS looks like, then use the “for” command.

The syntax is simple enough:

    for <filename> in (<directory>) do <command> <filename>

Working with our directory full of (*.log / *.txt) files, we use the “type” command and then pass each file into a new file using the >> operator.

">>" Appends data to the end of the file.
">" Completely replaces the file with new contents.

Inconclusion then here’s the command you need to run.

    for %f in (*.log) do type “%f” >> NewFile.txt

This assumes you are in the directory containing the log files.  If you are appending *.txt files I'd advise you to use NewFile.log.  otherwise the command will append the new file to itself when it finds it in the directory !

As always, hope it proves useful.