Microsoft Office Excel ® Kod Kılavuzu

Microsoft Office Excel® Code Guide

10 Ekim 2009 Cumartesi

Error Statement


'Module1

Option Explicit
Dim Hafıza, i, HatalıSonuç
Dim r, c, er


'A) Constant Error Number Cell Error Value

'xlErrDiv0 2007 #DIV/0! 'xlErrNA 2042 #N/A
'xlErrName 2029 #NAME?
'xlErrNull 2000 #NULL!
'xlErrNum 2036 #NUM!
'xlErrRef 2023 #REF!
'xlErrValue 2015 #VALUE!


Sub HücreÜzeriHataMesajlarıListesi()
On Error Resume Next Hafıza = Array(xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum, xlErrRef, xlErrValue)
For i = 1 To 7
ThisWorkbook.Worksheets(1).Cells(i, 1).Value = VBA.CVErr(Hafıza(i - 1))
Next i
End Sub
Sub HücreÜzeriHataMesajlarıTesbiti()
On Error Resume Next
If IsError(ActiveCell.Value) Then
HatalıSonuç = ActiveCell.Value
Select Case HatalıSonuç
Case CVErr(xlErrDiv0)
MsgBox "#DIV/0! error" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Constant Error Number Cell ERROR VALUE"
Case CVErr(xlErrNA)
MsgBox "#N/A error" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Constant Error Number Cell ERROR VALUE"

Case CVErr(xlErrName)

MsgBox "#NAME? error" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Constant Error Number Cell ERROR VALUE"

Case CVErr(xlErrNull)

MsgBox "#NULL! error" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Constant Error Number Cell ERROR VALUE"

Case CVErr(xlErrNum)

MsgBox "#NUM! error" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Constant Error Number Cell ERROR VALUE"

Case CVErr(xlErrRef)

MsgBox "#REF! error" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Constant Error Number Cell ERROR VALUE"

Case CVErr(xlErrValue)

MsgBox "#VALUE! error" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Constant Error Number Cell ERROR VALUE"

Case Else

MsgBox "Bu hiçbir zaman olmamalıdır!!" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Constant Error Number Cell ERROR VALUE"
End Select
End If
End Sub

'B) SqlState Error Property

Sub ODBCBağlantıHatalarıTesbiti()
On Error Resume Next
With Worksheets(1).QueryTables(1)
.Refresh Set HatalıSonuç = Application.ODBCErrors
If HatalıSonuç.Count = 0 Then
MsgBox "Bağlantı hatasızca tamamlandı" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] SqlState Error"
Else
Set r = .Destination.Cells(1) r.Value = "Aşağıdaki hatalar oluştu:"
c = 0
For Each er In HatalıSonuç
c = c + 1 r.Offset(c, 0).Value = er.ErrorString
r.Offset(c, 1).Value = er.SqlState
Next
End If
End With
End Sub

'C) ErrorCheckingOptions Object

Sub CheckBackground()
On Error Resume Next
' Simulate an error by referring to empty cells.
Application.ErrorCheckingOptions.BackgroundChecking = True Range("A1").Select
ActiveCell.Formula = "=A2/A3"
End Sub
Sub CheckEmptyCells()
On Error Resume Next Application.ErrorCheckingOptions.EmptyCellReferences = True
Range("A1").Formula = "=A2+A3"
End Sub
Sub CheckEvaluationError()
On Error Resume Next
' Simulate a divide-by-zero error.
Application.ErrorCheckingOptions.EvaluateToError = True Range("A1").Value = 1
Range("A2").Value = 0
Range("A3").Formula = "=A1/A2"
End Sub
Sub CheckFormula()
On Error Resume Next Application.ErrorCheckingOptions.InconsistentFormula = True
Range("A1:A3").Value = 1
Range("B1:B3").Value = 2
Range("C1:C3").Value = 3
Range("A4").Formula = "=SUM(A1:A3)" ' Consistent formula.
Range("B4").Formula = "=SUM(B1:B2)" ' Inconsistent formula.
Range("C4").Formula = "=SUM(C1:C3)" ' Consistent formula.
End Sub
Sub IndicatorColorIndexProperty_CheckIndexColor()
On Error Resume Next
If Application.ErrorCheckingOptions.IndicatorColorIndex = xlColorIndexAutomatic Then
MsgBox "Your indicator color for error checking is set to the default system color."
Else
MsgBox "Your indicator color for error checking is not set to the default system color."
End If
End Sub
Sub CheckNumberAsText()
On Error Resume Next
' Simulate an error by referencing a number stored as text.
Application.ErrorCheckingOptions.NumberAsText = True
Range("A1").Value = "'1"
End Sub
Sub CheckOmittedCells()
On Error Resume Next Application.ErrorCheckingOptions.OmittedCells = True
Range("A1").Value = 1
Range("A2").Value = 2
Range("A3").Value = 3
Range("A4").Formula = "=Sum(A1:A2)"
End Sub
Sub CheckTextDate()
On Error Resume Next
' Simulate an error by referencing a text date with a two-digit year.
Application.ErrorCheckingOptions.TextDate = True
Range("A1").Formula = "'April 23, 00"
End Sub
Sub CheckUnlockedCell()
On Error Resume Next Application.ErrorCheckingOptions.UnlockedFormulaCells = True
Range("A1").Value = 1
Range("A2").Value = 2
Range("A3").Formula = "=A1+A2"
Range("A3").Locked = False
End Sub

