Danny's VBA Scripts

Declaring Variables and Constants Visual Basic VBA

Fri, 17 Feb 2012 13:03:25 GMT

As was mentioned earlier, VBA supports a default data type, which means that, unlike many other programming languages, VBA allows the implicit declaration of variables. As soon as you use a variable or constant name within your code, VBA does all the necessary work of allocating memory space, etc., and the variable is considered to be declared. However, it's good programming practice and one that will save you endless hours of grief to explicitly declare any variables and constants you want to...

Dialog Box Controls The Details Excel 2007 VBA 3

Fri, 17 Feb 2012 03:18:32 GMT

In the following sections, I introduce you to each type of control you can use in custom dialog boxes and discuss some of the more useful properties. I don't discuss every property for every control because that would require a book that's about four times as thick and it would be a very boring book . The Help system for controls and properties is thorough. To find complete details for a particular property, select the property in the Properties window and press F1. Figure 17-4 shows the online...

The Fundamental Properties Excel VBA Macros

Thu, 16 Feb 2012 21:15:13 GMT

The PivotField object has a handful of basic properties that you will almost always want to set. 20.7.3.1 Function property This property applies only to data fields and returns or sets the aggregate function used to summarize the pivot field. It can be one of the following XlConsolidationFunction constants xlVarP -4165 xlVar -4164 xlSum -4157 xlStDevP -4156 xlStDev -4155 xlProduct -4149 xlMin -4139 xlMax -4136 xlCountNums -4113 xlCount -4112 xlAverage -4106 xlUnknown 1000 ' Excel 9 only This...

NotInList Adding a Value to a Combo Box at Runtime Access 2003 VBA

Wed, 15 Feb 2012 00:52:08 GMT

The NotInList event property is triggered when you enter a value into a combo box that is not contained within the list. This event property and procedure works in conjunction with the LimitToList property, which is a Boolean property of a combo box that instructs Access to restrict selections to values in the list. This example comes from the need to add a new value to the underlying table used to populate a combo box list. Because a combo box was designed to display values from a table, it...

Defining a Range Object Excel VBA Macros

Thu, 09 Feb 2012 19:28:19 GMT

As witness to the importance of the Range object, there are a total of 113 members properties and methods throughout the Excel object model that return a Range object. This number drops to 51 if we count only distinct member names, as shown in Table 19-2. For instance, BottomRightCell is a property of 21 different objects, as is TopLeftCell. Table 19-2. Excel Members That Return a Range Object Table 19-2. Excel Members That Return a Range Object Let us take a look at some of the more prominent...

Language Version of Windows Excel 2007 VBA 2

Wed, 08 Feb 2012 19:57:02 GMT

By this, I mean the basic language version of Windows itself. This choice defines the text for the standard buttons in the VBA MsgBox function when using the vbMsgBoxStyles constants . Hence, whereas the text of the buttons on Excel's built-in messages responds to the Office UI language, the text of the buttons on your own messages responds to the Windows language. Note that the only way to discover the Windows language is with a Windows API call. Some things in Office 2007 are 100 U.S. English...

Using a Select Case block Access 2007 VBA 2

Wed, 08 Feb 2012 16:18:37 GMT

Each Case statement can have any number of statements beneath it. When the code executes, only those statements after the Case statement that matches the value at the top of the block execute. Figure 4-9 shows the general concept. Case possibleVahie2 statements statements Case possibleVahie3 statements statements These statements executed only if no

How the Solver Works Excel 2007 VBA Methods

Sun, 05 Feb 2012 18:51:22 GMT

The Solver uses the Generalized Reduced Gradient GRG2 nonlinear optimization code developed by Leon Lasdon, University of Texas at Austin, and Allan Waren, Cleveland State University . For each of the changing cells, the Solver evaluates the partial derivative of the objective function F the target cell with respect to the changing cell a , by means of the finite-difference method. The procedure works something like this the Solver reads the value of each changing cell a, in turn, modifies the...

TotalsRelated Members Excel VBA Macros

Sun, 05 Feb 2012 08:30:20 GMT

The PivotTable object has two properties that affect the display of totals. When this property is True, the PivotTable shows grand column totals. When this property is True, the PivotTable shows grand row totals. To illustrate, referring to the pivot table in Figure 20-6, the code ColumnGrand False RowGrand False produces the pivot table in Figure 20-9, with no grand totals. We can also suppress the display of individual pivot-field totals, such as the totals for Store City in Figure 20-9. This...

