Function for going to specific sheet

[myrtoan]

Hello experts,

As quite a beginner in VBA I would like your help to create a function that takes 2 variables. The one is a cell which contains the name of the sheet I would like the data from and the second the specific cell in the aforementioned sheet to take the specific value.

I know that it may be quite easy, but I have not found it somewhere.

Also, is it possible to take the data from other workbooks? The problem is that I have already 50 sheets and I will need to create many more.

Thank you for the help.

 

[Yongle]

Is this what you want

Function SheetCellValue(aSheet As String, aCell As String)
    SheetCellValue = Sheets(aSheet).Range(aCell).Value
End Function

Call using VBA

Sub CallTheSub()
    MsgBox SheetCellValue("Sheet2", "D2")
End Sub

As a function in a cell
formula:
=SheetCellValue("Sheet2","D2")

If A3 value is "Sheet2", and "B3" value is D2, formula:
=SheetCellValue(A3,B3)

 

[Yongle]

Do you need a UDF?

How about this formula
=Sheet2!D2

Or if required values are in cells
with A3 value "Sheet2", and B3 value "D2", formula:
=INDIRECT("'"&A3&"'!"&B3)

 

[Yongle]

The function would be improved with an extra line:

Function SheetCellValue(aSheet As String, aCell As String)
    Application.Volatile
    SheetCellValue = Sheets(aSheet).Range(aCell).Value
End Function

 

[myrtoan]

The function would be improved with an extra line:

Function SheetCellValue(aSheet As String, aCell As String)
    Application.Volatile
    SheetCellValue = Sheets(aSheet).Range(aCell).Value
End Function

Thank you very much Yongle.
I tried both ways and neither seems to be working. Maybe I am doing something wrong. I did not implement the sub for example, but this is just for calling no?

The first one has the error reference and the other the error Value. For the function way, should I have the name of the sheet in cell with ""?

Thank you again.

 

[Yongle]

Where did you put the function?

 

[myrtoan]

In a module in VBA and then typed it on a cell with the two variables.

 

[Yongle]

I have absolutely no idea why it is not working for you

Same function with minor mod

Function SheetCellValue(ByVal aSheet As String, aCell As String)
    Application.Volatile
    SheetCellValue = Sheets(aSheet).Range(aCell).Value
End Function

to test:
create a NEW workbook
if workbook only contains Sheet1, then insert a new sheet (Sheet2)
enter 100 in cell A1 in Sheet2
copy Function SheetCellValue
{ALT} {F11} for VBA window
right-click in Project Window \ insert \ module
paste the function into Module1 window
{ALT} {F11} for workbook window
In Sheet1
cell A1 enter (normal text) sheet2
cell B1 enter (normal text ) a1
cell C1 formula =SheetCellValue(A1,B1)
cell D1 formula =SheetCellValue("sheet2","a1")

Both C1 and D1 should return value 100

 

[myrtoan]

Thank you soo much! What I had done is combining the two ways you are mentioning.
So instead of putting in "" the referring cell, I was just typing the cell. This resulted to refer to the cell of the sheet I was typing it on.

Next time I will remember the value and text difference.

Have a nice week.