Configuring Macro Security Settings
With macro viruses becoming an increasing threat, working with VBA projects can be difficult. This is true even if you just want to run your own VBA projects, because Office 2007 disables macros in documents that aren't in a trusted location. You have three ways to work around this problem Store your macro-enabled documents in a trusted location. Office allows those documents' macros to run, while disabling macros in every other document. Enable all macros in all documents. This ensures that...
Customizing an Existing
If you have just a few macros that require an interface, building a new tab is probably overkill. Instead, you can add controls for those macros to one of the application's built-in tabs. To work with a built-in tab, you again use the < tab> element, but the syntax is slightly different. Here's a simplified version < tab idMso ID visible true false > ID The unique string identifier for the built-in tab. The value of the idMso attribute takes one of the following forms TabName This is...
Pausing a Procedure
Pausing a procedure in midstream lets you see certain elements such as the current values of variables and properties. It also lets you execute program code one statement at a time so you can monitor a procedure's flow. When you pause a procedure, VBA enters break mode, which means it displays the code window, highlights the current statement (the one that VBA will execute next) in yellow, and displays a yellow arrow in the margin indicator bar that points to the current statement. See the done...
Logging Off an Outlook Session
When you've completed your labors in an Outlook session, you can log off by running the NameSpace object's Logoff method NameSpace.Logoff NameSpace The NameSpace object. Listing 11.11 shows a procedure that logs on to a MAPI session, runs through the items in the default Inbox folder, and records the SenderName, SenderEmailAddress, Subject, Size, ReceivedTime, and the first 100 characters of the Body onto a worksheet. Listing 11.11 A Procedure That Reads Inbox Data into a Worksheet Dim ol As...
M
Macro Name list, 8 Macro Options dialog box, 11 macro-enabled documents templates, creating, 265-267 shortcuts to, 142-143 CloseAllOpenDocuments, 121-122 procedures definition of, xviii, 19 Excel macros Personal Macro Workbook, 7 recording, 7-8 shortcut keys, 10-11 LaunchInsertHyperlink Dialog, 289 Macro Name list, 8 macro statements, xviii naming, 20 pausing, 141 Quick Access toolbar Excel macros, 7-8 overview, 3 Word macros, 5-7 running, 21-22 at specific times, 144 when user selects Repeat...
Saving a Document
The worst nightmare of any Word user is a power failure or glitch that shuts down Word or even the computer itself while you have one or more documents open with unsaved changes. I know people who have lost hours of work when this has happened. We tell ourselves to save more often, but it's easy to forget in the heat of battle. Even Word's AutoRecover feature doesn't always work as advertised, so it can't be relied upon. Fortunately, VBA proves very useful in solving this problem because it's...
Listing Editing Recordset Data
Dim rs As ADODB.Recordset Dim strCriteria As String Set rs .Source Employees .ActiveConnection Northwind .CursorType adOpenKeyset .LockType adLockPessimistic .Open End With strCriteria Job Title 'Sales Representative1 rs.Find strCriteria 1 Loop to find other records that meet the criteria rs(Job Title) Account Manager rs.Update MsgBox rs(First Name) & & rs(Last Name) & , & rs(Job Title) 1 Search again, but skip a row rs.Find strCriteria, 1 After opening the Employees table, the Find...
Basic Strategy for Debugging
Debugging, like most computer skills, involves no great secrets. In fact, all debugging is usually a matter of taking a good, hard, dispassionate look at your code. Although there are no set-in-stone techniques for solving programming problems, you can formulate a basic strategy that will get you started. When a problem occurs, the first thing you need to determine is what kind of error you're dealing with. There are four basic types syntax errors, compile errors, runtime errors, and logic...
Creating Custom VBA Dialog Boxes
VBA procedures are only as useful as they are convenient. There isn't much point in creating a procedure that saves you (or your users) a few keystrokes if you (or they) have to expend a lot of time and energy hunting down a routine. Shortcut keys are true time-savers, but some applications (such as Excel) have only a limited supply to dole out (and our brains can memorize only so many Ctrl+key combinations). Instead, you need to give some thought to the type of user interface you want to...
VBA Functions
Although I discussed quite a few VBA functions in this book, my coverage was by no means exhaustive. VBA boasts more than 160 built-in functions that cover data conversion, dates and times, math, strings, and much more. This appendix presents a categorical list of VBA functions and the arguments they use (required arguments are shown in bold type). You can get full explanations and examples for all the functions in the Functions section of the VBA Help file. An expression converted to a Boolean...
Basic Error Trapping Strategy
For many programmers, adding error-trapping code to procedures can usually be found near the bottoms of their to-do lists (probably just before adding comments ). Error-trapping code isn't even remotely glamorous, and the optimistic (some would say foolhardy) programmer assumes it will never be needed. That's a shame, because setting up a bare-bones error trap takes very little time. Even a more sophisticated trap can be reused in other procedures, so you really have only a onetime expenditure...
Creating and Running Recorded Macros
This book is about programming, but for certain tasks you may not need to do any programming at all. That's because VBA in Word and Excel lets you create a macro automatically by recording a set of steps you perform in the program. (Unfortunately, the other programs in the 2007 Office suite don't have macro recording capabilities.) This is by far the easiest way to create a macro. With this method, you start the macro recording feature and then run through the operations you want to auto-mate...
This Books Special Features
VBA for the 2007 Microsoft Office System is designed to give you the information you need without making you wade through ponderous explanations and interminable technical background. To make your life easier, this book includes various features and conventions that help you get the most out of the book and VBA itself. Things you type Whenever I suggest that you type something, what you type appears in a bold font. Commands I use the following style for Ribbon commands View, Macros. This means...
Listing A First Attempt at Calculating the Pre Tax Cost
Function PreTaxCost(totalCost As Currency, taxRate As Single) As Currency PreTaxCost totalCost 1 + taxRate End Function Figure 4.3 shows an Excel worksheet that uses this function. The value in cell B4 is passed to the totalCost argument and the value in cell B1 is passed to the taxRate argument. A function that attempts to calculate the pre-tax cost of an item. A function that attempts to calculate the pre-tax cost of an item. * Without Precedence Control With Pre As you can see, the result is...
Listing A Procedure That Stores the Names of the Worksheets in a Dynamic Array
Dim sheetNames() As String Dim totalSheets As Integer Dim sheet As Worksheet Dim i As Integer Dim strMessage As String ' Store the total number of worksheets 1 that are in the current workbook totalSheets ActiveWorkbook.Worksheets.Count 1 Loop through the worksheets to store the names in the array sheetNames(i - 1) ActiveWorkbook.Worksheets(i).Name Next 'i ' Loop through the array to add the names to a string strMessage Here are the worksheet names & vbCrLf For i 0 To totalSheets - 1...
Creating a New
If you're building an interface for a number of macros and user forms, you can make it easy for the user to find the custom tools by placing them within a new tab in the Ribbon. This gives you plenty of room to add whatever groups and controls you need to build your interface. In RibbonX, you create a tab by using the < tab> element. Here's the basic syntax < tab id ID label Label InsertAfterMso AfterID InsertBeforeMso BeforelD keytip KeyTip> < tab> ID A unique string identifier for...
Creating a New Workbook
If you need to create a new workbook, use the Workbooks collection's Add method Workbooks.Add(Template) Template is an optional argument that determines how the workbook is created. If Template is a string specifying an Excel file, VBA uses the file as a template for the new workbook. You also can specify one of the following constants xlWBATWorksheet Creates a workbook with a single worksheet. xlWBATChart Creates a workbook with a single chart sheet. Here's a sample statement that uses the Add...
Listing A Procedure That Lists the First and Second Level Folders in the Outlook Namespace
1 Run through the first-level folders For Each folder In ns.Folders Debug.Print folder.Name 1 Run through the second-level folders, if any For Each subfolder In folder.Folders Debug.Print & subfolder.Name Next 'subfolder End If Next 'folder Set ns Nothing End Sub For the Outlook procedures in this chapter, I've put everything into a text file named Chapter11.txt, which you'll find on my website To use the code,create a module in Outlook's Visual Basic Editor,copy the code from...
Listing Continued
.Fill.UserPicture strImagePath .Width 542 .Left 90 ' Configure the picture animation and shadow With .AnimationSettings .Animate True .AdvanceMode ppAdvanceOnTime .AdvanceTime 0 .EntryEffect ppEffectFade End With With .Shadow .ForeColor.RGB RGB(0, 0, 0) .OffsetX 10 .OffsetY 10 .Visible True End With End With With slideText.TextFrame.TextRange Select Case i Case 1 .Text Place two balls in your dominant hand, & _ one in front of the other. & Chr(13) & _ Hold the third ball in your other...
E
Complete Word, 30 Copy, 245 Delete, 245 List Constants, 28 List Properties Methods, 28 Parameter Info, 29 Select All, 243 Edit Watch command (Debug menu), 346 range text (Word), 126 records, 203-205 watch expressions, 346 EditingARecord procedure, 204 editors Custom UI Editor, 270 Visual Basic Editor creating modules, 17-18 displaying, 16 opening modules, checkBox, 281 comboBox, 288 dialogBoxLauncher, 289 dropdown, 284 gallery, 285-287 group, 274-275 menu, 278 Relationship, 265, 269 ribbon, 271...
The Choose Function
In the previous section, I showed you how the IIf function is an efficient replacement for If Then Else when all you need to do is assign a value to a variable based on the results of the test. Suppose now you have a similar situation with the Select Case structure. In other words, you want to test a number of possible values and assign the result to a variable. For example, you saw in Chapter 4 that VBA's Weekday function returns the current day of the week as a number. Here's a procedure...
Hiding the BuiltIn Ribbon
Most of the time, your RibbonX customizations will augment the Office program's built-in Ribbon interface by adding one or more custom tabs or by adding new groups and controls to existing tabs. Occasionally, however, your VBA application might require that you hide the built-in Ribbon and display only your custom interface. You'll see in the next few sections that you can hide individual tabs (as well as groups and controls within a tab), but you don't have to hide all the program's tabs...
Listing A Macro That Displays the Lengths of Sentences in the Active Document
Sub DisplaySentenceLengths() Dim s As Range Dim maxWords As Integer Dim i As Integer Dim sentenceLengths() As Integer Dim str As String ' Run through all the sentences to find the longest maxWords CountWord(s) End If Next s 1 Redimension the array of sentence lengths ' Run through the sentences again to count 1 the number of sentences for each length ' Get the word count for the sentence 1 If it's not empty, add it to the array sentenceLengths(j - 1) sentenceLengths(j - 1) + 1 End If Next 's '...
Step Rename and Open the Document
Your customization work is done, so close the container and then rename the file to remove the .zip extension. The next time you open the document, the application parses the custom XML file and displays the new Ribbon elements. Figure 13.2 shows Word with the new tab, group, and button specified by the custom XML file used as an example in step 2 (Create a Text File and Add the Custom XML Markup). Clicking the button runs the associated macro, which displays the dialog box. Microsoft Word 2007...
Logical Operators
You use the logical operators to combine or modify true false expressions. Table 4.3 summarizes VBAs logical operators. I provide more detail about each operator later in this chapter (see Working with Logical Expressions). True if both Exprl and Expr2 are true False otherwise. True if at least one of Exprl and Expr2 are true False otherwise. False if both Exprl and Expr2 are true or if both Exprl and Expr2 are false True otherwise. True if Expr is false False if Expr is true.
Processing the Form Results
When the user clicks OK or Cancel (or any other control that includes the Unload Me statement in its Click event handler), you usually need to examine the form results and process them in some way. Obviously, how you proceed depends on whether the user has clicked OK or Cancel because this almost always determines whether the other form selections should be accepted or ignored. If OK is clicked, the Click event handler for that button can process the results. In other words, it can read the...
Working with Modules
You've seen so far that modules are where most of the VBA action takes place. True, you've also seen that much VBA work happens within user form windows and the Properties window, but modules are really the heart of VBA. Given that, it will help to have a few module manipulation techniques under your belt. To that end, the next four sections show you how to rename, export, import, and remove modules. When you insert a new module, VBA gives it an uninspiring name such as Modulel. That's fine if...
Using the Folders Property
Alternatively, you can use the NameSpace object's Folders property to return a Folders object that represents all the MAPIFolder objects in the PST file. To reference a specific folder, use Folders(Index), where Index is one of the following An integer value with the first folder being 1, the second folder being 2, and so on. The name of the folder in quotation marks. The NameSpace object has only one folder known as the root which is usually called Personal Folders. Therefore, the following...
Running a Recorded Macro
In almost all cases, you record a macro so that you can run it again in the future, probably a number of times depending on the tasks you recorded. (The exception would be, as I mentioned earlier, if you record a macro to use as a starting point for writing your own code.) So after you record a macro, how do you get it to run again There are three main methods you can use the Macro Name list, a shortcut key, and a Quick Access toolbar button. The Macro Name list is a listing of all your...
Specifying a Worksheet Object
If you need to deal with a worksheet in some way, or if your code needs to specify an object contained in a specific worksheet (such as a range of cells), you need to tell Excel which worksheet you want to use. To do this, use the Worksheets object. Worksheets is the collection of all the worksheets in a particular workbook. To specify a worksheet, either use its index number (where 1 represents the first worksheet tab, 2 the second worksheet tab, and so on) or enclose the worksheet name in...
Taking Advantage of Intelli Sense
VBA's IntelliSense feature is like a mini version of the VBA Help system. It offers you assistance with VBA syntax, either on the fly or on demand. You should find this an incredibly useful tool because, as you'll see as you work through this book, VBA contains dozens of statements and functions and VBA-enabled programs offer hundreds of objects to work with. Few people are capable of committing all this to memory, and it's a pain to be constantly looking up the correct syntax. IntelliSense...
Creating a Menu
If you have a small collection of related macros, you can save space in your custom interface by bundling the buttons for all those macros into a menu. You create the menu control by using the < menu> element label value imageMso value itemSize normal large InsertAfterMso value InsertBeforeMso value onAction value enabled true false visible true false screentip value supertip value keytip value> Note that you use itemSize instead of size, and that this attribute affects every item in the...
Creating a Command Macro
As I mentioned at the start of this chapter, recording macros is limiting because there are plenty of macro features that you can't access with mouse or keyboard actions or by selecting menu options. In Excel, for example, VBA has a couple dozen information macro functions that return data about cells, worksheets, workspaces, and more. Also, the VBA control functions enable you to add true programming structures such as looping, branching, and decision-making (see Chapter 6, Controlling Your...
Programming Excel
Excel's Application Object 139 Manipulating Workbook Objects 146 Dealing with Worksheet Objects 150 Working with Range Objects 153 If you're using VBA in Excel, most of your procedures will eventually do something to the Excel environment. They might open a workbook, rename a worksheet, select a cell or range, enter a formula, or even set some of Excel's options. Therefore, knowing how VBA interacts with Excel is crucial if you ever hope to write useful routines. This chapter looks closely at...
Variable Data Types
The data type of a variable determines the kind of data the variable can hold. You specify a data type by including the As keyword in a Dim statement. Here is the general syntax Dim variableName As DataType variableName is the name of the variable and DataType is one of the data types. Here's a rundown of the most useful VBA data types String This type holds strings, which are simple text values. Here's a sample declaration and assignment statement (note the use of quotation marks in the...
Programming Access Databases
In the past few chapters you've learned about the objects, properties, and methods associated with Word, Excel, and PowerPoint. You've seen that it's possible to manipulate these objects to automate routine tasks and gain an unprecedented amount of control over these programs. In this chapter, you'll see that using VBA with Access is quite a bit different because you won't learn anything about Access objects. Yes, Access does have an Application object and there's a whole hierarchy of objects...
Table VBAs Date and Time Functions
CDate(expression) Converts expression into a Date value. Date The current system date, as a Variant. Date () The current system date, as a String. DateAdd(interval, number, date) A Date value derived by adding the specified number of intervals (days, months, years, and so on) to date. A numeric value that represents the number of intervals (days, months, years, and so on) between date1 and date2. DatePart(interval,date) A numeric value that corresponds to the part of date specified by interval...
Using DoLoop Structures
What do you do when you need to loop but you don't know in advance how many times to repeat the loop This could happen if, for example, you want to loop only until a certain condition is met, such as encountering a blank cell in an Excel worksheet. The solution is to use a Do Loop. The Do Loop has four different syntaxes Checks condition before entering the loop. Executes the statements only while condition is True. Checks condition after running through the loop once. Executes the statements...
What Is an Object
The dictionary definition of an object is anything perceptible by one or more of the senses, especially something that can be seen and felt. Now, of course, you can't feel anything in an Office application, but you can see all kinds of things. To VBA, an object is anything in an application that you can see and manipulate in some way. For example, a paragraph in a Word document is something you can see, and you can manipulate it by inserting text, changing the style, setting the font, and so...
Setting the Tab Order
As you know, you can navigate a form by pressing the Tab key. The order in which the controls are selected is called the tab order. VBA sets the tab order according to the order you create the controls on the form. You'll often find that this order isn't what you want to end up with, so the Visual Basic Editor lets you control the tab order yourself. The following procedure shows you how it's done 1. Select View, Tab Order. (You can also right-click an empty part of the form and click Tab Order...
VBAs Financial Functions
VBA has quite a few financial functions that offer you powerful tools for building applications that manage both business and personal finances. You can use these functions to calculate such things as the monthly payment for a loan, the future value of an annuity, or the yearly depreciation of an asset. Although VBA has a baker's dozen financial functions that use many different arguments, the following list covers the arguments you'll use most frequently rate The fixed rate of interest over...
Creating Custom Tabs
The Ribbon in each Office application is divided into several tabs, and those tabs contain the groups and controls that expose the application's functionality. With RibbonX, you can create your own custom tabs or you can customize any of the application's existing tabs. Either way, your markup must appear between the < tabs> and < tabs> elements, so your tab-based RibbonX markup always begins like this < customUI < ribbon> < tabs> < tabs> < ribbon> < customUI>
More Complexity Means More Power
As I mentioned earlier, and as you saw in the preceding steps, extending the Office 2007 Ribbon is a far more involved (some would say convoluted) process than the relatively simple and straightforward menu and toolbar customizations in earlier versions of Office. Fortunately, as you'll soon see, there is an upside to the more difficult Ribbon procedure. That added complexity also gives you added power. Your previous Office customizations were restricted to mere buttons that ran macros. With...
The Switch Function
Choose is a welcome addition to the VBA function library, but its use is limited because of two constraints You can use Choose only when the index argument is a number or a numeric expression. Choose can't handle logical expressions. To illustrate why the last point is important, consider the Select Case structure used earlier in this chapter to convert a test score into a letter grade LetterGrade ERROR Score greater than 100 End Select At first blush, this structure seems to satisfy the same...
Creating a Button
The most basic control and the control you'll use most often is the button which, when clicked, runs a macro that you specify label value imageMso value size normal large InsertAfterMso value InsertBeforeMso value onAction value enabled true false visible true false screentip value supertip value keytip value > < button id CustomButton1 imageMso HappyFace label Hello World size large screentip Hello World supertip Displays the canonical Hello World message. keytip M > Figure 13.6 shows...
Working with String Expressions
A string expression is an expression that returns a value that has a String data type. String expressions can use as operands string literals (one or more characters enclosed in double quotation marks), variables declared as String, or any of VBAs built-in functions that return a String value. Table 4.7 summarizes most of the VBA functions that deal with strings. The ANSI character code of the first letter in string. The character, as a Variant, that corresponds to the ANSI code given by...
Trappable VBA Errors
They're all described in Table 15.1. Sub, Function, or Property not defined This array is fixed or temporarily locked ActiveX component not correctly registered ActiveX component did not run correctly Unable to unload within this context The specified file is out of date. This program requires a later version. The specified object can't be used as an owner form for Show Property Set can't be executed at runtime Property Set can't be used with a read-only...
Understanding Ribbon Extensibility
If you've used previous versions of Office to customize menus and toolbars, you'll no doubt be disappointed to learn that all your hard-won customization knowledge must now be discarded. Most unfortunately, Microsoft offers no direct method for customizing the Ribbon and its various tabs, groups, and buttons. Note that this does not mean that the Ribbon isn't customizable. It certainly is, but it's just that now the process is much more involved and requires quite a few more steps. If you just...
R
Range method, 125, 153-154, 171, 175 names, 162 inserting data into ranges, 162-163 resizing ranges, Columns method, 156 returning with Offset method, 157-158 returning with Range method, 155-156 selecting ranges, 158-161 in Word, 153 Bold property, 126 Case property, 126 Delete method, 128-129 deleting text, 128-129 Font property, 126 formatting text, 126 InsertAfter method, 127 InsertParagraphsAnd Text procedure, 127-128 Italic property, 126 reading and changing range text, 126 returning with...
Listing A Procedure That Saves Every Open Workbook
1 Run through all the open workbooks ' Has the workbook been saved before If wb.Path < > Then ' If not, display the Save As dialog box ' to get the workbook's path & filename newFilename .GetSaveAsFilename( _ FileFilter Microsoft Office & Excel Workbook & _ (*.xlsx), *.xlsx) ' If not, save the workbook using the ' specified path and filename wb.SaveAs fileName newFilename End If End If Next 'wb End Sub The main loop in the SaveAll macro uses the Workbooks collection and a For Each...
Contents
What Is a What Does VBA Have to Do with What You Should Know Before Reading This What's in the This Book's Special 1 Creating and Running Recorded Recording a VBA Recording a Word Recording an Excel Running a Recorded Using the Macro Name Assigning Shortcut Keys to Recorded Word Assigning Shortcut Keys to Recorded Excel Creating a Quick Access Toolbar Button for a Recorded From 2 Writing Your Own Activating the Ribbon's Developer Displaying the Visual Basic Touring the Visual Basic Creating a...
Accessing an Applications Builtin Dialog Boxes
Many VBA methods are known as dialog box equivalents because they let you select the same options that are available in an application's built-in dialog boxes. Using dialog box equivalents works fine if your procedure knows which options to select, but there are times when you might want the user to specify some of the dialog box options. For example, if your procedure prints a document (using the Application object's Printout method), you might need to know how many copies the user wants or...
Creating a Gallery
The more choices you have to offer, the less attractive becomes the drop-down list control because the list just gets too long to navigate efficiently. A better solution for a large number of options is a gallery control, which displays multiple items in the number of rows and columns you specify. To create a gallery in RibbonX, begin with the < gallery> element label value imageMso value size normal large InsertAfterMso value InsertBeforeMso value onAction value enabled true false visible...
Creating a New Document
If you need to create a new document, use the Documents collection's Add method Documents.Add Template , NewTemplate , DocumentType , Visible Template This optional argument specifies the template file to use as the basis for the new document. Enter a string that spells out the path and name of the .DOT file. If you omit this argument, Word creates the new document based on the Normal template. NewTemplate If you set this optional argument to True, Word creates a new template file. DocumentType...
Specifying the Number of Sheets in a New Workbook
When you create a new workbook in Excel, the file comes with three worksheets by default. Most people just use one worksheet, but leave the other two sheets in the workbook, just in case. If you use several sheets in many or all of your workbooks, you should consider increasing the default number of sheets that Excel includes in new workbooks. Follow these steps 1. Choose Office, Excel Options to open the Excel Options dialog box. 3. Use the Include This Many Sheets spin box to set the number...
Listing Using the Find Method
Sub SearchRecordsWithFind Dim rs As ADODB.Recordset Dim strCriteria As String Set rs .Source Employees .ActiveConnection Northwind .CursorType adOpenKeyset .Open End With strCriteria City 'Seattle' rs.Find strCriteria ' Loop to find other records that meet the criteria Do While Not rs.EOF MsgBox rs First Name amp amp rs Last Name 1 Search again, but skip a row rs.Find strCriteria, 1 After opening the Employees table as the recordset, this code uses the strCriteria variable to hold the criteria...
Recording a VBA Macro
As I mentioned earlier, only Word and Excel come with the Record Macro command, which is a shame. In Office 2003, you could record macros in PowerPoint, too. However, these two programs are the most suited to recording macros, so it's not all that surprising that Microsoft has restricted the Office macro recording capabilities. The next two sections show you how to record a macro in Word and Excel. Before getting started, make sure that Word is set up so that it's ready to record. If you want...
Saving Application Settings in the Registry
In a VBA procedure, you use variables to store values you need to use while you're running the procedure. When the procedure finishes, the values of those variables are wiped from memory. What do you do if you have values that you want to preserve from one VBA session to another You could store the values somewhere in the document, but this isn't a great idea because those values could be easily changed or even deleted. A better idea is to use the Registry. Windows uses the Registry to store...
Monitoring Procedure Values
Many runtime and logic errors are the result of or, in some cases, can result in variables or properties assuming unexpected values. If your procedure uses or changes these elements in several places, you need to enter break mode and monitor the values of these elements to see where things go awry. The Visual Basic Editor offers a number of methods for monitoring values, and I discuss them in the next few sections. Most of the values you'll want to monitor will be variables. Although watch...
Listing A Procedure That Uses Select Case to Convert a Raw Test Score into a Letter Grade
Function LetterGrade rawScore As Integer As String Select Case rawScore Case Is lt 0 LetterGrade ERROR Score less than 0 Case Is lt 60 LetterGrade F Case Is lt 70 LetterGrade D Case Is lt 80 LetterGrade B Case Is lt 100 LetterGrade A Case Else LetterGrade ERROR Score greater than 100 The rawScore argument is an integer value between 0 and 100. The Select Case structure first checks to see whether rawScore is negative and, if so, the function returns an error message. The next Case statement...
Step One Create a Reference
This may sound strange, but the ability to program a database with ADO is not built into Access VBA by default As I mentioned at the top of the chapter, Access VBA is set up to program forms and reports among other things it just can't work with the data that's in those forms and reports, not to mention the tables where the data actually resides. It's weird, I know. So why did Microsoft set things up this way In simplest terms, there are actually several different ways to program data, and...
Accessing the File System Through VBA
If your applications need to work with the file system, VBA boasts quite a few features that make it easy. These features include a number of statements that return information about files and folders, as well as a number of functions with which you can manipulate files and folders. There are also powerful functions that give you direct access to files. This section examines all VBAs file-related statements and functions. Returning File and Folder Information If you need information about the...
Defining a Range Name
In Excel VBA, range names are Name objects. To define them, you use the Add method for the Names collection which is usually the collection of defined names in a workbook . Here is an abbreviated syntax for the Names collection's Add method this method has eleven arguments see the VBA Reference in the Help system Text The text you want to use as the range name. RefersTo The item to which you want the name to refer. You can enter a constant, a formula as text such as Sales-Expenses , or a...
Tab Strips and Multi Page Controls
I mentioned earlier that you can use frames to group related controls visually and help the user make sense of the form. However, there are two situations in which a frame falls down on the job. The first situation is when you need the form to show multiple sets of the same or similar data. For example, suppose you have a form that shows values for sales and expense categories. You might want the form to be capable of showing separate data for various company divisions. One solution would be to...
