Προγρ Λογ Φύλλων -ΙΝΕΠ - ΚΑΛΠΑΚΑΣ - 4η και 5η ΗΜΕΡΑ

((Excel VBA)Excel VBA)

[email protected]@otenet.gr.: 6944261906

.. 2010

22. . -- [email protected]@otenet.gr

44 55 VBAVBA & & ((DebuggingDebugging & & Error HandlingError Handling))

OfficeOffice & &


44

-- VBAVBA

& & ((DebuggingDebugging & & Error HandlingError Handling))


& & (()) ExcelExcel

Excel (Workbook)

(Workbook) Excel (WorkSheets)

Excel (Rows) (Columns)

(Cell)

, , (Range)


& &

Private Sub InsertSheetButton_Click()ThisWorkbook.Sheets.AddMsgBox : " & ThisWorkbook.Sheets.Count & " "

End Sub

Private Sub DeleteSheetButton_Click()ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count - 1).DeleteMsgBox : " & ThisWorkbook.Sheets.Count & " "

End Sub

Private Sub RenameSheetButton_Click()NewName = InputBox( : ", ")ThisWorkbook.Sheets(1).Name = NewName

End Sub

Excel VBA, WorkBook WorkSheet, :


& &

Private Sub UpdateButton_Click()Dim wb As Workbook

' OpenWorkbooksCombo.Clear

' WorkbookFor Each wb In Workbooks

' OpenWorkbooksCombo.AddItem wb.Name

Next wbEnd Sub

Private Sub ExitButton_Click() Unload Me

End Sub

, (dropdown list). .

Private Sub OpenWorkbooksCombo_Change()Workbooks(OpenWorkbooksCombo.Value).Activate

End Sub


((Charts) Charts) VBA VBA (1/4)(1/4)

, , . , ( ), (ActiveSheet).

(Chart) Excel VBA, .



(Range).



' ' .Private Sub UserForm_Activate()

CreateChartForm.Show vbModeless ' ModalChartTypeComboBox.Style = fmStyleDropDownList ' ListBoxChartTypeComboBox.AddItem "3D-Columns" ' ListBoxChartTypeComboBox.AddItem "3D-Bars"ChartTypeComboBox.AddItem "3D-Pie"ChartTypeComboBox.Value = ChartTypeComboBox.List(0) '.

End Sub

' Private Sub ExitButton_Click()

Unload MeEnd Sub


(Debugging)(Debugging) Bug (moth) Mark II Harvard, 9 1947. (debugging), .

Harvard 9 . 1947


(Debugging)(Debugging)

Bill Gates ( Microsoft) :

Microsoft Testers (), Developers () .

( ).

.

, , .


VBA, .

: - BreakPoints ( )

, (Watches, Locals, Immediate)

((Debugging) Debugging) VBAVBA


((Debugging) Debugging) VBAVBABreak PointsBreak Points

Break Point , VBA Break pointBreak point.

Break Mode, , ImmediateImmediate.

Break PointBreak Point , (Dim).

BBreakreak PPointoint F5, "ContinueContinue" RunRun, -.

Break PointsBreak Points WorkBook .


VBA, BreakPoint .

VBA Menu : Toggle BreakPoint. F9 Menu Debug

VBA BreakPoint, .



((Debugging) Debugging) VBAVBA -- (Step Into / Over / Out )

-- F8 (Sub) cursor BreakBreak--PointPoint. --, .

-- , -- SHIFT+F8SHIFT+F8 "StepStep OverOver" - .

CTRL+CTRL+SHIFT+F8SHIFT+F8 , "StepStepOutOut" -- .

RunRun ToTo CursorCursor

VBA cursor. Break Point.


Debug

VBA

Immediate, Locals Watches

, .



((Debugging) Debugging) VBAVBA WatchWatch

WatchWatch (WatchesWatches) Break modeBreak mode WatchWatch True WatchWatch .

WatchWatch ViewView.

WatchWatch, AddAddWatchWatch DebugDebug, Add WatchAdd Watch .

, Watch TypeWatch Type Watches.

WatchesWatches Project.


((Debugging) Debugging) VBAVBA LocalsLocals

LocalsLocals () Global --.

LocalsLocals.

LocalsLocals ViewView.