Creating Forms the Way Access 2007 VBA

Fri, 03 Feb 2012 20:50:47 GMT

When you are ready to create a new form, you'll probably have a good idea about its general look and function based on the record source and purpose. Access 2007 gives developers a huge jump-start on form design by providing a good selection of form options on the Ribbon see Figure 10-1 . You can select an item from the Navigation pane, which will become the Record Source of the new form, and then click on one of the Ribbon's form buttons to initiate the process. Thanks to a form wizard, the...

Sending Information from Access to Excel Access 2007 VBA

Fri, 03 Feb 2012 15:26:30 GMT

Access 2007 provides the capability to create forms and reports, which include graphs and tables. However, you may want to leverage some of the powerful Excel features, such as the new Charting and Conditional Formatting features new to Office 2007. Also, users may find it useful to be able to export their data in an Excel spreadsheet. The code samples for working with Excel can be found in the code behind the Export Report Manager form in the sample database. As with Outlook, using Excel...

Referencing UserForm Controls Excel 2007 VBA

Thu, 02 Feb 2012 23:41:07 GMT

When working with controls on a UserForm, the VBA code is usually contained in the code window for the UserForm. You can also refer to UserForm controls from a general VBA module. To do so, you need to qualify the reference to the control by specifying the UserForm name. For example, consider the following procedure, which is located in a VBA module. It simply displays the UserForm named userForml. Assume that UserForml contains a text box named TextBoxi , and you want to provide a default...

Altering the SQL Inside Queries Access 2003 VBA

Wed, 01 Feb 2012 18:55:13 GMT

Sometimes it can be advantageous to alter the SQL inside a saved query. This is especially common when you are using pass-through queries to another database like SQL Server, but it can also come in handy when you need to nest Access queries several layers deep. Since the queries a few layers down can't be modified directly in a report or form's recordsource you may need to change them directly. Use the right syntax Remember, if you use this technique for a pass-through query, you must use the...

Create the Code Behind the UserForm 1 Excel VBA Macros

Wed, 01 Feb 2012 16:30:37 GMT

Now it is time to create the code behind these controls. 18.4.2.1 The Declarations section The Declarations section of the dlgPrintSheets UserForm should contain declarations of the module-level variables, as shown in Example 18-1. Example 18-1. Module-Level Variable Declarations The Cancel button code is shown in Example 18-2. Example 18-2. The cmdCancel_Click Event Handler The Print button calls the main print procedure and then unloads the form its source code is shown in Example 18-3....

On Close Save a Setting to the Registry Access 2003 VBA

Tue, 31 Jan 2012 14:40:48 GMT

In case you are unfamiliar with Windows, there is a very important database called the registry. In Windows 3.1, the registration database stored OLE registration information and file associations, while program settings were stored in initialization .ini files. With the introduction of Windows 95 and later versions, the registry became the central repository for all this information. As you have read, the registry database is vital to the operation of all applications installed on your...

Processing a series of files Excel 2007 VBA

Tue, 31 Jan 2012 06:17:32 GMT

One common use for macros, of course, is to repeat an operation a number of times. The example in this section demonstrates how to execute a macro on several different files stored on disk. This example -which may help you set up your own routine for this type of task - prompts the user for a file specification and then processes all matching files. In this case, processing consists of importing the file and entering a series of summary formulas that describe the data in the file. In previous...

Topics in Learning Excel Programming Excel VBA Macros

Fri, 27 Jan 2012 21:15:10 GMT

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 Excel VBA Programming 3

Fri, 27 Jan 2012 11:59:31 GMT

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 Access 2007 VBA 2

Wed, 25 Jan 2012 15:46:56 GMT

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 Excel 2007 VBA 4

Tue, 24 Jan 2012 18:30:45 GMT

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 Excel 2007 VBA 2

Tue, 24 Jan 2012 16:24:04 GMT

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 Excel VBA Programming

Fri, 20 Jan 2012 14:18:42 GMT

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 Access 2007 VBA

Thu, 19 Jan 2012 02:44:17 GMT

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 Excel 2007 VBA 2

