Custom Function kicking out mid-function

I'm trying to develop what is basically a horizontal/vertical lookup. I need to look up the codes associated with individual phone numbers, which are currently organized in a two-dimensional chart. We have different accounting units split up between the different columns, with the different phone numbers on each row beneath the 7-digit code.

Here's the code I have for this:

Please Login or Register  to view this content.

I'm not sure what it is, but if anybody can help, I'm stumped now. Is it possibly because I'm trying to open a .xls intead of an Excel 2010 file?

Is it possibly because I'm trying to open a .xls intead of an Excel 2010 file?

It isn't really about the file type, it more that a UDF (a VBA function procedure called from a spreadsheet cell) cannot modify the spreadsheet environment (by opening a file, for example). It looks to me like the function should work just fine if you delete the open command, and have the inventory file already open.

Okay, I've tried that (just commented it out and made sure the file was open), and it's still kicking out at the Set wbTelcom line.

I tried to comment that out and use the long-form reference to the workbook, and it still boots out at the first line.

Thanks for the point about the Open though; I didn't know that was a problem, though I thought it might be.

Current version:

Please Login or Register  to view this content.

Shouldn't the syntax of the Workbook.Open method be:
Workbooks.Open Filename:="\\files1\TelcomPublicShare\CL Master Long Distance Inventory 2012.xls"?

You can also ascertain the version of Excel running by code:
Application.Version Property
Returns a String value that represents the Microsoft Excel version number.

A couple of thoughts:

When a workbook is open, you usually don't need the entire path to reference the specific workbook, and I'm wondering if this is the error. Try dropping the path identifiers and simply refer to it as "CL Master..."

I rarely use the structure you are demonstrating here. For my UDF's, any information the UDF needs from the spreadsheet I pass through the argument list. You might try re-writing your UDF to have the wbTelcom information passed to the UDF through the argument list

Please Login or Register  to view this content.

called as =getcoding(A1,'[cl master long distance inventory 2012.xls]'!$A$1:$A$30000)