Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't this function work? | Excel Worksheet Functions | |||
Function doesn't work | Excel Worksheet Functions | |||
need a function that will work using multiple work books and sheet | Excel Worksheet Functions | |||
how do i get the mid function to work with a zero | Excel Worksheet Functions | |||
Why it can work in Function but ok in Sub | Excel Programming |