VBA/Excel/Access/Word/Application/Application

Application.InchesToPoints

 
Sub Macro1_Version2()
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(1.5)
        .RightMargin = Application.InchesToPoints(1.5)
        .TopMargin = Application.InchesToPoints(1.5)
        .BottomMargin = Application.InchesToPoints(1.5)
        .HeaderMargin = Application.InchesToPoints(1)
        .FooterMargin = Application.InchesToPoints(1)
    End With
End Sub

Application.Path

 
Sub main()
    MsgBox Application.Path & "\EXCEL.EXE"
End Sub

Benchmark with/without setting the Application.ScreenUpdating

 
Sub WriteReadRange()
    Dim MyArray()
    Dim Time1 As Date
    Range("A:A").ClearContents
    NumElements = 1000
    If NumElements = "" Then Exit Sub
    ReDim MyArray(1 To NumElements)
    For i = 1 To NumElements
        MyArray(i) = i
    Next i
    
    Application.ScreenUpdating = False
    Time1 = Timer
    For i = 1 To NumElements
        Cells(i, 1) = i
    Next i
    WriteTime = Format(Timer - Time1, "00:00")
    
    Time1 = Timer
    For i = 1 To NumElements
        MyArray(i) = Cells(i, 1)
    Next i
    ReadTime = Format(Timer - Time1, "00:00")
    Application.ScreenUpdating = True
    
    Debug.Print "Write: " & WriteTime
    Debug.Print "Read: " & ReadTime
End Sub

CalculationVersion: Right four digits indicate the version of the calculation engine whereas the digits to the left indicate the major version of Excel.

 
Sub InspectTheEnvironment() 
    Debug.Print Application.CalculationVersion 
End Sub

Clearing the Recently Used Files List

 
Sub clear()
    Dim myMax As Long
    With Application.RecentFiles
        myMax = .Maximum
        .Maximum = 0
        .Maximum = myMax
    End With
End Sub

Cursors Available to Use with the Cursor Property

 
Sub ViewCursors() 
    Application.Cursor = xlNorthwestArrow 
    MsgBox "Do you like the xlNorthwestArrow? Hover over the worksheet to see it." 
    Application.Cursor = xlIBeam 
    MsgBox "How about the xlIBeam? Hover over the worksheet to see it." 
    Application.Cursor = xlWait 
    MsgBox "How about xlWait? Hover over the worksheet to see it." 
    Application.Cursor = xlDefault 
    MsgBox "Back to the default..." 
End Sub

Demonstration of Window size Properties

 
Sub GetWindowInfo() 
    Dim lState As Long 
    Dim sInfo As String 
    Dim lResponse As Long 
    " Prepare message to be displayed 
    sInfo = sInfo & "Usable Height = " & Application.UsableHeight & vbCrLf 
    sInfo = sInfo & "Usable Width = " & Application.UsableWidth & vbCrLf 
    sInfo = sInfo & "Height = " & Application.Height & vbCrLf 
    sInfo = sInfo & "Width = " & Application.Width & vbCrLf & vbCrLf 
    lResponse = MsgBox(sInfo, vbYesNo, "Window Info") 
    " Minimize window if user clicked Yes 
    If lResponse = vbYes Then 
        Application.WindowState = xlMinimized 
    End If 
End Sub

Display application in full screen mode

 
Sub FullScreen_Icon()
  If Application.DisplayFullScreen Then
    Application.DisplayFullScreen = False
    Application.WindowState = xlMinimized
  Else
    Application.DisplayFullScreen = True
  End If
End Sub

Execute the next statement to set the calculation mode to automatic:

 
Sub calcu()
    Application.Calculation = xlCalculationAutomatic
End Sub

Get application user name

 
Private Sub CommandButton1_Click()
    MsgBox "Hello " & Application.UserName
End Sub

Get window state information

 
Sub GetWindowInfo() 
    Dim lState As Long 
    lState = Application.WindowState 
    Select Case lState 
        Case xlMaximized 
            Debug.Print "Window is maximized."
        Case xlMinimized 
            Debug.Print  "Window is minimized." 
        Case xlNormal 
            Debug.Print  "Window is normal." & vbCrLf 
    End Select 
End Sub

If you need to avoid user interaction you can turn off alerts in Excel by setting the DisplayAlerts property of the Application object to False before deleting the worksheet and then turning alerts back on again:

 
Sub alert()
    Application.DisplayAlerts = False
    myWorkbook.Sheets("Summary").Delete
    Application.DisplayAlerts = True
End Sub

Listing Application CommandBars

 
Sub Inventory()
    Dim cb As commandBar
    For Each cb In Application.rumandBars
        Debug.Print cb.name
        Debug.Print cb.Index
        Debug.Print cb.BuiltIn
        Debug.Print cb.Enabled
        Debug.Print cb.Visible
        Debug.Print cb.Controls.Count
    Next
    Set cb = Nothing
End Sub

Make sure that the help file can be located:

 
Sub GetHelp()
    Path = ThisWorkbook.Path
    Application.Help Path & "\USER.HLP"
End Sub

MemoryFree Returns the amount of memory in bytes that Excel is allowed to use, not including memory already in use.

 
Sub InspectTheEnvironment() 
    Debug.Print Application.MemoryFree 
End Sub

MemoryTotal Returns the total amount of memory, in bytes, that Excel can use. It includes memory that is already in use. It is the sum of MemoryFree and MemoryUsed.

 
Sub InspectTheEnvironment() 
    Debug.Print Application.MemoryTotal 
End Sub

