Danny's VBA Scripts

Programming Combo and List Boxes Access 2007 VBA 2

Fri, 18 May 2012 19:55:25 GMT

When working with combo and list boxes through VBA, you often want to start with just a simple unbound control one that's not attached to any field in the form's underlying table or query and then let VBA control the properties. To add an unbound ComboBox or a ListBox control to a form, first make sure that the form is open in Design view. Then click the Form Design Tools Design tab to see the Controls group see Figure 10-2 . To prevent the Control Wizards from helping you create the control,...

Creating A New Toolbar Excel 2003 VBA

Fri, 18 May 2012 13:16:56 GMT

To create a new toolbar, click the New button of the Customize dialog box and then enter a name in the New Toolbar dialog box that appears. Excel creates and displays an empty toolbar. You can then add buttons or menu commands to the new toolbar. Figure 22-2 shows a custom toolbar that I created manually. This toolbar, called Custom Formatting, contains the formatting tools that I use most frequently. Notice that this toolbar includes drop-down menus as well as standard toolbar buttons. Bor.lei...

Info Gdu Excel 2003 VBA Macros

Thu, 17 May 2012 13:11:35 GMT

Creating User-Defined Functions 75 Case Study Custom Functions Example and Explanation 76 Useful Custom Excel Functions 77 Next Steps 102 j UDFs can be entered only into standard modules.Sheet and ThisWorkbook modules are a special type of module and if you enter the function there, Excel doesn't recognize that you are creating a UDF.

Naming columns and rows Excel 2003 VBA

Tue, 15 May 2012 19:44:25 GMT

With Excel, you can also name complete rows and columns. In the preceding example, the name Qtrl is assigned to the range C3 C6. Alternatively, Qtr1 could be assigned to all of column C, Qtr2 to column D, and so on. You also can do the same horizontally so that North refers to row 3, South to row 4, and so on. The intersection operator works exactly as before, but now you can add more regions or quarters without having to change the existing names. When naming columns and rows, make sure that...

Type Conversion Visual Basic VBA

Mon, 14 May 2012 21:20:14 GMT

VBA provides two sets of built-in conversion functions. The first set, which includes Int and Str, is from the early versions of VB and is simply left in for backwards compatibility. The functions of the second set all start with the letter C and are the more recent conversion functions. Microsoft recommends that you use this latter set of functions, since they are locale-aware that is, they take account of international date, time, and number settings on the host system. The syntax for each of...

Ensuring that an addin is installed Excel 2003 VBA

Mon, 14 May 2012 06:37:15 GMT

In some cases, you might need to ensure that your add-in is installed properly that is, opened via the Tools Add-Ins command and not the File Open command. This section describes a technique that determines just that. If the add-in isn't properly installed, the code displays a message see Figure 21-6 , and the file is closed. In other words, the add-in remains open only if it is properly installed. Figure 21-6 When attempting to open the add-in incorrectly, the user sees this message. Figure...

The Forms Initialize event Excel VBA Macros

Sun, 13 May 2012 18:11:35 GMT

The UserForm's Initialize event, which is shown in Example 21-8, is the place to fill the list box with a list of embedded charts. Our application uses a module-level array to hold the chart names, a module-level array to hold the ChartObject object names, and a module-level integer variable to hold the chart count. We fill these variables in the Initialize event and then use the arrays to fill the list. The variables are used again in the main print procedure, which is why we have declared...

An Addin Example Excel VBA Programming

Fri, 11 May 2012 21:55:27 GMT

In this section I discuss the basic steps involved in creating a useful add-in. The example uses the ChangeCase text conversion utility that I describe in Chapter 16. The XLS version of this example is available at this book's Web site. You can create an add-in from this workbook. The workbook consists of one blank worksheet, a VBA module, and a UserForm. In addition, I added code to the ThisWorkbook object that creates a new menu item on the Tools menu. The following code, located in the Code...

Chapter Project The Math Game Excel VBA Programming 3

Thu, 10 May 2012 20:54:12 GMT

The Math Game program from Chapter 4 was fairly simple with randomly generated problems that were stored in memory, and then written to a worksheet at the end of the game potentially the only data saved by the program but only if the user so desired . The new version of the Math Game automatically stores the program's data tests, student names, and test results in XML files. I added worksheet interfaces for writing tests, maintaining student lists, and viewing test results. The program...

Writing an EventHandler Procedure Excel VBA Programming

Thu, 10 May 2012 17:22:02 GMT

The VBE helps you out when you're ready to write an event-handler procedure it displays a list of all events that Excel can recognize. Figure 11-2 shows a Code window for the ThisWorkbook object the code window is maximized to fill the entire code window area . To display this empty Code window, double-click the ThisWorkbook object in the Project window. This Code window has two drop-down lists at the top. An empty Code window for the This Workbook object. Figure 11-2 shows a Code window for...

The Complete Code Excel VBA Macros

Thu, 10 May 2012 15:37:55 GMT

For reference, let us put together the code required to create the pivot table in Figure 20-6 it is shown in Example 20-3. Example 20-3. The CreatePivot Procedure ' Assumes that the source table is in sheet called Source ActiveSheet.PivotTableWizard SourceType xlDatabase, SourceData 'Company Both' R1C1 R145C7, ' Assign field orientations and data fields With xlPageField .PivotFields Year .Position 1 .PivotFields Store City .Orientation xlRowField .PivotFields Store City .Position 1 .PivotFields...

Using the MsgBox Function Excel 2002 VBA XML ASP

Thu, 10 May 2012 13:44:18 GMT

The MsgBox function that you have used so far was limited to displaying a message to the user in a simple, one-button dialog box. You closed the message box by clicking the OK button or pressing the Enter key. Create a simple message box by following the MsgBox function name with the text enclosed in quotation marks. In other words, to display the message The procedure is complete, you should prepare the following statement MsgBox The procedure is complete You can quickly try out the above...

Passing Parameters Visual Basic VBA

Thu, 10 May 2012 07:15:09 GMT

