Correct use of Worksheets statement in VBA

[cuddling101]

I have a function where I am seeking to look across from a worksheet in one workbook to a worksheet in another workbook.

I have created a name reference across to the other sheet, the name is Subsidiary_Names_Sheet.

I then use that name in the call to my function within the CELL function, viz. -

(CELL("filename",Subsidiary_Names_Sheet))

This results in the function receiving the worksheet reference as -

E:\Old PC\Family_History (G)\Johnson Extended Family History\[Genealogical_Service_Users_Tracing_Project_Families.xlsm]Names in Alpha Order

I then use that parameter along with a couple of loop counts, viz -

Test_Surname = Worksheets(fWksht_Srce).Cells(I, J).Value

This will 'convert', at execution, for example, into

Test_Surname = Worksheets(E:\Old PC\Family_History (G)\Johnson Extended Family History\[Genealogical_Service_Users_Tracing_Project_Families.xlsm]Names in Alpha Order).Cells(3, 4).Value

but this does not work.

The parameters are being received by the function, just as I want, and the loop counts are starting correctly, but I am clearly not using precisely the correct syntax in the Worksheets statement.

I have done some research and found information that points me in the direction of needing to declare Excel.Workbook and Excel.Worksheet variables within the function, which I have done but I am still getting a subscript out of range error when I execute the statement -

Set fExt_Workbook = Excel.Workbooks(fExt_Workbook_Name)
Do I need some double and/or single quotes somewhere? Am I passing the wrong reference type in my CELL statement? Or is there something else wrong?
With thanks in anticipation. Ideas please.

Just for completeness I post below the text of the call and the full text of the function

Call:

=Subsidiary_Family_to_Main_Family_Index_Number(Z463,(CELL("contents",Main_Families_Count)),(CELL("filename",Subsidiary_Names_Sheet)))

Function:

Function  Subsidiary_Family_to_Main_Family_Index_Number(Subord_Surname As String,  Fams_Cnt As Long, Worksheet_Source As String) As Long

 

Dim fSubord_Surname As String
Dim fFams_Cnt As Long
Dim fWksht_Srce As String

 

Dim fExt_Workbook_Name As String
Dim fExt_Worksheet_Name As String

 

Dim fExt_Workbook As Excel.Workbook
Dim fExt_Worksheet As Excel.Worksheet

 

Dim Len_fWksht_Srce As Long
Dim Len_fExt_Workbook_Name As Long
Dim Len_fExt_Worksheet_Name As Long

 

Dim Test_Surname As String
Dim TSChar As String


Dim I As Long
Dim J As Long
Dim R As Long

 

fSubord_Surname = Subord_Surname
fFams_Cnt = Fams_Cnt
fWksht_Srce = Worksheet_Source

 

Len_fWksht_Srce = Len(fWksht_Srce)
Len_fExt_Workbook_Name = WorksheetFunction.Find("]", fWksht_Srce)
Len_fExt_Worksheet_Name = Len_fWksht_Srce - Len_fExt_Workbook_Name

 

fExt_Workbook_Name = Left(fWksht_Srce, Len_fExt_Workbook_Name)
fExt_Worksheet_Name = Right(fWksht_Srce, Len_fExt_Worksheet_Name)

 

' The following statement is where I am getting the subscript out of range error - I have even tried

' to do the statement in the immediate window (within debug), but using literals, rather than the

' variable name; I still get the same error.

 

Set fExt_Workbook = Excel.Workbooks(fExt_Workbook_Name)
Set fExt_Worksheet = fExt_Workbook.Worksheets(fExt_Worksheet_Name)

 

R = 0
I = 0
J = 0

 

For I = 3 To (fFams_Cnt + 2)
For J = 4 To 24
Test_Surname = fExt_Worksheet.Cells(I, J).Value
TSChar = Test_Surname
If Test_Surname = fSubord_Surname Then
R = I
GoTo Heading_Out
End If
Next J
Next I


Heading_Out:


Subsidiary_Family_to_Main_Family_Index_Number = R


End Function