((Debugging) Debugging) VBAVBA ImmediateImmediate

ImmediateImmediate Break modeBreak mode . ViewView CTRL+GCTRL+G.

:?ActiveCell.Address ' ( ?)$A$10 ' ( )

? , ENTER .

:Range("A1").Value = ""

, : .

:For j=1 To 10 : Debug.Print MyArray(j) : Next j


((Debugging) Debugging) VBAVBADebug.PrintDebug.Print

Debug.PrintDebug.Print / ImmediateImmediate. :'' VBA'Debug.Print MyArray(4) ' MyArray(4)

Debug.AssertDebug.Assert

Debug.AssertDebug.Assert ( ( )) , TrueTrue FalseFalse. FalseFalse,, VBA BBreakreakModeMode . :

Dim i As Integeri = 200Debug.Assert (i < 90) ' False,

' Break Mode


((Debugging) Debugging) VBAVBA ((Error CodesError Codes) ) VBA VBA (1/3)(1/3)

Error code Error message

3 Return without Gosub5 Invalid procedure call6 Overflow7 Out of memory9 Subscript out of range

10 Duplicate definition (versions 5.0 and 7.0)

10 This array is fixed or temporarily locked (version97)

11 Division by zero13 Type mismatch14 Out of string space16 String formula too complex (versions 5.0 and 7.0)16 Expression too complex (version 97)17 Can't perform requested operation18 User interrupt occurred23 Resume without error28 Out of stack space35 Sub or function not defined (versions 5.0 and 7.0)35 Sub, function, or property not defined (version 97)47 Too many DLL application clients (version 97)48 Error in loading DLL49 Bad DLL calling convention51 Internal error52 Bad file name or number53 File not found54 Bad file mode55 File already open

VBA Error CodesError code Error message

57 Device I/O error58 File already exists59 Bad record length61 Disk full61 Input past end of line63 Bad record number67 Too many files68 Device unavailable70 Permission denied71 Disk not ready74 Can't rename with different drive75 Path/File access error76 Path not found91 Object variable not set (versions 5.0 and 7.0) 91 Object variable or with block variable not set (version 97)92 For Loop not initialized93 Invalid pattern string94 Invalid use of Null95 User-defined error (versions 5.0 and 7.0 only)298 System DLL could not be loaded (version 97)

320 Can't use character device names in specified file names (version 97)321 Invalid file format (version 97)322 Can't create necessary temporary file (version 97)323 Can't load module; invalid format (versions 5.0 and 7.0)325 Invalid format in resource file (version 97)327 Data value named was not found (version 97)

VBA Error Codes




328 Illegal parameter; can't write arrays (version 97)335 Could not access system registry (version 97)336 ActiveX component not correctly registered (version 97)337 ActiveX component not found (version 97)338 ActiveX component did not correctly run (version 97)360 Object already loaded (version 97)361 Can't load or unload this object (version 97)363 Specified ActiveX control not found (version 97)364 Object was unloaded (version 97)365 Unable to unload within this context (version 97)

368 The specified file is out of date. This program requires a newer version (version 97)

371 The specified object can't be used as an owner form for Show (version 97)380 Invalid property value (version 97)381 Invalid property -array index (version 97)382 Property Set can 't be executed at run time (version 97)

383 Property Set can't be used with a read-only property (version 97)385 Need property-array index (version 97)387 Property Set not permitted (version 97)393 Property Get can't be executed at run time (version 97)

394 Property Get can't be executed on write-only property (version 97)400 Form already displayed; can't show modally (version 97)402 Code must close topmost modal form first (version 97)419 Permission to use object denied (version 97)


422 Property not found (version 97)423 Property or method not found424 Object required425 Invalid object use (version 97)

429 ActiveX component can't create object or return reference to this object (version 97)

430 Class doesn't support OLE Automation430 Class doesn't support Automation (version 97)

432 File name or class name not found during Automation operation (version 97)438 Object doesn't support this property or method440 OLE Automation error440 Automation error (version 97)

442 Connection to type library or object library for remote process has been lost (version 97)

443 Automation object doesn't have a default value (version 97)445 Object doesn't support this action446 Object doesn't support named arguments447 Object doesn't support current locale settings448 Named argument not found449 Argument not optional

