Forum Discussion
Passing a workbook name and worksheet number to find out worksheet's name
Sorry I misunderstood. I thought you were calling the function within the module itself.
You may tweak your function like this and let me know if that works for you...
Function Sheet_Name_from_Sheet_Num(ByVal Workbook_Source As String, ByVal Worksheet_Number As Long) As String Dim wbSource As Workbook Dim wsSource As Worksheet If IsWorkbookOpened(Workbook_Source) Then Set wbSource = Workbooks(Workbook_Source) Else Sheet_Name_from_Sheet_Num = "Workbook is not opened!" Exit Function End If If SheetExists(wbSource, Worksheet_Number) Then Set wsSource = wbSource.Worksheets(Worksheet_Number) Else Sheet_Name_from_Sheet_Num = "Worksheet with Index " & Worksheet_Number & " was not found!" Exit Function End If Sheet_Name_from_Sheet_Num = wsSource.Name End Function Function IsWorkbookOpened(ByVal wbName As String) As Boolean Dim wb As Workbook On Error Resume Next Set wb = Workbooks(wbName) On Error GoTo 0 If Not wb Is Nothing Then IsWorkbookOpened = True End Function Function SheetExists(wb As Workbook, ByVal wsIndex As Long) As Boolean Dim ws As Worksheet On Error Resume Next Set ws = wb.Worksheets(wsIndex) On Error GoTo 0 If Not ws Is Nothing Then SheetExists = True End FunctionAnd then you may call this function on the worksheet like this...
=Sheet_Name_from_Sheet_Num("Johnson_Project_Patriarchal_Lines.xlsm",A2)
Dear Subodh
Thank you for contributing.
From what you are saying, I have made errors in the calling for both parameters.
First Parameter:
I am trying to look across from a worksheet in one open workbook to a worksheet in another open workbook. The name of the workbook that I wish to look across at is -
Johnson_Project_Patriarchal_Lines.xlsm
You say that I need to specify the full path of the workbook file that I am trying to open.
My questions from that comment are therefore -
How do I obtain the full path of an open workbook?
Where do I do this obtaining of the full path, i.e. in the call statement or in the function?
What is the correct punctuation to be placed around the file name, within this statement to obtain the full path?
Second parameter
The cell A2, in the calling worksheet, contains a number, e.g. 1. I have never heard of this need to enclose the using of a cell reference in a function call within square brackets, or a range statement. I have always just referred to a cell in a call, given it a field name within the brackets of the function line and then duly used that field name within the function. Is it because I am using these rather 'special' Workbook open statements etcetera, in the function, that I have to use this, what for me, is an unusual approach to passing the contents of a cell into a function?
Just as an example, and it is quite a long call -
=Surname_for_Letter(A273,(IF((ISERROR((FIND(" ",A273)))),(IF((A273=""),""," ")),((RIGHT(A273,LEN(A273)-FIND("*",SUBSTITUTE(A273," ","*",LEN(A273)-LEN(SUBSTITUTE(A273," ",""))))))))),(IF((ISERROR((FIND(" ",A273)))),0,(FIND(" ",A273)))),(IF((ISERROR(FIND(" née ",A273))),0,(FIND(" née ",A273)))))
Here I am passing the contents of the cell A273 into a perfectly working function called Surname_for_Letter. Within that function's Function line I assign field names to the various parameters and then the function uses those field names accordingly.
Function Surname_for_Letter(Passed_Name As String, Passed_Surname As String, _
First_Space_Pos As Long, Née_Point As Long) As String
Why then in this function, which I am needing help on, are you suggesting that I need to use this square bracket or range structure and if so, precisely, please, how and where do I use this square bracket or range structure?
I look forward to hearing from you again.
Sorry I misunderstood. I thought you were calling the function within the module itself.
You may tweak your function like this and let me know if that works for you...
Function Sheet_Name_from_Sheet_Num(ByVal Workbook_Source As String, ByVal Worksheet_Number As Long) As String
Dim wbSource As Workbook
Dim wsSource As Worksheet
If IsWorkbookOpened(Workbook_Source) Then
Set wbSource = Workbooks(Workbook_Source)
Else
Sheet_Name_from_Sheet_Num = "Workbook is not opened!"
Exit Function
End If
If SheetExists(wbSource, Worksheet_Number) Then
Set wsSource = wbSource.Worksheets(Worksheet_Number)
Else
Sheet_Name_from_Sheet_Num = "Worksheet with Index " & Worksheet_Number & " was not found!"
Exit Function
End If
Sheet_Name_from_Sheet_Num = wsSource.Name
End Function
Function IsWorkbookOpened(ByVal wbName As String) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(wbName)
On Error GoTo 0
If Not wb Is Nothing Then IsWorkbookOpened = True
End Function
Function SheetExists(wb As Workbook, ByVal wsIndex As Long) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = wb.Worksheets(wsIndex)
On Error GoTo 0
If Not ws Is Nothing Then SheetExists = True
End Function
And then you may call this function on the worksheet like this...
=Sheet_Name_from_Sheet_Num("Johnson_Project_Patriarchal_Lines.xlsm",A2)
Thank you very much Subodh. It worked just fine. Thank you also for using my variable names, it made it much easier for me to understand.
You're welcome! Glad it worked as desired.