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