XL: Getting Windows Status Information from Windows API

ID: Q152601

The information in this article applies to:

Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows

SUMMARY

The following Visual Basic for Applications sample procedure demonstrates how you can obtain system status information similar to the information displayed in the Windows Program Manager About box. The sample program displays the following information using the Windows API function(s) indicated:

The Windows version number with the GetVersion function.
The CPU processor type.
The amount of free memory

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

To get Windows status information using Windows API calls, use the following steps to enter and run the code:

1. In Microsoft Excel, create a new workbook.

2. On the Insert menu, point to Macro, and then click Module.

   In Microsoft Excel 97, click the Tools menu, point to Macro, and click
   Visual Basic Editor. Then, click Module on the Insert menu.

3. On the new module sheet, type the following code:

    Option Explicit

    Type SYSTEM_INFO
        dwOemID As Long
        dwPageSize As Long
        lpMinimumApplicationAddress As Long
        lpMaximumApplicationAddress As Long
        dwActiveProcessorMask As Long
        dwNumberOrfProcessors As Long
        dwProcessorType As Long
        dwAllocationGranularity As Long
        dwReserved As Long
    End Type

    Type OSVERSIONINFO
        dwOSVersionInfoSize As Long
        dwMajorVersion As Long
        dwMinorVersion As Long
        dwBuildNumber As Long
        dwPlatformId As Long
        szCSDVersion As String * 128
    End Type

    Type MEMORYSTATUS
        dwLength As Long
        dwMemoryLoad As Long
        dwTotalPhys As Long
        dwAvailPhys As Long
        dwTotalPageFile As Long
        dwAvailPageFile As Long
        dwTotalVirtual As Long
        dwAvailVirtual As Long
    End Type

    'The following three Declare lines must be each entered on a single
    'line.
    Declare Function GetVersionEx Lib "kernel32" Alias "GetVersionExA" _
                 (LpVersionInformation As OSVERSIONINFO) As Long
    Declare Sub GlobalMemoryStatus Lib "kernel32" (lpBuffer As _
                MEMORYSTATUS)
    Declare Sub GetSystemInfo Lib "kernel32" (lpSystemInfo As _
                  SYSTEM_INFO)

    Public Const PROCESSOR_INTEL_386 = 386
    Public Const PROCESSOR_INTEL_486 = 486
    Public Const PROCESSOR_INTEL_PENTIUM = 586
    Public Const PROCESSOR_MIPS_R4000 = 4000
    Public Const PROCESSOR_ALPHA_21064 = 21064

    Sub SystemInformation()
    Dim msg As String         ' Status information.
    Dim NewLine As String     ' New-line.
    Dim ret As Integer        ' OS Information
    Dim ver_major As Integer  ' OS Version
    Dim ver_minor As Integer  ' Minor Os Version
    Dim Build As Long         ' OS Build

          NewLine = Chr(13) + Chr(10)  ' New-line.
           ' Get operating system and version.
          Dim verinfo As OSVERSIONINFO
          verinfo.dwOSVersionInfoSize = Len(verinfo)
          ret = GetVersionEx(verinfo)
          If ret = 0 Then
              MsgBox "Error Getting Version Information"
              End
          End If

          Select Case verinfo.dwPlatformId
              Case 0
                  msg = msg + "Windows 32s "
              Case 1
                  msg = msg + "Windows 95 "
              Case 2
                  msg = msg + "Windows NT "
          End Select

          ver_major = verinfo.dwMajorVersion
          ver_minor = verinfo.dwMinorVersion
          Build = verinfo.dwBuildNumber
          msg = msg & ver_major & "." & ver_minor
          msg = msg & " (Build " & Build & ")" & NewLine & NewLine

          ' Get CPU type and operating mode.
          Dim sysinfo As SYSTEM_INFO
          GetSystemInfo sysinfo
          msg = msg + "CPU: "
          Select Case sysinfo.dwProcessorType
              Case PROCESSOR_INTEL_386
                  msg = msg + "Intel 386" + NewLine
              Case PROCESSOR_INTEL_486
                  msg = msg + "Intel 486" + NewLine
              Case PROCESSOR_INTEL_PENTIUM
                  msg = msg + "Intel Pentium" + NewLine
              Case PROCESSOR_MIPS_R4000
                  msg = msg + "MIPS R4000" + NewLine
              Case PROCESSOR_ALPHA_21064
                  msg = msg + "DEC Alpha 21064" + NewLine
              Case Else
                  msg = msg + "(unknown)" + NewLine
          End Select

          msg = msg + NewLine

          ' Get free memory.
          Dim memsts As MEMORYSTATUS
          Dim memory As Long
          GlobalMemoryStatus memsts
          memory = memsts.dwTotalPhys
          msg = msg + "Total Physical Memory: "
          msg = msg + Format(memory \ 1024, "###,###,###") + "K" + NewLine
          memory = memsts.dwAvailPhys
          msg = msg + "Available Physical Memory: "
          msg = msg + Format(memory \ 1024, "###,###,###") + "K" + NewLine
          memory = memsts.dwTotalVirtual
          msg = msg + "Total Virtual Memory: "
          msg = msg + Format(memory \ 1024, "###,###,###") + "K" + NewLine
          memory = memsts.dwAvailVirtual
          msg = msg + "Available Virtual Memory: "
          msg = msg + Format(memory \ 1024, "###,###,###") + "K" + NewLine

          MsgBox msg, vbOKOnly, "System Info"
    End Sub

4. On the Tools menu, click Macro, click SystemInformation, and then click

   Run to run the macro.

   In Microsoft Excel 97, click the Tools menu, point to Macro, and click
   Macros. Then, click SystemInformation and click Run.

The macro should display a message box that shows your system information.

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q147886
   TITLE:      How VB Can Get Windows Status Information via API Calls

   ARTICLE-ID: Q146650
   TITLE:      Popular Windows API Functions to Use with Visual Basic 4.0

Additional query words: 7.00 97 8.00 XL97 API System Info Information Win95 vbapp

Keywords          : kbprg kbdta kbdtacode KbVBA kbhowto 
Version           : WINDOWS:7.0,7.0a,97
Platform          : WINDOWS

Last Reviewed: May 18, 1999