Wed, 18 Jan 2012 04:31:45 GMT

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 Excel 2007 VBA Methods

Tue, 17 Jan 2012 15:08:15 GMT

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 Excel VBA Macros

Sat, 14 Jan 2012 04:07:11 GMT

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 Excel VBA Macros

Fri, 13 Jan 2012 12:46:59 GMT

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 Excel 2007 VBA Methods

Thu, 12 Jan 2012 17:24:03 GMT

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 Access 2007 VBA

Wed, 11 Jan 2012 19:27:02 GMT

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 Access 2007 VBA

Tue, 10 Jan 2012 12:59:51 GMT

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 Visual Basic VBA

Tue, 10 Jan 2012 04:33:28 GMT

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 Access 2007 VBA 2

Sat, 07 Jan 2012 19:08:48 GMT

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 Excel 2002 VBA XML ASP

Fri, 06 Jan 2012 19:49:14 GMT

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...

ImportExportSpecification Object Properties Access 2007 VBA

Thu, 05 Jan 2012 16:24:38 GMT

The following table describes the properties of the ImportExportSpecification object. Returns the current Application object for the specification. Specifies a friendly description for the specification. Specifies the name of the specification. the CurrentProject or CodeProject object for the specification. Specifies the XML representation of the specification.

Adding Editing and Deleting Rows Access 2007 VBA

Wed, 04 Jan 2012 21:45:52 GMT

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 Excel 2007 VBA Methods

Wed, 04 Jan 2012 18:02:47 GMT

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 2007 VBA 2

Tue, 03 Jan 2012 10:27:30 GMT

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 2007 VBA

Mon, 02 Jan 2012 07:05:08 GMT

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 Access 2007 VBA

Mon, 02 Jan 2012 06:44:06 GMT

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 Visual Basic VBA

Thu, 29 Dec 2011 19:21:17 GMT

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 Access 2007 VBA

Wed, 28 Dec 2011 19:13:04 GMT

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 Access 2007 VBA

Wed, 28 Dec 2011 18:37:23 GMT

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 Access 2007 VBA

Tue, 27 Dec 2011 19:29:15 GMT

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 Access 2007 VBA

Tue, 27 Dec 2011 11:52:33 GMT

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 Access 2007 VBA

Fri, 23 Dec 2011 10:02:41 GMT

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 Excel 2007 VBA Methods

Thu, 22 Dec 2011 19:25:39 GMT

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 Excel 2007 VBA 2

Wed, 21 Dec 2011 17:20:45 GMT

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 Access 2007 VBA

Tue, 20 Dec 2011 21:00:48 GMT

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 Access 2007 VBA

Tue, 20 Dec 2011 04:49:40 GMT

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 Excel 2007 VBA 2

Mon, 19 Dec 2011 23:57:50 GMT

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 Access 2003 VBA

Mon, 19 Dec 2011 23:38:04 GMT

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 Access 2007 VBA

Mon, 19 Dec 2011 17:38:29 GMT

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 Access 2007 VBA 2

Mon, 19 Dec 2011 17:15:01 GMT

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 Access 2007 VBA 2

Fri, 16 Dec 2011 21:04:57 GMT

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 Access 2007 VBA

Thu, 15 Dec 2011 20:36:48 GMT

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 Access 2007 VBA 2

Thu, 15 Dec 2011 13:13:27 GMT

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 Excel 2003 VBA

Tue, 13 Dec 2011 19:02:46 GMT

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 Access 2007 VBA

Mon, 12 Dec 2011 20:11:29 GMT

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 Access 2007 VBA

Fri, 09 Dec 2011 16:31:24 GMT

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 Access 2007 VBA 2

Thu, 08 Dec 2011 20:09:32 GMT

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...

Using VBA to position the cursor Access 2007 VBA 2

Wed, 07 Dec 2011 22:56:22 GMT

With VBA, you can move the cursor to any control on a form. In programmer lingo, moving the cursor to a control is called giving that control the focus. When you type, your text appears in whatever control on a form now has the focus. VBA itself doesn't use square brackets. In fact, about the only time you see square brackets in VBA is when you're looking at a syntax chart, where square brackets are used to identify optional as opposed to required arguments. Access, however, uses square...

Enabling or Disabling Form Controls Access 2007 VBA 2

