Why it can work in Function but ok in Sub
 

Can anyone tell me why the following code cannot work in Function but ok in
a Sub?
In Function, the statement
Workbooks("testing.xls").Activate
doesn't work, but ok in Sub

Thanks

Function no_of_HK_working_day(date1 As Date, date2 As Date, half_sat As
Double)

Set wb_current = ActiveWorkbook
Workbooks("testing.xls").Activate
Set wb_holiday = ActiveWorkbook
wb_current.Activate

End Function

Why it can work in Function but ok in Sub
 

If the function is ultimately called from VBA code, then it can work.
However a function that is ultimately called from a worksheet cell can only
return a value to that cell, it can do nothing to modify the environment
(can't select, activate, format, etc anything).

Jerry

"new.microsoft.com" wrote:

Can anyone tell me why the following code cannot work in Function but ok in
a Sub?
In Function, the statement
Workbooks("testing.xls").Activate
doesn't work, but ok in Sub

Thanks

Function no_of_HK_working_day(date1 As Date, date2 As Date, half_sat As
Double)

Set wb_current = ActiveWorkbook
Workbooks("testing.xls").Activate
Set wb_holiday = ActiveWorkbook
wb_current.Activate

End Function

Why it can work in Function but ok in Sub
 

I answered this same question from you yesterday.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"new.microsoft.com" wrote in message
...
Can anyone tell me why the following code cannot work in Function but ok
in
a Sub?
In Function, the statement
Workbooks("testing.xls").Activate
doesn't work, but ok in Sub

Thanks

Function no_of_HK_working_day(date1 As Date, date2 As Date, half_sat As
Double)

Set wb_current = ActiveWorkbook
Workbooks("testing.xls").Activate
Set wb_holiday = ActiveWorkbook
wb_current.Activate

End Function

Why it can work in Function but ok in Sub
 

Thanks so much Jerry!
Then can I send/retrive a value to/from a particular cells in another
workbooks within a Function, if your how?


"Jerry W. Lewis" wrote in message
...
If the function is ultimately called from VBA code, then it can work.
However a function that is ultimately called from a worksheet cell can
only
return a value to that cell, it can do nothing to modify the environment
(can't select, activate, format, etc anything).

Jerry

"new.microsoft.com" wrote:

Can anyone tell me why the following code cannot work in Function but ok
in
a Sub?
In Function, the statement
Workbooks("testing.xls").Activate
doesn't work, but ok in Sub

Thanks

Function no_of_HK_working_day(date1 As Date, date2 As Date, half_sat As
Double)

Set wb_current = ActiveWorkbook
Workbooks("testing.xls").Activate
Set wb_holiday = ActiveWorkbook
wb_current.Activate

End Function