The Workbook and Window Objects

Thisworkbook Vba

You have already seen in action, in some of the examples in this chapter, the Workbooks, and Worksheets collection objects, as well as the Workbook and Worksheet objects. The difference between collection objects and regular objects was discussed earlier. When working with these objects, keep in mind that the Workbook object is higher in the hierarchy than the Worksheet object. If you are familiar with Excel, this makes sense to you because a single workbook can hold multiple worksheets....

Adding Sound to Your VBA Program

Microsoft removed support for playing sound files in Excel several versions ago. This leaves two choices for playing sounds in Excel applications with VBA ActiveX controls and the Windows API application programming interface . As there are no ActiveX controls for playing sound that currently ship with VBA, the Windows API will be used for adding sound to your VBA programs in this book. There is a multimedia control that comes with Windows and it can be used to play sound files in your VBA...

The VBA Integrated Development Environment IDE

Before learning how to program in VBA, you have to learn how to use the software required for creating your projects. The VBA development software is included with each component of the Microsoft Office suite of programs, including Excel. Starting the VBA development software places you in the VBA programming environment IDE, which provides you with a number of tools for use in the development of your project. An IDE is software used by programmers for rapid application development RAD . IDE's...

Adding ActiveX Controls to a Form

Like the Worksheet object, the UserForm object is a container object, meaning it is used to hold other objects. When a form is added to a project, the Control Toolbox should automatically appear see Figure 6.3 . If the Control Toolbox does not appear, select View Toolbox from the menu bar. There will be a few additional controls displayed in the control toolbox when viewed with a form relative to a worksheet , including the MultiPage and Frame controls discussed later . ActiveX controls are...

The Shape Range Collection Object

Shapes Excel

The ShapeRange collection object represents a collection of Shape objects that may contain all, some, or just one of the Shape objects in the drawing layer of a worksheet. A ShapeRange collection object can be constructed from the current shapes using any of several criteria defined in decision structures If Then . For example, a ShapeRange collection object could be constructed out of just those shapes that are of type AutoShape, or perhaps only those Shape objects that are lines. If you want...

Manipulating a Shape Object

After a Shape object is selected from the Shapes collection object, you can edit the shape through its properties and methods. As always, the properties and methods available are specific to the type of Shape object. Also, there may be properties and methods of subordinate objects available for editing. The following example adds a rectangle to the active worksheet and manipulates a few of its properties the result is shown in Figure 10.4. 100, 100, 50, 50 .Select With Selection .Name Red...

Magic Squares Spreadsheet Selectionchange Procedure

Pages collection object, MultiPage control and, 223 PageSetup object, PrintArea property of, 324 Parameters, 40, 41 with event procedures, 57-59 Parent class, 151 Parentheses, order of operator execution and, 92 PassByRef procedure, 64 PassByVal procedure, 64 PasswordChar property, 299 Paste method, 121 Path property, of Workbook object, 281 Period . , avoiding in XML element names, 336 Picture property, 13, 79, 241, 317 Pie chart, 384 PieClock.xls workbook, 388 PlaceComputerShips sub...

Initializing Battlecell and Starting the Game

The worksheet module named Battlesheet contains most of the game's code because this worksheet serves as the user interface and contains the Command Button control that starts play. The code begins with a few module-level variables that are used in multiple procedures in this module. Private allowSelection As Boolean Private gameStarted As Boolean Private ships As Variant Private Const NUMSHIPS 5 The module level variables allowSelection and gameStarted are used by the program to distinguish...

Constructing the Biorh Ythms and the Time of Your Life PRogRAm

The Time Your Life

This project will utilize several of the VBA programming components discussed in this chapter. The project contains several different examples of data types including integer, floating point, string, and date types. I introduce some new functions designed to work with the date and string data types. The project also demonstrates nesting functions, the use of constants, and some simple mathematical operations. The majority of the work for this project will be handled by the Excel application via...

Table Dialog Types Used with the File Dialog Object

Dialog Type VBA Constant FileDialogType File Picker msoFileDialogFilePicker Folder The Execute method allows the user to carry out the specified action of the dialog box for files that are compatible with the Excel application for example, files of type .xls, .xlt, .csv, and so on . For example, the Open dialog box allows the user to select one or more files to open when the Execute method of the FileDialog object is invoked. When the following statement follows the Show method for the Open...

Validation with a Spreadsheet Cell

In older versions of Excel, validation of spreadsheet content meant writing a lot of code to ensure the data was of proper type and or format. With the latest versions of Excel, this is no longer the case. Data validation is now included in the Excel application, so you don't necessarily have to write any code. Figure 4.2 to shows the data validation dialog box (select Data, Validation from the Excel application menu). Use this tool in your spreadsheets to force validation of data entered by...

Write A Vba Procedure That Outputs A Range After Being Selected By The User

