Variable reference

[Guest]

I use the following call to fetch a number from another open spreadsheet.

The call is successful, the FetchWages Function works as expected and
assigns the result to its 3rd Parameter (named wages in the FetchWages
function).

Yet, upon returning, the wnCurWages parameter in the calling program remains
0.

Is it a problem of variable declaration, scope, reference or what?

Call Application.Run("Misthoi.xls!FetchWages", wcCurEmpl, Format(wnCurDate,
"#"), wnCurWages)
.......
Public Function FetchWages(emp_code, search_date, wages)

Workbooks("Misthoi.xls").Activate
Worksheets("Data").Activate
evaluate_string = "MATCH(1,(R_CODES=" & emp_code & ")*(R_FROM<=" &
search_date & ")*(R_TO >=" & search_date & "),0)"
table_row = Evaluate(evaluate_string)

If IsNumeric(table_row) Then
wages = Application.Index(Range("D_WAGES"), table_row, 7)
Else
wages = 0
End If

End Function

 

[Guest]

OK, I figured it out myself. A function's return value is its own name.

The following code works...

Public Function FetchWages(emp_code, search_date)

Workbooks("Misthoi.xls").Activate
Worksheets("Data").Activate
evaluate_string = "MATCH(1,(R_CODES=" & emp_code & ")*(R_FROM<=" &
search_date & ")*(R_TO >=" & search_date & "),0)"
table_row = Evaluate(evaluate_string)

If IsNumeric(table_row) Then
FetchWages = Application.Index(Range("D_WAGES"), table_row, 7)
Else
FetchWages = 0
End If

End Function