Apr 03 2019 04:42 PM - edited Apr 03 2019 11:41 PM
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
Apr 04 2019 06:11 AM - edited Apr 04 2019 06:11 AM
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.
Apr 04 2019 07:31 AM
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.
Apr 04 2019 09:20 AM
SolutionI 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?
Apr 04 2019 06:26 PM
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
Apr 04 2019 09:20 AM
SolutionI 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?