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!

--
Message posted via http://www.officekb.com

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



"Michiel via OfficeKB.com" wrote:

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!

--
Message posted via http://www.officekb.com

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

Jim Thomlinson


"Michiel via OfficeKB.com" wrote:

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!

--
Message posted via http://www.officekb.com

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

Jim Thomlinson


"JLatham" wrote:

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



"Michiel via OfficeKB.com" wrote:

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!

--
Message posted via http://www.officekb.com

=fnc("sheet1")

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michiel via OfficeKB.com" <u40062@uwe wrote in message
news:7f1a3c3798e9a@uwe...
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!

--
Message posted via http://www.officekb.com

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

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"JLatham" wrote:

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



"Michiel via OfficeKB.com" wrote:

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!

--
Message posted via http://www.officekb.com

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.

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"JLatham" wrote:

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



"Michiel via OfficeKB.com" wrote:

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!

--
Message posted via http://www.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!

--
Message posted via http://www.officekb.com

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

Jim Thomlinson


"Michiel via OfficeKB.com" wrote:

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!

--
Message posted via http://www.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.

Jim Thomlinson wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michiel via OfficeKB.com" <u40062@uwe wrote in message
news:7f3e42b26c8d9@uwe...
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.

Jim Thomlinson wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1

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 wrote:
Let's review. You want help. You're too lazy to help yourself. So, you want
us to do it for you.
"Happy Trails"

--
Message posted via http://www.officekb.com

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michiel via OfficeKB.com" <u40062@uwe wrote in message
news:7f3f4ed5a88f3@uwe...
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 wrote:
Let's review. You want help. You're too lazy to help yourself. So, you
want
us to do it for you.
"Happy Trails"

--
Message posted via http://www.officekb.com

OK thanks!

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

--
Message posted via http://www.officekb.com

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



*** Sent via Developersdex http://www.developersdex.com ***