How to access a function from a different workbook

[jean grey]

Hi everyone.
I have 2 worksheets, say Workbook1 and Workbook2.
I have a routine in Workbook1 which needs to retrieve a value from Workbook2.
For example in Workbook1 I have:

Sub GetValueFromWorkbook2()
Dim val as Integer
val = Application.Run ("'Workbook2.xla'!GetValue")
MsgBox (val)
End Sub

and in Workbook2 which is also opened and acquired certain values before
call from Workbook1, I have:

Private val as Integer

Private Sub Workbook_Open()
val = 5
End Sub

Public Function GetValue()
GetValue = val
End

This would display 0, instead of 5. So, how will I get the correct value?

Thanks in advance.

 

[Alan Moseley]

I may be wrong, but I think that your GetValue function needs to be in a
module rather than in the thisworkbook space. In addition, as the function
is now not in thisworkbook, the declaration for val would need to be public
and not private.