449 Argument not optional or invalid property assignment (version 97)450 Wrong number of arguments

450 Wrong number of arguments or invalid property assignment (version 97)451 Object not a collection

VBA Error Codes




452 Invalid ordinal453 Specified DLL function not found454 Code resource not found455 Code resource lock error

457 This key is already associated with an element of this collection (version 97)

458 Variable uses a type not supported in Visual Basic (version 97)459 This component doesn't support events (version 97)460 Invalid clipboard format (version 97)461 Specified format doesn't match format of data (version 97)480 Can't create AutoRedraw image (version 97)481 Invalid picture (version 97)482 Printer error (version 97)

483 Printer driver does not support specified property (version 97)

484 Problem getting printer information from the system. Make sure the printer is set up correctly (version 97)485 Invalid picture type (version 97)486 Can't print form image to this type of printer (version 97)735 Can't save file to Temp directory (version 97)744 Search text not found (version 97)746 Replacements too long (version 97)


1000 Classname does not have propertyname property (versions 5.0 and 7.0)

1001 Classname does not have methodname method (versions 5.0 and 7.0)

1002 Missing required argument argumentname (versions 5.0 and 7.0)

1003 Invalid number of arguments (versions 5.0 and 7.0)

1004 Methodname method of classname class failed (versions 5.0 and 7.0)

1005 Unable to set the propertyname property of the classname class (versions 5.0 and 7.0)

1006 Unable to get the propertyname property of the classname class (versions 5.0 and 7.0)

31001 Out of memory (version 97)31004 No object (version 97)31018 Class is not set (version 97)31027 Unable to activate object (version 97)31032 Unable to create embedded object (version 97)31036 Error saving to file (version 97)31037 Error loading from file (version 97)

VBA Error Codes


((Debugging) Debugging) VBAVBA WatchesWatches (1/4)(1/4)

300 . Watches



BreakPoint

2 Watches .



Watch Val().

(Double).



, , .


((Debugging) Debugging) VBAVBA LocalsLocals & & WatchesWatches (1/(1/33))

LocalsLocals ( View), Sum2Sum2 ( ).

BreakPointBreakPoint F8F8 (Step IntoStep Into)

WatchWatch SumTextBox.ValueSumTextBox.Value


((Debugging) Debugging) VBAVBA LocalsLocals & & WatchesWatches (2/(2/33))

(SumTextBox.ValueSumTextBox.Value = Sum2Sum2) FalseFalse .


