Pasting formulas between workbooks without workbook link

Have you ever needed to copy and paste a range of cells containing formula, each referencing cells in other worksheets, from one workbook to another ?  When you copy the formula across workbooks, Excel, by default, copies a link from the original file.  So instead of copying “=Sheet2!D6+Sheet2!E6” you end up with =[YourOriginalFile.xls]Sheet2!D6+[YourOriginalFile.xls]Sheet2!E6.

This has always frustrated me, I can see why it would be useful, but wouldn’t it be nice to have a simple way of copying the formula without the original file name.

For a while now I’ve been using a fairly simple work-around, using Edit / Replace.

• Select the range you want to copy
• Edit->Replace
• What:= “=” (the equal sign)  /  With:=”^^” (or some unique string)
• Replace All

Now the formula are text strings, these can be copied and pasted without Excel doing anything to “help” you.

• Select the new range
• Edit->Replace
• What:= “^^” (or your unique string) / With:=”=” (equal sign)
• Replace All

Now the text strings are formulas again.  You will need to ensure you have matching worksheets in that new workbook, otherwise you’ll be dismissing lots of dialogs.

Clear DNS cache

The ipconfig /flushdns command allow you to flush and reset the contents of the DNS client resolver cache. During DNS troubleshooting, if necessary, you can use this procedure to discard negative cache entries from the cache, as well as, any other dynamically added entries.
Resetting the cache does not eliminate entries that are preloaded from the local Hosts file. To eliminate these entries from the cache, edit the hosts file using a text editor.

To clear DNS Cache in client, do the following:

• Start
• Run
• Type "cmd" and press enter
• In the command window type "ipconfige /flushdns"

If everything has been run correctly you will received the following message "Successfully flushed the DNS Resolver Cache."  If you receive an error, "Could not flush the DNS Resolver Cache: Function failed during execution.", follow the Microsoft KB Article 919746 to enable the cache.

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.)