An Object Showdown

Is it possible to demonstrate any compelling benefits with such a simple object Perhaps a few test scenarios will illustrate some clear benefits. With such a simple object, it is trivial to code a normal set of procedures that deliver the main functionality that the Loan class delivers. To summarize, the only thing the Loan class does is remember who it is (LoanNumber), how much it is for (Princi-palAmount), what interest rate it carries (InterestRate), how long it is for (Term), and finally,...

The Solution

The solution to this problem is to employ automation in Excel that will automatically create the presentation based on data in your Excel workbook. Using VBA you can write code that will Create an instance of PowerPoint. Create an empty presentation using the designated template. Create a slide for each report you have created in Excel. Save the presentation to a location of your choosing. Ideally, the only thing you'll need to do is add any content to the presentation that doesn't come from...

Syntax Errors Are Your Friend

Syntax errors are errors that occur when your code doesn't adhere to the syntax requirements of VBA. Perhaps no error is your friend, but if you had to pick an error as your friend, this would be a good choice. Syntax errors are relatively benign because they usually occur at development time and, as long as you have the Auto Syntax Check feature turned on, some can be automatically detected by the VBE as you enter your code. NOTE Programmers frequently use the terms development time and...

Accepting Worksheet Input

When you go to accept worksheet-based input, you'll encounter many of the issues that surround structured output. Chiefly, you need to develop a strategy for ensuring that the input is in the location you expect and that any supporting objects, such as specific worksheets or named ranges, are also present. Another facet of accepting worksheet-based input makes accepting input slightly more difficult than displaying output. Usually your procedures expect input of a specific data type such as a...

Specifying the Index Range of an Array

As I mentioned earlier, by default the first element of an array is located at index 0. By using the Option Base 1 statement at the top of your module, you can instruct VBA to begin all arrays at index 1. Another way to specify how your array indexes the elements it contains is by using the To clause in the declaration. A good example of this is an array that holds data specific to the day of the week. For example, suppose you need a variable to hold sales data associated with each day of the...

Listing List Processing with the Offset Property

With ThisWorkbook.Worksheets(List Example) .Rows.Hidden False .Rows.Font.Bold False .Rows(1).Font.Bold True End With End Sub Sub FilterYear(nYear As Integer) Dim rg As Range Dim nMileageOffset As Integer ' 1st row is column header so start ' with 2nd row Set rg ThisWorkbook.Worksheets(List Example).Range(A2) nMileageOffset 6 ' go until we bump into first ' empty cell Do Until IsEmpty(rg) If rg.Offset(0, nMileageOffset).Value < 40000 Then rg.Offset(0, nMileageOffset).Font.Bold True...

Listing Experimenting with Workbook Object Events

