Correct use of Worksheets statement in VBA

[PMHunt1955]

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.

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.

I have done some further 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 -

With thanks in anticipation.  Ideas please.

Just for completeness I post below the text of the call and the full text of the function (as now updated since the time of the original post)

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

[#2]

Hi,
Your question is quite difficult to read through and hence to answer. Can you try to reduce the question to a slightly simpler problem please? I am confident what you are trying to achieve isn;t that hard in VBA, but the complexity of the question makes it very hard to understand the setup.

[#3]

@Jan Karel Pieterse 

Dear Jan

Thank you for replying.

I feel that the essence of my problem is stated quite clearly in the first sentence but I will slightly reword it, with the hope that this helps.

I have a function which is called from a worksheet in one workbook, where the function needs to view, in succesion, within a couple of nested for-next loops, the contents of a series of cells (one at a time), which are located in a worksheet in another workbook. Both workbooks are open.

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.

[CODE]
Set fExt_Workbook = Excel.Workbooks(fExt_Workbook_Name)

[/CODE]

I have tried to follow the guidance that I have read through a number of other threads on a few different Excel support bulletin boards, which have discussed similar situations, but I am just not doing exactly the right thing, obviously.

I apologise for what you feel is the long windedness of my question; I have, though, often found that 'helpers' ask to see the full code, so that is why it is there.

As you say I am also fairly confident that what I am trying to achieve is not that difficult in Excel, but I have just got the syntax wrong somewhere.

Best regards

Philip

P. S. I used to be a mainframe / mini programmer in PL/1, COBOL and dBase, so that is why I often try to do more tricky things, eh.

[#4 · Accepted solution]

@PMHunt1955 

I wonder whether opening the target workbook and then working step by step might provide a starting point.

Set wb = Workbooks("Genealogical_Service_Users_Tracing_Project_Families.xlsm")
Set ws = wb.Worksheets("Names in Alpha Order")
Test_Surname = ws.Cells(3, 4).Value

Something radically different might be to ditch the usual sheet name and cell address approach and go for a Table using Power Query?

[#5]

@Peter Bartholomew 

Dear Peter

Thank you for pointing me in the right direction.  I eventually wrote two other small functions which stripped the CELL return value down to just the workbook and worksheet names, respectively.  This then made the central essence of the function work.  I could therefore remove all the string manipulation from the 'main' function.

Best

Philip