Worksheets("Sheetname").Select refuses to kick in from User Defined function

[Michiel via OfficeKB.com]

Hi all,

I have created a small Function that does the following:
1) Go to a worksheet in my workbook where it should get data
2) Get data of Cell A1
The function (in a simplified version) looks like this:

Function fncGetData(LSheetname As String) As Variant
Worksheets(LSheetname).Select
fncGetData = Cells(1, 1).Value
End Function

I run the function e.g. as Msgbox fncGetData(Sheet2)
(And Sheet 2 exists!!!)

Now when I run the function from VBA it works fine. The sheet is opened and I
get the contents of cell A1 of the Sheet2. Even when I had Sheet1 open.

However, if I run the function from another sheet as a user defined function
it fails to activate the desired sheet and it displays the contents of Cell
A1 of the sheet on which I use the worksheet function.

How can I get this right. I tried DoEvents but that does not work.

Thanks!

 

[JLatham]

Things work differently when running directly in VBA and when running as a
UDF. Try it this way:

Function fncGetData(LSheetname As String) As Variant
Dim anySheet as Worksheet
Set anySheet = ThisWorkbook.Worksheets(LSheetname)
fncGetData = anySheet.Cells(1, 1).Value
set anySheet=Nothing
End Function

 

[Jim Thomlinson]

Selecting in a UDF is not going to work for you. You would not want the
select to actually occure each time the formula was evaluated, so it
doesn't... Try this...

Function fncGetData(LSheetname As String) As Variant
fncGetData = Worksheets(LSheetname).Cells(1, 1).Value
End Function

Note that UDF's must be stored in regular code modules and not in worksheets
or ThisWorkBook...

 

[Jim Thomlinson]

General interest question... Why declare, set and destroy the worksheet
object when you could reference the sheet directly? I only see value if you
use the object to determine if the sheet exists something like this...

Function fncGetData(LSheetname As String) As Variant
Dim anySheet as Worksheet
on error resume next
Set anySheet = ThisWorkbook.Worksheets(LSheetname)
on error goto 0
if anySheet is nothing then
fncGetData = "Invalid Sheet Name"
Else
fncGetData = anySheet.Cells(1, 1).Value
set anySheet=Nothing
end if
End Function

of course I could be missing something. That happens more than I care to
admit...

 

[Don Guillett]

=fnc("sheet1")

Function fnc(x As String)
fnc = Sheets(x).Cells(1, 1)
End Function

 

[JLatham]

Probably simply from lack of enough coffee when I posted? I just get in the
habit of using an object like that because I usually have more to do with
them than just grab the value of a single cell. Old habits die hard...

 

[JLatham]

I should also add that the code you put up is probably more like what I'd
have written in a solution for a client - hard to build in TOO much bullet
proofing.

Developer Rule #1: Any situation you haven't thought of, the customer will.
Corollary to Developer Rule #1: Usually within the first hour after delivery.

 

[Michiel via OfficeKB.com]

Hi all,

Thank you so much for helping me!

I am afraid I simplified my actual Function too much. It is a bit more
complicated it uses commands that only can be run on the sheet named
LSheetname.
So, the function does absolutely need to activate that sheet.
A better way to present my function is:

Function fncGetData(LSheetname As String) As Variant
Worksheets(LSheetname).Select
<Do specific actions on the sheet LSheetname
fncGetData = <result of these actions>
End Function

And, still, this must be working from a user defined Function in another
sheet.

Thanks!

 

[Jim Thomlinson]

There is almost nothing in XL that requires a sheet to be active... You just
need to understand the object model and to re-engineer some of your code.

 

[Michiel via OfficeKB.com]

I know and fully agree on that.

In this case it is a matter of many old macros that are called. These macros
were not well programmed. I am too lazy to rewrite them.
So if you know any method I woul dwelcome them!

M.

 

[Don Guillett]

Let's review. You want help. You're too lazy to help yourself. So, you want
us to do it for you.
"Happy Trails"

 

[Michiel via OfficeKB.com]

Come on Don,

That is quite exaggerated! There is quite some difference between "please do
something for me" and "if you might know a trick, I'd be happy to know".
I never meant to exploit any of you kind people. If you feel it like that I
apologize.

Let's rephrase my question once more to a Yes or No question:

Is it possible to leave the sheet on which one is calling a user defined
function while calling this function?

Thanks!

 

[Don Guillett]

NO, but you can return values from that sheet as previously posted.
Use a sub instead of a UDF

 

[Michiel via OfficeKB.com]

OK thanks!

NO, but you can return values from that sheet as previously posted.
Use a sub instead of a UDF

 

[AW]

I have a function very similar to this. The data that I'm passing in is
a sheet codename. i.e LSheetname = sh_MTD_Cost_Detail. When I try to set
the worksheet (here anySheet) my code errors out. I've been trawling the
web for 2 days looking for the solution. Does anyone have any ideas
about how to solve this?

Many Thanks in advance!!



Function fncGetData(LSheetname As String) As Variant
Dim anySheet as Worksheet
Set anySheet = ThisWorkbook.Worksheets(LSheetname)
fncGetData = anySheet.Cells(1, 1).Value
set anySheet=Nothing
End Function