Private Sub Workbook_Activate() If UseEvents Then MsgBox Welcome back , vbOKOnly, Activate Event End If End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim lResponse As Long lResponse MsgBox(Thanks for visiting & _ Are you sure you don't want to stick around , _ vbYesNo, See ya ) If lResponse vbNo Then Cancel True End If End Sub Private Sub Workbook_Deactivate() If UseEvents Then MsgBox See you soon , vbOKOnly, Deactivate Event End If End Sub Private Sub Workbook_Open() Dim...

Listing Two Ways to Use an Object

' declare a loan variable and explicitly ' create the object that the variable ' will refer to. Dim objLoanl As New SimpleLoan ' declare a loan variable Dim objLoan2 As SimpleLoan ' demonstrate that the two ' loans are separate objects objLoanl.LoanNumber 1 objLoan2.LoanNumber 2 Debug.Print objLoanl.LoanNumber is & objLoanl.LoanNumber Debug.Print objLoan2.LoanNumber is & objLoan2.LoanNumber ' terminate the objects and ' free the memory associated with ' the object variables Set objLoanl...

Debugging Tactics that Work

You HAVE ALREADY READ this next statement multiple times you will make mistakes. Lots of them. This has nothing to do with skill. Everyone makes mistakes. The difference between the beginning developer and the veteran is that the veteran recognizes, diagnoses, and corrects her mistakes much, much more quickly than a beginner. In this chapter, I'll get you off to a good start by teaching the debugging tools and features included in the Visual Basic Editor (VBE) and tactics that you can employ to...

Lock Up Your Valuables

If you haven't already, you'll run into many situations that require you to use worksheet protection. When you're developing Excel applications, one of the most common reasons to use worksheet protection is to lock certain sections of the workbook in order to prevent end users from making changes that would break, or in some way negatively affect, the workbook. Locking up a worksheet presents many challenges. First of all, you need to consider what kind of changes you want to allow end users to...

Listing Checking the Status of All the Links in a Workbook

Sub CheckAllLinks(wb As Workbook) Dim avLinks As Variant Dim nLinkIndex As Integer Dim sMsg As String avLinks wb.LinkSources(xlExcelLinks) Debug.Print wb.Name & does not have any links. For nLinkIndex 1 To UBound(avLinks) Debug.Print Workbook & wb.Name Debug.Print Link Source & avLinks(nLinkIndex) Debug.Print Status & _ GetLinkStatus(wb, CStr(avLinks(nLinkIndex))) To call CheckAllLinks from the ProcessFileBatch procedure, locate these statements shown in the ProcessFileBatch...

Database Basics

The simplest definition of a database is that it is one or more sets of persistent, related data. By that definition, text files and even a list in Excel could qualify as a database. However, generally when the term database is used, it also refers to the software used to create and manage the database. This type of software is referred to as database management systems (DBMS). Most databases created using a DBMS are relational databases. A relational database is a database that allows the...

Chart Manipulation

Usually any programmatic manipulation with a chart is relatively minor. Your needs may be totally different than anything I've ever done, but when I have had applications that use charts in the past, usually the charts are precreated and then displayed or hidden as needed. You can manage which data is displayed in a chart by having a static chart source range and then programmatically managing which data appears in the chart source range. In this way, you can avoid all of the details of having...

Native Excel Database Integration

Once you put your data in a database, you need an efficient way to get it out. Without writing a single line of code, you can easily incorporate data from a database into an Excel workbook using Microsoft Query (MS Query). MS Query is included with every edition of Microsoft Office. As you can see in Figure 16.1, MS Query is a visual query tool that looks similar to the query design view in Access. Using MS Query, you can define a query that runs and returns data to Excel. A query is basically...

Display Oriented Features That Are Nice to Know

Although I use the ScreenUpdating and StatusBar properties in nearly every project of significance, I seldom use the remaining display-oriented features. These features deal primarily with manipulating or obtaining information about windows (the user interface element, not the operating system). The first property I'd like to mention in this section deals with the cursor. In fact, it is referred to as the Cursor property. You can use the Cursor property to change or determine which mouse icon...

Chart Hunting

As you know, a chart can take up residence in two locations as a chart sheet in the workbook, or as an embedded chart on a worksheet. Setting a reference to an existing chart varies according to what kind of location the chart lives in. For chart sheets, you can refer to them almost like a worksheet as the following code snippet demonstrates. Dim chrtl As Chart Dim chrt2 As Chart ' set a reference to the chart sheet named Chart4 Set chrtl ' set a reference to the 2nd chart sheet in the wb Set...

Birds Eye View of the Application Object

The Application object has so many useful aspects that it is probably a good idea to quickly summarize. The vast majority of your Excel development tasks will involve performing actions or manipulating lower-level Excel objects, such as ranges and worksheets, along with standard variables that represent the basic data types. Normally you don't spend much time thinking about the Application object. When you do use it though, the functions it performs are often critical to your application. Other...

Formatting Those Figures

Excel is all about numbers, so it comes as no surprise that you can format a number in Excel in about a million different ways. Well, maybe not a million, but you can get pretty creative. Believe it or not, though, you only need to use one property of the Range object to have complete control of the various ways you can format a number. That property is the NumberFormat property. Can you guess which tab of the Format Cells dialog box provides functionality similar to the NumberFormat property...

Untangle Links Programmatically Part I

As a former financial analyst (before I learned more efficient ways to analyze data), I used to build elaborate virtual ecosystems of linked Excel models. Inevitably, I'd need to make some sort of change to a particular workbook, and the change would break dependencies I had created in other workbooks that linked to the workbook I was making the change in. Maintaining this structure was a living nightmare. One of the tools that would've been helpful in those days was one that would...

Using XML for Persistent Settings

One of the many things that you can use XML (Extensible Markup Language) for is to create a modern-day initialization file. In fact, some of the most exciting new features in Excel 2003 rely heavily on XML for this purpose. Though the Windows registry was meant to replace INI (initialization) files, they are still in widespread use. One of the strengths of INI files versus the registry is that, because INI files are simple text, they can be easily inspected and modified using a text editor such...

Thinking Like a Computer Revisited

At the beginning of the chapter, I talked about a simple program that would loop through the items on a worksheet list and highlight any item that begins with 'A' in bold font. You're now familiar enough with the vocabulary of VBA that you can implement this program using terms your computer can understand, as shown in Listing 3.4. The part that I haven't covered yet is how to use Excel's Workbook object and Range object. I'll cover these in Chapters 7 and 8, respectively. However, I think...

Convenient Excel Object Properties

The Application object has a number of properties that conveniently return Excel objects of interest. For example, you often need a way to refer to the currently selected range, the active workbook, or the workbook that the module resides in (this may or may not be equal to the active workbook). The Application object has properties that return objects representing all of these and more. Table 5.1 lists the properties that are useful for returning particular Excel objects. Table 5.1 Properties...

Listing Using the Parent Property to Obtain a Reference to an Objects Parent Object

Vba Excel Ribbon

' Declare a worksheet variable named ws Dim ws As Worksheet Set ws ' Please meet my parent - Mrs. Workbook Debug.Print ws.Parent.Name TIP Keep the Parent property in mind as you work with the Excel object model. For example, when you write pro-cedures that require parameters that are Excel objects, use a Range object and the Parent property to get the worksheet to which the range belongs, and use the Parent property again to get the Workbook with which the range is associated. This can...

Importing Text Data onto a Worksheet

Similar to how you opened a text file in the previous section, you can also import text into an existing worksheet. This process involves many of the steps that you go through to open a text file directly. However, importing data onto an existing worksheet provides you with an added capability that you don't have when you open a text file directly. When you import text data onto an existing worksheet, the range that contains the imported text data is considered a data range. Among other things,...

Listing Safely Deleting Worksheets Using the Delete Sheet Function

' deletes the worksheet given in the ws parameter ' If bQuiet then do not display Excel alerts Function De1eteSheet(ws As Worksheet, bQuiet As Boolean) As Boolean Dim bDeleted As Boolean If CountVisibleSheets(ws.Parent) > 1 Then ' ok to delete - display alerts If bQuiet Then Application.DisplayAlerts False bDeleted ' forget it - need at least ' one visible sheet in a ' workbook. bDeleted is ' already false End If ' make sure display alerts is always on Application.DisplayAlerts True...

Short Survey of Excel Applications

Over the course of my career, I have had the opportunity to observe hundreds of ways that various corporations use Excel. I am continually amazed at their creativity. Many times Excel is used appropriately however, some applications in Excel are clearly the result of a developer (or otherwise) who did not know how to solve the problem using other technology better suited for the task at hand. In these cases, though I personally would not have chosen Excel as my canvas, I am often impressed that...

Listing Loan Object Implementation Details

Public Property Get Principa1Amount() As Variant PrincipalAmount mvPrincipalAmount End Property Public Property Let Principa1Amount(ByVa1 vNewValue As Variant) mvPrincipalAmount vNewValue End Property Public Property Get InterestRate() As Variant InterestRate mvlnterestRate End Property Public Property Let InterestRate(ByVa1 vNewValue As Variant) mvlnterestRate vNewValue End Property Public Property Get LoanNumber() As Variant LoanNumber mvLoanNumber End Property Public Property Let...

Listing Returning the Last Used Cell in a Column or Row with Worksheet Callable Functions

' returns a number that represents the last ' nonempty cell in the same column ' callable from a worksheet Function GetLastUsedRow(rg As Range) As Long lMaxRows If IsEmpty(rg.Parent.Cells(lMaxRows, rg.Column)) Then GetLastUsedRow _ rg.Parent.Cells(lMaxRows, rg.Column).End(xlUp).Row GetLastUsedRow rg.Parent.Cells(lMaxRows, rg.Column).Row ' returns a number that represents the last ' nonempty cell in the same row ' callable from a worksheet Function GetLastUsedColumn(rg As Range) As Long Dim...

Creating a Simple Object

Excel Vba Classes

Ok, let's test the water by creating a simple object and then using it within a standard module. By the powers vested in me, I hereby pronounce you a banker. Umm, make that a bank programmer. I know you would prefer the banker's hours, but without the well-oiled systems that you develop, those bankers wouldn't be able to get on the links so early (banker's hours are traditionally 9 00 A.M. to 3 00 P.M. of course, these days bankers work just as hard as anyone else). Your goal is to create a...

