Workbooks.Open method failing to run

[JasonLeisemann]

I'm trying to change a subroutine that I've been using for a while into a function, so that it can be called directly rather than from a separate subroutine. Here's what I have at the moment:

Please Login or Register  to view this content.

The problem is that when I run the Workbooks.open method from the function, it doesn't do anything. It loads all the property data for the file into wbTelcom, but the file doesn't open up, and the object seems to refer back to ThisWorkbook instead.

This only seems to be a problem if I enter macro processing through the function; if I use a command button that calls a standard subroutine with the method called there, it works fine. Is it some sort of problem with trying to call the method from a function? Or did I screw up the syntax somewhere?

[Norie]

How exactly are you calling the function?

[JasonLeisemann]

As a user-defined function from Excel. A simple '=FindCode(B2)'

[JasonLeisemann]

Picking up on the lead you were putting there, I tried running it from the Immediate window in the editor and that did work. Still no luck with the formula being run from the worksheet though. Even if I have the workbook open from the start, it doesn't set the wbTelcom object to the appropriate reference for some reason.

[romperstomper]

A UDF called from a cell can't open a workbook.

[JasonLeisemann]

Ah, didn't know that.

So, that explains why it won't open the workbook properly when I have it closed. Does the same apply to activate worksheets in other workbooks? If it does, that would explain both of my problems.

[romperstomper]

Yes. Why do you need to activate another sheet?

[JasonLeisemann]

The same reason that I needed to open the workbook; my Master Inventory file is in another workbook from the worksheet where I've got the data that I'm searching for, so I need to open it and switch over in order to find the billing code used for that number.

I'll probably just go back to the original method I was using and not worry about creating a function, at least for now.