There are numerous occasions when you need to call a custom function or subroutine from another function or subroutine, and a variable you are using in the calling procedure is needed in the called procedure. You therefore pass the variable as a parameter to the called procedure. Whether the called procedure is in the same module, the same project, or is a method within a class on a remote server, passing variables from one procedure to the other is always the same. The called procedure, and...

Template files Excel 2003 VBA

Tue, 08 May 2012 21:25:14 GMT

You can save any workbook as a template file XLT extension . Doing so is useful if you tend to create similar files on a regular basis. For example, you might need to generate a monthly sales report. You can save some time by creating a template that holds the necessary formulas and charts for your report. When you start new files based on the template, you need only plug in the values. To create a new workbook that's based on an existing template, choose the File New command and then select...

Tip A Check Box or an Option Button Excel 2002 VBA XML ASP

Tue, 08 May 2012 15:53:02 GMT

Use option buttons where only one option can be selected at a given time. Use check boxes to have the user select any number of options that apply. Click the Label control in the Toolbox. Click the empty space below the frame labeled Computer Type. The Label1 control should appear. Change the Caption property of Label1 to Where Used. Click the ComboBox control in the Toolbox. Click the empty space below the Where Used label and drag the mouse to draw a rectangle. Release the mouse button. The...

Put Statement Visual Basic VBA

Tue, 08 May 2012 14:07:24 GMT

Put filenumber, recnumber , varname filenumber Record or byte number to begin the write operation. The name of the variable containing the data to be written to the file. Description Writes data from a program variable to a disk file. Rules at a Glance If filenumber is opened in random access mode, recnumber refers to the record number if the file is opened in binary access mode, recnumber refers to a byte number. Both bytes and records in a file are numbered from 1 upward. If recnumber is...

Create A Custom Toolbar Excel VBA Programming 2

Tue, 08 May 2012 01:45:25 GMT

You can design a VBA procedure to create new toolbars within Excel where you can place links to the custom macros you create. You create a new toolbar by adding a new CommandBar object to the CommandBars object collection. Excel comes with approximately 30 different built-in toolbars, to which you can add controls. By creating new toolbars to house your custom toolbar options, you do not affect the layout of the standard toolbars. of True for the MenuBar parameter. If you only want the toolbar...

Working with Ranges Excel VBA Programming

Mon, 07 May 2012 15:46:50 GMT

Most of your VBA programming probably involves worksheet ranges. For a refresher course on Range objects, refer to Chapter 8. When you work with Range objects, keep the following points in mind 11 Your VBA doesn't need to select a range to work with it. 1 If your code does select a range, its worksheet must be active. i The macro recorder doesn't always generate the most efficient code. Often, you can create your macro by using the recorder and then edit the code to make it more efficient. i...

Common Uses for Macros Excel 2002 VBA XML ASP

Sun, 06 May 2012 23:33:55 GMT

Microsoft Excel 2002 comes with dozens of built-in, timesaving features that allow you to work faster and smarter. Before you decide to automate a worksheet task with a macro, make sure there is not already a built-in feature that you can use to perform that task. Consider, however, creating a macro when you find yourself performing the same series of actions over and over again or when Excel does not provide a built-in tool to do the job. Macros enable you to automate just about any part of...

FolderCreateTextFile Method VB Visual Basic VBA

Sat, 05 May 2012 21:07:53 GMT

oFolderObj.CreateTextFile FileName , Overwrite , _ Unicode Any object variable returning a Folder object. Any valid filename and optional path. Flag to indicate if an existing file of the same name should be overwritten. Flag to indicate if file is to be written in Unicode or ASCII. Return Value A TextStream object Description Creates a new file at the specified location and returns a TextStream object for that file. Rules at a Glance Wildcard characters aren't allowed in FileName. The default...

Determining User Needs Excel 2007 VBA

Sat, 05 May 2012 00:06:20 GMT

When you undertake a new Excel project, one of your first steps is to identify exactly what the end users require. Failure to thoroughly assess the end users' needs early on often results in additional work later when you have to adjust the application so that it does what it was supposed to do in the first place. In some cases, you'll be intimately familiar with the end users - you might even be an end user yourself. In other cases for example, a consultant developing a project for a new...

Set Display Options For The Code Window Excel VBA Programming 2

Thu, 03 May 2012 20:32:59 GMT

You can modify the display settings for the text that displays on the Code window in the Visual Basic Editor. You can change the text color, font type, and font size for the text that displays in the Code window. You can not only specify the text color but also the background color. Just like any basic editor, the Code window has predefined formatting for the type of text that displays in the window. For example, there is a definition for comments and a different definition for normal text. You...

Adding menu items to shortcut menus Excel 2003 VBA

Wed, 02 May 2012 23:23:18 GMT

Adding a menu item to a shortcut menu works just like adding a menu item to a regular menu. The following example demonstrates how to add a menu item to the Cell shortcut menu that appears when you right-click a cell. This menu item is added to the end of the shortcut menu, with a separator bar above it. Set NewItem .Caption Toggle Word Wrap .OnAction ToggleWordWrap .BeginGroup True End With End Sub Selecting the new menu item executes a procedure named ToggleWordWrap not shown here . Figure...

Creating Unbound Forms Access 2003 VBA

Wed, 02 May 2012 17:48:31 GMT

It is normally easier to create a normal form and then convert it to an unbound form than it is to create an unbound form from scratch. When your form is bound, you are able to use the built-in form design tools to drag and drop fields onto the form and minimize the chance for misspelling a field name. Once you have your data fields added to the form, you can then convert it to an unbound form relatively easily. To keep things simple, the Shippers table in the NorthwindCS SQL Server database...

Name Statement Syntax Visual Basic VBA

Wed, 02 May 2012 14:07:48 GMT

Name oldpathname As newpathname oldpathname The current filename and optional path. 434 Chapter 7 - The Language Reference The new filename and optional path. Description Renames a disk file or folder. Rules at a Glance newpathnamie must not already exist or an error is generated. ne a thnam e and oldpathnamie can't be on different drives. Path information included in newpathname and oldpathname can take the form of the local system's path or the UNC path. newpathname and oldpathname can't...

The ApplicationEvaluate ApplicationConvertFormula and ApplicationExecuteExcelMacro Functions Excel 2007 VBA 2