Listing Using Error Handling When Using Special Cells

Dim ws As Worksheet Dim rgSpecial As Range Dim rgCell As Range Set ws ThisWorkbook.Worksheets Special Cells MsgBox Congratulations amp ws.Name amp _ is an error-free worksheet. Set rgSpecial Nothing Set rgCell Nothing Set ws Nothing End Sub All this procedure does is look for any errors on a worksheet named Special Cells. If it finds any errors, it makes the background of the cells that contain an error red. Otherwise the procedure reports that it didn't find any errors. The first thing this...

Listing Using the Change Event to Respond to Worksheet Changes

Private Sub Worksheet_Change ByVal Target As Range Select Case Target.Address Case B 1 ChangeColumnWidth Target.Value Case B 2 ChangeRowHeight Target.Value End Select End Sub Private Sub ChangeColumnWidth Width As Variant If IsNumeric Width Then If Width gt 0 And Width lt 100 Then Me.Columns.ColumnWidth Width ElseIf Width 0 Then Me.Columns.ColumnWidth Me.StandardWidth End If End If End Sub Private Sub ChangeRowHeight Height As Variant If IsNumeric Height Then If Height gt 0 And Height lt 100...

Variables Are the Elements You Interact With

Your code consists of variables, operators and statements. Variables are the nuts and bolts of an application. Conceptually, variables represent the items or objects that your code works on. For example, a variable can represent a number that you need to perform math on, a text string that you need to edit, a worksheet, a workbook, and so on. You can programmatically manipulate anything in Excel that you interact with in normal use. To interact with these items Create a variable of the...

