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