Determining User Needs
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...
Naming objects
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...
The FileNameOnly function
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...
Using the Editor Format tab
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...
Looping through a selected range efficiently
A common task is to create a macro that evaluates each cell in a range and performs an operation if the cell meets a certain criterion. The procedure that follows is an example of such a macro. The ColorNegative procedure sets the cell's background color to red for cells that contain a negative value. For non-negative value cells, it sets the background color to none. Note This example is for educational purposes only. Using Excel's conditional formatting is a much better approach. Sub...
Referencing UserForm Controls
When working with controls on a UserForm, the VBA code is usually contained in the code window for the UserForm. You can also refer to UserForm controls from a general VBA module. To do so, you need to qualify the reference to the control by specifying the UserForm name. For example, consider the following procedure, which is located in a VBA module. It simply displays the UserForm named userForml. Assume that UserForml contains a text box named TextBoxi , and you want to provide a default...
How the MyMsgBox function works
Notice the use of four Public variables. The first three Promptl, Buttonsl, and Titlel represent the arguments that are passed to the function. The other variable UserClick represents the values returned by the function. The userForm_Initialize procedure needs a way to get this information and send it back to the function, and using Public variables is the only way to accomplish that. The UserForm shown in Figure 15-12 contains four Image controls one for each of the four possible icons , three...
Duplicating rows a variable number of times
The example in this section demonstrates how to use VBA to create duplicates of a row. Figure 11-9 shows a worksheet for an office raffle. Column A contains the name, and column B contains the number of tickets purchased by each person. Column C contains a random number generated by the RAND function . The winner will be determined by sorting the data based on column 3 the highest random number wins . Figure 11-9 The goal is to duplicate rows based on the value in column B. Figure 11-9 The goal...
Dissecting a file
In this section, I describe the various parts within a typical Excel XLSM macro-enabled workbook file. The workbook, named sample. xlsm, is shown in Figure 4-4. It has one worksheet, one chart sheet, and a simple VBA macro. The worksheet contains a table, a button from the Forms controls , a SmartArt diagram, and a photo of a flower. CD The sample. xlsm workbook is available on the companion CD-ROM. To view the innards of an Excel 2007 file, you need to open an Explorer window and add a ZIP...
Determining default printer information
The example in this section uses a Windows API function to return information about the active printer. The information is contained in a single text string. The example parses the string and displays the information in a more readable format. Private Declare Function GetProfileStringA Lib kernel32 ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As _ String, ByVal nSize As Long As Long Dim strLPT As String 255 Dim Result As String Call...
Documenting the development effort
Putting a spreadsheet application together is one thing. Making it understandable for other people is another. As with traditional programming, it's important that you thoroughly document your work. Such documentation helps you if you need to go back to it and you will , and it helps anyone else whom you might pass it on to. Tip You might want to consider a couple of things when you document your project. For example, if you were hired to develop an Excel application, you might not want to...
How Excel Handles Custom Dialog Boxes
A custom dialog box is created on a UserForm, and you access UserForms in the Visual Basic Editor VBE . Following is the typical sequence that you will follow when you create a UserForm 1. Insert a new UserForm into your workbook's VB Project. 2. Write a procedure that will display the UserForm. This procedure will be located in a VBA module not in the code module for the UserForm . 3. Add controls to the UserForm. 4. Adjust some of the properties of the controls that you added. 5. Write event...
The Offset property
The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods that I discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows obj ect.Offset rowOffset, columnOffset The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive down or to the right , negative up or to the left , or...
Thinking in Terms of Objects
When you are developing applications with Excel especially when you are dabbling with Visual Basic for Applications - VBA , it's helpful to think in terms of objects, or Excel elements that you can manipulate manually or via a macro. Here are some examples of Excel objects A range or a table in a worksheet A ListBox control on a UserForm a custom dialog box A chart embedded in a worksheet A particular data point in a chart You may notice that an object hierarchy exists here The Excel object...
Randomizing a range
The RangeRandomize function, which follows, accepts a range argument and returns an array that consists of the input range - in random order Dim V As Variant, ValArray As Variant Dim Temp1 As Variant, Temp2 As Variant Dim RCount As Integer, CCount As Integer ' Return an error if rng is too large CellCount rng.Count If CellCount gt 1000 Then RangeRandomize CVErr xlErrNA Exit Function ReDim ValArray 1 To 2, 1 To CellCount Fill ValArray with random numbers and values from rng For i 1 To CellCount...
Writing the Sort procedure
It was time to sort the SheetNames array. One option was to insert the sorting code in the SortSheets procedure, but I thought a better approach was to write a general-purpose sorting procedure that I could reuse with other projects sorting arrays is a common operation . You might be a bit daunted by the thought of writing a sorting procedure. The good news is that it's relatively easy to find commonly used routines that you can use or adapt. The Internet, of course, is a great source for such...
Overview Aco
Excel's pivot table feature is, arguably, its most innovative and powerful feature. Pivot tables first appeared in Excel 5, and the feature remains unique to Excel. No other spreadsheet program has anything that comes close to it. This chapter is not an introduction to pivot tables. I assume that you're familiar with this feature and its terminology and that you know how to create and modify pivot tables manually. In this chapter, I cover the following topics What you need to know to create...
Using a ListBox to activate a sheet
The example in this section is just as useful as it is instructive. This example uses a multicolumn ListBox to display a list of sheets within the active workbook. The columns represent The type of sheet worksheet, chart, or Excel 5 95 dialog sheet The number of nonempty cells in the sheet Whether the sheet is visible Figure 14-17 shows an example of the dialog box. Figure 14-17 This dialog box lets the user activate a sheet. Figure 14-17 This dialog box lets the user activate a sheet. The code...
IIIII i
Figure 18-7 Applying a shadow to a chart. With MyChart.ChartArea.Format.Shadow .Visible msoTrue .Blur 10 .Transparency 0.4 .OffsetX 6 .OffsetY 6 End With The example that follows adds a subtle shadow to the plot area of the chart With MyChart.PlotArea.Format.Shadow .Visible msoTrue .Blur 3 .Transparency 0.6 .OffsetX 1 .OffsetY 1 End With If an object has no fill, applying a shadow to the object has no visible effect. For example, a chart's title usually has a transparent background no fill...
Lotus
Envious of VisiCalc's success, a small group of computer freaks at a start-up company in Cambridge, Massachusetts, refined the spreadsheet concept. Headed by Mitch Kapor and Jonathan Sachs, the company designed a new product and launched the software industry's first full-fledged marketing blitz. I remember seeing a large display ad for 1-2-3 in The Wall Street Journal. It was the first time that I'd ever seen software advertised in a general interest publication. Released in January 1983,...
Removing a VBA module
If you need to remove a VBA module or a class module from a project, select the module's name in the Project Explorer window and choose File Remove xxx where xxx is the name of the module . Or you can right-click the module's name and choose Remove xxx from the shortcut menu. You are asked whether you want to export the module before removing it. See the next section for details. You cannot remove code modules associated with the workbook the ThisWorkbook code module or with a sheet for...
Methods of a Comment object
Table 7-2 shows the methods that you can use with a Comment object. Again, these methods perform common operations that you may have performed manually with a comment at some point but you probably never thought of these operations as methods. Table 7-2 METHODS OF A COMMENT OBJECT Table 7-2 METHODS OF A COMMENT OBJECT Returns a Comment object that represents the next comment in the worksheet. Returns a Comment object that represents the previous comment in the worksheet. Returns or sets the...
Determining disk drive information
VBA doesn't have a way to directly get information about disk drives. But with the assistance of three API functions, you can get just about all of the information you need. Figure 11-19 shows the output from a VBA procedure that identifies all connected drives, determines the drive type, and calculates total space, used space, and free space. In the example shown, the system has six drives connected. Blfrivr H 1 hu- ni UjB Tl-. BLIW UTII- Blin-Ul l. Figure 11-19 Using Windows API functions to...
Counting cells between two values
The following function, named CountBetween , returns the number of values in a range first argument that fall between values represented by the second and third arguments Function CountBetween InRange, num1, num2 As Long ' Counts number of values between num1 and num2 With Application.WorksheetFunction If num1 lt num2 Then CountBetween .CountIf InRange, gt amp .CountIf InRange, gt amp num2 CountBetween .CountIf InRange, gt amp .CountIf InRange, gt amp num1 Note that this function uses Excel's...
Charts
Excel is perhaps the most commonly used application in the world for creating charts. As I mention earlier in this chapter, you can store charts on a chart sheet or float them on a worksheet. NEW Excel 2007 still hasn't introduced any new chart types, but charts are easier to create, and they most definitely look much better. Figure 2-22 shows an Excel 2007 chart that uses some of the new formatting options. w w m t i im m V gt hhjhwh WW who ust in Figure 2-22 Excel 2007 charts have improved in...
A multifunctional function
This example describes a technique that may be helpful in some situations making a single worksheet function act like multiple functions. For example, the following VBA listing is for a custom function called StatFunction . It takes two arguments the range rng and the operation op . Depending on the value of op , the function returns a value computed using any of the following worksheet functions AVERAGE, COUNT, MAX, MEDIAN, MIN, MODE, STDEV, SUM, or VAR. For example, you can use this function...
Copying a noncontiguous range
If you've ever attempted to copy a noncontiguous range selection, you discovered that Excel doesn't support such an operation. Attempting to do so brings up an error message That command cannot be used on multiple selections . When you encounter a limitation in Excel, you can often circumvent it by creating a macro. The example is this section is a VBA procedure that allows you to copy a multiple selection to another location. Sub CopyMultipleSelection Dim SelAreas As Range Dim PasteRange As...
Creating a user Help system
With regard to user documentation, you basically have two options paper-based documentation or electronic documentation. Providing electronic help is standard fare in Windows applications. Fortunately, your Excel applications can also provide help - even contextsensitive help. Developing help text takes quite a bit of additional effort, but for a large project, it may be worth it. Figure 6-6 shows an example of a custom Help system in compiled HTML format. ni rh l jiu1 irtnefevi l-Kta.' Urftfi...
Some Useful Application Properties
When you're working with Excel, only one workbook at a time can be active. And if the sheet is a worksheet, one cell is the active cell even if a multicell range is selected . VBA knows about active worksbooks, worksheets, and cells, and lets you refer to these active objects in a simplified manner. This is often useful because you won't always know the exact workbook, worksheet, or range that you want to operate on. VBA handles this by providing properties of the Application object. For...
Dealing with the Insert Function Dialog Box
Excel's Insert Function dialog box is a handy tool. When you are creating a worksheet formula, this tool lets you select a particular worksheet function from a list of functions see Figure 10-8 . These functions are grouped into various categories to make it easier to locate a particular function. The Insert Function dialog box also displays your custom worksheet functions, and the Function Arguments dialog box prompts you for a function's arguments. Tyj b lt J iri gt on of vtfu wartt Jo do...
Working with dates
You can use a string variable to store a date, but if you do, it's not a real date meaning you can't perform date calculations with it . Using the Date data type is a better way to work with dates. A variable defined as a date uses 8 bytes of storage and can hold dates ranging from January 1, 0100 to December 31, 9999. That's a span of nearly 10,000 years - more than enough for even the most aggressive financial forecast The Date data type is also useful for storing time-related data. In VBA,...
Displaying the date and time
If you understand the serial number system that Excel uses to store dates and times, you won't have any problems using dates and times in your VBA procedures. The DateAndTime procedure displays a message box with the current date and time, as depicted in Figure 11-14 . This example also displays a personalized message in the message box title bar. Figure 11-14 A message box displaying the date and time. Figure 11-14 A message box displaying the date and time. The procedure uses the Date...
A function with one argument
This section describes a function for sales managers who need to calculate the commissions earned by their sales forces. The calculations in this example are based on the following table Note that the commission rate is nonlinear and also depends on the month's total sales. Employees who sell more earn a higher commission rate. There are several ways to calculate commissions for various sales amounts entered into a worksheet. If you're not thinking too clearly, you might waste lots of time and...
Emulating Excels SUM Function
In this section, I present a custom function called MySum. Unlike the simplesum function listed in the previous section, the MySum function emulates Excel's SUM function almost perfectly. Before you look at the code for MySum, take a minute to think about the Excel SUM function. It is, in fact, very versatile. It can have as many as 255 arguments even missing arguments , and the arguments can be numerical values, cells, ranges, text representations of numbers, logical values, and even embedded...
A better way to write to a range
The example in the preceding section uses a For-Next loop to transfer the contents of an array to a worksheet range. In this section, I demonstrate a more efficient way to accomplish this. Start with the example that follows, which illustrates the most obvious but not the most efficient way to fill a range. This example uses a For-Next loop to insert its values in a range. ' Fill a range by looping through cells Dim CellsDown As Long, CellsAcross As Integer Dim CurrRow As Long, CurrCol As...
Comments
A comment is descriptive text embedded within your code and ignored by VBA. It's a good idea to use comments liberally to describe what you're doing because an instruction's purpose is not always obvious. You can use a complete line for your comment, or you can insert a comment after an instruction on the same line. A comment is indicated by an apostrophe. VBA ignores any text that follows an apostrophe -except when the apostrophe is contained within quotation marks - up until the end of the...
The Module VBA module
The Module1 VBA module contains the declarations, a simple procedure that kicks off the utility, and a procedure that handles the undo operation. DECLARATIONS IN THE MODULE1 VBA MODULE Following are the declarations at the top of the Module1 module Public Const APPNAME As String Text Tools Utility Public Const PROGRESSTHRESHOLD 2000 Public UserChoices 1 To 8 As Variant 'stores user's last choices Public UndoRange As Range ' For undoing Public UserSelection As Range 'For undoing I declare a...
Displaying the date when a file was saved or printed
An Excel workbook contains several built-in document properties, accessible from the BuiltinDocumentProperties property of the Workbook object. The following function returns the date and time that the workbook was last saved Application.Volatile LastSaved ThisWorkbook. _ BuiltinDocumentProperties Last Save Time End Function The following function is similar, but it returns the date and time when the workbook was last printed or previewed. If the workbook has never been printed or previewed,...
Planning an Application That Meets User Needs
After you determine the end users' needs, it's very tempting to jump right in and start fiddling around in Excel. Take it from someone who suffers from this problem Try to restrain yourself. Builders don't construct a house without a set of blueprints, and you shouldn't build a spreadsheet application without some type of plan. The formality of your plan depends on the scope of the project and your general style of working, but you should spend at least some time thinking about what you're...
Project requirements
Where to begin One way to get started is to list the requirements for your application. When you develop your application, you can check your list to ensure that you're covering all the bases. Here's the list of requirements that I compiled for this example application 1. It should sort the sheets that is, worksheets and chart sheets in the active workbook in ascending order of their names. 2. It should be easy to execute. 3. It should always be available. In other words, the user shouldn't...
Array Formulas
In Excel terminology, an array is a collection of cells or values that is operated on as a group. An array formula is a special type of formula that works with arrays. An array formula can produce a single result, or it can produce multiple results - with each result displayed in a separate cell. For example, when you multiply a 1 5 array by another 1 5 array, the result is a third 1 5 array. In other words, the result of this kind of operation occupies five cells each element in the first...
Using the FileDialog object to select a directory
If users of your application all use Excel 2002 or later, you might prefer to use a much simpler technique that uses the FileDialog object. The following procedure displays a dialog box see Figure 12-10 that allows the user to select a directory. The selected directory name or Canceled is then displayed by using the MsgBox function. Figure 12-10 Using the FileDialog object to select a directory. Figure 12-10 Using the FileDialog object to select a directory. Sub GetAFolder2 ' For Excel 2002 and...
Accommodating keyboard users
Many users prefer to navigate through a dialog box by using the keyboard The Tab and Shift Tab keystrokes cycle through the controls, and pressing a hot key an underlined letter operates the control. To make sure that your dialog box works properly for keyboard users, you must be mindful of two issues tab order and accelerator keys. The tab order determines the sequence in which the controls are activated when the user presses Tab or Shift Tab. It also determines which control has the initial...
Making the application aesthetically appealing and intuitive
If you've used many different software packages, you've undoubtedly seen examples of poorly designed user interfaces, difficult-to-use programs, and just plain ugly screens. If you're developing spreadsheets for other people, you should pay particular attention to how the application looks. How a computer program looks can make all the difference in the world to users, and the same is true with the applications that you develop with Excel. Beauty, however, is in the eye of the beholder. If your...
Using the Properties window
In the VBE, the Properties window adjusts to display the properties of the selected item which can be a control or the UserForm itself . In addition, you can select a control from the drop-down list at the top of the Properties window see Figure 13-6 . Figure 13-6 Selecting a control CheckBox3 from the drop-down list at the top of the Properties window. Note The Properties window has two tabs. The Alphabetic tab displays the properties for the selected object in alphabetical order. The...
What You Need to Know
This is not a book for beginning Excel users. If you have no experience with Excel, a better choice might be my Excel 2007 Bible, which provides comprehensive coverage of all the features of Excel. That book is meant for users of all levels. To get the most out of this book, you should be a relatively experienced Excel user. I didn't spend much time writing basic how-to information. In fact, I assume that you know the following How to create workbooks, insert sheets, save files, and so on How...
Setting up the MultiPage control for the wizard
Start with a new UserForm and add a MultiPage control. By default, this control contains two pages. Right-click the MultiPage tab and insert enough new pages to handle your wizard one page for each wizard step . The example on the CD-ROM is a four-step wizard, so the MultiPage control has four pages. The names of the MultiPage tabs are irrelevant because they will not be seen. The MultiPage control's Style property will eventually be set to 2 - fmTabStyleNone. Tip While working on the UserForm,...
Errorhandling examples
The first example demonstrates an error that can safely be ignored. The SpecialCells method selects cells that meet a certain criterion. The SpecialCells method is equivalent to choosing the Home Editing Find amp Select lt Go To Special command. The Go To Special dialog box provides you with a number of choices. For example, you can select cells that contain a numeric constant non-formula . In the example that follows, the SpecialCells method selects all the cells in the current range selection...
Declaring a function
The syntax for declaring a function is as follows Public Private Static Function name arglist As type instructions name expression Exit Function instructions name expression End Function The Function procedure contains the following elements Public Optional Indicates that the Function procedure is accessible to all other procedures in all other modules in all active Excel VBA projects. Private Optional Indicates that the Function procedure is accessible only to other procedures in the same...
GoTo statements
The most straightforward way to change the flow of a program is to use a GoTo statement. This statement simply transfers program execution to a new instruction, which must be preceded by a label a text string followed by a colon, or a number with no colon . VBA procedures can contain any number of labels, but a GoTo statement cannot branch outside of a procedure. The following procedure uses the VBA InputBox function to get the user's name. If the name is not Howard, the procedure branches to...