'D) ShowError Property

Sub ShowErrorProperty()
With Worksheets(1).Range("A10").Validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="5", Formula2:="10" .ErrorMessage = "value must be between 5 and 10"
.ShowInput = False
.ShowError = True
End With
End Sub

'E) DisplayErrorString Property

Sub DisplayErrorStringProperty()
On Error Resume Next
With Worksheets(1).PivotTables("Pivot1")
.ErrorString = "-"
.DisplayErrorString = True
End With
End Sub

'F) ShowErrors Method

Sub ShowErrorsMethod()
On Error Resume Next
If IsError(ActiveCell.Value) Then
ActiveCell.ShowErrors
End If
End Sub

'G) On Error Statement

'On Error GoTo line: Enables the error-handling routine that starts at line specified in the required line argument. The line argument is any line label or line number. If a run-time error occurs, control branches to line, making the error handler active. The specified line must be in the same procedure as the On Error statement; otherwise, a compile-time error occurs. 'On Error Resume Next: Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues. Use this form rather than On Error GoTo when accessing objects.
'On Error GoTo 0: Disables any enabled error handler in the current procedure.


'H) OLEDBErrors Property

Sub OLEDBErrorsProperty()
On Error Resume Next Set objEr = Application.OLEDBErrors.Item(1)
MsgBox "The following error occurred:" & objEr.ErrorString & " : " & objEr.SqlState
End Sub

'I) ODBCErrorsProperty

Sub ODBCErrorsProperty()
On Error Resume Next
With Worksheets(1).QueryTables(1)
.Refresh Set errs = Application.ODBCErrors
If errs.Count = 0 Then
MsgBox "Query complete: all records returned."
Else
Set r = .Destination.Cells(1) r.Value = "The following errors occurred:"
c = 0
For Each er In errs
c = c + 1 r.Offset(c, 0).Value = er.ErrorString
r.Offset(c, 1).Value = er.SqlState
Next
End If
End With
End Sub

'J)TrappableErrors