Wed, 02 May 2012 10:18:53 GMT

These functions all play by the rules, in that you must use U.S.-formatted strings. They do not, however, have local equivalents. To evaluate a formula that the user may have typed into a UserForm or convert it from using relative to absolute cell ranges , you need to convert it to U.S. format before passing it to Application.Evaluate or Application.ConvertFormula. The Application.ExecuteExcel4Macro function is used to execute XLM-style functions. One of the most common uses of it is to call...

Declare An Array Excel VBA Programming 2

Wed, 02 May 2012 09:08:45 GMT

You can declare an array a group of the same type of data values. You declare an array in the same fashion as you declare any other variable. Just like other variables, you declare arrays as either local or global variable arrays. You specify the scope of an array with either the Dim, Private, or Public statements. See the section Using Global Variables for more information about setting the scope of a variable. You declare arrays to store a group of related data. The array stores data with the...

What Is the Visual Basic Editor Excel 2007 VBA 3

Tue, 01 May 2012 23:28:53 GMT

The Visual Basic Editor is a separate application where you write and edit your VBA macros. It works seamlessly with Excel. By seamlessly, I mean that Excel takes care of opening the VBE when you need it. You can't run the VBE separately Excel must be running in order for the VBE to run. The quickest way to activate the VBE is to press Alt F11 when Excel is active. To return to Excel, press Alt F11 again. You can also activate the VBE by using the DeveloperOCodeOVisual Basic command. If you...

Add Controls To A Excel VBA Programming 2

Tue, 01 May 2012 14:30:55 GMT

You can add controls to a toolbar that correspond to VBA macro code or any other Excel commands that you want to execute when selecting that toolbar control. You can add controls to any toolbar available within Excel. You can add existing Excel controls to the toolbar, or you can add new controls. When you add a new control to a toolbar, you can specify the icon image to represent the control along with the tool tip text, which displays when you drag the cursor across the control. You add a new...

FileSystemObjectCreateFolder Method VB Visual Basic VBA

Tue, 01 May 2012 08:32:32 GMT

oFileSysObj.CreateFolder Path oFileSysObj 286 Chapter 7 - The Language Reference Any object variable returning a FileSystemObject object. An expression that returns the name of the new folder to create. Return Value A Folder object Description Creates a single new folder in the path specified and returns its Folder object. Rules at a Glance Wildcard characters aren't allowed in newfoldername. Path can be a relative or absolute path. If no path is specified in Path, the current drive and...

For EachNext Statement Visual Basic VBA

Mon, 30 Apr 2012 21:34:11 GMT

For Each element In group statements Exit For statements Next element 322 Chapter 7 - The Language Reference A variant or object variable to which the current element from the group is assigned. A collection, object collection, or an array. A line or lines of program code to execute within the loop. Description Loops through the items of a collection or the elements of an array. Rules at a Glance The For Each code block is executed only if group contains at least one element. If group is a...

End Statement Visual Basic VBA

Mon, 30 Apr 2012 21:27:02 GMT

End Property End Select End Sub End Type End With Ends a procedure or a block of code. The End statement is used as follows Marks the end of an enumerated type. Marks the end of a Function procedure. Marks the end of an If Then Else statement. Marks the end of a Property Let, Property Get, or Property Set procedure. Marks the end of a Select Case statement. Marks the end of a user-defined type definition. Although supported, the End statement used by itself to terminate the program shouldn't be...

Naming objects Excel 2007 VBA

Mon, 30 Apr 2012 15:48:33 GMT

In addition to providing names for cells and ranges, you can give more meaningful names to objects such as pivot tables and shapes. This can make it easier to refer to such objects, especially when you refer to them in your VBA code. To change the name of a nonrange object, use the Name box, which is located to the left of the formula bar. Just select the object, type the new name in the Name box, and then press Enter. Note If you simply click elsewhere in your workbook after typing the name in...

String String Functions Visual Basic VBA

Mon, 30 Apr 2012 10:23:00 GMT

Character or character code used to create the required string. VB amp VBA in a Nutshell The Language, eMatter Edition Copyright 2000 O'Reilly amp Associates, Inc. All rights reserved. A Variant of subtype string made up of character, repeated number times. Description Creates a string comprising a specified single character repeated a specified number of times. If number contains Null, Null is returned. If character contains Null, Null is returned. character can be specified as a string or as...

Adding a new Comment object Excel 2003 VBA

Mon, 30 Apr 2012 08:22:20 GMT

You might have noticed that the list of methods for the Comment object doesn't include a method to add a new comment. This is because the AddComment method belongs to the Range object. The following statement adds a comment an empty comment to cell A1 on the active worksheet If you consult the Help system, you'll discover that the AddComment method takes an argument that represents the text for the comment. Therefore, you can add a comment and then add text to the comment with a single...

Multidimensional arrays Excel VBA Programming

Sat, 28 Apr 2012 19:43:53 GMT

The arrays created in the previous examples are all one-dimensional arrays. Arrays you create in VBA can have as many as 60 dimensions although you rarely need more than two or three dimensions in an array. The following example declares a 100-integer array with two dimensions Dim MyArray 1 to 10, 1 to 10 As Integer You can think of this array as occupying a 10-by-10 matrix. To refer to a specific element in this array, you need to specify two index numbers. The following example shows how you...

More about Using Worksheet Functions Excel 2007 VBA 3

Sat, 28 Apr 2012 07:24:11 GMT

Newcomers to VBA often confuse VBA's built-in functions and Excel's workbook functions. A good rule to remember is that VBA doesn't try to reinvent the wheel. For the most part, VBA doesn't duplicate Excel worksheet functions. Bottom line If you need to use a function, first determine whether VBA has something that meets your needs. If not, check out the worksheet functions. If all else fails, you may be able to write a custom function by using VBA. The WorksheetFunction object contains the...

Listing text options Access VBA Programming

Fri, 27 Apr 2012 21:13:22 GMT

A combo box or list box can get its values from a simple string called a Value List. The string just contains each item in the list separated by semicolons. If the items in the list are all text, it's best to enclose each item in quotation marks. For example, Figure 10-4 shows a combo box named OpsCombo added to a form. You can see the items in the open combo box , Like, lt , gt , and so forth. You can also see the properties for the control. Notice that the Row Source Type is Value List, and...

