Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
    Exit For
    End If
    Next wbk
    End Function

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •