Excel VBA(1) Application Object

The Active Properties

ActiveCell ActiveChart ActivePrinter ActiveSheet ActiveWindow ActiveWorkbook Selection

Display Alerts

Application.DisplayAlerts = False
’operations...
Application.DisplayAlerts = True

Screen Updating

Application.ScreenUpdating = False

Evaluate

Evaluate("Expression")
or
[Expression]

InputBox

vAnswer = Application.InputBox(Prompt:="Enter range", Type:=8)

Value of Type Meaning 0 A formula 1 A number 2 Text(a string) 4 A logical value(True or False) 8 A cell reference, as a Range object 16 An error value, such as #N/A 64 An array of values

eg. Get a range:

Sub GetRange()
	Dim rng As Range

	On Error Resume Next
	Set rng = Application.InputBox(Prompt:="Enter range", Type:=8)
	If rng Is Nothing Then
		MsgBox "Operation Cancelled"
	Else
		rng.Select
	End If
End Sub

StatusBar

Application.StatusBar = "Message"
code operations....
Hide message:
Application.StatusBar = False

SendKeys

Application.SendKeys "keysss..."
~: Enter, %:Alter, ^:Control

OnTime

Application.OnTime Date + TimeSerial(15, 0, 0), "SubName"
Application.OnTime Now+ TimeSerial(0, 1, 0), "SubName"

OnKey

Sub AssignDown()
	Application.OnKey "{Down}", "DownTen"
End Sub

Sub DownTen()
	ActiveCell.Offset(10, 0).Select
End Sub

Sub ClearDown()
	Application.OnKey "{Down}"
End Sub

Worksheet Functions

Caller

The Active Properties ActiveCell ActiveChart ActivePrinter ActiveSheet ActiveWindow ActiveWorkbook Selection Display Alerts Application.DisplayAlerts = False ’operations... Application.DisplayAlerts = True Screen Updating Application.ScreenUpdating = False Evaluate Evaluate("Expression") or [Expression] InputBox vAnswer = Application.InputBox(Prompt:="Enter range", Type:=8)   Value of Type Meaning 0 A formula 1 A number 2 Text(a string) 4 A logical value(True or False) 8 A cell reference, as a Range object 16 An error value, such as #N/A 64 An array of values eg. Get a range: Sub GetRange() Dim rng As Range On Error Resume Next Set rng = Application.InputBox(Prompt:="Enter range", Type:=8) If rng Is Nothing Then MsgBox "Operation Cancelled" Else rng.Select End If End Sub StatusBar Application.StatusBar = "Message" code operations.... Hide message: Application.StatusBar = False SendKeys Application.SendKeys "keysss..." ~: Enter, %:Alter, ^:Control OnTime Application.OnTime Date + TimeSerial(15, 0, 0), "SubName" Application.OnTime Now+ TimeSerial(0, 1, 0), "SubName" OnKey Sub AssignDown() Application.OnKey "{Down}", "DownTen" End Sub Sub DownTen() ActiveCell.Offset(10, 0).Select End Sub Sub ClearDown() Application.OnKey "{Down}" End Sub Worksheet Functions Caller