Listing Using a Progress Meter in Access
Public Function UseApplication ' Create an output string. Dim Output As String ' Fill it with text version of a numeric error. Output Application.AccessError 14 MsgBox Error number 14 is Chr amp H22 Output _ Chr amp H22 , vblnformation, Error Number Text ' Add this database to the favorites list. Application.AddToFavorites ' Perform some system commands. ' Create a progress meter. Application.SysCmd acSysCmdInitMeter, Progress Meter, 5 ' Update the meter until finished. For Counter 1 To 5...
Performing tasks when the Ribbon loads
You have access to a wealth of information about the Ribbon. However, most of this information comes from callbacks. One of the most important callbacks concerns Ribbon loading. You use the onLoad attribute for the lt customUI gt element, as shown here onLoad RibbonLoaded gt This callback looks for a RibbonLoaded Sub in your VBA code. As with button and other control events, you can ask the Custom UI Editor to generate the required Sub code for you automatically. All you need to do then is fill...
VBA Programming with XML
Defining the features of Word Markup Language WordML Designing your first XML document Working with XML data Creating an XML document Using eXtensible Style Language Transformation XSLT to modify presentation 7he eXtensible Markup Language XML is a special way of marking text so that it contains both information and context. Using this technique means that the recipient knows not only the information but also the meaning behind the information. For example, if you see 12.99 in a text file, you...
Working with Settings
Many applications, including Windows, use INI files to store information. The INI file has a standard format that is easy to work with. View any of the INI files in the Windows folder, such as Win.INI or System.INI, and you see two kinds of entry. The first is the section entry, which is surrounded by square brackets, such as the fonts section in the Win.INI file. The second kind of entry is a key-and-value pair. The key comes first and is separated from the value by an equals sign. You might...
Security under Vista
Nlfii Vista places extra security constraints on Office products. The User Access Control UAC makes it impossible to run some macros that would ordinarily work under previous versions of Windows. Even setting the macro security won't help, in some cases, depending on the security policies set by the administrator, your personal security settings, and the task the macro performs. In general, you want to sign your macros before you use them under Vista. See the Adding a Digital Signature to Your...
Defining compiler options
When you first start VBA, it makes certain assumptions about how you want to write code. These assumptions might not be accurate, so Microsoft provides a way for you to tell VBA to do something else. The compiler options listed in Table 3-1 help you define how VBA works with your code. A compiler reads your code and translates the words into instructions that Windows can understand. You can add these options at the very beginning of a module, class module, or form, before any other code...
Customizing an applications interface
Sometimes an application feature just bugs you. You could turn it off if it bugs you that much, but that might not be an option if you need that function in your work. Use VBA to create a new version of the feature with everything that you need and nothing that you don't. For example, I never liked how Word performs a word count, so I created my own program to perform the task. Chapter 12 shows you some of my secrets for taming unruly interfaces. Changing an application interface to your liking...
Defining a schema
A schema is a definition of the structure of your data. You can look at the data and figure out the structure, but the computer can't it needs additional information. Database managers such as Access always use a schema you define it when you create a table , but other Office applications don't because you normally don't need to organize the information by using a schema. Figure 11-4 shows a typical worksheet. The data structure is probably obvious to you because each of the columns is named,...
Listing Opening a Configuration File for Writing
Public Sub OpenWriteConfig _ AppName As String, _ Filename As String, _ ByRef Output As TextStream ' Define the path variable. Dim DataPath As String ' Create a path string for the file. Start with the ' default program settings path. Add a special folder ' for this program. DataPath Application.UserLibraryPath AppName ' Create a file system object. Dim FS As FileSystemObject Set FS New FileSystemObject ' Verify the path exists. If Not FS.FolderExists DataPath Then ' If not, create it....
Using forms creatively
Good design begins when you consider how your application uses the information that appears on the form and how you want to interact with it. A form that presents every piece of information that you might ever need sounds good until you try to use it. A form should contain focused information that easily fits on any display that you use. You might find that you need to design a series of forms that appear in sequence. Another consideration is clutter. Even if you create a focused form, it might...
Writing Your First Sub
Most Microsoft Office products provide a Properties dialog box, similar to the one shown in Figure 3-10, that contains a Summary tab for documents. You can also find a variation of this Properties dialog box for most third-party products. The Summary tab can provide a lot of valuable information for your programs. You can find out basic statistics, such as the author's name and the company that created the document. The document information also includes statistics, such as the number of words...
Using the Workbooks collection
The Workbooks collection contains a list of all the workbooks that are open at any given time. From this list, you can select a single Workbook object to use in your program. The Workbook object contains general information about the file, such as its name and location. You can also use the Workbook object to access any other major object in the document, which includes all Worksheet objects and standalone Chart objects. Listing 14-1 shows an example of the Workbooks collection in use. You can...
Understanding userdefined data types
Creating a user-defined type means defining the data that you want to use together and deciding which native data types work best. The example in the earlier Adding keyed data to the collection section uses a database connection to get hold of data and place it in a collection. The implementation the actual coding in that example is difficult because you have to make three entries in the collection for each database record. Here's a user-defined type that places all the information in one...
Understanding array usage
You can define arrays by using several techniques. However, all these techniques use the same basic approach. Listing 9-1 contains an example that demonstrates the essential array usage process. You can find the source code for this example on the Dummies.com site at http www.dummies. com go vbafd5e. Listing 9-1 Creating and Using an Array for String Data ' Tell VBA to start all arrays at 0. Option Base 0 ' Define an output string. Dim Output As String ' Define a variant to hold individual...
Listing Designing a Rotating Chart Presentation
Set EmbeddedChart Sheet2.ChartObjects 1 .Chart ' Rotate between chart types. Change the title as ' needed to match the chart type. Select Case .ChartType Case XlChartType.xlPie .ChartType xlArea .ChartTitle.Caption More Data Case XlChartType.xlArea .ChartType xlLine .ChartTitle.Caption More Data Case XlChartType.xlLine .ChartType xlColumnClustered .ChartTitle.Caption More Data Case XlChartType.xlColumnClustered .ChartType xlPie .ChartTitle.Caption More Data End Select The code begins by...
Creating a connection between forms and modules
Generally, you need to make some type of connection between a form and a module to display the form. The user can only access Subs contained in a module through the Macro dialog box. Consequently, the code you include with a form should affect only the handling of data locally to that form. When you want to interact with the user programmatically, you use a module. The example in this section shows how to display a form from a module. You'll see a number of other examples of interactions...
Understanding the Database objects
Access provides a number of Database objects that you commonly use to create programs. Here's a list of the common objects and collections that you use for database programs 1 Connections collection You must create at least one connection for each Access database. The connection determines how the program communicates with the database. However, you can create more than one connection when you're using ADO. By way of contrast, when you're using DAO, the Connection object and the Database object...
Listing Using the DoCmd Object for Special Tasks
Public Function SpecialCommands ' Sound a beep. MsgBox Sounding a beep , vblnformation, DoCmd Event DoCmd.Beep ' Turn echo off and then back on. DoCmd.Echo False, Echo is off MsgBox Echo is off , vblnformation, DoCmd Event DoCmd.Echo True, Echo is on MsgBox Echo is on , vblnformation, DoCmd Event ' Open and close a query. DoCmd.OpenQuery GetWordList, acViewNormal, acReadOnly MsgBox Query is open , vblnformation, DoCmd Event DoCmd.Close acQuery, GetWordList MsgBox Query is closed ,...
Presenting data in a pleasing format
Most programs can use the default formatting that VBA provides for output. However, you might need some way to present the data in another way, such as a short or long date 04 24 06 or 24 April 2006 . It's important to know how to format data so that you can create astounding reports and concise analysis. The Format function is the most common way to change the appearance of data. This function accepts any valid expression as input. You can also supply an expression that defines how to format...
Using date and time Values
Tracking time and date in your program can be important. Client contact entries in Access usually require the date that the client was last contacted. A spreadsheet might require dates for each entry in a ledger. It's helpful to include dates in Word documents so that you know the last time that someone accessed or changed it. You might need to know how long a task takes or have an indicator of when time has elapsed. Both date and time variables rely on the Date data type. This data type...
Using currency Values for money calculations
Money usually requires special handling on a computer because you don't want to introduce rounding or other errors. Even small incremental errors can result in large errors if they accumulate over time. The Currency data type provides special handling for money calculations but at a slight performance hit because the Currency data type requires additional memory and processing cycles. Along with the Currency data type, VBA provides a number of special functions for calculating common monetary...
Listing Defining WaitForSingleObject
' This function causes VBA to wait for the external ' process and defines how long to wait. Private Declare Function WaitForSingleObject _ Lib kernel32 _ ByVal hHandle As Long, _ ByVal dwMilliseconds As Long As Long The input arguments are simple. The handle is the handle returned by CreateProcessA . The dwMilliseconds argument tells how long to wait in milliseconds. Of course, you might not know how long to wait, so WaitForSingleObject also provides a special INFINITE value, as described here...
Listing Using the Clipboard to Copy Data
' Create a Clipboard storage object. Dim ClipData As DataObject Set ClipData New DataObject ' Place the text in lblOutput in the Clipboard. ClipData.SetText lblOutput.Caption ' Place the object on the Clipboard. Private Sub btnQuit_Click 'End the program. End End Sub ' Create a string to hold the data. Dim Output As String ' Create a document property holder. Dim DocProp As DocumentProperty ' Handle properties that the application doesn't ' Start creating the output string. For Each DocProp In...
Listing Creating and Printing a Label
' Set the form's properties for labels. Set GetData New frmAddress GetData.Caption Create Labels GetData.btnCreate.ControlTipText _ Create the labels. GetData.btnCancel.ControlTipText _ Don't print the labels. ' Get the recipient's address. GetData.Show vbModal ' Determine whether the user wants to ' create the envelope. If GetData.Result Cancel Then ' Display a result message and exit. MsgBox Label Printing Cancelled, _ vbOKOnly Or vblnformation, _ No Labels Today Exit Sub End If ' Send the...
Using the CurrentDB and related objects
The latest version of Access has new and interesting ways to perform a task. The CurrentDB object originally appeared in Office 2000 as an updated version of the DBEngine.Workspaces 0 .Databases 0 object. You should use the CurrentDB object, whenever possible, to perform actual database work because this object includes a few new features that make it a better fit for multi-user environments. However, when you want to use multiple workspaces, you still need to use the DBEngine.Workspaces...
Listing Highlighting Text in Word
' Holds the current pane. Dim CurrPane As Pane ' Locate the window based on the expected name. Dim LocateWindow As Window ' The current window. Dim WinString As String ' The window caption. For Each LocateWindow In Application.Windows If Left LocateWindow.Caption, 15 _ WordObjects.doc Then WinString LocateWindow.Caption End If ' Make sure you can access the pane properly. Application.Windows WinString .View _ wdNormalView ' Access the test document pane. Set CurrPane _ ' Get the word to...
Listing Opening a Configuration File for Reading
Public Function OpenReadConfig AppName As String, Filename As String, ByRef Output As TextStream As Boolean ' Define the path variable. Dim DataPath As String DataPath Application.UserLibraryPath AppName ' Create a file system object. Dim FS As FileSystemObject Set FS New FileSystemObject ' Determine whether the file exists. If Not FS.FileExists DataPath Filename Then ' Tell the user there are no config settings. OpenReadConfig False ' Exit the Sub without reading the file. Exit Function End If...
Listing Processing a Word Document into an Outlook Message
' Go to the beginning of the document. .GoTo What wdGoToLine, Which wdGoToFirst ' Holds the subject and general text. Dim Subject As String Dim General As String ' Start by selecting the first word. .End .Start 8 ' Make sure it's a subject. If .Text Subject Then 'Go to the next line and select the subject. .GoTo What wdGoToLine, Which wdGoToNext .EndOf Unit wdParagraph, Extend wdExtend .End .End - 1 Subject .Text Else MsgBox You must include a subject line , _ vbCritical, File Formatting Error...
Listing Creating an Excel Chart
Public Sub BuildChart ' Create a new chart. Dim NewChart As Chart Set NewChart ' Change the name. NewChart.Name Added Chart ' Create a series for the chart. Dim TheSeries As Series NewChart.SeriesCollection.Add _ Source Worksheets My Data Set TheSeries NewChart.SeriesCollection l ' Change the chart type. TheSeries.ChartType xl3DPie TheSeries.Name Data from My Data Sheet ' Perform some data formatting. With TheSeries .HasDataLabels True .DataLabels.ShowValue True .DataLabels.Font.Italic True...
Listing Defining CreateProcessA
' This Win32 API call creates an external process. Private Declare Function CreateProcessA Lib kernel32 ByVal lpApplicationName As String, _ ByVal lpCommandLine As String, _ ByVal lpProcessAttributes As Long, _ ByVal lpThreadAttributes As Long, _ ByVal bInheritHandles As Long, _ ByVal dwCreationFlags As Long, _ ByVal lpEnvironment As Long, _ ByVal lpCurrentDirectory As String, _ lpStartuplnfo As STARTUPINFO, _ lpProcesslnformation As PROCESS_INFORMATION _ As Long ' This structure describes how...
Overcoming UACproblems in Vista
Many Word 2007 applications rely on Registry access, as this one does. Unfortunately, the UAC feature of Vista restricts Registry access. You may find that some of your code doesn't run unless you disable the requisite UAC policy. Rather than disable UAC completely, you can overcome this problem by using the following procedure 1. Open the Local Security Policy console, located in the Administrative Tools folder of the Control Panel. If necessary, give permission to Vista when asked to open the...
Ten Kinds of VBA Resources
Using traditional paper magazines and periodicals Getting information through free electronic newsletters Getting the latest scoop using RSS Accessing Microsoft newsgroups Using third-party newsgroups Joining list servers and other e-mail-based systems Finding coding examples and non-Microsoft documentation Locating tools to make programming easier Downloading cool ActiveX controls and components Using the author of this book as a resource X ou aren't alone in your quest for the perfect VBA...
Displaying or hiding toolbars and menus
It's helpful to hide or display menus or toolbars as needed. Keeping the display free from clutter is always helpful in improving efficiency and reducing confusion. Both menus and items use the same technique. Working with menus is a little more cumbersome because you have to figure out the name of the menu bar first. It would be nice if Microsoft used the same name for the menu bar in every Office application, but that's just not the case. For example, Excel's menu bar is named Worksheet Menu...
Saving your Word document by using XSLT
You can use XSLT to work with any XML file generated by an Office application even the WordML documents created by default with Word see the earlier Comparing WordML with Saved XML section for details . When you use the Save As dialog box refer to Figure 11-3 to save a Word document as XML, you can choose to use an XSLT file to modify the output. Although this chapter provides enough information about XSLT to work with the examples, XSLT For Dummies, by Richard Wagner, provides additional...
Looking at the Integrated Development Environment IDE
VBA is a visual programming environment. That is, you see how your program will look before you run it. Its editor is very visual, using various windows to make your programming experience easy and manageable. You'll notice slight differences in the appearance of the editor when you use it with Vista as compared to older versions of Windows. In addition, you might notice slight differences when using the editor with a core Office application one that uses the new Ribbon interface. Figure 1-3...
Writing an INI file
It pays to create one Dictionary object for each section of your INI file. Using the objects in this way makes it easy to quickly locate the information that you need. The trade-off is that you have more objects to work with, and your program will experience a small performance hit. Listing 10-1 contains the code that you need to use a Dictionary object to write to an INI file. You can find the source code for this example on the Dummies.com site at http www.dummies.com go vbafd5e.
Using the Basic Controls
VBA provides a good set of basic controls, as shown in Figure 7-2. You might use VBA to create a number of programs and never need anything more than the basic control set. In fact, the basic control set meets just about every need, and you seldom see anything other than these controls used in Windows programs. Click the Select Objects button refer to Figure 7-2 whenever you want to select any object on a form. VBA selects this button by default. It also picks the Select Objects button after...
Listing Using XSLT to Save a Word Document
lt xml version '1.0' gt lt xsl stylesheet version '1.0' lt xsl output method html indent yes gt lt xsl template match gt lt -- Create the HTML Code for this stylesheet. -- gt lt TITLE gt Word Document Properties lt TITLE gt lt HEAD gt lt CENTER gt lt H3 gt Word Document Property Values lt H3 gt lt CENTER gt lt TABLE BORDER 2 gt lt TR gt lt TH gt Property lt TH gt lt TH gt Value lt TH gt lt TR gt lt xsl apply-templates select w title gt lt TABLE gt lt -- XSL template section that describes...
Listing Accessing the Registry with VBA
' This Windows API function opens a Registry key. Public Declare Function RegOpenKey _ Lib advapi3 2.dll _ Alias RegOpenKeyA ByVal HKey As Long, _ ByVal lpSubKey As String, _ phkResult As Long As Boolean ' Use this enumeration for the top-level keys. Public Enum ROOT_KEYS HKEY_CLASSES_ROOT amp H80000000 HKEY_CURRENT_USER amp H80000001 HKEY_LOCAL_MACHINE amp H800 000 02 HKEY_USERS amp H800 00 003 HKEY_PERFORMANCE_DATA amp H80000004 HKEY_CURRENT_CONFIG amp H80000005 HKEY_DYN_DATA amp H80000006...
Using Project Explorer
Project Explorer appears in the Project Explorer window. You use it to interact with the objects that make up a project. A project is an individual file used to hold your program, or at least pieces of it. The project resides within the Office document that you're using, so when you open the Office document, you also open the project. See Chapter 3 for a description of how projects and programs interact. Project Explorer works much like how the left pane of Windows Explorer does. Normally, you...
Listing Getting the Default DBEngine Settings
Public Function GetDBEngineProperties ' Create an output string. Dim Output As String ' Make a place for the property values. Dim CurProp As Property ' Some properties won't have a value. On Error Resume Next ' Get the current properties. For Each CurProp In DBEngine.Properties Output Output CurProp.Name CStr CurProp.Value vbCrLf MsgBox Output, vblnformation, DBEngine Properties End Function Many of the objects in Access have a Properties collection. Each Property is a separate object that...
Handling form events
Events are a part of most objects. Whenever the system, a user, or an external input interacts with your program, it's likely to generate an event. For example, when you click a button, it generates a Click event. An event is simply a way of saying that something has happened. Forms and every control that they contain support a number of events. Look at the top of the Code window, and you see two Combo Boxes. The first is the Object list. It contains a list of every object associated with the...
Listing Converting a Numeric Type Value into a String
This function ends up as a giant Select Case statement. Always include a Case Else clause in case the function receives a nonstandard value. Otherwise, your code could fail. Figure 15-5 shows typical output from this example. TableDef, Record, and Field objects to work with tables. MSysAccessStora g e Name DateCreate Value 3 31 2003 4 52 02 PM Name DateUpdate Type dbLongBinary Value Null Name Name Value MSysAccessStorage_ROOT Name Parentld Figure 15-5 shows one of the hidden Access tables that...
Listing Listing Word Template Properties
' Holds the current template. Dim CurrTemp As Template ' Holds the built-in properties. Dim CurrProp As DocumentProperty ' Holds the property name length. Dim PropLen As Integer ' Holds the output. Dim Output As String ' Look at each template in the Templates collection. For Each CurrTemp In Application.Templates ' Get information about the current template. With CurrTemp Name .Name vbCrLf _ Full Name .FullName vbCrLf ' Check property values for the template. For Each CurrProp In _ ' Some...
Listing Removing Specific Highlights from a Word Document
Public Sub RemoveHighlight ' Holds the current pane. Dim CurrPane As Pane ' Locate the window based on the expected name. Dim LocateWindow As Window ' The current window. Dim WinString As String ' The window caption. For Each LocateWindow In Application.Windows If Left LocateWindow.Caption, 15 _ WordObjects.doc Then WinString LocateWindow.Caption End If ' Make sure you can access the pane properly. Application.Windows WinString .View _ wdNormalView ' Access the test document pane. Set CurrPane...
Listing Importing XML from Disk
' Obtain the XML from disk using the Xmllmport. ThisWorkbook.Xmllmport ExcelXML2.XML, _ The Xmllmport method requires three arguments. The first is the name of the file that you want to import. The second indicates whether you want to overwrite any existing data. Finally, you must supply a location to hold the data. When you run this program, you see an Error in Schema dialog box. The data exported with the example doesn't include an XSD reference. Excel doesn't know how to interpret the data...
Listing Adding a New Toolbar
Public Sub AddToolbar ' Add the toolbar. Dim MyToolbar As CommandBar Set MyToolbar _ Application.CommandBars.Add My Toolbar ' Add a control to the toolbar. Dim SayHello As CommandBarButton Set SayHello _ ' Configure the control. With SayHello .DescriptionText This button displays a message. .OnAction DoSayHello .TooltipText This button displays a message. .Visible True .Style msoButtonlconAndCaption .Faceld 59 End With ' Make the toolbar visible. MyToolbar.Visible True End Sub The entire...
Avoiding misuse of the GoTo statement
Many programmers misused the GoTo statement so severely in the past that most books tell you not to use it at all. Misuse of the GoTo statement leads to buggy code that is hard to read. In addition, GoTo statements can hide poor program design. However, the GoTo statement can also accomplish useful work, so the goal is to avoid misuse of the GoTo statement and concentrate on useful tasks. Here are some ways to avoid misusing the GoTo statement Loops Never use a GoTo statement as a loop...
Using the Do UntiLLoop statement
The Do Until Loop statement continues processing information until the expression is false. You can view the Do While Loop statement as a loop that continues while a task is incomplete. The Do Until Loop statement continues until the task is finished. The subtle difference between the two statements points out something interesting They rely on your perspective of the task to complete. These two statement types are completely interchangeable. The big difference is how you define the expression...
Performing calculations
One of the most common uses of special applications is to perform complex calculations. You can create many types of equations by using any of the Microsoft Office products. Sometimes, however, you need to change the data before you can use it or perform the calculation differently depending on the value of one or more inputs. Whenever a calculation becomes too complicated for a simple equation, use VBA to simplify things by solving the calculation problem using small steps rather than one big...