Wed, 07 Dec 2011 19:35:38 GMT

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 Excel 2007 VBA Methods

Wed, 07 Dec 2011 15:45:51 GMT

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 Excel 2007 VBA Methods

Mon, 05 Dec 2011 14:24:30 GMT

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 Access 2007 VBA 2

Wed, 30 Nov 2011 14:03:53 GMT

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 Access 2003 VBA

Sat, 26 Nov 2011 14:08:50 GMT

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 Access 2007 VBA

Wed, 23 Nov 2011 05:07:03 GMT

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 Access 2007 VBA

Wed, 23 Nov 2011 02:20:35 GMT

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...

Late Binding Access 2007 VBA

Mon, 21 Nov 2011 19:23:22 GMT

Another timing issue that can have significant impact on performance is whether the form is using early or late binding. Basically, this refers to when the record or recordset is created for a form or other object. Late binding typically involves filtering, which reduces the size of the recordset and also allows the developer greater control of the record source. When you add fields to your form, the text boxes that are created become bound text boxes. That is, the table or query fields are...

Updating Records Access 2003 VBA

Mon, 21 Nov 2011 15:56:54 GMT

The code for updating records in a recordset is almost the same as adding them, including the need to specify the dbOpenDynaset Type. You may also need to find the correct record to update using FindFirst. If you find it successfully, you can update it. .FindFirst CustomerKey amp Me CustomerKey If Not .NoMatch Then 'we found the record .Edit CustomerName ABC Construction CustomerStatus 1 .Update End If End With The With statement is purely a programming convenience. Instead of typing the name...

Understanding Object Models Access 2007 VBA 2

Sun, 20 Nov 2011 10:58:34 GMT

An object model is a road map, or the view from 30,000 feet, of all the objects and properties that VBA can manipulate. Because there are so many thousands of objects, you need a sort of road map to find them, just like you need a map to navigate unfamiliar territory. When you view an object model or portion of an object model , all you see are color-coded boxes arranged in a vertical hierarchy. For example, Figure 5-1 shows a graphical representation of the Access object model. Notice the...

Using VBA with Multiple Databases Access 2007 VBA 2

Thu, 17 Nov 2011 20:10:39 GMT

Importing data from external tables and queries Linking to external Access tables Creating recordsets from external data Importing, exporting, and linking to anything sually an Access database an ACCDB file contains all the tables, queries, forms, reports, and other objects that make up a single database. However, you find situations where it's to your advantage to split things into two or more database files. For example, you might want to put some tables for a database into a single ACCDB...

Derivatives of a Worksheet Formula Excel 2007 VBA Methods

Thu, 17 Nov 2011 03:57:01 GMT

Instead of calculating the first or second derivative of a curve represented by data points, we may wish to find the derivative of a function a worksheet formula . In the following, two different methods are illustrated to calculate the first or second derivative of a worksheet formula by using a user-defined function. The calculation of the first derivative of the function y 3x3 5x2 - 5x 11 is used as the example for each method Derivatives of a Worksheet Formula Calculated by Using a VBA...

Considering Types of Program Errors Access 2007 VBA 2

Wed, 16 Nov 2011 22:49:07 GMT

Errors in code can happen at any time in the create-compile-execute sequence. Programmers generally categorize the types of errors that they have to deal with in three ways 1 Compile errors Any problem that prevents the VBA Editor from translating a line of source code to something executable generates a compile error, like the example shown in Figure 12-1. Such errors are usually syntax errors, which means that you didn't obey the rules of syntax for that statement when typing the code. 1...

An Example Heat Conduction in a Brass Rod Excel 2007 VBA Methods

Wed, 16 Nov 2011 16:13:14 GMT

Consider an insulated 10-cm brass rod, initially at a temperature of 0 C. One end of the rod is heated to 100 C. Equation 12-20 describes the heat flow in the rod as a function of time. For simplicity, we assume that there is no heat loss through the sides of the rod. For brass, the coefficient of thermal conductivity k is 0.26 cal s cm-1 deg ', the heat capacity c is 0.094 cal g_1 deg-1 and the density p is 8.4 g cm-3. From these values, the coefficient k in equation 12-22 is 3.04 s cm2....

Calling the Record Finder Code Access 2007 VBA

Wed, 16 Nov 2011 00:06:43 GMT

