VBA/Excel/Access/Word/Application/Application
Версия от 16:33, 26 мая 2010; (обсуждение)
Содержание
- 1 Application.InchesToPoints
- 2 Application.Path
- 3 Benchmark with/without setting the Application.ScreenUpdating
- 4 CalculationVersion: Right four digits indicate the version of the calculation engine whereas the digits to the left indicate the major version of Excel.
- 5 Clearing the Recently Used Files List
- 6 Cursors Available to Use with the Cursor Property
- 7 Demonstration of Window size Properties
- 8 Display application in full screen mode
- 9 Execute the next statement to set the calculation mode to automatic:
- 10 Get application user name
- 11 Get window state information
- 12 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:
- 13 Listing Application CommandBars
- 14 Make sure that the help file can be located:
- 15 MemoryFree Returns the amount of memory in bytes that Excel is allowed to use, not including memory already in use.
- 16 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.
- 17 MemoryUsed Returns the amount of memory, in bytes, that Excel is currently using.
- 18 OperatingSystem Returns the name and version of the Operating System.
- 19 OrganizationName Returns the name of the organization to which the product is registered.
- 20 Performance Implications of Screen Updating
- 21 Pick the Perfect Location with GetSaveAsFilename
- 22 Quitting Application
- 23 Setting a Default File Location
- 24 Specifying the proper Excel version: Warn users who attempt to open the add-in using Excel 97
- 25 start Access
- 26 start Foxpro
- 27 start mail client
- 28 start Power point
- 29 start Project
- 30 start schedule
- 31 start word
- 32 System Information Available Using Application Object Properties
- 33 To avoid these alert messages, insert the following VBA statement in your macro:
- 34 Toggles the calculation mode between manual and automatic and displays a message indicating the current mode:
- 35 Turning off automatic calculation: Sets the Excel calculation mode to manual
- 36 Turning off screen updating
- 37 UserName Returns or sets the name of the current user.
- 38 Use the Application object qualifier to set properties
- 39 Using the StatusBar property to Display Information
- 40 Version Returns the version of Excel that is in use.
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