Write a VBA procedure that outputs a range after being selected by the user one statement will do it . 2. Write a VBA procedure that first asks the user to input some text and then changes the caption of the current window to the text value input by the user. 3. Write a VBA procedure that adds three additional workbooks to the application and 10 additional worksheets to each workbook added. Hint Use object variables and nested For Each loops. 4. Write a VBA procedure that deletes all but one...

Creating an Embedded Chart

Refedit Excel

To add an embedded chart to a worksheet, use the Add method of the ChartObects collection object. The AddEmbeddedChart sub procedure creates the same column chart as the AddChartSheet sub procedure listed in the previous section however, it embeds the chart on an existing worksheet named Embedded Charts. Public Sub AddEmbeddedChart Dim dataRange As Range Set dataRange Range frmDataRange.txtDataRange.Text frmDataRange.Hide Sheets Create Chart .ChartObjects.Add Left 200, _ Top 50, Width 500,...

Programming Components within Excel

Command Caption

Not everything of interest to the VBA programmer can be found in the VBA IDE. There are a few programming-related components that you can access from the Excel application. The components I am referring to are the Macro items found under the Tools menu, and three of the available toolbars Visual Basic, Control Toolbox, and Forms found in the View menu in Excel. Now that you've had an introduction to the VBA IDE, it's time to look at development tools accessed directly from Excel. To begin, take...

The Multi Page Control

Excel Vba Multiseiten

The MultiPage control is another example of a container control that groups or organizes the user interface on a form into multiple categories. An example of the MultiPage control in the Excel application is the Options dialog box shown in Figure 6.10. The Options dialog can be selected in the Excel application from the Tools menu. You can see from this example that the MultiPage control allows you to cram a lot of options onto a single form. The MultiPage control allows you to design an...

Designing the Math Game

The program interface is built from a single Excel worksheet. The worksheet is formatted with colors and a large font to make it easy for the user to see the questions. The macro recording tool is activated while formatting the worksheet in order to save most of the interface design as VBA code. ActiveX controls Option Buttons and a Command Button control are drawn on the worksheet in a convenient location to provide the user with a selection of mathematical operators, and an easy way to start...

The List Object Object

As discussed earlier, when you import XML data into a worksheet you have the choice to insert the data as an Excel list. When adding XML data to a list, Excel creates a ListObject object to represent the list. The ListObject object is subordinate to the Worksheet object therefore, all ListObject objects added to a worksheet are returned as a collection via the ListObjects properties of the Worksheet object. Individual ListObject objects can be accessed from the ListObjects collection. Dim...

Vba Program Challenges

Load the Wordfind.xls project and open the IDE. Find the PlaceWordO sub procedure and set a breakpoint on the statement If Not SelectionValid wordDirection Then. Return to Excel and run the program by clicking the Clear All button. Do not click refresh Next, select a cell in the puzzle grid and click on an arrow button. When the debugger is invoked follow the order of program execution to see which statement generates the runtime error and triggers the code in the error handler. 2. Load the...

Project Bior HytHMs and tHe TiMe of Your Life

The Biorhythms and the Time of Your Life program see Figure 2.1 begins by asking for the user's name and birth date. The program then calculates the length of the user's life in years, months, days, hours, minutes, and seconds. Following the user input, the user's name, birth date, and age in the aforementioned units are displayed in the worksheet. The worksheet also contains an embedded chart that displays the current state of the user's three biorhythm cycles physical, emotional, and...

General Purpose Public Procedures