CodeModule Properties Excel 2007 VBA 2

Fri, 27 Apr 2012 17:54:55 GMT

Read-only. Returns the active CodePane for the module. If there is no visible CodePane, one is created and displayed. Note that a CodeModule can have up to two code panes, but there is no CodePanes collection for them Read-only. Returns the number of lines at the top of the module used for Dim, Type, and Option statements. If there are any such items at the top of the module, any comments following them are considered to be part of the following procedure, not the declarations. The following...

Creating Other Types of Outlook Objects from Access Access 2007 VBA

Fri, 27 Apr 2012 17:04:02 GMT

Creating e-mail messages in Outlook isn't the only way to use VBA and Outlook to enhance your database solution. Meetings, appointments, tasks, and journal items can be managed via Outlook using VBA For example, it is common for business users to schedule tasks in Outlook to remind them to complete an action by a certain date and time. It is very easy to create these tasks, as well as other Outlook items, with just a few lines of code. View the code behind the Create Task button's Click event...

RunTime ErrorHandling Excel 2007 VBA 2

Fri, 27 Apr 2012 14:18:02 GMT

When you are designing an application, you should try to anticipate any problems that could occur when the application is used in the real world. You can remove all the bugs in your code and have flawless logic that works with all permutations of conditions, but a simple operational problem could still bring your code crashing down with a less than helpful message displayed to the user. For example, if you try to save a workbook file to the floppy disk in the A drive, and there is no disk in...

The FileNameOnly function Excel 2007 VBA

Fri, 27 Apr 2012 12:58:08 GMT

This function accepts one argument a path with filename and returns only the filename. In other words, it strips out the path. Private Function FileNameOnly pname As String ' Returns the filename from a path filename string Dim i As Integer, length As Integer, temp As String Dim Cnt As Integer ' Count the path separator characters Cnt 0 If Mid pname, i, 1 Application.PathSeparator Then FileNameOnly Split pname, Application.PathSeparator, Cnt End Function If the argument is c excel files 2007...

Customizing Toolbars Excel VBA Programming

Fri, 27 Apr 2012 10:12:58 GMT

The following list summarizes the ways you can customize toolbars. I discuss these topics in detail later in this chapter. 1 Remove toolbar controls from built-in toolbars. You can get rid of toolbar controls that you never use, reduce screen clutter, and free up a few pixels of screen space, to boot. 1 Add toolbar controls to built-in toolbars. You can add as many toolbar controls as you want to any toolbar. The controls can be custom buttons or buttons copied from other toolbars, or they can...

Hiding and restoring toolbars Excel VBA Programming

Thu, 26 Apr 2012 15:02:06 GMT

In some cases, you may want to remove all the toolbars when a workbook is opened. It's only polite, however, to restore the toolbars when your application closes. In this section I present two procedures, both stored in the code window of the ThisWorkbook object. The Workbook_Open procedure, available at this book's Web site, is executed when the workbook is opened. This procedure saves the names of all visible toolbars in column A of Sheet1 and then hides all the toolbars For Each cbar In...

ErrClear Method Syntax Visual Basic VBA

Wed, 25 Apr 2012 23:48:04 GMT

Data Type Error object An instance of the Err object. Description Explicitly resets all the properties of the Err object after an error has been handled. Rules at a Glance You need only to Clear the Err object if you need to reference its properties for another error within the same subroutine or before another On Error statement within the same subroutine. MsgBox The Error amp Err.Description amp vbCrLf _ amp was generated in amp Err.Source Err.Clear End If MsgBox The Error amp Err.Description...

Importing XML data to a list Excel 2003 VBA

Wed, 25 Apr 2012 13:47:47 GMT

The example in the preceding section used XML files that contained only a single record. XML files often contain multiple records, called repeating elements. Examples include a customer list or data for all employees in an organization. You can use the Excel File Open command to open an XML file that contains repeating elements. After you specify the filename, Excel presents the Open XML dialog box, as shown in Figure 4-6. This dialog box has three options As an XML List The file opens, and...

Exchanging Data with Microsoft Word Access 2007 VBA

Tue, 24 Apr 2012 18:34:11 GMT

Access 2007 provides a robust set of reporting features for building breathtaking reports. You can sort, group, filter, total, and even employ and manipulate almost every reporting feature using VBA. But even with all of the flexibility supplied by Access, there are still a number of tasks you cannot complete with Access alone and other tasks where you may want to leverage the rich text editing features of Microsoft Word. As such, this section highlights some code samples illustrating how to...

A Reflection on the Learning Curve Excel 2003 VBA 3

Tue, 24 Apr 2012 12:49:41 GMT

I believe that learning how to develop in Excel using VBA is easy. That said, you'll notice a dramatic improvement in your ability to churn out applications efficiently as you progress. When I first started learning VBA in Excel 5.0, I had a significant amount of experience with Excel. In fact, I was the Excel guru at work. Back then, the development environment wasn't nearly as friendly and hospitable to beginners as it is now. You actually had to memorize the Excel object model you didn't...

Access Database Templates Access 2007 VBA

Tue, 24 Apr 2012 08:33:32 GMT

New to Access 2007, database templates are a great starting point for a simple database solution. Several different types of business and personal database templates are installed with Access and more are available from Office Online. Some of the different types of database applications you can create include Assets For tracking tangible items. Contacts For tracking people or organizations. Events For tracking important dates. Issues For tracking assignable issues or problems. Tasks For...

Control Properties Access 2003 VBA

Tue, 24 Apr 2012 01:50:29 GMT

The Control object has a variety of properties found in the following table. Returns the currently active Application object Used to refer to a specific column in a combo box or list box Used to refer to the collection of all the controls on the form Used to access the properties and methods of a hyperlink object associated with a control Returns the data in the bound column for the specified row in a combo box or list box Returns a reference to the ItemsSelected collection Returns a reference...

Enhancing Reports with VBA Access 2003 VBA

Mon, 23 Apr 2012 22:00:12 GMT

