Custom Function Problem

[mouse88]

I am using a function i wrote in some formulas. The function grabs data from a range in another workbooks, the link to the workbook is provided as a parameter in the formula. The function then returns a date.

When I open the book with the formulas in it shows errors but when I open the file it is getting the data from the data appears correctly.

Can anyone advise if there is a way to get the function to get the data without the source workbook having to be open.

Thanks

 

[Ruddles]

Show us the code which is trying to get the data from the closed workbook? Or the whole of the function code if it isn't too long?

Don't forget to enclose the code in CODE tags - the # icon in the advanced editor toolbar.

 

[mouse88]

Heres the code:

Option Explicit
'Look Up Name, Find And Return Total Of Selected Option'
Public Function GETSHRINKAGE(Name As String, Activity As String, Report As Range) As Variant
'-----------------'
'Declare Variables'
'-----------------'
Dim StartRowIndex As Long 'To Store The First Row Number'
Dim EndRowIndex As Long 'To Store The Last Row Number'
Dim ReturnValue As Date 'To Hold The Running Total'
Dim TestRange As Range 'To Hold The Resulting Range Of The Search'
'-------------'
'Set Variables'
'-------------'
ReturnValue = TimeValue("00:00:00") 'Set Starting Value So 00:00:00 Is Returned For No Match'
StartRowIndex = 0 'Set Initial Value'
EndRowIndex = 0 'Set Initial Value'
'--------------------'
'Get Start Row Number'
'--------------------'
Set TestRange = Report.Find(Name) 'Look For Name And Store Resulting Range'
'Test If Name Found'
If TestRange Is Nothing Then
    GETSHRINKAGE = ReturnValue
    Exit Function
End If
'Result Found/Store Start Row Index'
StartRowIndex = TestRange.Row
'--------------------'
'Get End Row Number'
'--------------------'
Dim LoopCounter As Long
LoopCounter = StartRowIndex + 1
'Loop Rows Until Next Name Found'
Do Until Left(Report.Range("A" & LoopCounter).Text, 5) = "Agent"
    LoopCounter = LoopCounter + 1
    If LoopCounter > StartRowIndex + 500 Then Exit Do
Loop
EndRowIndex = LoopCounter 'Set End Row Index'
'---------------------------------------------'
'Loop Rows And Look For Option And Add Up Time'
'---------------------------------------------'
Dim Cell As Range
For Each Cell In Report.Range("B" & StartRowIndex, "B" & EndRowIndex)
    If Cell.Text = Activity Then
        ReturnValue = ReturnValue + TimeValue(Cell.Offset(0, 1).Text)
    End If
Next Cell
'-------------'
'Return Result'
'-------------'
GETSHRINKAGE = ReturnValue
End Function

Thanks

 

[Ruddles]

I can't see that your function is actually fetching data from another workbook. If you're passing data from another workbook to it as a parameter that should work okay.

Show us the formula where you're calling the function?

 

[mouse88]

=GETSHRINKAGE(A1, "Other Admin", 'C:\Users\Matt\Desktop\[FTE.xls]Sheet1'!$J$3)

 

[Ruddles]

Okay, strange... if you place a simple link to 'C:\Users\Matt\Desktop\[FTE.xls]Sheet1'!$J$3 in your worksheet, it gets the value of the cell with no problem but if you place the reference in a formula, it needs FTE.xls to be open otherwise it errors.

So if you placed a link to cell J3 in your current workbook, you could reference that in your function call and it would work - it does work because I've tried it.

But I guess that's not a viable solution to you, is it?

 

[mouse88]

No it needs to be linked to a report in a seperate workbook. Its not the end of the world because I can open the report and then in the main workbook just copy and paste it as values so they stay in there.

 

[AD_Taylor]

Could you try:

Report As Worksheet

in the Function declaration.

And then call the function using the formula:

=GETSHRINKAGE(A1, "Other Admin", 'C:\Users\Matt\Desktop\[FTE.xls]Sheet1'!)

I have no idea if that will work but it may be worth a go?

Adam