Worksheets reference with String variable

[Pocho]

Hi,

I am trying to reference a sheet by name in a UDF but do not know the syntax.

Whithout the variable the reference is:

Workbooks("Salaries").Worksheets("Accounting").Range("j3").Value

And that, of course works.

I tried:

Workbooks("Salaries").Worksheets(sheetname).Range("j3").Value

Where sheetname is a String variable containing the value Accounting

And no dice.

I have seen examples where variables are used, but it is usually an Integer referenceing the number of the worksheet ei. isheet

What is the syntax in VBA to reference the sheet by name (not number) using a variable?

Thank you.

 

[dangre]

Referencing a declared variable as the sheet name works for me using this syntax:

Sub asdfasdf()

Dim sheetname
sheetname = "Sheet3"
Sheets(sheetname).Activate

End Sub

Good luck!

 

[Pocho]

OK, it works for me as long as I call the UDF from the same sheet as I am referencing (not exactly very useful). What could possibly be going wrong?

The exact syntax is:
Function getsalary(Ticker As String, Dia As Long)

Dim Lines As Integer

Lines = Workbooks("MASTER LIST").Worksheets(Ticker).Range("j3").Value
getsalary = Lines

End Function

When called from the same sheet it returns the value of cell j3, but if called from another sheet I get #value

 

[Pocho]

Still can't figure out what the deal is!!!

Forgot to mention I used Public Function()

Is there something I am doing wrong with the worksheet reference? I actually would like the function to be available from another workbook, but would be pleased if I could at least get it to work from another sheet.

Anyone?

Buler?