The On Click event of the First button shown in Figure M-4 includes this code Private Sub cmdFirst_Click On Error GoTo Error_Handler DisplayUnexpectedError Err.Number, Err.Description The code really has only one operative line The code behind cmdNext_Click is almost identical. Instead of using first as the parameter for FindRecordLike, it sends in next Now here's the code for the subroutine FindRecordLike. It also resides in the index form that contains the record finder controls shown in...

The Function to Get a Key Value Access 2007 VBA

Tue, 15 Nov 2011 01:23:06 GMT

The GetKeyValue procedure wraps both RegOpenKeyEx and RegQueryValueEx functions, which open the subkey and retrieve its value, respectively. Again, after each function call, it checks if the call completed successfully, and if not, raises a custom error and returns a Null value. If the call does complete successfully, it returns the current value. Public Function GetKeyValue lngRootKey As w32Key, _ strSubKey As String, _ strValueName As String _ As Variant lngReturn RegOpenKeyEx lngRootKey,...

Methods of the Parameters Collection Access 2007 VBA

Mon, 14 Nov 2011 20:26:19 GMT

There are exactly three methods for the Parameters collection. They are described in the following table. Used to add a Parameter object to the collection. Updates all of the Parameter objects in the collection with the latest information from the provider. Deletes a Parameter object from the collection. The Index value is either the name or ordinal position of the Parameter in the collection.

Making Decisions in VBA Code Access 2007 VBA 2

Mon, 14 Nov 2011 18:27:22 GMT

Decision-making is a big part of programming because most programs need to be smart enough to figure out what to do, depending on the circumstances. Often, you want your code to do one thing if such-and-such is true but do something else if such-and-such is false. You use conditional expressions to determine whether something is true or false. A conditional expression is one that generally follows this syntax where Value is some chunk of information, and the ComparisonOperator is one of those...

A Function to Print Check Amounts Access 2007 VBA 2

Mon, 14 Nov 2011 10:45:10 GMT

Suppose you want to use Access to print checks from a table of payable amounts. You have your printer and your preprinted checks, and maybe you already created a report format to print the checks. What about the part of the check where you're supposed to write out the dollar amount, such as One Hundred Thirty-Five and 49 100 How can you get that part of the check printed No built-in function is capable of doing that for you. And heaven knows you don't want to type all those words The solution...

Understanding Class Modules Excel 2003 VBA

Thu, 10 Nov 2011 23:01:53 GMT

This chapter presents an introduction to class modules and includes several examples that might help you better understand this feature and give you ideas for using class modules in your own projects. An introduction to class modules A list of some typical uses for class modules Examples that demonstrate some key concepts related to class modules For many VBA programmers, the concept of a class module is a mystery. This feature has been available in Visual Basic for several years and was added...

How the MyMsgBox function works Excel 2007 VBA

Thu, 10 Nov 2011 22:24:44 GMT

Notice the use of four Public variables. The first three Promptl, Buttonsl, and Titlel represent the arguments that are passed to the function. The other variable UserClick represents the values returned by the function. The userForm_Initialize procedure needs a way to get this information and send it back to the function, and using Public variables is the only way to accomplish that. The UserForm shown in Figure 15-12 contains four Image controls one for each of the four possible icons , three...

Displaying Excels Builtin Dialog Boxes Excel VBA Programming

Thu, 10 Nov 2011 17:10:24 GMT

You can write VBA code that performs the equivalent of selecting an Excel menu command and making choices in the resulting dialog box although Excel doesn't actually display the dialog box. For example, the following statement has the same effect as choosing the EditOGo To command, specifying a range named InputRange, and clicking OK Application.Goto Reference InputRange When you execute this statement, the Go To dialog box does not appear. This is almost always what you want to happen you...

Basic Error Handling with an Extra Resume Access 2007 VBA

Tue, 08 Nov 2011 19:42:08 GMT

One of the problems with basic error handling is that when an error does occur, you have no easy way of knowing the exact line that caused the error. After all, your procedure may have dozens or hundreds of lines of code. When you see the error message, the execution of your code has already jumped to your error handler routine and displayed the message box you may not be able to tell which line caused the problem. Many programmers rerun the code, using debug mode, to step through the code to...

Setting Permissions on Database Objects Access 2007 VBA