This chapter focuses on the ability to modify Access Reports using VBA programming. If you have read the chapter about enhancing forms, then in this chapter you will recognize many of the same concepts that occurred there. There are many similarities, in that each object has its own properties, which can execute an event procedure. For example, the Form object has an Open event procedure and the Report object has one as well. Once you learn how to utilize an event procedure for one object,...

Creating a Function Procedure Excel 2002 VBA XML ASP

Mon, 23 Apr 2012 17:16:00 GMT

Like Excel functions, function procedures perform calculations and return values. The best way to learn about functions is to create one. So let's get started. After setting up a new VBA project, you will create a function procedure that sums up two values. 1. Open a new Excel workbook and save it as Chap04.xls. 2. Switch to the Visual Basic Editor window and select VBAProject Chap04.xls . 4. Select MyFunctions Chap04.xls in the Project Explorer window, and choose Insert Module. 5. In the...

Setting Other Control Properties Excel 2002 VBA XML ASP

Mon, 23 Apr 2012 13:26:26 GMT

The controls that you placed on the Info Survey form are objects. Each of these objects has its own properties and methods. In the previous section, you changed the Name property for all the objects that will be referenced later from within VBA procedures. The control properties can be set during the design phase of your custom form or at run time that is, when your VBA procedure is executed . Let's now set some control properties for selected controls. To set a property, click a control on the...

Creating Tables and Fields Access 2003 VBA

Sun, 22 Apr 2012 14:52:54 GMT

For our invoicing system, we have two tables to create. The basic procedure for creating a table in code is as follows 1. Check if the table already exists, and if so, delete it. 2. Create the table object using the Database's CreateTableDef method. 3. Create the Field objects in memory, using the TableDef's CreateField method, setting each field's attributes as appropriate. 4. Append each Field object to the TableDef's Fields collection. 5. Append the TableDef object to the Database's...

Converting Forms to Dialog Boxes Access VBA Programming

Sun, 22 Apr 2012 10:07:18 GMT

Message boxes are fine when your code just needs to ask the user a simple question, but sometimes you want to give users several options to choose from. You might want to use a variety of controls, such as check boxes and combo boxes, to present those options. Read about this in the upcoming section, Creating Custom Combo Boxes. When your code needs more than a simple answer to a single question, use a dialog box rather than a message box. A dialog box often called a dialog for short in an...

VBA Excel version compatibility Excel VBA Reference

Sat, 21 Apr 2012 21:01:28 GMT

When Microsoft introduced Excel 97, some radical changes to both the language and the developers interface were made. Excel 97 was the first time that Active X components could be embedded with worksheets and user forms. Compatibility with previous versions of VBA is far less likely than with versions released after Excel 97. At the time of writing this book, these include Excel 2000 and Excel XP. The VBA macros written in this book should work with versions of Excel 97 onwards. However,...

The Select Case structure Excel VBA Programming

Sat, 21 Apr 2012 10:22:19 GMT

The Select Case structure is useful for decisions involving three or more options although it also works with two options, providing an alternative to the If-Then-Else structure . The syntax for the Select Case structure follows Select Case testexpression Case expressionlist-n Don't be scared off by this official syntax. Using the Select Case structure is quite easy. The following example shows how to use the Select Case structure. This also shows another way to code the examples presented in...

Customizing message boxes Excel VBA Programming

Fri, 20 Apr 2012 23:51:59 GMT

The flexibility of the buttons argument makes it easy to customize your message boxes. You can specify which buttons to display, determine whether an icon appears, and decide which button is the default. Table 15-2 lists some of the built-in constants you can use for the buttons argument. If you prefer, you can use the value rather than a constant but I think using the built-in constants is a lot easier . Constants Used in the MsgBox Function Displays Abort, Retry, and Ignore buttons Displays...

Worksheet function examples Excel VBA Programming

Fri, 20 Apr 2012 22:28:59 GMT

In this section, I demonstrate how to use worksheet functions in your VBA expressions. Finding the maximum value in a range Here's an example showing how to use the MAX worksheet function in a VBA procedure. This procedure displays the maximum value in the range named NumberList on the active worksheet You can use the MIN function to get the smallest value in a range. And, as you might expect, you can use other worksheet functions in a similar manner. For example, you can use the LARGE function...

Assigning A Custom Macro To A Builtin Button Excel 2003 VBA

Fri, 20 Apr 2012 19:26:15 GMT

Each of Excel's built-in toolbar buttons executes a specific internal procedure. You can assign your own macro to a built-in button. To do so, just use the OnAction property. The following instruction assigns a macro to the Sort Ascending toolbar button. Ascending .OnAction ShowMsg After executing the instruction, clicking the Sort Ascending button will no longer work. Rather, it will execute the ShowMsg VBA procedure. To return the button to its normal functionality, assign an empty string to...

Using the Editor Format tab Excel 2007 VBA

Fri, 20 Apr 2012 14:28:48 GMT

Figure 7-9 shows the Editor Format tab of the Options dialog box. The options on this tab control the appearance of the VBE itself. Breeipwtt Tem Comment Text KeywddTert Figure 7-9 The Editor Format tab of the Options dialog box. The Code Colors option lets you set the text color foreground and background and the indicator color displayed for various elements of VBA code. This is largely a matter of individual preference. Personally, I find the default colors to be just fine. But for a change...

AutoFilter Properties Excel 2007 VBA 2

Fri, 20 Apr 2012 09:41:57 GMT

Filter Boolean Set Get whether a given worksheet is in the AutoFilter Read-only. Returns the collection of filters associated with the range that was auto-filtered for example, non-blank rows Read-only. Returns the group of cells that have an AutoFilter applied to them Controls the attributes and specifications of a sort with the AutoFilter object

Using Comments in Your VBA Code Excel VBA Programming

Thu, 19 Apr 2012 16:50:56 GMT

A comment is the simplest type of VBA statement. Because VBA ignores these statements, they can consist of anything you want. You can insert a comment to remind yourself why you did something or to clarify some particularly elegant code you wrote. Use comments liberally to describe what the code does which isn't always obvious by reading the code itself . Often, code that makes perfect sense today mystifies you tomorrow. Been there. Done that. You begin a comment with an apostrophe ' . VBA...