Object Inspection Using the Object Browser

Take Your Own Inventory

When you develop in Excel with VBA, your programmatic interaction with Excel consists of manipulating or using Excel's various objects. Excel has many, many objects available for you to manipulate. Without any other information, you can probably surmise that a worksheet object represents worksheets and a workbook object represents workbooks. One way you can see what other objects are available is to use the Object Browser. An example of the Object Browser is shown in Figure 2.12. To display the...

Break Mode For Fixing Things that Are Broken

The prime time for catching bugs is in Break mode. Break mode is a special mode of operation in the VBE that allows you to execute lines of code one at a time. You can watch your code execute and stop after each line to examine the state and values of your variables. This can be a big help in eradicating logical errors, because many logical errors stem from a fundamental difference between the way you thought a certain section of code would execute and the way that VBA actually executes it. By...

Implementing Branching with lfThen

Now that you know how to execute repetitive blocks of code, you need to know how to implement branching in your code. Branching is the process of selectively executing certain blocks of code depending on the value of an expression. You use the If .Then statement to implement branching. The simplest form of the If .Then statement tests a single expression and executes a single statement if the expression is true. An example of a simple If .Then is shown here If sLetter A Then rg.Font.Bold True...

Opening Files Under the Covers

So far this chapter has been primarily geared towards working with structured text files. Structured text files are usually used to move data between incongruent applications that do not have a built-in facility for sharing data with each other. As a developer, structured text files are likely to be the most frequent type of text file you work with. You saw earlier in this chapter that Excel has a native ability to open structured text files. The examples so far used Excel to open the text...

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

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 Fixed Width 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 Str Comp

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 Pseudo Collection 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 Range NameExists 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 Current Region 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

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

Microsoft Office Ehcel

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

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

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

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

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

Input Easy Output Easier

You now know everything that you neei to know to learn how to collect worksheet-based input and display output. Worksheet-based input output I O draws on your knowledge of using the Application, Workbook, Worksheet, and Range objects. Sure, the direct object you use is the Value property of the Range object, but you can't effectively and professionally do this without using all of the other objects I mentioned. One of the reasons you need to draw on your knowledge of all of the objects we have...

Implementing Loops

Looping, or executing a section of code repeatedly, is a common need in programming. There are two classes of loops fixed loops, have a definite, known number of times that they execute, and variable loops generally rely on a logical expression to determine whether looping should continue or not. Not only are For .Next loops easy to use, they're also fairly flexible. I show the general structure of a For .Next loop in the following simple procedure. This procedure prints the numbers 1 to 50 in...

Work Magic with ADO

I feel it is important to learn how to use MS Query. I've seen numerous VBA applications that were created to retrieve data from a database into Excel that could have been done using MS Query alone. There's no sense in writing custom code to work with a database if you can achieve the same results with MS Query. For those times when MS Query won't cut it, you can turn to ADO. ADO is a set of objects that you can use to interact programmatically with a database. The ADO object model is shown in...

Obtaining Help Has Never Been So Easy

Excel Vba Object Model

You've now seen that you can use a simple keystroke to obtain detailed help and documentation about an item directly from the Object Browser. As you develop your application, you can highlight any keyword, statement, or object, and also obtain help just by pressing F1. The VBA help files are quite detailed and contain lots of examples. That said, I won't spend any time here detailing how to use the help system other than to point out a few things. Figure 2.16 shows the help page for the...

Listing Automating Power Point Presentation Creation from Excel

Set ws ' Create a new instance of PowerPoint Set ppt New PowerPoint.Application ' Create a new presentation Set pres ppt.Presentations.Add pres.ApplyTemplate _ c program files microsoft office templates amp presentation designs maple.pot With pres.Slides.Add 1, ppLayoutTitle .Shapes 1 .TextFrame.TextRange.Text October Sales Analysis .Shapes 2 .TextFrame.TextRange.Text 11 5 2003 End With CopyDataRange pres, ws.Range Sales_Summary , 2, 2 CopyChart pres, ws.ChartObjects 1 .Chart, 3, 1...

