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