MemoryUsed Returns the amount of memory, in bytes, that Excel is currently using.

 
Sub InspectTheEnvironment()     
    Debug.Print Application.MemoryUsed 
End Sub

OperatingSystem Returns the name and version of the Operating System.

 
Sub InspectTheEnvironment() 
    Debug.Print Application.OperatingSystem 
End Sub

OrganizationName Returns the name of the organization to which the product is registered.

 
Sub InspectTheEnvironment() 
    Debug.Print Application.OrganizationName 
End Sub

Performance Implications of Screen Updating

 
Sub TimeScreenUpdating() 
    Dim dResult As Double 
    dResult = TestScreenUpdating(True) 
    MsgBox Format(dResult, "0.00") & " seconds.", vbOKOnly 
    dResult = TestScreenUpdating(False) 
    MsgBox Format(dResult, "0.00") & " seconds.", vbOKOnly 
End Sub 
Function TestScreenUpdating(bUpdatingOn As Boolean) As Double 
    Dim nRepetition As Integer 
    Dim ws As Worksheet 
    Dim dStart As Double 
    dStart = Timer 
    Application.ScreenUpdating = bUpdatingOn 
    For nRepetition = 1 To 250 
        For Each ws In ThisWorkbook.Worksheets 
            ws.Activate 
        Next 
    Next 
    Application.ScreenUpdating = True 
    TestScreenUpdating = Timer - dStart 
    Set ws = Nothing 
End Function

Pick the Perfect Location with GetSaveAsFilename

 
Sub SimpleGetSaveAsFilename() 
    Dim sFile As String 
    sFile = Application.GetSaveAsFilename 
    Debug.Print sFile  
End Sub

Quitting Application

 
Sub quit()
    Application.Quit
End Sub

Setting a Default File Location

 
Sub def()
    Application.DefaultFilePath = "\\server3\users\mjones\files"
End Sub

Specifying the proper Excel version: Warn users who attempt to open the add-in using Excel 97

 
Sub CheckVersion()
    If Val(Application.Version) < 9 Then
        MsgBox "This works only with Excel 2000 or later"
    End If
End Sub

start Access

 
Sub Start()
    Application.ActivateMicrosoftApp xlMicrosoftAccess
End Sub

start Foxpro

 
Sub Start()
    Application.ActivateMicrosoftApp xlMicrosoftFoxPro
End Sub

start mail client

 
Sub Start()
    Application.ActivateMicrosoftApp xlMicrosoftMail
End Sub

start Power point

 
Sub Start()
    Application.ActivateMicrosoftApp xlMicrosoftPowerPoint 
End Sub

start Project

 
Sub Start()
    Application.ActivateMicrosoftApp xlMicrosoftProject
End Sub

start schedule

 
Sub Start()
    Application.ActivateMicrosoftApp xlMicrosoftSchedulePlus
End Sub

start word

 
Sub StartWord()
    Application.ActivateMicrosoftApp xlMicrosoftWord
End Sub

System Information Available Using Application Object Properties

 
Sub InspectTheEnvironment() 
    Debug.Print Application.CalculationVersion 
End Sub

To avoid these alert messages, insert the following VBA statement in your macro:

 
Sub alert()
    Application.DisplayAlerts = False
End Sub

Toggles the calculation mode between manual and automatic and displays a message indicating the current mode:

 
Sub ToggleCalcMode()
    Select Case Application.Calculation
        Case xlManual
            Application.Calculation = xlCalculationAutomatic
            MsgBox "Automatic Calculation Mode"
        Case xlAutomatic
            Application.Calculation = xlCalculationManual
            MsgBox "Manual Calculation Mode"
    End Select
End Sub

Turning off automatic calculation: Sets the Excel calculation mode to manual

 
Sub cal()
    Application.Calculation = xlCalculationManual
End Sub

Turning off screen updating

 
Sub update()
    Application.ScreenUpdating = False
    Application.ScreenUpdating = True
End Sub

UserName Returns or sets the name of the current user.

 
Sub InspectTheEnvironment() 
    Debug.Print Application.UserName 
End Sub

Use the Application object qualifier to set properties

 
Sub appProperty()
    Application.Calculation = xlManual
    Application.EditDirectlyInCell = False
    Application.DefaultFilePath = "C:\My Documents"
    
End Sub

Using the StatusBar property to Display Information

 
Sub TimeStatusBar()
    Dim dStart As Double
    Dim dResult As Double
    Dim bDisplayStatusBar As Boolean
    bDisplayStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    dStart = timer
    TestStatusBar 1, False
    dResult = timer - dStart
    MsgBox Format(dResult, "0.00") & " seconds.", vbOKOnly
    dStart = timer
    TestStatusBar 1, True
    dResult = timer - dStart
    MsgBox Format(dResult, "0.00") & " seconds.", vbOKOnly
    dStart = timer
    TestStatusBar 5, True
    dResult = timer - dStart
    MsgBox Format(dResult, "0.00") & " seconds.", vbOKOnly
    Application.DisplayStatusBar = bDisplayStatusBar
End Sub
Sub TestStatusBar(nInterval As Integer, bUseStatusBar As Boolean)
    Dim lRow As Long
    Dim lLastRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    lLastRow = ws.Rows.Count
    For lRow = 1 To lLastRow
        If lRow Mod nInterval = 0 Then
            If bUseStatusBar Then
                Application.StatusBar = "Processing row: " & lRow & _
                " of " & lLastRow & " rows."
            End If
        End If
    Next
    Application.StatusBar = False
    Set ws = Nothing
End Sub

Version Returns the version of Excel that is in use.

 
Sub InspectTheEnvironment() 
    Debug.Print Application.Version 
End Sub