DictionaryRemove Method VB Visual Basic VBA

Wed, 18 Apr 2012 12:31:01 GMT

VB amp VBA in a Nutshell The Language, eMatter Edition Copyright 2000 O'Reilly amp Associates, Inc. All rights reserved. The key associated with the item to be removed. Description Removes both the specified key and its associated data i.e., its item from the dictionary. If key doesn't exist, runtime error 32811, Method 'Remove' of object 'IDictionary' failed, occurs.

Workspace files Excel 2003 VBA

Tue, 17 Apr 2012 22:27:13 GMT

A workspace file is a special file that contains information about an Excel workspace. For example, if you have a project that uses two workbooks and you like to have the workbook windows arranged in a particular way, you can save an XLW file choose the File Save Workspace command to save this window configuration. Then, whenever you open the XLW file, Excel restores the desired workspace. Which Version Created That XLS File Unfortunately, there is no direct way to determine which version of...

Integration with SharePoint Access 2007 VBA

Tue, 17 Apr 2012 15:59:16 GMT

In the past, the default setting was for Access MDB database files to be blocked by SharePoint, Outlook, and most anti-virus programs because unsafe code could be shipped and trigged to run from the database. That isn't the case for the Access 2007 ACCDB file format. Since the code can be either verified as safe or disabled, it is easier to integrate Access databases more fully with SharePoint Version 3 sites and Office Outlook 2007. That also means that it is easier for anti-virus programs to...

Defining the recordset and data source Access 2007 VBA 2

Tue, 17 Apr 2012 15:29:48 GMT

Referring to most of the previous recordset examples, the second two lines of code declare what the recordset is and where it gets its data, as shown here Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection cnn1 The first line declares to all the code that follows within the current procedure that the name myRecordSet refers from here on out to an ActiveX Data Objects Database recordset. That tells the rest of the code a lot about what myRecordSet is, but it doesn't say anything...

Stepping through Code Access 2003 VBA

Tue, 17 Apr 2012 11:00:07 GMT

In most cases, you design code to run with little or no user intervention. However, when you're testing code, sometimes you want to do more than insert a breakpoint or two or include a couple of Debug .Print statements. If you're running code with many variable changes or some intricate looping, it can sometimes be helpful to step through the code line by line. Doing this allows you to watch the value of variables after each line of code is executed. This can help you pinpoint any errors or...

Converting to Access Is a TwoVersion Process Access 2007 VBA

Mon, 16 Apr 2012 16:53:27 GMT

Rather than converting a 2007 file to work with Access 97, consider converting all Access 97 applications to the 2002-2003 file format if at all possible. If the situation demands that the files be converted to Access 97, keep in mind two import factors Microsoft no longer supports Access 97, although technical support is still available from a dwindling percentage of developers. Access 97 does not support Unicode, so there will be issues if the databases contain Unicode data, including Asian...

FolderCopy Method VB Visual Basic VBA

Fri, 13 Apr 2012 13:24:06 GMT

oFolderObj.Copy Destination , OverwriteFiles oFolderObj Any object variable returning a Folder object. The path and optionally the filename of the copy to be made. Indicates whether existing files and folders should be overwritten True or not False . Copies the current folder and its contents, including other folders, to another location. Wildcard characters can't be used in Destination. The folder and all subfolders and files contained in the source folder are copied to Destination. That is to...

Vba Programming Basics Ohj Excel VBA Programming 2

Fri, 13 Apr 2012 11:25:15 GMT

A procedure is simply a block of code that performs specific actions. Typically, when using VBA with Excel macros, you perform the actions with or on Excel objects. VBA provides essentially two types of VBA procedures Functions and Subroutines. The only real difference between the two types of procedures is that a function returns a value. If, up to this point, you have created all of your VBA code with the Macro Recorder, you have probably seen only subrountines. Because you cannot call...

Open A Workbook Excel VBA Programming 2

Thu, 12 Apr 2012 18:34:14 GMT

can create a procedure to open a workbook in Excel using the Open method of the Workbooks collection. Each time you open another workbook, Excel adds that workbook to the Workbooks collection. Similar to using the Open command on the File menu, opening another workbook using the Open method makes the workbook active. Sixteen different parameters determine how Excel opens a workbook. Of these parameters, Excel requires only FileName. In addition, you only need to use the FileName, ReadOnly,...

Selecting a range Excel VBA Programming

Thu, 12 Apr 2012 14:11:21 GMT

In some cases, you may want the user to select a range while a dialog box is displayed. An example of this choice occurs in the second step of the Excel Chart Wizard. The Chart Wizard guesses the range to be charted, but the user is free to change it from the dialog box. To allow a range selection in your dialog box, add a RefEdit control. The following example displays a dialog box with the current region's range address displayed in a RefEdit control, as shown in Figure 18-6. The current...

Other operators Excel VBA Programming

Thu, 12 Apr 2012 10:29:33 GMT

Operators play a major role in VBA. Besides the equal sign operator discussed in the previous section , VBA provides several other operators. Table 7-3 lists these operators, with which you are familiar from your worksheet formulas experience. The term concatenation is programmer speak for put together. Thus, if you concatenate strings, you are combining strings to make a new and improved string. As shown in Table 7-4, VBA also provides a full set of logical operators. Consult the Help system...

The Erase Function Excel 2002 VBA XML ASP

Thu, 12 Apr 2012 00:35:32 GMT

When you want to remove the data from an array, you should use the Erase function. This function deletes all the data held by static or dynamic arrays. In addition, for a dynamic array, the Erase function reallocates all of the memory assigned to the array. If a procedure has to use the dynamic array again, you must use the ReDim statement to specify the size of the array. The example below shows how to erase the data from the array cities. 1. Insert a new module into the current project and...

The Delete method Excel VBA Programming

Wed, 11 Apr 2012 14:25:11 GMT

Clearing a range differs from deleting a range. When you delete a range, Excel shifts the remaining cells around to fill up the range you deleted. The following example uses the Delete method to delete row 6 When you delete a range that's not a complete row or column, Excel needs to know how to shift the cells. To see how this works, experiment with the Excel EditODelete command. The following statement deletes a range and then fills the resulting gap by shifting the other cells to the left...

