A Reflection on the Learning Curve
I believe that learning how to develop in Excel using VBA is easy. That said, you'll notice a dramatic improvement in your ability to churn out applications efficiently as you progress. When I first started learning VBA in Excel 5.0, I had a significant amount of experience with Excel. In fact, I was the Excel guru at work. Back then, the development environment wasn't nearly as friendly and hospitable to beginners as it is now. You actually had to memorize the Excel object model you didn't...
Data Types
When you declare or create a variable, it is best to think about what kind of data the variable will hold and then create a variable of the appropriate data type. You can think of a variable as a container that can hold a value. Variables can be declared specifically to hold certain kinds of values or data types. If you declare a variable as capable of storing Integer values, that variable would be referred to as an Integer variable. If you are not sure what kind of data the variable will hold,...
Listing Creating an OLAP Query File
Dim IFileNumber As Long Dim sText As String Dim oSettings As New Settings Dim sFileName As String ' Obtain a file number to use IFileNumber FreeFile ' Determine the file name and folder location. sFileName QueriesPath amp amp .oqy ' Open the file. Note - this overwrites any existing file ' with the same name in the same folder Open sFileName For Output As 1Fi1eNumber Print 1Fi1eNumber, QueryType OLEDB Print 1Fi1eNumber, Version 1 Print 1Fi1eNumber, CommandType Cube Data Source amp amp _ Initial...
Expounding On Compound Documents
As I mentioned in the previous section, compound documents are created using multiple applications yet they appear as a single cohesive document in the compound document's host application. You use a document's host application to view the document in its entirety. A compound document can store its components in two different ways. The first way is via linking. When you create a compound document in which a component of the document is linked to another document, the compound document stores...
Smart Workbook Design
Although this book focuses on the VBA aspect of developing Excel applications, this chapter wouldn't be complete without mentioning some practical advice regarding the general layout of an Excel application. In Chapter 22, I describe various application-distribution strategies and techniques such as templates, add-ins, and standard Excel workbooks. For the purposes of this section, I'm assuming that you're developing a standard Excel workbook. By standard Excel workbook, I mean an application...
Listing Opening FixedWidth Files Simplified
Sub TestOpenFixedWidthFileO Dim wb As Workbook Dim vFields As Variant The third column of the orders file is a date column MM DD YYYY . The rest are general default Array 0, xlGeneralFormat , _ Array 7, xlGeneralFormat , _ Array 21, xlMDYFormat , _ Array 32, xlGeneralFormat , _ Array 43, xlGeneralFormat C fixed width orders.txt, 1, vFields Function OpenFixedWidthFile sFile As String, _ Application.Workbooks.OpenText _ Filename sFile, _ StartRow lStartRow, _ DataType xlFixedWidth, _ FieldInfo...
Scrutinizing Strings with InStr and StrComp
VBA has a number of built-in functions for working with strings. InStr and StrComp are used to either look for the presence of one string inside another string or to compare two strings for equivalence. InStr returns a variant the subtype is long that indicates the position of the first occurrence of one string inside another. The syntax of InStr is as follows InStr start, string1, string2 , compare The start parameter is optional and specifies where InStr should begin searching for string2...
Listing The Settings Classa PseudoCollection of Setting Objects
Private Const SETTINGS_WORKSHEET Settings Count mwsSettings.Ce11s 65536, 1 .End x1Up .Row - 1 ' adds a new setting. returns setting object ' associated with the new setting. Public Function Add Name As String As Setting Dim IRow As Long Dim oSetting As Setting ' make sure a setting with this name ' find the last used row and move down one row IRow mwsSettings.Ce11s 65536, 1 .End x1Up .Row 1 Err.Raise vbObjectError 201, Settings Class, _ A setting named amp Name amp already exists. Set oSetting...
Listing Adding Clickable Sorting to Worksheet Lists
Dim mnDirection As Integer Dim mnColumn As Integer Private Sub Worksheet_BeforeDoub1eC1ick ByVa1 Target As Range, Cancel As Boolean ' make sure the double-click occurred in a cell ' containing column labels If Target.Column lt 5 And Target.Row 1 Then ' see if we need to toggle the ' direction of the sort If Target.Column lt gt mnColumn Then ' clicked in new column - record ' which column was clicked mnColumn Target.Column ' set default direction mnDirection xIAscending ' clicked in same column...
Listing Validating Data in Property Let Procedures
' private class variables to hold property values ' create an enumeration of loan terms ' set each value equal to the term in months Enum InLoanTerm 1n2Years 24 1n3Years 36 1n4Years 48 1n5Years 60 1n6Years 70 End Enum ' Lending limits Private Const MIN_LOAN_AMT Private Const MAX_LOAN_AMT ' Interest rate limits Private Const MIN_INTEREST Private Const MAX_INTEREST_RATE 0.21 ' set default principal amount to 0 mcPrincipalAmount 0 ' set default interest rate to 8 annually mdInterestRate 0.08 ' set...
Listing System Information Available Using Application Object Properties
Debug.Print Application End Sub This code produces the following output on my computer. Windows 32-bit NT 5.01 Dakota Technology Group, Inc. Steven M. Hansen 11.0 Table 5.2 details some of the system-oriented properties of the Application object. Table 5.2 System-Oriented Properties of the Application Object CalculationVersion Right four digits indicate the version of the calculation engine whereas the digits to the left indicate the major version of Excel. MemoryFree Returns the amount of...
Listing Formatting a Basic Chart
Sub FormattingCharts Dim cht As Chart Dim ws As Worksheet Dim ax As Axis Set ws ThisWorkbook.Worksheets Basic Chart Set cht GetChartByCaption ws, GDP .AxisTitle.Font.Size 12 .AxisTitle.Font.Color vbRed End With .MinorGridlines.Border.LineStyle xlDashDot End With ' format plot area With cht.PlotArea .Border.LineStyle xlDash .Border.Color vbRed .Interior.Color vbWhite .Width cht.PlotArea.Width 10 .Height cht.PlotArea.Height 10 End With cht.ChartArea.Interior.Color vbWhite cht.Legend.Position...
Listing A Simple Example of Standard Workbook Properties
PrintGeneralWorkbookInfo ThisWorkbook End Sub Sub PrintGenera1WorkbookInfo wb As Workbook Debug.Print Name amp wb.Name Debug.Print Full Name amp wb.FullName Debug.Print Code Name amp wb.CodeName Debug.Print FileFormat amp GetFileFormat wb Debug.Print Path amp wb.Path If wb.ReadOnly Then Debug.Print The workbook has been opened as read-only.' Debug.Print The workbook is read-write. End If Debug.Print The workbook does not need to be saved. Debug.Print The workbook should be saved. End If End Sub...
Deactivate Activate Dont Select Select
The use of the Activate and Select methods are the two most common blunders I see. Activate is used to activate a workbook, worksheet, or range. Select is used to select a range. Once a range is selected, it can be manipulated using the Selection property of the Application object. Manipulating Excel objects using Activate, Select, and Selection is slow, error-prone, and usually results in code that is difficult, at best, to maintain. The biggest offender of this guidance is Excel's macro...
Listing Validating Names Using the RangeNameExists Procedure
' Checks for the existence of a named range on a worksheet Function RangeNameExists ws As Worksheet, sName As String As Boolean Dim s As String RangeNameExists True Exit Function RangeNameExists False End Function If Test Then MsgBox The name exists, it refers to amp _ _ vbOKOnly MsgBox The name does not exist, vbOKOnly End If If djfs Then MsgBox The name exists, it refers to amp _ _ MsgBox The name does not exist, vbOKOnly End If RangeNameExists returns a Boolean value that is true if the...
Automatic Text Files
Now that you have reviewed how to open text files manually, opening text files programmatically isn't much of a stretch. To open text files programmatically, use the OpenText method of the Workbooks object. The syntax of OpenText is as follows. Application.Workbooks.OpenText Filename, Origin , _ StartRow , DataType , TextQualifier , _ ConsecutiveDelimiter , Tab , Semicolon , Comma , _ Space , Other , OtherChar , FieldInfo , _ TextVisualLayout , DecimalsSeparator , _ ThousandsSeparator ,...
Excel Programming with VBA
Associate Publisher Joel Fugazzotto Acquisitions Editor Tom Cirtin Developmental Editor Brianne Agatep Production Editor Susan Berge Technical Editor Acey Bunch Copyeditor Rebecca C. Rider Compositor Jeff Wilson, Happenstance Type-O-Rama Proofreaders Laurie O'Connell, Amy J. Rasmussen, Nancy Riddiough Indexer Ted Laux Book Designer Maureen Forys, Happenstance Type-O-Rama Cover Illustrator Tania Kac, Design Site Copyright 2004 SYBEX Inc., 1151 Marina Village Parkway, Alameda, CA 94501. World...
The Versatile Properties Window
The Properties window is mainly used for Microsoft Excel Objects and Forms. The Properties window is context specific this means that, depending on what you have selected either in the Project Explorer or while designing a form, the Properties window displays a number of properties you can think of these properties as settings or characteristics associated with the object that you can examine and edit. If you are not sure what a particular property is, select the property in the Properties...
Listing Referring to Ranges Using the Application Object
Sub ReferringToRangesI Dim rg As Range ' ActiveCell is a range representing the ' active cell. There can be one and ' only one active cell. Debug.Print Application.ActiveCell.Address ' Selection refers to a range representing ' all of the selected cells. There can be ' one or more cells in the range. Debug.Print Application.Selection.Address ' Application.Range works on the active ' worksheet ThisWorkbook.Worksheets 1 .Activate Set rg App1ication.Range D5 Debug.Print Worksheet 1 is active...
Thinking Like a Computer
Imagine the most mentally incompetent person you know. Now picture someone 10 times more inept. This is your new imaginary friend. You should cut your new friend a little slack and assume that he has two redeeming traits he is excellent at following directions and he can follow these directions at amazing speeds. Oh, one more thing, your friend will do anything you say without question provided he understands you, of course. That imaginary friend is your computer. The problem with your friend...
Listing Specifying Individual Cells with the Cells Property
Sub UsingCells Dim rg As Range Dim nRow As Integer Dim nColumn As Integer Dim ws As Worksheet Set rg ws.Cells nRow, nColumn rg.Value rg.Address Set rg Nothing Set ws Nothing End Sub Listing 8.2 also demonstrates two properties of the Range object Value and Address. All this listing does is loop through a 10 X 10 block of cells and set the value of each cell equal to the address of the cell, as is shown in Figure 8.1. Your first listing that outputs to a worksheet As you can see, the Cells...
Listing Calling CurrentRegion to Inspect a Lists Useful Characteristics
Sub CurrentRegionExample Dim rg As Range Dim ws As Worksheet Set ws ThisWorkbook.Worksheets Current Region ' get current region associated with cell A1 Set rg ws.Ce11s 1, 1 .CurrentRegion ' number of header rows ws.Range I2 .Va1ue rg.ListHeaderRows ws.Range I3 .Va1ue rg.Columns.Count ' resize to exclude header rows Set rg rg.Resize _ rg.Rows.Count - rg.ListHeaderRows, _ rg.Co1umns.Count .Offset 1, 0 ' number of rows ex header rows ws.Range I4 .Va1ue rg.Rows.Count ' number of cells ex header...
File Send To
Not every Excel application can be distributed by simply sending the file to the intended user, but many can. You'll find that you have numerous options available for distributing your Excel application, and most are as simple as clicking File Send To to send the file to the intended recipient. Contrast that to installing traditional applications. As you may know, installing traditional applications can be problematic on multiple levels. First, you must create a setup program just to install...
Turn On All of the Code Settings
The Code Settings on the Editor tab should all be turned on, especially because you are learning see Figure 2.5 . However, you could argue that Require Variable Declaration should be left off because it allows beginners to begin developing without an ounce of knowledge regarding how to use variables While this is true, if Require Variable Declaration is left off, subtle yet serious errors may be introduced that beginning developers are presumably not well equipped to correct or diagnose yet....
Summary Ppi
Believe it or not, you now have a basic toolbox with which to begin furthering your skills as an Excel developer. In the process of learning how to use Excel's object model you'll be continually exposed Identity Confusion Am i a Property or a Method One of the reasons that differentiating between properties and methods is often difficult is that programmers don't always follow the conventions for naming properties and methods. The Count property is a good example. Generally speaking, methods...
Listing Using the Interior Object to Alter the Background of a Range
Sub InteriorExamp1e Dim rg As Range ' create examples of each pattern Set rg _ Range ListStart .Offset 1, 0 ' create examples of each VB defined color constant Set rg _ 0 rg.0ffset 0, 2 .Interior.Co1or rg.0ffset 0, 1 .Va1ue Set rg rg.0ffset 1, 0 rg.0ffset 0, 2 .Interior.Pattern rg.0ffset 0, 1 .Va1ue rg.0ffset 0, 3 .Interior.Pattern rg.0ffset 0, 1 .Va1ue rg.0ffset 0, 3 .Interior.PatternCo1or vbRed Set rg rg.0ffset 1, 0 This listing loops through two lists that I've created on a worksheet named...
Listing Seaching for Charts Using the Chart Title
' searches charts on a worksheet by chart title Function GetChartByCaption ws As Worksheet, sCaption As String _ As Chart Dim chtObj As ChartObject Dim cht As Chart Dim sTitle As String ' loop through all chart objects on the ws For Each chtObj In ws.ChartObjects ' make sure current chart object chart has a title If chtObj.Chart.HasTitle Then sTitle chtObj.Chart.ChartTitle.Caption If StrComp sTit1e, sCaption, vbTextCompare 0 Then Set cht chtObj.Chart Exit For End If Set GetChartByCaption cht...
Would You Like Special Sauce with That
Are you familiar with the Go To Special functionality in Excel This is another chunk of functionality that many Excel users either don't know exists or don't take advantage of. Check it out in Excel select Edit Go To and then click the Special button at the bottom left corner of the Go To dialog box. Figure 9.7 is the result. Go To Special is another useful, yet underutilized, chunk of Excel functionality. If you haven't used this yet, let me tell you this handy little dialog box can be a real...
Keeping End Users Informed with the Status Bar
Another property of the Application bar that I use frequently is the StatusBar property. You can see the status bar in Figure 5.1. Look closely at the bottom-left corner. See the text that reads, I am the StatusBar. Hear me roar. You may have known right where to look, but many other people don't pay much attention to the status bar. Keep that in mind as you decide what information to display there. If it is critical or important information, you need to present it to the end user using a...
Getting to Know Your Environment
Before YOU CAN START writing any serious code, you need to learn your way around the development environment. In this chapter, you'll examine the various aspects and features of the Visual Basic Editor VBE . The VBE includes many features that do an excellent job of easing beginning developers into programming and helping experienced developers become more productive. Personally, I have always disliked these kinds of chapters because I am always so eager to get on to the good stuff. If you have...
Listing Hiding and Unhiding Worksheets
Sub HideWorksheet sName As String, bVeryHidden As Boolean If WorksheetExists ThisWorkbook, sName Then If bVeryHidden Then xlSheetVeryHidden xlSheetHidden End If End If End Sub Sub UnhideWorksheet sName As String If WorksheetExists ThisWorkbook, sName Then xlSheetVisible End If ' hide the worksheet HideWorksheet Sheet2, True ' show that it is hidden - ask to unhide lResponse MsgBox The worksheet is very hidden. Unhide , vbYesNo UnhideWorksheet Sheet2 End If Listing 7.4 uses the WorksheetExists...
Give Syntax Highlighting a Try
To change the syntax highlighting settings, select Tools Options and then choose the Editor Format tab. The Editor Format tab is displayed in Figure 2.4. NOTE In the next chapter, I'll begin covering information that will give this section more context. For now, just realize that this feature exists and that it can be very helpful in your development and maintenance efforts. For each item in the Code Colors list box, you can set the foreground color the color of the font , the background color,...
Listing Examples of the VBA Open Statement
Sub SimpleOpenExamples Dim lInputFile As Long Dim lOutputFile As Long Dim lAppendFile As Long Open C MyInputFile.txt For Input As lInputFile ' Get another valid file number lOutputFile FreeFile ' Create a new file for output Open C MyNewOutputFile.txt For Output As lOutputFile ' Get another valid file number lAppendFile FreeFile ' Open MyAppendFile.txt to append data to it ' or create new file if MyAppendFile doesn't exist Open C MyAppendFile.txt For Append As lAppendFile Close lInputFile,...
Listing Validating a Range for Appropriate Data
Function ReadCurrencyCell rg As Range As Currency Dim cValue As Currency If IsEmpty rg Then GoTo ExitFunction If Not IsNumeric rg Then GoTo ExitFunction ReadCurrencyCell cValue Exit Function You are guaranteed to get a numeric value back when you use the ReadCurrencyCell function. This eliminates the problem caused when a range of interest either contains text data or doesn't contain any value at all. In both of these cases, the procedure returns a zero.
Data Mobility with Cut Copy and Paste
Some of the most mind-numbing work I can think of is performing a long, manual, repetitive sequence of copy paste or cut paste. Have you ever experienced this Maybe you receive a dump of data from some other system and need to systematically sort and group the data and then prepare a report for each grouping. Based on my observations, I'm not the only person who has suffered through this kind of activity. More than a few people have probably become attracted to the potential benefits of...
Listing Using the Names Object to List All Named Ranges
' Test the ListWorkbookNames procedure ' Outputs to cell A2 on the 2nd worksheet in the workbook Sub TestListNames ListWorkbookNames ThisWorkbook, End Sub Sub ListWorkbookNames wb As Workbook, rgListStart As Range Dim nm As Name ' print out the name of the range rgListStart.Value nm.Name ' print out what the range refers to ' the ' is required so that Excel doesn't ' consider it as a formula rgListStart.0ffset 0, 1 .Va1ue ' amp nm.RefersTo ' set rgListStart to refer to the cell ' the next row...
Clarify the Muddle with the Call Stack
Initially, you probably won't have much reason to be concerned about a call stack because the procedures you'll write will likely be monolithic in nature with a few procedures. Usually these types of programs have simple relationships between procedures at run-time. As you progress, you'll write smaller, purposeful procedures and build functionality by assembling groups of procedures that collectively perform useful actions. In assembling groups of procedures, you'll occasionally create a maze...
Excel Development Best Practices
As YOU ADVANCE YOUR Excel development skills, you'll experience dramatic increases in productivity. These increases come from knowing more about various Excel objects and their properties and methods, being able to apply the correct object to the task at hand, and realizing more efficient development methodologies. The goal of this chapter is to present tips, techniques, and other knowledge that I've found useful. Hopefully this will save you a lot of time and save you from just stumbling...
Listing Programmatically Retrieving Link Source Information
Sub PrintSimpleLinkInfo wb As Workbook Dim avLinks As Variant Dim nIndex As Integer ' get list of Excel-based link sources avLinks wb.LinkSources xlExcelLinks If Not IsEmpty avLinks Then ' loop through every link source For nIndex 1 To UBound avLinks Debug.Print Link found to ' amp avLinks nIndex amp ' Next nIndex Debug.Print The workbook ' amp wb.Name amp _ ' doesn't have any links. As you can see, the only thing you need to check for when you're using LinkSources is to see if it returns Empty...
Listing A Robust Batch Workbook Processing Framework
Sub ProcessFileBatch Dim nIndex As Integer Dim vFiles As Variant Dim wb As Workbook Dim bAlreadyOpen As Boolean vFiles GetExce1Fi1es Se1ect Workbooks for Processing ' Make sure the dialog wasn't cancelled - in which case ' vFiles would equal False and therefore wouldn't be an array. If Not IsArray vFiles Then Debug.Print No files selected. ' OK - loop through the filenames For nIndex 1 To UBound vFiles If IsWorkbookOpen CStr vFi1es nIndex Then Set wb Debug.Print Workbook already open amp...
Procedural Programming
Procedural programming is a programming paradigm in which a program is constructed of small procedures that are linked together to perform a given task. One school of thought regarding procedural programming is that procedures have one and only one exit point. In Listing 6.1, the use of the Exit statement in the event that an array is not returned would violate this guideline. The alternative is to embed nearly the entire remaining block of statements inside a giant If .Then statement. I used...
Logical Errors Cause Gray Hair
The third and final classification of errors consists of errors caused by faulty logic. Logical errors have the potential to live unnoticed for a long period of time this is because your application will, from all outward appearances, appear to work just fine. Logical errors aren't discovered by the compiler at development or compile time and they don't embarrass you by displaying terse run-time errors to your end users. Though most logical errors are found and don't cause serious problems,...
I Like Treats
At this point, I would like to share a treat with you by introducing you to a special product Microsoft Analysis Services. Analysis Services is a special kind of database product that ships in the box with Microsoft SQL Server. Analysis Services is an OLAP product. OLAP stands for Online Analytical Processing. OLAP applications enable rapid analysis of numerical data along multiple dimensions. For example, an OLAP database may be designed to summarize financial data that can be viewed by...
Think Like an Environmentalist
One of the quickest ways an application can annoy or alienate a user is by operating like a toxic manufacturing factory located in Yellowstone National Park. Like a contemptible website that spawns countless pop-up windows, these programs go about rearranging options, menus, shortcut keys, and other aspects of Excel with reckless abandon. Programs should respect the environment that they operate in and respect the settings and preferences of the user. A conscientious program should Leave things...
Listing Link Status Checker
Function GetLinkStatus wb As Workbook, sLink As String As String Dim avLinks As Variant Dim nIndex As Integer Dim sResult As String Dim nStatus As Integer ' get a list of link sources avLinks wb.LinkSources xlExcelLinks ' make sure there are links in the workbook If IsEmpty avLinks Then GetLinkStatus No links in workbook. Exit Function End If ' default result in case the link is not found sResult Link not found. For nIndex 1 To UBound avLinks If _ StrComp avLinks nIndex , sLink, vbTextCompare 0...
Security Considerations
Because you don't particularly want your users modifying the Settings worksheet on a whim, normally this worksheet is very hidden xlVeryHidden so hidden it doesn't show up when you select Format gt Sheet gt Unhide . Although I haven' t done so here, you could extend the final product to use a protection scheme on this worksheet as well so that it can't be changed without using a password. While I'm on the subject, this method of storing data is not inherently secure. Ninety-nine percent of...
Variable Scope and Lifetime
Variables have both a scope and a lifetime. Understanding the concepts of scope and lifetime are often critical to writing code that works as you expect. It is common practice to use naming conventions to add meaning to your variable names. One convention, of course, is to not use any convention at all. Another convention is to append a one- to three-letter prefix to your variable names to help remind you of the variable's data type. Some programmers also may prefix their module level variables...
Opening Text Files in Excel
Even if you are already familiar with the mechanics of opening a text file in Excel, it is a good idea to refresh your knowledge here because the process of opening these files programmatically depends on your familiarity with the manual process. I will demonstrate the mechanics of opening up a delimited file using the file shown in Figure 15.1. For any text file, you begin the process by selecting File Open in Excel and then you change the Files of Text drop-down box to Text Files prn., .txt,...
What Is an Excel Application
First of all, I should define what I mean by an Excel application. By Excel application, I mean a spreadsheet that you have developed using Excel and VBA for use by yourself or other users. VBA stands for Visual Basic for Applications, which is essentially Visual Basic that has been modified to run within other host applications such as Excel or Microsoft Word. Excel applications contain VBA code that may perform one or more of the following tasks Automate tedious processes. Many times, once...
Listing The Complete Setting Class
' private class variables Private mwsSettings As Worksheet Private mrgSetting As Range Private mbAllowEditing As Boolean Enumeration for the kinds of Setting types Enumeration for the kinds of Setting types Enum setSettingType setPrivate 0 setReadOnly 1 setReadWrite 2 setReadProtectedWrite 3 End Enum don't display in user inteface read-only setting read-write setting read-write with password ' Setting Description Property Get Description As String If mrgSetting Is Nothing Then Description...