Sat, 05 Nov 2011 18:40:11 GMT

After the workgroup information has been established and all of the groups have been created, the database object for the particular MDB file needs to have permissions applied. The User and Group Permissions dialog box can be used to add permissions to any database currently opened in Access, based on the workgroup information loaded for that session. Of course, the person modifying the permissions is required to have the proper permissions to modify those objects. If the database were created...

Dealing with Logical Errors Access 2007 VBA 2

Fri, 04 Nov 2011 14:16:24 GMT

Even if your code compiles and runs without generating an error message, the code isn't necessarily perfect. It can also contain logical errors. Unlike a compile error, which is an error in syntax or a typographical error, a logical error is an error in your thinking logic . The computer always does exactly what the code tells it to do, even if you tell it to do the wrong thing. Suppose that you intend to write a line of code to open some form, but you accidentally write the code to close the...

Nonlinear Regression Using the Solver Excel 2007 VBA Methods

Thu, 03 Nov 2011 13:02:35 GMT

If you have read the preceding chapter on linear regression and are familiar with the use of LIN EST, you should have no trouble recognizing a function that is linear in the coefficients. Some examples of functions that are linear in the coefficients arsy a bx cx2 dx ory aex. However, if the function is one such as it is not linear in the coefficients. It should be obvious that it's not possible to apply LI NEST to this equation given a column of x values, you can't create a column of ea bx...

Handling Errors Another Way Excel 2007 VBA 3

Tue, 27 Sep 2011 13:23:40 GMT

How can you identify and handle every possible error The answer is that often you can't. Fortunately, VBA provides another way to deal with errors. Revisiting the EnterSquareRoot procedure Examine the following code. I modified the routine from the previous section by adding an On Error statement to trap all errors and then checking to see whether the InputBox was cancelled. Sub EnterSquareRoot5 Dim Num As Variant Dim Msg As String ' Set up error handling On Error GoTo BadEntry Num InputBox...

Info Wwk Outlook 2002 VBA

Tue, 27 Sep 2011 09:28:59 GMT

Figure 1.10 - The VBScript Samples folder. This document was created by an unregistered ChmMagic, please go to http www.bisenter.com to register it. T For additional information about the types of applications you can create, visit the following Web sites http www. slipstick. com addins mssampleapps. htm http msdn. microsoft, com office Microsoft Outlook offers easy-to-use design tools for creating custom forms and folders. For example, in Design mode, Outlook provides an AutoLayout feature...

Generic Classes Visual Studio 2005 VBA

Tue, 27 Sep 2011 06:55:22 GMT

Generic classes are a new feature in the .NET Framework and are supported in Visual Basic 2005. A generic class is a single class that provides functionality for different data types, without the need to write a separate class definition for each data type. You can also define generic methods, structures, and interfaces. A generic class uses type parameters as placeholders for the data types. The code example in Listing 3.10 shows the declaration of a generic class using t to represent the type...

Sending a Word Letter to a Single Access Contact Office 2007 VBA 2

Tue, 27 Sep 2011 06:48:30 GMT

You may have a Contacts or Customers form in an Access database, and it would be convenient to have a quick way to create a letter to the current contact, using a command button on the form. The sample Word Export database has a form for browsing contacts, frmContacts, shown in Figure 6.15. If you click the Word button in this form's header, a letter to the selected contact is created, filling Word document properties with data from that record. Opening the old Paragraph dialog box from the new...

Duplicating rows a variable number of times Excel 2007 VBA

Tue, 27 Sep 2011 06:22:52 GMT

The example in this section demonstrates how to use VBA to create duplicates of a row. Figure 11-9 shows a worksheet for an office raffle. Column A contains the name, and column B contains the number of tickets purchased by each person. Column C contains a random number generated by the RAND function . The winner will be determined by sorting the data based on column 3 the highest random number wins . Figure 11-9 The goal is to duplicate rows based on the value in column B. Figure 11-9 The goal...

The Top Ten Questions about VBA Excel VBA Programming

Tue, 27 Sep 2011 05:13:21 GMT

I created a custom VBA function. When I try to use it in a formula, the formula displays NAME . What's wrong You probably have your function code in the wrong location. VBA code for worksheet functions must be in a standard VBA module not in a module for a sheet or in ThisWorkbook. Can I use the VBA macro recorder to record all of my macros No. Normally you use it only to record simple macros or as a starting point for a more complex macro. It cannot record macros that use variables, looping,...

