Samples

Microsoft Excel Samples ®

Microsoft Excel '97 has a new interface for macro development, the Visual Basic Integrated Development Environment (IDE). If you have developed macros in Excel before, you will notice that the macro modules are now displayed in the IDE instead of as sheets in the workbook. In this samples file, there are worksheets that explain procedures you can use to automate a variety of common tasks in Microsoft Excel. From each worksheet, you can jump to the IDE to view the code. The procedures can be copied into your own modules, and modified to meet your needs.

Worksheet Functions

Data Access

Working with Arrays

Office Automation

Repeating Tasks

Event Handling

Adding Labels to Charts

To Start, click on one of the areas at the right.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this workbook are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft AnswerPoint Engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers.

Samples Data Access Office Automation Event Handling

xpressed or implied, including, but not limited to the implied warranties of is' and Microsoft does not guarantee that they can be used in all situations. ese examples to provide added functionality, nor will they help you construct Microsoft Solution Providers.

Worksheet Functions

Worksheet Function Examples This worksheet contains sample formulas you can use to complete common spreadsheet tasks. Cells containing formulas are blue. To view a sample formula, hover your mouse cursor over the cell to display the comment. Or, press CTRL+` to switch between displaying values and displaying formulas on the worksheet. For more information about a worksheet function, select the cell containing the function, then click the Edit Formula (=) button on the Standard toolbar.

Suppressing the Display of Error Values It is common for worksheet formulas to return an error value (#DIV/0!, #N/A, #VALUE!,#REF!, and #NUM!) if they are based on an unexpected value. An Example is a simple division formula. If the source cell contains a zero, and #DIV/0! Error will be returned. Source A 25

Source B 0

Original Formula New Formula #DIV/0!

A general way to express this is: =IF(ISERROR(),"",) where is the formula for which you want to suppress the error value. If returns an error, this statement returns "" (a null character), otherwise it returns the result of .

Page 3

Worksheet Functions

Another way to suppress the display of error values is to use the Conditional Formatting Feature, new to Excel '97. The Conditional Formatting feature allows you to have a cell display different formats depending on the contents of the cell. For error values, you need to follow these steps: 1. Select the cell to be formatted (in this case $F$19) 2. Click Conditional Formatting on the Format Menu 3. In the first dropdown box, select "Formula Is" 4. In the edit box next to it, type: =ISERROR($F$19) 5. Click on the format button, and select a format, in this case, a blue background and the same blue font was used. 6. Click OK The result is that for error values, the font color is the same as the background, and nothing will be displayed.

Source A 25

Source B 0

Original Format #DIV/0!

New Format #DIV/0!

Indexing, Matching, and Looking Up Values One of the most common List Management tasks is to find the value at the intersection of a column and a row in a rectangular range of cells on a worksheet. The Lookup Wizard helps you to write formulas to accomplish this task. To use this feature, select a cell within the data range that you would like to use, then select Tools / Wizard / Lookup, and follow the steps in the wizard. For additional information, use the assistant and search using the words "Lookup Wizard".

Conditional Sum Formulas Another common task in Excel is to calculate the sum of the values meeting the conditions you specify. The Conditional Sum Wizard helps you write formulas that accomplish this task. To use this feature, select a cell within the list that you would like to summarize, then select Tools / Wizard / Conditional Sum, and Page 4 follow the steps in the wizard. For additional information, use the assistant and search using the words "Conditional Sum".

Another common task in Excel is to calculate Worksheet the sum of Functions the values meeting the conditions you specify. The Conditional Sum Wizard helps you write formulas that accomplish this task. To use this feature, select a cell within the list that you would like to summarize, then select Tools / Wizard / Conditional Sum, and follow the steps in the wizard. For additional information, use the assistant and search using the words "Conditional Sum".

Page 5

Working with Arrays SUMMARY The most common way to transfer the contents of an array to a worksheet is through a loop, such as a For...Next loop. A For...Next loop allows you to index into the array and transfer elements of the array to their destination address one at a time. You can accomplish this same result without a loop using the Visual Basic, Applications Edition, FormulaArray property of the Range object. In Visual Basic, Applications Edition, the orientation of a one-dimensional array is horizontal. Therefore, if the range of cells on a worksheet that are to receive the contents of an array are also in the same orientation (that is, one row by several columns), the contents can be transferred with a single FormulaArray statement. For example: Sub ArrayDump1() 'For a One dimensional horizontal array Dim x(1 To 10) As Double 'Declares an array of size 10 For j = 1 To 10 'Calculates random values x(j) = j * j Next j 'Transfers array contents to a horizontal area Range(Cells(2, 1), Cells(2, 10)).FormulaArray = x End Sub The above example works fine when the destination worksheet cells involve only one row. However, when the contents of an array need to be transferred to a range of cells with a vertical, as opposed to a horizontal orientation (that is, one column by several rows), the above example is no longer valid.

Goto ArrayDump1 Sample Code

In the event that multiple rows of data are within the two-dimensional array, you must change the orientation of the array. To do this, you can declare the array a two-dimensional array with dimensions of several rows by one column. For example: Sub arraydump2() 'For a two dimensional vertical array 'Declares an array ten rows by one column Dim x(1 To 10, 1 To 1) As Double 'Calculates random values For j = 1 To 10 x(j, 1) = j * j Next j 'Transfers array contents to a vertical area Range(Cells(1, 2), Cells(10, 2)).FormulaArray = x End Sub The two-dimensional array shown in this example allows Visual Basic to set the orientation of the array as vertical; therefore, the array can be transferred to a worksheet without a loop.

Goto ArrayDump2 Sample Code

Goto ArrayDump1 Sample Code

Goto ArrayDump2 Sample Code

Repeating Tasks Often it is necessary to perform the same task on a group of items. These items may be cells in a range, worksheets in a workbook, workbooks in the Application. Although the macro recorder cannot record loops, the recorder can still be used to record the main task to be repeated. Then, with some minor modifications to the recorded code, various types of loops can be created depending on what is needed for an individual project. The samples below focus on a scenario where the programmer has a range of cells in column A containing numbers, and depending on the value of the cell, wishes to change the color of the corresponding cell in column B. First, we can record the single step process of changing the color of the background of a cell: (To record: From the Tools Menu, click Macro, then click Record New Macro) While recording, from the Format menu, click Cells. Then, click the Patterns tab and select a color. This sample uses Yellow (.ColorIndex=6). Now stop the recorder using the Stop button on the Stop Recording Toolbar. The currently selected cell will change color and the following macro will have been recorded: Sub Recorded_Macro() ' ' Macro recorded 6/30/97 ' With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub

Goto Recorded_Macro Sample Code

Note: If, while recording, you selected a cell, your macro may contain something similar to Range("A3").Select. You may want to remove this line, since every time the macro is run, this line will force the cell specified in the Range object to be selected. If you want that cell to be selected first then leave that line in the macro. Now, we can modify the code slightly and add one of the various looping structures to the recorded code.

For Each...Next Loop If we know what range of cells to which we want to apply the recorded action, we can use a For Each…Next loop. In this example, we also want to only affect the cell in column B if the cell in A is greater than 20. To do this we add an If statement around our recorded With statement. This will ensure the color will be changed only if the If statement's condition is true. Lastly, because we are wanting to change the corresponding cell one column to the right of column A (column B), we will replace the Selection property in the recorded code with the Offset method of the looping cell object (cell_in_loop). The resulting code might look like this: Sub For_Each_Next_Sample() ' ' Macro recorded 6/30/97 '

For Each cell_in_loop In Range("A1:A5") If cell_in_loop.Value > 20 Then With cell_in_loop.Offset(0, 1).Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next End Sub

Goto For Each..Next Sample Code

For…Next Loop If you know how many times you wish to loop through your code you can use a For..Next loop. Using our example, if you want to only check 10 cells down, starting from the selected cell, then your modified recorded code might look like this: Sub For_Next_Sample() For Counter = 0 To 9 If Selection.Offset(Counter, 0).Value > 20 Then With Selection.Offset(Counter, 1).Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next End Sub

Goto For..Next Sample Code

Here the Selection object is being used so that the code is not specific to any given range, but will always loop through ten (0 to 9 as a counter) cells below the active cell. The Counter variable increments each time through the loop and can be used within the looping structure. Here it is used as an Offset argument to indicate how many rows to offset from Selection (the current cell). So, if we start this macro and the active cell is A1, the first time through the loop the Counter variable will equal 0, therefore we will Offset 0 rows from A1 as shown in the Selection.Offset(Counter, 0).Value statement.

Do…Loop To determine whether or not to stop a loop based on a certain condition, a Do…Loop might be appropriate. This looping structure allows you to check properties or variable conditions before the loop is executed. In the sample below, we continue to loop until the referenced cell's row number, Selection.Offset(Counter, 0).Row, has exceeded 100. This can be useful if you never want to perform the looping task below row 100. Sub Do_Loop_Sample() Counter = 0 Do Until Selection.Offset(Counter, 0).Row > 100 If Selection.Offset(Counter, 0).Value > 20 Then With Selection.Offset(Counter, 1).Interior

Goto Do…Loop Sample Code

.ColorIndex = 6 .Pattern = xlSolid End With End If Counter = Counter + 1 Loop

Goto Do…Loop Sample Code

Note: There are three more types of Do…Loops available. The various forms offer more flexibility when needed in various circumstances. To find more information about these, and more detail about the other looping structures, please access Help from within the Visual Basic Environment and use the keyword looping.

ach…Next loop.

Attaching Labels to an XY (Scatter) Chart Labels X Values Y Values Datapoint1 2 5 Datapoint2 9 7 Datapoint3 5 3 Datapoint4 4 8 Datapoint5 1 4

Place Labels on Chart

Reset Chart

8 7.5 7

In Microsoft Excel, there is no built-in way to automatically attach text labels to an xy (scatter) chart. You can use a macro to accomplish this. The attached macro demonstrates how to apply labels to an xy (scatter) chart, and assumes that your data and associated labels are arranged on your worksheet in the same fashion as the shaded cells above. To apply the data point labels, press the "Place Labels on Chart" Button. To remove the labels for another demo, press the "Reset Chart" button. To view the macro code attached to the sheet, press the "View Code" button.

6.5 6 5.5

Y Values

5 4.5 4 3.5 3 2

3

4

5

6

7

View Code

8

9

Data Access SUMMARY Data Access Objects (DAO) allow direct manipulation of a database. You must reference the Microsoft DAO 3.5 Object Library (References dialog, Tools menu) in VBE in order to use DAO. You must have the appropriate ISAM or ODBC drivers installed as well. For information on installing ODBC drivers, see "Install an ODBC driver so you can access an external data source" in the help file. To install DAO or ISAMs, see the help topic "Install or remove individual components of Microsoft Office or Microsoft Excel." The paths used in the examples point to the default installation location. If Microsoft Office was installed to a different location, modify the paths in VBE prior to running it. RDO EXAMPLE This example retrieves data from a dBase file using RDO, and places the recordset in a Query Table on a new worksheet. Use this method when connecting to SQL databases. RDO bypasses DAO which decreases the overhead in memory. Sub Dim Dim Dim Dim Dim Dim

RDOExample() ws As Workspace rs As Recordset qt As QueryTable c As Connection ConnectStr As String NewSheet As Object 'If Microsoft Office was not installed to the default location, 'change the path to the appropriate folder prior to running the code ConnectStr = "odbc;Driver={Microsoft dBase Driver (*.dbf)};DBQ=c:\program files\microsoft office\office;" 'Creates a workspace that bypasses DAO Set ws = CreateWorkspace("w1", "admin", "", dbUseODBC) 'Opens the connection to the directory containing DBF files Set c = ws.OpenConnection("", 1, 0, ConnectStr) 'Gets all records from the table, and allows forward movement only 'through the records (fastest type) Set rs = c.OpenRecordset("select * from orders", dbOpenForwardOnly) 'Insert a new worksheet in the active workbook Set NewSheet = Worksheets.Add 'Puts a new Query Table based on the recordset on the new sheet Set qt = NewSheet.QueryTables.Add(rs, Range("a1")) 'Performs a synchronous refresh on the Query Table qt.Refresh False 'Close the recordset rs.Close 'Close the connection c.Close End Sub RESETTING A DAO RECORDSET This example uses the Microsoft Access sample database Northwind.mdb to create a Query Table on a new worksheet, then reset the recordset to a different table within the database.

R

Sub Dim Dim Dim Dim Dim

ResettingDAORecordset() db As Database rs As Recordset qt As QueryTable NewSheet As Object nwind As String 'Default path to Northwind.mdb sample database nwind = "C:\Program files\microsoft office\office\samples\Northwind.mdb" 'Opens the Northwind.mdb sample database Set db = DBEngine.Workspaces(0).OpenDatabase(nwind) 'Opens a recordset containing all records from the customers table Set rs = db.OpenRecordset("customers") 'Insert a new worksheet in the active workbook Set NewSheet = Worksheets.Add 'Insert a Query Table containing the recordset information on the new sheet Set qt = NewSheet.QueryTables.Add(rs, Range("a1"))

'Performs a synchronous refresh on the Query Table qt.Refresh False 'Close the recordset rs.Close 'Opens a recordset containing all records from the orders table Set rs = db.OpenRecordset("orders") 'Changes the recordset for the existing Query Table Set qt.Recordset = rs 'Synchronous refresh the Query Table to display the data qt.Refresh False 'Close the recordset rs.Close 'Close the database db.Close End Sub RETRIEVING DATA FROM MICROSOFT ACCESS This example uses DAO to create a query based on two tables in the Northwind.mdb sample file. If the query already exists, the error handling routine deletes it. Sub Dim Dim Dim Dim Dim

RetrieveAccessData() Nsql As String, Ncriteria As String, Njoin As String nwind As String h As Integer db As Database, qry As Object rec As Recordset, NewSheet As Object 'Default installation location of Northwind.mdb database nwind = "C:\Program files\microsoft office\office\samples\Northwind.mdb" 'When an error is encountered go to the label errorhandler: On Error GoTo errorhandler 'Open database Set db = DBEngine.Workspaces(0).OpenDatabase(nwind) 'SQL statements for query (copied from MS Query's SQL window) Nsql = "SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice "

R

Njoin = "FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID " Ncriteria = "WHERE ((([Products].Discontinued)=No) AND (([Products].UnitsInStock)>20));" 'Create a query named TempQuery Set qry = db.CreateQueryDef("TempQuery") 'Send SQL statement to TempQuery qry.sql = Nsql & Njoin & Ncriteria 'Open the resulting recordset created by TempQuery Set rec = qry.OpenRecordset() 'Add a new worksheet to the workbook containing this code Set NewSheet = ThisWorkbook.Sheets.Add(after:=Worksheets("Data Access"), Type:=xlWorksheet) 'Put field names in row 1 of the new worksheet For h = 0 To rec.Fields.Count - 1 NewSheet.[a1].Offset(0, h).Value = rec.Fields(h).Name Next h 'Copy the dynaset to excel NewSheet.[a2].CopyFromRecordset rec 'Delete temporary query db.QueryDefs.Delete "TempQuery" 'Close the database db.Close 'If no errors encountered, stop executing code Exit Sub 'Error handler to delete the query if it already exists errorhandler: If DBEngine.Errors(0).Number = 3012 Then db.QueryDefs.Delete "TempQuery" 'Begins executing code at the line that caused the error Resume Else MsgBox Error(Err) End If End Sub RETRIEVE DATA VIA ISAM DRIVERS This example retrieves data from a dBase file, and places the results in a new worksheet. You must have the appropriate ISAM drivers installed. Sub RetrieveISAMdata() Dim Db As database, rec As Recordset dPath As String Dim h As Integer, NewSheet As Object 'Default installation path for dbf files dPath = "C:\Program files\microsoft office\office" 'Opens the database Set Db = DBEngine.Workspaces(0).OpenDatabase(dPath, 0, 0, "dBase III")

'Opens a recordset with all records from orders.dbf Set rec = Db.OpenRecordset("SELECT * FROM orders") 'Add a new worksheet to the workbook containing this code Set NewSheet = ThisWorkbook.worksheets.Add(after:=Worksheets("Data Access")) 'Loop through fields, and return field names to worksheet For h = 0 To rec.Fields.Count - 1 NewSheet.[a1].Offset(0, h).Value = rec.Fields(h).Name Next h 'Copy the dynaset to excel NewSheet.[a2].CopyFromRecordset rec 'Close the database Db.Close End Sub LIST TABLES IN DATABASE This example lists the table names in the Microsoft Access sample database Northwind.mdb. NOTE: Microsoft Access 97 must be installed in order to run this code. Sub ListTables() Dim db As Database, TableCount As Long, i As Long Dim dPath As String 'Default location of Northwind.mdb dPath = "C:\Program files\microsoft office\office\samples\Northwind.mdb" 'Open nwind.mdb Set db = DBEngine.Workspaces(0).OpenDatabase(nwind) 'Set a variable to number of tables TableCount = db.TableDefs.Count 'Loop through all tables For i = 0 To TableCount - 1 'Display the table name MsgBox db.TableDefs(i).Name Next 'Close the database db.Close End Sub LIST FIELDS IN DATABASE This example lists the field names from the Customer table. If you do not have customer.dbf installed, please refer to Help topic "Install or remove individual components of Microsoft Office or Microsoft Excel."

Sub List_Fields() Dim db As Database, rec As Recordset Dim fieldcount As Long, i As Long, dPath As String 'Default location of sample dbf files dPath = "C:\Program files\microsoft office\office" 'Open the database Set db = OpenDatabase(dPath, 0, 0, "dBase III") 'Open all records from customer.dbf Set rec = db.OpenRecordset("SELECT * FROM customer") 'Count the number of fields fieldcount = rec.Fields.Count 'Loop same num. times as num. of fields For i = 0 To fieldcount - 1 'Display the field names MsgBox rec.Fields(i).Name Next 'Close the database db.Close End Sub CONNECT TO SQL DATABASE This example returns the table names in an SQL database. You must have access to a SQL database, and have a valid DSN created. For more information on DSNs, click the 32bit ODBC icon in the Windows Control Panel. Sub ODBC_Connection() Dim db As Database, i As Long 'Displays the DSN dialog box allowing the user to select the dsn, 'then prompts for additional information if necessary Set db = DBEngine.Workspaces(0).OpenDatabase("", , , "ODBC;") 'Count the number of tables TableCount = db.TableDefs.Count 'Loop to display all table names For i = 0 To TableCount - 1 MsgBox db.TableDefs(i).Name Next 'Closes the database db.Close End Sub TRAPPING DAO ERRORS The following example demonstrates trapping DAO errors. Displaying the error description often provides useful information to the user. Knowing the error number allows the programmer to trap and handle specific errors.

Sub Trap_DAO_Error() Dim d As database, r As Recordset 'When an error is encountered go to the label errorhandler: On Error GoTo errorhandler 'Don't display built in error messages Application.DisplayAlerts = False 'Attempt to open non-existent DB Set d = DBEngine.Workspaces(0).OpenDatabase("c:\xl95\db4.mdb") Exit Sub 'Exit the sub if no errors encountered errorhandler: MsgBox DBEngine.Errors(0).Description 'Text of error message MsgBox DBEngine.Errors(0).Number 'Error number MsgBox DBEngine.Errors(0).Source 'Where error occurred MsgBox DBEngine.Errors(0).HelpContext End Sub CREATING A TABLE This example demonstrates how to create a new table within an existing database. The following example uses the Northwind sample database installed with Microsoft Access 97. Sub Create_Table() Dim t As Object, f As Object, d As Database Dim dPath As String 'When an error is encountered go to the label errorhandler: On Error GoTo errorhandler 'Default path for Northwind.mdb sample database dPath = "C:\Program files\microsoft office\office\samples\Northwind.mdb" 'Open the Northwind database Set d = DBEngine.Workspaces(0).OpenDatabase(dPath) 'Create new TableDef Set t = d.CreateTableDef("NewTable") ' Add fields to NewTable. Set f = t.CreateField("Field1", dbDate) t.Fields.Append f 'Adds field to the field collection Set f = t.CreateField("Field2", dbText) t.Fields.Append f Set f = t.CreateField("Field3", dbLong) t.Fields.Append f ' Save TableDef definition by appending it to TableDefs collection. d.TableDefs.Append t 'Close the database d.Close Exit Sub errorhandler: MsgBox DBEngine.Errors(0) End Sub

Goto GotoRetrieve RDO Example Access Data Sample Sample Code Code

Goto GotoRetrieve Resetting Access DAO Recordset Data Sample Sample Code Code

Goto GotoRetrieve Resetting Access DAO Recordset Data Sample Sample Code Code

Goto GotoRetrieve RetrieveAccess Access Data DataSample SampleCode Code

Goto Retrieve ISAM Data Sample Code

Goto List Tables Sample Code

Goto List Fields Sample Code

Goto List Fields Sample Code

Goto ODBC Connection Sample Code

Goto Trap DAO Error Sample Code

Goto Create Table Sample Code

Office Automation SUMMARY Automation is a feature of the Component Object Model (COM), a technology that applications use to expose their objects to development tools, macro languages, and other applications that support Automation. For example, a spreadsheet application may expose a worksheet, chart, cell, or range of cells each as a different type of object. A word processor might expose objects such as an application, a document, a paragraph, a sentence, a bookmark, or a selection. The following examples demonstrate automating tasks between Microsoft Excel and other Microsoft applications. For more information on Automation, refer to Understanding Automation in the VBA help file. MICROSOFT ACCESS This example returns the location of the Microsoft Access sample databases. Sub MS_Access() Dim AccDir As String Dim acc As Object 'OLE automation to Access Set acc = CreateObject("access.application") 'Return the path for msaccess.exe AccDir = acc.SysCmd(Action:=acSysCmdAccessDir) 'Display the path MsgBox "MSAccess.exe is located in " & AccDir 'Free up variable storage space Set acc = Nothing End Sub

Goto MS_Access Sample Code

MICROSOFT WORD This example copies the chart from the Chart Labels sheet into a new Microsoft Word document. Sub MS_Word() Goto MS_Word Dim wd As Object Sample Code 'Create a Microsoft Word session Set wd = CreateObject("word.application") 'Copy the chart on the Chart Labels sheet Worksheets("Chart Labels").ChartObjects(1).Chart.ChartArea.Copy 'Make document visible wd.Visible = True 'Activate MS Word AppActivate wd.Name With wd 'Create a new document in Microsoft Word .Documents.Add 'Insert a paragraph .Selection.TypeParagraph 'Paste the chart .Selection.PasteSpecial link:=True, DisplayAsIcon:=False, Placement:=wdInLine End With Set wd = Nothing End Sub MICROSOFT POWERPOINT This example copies the chart from the Chart Labels sheet into a new Microsoft PowerPoint presentation.

Sub MS_PowerPoint() Dim ppt As Object, pres As Object 'Create a Microsoft PowerPoint session Set ppt = CreateObject("powerpoint.application") 'Copy the chart on the Chart Labels Demo sheet Worksheets("Chart Labels").ChartObjects(1).Copy 'Create a new document in Microsoft PowerPoint Set pres = ppt.Presentations.Add 'Add a slide pres.Slides.Add 1, ppLayoutBlank 'Make PowerPoint visible ppt.Visible = True 'Activate PowerPoint AppActivate ppt.Name 'Paste the chart ppt.ActiveWindow.View.Paste Set ppt = Nothing End Sub

Goto MS_PowerPoint Sample Code

MICROSOFT OUTLOOK This example creates and adds information to a new Outlook task. Run Outlook and click Tasks on the Outlook bar to see the new task. NOTE: It may take a few minutes for the task to appear. Sub MS_Outlook() Goto MS_Outlook Sample Dim ol As Object, myItem As Object Code 'Create a Microsoft Outlook session Set ol = CreateObject("outlook.application") 'Create a task Set myItem = ol.CreateItem(olTaskItem) 'Add information to the new task With myItem .Subject = "New VBA task" .Body = "This task was created via Automation from Microsoft Excel" .NoAging = True .Close (olSave) End With 'Remove object from memory Set ol = Nothing End Sub MICROSOFT BINDER This example creates a new Microsoft Binder file, adds sections, manipulates the Microsoft Excel section, and then saves the file. Sub MS_Binder() Goto MS_Binder Sample Dim MyBinderNew As Object, Section As Object Code Dim MyVar As String, MyFile As String 'Create a Microsoft Office Binder session Set MyBinder = CreateObject("office.binder") 'Make binder session visible MyBinder.Visible = True 'Add a new Word section MyBinder.Sections.Add Type:="word.document" 'Add an existing Excel file before the first section MyFile = Application.Path & "\examples\solver\solvsamp.xls" Set NewSection = MyBinder.Sections.Add(FileName:=MyFile, Before:=1)

With NewSection 'Change the name of the section .Name = "Solver Samples" 'Get the value in cell A2 on the 3rd sheet in the workbook MyVar = .Object.Worksheets(3).Range("A2").Value End With 'Save the binder as mybinder.obd, overwriting if it exists already MyBinder.SaveAs Application.Path & "\mybinder.obd", bindOverwriteExisting 'Hide the binder MyBinder.Visible = False 'Disassociates the object, releasing the resources Set MyBinder = Nothing 'Shows the value from A2 on the 3rd sheet in the Excel section MsgBox "The following data is from cell A2 in the Solver Samples section." & Chr(10) & Chr(10) & MyVar End Sub

Programming with Events Often programmers wish to have a certain event trigger a macro to perform some task. Microsoft Excel 97 now offers this capability. Events are always associated with an object. Examples of these might be a worksheet, or a workbook. In this sample file we will only discuss a few of the events available and use a MsgBox as a filler to demonstrate where regular code could go. To find out more about any of these and other events, click on the assistant while in the VBE and use the object name and the word "events" as your keywords. (e.g. worksheet events).

BeforeDoubleClick A very popular type of Event is the BeforeDoubleClick event on a worksheet. A very simple example may look like: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) MsgBox "You double clicked on cell " & Target.Address Cancel = True End Sub This event will fire whenever the user double clicks a cell in the worksheet. The parameter Target is passed into the macro so the programmer will know what cell has been double clicked. The Cancel argument has a default value of False but can be changed to True within the code. Setting Cancel to True will cancel the default action for the event. In this case, the default action for a double click on a cell is to switch to edit mode on the cell. Since we set Cancel to True, this will not occur. If you want to still have the default action occur, then the Cancel=True line can be removed.

Change Another very useful event is the Change event for a worksheet. Any time the user enters a new value in any cell this event fires. Private Sub Worksheet_Change(ByVal Target As Excel.Range) MsgBox "This sheet has changed in cell " & Target.Address End Sub Note: This event does not occur if the sheet has just been recalculated. There is an event called Calculate which will fire when the worksheet recalculates.

BeforeClose A useful Workbook related event is BeforeClose. These events can be used to perform "housekeeping" tasks before a file is saved or closed. Sub Workbook_BeforeClose(Cancel As Boolean) a = MsgBox("Do you really want to close the workbook?", vbYesNo) If a = vbNo Then Cancel = True End Sub

In this example, the user is prompted to close the file regardless if the file has been saved or not. Using Events with the Application Object Before you can use events with the Application object, you must create a new class module and declare an object of type Application with events. For example, assume that a new class module is created and called EventClassModule. The new class module contains the following code. Public WithEvents App As Application After the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.) Before the procedures will run, however, you must connect the declared object in the class module with the Application object. You can do this with the following code from any module. Dim X As New EventClassModule Sub InitializeApp() Set X.App = Application End Sub After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur. Note: To find out more information on all events, please consult the Help file for further information.

cel As Boolean)