Results 1 to 2 of 2
Thread: INFO command in VBA (2000 SR1)
2003-06-11, 12:34 #1
- Join Date
- Feb 2001
- Youngstown, Ohio, USA
- Thanked 0 Times in 0 Posts
INFO command in VBA (2000 SR1)
I was looking at the INFO command and thought that one aspect of it could be useful in some of my VBA applications, but I'm not sure how/if it can be done. I have a number of workbooks set up to automatically close once their routines are done, but they are not set up to exit Excel - just in case other workbooks are open at the same time. The INFO command's option "numfile" can report the number of active worksheets. Can VBA use this command, with an IF set up so that If INFO("numfile") = 0 then Quit? This way I would not have to leave 'empty' instances of Excel open.
2003-06-11, 13:08 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: INFO command in VBA (2000 SR1)
INFO is a worksheet function. Although most worksheet functions can be called in VBA by using Application.WorksheetFunction.name_of_function, INFO is an exception because there are much better ways to get the same information in VBA.
The number of open workbooks is Workbooks.Count, but there are some gotchas. If you have a Personal.xls, it will always be open. And if you run this code in an Excel workbook, that workbook is open too. So you have to test very carefully. For example:
Function IsOtherOpen() As Boolean
Dim wbk As Workbook
For Each wbk In Workbooks
If UCASE(wbk.Name) <> "PERSONAL.XLS" And _
UCASE(wbk.Name) <> UCASE(ThisWorkbook.Name) Then
IsOtherOpen = True
Other gotcha: if you want this to run in foreign language versions of Excel, you must be aware that the name of Personal.xls is localized.
You could also test if the workbooks are visible (Personal.xls is hidden by default)