About the ListBox control Excel 2003 VBA

Wed, 11 Apr 2012 13:17:22 GMT

Following are a few points to keep in mind when working with ListBox controls. Examples in the sections that follow demonstrate many of these points. The items in a ListBox can be retrieved from a range of cells specified by the RowSource property , or they can be added by using VBA code using the Addltem method . A ListBox can be set up to allow a single selection or a multiple selection. This is determined by the MultiSelect property. If a ListBox is not set up for a multiple selection, the...

ListBox control Excel VBA Programming

Wed, 11 Apr 2012 13:16:58 GMT

The ListBox control presents a list of items from which the user can choose one or more. Figure 17-10 shows a dialog box with two ListBox controls. ListBox controls are very flexible. For example, you can specify a worksheet range that holds the ListBox items, and the range can consist of multiple columns. Or you can fill the ListBox with items by using VBA code. The following is a description of the most useful ListBox control properties 1 BoundColumn If the list contains multiple columns,...

Stepping through a Procedure Excel 2002 VBA XML ASP

Tue, 10 Apr 2012 16:49:53 GMT

1. Place the insertion point anywhere inside the code of the procedure whose execution you wish to trace. 2. Press F8 or choose Debug Step Into. Visual Basic executes the current statement and automatically advances to the next statement and suspends execution. While in break mode, you can activate the Immediate window, Watches window, or Locals window to see the effect of a particular statement on the values of variables and expressions. And, if the procedure you are stepping through calls...

Building SQL Strings with Quotes Access 2003 VBA

Mon, 09 Apr 2012 20:33:46 GMT

The first thing to learn about building SQL statements in VBA is handling concatenation and quotes. They may seem simple, but many programmers have stared at VBA strings with multiple nested quotes and struggled to make them work. Consider a SQL string that selects a record for a particular business from a table of businesses Select From tblBusiness Where BusinessKey 17 In actual usage, we need to replace the 17 in the above statement with the BusinessKey that the user is currently working...

Using Worksheet Functions in VBA Excel VBA Programming

Fri, 06 Apr 2012 13:44:37 GMT

Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use most of Excel's worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function. VBA makes Excel's worksheet functions available through the WorksheetFunction object, which is contained in the Application object. Remember, the Application object is Excel. Therefore, any statement...

An Intentional Error Excel VBA Programming

Thu, 05 Apr 2012 16:36:26 GMT

Sometimes you can use an error to your advantage. For example, suppose you have a macro that works only if a particular workbook is open. How can you determine whether that workbook is open Perhaps the best solution is to write a general-purpose function that accepts one argument a workbook name and returns True if the workbook is open, False if it's not. This function takes advantage of the fact that Excel generates an error if you refer to a workbook that is not open. For example, the...

Example Code in the Chart Sheets Code Module Excel VBA Macros

Wed, 04 Apr 2012 06:13:54 GMT

Dim iType As Integer, sName As String Dim fr As Integer charttypes.txt For Input As fr ActiveChart.ChartType iType ActiveChart.HasTitle True ActiveChart.ChartTitle.Text _ iType amp amp sName DoEvents Loop Until bPause False End If Loop Sub Delay rTime As Single 'Delay rTime seconds min .01, max 300 Dim OldTime As Variant 'Safty net If rTime lt 0.01 Or rTime gt 300 Then rTime 1 OldTime Timer Do Loop Until Timer - OldTime gt rTime End Sub Private Sub Chart MouseDown ByVal Button As Long, ByVal...

Determining the selection type Excel VBA Programming

Thu, 29 Mar 2012 19:55:39 GMT

If you design your macro to work with a range selection, the macro must be able to determine whether a range is actually selected. If something other than a range is selected such as a chart or a shape , the macro will probably bomb. The following procedure uses the VBA TypeName function to identify the type of object that is currently selected If a Range object is selected, the MsgBox displays Range. If your macro works only with ranges, you can use an If statement to ensure that a range is...

Abs Function Visual Basic VBA

Thu, 29 Mar 2012 14:50:14 GMT

Data Type Any valid numeric expression The number whose absolute value is to be returned. The absolute value of number. The data type is the same as that passed to the function. Returns the absolute value of a number i.e., its unsigned magnitude . For example, Abs -1 and Abs 1 both return 1. If number contains Null, Null is returned if it's an uninitialized variable, zero is returned. Only numeric values can be passed to the Abs function. Example In this example, the LineLength function...

Problems 1 Excel 2007 VBA Methods

Wed, 28 Mar 2012 14:30:03 GMT

Answers to the following problems are found in the folder Ch. 04 Number Series in the Problems amp Solutions folder on the CD. 1. Evaluate the following infinite series a 1 2 b 1 In2 c 1 3. Evaluate the following infinite series in - lX2 1 over the first 100 terms of the series. 7. Evaluate Wallis' series for , summing over 65,536 terms. Use a worksheet formula that uses ROW and INDIRECT to create the series of integers. 8. A simple yet surprisingly efficient method to calculate the square root...

Forms Access 2007 VBA

Wed, 28 Mar 2012 13:09:10 GMT

First impressions are lasting, and developers rely on forms to create an instant bond between their solutions and the user. The forms need to be intuitive, readable, and easy to navigate. No matter how good the data structure is, if the user doesn't understand how a form is intended to work, the solution won't fly. Some of the new features for forms include the split form, alternating row color, auto resizing, context sensitive filtering, and the date picker calendar control , bound image...

The Terminate Event Visual Basic VBA

Tue, 27 Mar 2012 16:00:59 GMT

As with the Initialize event, the precise time the Terminate event is fired has created some confusion. Once again, we'll examine when the Terminate event is fired before looking at some applications of the Terminate event handler. The simple answer is that the Terminate event is fired when all references to the object are set to Nothing. However, life's never that simple. You may assume that because you have placed a Set objVar Nothing statement in your program that the objVar's Terminate...

NonMail Merge OperationsSending Access Data to Word Access 2003 VBA

Sun, 25 Mar 2012 04:14:15 GMT