Copy and Cut Methods Excel VBA Macros

Mon, 26 Sep 2011 23:27:55 GMT

As applied to the Range object, the Copy method has the syntax where Destination is a Range object that specifies the new range to which the specified range will be copied. If this argument is omitted, Excel will copy the range to the Clipboard. For instance, the code Range A1 C3 .Copy Range D5 copies the range A1 C3 to a range of like size whose upper-left corner is cell D5. Note that the same rules apply here as when copying using Excel's user interface. In particular, if the destination is...

Removing a VBA module Excel 2003 VBA

Mon, 26 Sep 2011 22:34:06 GMT

If you need to remove a VBA module or a class module from a project, select the module's name in the Project Explorer window and choose File Remove xxx where xxx is the name of the module . Or you can right-click the module's name and choose Remove xxx from the shortcut menu. You'll be asked whether you want to export the module before removing it. See the next section for details. Remember You cannot remove code modules associated with the workbook the ThisWorkbook code module or with a sheet...

OnChange Late Bind a Subform on a Tab Control Access 2003 VBA

Mon, 26 Sep 2011 21:45:08 GMT

The degradation of performance is a never-ending battle for the Access developer as users want more and more information, on the same form no less. But, no matter how accommodating you are to satiate their every need and desire, the smallest of performance hits causes your phone to ring and e-mail inbox to overflow with complaints. Even when you tell them ahead of time The reduction of Access performance sometimes sneaks up on you slowly, other times it's instantaneous. In case you're new, the...

AfterLayout Office 2003 VBA

Mon, 26 Sep 2011 19:52:11 GMT

The AfterLayout event occurs after the chart in the PivotChart view has been laid out but before the chart elements have been drawn on screen. Use this event to draw custom lines, text, and various drawings on the chart. The drawObject parameter in the AfterLayout event represents a chChartDraw object. The DrawEllipse, DrawLine, DrawPolyLine, and DrawPolygon methods of the chChartDraw object can be used to add drawing objects to a chart. The DrawText method can be used to add text to a chart....

Calendar Property Visual Basic VBA

Mon, 26 Sep 2011 19:20:23 GMT

Calendar calendarconstant calendarconstant can be either of the following constants Application uses the Gregorian calendar default Returns or sets a value specifying the type of calendar to use with your project. The Gregorian calendar year of 1998 which is based on the birth of Christ is roughly equivalent to the Hijri year of 1418 which is based on the birth of Mohammed . Note that the new year of each calendar is different, and the Hijri calendar, being based more accurately on the cycles...

Deleting a Custom Toolbar Excel 2002 VBA XML ASP

Mon, 26 Sep 2011 18:09:05 GMT

If you create a toolbar and decide that you don't want to keep it, you can get rid of it without closing the Excel application. Simply use the Delete method. For example, to delete the Budget Plans toolbar you can enter the following statement in the Immediate window CommandBars Budget Plans .Delete Note You cannot delete built-in toolbars. Using the CommandBar Properties The CommandBar object has a number of properties. You'll work with some of them in the Immediate window. 1. Use the...

AcadToolbarItem Object Properties AutoCAD 2006 VBA

Mon, 26 Sep 2011 16:49:02 GMT

As well as the common Application property, the AcadToolbarItem object supports the following properties. Gets the toolbar associated with the item, if it's a flyout toolbar item. This property's value is read-only. Gets or sets the help string for the menu item, which appears in the AutoCAD status line when a user highlights a menu item. Gets the index for the menu item. The first position in the index is 0. This property's value is read-only. Gets or sets the macro associated with the toolbar...

AcadToolbarItem Object AutoCAD 2006 VBA

Mon, 26 Sep 2011 16:48:40 GMT

The AcadToolbarItem object represents a single button or separator on an AutoCAD toolbar. A toolbar button can contain a macro to be executed when it is selected by the user, or it can contain a nested toolbar called a flyout. Separators can't contain macros or flyouts. The Macro property is used to add or change the button's associated macro. The AcadToolbarItem object supports the following methods. Name Description AttachToolbarToFlyout Attaches the toolbar to a toolbar button defined as a...