Hi Mike, You cannot use Event Methods like Open, Activate, Select etc in Custom Functions.
Regards Ashish Jain Microsoft Certified Application Specialist (Excel) http://www.excelitems.com http://www.openexcel.com ______________________________________ On May 28, 9:11 pm, Mike Ratcliffe <mich...@ratcliffefamily.org> wrote: > I have a custom function that needs to read data from an external > worksheet. > > To make sure that the workbook is open I use the following method: > Sub EnsureWorkbookOpen(ByVal fPath As String, ByVal fName As String) > On Error Resume Next > Err.Clear > > Workbooks(fName).Activate > If Err.Number <> 0 Then > Workbooks.Open(fPath & fName).Activate > End If > End Sub > > This works fine from ribbon buttons etc. but if this method is called > from within a custom function (=myfunc(A4)) the worksheet is not > opened because Workbooks.Open(...) returns Nothing. > > Is there some kind of limitation when it comes to making sure that > files are open (and opening them if they are not open) from within > custom functions and does anybody know a workaround? -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