Figure

A simple list for testing the End property e2 Microsoft Office Excel 2003 Beta - Chapter 8 Examples. 0 f l EfQ Fie Edit View Insert Format locfs E gt ata Window Help If the current cell is not empty, then see if the cell next to the current cell in the direction specified by the arrow key is empty. If the next cell is empty, then select the first nonempty cell. If a nonempty cell isn't found, select the cell next to the boundary of the worksheet. If the next cell is not empty, then select the...

The Need for Persistence

Because your code lives in a workbook, why not store user settings there In fact, why stop with user settings Why not put any configurable setting on a worksheet where it can be easily modified as needed You'll encounter certain situations in which this is not a good idea or not practical, but for many Excel applications this is a useful way to store this information in a manner that is easy and portable the settings travel with the workbook . By portable, I mean that the workbook doesn't have...

Sorting Lists Simplified

The Sort method looks rather intimidating at first glance. Fear not, however. It is a lot easier to use than it looks here because all the parameters are optional and you'll usually only use a handful of them. YourRangeToSort.Sort Key1 , Order1 As XlSortOrder , _ Key2 , Type , Order2 As XlSortOrder , _ Key3 , Order3 As XlSortOrder , _ Header As XlYesNoGuess , OrderCustom , MatchCase , _ Orientation As XlSortOrientation , _ DataOption1 As XlSortDataOption , _ DataOption2 As XlSortDataOption , _...

Use Literal Values with Care

Literal hard-coded values can be maintenance nightmares and should be used cautiously. You can use literal values in many different ways. Some of the more common uses for these values within an Excel application include Database connection information In the last chapter, I presented the Setting and Settings classes. You can use these classes to move these kinds of things out of your VBA code and onto a worksheet where you can manage them better. Although this has the benefit of centralizing...

Parameters Passed by Reference

ByRef means that the parameter is passed by reference. If you do not use the ByRef keyword when you declare parameters, then the parameters are passed by value. You could explicitly specify this by declaring parameters using ByVal. Anyway, rather than get into the details of this, it is probably best to just think of parameters in terms of read write. ByVal parameters are read only from the perspective of the calling procedure. The procedure that is called can change the values of the...

Pick the Perfect Location with Get SaveAsFilename

Getsaveasfilename

The yin of GetOpenFilename's yang is the GetSaveAsFilename method. This method presents the user with the familiar looking Save As dialog box found in most Windows applications see Figure 5.4 . As with GetOpenFilename, GetSaveAsFilename merely allows users to indicate a filename and a location to which to save a file without actually saving it. The syntax of GetSaveAsFilename is shown here _ FileFilter , FilterIndex , Title , ButtonText The parameters to GetSaveAsFilename are as follows....

Creating OLAP Query Files with Print

Just today, I needed to write a small utility that would automatically create a Microsoft Office Excel OLAP Query File .oqy . A query file is a file that Excel uses to store information related to the details associated with a connection to a Microsoft Analysis Services OLAP database. Anyway, this utility is handy because it eliminates the need to physically create the file for users or instruct them how to create it themselves. NOTE See Chapter 16for more information on working with OLAP...

Removing Components from Your Project

Removing a component from your project is nearly as easy as right-clicking the object in the Project Explorer and choosing Remove YourComponentName. This process has a built-in safety annoyance feature that asks you if you want to export the component before removing it. This is kind of a silly question because the menu item immediately above the Remove item is Export File. As if you couldn't find the Export File feature Anyway, it does help prevent accidental deletion when you select the...

Declaring Variables

Once you have decided the appropriate data type for a variable, declaring it is easy. The basic syntax for declaring a variable is as follows Dim RowCount As Integer Dim WorksheetName As String Dim CellValue As Variant Dim Salary As Currency Note that if you don't specify a data type, the variable is given a Variant data type. Consequently, CellValue could also be declared simply as However, when programming, a general rule of thumb is to write your code in a way that clearly expresses your...

Using the Windows Registry for Storing Values

Another option for storing literal values is the Windows registry. In fact, if you browsed the registry using the Registry Editor, you'd see that this is a common way for Windows programs to store configuration information. In Figure 13.4, you can see some of the registry settings used by Excel. NOTE To view the registry, choose Start Run, type regedit, and click OK. WARNING Use extreme caution using the Registry Editor. Never modify registry information unless you are 100 percent sure of the...