In addition to using VBA within Access to create a mail merge, you can also export just about any information within Access to Word using VBA and the Word object model. As a short example, the following code uses the CommonDialog control to allow a user to export an Access report to a specific Word document. You might want to do this if you have users who need to modify the report or need to perform other operations such as sending an external document to the report or e-mailing the report to...

FileSystemObjectBuildPath Method VB Visual Basic VBA

Fri, 23 Mar 2012 14:31:04 GMT

282 Chapter 7- The Language Reference Any object variable returning a FileSystemObject object. The folder or file path to append to path Return Value String Creates a string by concatenating the path parameter with the folder or filename, adding, where required, the correct path separator for the host system. Path can be an absolute or relative path and doesn't have to include the drive name. Neither Path nor Name has to currently exist. BuildPath doesn't check the validity of the new folder or...

Replace Function VB Visual Basic VBA

Fri, 23 Mar 2012 11:15:19 GMT

Replace string, stringToReplace, replacementString , _ start , count , compare The complete string containing the substring to be replaced. The substring to be found by the function. The new substring to replace stringToReplace in string. The character position in string at which the search for stringToRe-place begins. The number of instances of stringToReplace to replace. The method that compares stringToReplace with string its value can be vbBinaryCompare, vbTextCompare, or vbDatabaseCompare....

Adding a Chart Sheet Using VBA Code Excel 2007 VBA 2

Fri, 23 Mar 2012 08:16:29 GMT

The recorded code is a bit odd. There is no need to create an embedded chart. You can simply add a chart sheet and set its properties directly. You can also create an object variable, so that you have a simple and efficient way of referring to the chart in subsequent code. Rather than limit yourself to the preset layouts, you can select the chart features you want, such as a title. There is no need to plot by columns and then plot by rows. The following code incorporates these changes Sub...

Reference Object Properties Access 2003 VBA

Fri, 23 Mar 2012 07:22:18 GMT

The properties of the Reference object are listed in the following table. Specifies whether a reference points to a default Reference necessary for Access to function properly Returns a reference to the collection that contains an object Specifies the path and file name of the referenced type library Returns a GUID that identifies the type library in the registry Specifies whether a Reference object points to a valid reference in the registry Specifies the type of reference that a Reference...

UserLevel Security Using DAO Access 2007 VBA

Tue, 20 Mar 2012 14:01:05 GMT

User-level security can be modified using DAO code. Users and Groups are collections of the Database Engine Workspace. The procedures defined here work with users and groups on the open workspace in the current database engine, so the current session must use the correct MDW file to use these procedures. Additionally, these example procedures set permissions in the database that is open returned by the CurrentDB object . Calling these methods in the current database will result in modifying the...

Return A Portion Of A String Excel VBA Programming 2

Mon, 19 Mar 2012 16:20:45 GMT

Instead of an entire string, you can use the built-in functions available in VBA to return only a portion of a string. These functions work well when you only want a smaller portion of a string. You can use three different functions to return a portion of a string. The Left function returns the specified number of characters starting at the left side, or beginning, of the string. The Right function returns the specified number of characters starting at the right side, or end of the string. Each...

VBA function examples Excel VBA Programming

Mon, 19 Mar 2012 16:11:28 GMT

In this section, I present a few examples of using VBA functions in code. In many of these examples, I use the MsgBox function to display a value in a message box. Yes, MsgBox is a VBA function a rather unusual one, but a function nonetheless. This useful function displays a message in a pop-up dialog box. For more details about the MsgBox function, see Chapter 15. A workbook that contains all of the examples is available at this book's Web site. The first example uses VBA's Date function to...

Maintaining Groups with DAO Access 2007 VBA

Mon, 19 Mar 2012 12:13:04 GMT

The AddGroupDAO procedure adds groups to the MDW file. The RemoveGroupDAO procedure drops groups from the MDW file. Adding groups to the MDW file does not modify the original database in any way, only the MDW file. To add a group, create an object of type Group, set the properties for it, and then append the group to the Groups collection for the workspace Public Sub AddGroupDAO strGroupName As String, strGID ' Define variables Dim ws As Workspace Dim grp As New Group ' Create a workspace...

FileSystemObjectGetTempName Method VB Visual Basic VBA

Mon, 19 Mar 2012 07:09:24 GMT

oFileSysObj. GetTempName oFileSysObj Data Type FileSystemObject object Any object variable returning a FileSystemObject object. Return Value A String. Description Returns a system-generated temporary file or folder name. Rules at a Glance GetTempName doesn't create a temporary file or folder it simply provides a name you can use with the CreateTextFile method. As a general rule, you shouldn't create your own temporary filenames. Windows provides an algorithm within the Windows API to generate...

Working With Other Workbooks And Files Ilp Excel VBA Programming 2

Thu, 15 Mar 2012 21:10:04 GMT

' 0 Type Workbooks.OpenText Filename UserFile to open the file selected in the Open dialog box. Note See Open a Text File as a Workbook for more information on using the OpenText method. Switch to Excel and run the macro. The Text Files dialog box requests the workbook to open.

LeaderLines Object Example Excel 2007 VBA 2

Thu, 15 Mar 2012 09:33:01 GMT

Sub AddAndFormatLeaderLines Dim oChart As Chart Dim oLeaderLines As LeaderLines Set oChart ActiveSheet.ChartObjects Chart 1' 'Apply labels and add leaderlines With oChart.SeriesCollection 1 .ApplyDataLabels .HasLeaderLines True End With Set oLeaderLines oChart.SeriesCollection 1 . 'Format the leaderlines With oLeaderLines .Border.LineStyle xlContinuous .Border.ColorIndex 5 End With End Sub

Naming cells and ranges Excel 2003 VBA

Wed, 14 Mar 2012 02:38:23 GMT

You create names for cells or ranges by choosing the Insert Name Define command or by pressing Ctrl F3 . An even faster way to create names is to use the Name Box the drop-down list at the left side of the formula bar . When using the Name Box, just select the cell or range, type the name into the Name Box, and press Enter. You can choose the Insert Name Create command to create names automatically for cells or ranges based on row or column titles on your worksheet. In Figure 3-2, for example,...