INDIRECT() function in Excel

This is not a VBA issue but a recent discussion on LinkedIn brought up the question of how to use INDIRECT to construct a reference to a named range in an external sheet. I have been using this very useful function for years to programmatically construct references based on different parameters which resolve to a specific workbook, sheet, name etc. Here is some information on how to properly construct a text based reference.

INDIRECT returns a reference specified by a text string and the syntax is INDIRECT(ref_text [, a1]) where ref_text is a reference to a cell containing an A1-style reference, an R1C1-style reference, a name defined as a reference or a reference to a cell as a text string. An error in the reference returns #REF!. a1 specifies the type of reference and for TRUE or omitted means A1-style and FALSE means R1C1 style.

The INDIRECT function will return #REF! if the target workbook is not open at the time you open the source workbook (closing the target after it has been open doesn't cause this error as Excel remembers the value until you force a recalc).

The proper construction of a text string based reference is as follows:

'path[file]sheet'!range

where the literal characters apostrophe ', square brackets [] and exclamation mark ! delimit the following

• path = drive and folder where the file exists. If you leave this out then it will use the workbook named file you have open no matter its location on disk. If the path has a folder with spaces in it then you must use the apostrophes.
  
• file = the name of the file with extension. Use of the apostrophes means that this name can have spaces in it. If you leave out the apostrophes then no spaces are allowed!
  
• sheet = the name of the worksheet. Only use the "[]" delimiters if a sheet name is provided. No sheet name, no brackets around file!
  

If you don't provide a sheet name then the reference will default to the first sheet in the workbook. For a named range, no sheet will work if the named range has a scope of workbook or a scope of the first sheet. Obviously, if you provide a sheet for a named range then it must have a scope of that sheet or the reference doesn't exist and returns #REF!

I hope this is helpful to folks that are confused about this very useful function.

Project Holiday Import Wizard

Check out this VBA enabled Microsoft Project Plan document that I recently developed. It neatly solves a need of every project manager who uses Microsoft Project to track their project: marking public holidays as non-working time in the project schedule.

My approach is to use the Outlook Holiday File that is installed on your computer when you install Microsoft Office Outlook. It contains hundreds of religious and national holidays from countries around the world. If you don't have Outlook installed, the wizard will take you to a page where you can learn about this file and download a copy to use as a source of holiday dates.

Best of all: its free!

Click here to visit my company website and check it out.

Possible Error in Excel 2007 Workbooks.Open method

I have discovered a disturbing "feature" in Excel 2007 VBA in the way that the Workbooks.Open method handles xlsx format files versus the legacy xls format file.

If a file has a "workbook protection" password set, then the following statement will succeed for an xls file but will fail for an xlsx file with run time error "1004: The password you supplied is not correct."

set wkb = Application.Workbooks.Open("file.???", , , , "password")

Why supply a password in this call? I am running Excel as an unattended automation server to load data captured in workbooks submitted from a website into an Access database. Sometimes the users apply a "file open" password which will cause Excel to display a dialog box and wait for someone to supply the password if no password is supplied as an argument to the Open method - very bad! On the other hand, if the password argument is present and is incorrect then the above 1004 error is generated and I can then deal with it in code. If there isn't a "file open" password on the file, the Open method ignores the password argument.

However, all my files have a "workbook protection" password set and some users submit the files in the new xlsx format. This causes me to discard the file when it should be able to be opened. By the way, even if the "workbook protection" password is "password" it generates this error. And if there is a "workbook protection" password on an xlsx file and no password argument is provided to the Open method, it opens the file without any trouble.

So looks like Microsoft have messed up the Workbooks Open method in the case where:

1. The file has a "workbook protection" password
2. The file is in the new xlsx format
3. A password argument is supplied in the call to the Workbooks.Open method

If you know someone who can alert Microsoft to this, please let them or me know!

*** UPDATE ***

Prominent Excel MVP Ron de Bruin has confirmed this is an error and has notified the Microsoft Excel team. Stay tuned for additional developments.

*** UPDATE ***