The procedures listed below could be used in just about any VBA project. You have already seen the PlayWav() procedure in the Battlecell program from Chapter 5. I have added one more procedure called Delay(). The entire content of the code module follows Public Declare Function sndPlaySoundA Lib winmm.dll _ (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long Public Sub PlayWav(filePath As String) sndPlaySoundA filePath, CONTINUE_CODE End Sub Public Sub Delay(curTime As Single,...

Table Select Properties of the Blackjack Form

Python Programming Precedence Table

Blackjack CenterScreen fmBorderStyleNone frmDealer and frmPlayer Dealer and Player fmBorderStyleSingle imgDlrl through imgDlr5 and imgPlayerl through imgPlayer5 fmBorderStyleSingle cmbNumDecks fmStyleDropDownList l fmStyleDropDownCombo 2 cmdHit Hit False cmdDeal Begin True Table 6.6 Select Properties of the Blackjack Form continued Table 6.6 Select Properties of the Blackjack Form continued To set the size of the Image controls, I first set the AutoSize property of one Image control to true....

File Input and Output IO

Vb6 Open Dir

VBA includes several objects, methods, and functions that can be used for file I O. You have probably surmised that one possibility for file I O involves the Workbook object and its methods for saving and opening files however, there are other tools available in VBA, the most relevant of which will be discussed in this chapter. When a VBA application requires file I O, it often involves a relatively small amount of data stored in program variables, and not in a worksheet or document. With...

The Chart Sheet Interface

Refedit Excel

This chapter teaches you how to program Excel's Chart object so the interface for the project is built from a chart. Specifically, a chart sheet consisting of a bubble chart will serve to display the images. The requirements state that the game must involve a 10 by 10 grid of 100 images. To satisfy this requirement I will create the chart from ten data sets consisting of ten x,y-value pairs. The data is charted as ten different series in the chart. Each data set must use the same set of values...

Constructing THe Colorful Stats Program

Excel Vba Design

When starting a project, programmers often compile a list of specific requirements, then refer to this list while designing the algorithm(s) that will be followed when writing the program. The advantage you have when compiling a requirement list is that the source document can be used to build a protocol for testing the program. I will follow this procedure for the chapter projects including the Colorful Stats project that follows. Requirements of the Colorful Stats Program The purpose for the...

Components of the User Form Object

In the same manner as ActiveX controls, when you select a form, its properties appear in the Properties window in the VBA IDE see Figure 6.3 . Table 6.1 lists some of the properties of the UserForm object that you will commonly set at Design Time. Table 6.1 Selected Properties of the UserForm Object Name Sets the name of the UserForm object for use as a code BackColor Sets the background color of the form. Caption Sets the text displayed in the title bar. Height Sets the height of the form....

The Option Button Control

The Option Button control is similar to that of the Check Box control in that it offers the user a selection from a group of possibilities. The difference between the two the Option Button control gives the user one selection therefore, when the user selects an Option Button from a group of Option Buttons, a previously selected Option Button is automatically deselected. Option Button controls are grouped by the container on which they have been added. So no matter how many Option Button...

Saving and Opening XML Documents

To save a workbook as an XML document use the SaveAs method of the Workbook object. The following example saves the workbook as an XML document with the name myFile.xml using two named arguments Filename and FileFormat with the SaveAs method. ActiveWorkbook.SaveAs Filename myFile.xml, FileFormat xlXMLSpreadsheet The constant xlXMLSpreadsheet assigned to the FileFormat argument specifies the Excel-XML format. To open an XML document previously saved with the Excel-XML structure use either the...

Accessing Existing Charts

When creating a chart in Excel, you have the choice of embedding the chart in an existing worksheet or creating a new worksheet to hold the chart. When a chart is created and placed in a new worksheet, it is referred to as a chart sheet. Chart sheets are special because their only function is to display a chart they cannot be used for holding any other data. Worksheets and chart sheets serve as containers for embedded charts. There are no limits (other than system memory) to the number of...

Initializing the Chart Sheet

The public sub procedure Main is triggered from the form button on the chart sheet and contains calls to the initialization procedures for the chart sheet, then scans the chart for score sequences. Screen updating is initially turned off otherwise Excel will update the screen as images are added or removed from the chart. Screen updating is turned back on so that the user can see the chart before it is scanned for score sequences. Note that the ChartTitle object is used to display help messages...

Saving Worksheets to XML Files

Saving existing data from a worksheet to an XML file is easy. Select File, Save As from the application window and choose one of two possibilities for XML file types from the Save As dialog box as shown in Figure 8.8. If you choose to save the data as an XML spreadsheet, Excel will use its own schema to define the document. As you might expect, the XML required to define a spreadsheet is quite long, but you don't have to worry about that because Excel creates it for you. The root tag is <...

Using VBA File IO Methods

In addition to the Open , Save , and SaveAs methods of the Workbooks, Workbook, and Worksheet objects, VBA and its associated object libraries include several I O objects such as the Dialogs, FileDialog, FileSystem, and FileSystemObject objects, and other subordinate objects. Some of these objects are conceptually more difficult to use and therefore will not be discussed in this chapter however, I will show you how to use one object from the Office library and VBA's Open statement for adding...

Chapter Project Blackjack

The Blackjack game is a favorite for beginning programmers because it is relatively straightforward programming and can be a lot of fun to customize. The game is saved as Blackjack.xls on the CD-ROM accompanying this book. I added some sound to the game, but it could easily be dressed up with features such as animation or odd rule twists. This particular version uses an Excel UserForm and various ActiveX controls to simulate the card game. There are two players, the user and the computer, and...

Writing the Code for Blackjack

Since the Blackjack form is the major component of the user interface, its code module contains most of the program code. Much of this code is contained in event procedures of the UserForm object and the ActiveX controls it contains. Several procedures private to the Blackjack form's code module are added to support the tasks required for the game. Program code for shuffling the cards is contained in the code module for the Shuffling form and public variable declarations and procedures are...

The Scroll Bar Control

You have undoubtedly seen and used scroll bars in numerous applications for scrolling through lengthy documents or large figures. Scroll bars sometimes automatically appear on the sides and or the bottom ofVBA controls so the user can view the entire content displayed in a control. Situations such as these require nothing extra from you, or your program the scroll bars are simply there to provide the user with a method of seeing the complete content of the control however, VBA also provides a...