Topics in Learning Excel Programming
In general, the education of an Excel programmer breaks down into a few main categories, as follows. First, you need to learn a bit about the environment in which Excel programming is done. This is the so-called Visual Basic Editor or Excel VBA Integrated Development Environment IDE for short . We take care of this in Chapter 3 and Chapter 4. Next, you need to learn a bit about the basics of the programming language that Excel uses. This language is called Visual Basic for Applications VBA ....
Objects Defined
There is no need to get too abstract here with the definition of an object. It really is a pretty simple thing to understand. You can think of objects as separate computer programs with specific and often common functions that are available for repeated use in your programs. Objects are dynamic in that they can be easily manipulated in code with the various parameters used to define them. In one common analogy, objects are equated as nouns in the English language. A programming object can be...
Using the WithEnd With statements
If you want your code to change several properties of a control, you can use a With End With block of code to make your code easier to read. For example, if you want your code to change several properties of a control named myControl on a form named myForm and the code isn't in a class module , you could include that lengthy identifier on every line of code, as shown here Forms myForm.myControl.BackStyle 1 Forms myForm.myControl.BackColor vbWhite Forms myForm.myControl.ForeColor vbRed Forms...
ObjectOriented Programming An Overview
I decided early on in the process of writing this book that I would concentrate my efforts on providing guidance in the creation of class-based solutions to Excel VBA coding problems where possible. I have found through programming in Access, VB 6, and then VB.NET that using OOP techniques has helped me visualize my applications more clearly, helped me to better organize my code, and has been invaluable in making my code easier to maintain. Standard code modules and procedural programming allow...
Worksheets Collection Properties and Methods
Read-only. Returns a collection holding all the horizontal page breaks associated with the Worksheets collection Set Get whether the worksheets in the collection are visible. Also can set this to xlVeryHidden to prevent a user from making the worksheets in the collection visible Read-only. Returns a collection holding all the vertical page breaks associated with the Worksheets collection Method. Parameters Before , After , Count , Type . Adds a worksheet to the collection. You can specify where...
Controlling Excel from Word
As you might expect, you can also control Excel from another application such as another programming language or a Word VBA procedure . For example, you might want to perform some calculations in Excel and return the result to a Word document. You can create any of the following Excel objects with the adjacent functions Application object Workbook object CreateObject Excel.Sheet Chart object An Excel VBA procedure created this Word document. An Excel VBA procedure created this Word document....
Using Arrays with Recordsets
Sometimes you may choose to populate an array with data from a recordset. Perhaps you're intending to pass the array to a Windows API, and because APIs do not accept recordsets as parameters, this is the only way you can do it. Typically, you would define the array and then loop through the rows, appending data to the array as you went, as the following code illustrates Dim varMyArray As Variant Dim varField As Variant Set rst dbs.OpenRecordset Table1, dbOpenSnapshot rst.MoveLast ReDim...
The RangeAutoFilter Method
The AutoFilter method of a Range object is a very curious beast. You are forced to pass it strings for its filter criteria, and hence you must be aware of its string handling behavior. The criteria string consists of an operator , gt , lt , gt , and so on followed by a value. If no operator is specified, the equals operator is assumed. The key issue is that when using the equals operator, AutoFilter performs a textual match, whereas using any other operator results in a match by value. This...
Entering VBA Code
Of course, most of the VBA code you create will not be recorded, but instead entered at the keyboard. As you type your VBA code, the Visual Basic Editor checks each line for syntax errors. A line that contains one or more errors will be displayed in red, the default color for errors. Variables usually appear in black. Other colors are also used comments see later are usually green and some VBA keywords Function, Range, etc. usually appear in blue. These default colors can be changed if you...
Create the UserForm 1
Create the dialog shown in Figure 18-7 in the Print.xls workbook. Name the dialog dlgPrintSheets and set its Caption property to Print Sheets. Then change the PrintSheets procedure to The dlgPrintSheets dialog has two command buttons and one list box Place a list box on the form as in Figure 18-7. Using the Properties window, set the properties shown in Table 18-4. Table 18-4. Nondefault Properties of the List Box Table 18-4. Nondefault Properties of the List Box When the Cancel property of the...
The Range Object as a Collection
The Range object is rather unusual in that it often acts like a collection object as well as a noncollection object. For instance, it has an Item method and a Count property. On the other hand, the Range object has many more noncollection-type members than is typical of collection objects. In particular, the average member count among all other collection objects is 19, whereas the Range object has 158 members. Indeed, the Range object should be thought of as a collection object that can hold...
Stepping Through Code
There are two ways to begin the process of stepping through the code of a Sub procedure Select the name of the procedure in the Macro Name list box and press the Step Into button. This will display the code module containing the procedure the first line of the procedure will be highlighted in yellow, as in Figure 2-22 . Add a breakpoint as described in the following section, then run the Sub procedure in the usual way. When the code window is displayed, with a line of code highlighted, you can...
The Access Object Model
By now you've probably read at least a few of the chapters in this book and have realized that there are a lot of tasks you can accomplish by programming in VBA. One concept that should be abundantly clear is that to use VBA to manipulate Access, you need some knowledge about the Access object model. It was discussed briefly in Chapter 4, but as a refresher, an object model is a set of objects and collections that programmers use to manipulate a program. Microsoft Access has a rich object...
The Fundamentals of the Hungarian Convention
The Hungarian convention has a very straightforward design. It dictates that a name may contain up to five parts, and that the parts are combined in the order of prefix es , tag, base name, qualifier, and suffix A prefix precedes a tag to provide clarification. It can describe one or more important properties with one or two lowercase characters. A tag is considered by some to be the critical, non-optional element. A tag should be unique and easily differentiated from similarly named tags. A...
Print Statement
Print filenumber, outputlist filenumber A list of expressions to output to a file. Spc n Tab n expression charpos Spc n Insert n space characters before expression. 450 Chapter 7 - The Language Reference Position the insertion point either at the next print zone by omitting n or at column number n . Position of the insertion point for the first character of the next expression. Description Outputs formatted data to a disk file opened for append or output. Rules at a Glance You can delimit...
Naming conventions for Variables
Some programmers use naming conventions to identify the data type of a variable as part of the variable's or constant's name. The naming conventions are entirely optional you don't have to use them. A lot of VBA programmers follow them, though, so you're likely to see them in any code you happen to come across. The idea behind a naming convention is simple When you define a new variable, make the first three letters of the name the tag stand for the type of variable or object. For example, the...
Repeating Actions in VBA
Do Loops Do While and Do Until Watching a Procedure Execute While Wend Loop For Next Loop For Each Next Loop Exiting Loops Early Nested Loops What's Next. Now that you've learned how conditional statements can give your VBA procedures decision-making capability, it's time to get more involved. Not all decisions are easy. Sometimes you will need to perform a number of statements several times to arrive at a certain condition. On other occasions, however, after you've reached the decision, you...
Adding Editing and Deleting Rows
Not all recordsets are editable, and the same can be said about some rows. Snapshot recordsets are never editable, and user permissions and record locks can result in recordsets or individual rows that you cannot edit. In addition, joins in some recordsets that are based on multiple tables can render the entire recordset uneditable. The procedure for adding rows to a recordset is quite simple Open the recordset, issue the recordset's AddNew method, make the additions, and then issue the Update...
An Example Vapor Diffusion in a Tube
An air-filled tube 20 cm long allows water vapor to diffuse from a source liquid water to a drying chamber, where the vapors are dissipated. Initially the tube is capped so that the vapor cannot escape. The temperature of the tube is held at 30 C. The equilibrium vapor pressure of water at this temperature is 31.8 mm Hg thus the vapor pressure inside the tube is 31.8 mm Hg. When the cap is removed, the vapor will diffuse toward the drying chamber, where the water vapor pressure is zero. We wish...
Popup Menus
Excel's built-in shortcut menus are included in the command bar listing created by the macro ListFirstLevelControls, which you saw earlier in this chapter. The following modified version of this macro shows only the command bars of type msoBarTypePopup, as shown in Figure 15-11. SHide PivotChart Field Buttons 3956 gt W tateifl . All Lei-els FatsEs I PofNfiE1 The code to display the popups is shown here Dim ctl As CommandBarControl Dim cbr As CommandBar Dim iRow As Integer If Not...
Opening Published Databases Via Code
Access provides the OpenCurrentDatabase method to open a database, including databases that have been published to SharePoint or other types of websites. OpenCurrentDatabase is a member of the Application object and takes three parameters, two of which are optional. The following table describes those parameters. The full name and path or URL string to the database to be opened. Required. The Boolean value which determines whether the database should be opened in exclusive mode. Optional, and...
Data Source Task Pane
The Data Source task pane is a fast, easy way to manage record sources. It's better than the field list because the task pane shows all of the source fields, and you have the option of viewing source, related, and other tables. That means the pane displays the tables and queries so that you can easily select a field. If you have a form and you want a combo box for a look-up field, for example, use the source, related, and other tables to select a field and drag it onto the form. Voil , a combo...
Environ Environ Functions Named Arguments
Environ envstring number envstring The name of the required environment variable. The ordinal number of the environment variable within the environment string table. Environ returns a string containing the text assigned to envstring. Description Returns the value assigned to an operating-system environment variable. Rules at a Glance A zero-length string is returned if envstring doesn't exist in the operating system's environment-string table, or if there is no environment string in the...
Using the BuiltIn VBA Registry Functions
Many programmers use global variables to hold values that are used throughout the application. There are two problems associated with this approach. First, if an unhandled error occurs, all your global variables are reset. Second, you have to reassign their values every time the application is launched. An alternative is to store this type of value in the database, but if you're storing the connection string to the remote data store, it might be a little difficult to get at if your application...
Control Object Methods
The methods you can use with a control object are explained in the following table. For all of these methods, the specified object is the control whose method is being called. Forces the list in the specified combo box to drop down. Moves the specified object to the coordinates specified. Updates the data behind a control by requerying the source data for the control. Moves the focus to the specified control. Sizes the control to fit the text or image it contains. Resets a control whose value...
DictionaryStyle Headings
Phone books and dictionaries typically add the first entry and last entry at the top of the page to make it easier to find information. You can accomplish this on your reports by adding some code in several different sections. The code uses the Format event to create listings at the top of each page. Because this event does not fire for reports open in Report view, you'll need to open the report in Print Preview to see the effect. The report you'll create is a listing of the attendees for the...
Other Considerations When Converting
Keep in mind that saving to a different file format is only a small part of the process. As already discussed, there may be issues involving code, references, macros, security, and integrating with other applications. Moving to newer versions is certainly easier than moving backward. Newer features may be lost or have only part of their functionality. Custom features may not be recognized or implemented as expected. Despite those concerns, it is certainly handy to have the ability to save a...
Creating Class Methods
Class modules have subs and functions, but to give the impression that they're somewhat special, they're called methods. It makes some sense when you consider that a class's procedures carry out actions on its properties, and therefore, constitute the method by which those actions are executed. In the same way that methods are executed against objects in the Access object model, class methods are executed against class objects. For example, to move a DAO recordset cursor to the next record, you...
Two Ways to Specify Arguments of Methods
VBA methods usually take one or more arguments. The Sort method, for example, takes 10 arguments. The syntax of the Sort method is object.Sort key1, orderl, key2, order2, key3, order3, header, orderCustom, The object argument is required all other arguments are optional. You can specify the arguments of a method in two ways. One way is to list the arguments in order as they are specified in the preceding syntax, i.e., Range A1 E150 .Sort Last Name, xIAscending which sorts the data contained in...
Writing to Text Files Using Print
Print enables you to write text files in any format you just have to do a bit more work. To see the effect of using Print instead of Write, change the Write statement in the WriteFile sub as follows Print iFNumber, dDate, sCustomer, sProduct, dPrice The output looks like that in Figure 11-3. If you want to read data in this format, you can read each line of the file using the Line Input statement. You then need to have code to parse out the data. Taking a hint from Write, you might want to use...
The Function to Set a Key Value
The next procedure, SetKeyValue , wraps both RegOpenKeyEx and RegSetValueEx functions to open the subkey and set its value, respectively. After each function call, it checks that the call completed successfully, and if not, raises a custom error and returns a Boolean False. If the call completes successfully, it returns a Boolean True. Public Function SetKeyValue lngRootKey As w32Key, _ strSubKey As String, _ strValueName As String, _ strNewValue As String _ As Boolean lngReturn RegOpenKeyEx...
Filtering and Ordering Recordsets
Whenever you work on records in a database, it is rare that you want to carry out an action on the entire table. If you did, you would be best served by using an action query because queries operate much faster on large numbers of rows than do row processing methods recordsets . However, it is more likely that you'll want to do something with a subset of records, and that means you would need to filter your query to select only those records that you wanted to work on. With recordsets, you have...
Chart Object and the Charts Collection Example
This example creates a 3D chart from a given range, formats it, and saves a picture of it as a .jpg image 'Set the data source and plot by columns .SetSourceData .Location Where xlLocationAsNewSheet 'Size and shape matches the window it's in .ChartTitle.Caption Main Chart .ApplyDataLabels Type xlDataLabelsShowNone 'Save a picture of the chart as a jpg image .Export c amp .Name amp .jpg, jpg, False
Backup DatabaseProject
Finally, a consistent, fast, and easy way to save your work. We can't tell you how many times we've either closed and zipped a database, or copied and renamed a database object to test some changes. Pretty soon the database window is filled with nonfunctional objects. And heaven forbid that someone forgets if Name AutoCorrect was on, which can result in the code referring to the wrong object. Again, the Access team felt the developer's pain and provided an awesome solution. You don't even have...
Sharing Application Files
For Access applications designed to support multi-user scenarios, it is usually recommended that a front-end database file be installed on each user's local machine and that data be linked to a back-end ACCDB MDB file stored on the network location. Access does support opening ACCDB MDB files over the network, and applications are often shared in this manner from a central network location. Sharing ACCDB MDB files over the network usually works well when only a small number of users are working...
Using lfEnd If statements
You have a couple of ways to write VBA code that's capable of making a decision. The simplest and by far the most common is the lf End If block of code, which uses this syntax If condition Then statements Else where condition is an expression that results in True or False, and statements refers to any number of valid VBA statements. If the condition proves True, the statements between Then and Else execute, and all other statements are ignored. If the condition proves False, only the statements...
Opening and Closing Forms
VBA doesn't limit you to working with individual controls on forms. You can work with entire forms as objects, too. For example, VBA can open a closed form and display it on-screen. The OpenForm method of the DoCmd do command object gives you great flexibility in exactly how VBA opens a form. The syntax for using the OpenForm method of the DoCmd object is DoCmd.OpenForm formName, View , FilterName , WhereCondition , DataMode , WindowMode OpenArgs Only the first argument, formName, is required....
Starting New or Choosing an Existing Template
The first step in the Package Solution Wizard is to identify whether to create a new template or use an existing template. If an existing template is selected, it can be run without modification. If there is an existing template available, you can apply the template by selecting Load Wizard Settings From Saved Template File. Choosing an existing template sets all of the options that are defined by that particular template. Still, most people prefer to make a custom template for their particular...
Understanding Your VBA Building Blocks
Commenting your code Storing data in variables and constants Repeating chunks of code with loops Making decisions with lf End If statements any programmers begin their careers not so much by writing code WWW from scratch as by acquiring bits of code from books, Web sites, and other resources because that's easier than trying to figure it out from scratch. Plenty of sample code is made available through books and Web sites. Don't worry about stealing the code If folks didn't want you copying...
Spreadsheet File Formats Supported
Although Excel's default file format is an XLS workbook file, it can also open and save a wide variety of files generated by several other applications. An important consideration is whether a particular file type can survive a round trip. In other words, do you lose any information if you save a file in a particular format and then reopen it in the same application As you might expect, using Excel's native file format XLS files ensures that you'll lose absolutely nothing as long as you use the...
Reserved Words and Special Characters
There are numerous words that should not be used to name fields, objects, and variables. For the most part, these are called reserved words. Reserved words have a specific meaning to Microsoft Access, the Jet database engine, and the new Access database engine ACE . We also list reserved words that have specific meaning to SQL Server or ODBC drivers. Depending on how your application interfaces with other programs, it may be prudent to avoid using words that have specific programmatic meanings...
Step Database Solution Installation Options
The second page of the wizard enables you to specify the details about the installation options for your database solution, including the path to the database solution that will be packaged and the location where the solution will be installed on the target system. You also can specify whether the Redistributable Access Runtime package is to be included or required with this installation. The last preference on this page enables you to specify is the shortcut installation options for the...
More DoCmd methods for forms
The DoCmd object used in the example in the preceding section to open and close forms provides many methods for working with data on forms. Table 6-3 summarizes some of the more commonly used DoCmd methods for working with forms and data in forms. Send object Send object You don't need to study and memorize all these methods now because you can easily get detailed information as needed. Just type the beginning of the statement into your code, like this Just double-click the method name such as...
Enabling or Disabling Form Controls
When you work in most programs and dialog boxes, Windows disables dims controls that aren't relevant at the moment. You can add that same capability to your Access databases by using some VBA code. For example, you might create a form that allows a user to choose from among different payment options. When the user chooses Credit Card, you want all the fields for entering credit card information to be enabled. When the user selects any other payment method, you want to disable those same...
An Example Vibration of a String
A string 50 cm long and weighing 0.5 g is under a tension of 33 kg. Initially the mid-point of the string is displaced 0.5 cm from its equilibrium position and released. We want to calculate the displacement as a function of time at 5 cm intervals along the length of the string, using equation 12-34. From equation 1235 the At must be 8.8 x 10 5 seconds. The spreadsheet shown in Figure 12-14 illustrates the solution of the vibrating string problem. Column B contains time in increments of At from...
Linear Regression
Linear regression is not limited to the case of finding the least-squares slope and intercept of a straight line. Linear regression methods can be applied to any function that is linear in the coefficients . Many functions that produce curved x-y plots are linear in the coefficients, including power series, for example, and some functions containing exponentials, such as Mathematically, a function that is linear in the coefficients is one for which the partial derivatives of the function with...
Changing the Appearance of Objects
A form, and each object on a form, contains certain properties that describe the general appearance of the object. Different types of objects have different combinations of appearance properties. When you're working in the form's Design view, the Format tab of the property sheet shows the properties that the selected object or objects support. For example, Figure 6-5 shows some of the appearance properties available for the selected TextBox control on the form. Some appearance properties for a...
Properties of the Report Object
The properties of the Report object are listed in the following table. Used with the Screen object to determine the control that has the focus Returns the currently active application object Determines whether the report will be automatically centered within the Application window Determines whether the report will be automatically resized to display complete records Specifies the type of border and border elements for the report Specifies the caption in the title bar for the report Specifies...
RegNotifyChangeKeyValue
Description Provides the mechanism to be notified when a Registry key or any of its Declaration Declare Function RegNotifyChangeKeyValue _ Lib advapi32.dll _ ByVal hKey As Long, _ ByVal bWatchSubtree As Long, _ ByVal dwNotifyFilter As Long, _ ByVal hEvent As Long, _ ByVal fAsynchronus As Long As Long Parameters hKey Long Integer The handle of the key to watch, or one of the lpWatchSubTree Long Integer Boolean flag that indicates whether to watch the subkeys for change. Zero Do not watch...
Event Properties Where Does the Code Go
The power of a form is often derived from responding to the user. Typically, a response is based on intentional input. But VBA can also respond to ancillary or inadvertent actions. The most common place to use code is in the event properties of the form or control. This is frequently referred to as the code behind the form. It isn't enough to have the code right the code also has to be behind the right event or it may not be triggered. Time and again, the problem of code that just doesn't work...