'3 Return without GoSub '5 Invalid procedure call
'6 Overflow
'7 Out of memory
'9 Subscript out of range
'10 This array is fixed or temporarily locked
'11 Division by zero
'13 Type mismatch
'14 Out of string space
'16 Expression too complex
'17 Can 't perform requested operation
'18 User interrupt occurred
'20 Resume without error
'28 Out of stack space
'35 Sub, Function, or Property not defined
'47 Too many code resource or DLL application clients
'48 Error in loading code resource or DLL
'49 Bad code resource or DLL calling convention
'51 Internal Error
'52 Bad file name or number
'53 File Not found
'54 Bad file mode
'55 File already open
'57 Device I/O error
'58 File already exists
'59 Bad record length
'61 Disk full
'62 Input past end of file
'63 Bad record number
'67 Too many files
'68 Device unavailable
'70 Permission denied
'71 Disk Not Ready
'74 Can 't rename with different drive
'75 Path/File access error
'76 Path Not found
'91 Object variable or With block variable not set
'92 For loop not initialized
'93 Invalid pattern string
'94 Invalid use of Null
'97 Can 't call Friend procedure on an object that is not an instance of the defining class
'98 A property or method call cannot include a reference to a private object, either as an argument or as a return value
'298 System resource or DLL could not be loaded
'320 Can 't use character device names in specified file names
'321 Invalid file format
'322 Can’t create necessary temporary file
'325 Invalid format in resource file
'327 Data value named not found
'328 Illegal parameter; can't write arrays
'335 Could not access system registry
'336 Component not correctly registered
'337 Component Not found
'338 Component did not run correctly
'360 Object already loaded
'361 Can 't load or unload this object
'363 Control Not Specified
'364 Object was unloaded
'365 Unable to unload within this context
'368 The specified file is out of date. This program requires a later version
'371 The specified object can't be used as an owner form for Show
'380 Invalid property value
'381 Invalid property-array index
'382 Property Set can't be executed at run time
'383 Property Set can't be used with a read-only property
'385 Need property-array index
'387 Property Set not permitted
'393 Property Get can't be executed at run time
'394 Property Get can't be executed on write-only property
'400 Form already displayed; can't show modally
'402 Code must close topmost modal form first
'419 Permission to use object denied
'422 Property not found
'423 Property or method not found
'424 Object Required
'425 Invalid object use
'429 Component Can 't create object or return reference to this object
'430 Class doesn 't support Automation
'432 File name or class name not found during Automation operation
'438 Object doesn 't support this property or method
'440 Automation Error
'442 Connection to type library or object library for remote process has been lost
'443 Automation object doesn't have a default value
'445 Object doesn 't support this action
'446 Object doesn 't support named arguments
'447 Object doesn 't support current locale setting
'448 Named Not Argument
'449 Argument not optional or invalid property assignment
'450 Wrong number of arguments or invalid property assignment
'451 Object not a collection
'452 Invalid ordinal
'453 Specified code resource not found
'454 Code Not resource
'455 Code resource lock error
'457 This key is already associated with an element of this collection
'458 Variable uses a type not supported in Visual Basic
'459 This component doesn't support the set of events
'460 Invalid Clipboard format
'461 Method or data member not found
'462 The remote server machine does not exist or is unavailable
'463 Class not registered on local machine
'480 Can 't create AutoRedraw image
'481 Invalid Picture
'482 Printer Error
'483 Printer driver does not support specified property
'484 Problem getting printer information from the system. Make sure the printer is set up correctly
'485 Invalid picture type
'486 Can 't print form image to this type of printer
'520 Can 't empty Clipboard
'521 Can 't open Clipboard
'735 Can 't save file to TEMP directory
'744 Search Not Text
'746 Replacements too long
'31001 Out of memory
'31004 No Object
'31018 Class is not set
'31027 Unable to activate object
'31032 Unable to create embedded object
'31036 Error saving to file
'31037 Error loading from file


'K) ErrorMessageProperty

Sub ErrorMessageProperty()
On Error Resume Next
With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="5", Formula2:="10" .InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With
End Sub

'L) Errors Property

Sub ErrorsProperty()
On Error Resume Next Range("A1").Formula = "'12"
If Range("A1").Errors.Item(xlNumberAsText).Value = True Then
MsgBox "The number is written as text."
Else
MsgBox "The number is not written as text."
End If
End Sub

'M) EvaluateToError Property

Sub CheckEvaluationError()
On Error Resume Next
' Simulate a divide-by-zero error.

Application.ErrorCheckingOptions.EvaluateToError = True
Range("A1").Value = 1
Range("A2").Value = 0
Range("A3").Formula = "=A1/A2"
End Sub

'N) PrintErrors Property

Sub UsePrintErrors()
On Error Resume Next Dim wksOne As Worksheet
Set wksOne = Application.ActiveSheet
' Create a formula that returns an error value.
Range("A1").Value = 1 Range("A2").Value = 0
Range("A3").Formula = "=A1/A2"
' Change print errors to display dashes.
wksOne.PageSetup.PrintErrors = xlPrintErrorsDash
' Use the Print Preview window to see the dashes used for print errors.
ActiveWindow.SelectedSheets.PrintPreview
End Sub


Hiç yorum yok:

Blog Arşivi

Bu gadget'ta bir hata oluştu

Bu Blogda Ara

Contributor

Contributor
Mustafa ULUSARAÇ İstanbul, TÜRKİYE
free counters
T. C. Central Bank Indicative Exchange Rates
Currency Exchange Rate Widget,Currency Converter Widget
Borsa İstanbul