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.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk
If I have solved your problem, please click Yes below. Thanks.
"jean grey" wrote:
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. :)