Worksheets reference with String variable

Pocho

New Member
Joined
Sep 25, 2006
Messages
12
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

dangre

Board Regular
Joined
May 27, 2002
Messages
134
Referencing a declared variable as the sheet name works for me using this syntax:

Code:
Sub asdfasdf()

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

End Sub

Good luck!
 

Pocho

New Member
Joined
Sep 25, 2006
Messages
12
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

:oops:
 

Pocho

New Member
Joined
Sep 25, 2006
Messages
12
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?

:oops:
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,545
Latest member
TWR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top