((Debugging) Debugging) VBAVBA LocalsLocals & & WatchesWatches ((33//33))

, Val()Val()( String ) .


((Debugging) Debugging) VBAVBA ImmediateImmediate ((11//22))

ImmediateImmediate Sum1Sum1 ( 333). .


((Debugging) Debugging) VBAVBA ImmediateImmediate ((22//22))

Sum1Sum1 : Sum1 = Sum2Sum1 = Sum2 FalseFalse.


VBAVBA

OnOn ErrorError OnOn ErrorError VBA . (Error trapping).

ErrorError()() Error()Error() , .

ResumeResume ResumeResume VBA , - On ErrorOn Error.

ErrErr ErrErr (Error) .. Err.Number Err.Description . Clear Raise .


VBAVBA On ErrorOn Error (1/3)(1/3)

(Error Handling) VBA On Error. VBA (Run time error). On Error:

1) On Error Goto 02) On Error Resume Next3) On Error Goto :

, On Error Goto 0, (DefaultDefault) VBA. , VBA , , standard Message Box, (Debug modeDebug mode) .

On Error Goto 0 (Error Handler).


On Error Resume Next, VBA .

On Error Resume Next, . (errors) . , Err.Number. 0, ( ) .

:On Error Resume NextN = 1 / 0 ' If Err.Number 0 Then '

N = 1 ' End If

#11 (Division By Zero). On Error Resume Next , , If Err.Number .End If, .



On Error Goto : VBA .

:

On Error Goto ErrHandler:N = 1 / 0 ' '' 'Exit Sub

ErrHandler:'

Resume NextEnd Sub



Resume , VBA .

( Error Handling Code), (endless loop) (EErrorrror HHandlingandling BBlocklock).

:

On Error GoTo ErrHandler:Worksheets("NewSheet").Activate ' Exit Sub

ErrHandler:If Err.Number = 9 Then '

Worksheets.Add.Name = "NewSheet" ' Resume '

End If

VBAVBA ResumeResume (1/3)(1/3)


Resume Resume Next. VBA .

( #11 - Division By Zero). (EErrorrror HHandlingandling BBlocklock) (error) :

On Error GoTo ErrHandler:N = 1 / 0 ' Debug.Print NExit SubErrHandler:N = 1 ' Resume Next '

'



Resume Resume : . VBA : . .

: On Error GoTo ErrHandler:N = 1 / 0 ' '' '

Label1:'' 'Exit Sub

ErrHandler:Resume Label1: ' Label1:


Resume (Error Handling Block), (Clear ) Err.


VBAVBA ErrErr

ErrErr (ErrorError) .

(Properties): Number (Error Code) Description () Source

(Methods): Clear () Err Raise (user-defined)

Err:'

MsgBox : & Err.DescriptionErr.Clear ' ErrErr.Raise 2000 ' 2000


VBAVBAHH Error()Error()

ErrorError() ()

.

: ErrorError( ( errornumbererrornumber )) errornumbererrornumber o . errornumber .

, #13, :Msg = " : " & Error(Err.Number) & vbCrLf & _

" : " & Error(Err.Number) :


VBAVBA OnErrorOnError, Resume, Err, Error(), , Resume, Err, Error(), Debug.PrintDebug.Print ((11//55))


VBAVBA OnErrorOnError, Resume ,Err, Error(), , Resume ,Err, Error(), Debug.PrintDebug.Print ((22//55))


VBAVBA OnErrorOnError, Resume, Err, Error(), , Resume, Err, Error(), Debug.PrintDebug.Print ((33//55))


(())

(Data Base) , .

.. (Relational) .

RDBMSRDBMS (Relational DB Management System). RDBMS Oracle, SQL Server, MySQL, MS-Access, ..

, de facto standard SQLSQL (SStructured QQuery LLanguage).

, RDBMS. (Programming Interfaces) SQL RDBMS.


SQLSQL (1/4)(1/4)

SQLSQL (SStructured QQueryLLanguage), :

DDLDDL (DData DDefinition LLanguage, ), . DMLDML (DData MManipulation LLanguage, ), . DCLDCL (DData CControl LLanguage, ), .

SQLSQL SQLSQL, .

SQLSQL (char(n), varchar(n),Int,, smallint, Numeric(p, d), real, double precision, float(n), date, time) , ..

Create domain Surname char(20); Surname 20 .


SQLSQL (2/4)(2/4)

MembersCreate Table Members( MemberCode integer not null,

Surname char(20),Name char(20), City char(15), BirthDate date, ClubCode integerprimary key MemberCode )

AFM, 9 , Members Alter table Members add (AFM char(9));

AFM Members Alter table Members drop AFM;

Members Drop table Members;


SQLSQL (3/4)(3/4)

/ / / /

Members Insert into Members (MemberCode, Surname, Name, City, BirthDate, ClubCode )

values (126, , , , null, 140);

ClubCode MembersUpdate Members Set ClubCode = 150 Where ClubCode = 140;

Members Delete from Members Where City = ;


SQLSQL (4/4)(4/4)

DML SQL Select:

: Select 1, 2, n *From 1, 2, , mWhere ; (*) .

: Name, Surname, City Members

Select Name, Surname, City from Members; Members

Select * from Members; Members

Select * from Members where City = ; Members

Select * from Members where City = OR City = ;


, , Microsoft , MDACMDAC(MMicrosoft DData AAccess CComponents). :

ADOADO (AActiveX DData OObjects), OLE DBOLE DB ODBCODBC (OOpen DDatabase CConnectivity)

ADOADO , OLE DB. .

To OLE DBOLE DB COM (.., File System, ).

ODBCODBC , standard, .


MDAC .


-- ODBCODBC

ODBC .. ODBC ManagerODBC Manager. ODBC DriverODBC Driver .. . , .. .

ODBC Manager

ODBC Driver 1 ODBC Driver 2 ODBC Driver N. . .

DataBase 1 DataBase 2 DataBase N

ODBC (Open Dataase Connectivity) standard (DataBasesDataBases). Microsoft 1992, 3.8 ( 2009).


ODBC ODBC DSNDSN Access Access (1/3)(1/3)

(1)

(2)(3)

ODBC DSNDSN(Data Source Name) . 6 .


ODBC ODBC DSNDSN Access Access (2/3)(2/3)

(4)

(6)

(5)

DSN

Access


ODBCODBC DSNDSN Access Access (3/3)(3/3)

ActiveSheet Excel

Access


-- ADOADO

ADOADO (ActiveX Data Objects ) (ProgrammingProgramming InterfaceInterface) , ..

Microsoft 1996 Microsoft ActiveXActiveXcomponentcomponent, IISIIS Microsoft VBA Excel (referencereference) ADOADO (ADO Object Library). :

ToolsTools VBA ReferencesReferences Microsoft ActiveX Data Objects 2.8 Microsoft ActiveX Data Objects 2.8 LibraryLibrary ( )

ADOADO COM componentCOM component ODBCODBC. ODBC . , .

ADO RDO (Remote Data Objects) DAO (Data Access Objects).


ADOADO *:

1. (Connection object)((:: Set Set MyConnMyConn = New = New ADODB.ConnectionADODB.Connection ))

2. RecordsetRecordset (Recordset object)((:: SetSet MyMyRecSetRecSet = = NewNew ADODB.RecordsetADODB.Recordset ))

3. ((: : MyConnMyConn .Open .Open ConnectionStringConnectionString, , UserIDUserID, , PasswordPassword, , OptionsOptions ))

4. / RecordsetRecordset((:: MyRecSMyRecSet.Openet.Open SourceSource, , ConnConnectionection, , CursorTypeCursorType, , LockTypeLockType, , OptionsOptions ))

5. RecordsetRecordset Excel((:: RangeExpression.CopyFromRecordsetRangeExpression.CopyFromRecordset MyRecSMyRecSetet))

6. Recordset Recordset ((: My: MyRecSet.CloseRecSet.Close))7. ((: : MyConnMyConn..CloseClose))8. (freefree) RecordsetRecordset ((:: Set Set MyRecSetMyRecSet = Nothing= Nothing))

9. (freefree) ((:: Set Set MyConnMyConn = Nothing= Nothing))

-- ADOADO

* ADO,


ADOADO Access Access (1/2)(1/2)

Excel .. MS Access

MSMS--AccessAccess TempTemp (ActiveSheet) Excel.

(1)

(2)


ADOADO Access Access ((22/2)/2)


ADOADO :

1. (Connection object)((:: Set Set MyConnMyConn = New = New ADODB.ConnectionADODB.Connection ))

2. RecordsetRecordset (Recordset object)((:: SetSet MyMyRecSetRecSet = = NewNew ADODB.RecordsetADODB.Recordset ))

3. ((: : MyConnMyConn .Open .Open ConnectionStringConnectionString, , UserIDUserID, , PasswordPassword, , OptionsOptions ))

4. RecordsetRecordset ..((:: MyRecSMyRecSet.Openet.Open TableNameTableName, , ConnConnectionection, , CursorTypeCursorType, , LockTypeLockType ))

5. RecordsetRecordset ((:: MyMyRecSetRecSet ..AddNewAddNew))6. RecordsetRecordset

((:: MyMyRecSetRecSet .Fields(.Fields() = Range() = Range(A5A5).Value).Value

7. .. Recordset Recordset ((:: MyMyRecSetRecSet .Update).Update)8. Recordset Recordset ((: My: MyRecSet.CloseRecSet.Close))9. ((: : MyConnMyConn..CloseClose))10. (freefree) RecordsetRecordset ((:: Set Set MyRecSetMyRecSet = Nothing= Nothing))

11. (freefree) ((:: Set Set MyConnMyConn = Nothing= Nothing))

-- ADOADO


ADOADO Access Access (1/2)(1/2)

MSMS--AccessAccess ActiveSheet Excel TempTemp.

(1)

(3)

(2)

(4)


ADOADO AccessAccess ((22/2)/2)


55

OfficeOffice

(())

& &


MSMS--Word, MSWord, MS--PowerPoint, MSPowerPoint, MS--OutlookOutlook

VBA Excel MS-Office (.. MS-Word, MS-PowerPoint, MS-Outlook ).

VBA VBA, Word PowerPoint .

MS-Word Document:

:

MS-PowerPoint :

(Slide) :


MSMS--Word Word MSMS--PowerPointPowerPoint (()) -- ((11/6)/6)

1 5 Word Document .



1 5 PowerPoint .


MSMS--Word Word MSMS--PowerPointPowerPoint (()) -- (5/6)(5/6)


MSMS--Word Word MSMS--PowerPointPowerPoint -- (()) -- (6/6)(6/6)


MSMS--Outlook Outlook -- (()) -- ((11//22))

e-mails , .


MSMS--Outlook Outlook -- (()) -- (2/(2/22))


ee--mail (SendMail)mail (SendMail) (1/2)(1/2)

(1)

(3)

(2)


ee--mail (SendMail)mail (SendMail) (2/2)(2/2)


VBA Excel .

VBA, :

Excel Excel VBA Excel ( Windows File System)


, (ActiveSheet), (Excel) . . CheckBox , ( ). Windows, . , ( ).

(1/7)(1/7)


(2/7)(2/7)

Windows


(3/7)(3/7)

3 .


(4/7)(4/7)

*.xls ActiveSheet


(5/7)(5/7)

*.xls ActiveSheet


(6/7)(6/7)


(7/7)(7/7)

. False


??


ee--mail: mail: [email protected]@otenet.gr

.: 6944.: 6944 261906261906

(Excel VBA)4 5 4 & () Excel & & (Charts) VBA (1/4) (Charts) VBA (2/4) (Charts) VBA (3/4) (Charts) VBA (4/4) (Debugging) (Debugging) (Debugging) VBA (Debugging) VBA Break Points (Debugging) VBA (Debugging) VBA - (Step Into / Over / Out ) (Debugging) VBA (Debugging) VBA Watch (Debugging) VBA Locals (Debugging) VBA Immediate (Debugging) VBA (Debugging) VBA (Error Codes) VBA (1/3) (Debugging) VBA (Error Codes) VBA (2/3) (Debugging) VBA (Error Codes) VBA (3/3) (Debugging) VBA Watches (1/4) (Debugging) VBA Watches (2/4) (Debugging) VBA Watches (3/4) (Debugging) VBA Watches (4/4) (Debugging) VBA Locals & Watches (1/3) (Debugging) VBA Locals & Watches (2/3) (Debugging) VBA Locals & Watches (3/3) (Debugging) VBA Immediate (1/2) (Debugging) VBA Immediate (2/2) VBA VBA On Error (1/3) VBA Err VBA H Error() VBA OnError, Resume, Err, Error(), Debug.Print (1/5) VBA OnError, Resume ,Err, Error(), Debug.Print (2/5) VBA OnError, Resume, Err, Error(), Debug.Print (3/5) VBA OnError, Resume ,Err, Error(), Debug.Print (4/5) VBA OnError, Resume ,Err, Error(), Debug.Print (5/5) () SQL (1/4) SQL (2/4) SQL (3/4) SQL (4/4) - ODBC ODBC DSN Access (1/3) ODBC DSN Access (2/3) ODBC DSN Access (3/3) - ADO - ADO ADO Access (1/2) ADO Access (2/2) - ADO ADO Access (1/2) ADO Access (2/2)5 MS-Word, MS-PowerPoint, MS-Outlook MS-Word MS-PowerPoint () - (1/6) MS-Word MS-PowerPoint () - (2/6) MS-Word MS-PowerPoint () - (3/6) MS-Word MS-PowerPoint () - (4/6) MS-Word MS-PowerPoint () - (5/6) MS-Word MS-PowerPoint - () - (6/6) MS-Outlook - () - (1/2) MS-Outlook - () - (2/2) e-mail (SendMail) (1/2) e-mail (SendMail) (2/2) (2/7) (3/7) (4/7) (5/7) (6/7) (7/7)

Προγρ Λογ Φύλλων -ΙΝΕΠ - ΚΑΛΠΑΚΑΣ - 4η και 5η ΗΜΕΡΑ

Documents

Transcript of Προγρ Λογ Φύλλων -ΙΝΕΠ - ΚΑΛΠΑΚΑΣ - 4η και 5η ΗΜΕΡΑ