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

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.

Autofilter on a protected worksheet

You will quite probably have come across this problem already.  You need to protect the data in a worksheet for any number of reasons, but the recipient of the report wants to be able to use autofilter.
I've had this code for a while, but only recently needed it again, which reminded me to put it up here.

It's a very simple piece of code.  The code below applies a password to a worksheet, but leaves autofilter available.

   With Worksheets("YourWorkSheet")
      EnableAutoFilter = True
      Protect Password:="123", Contents:=True, UserInterfaceOnly:=True
   End With

As always, hope it proves useful.

Microsoft Excel 2010 and MSCOMCTL.OCX

Recently when working with Excel 2010 and certain userform controls some of my users have received the following error message:

"Component 'Mscomctl.ocx' or one of its dependencies not correctly registered: a file is missing or invalid"

Sometimes certain Microsoft Libraries can become unregistered when installing and uninstalling software. A common problem is the MSCOMCTL.OCX file.

If you receive the above error first search your drive for MSCOMCTL.OCX to see if you have the file. The file should be found in your C:\WINDOWS\SYSTEM directory or at C:\WINDOWS\SYSTEM32 if you are using Windows XP.  If the file is missing you can download it from: http://www.majorgeeks.com/files/mscomctl.zip.

Once it is there click START -> RUN and type "REGSVR32 MSCOMCTL.OCX" (without quotes) into the box to register this control.

You should find this fixes the problem.

Also relevant: http://support.microsoft.com/kb/2296116

Lotus Notes Password Sync

If you are unlucky enough to work for a company that uses Lotus Notes you may, when starting Lotus Notes, receive the message:

"Your windows account password does not match your notes password. To synchronize passwords, use File->Tools->User ID and click Set Password" .

If you do not need to sync your password and this message is just another nuisance provided by Notes it can be stopped. I have found several “How To’s” that say you need to re-install Lotus Notes to remove the functionality. This is not the case.  It can be done by following the instructions below.

Close down Lotus Notes.

Windows 2000 users:

Go to Start -> Control Panel -> Administrative Tools -> Component Services -> Services (Local) -> Lotus Notes Single Sign on.
Change the start-up type to disabled by selecting the line then right clicking on options. Stop the service by clicking on stop.
Restart Lotus Notes and you will be prompted for the user password normally.

Windows XP users:

Go to Start -> Control Panel -> Performance and Maintenance -> Administrative Tools -> Component Services -> Services (Local) -> Lotus Notes Single Sign on. 
Change the start-up type to disabled by selecting the line then right clicking on options. Stop the service by clicking on stop.
Restart Lotus Notes and you will be prompted for the user password normally.

Good luck and I hope this helps.

It’s been a while

Hi, it’s been a while since anyone has gotten around to posting anything. 

We’ve been busy with some work for various clients but hope to post some articles in the coming weeks.  Our aim is to post at least two articles a month so if you have any questions do let us know.  You can contact us by leaving a comment under this post, or feel free to visit our main site at zypher.co.uk and use the contact us form.