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