Language Version of Windows
By this, I mean the basic language version of Windows itself. This choice defines the text for the standard buttons in the VBA MsgBox function when using the vbMsgBoxStyles constants . Hence, whereas the text of the buttons on Excel's built-in messages responds to the Office UI language, the text of the buttons on your own messages responds to the Windows language. Note that the only way to discover the Windows language is with a Windows API call. Some things in Office 2007 are 100 U.S. English...
Worksheets Collection Properties and Methods
Read-only. Returns a collection holding all the horizontal page breaks associated with the Worksheets collection Set Get whether the worksheets in the collection are visible. Also can set this to xlVeryHidden to prevent a user from making the worksheets in the collection visible Read-only. Returns a collection holding all the vertical page breaks associated with the Worksheets collection Method. Parameters Before , After , Count , Type . Adds a worksheet to the collection. You can specify where...
The RangeAutoFilter Method
The AutoFilter method of a Range object is a very curious beast. You are forced to pass it strings for its filter criteria, and hence you must be aware of its string handling behavior. The criteria string consists of an operator , gt , lt , gt , and so on followed by a value. If no operator is specified, the equals operator is assumed. The key issue is that when using the equals operator, AutoFilter performs a textual match, whereas using any other operator results in a match by value. This...
Popup Menus
Excel's built-in shortcut menus are included in the command bar listing created by the macro ListFirstLevelControls, which you saw earlier in this chapter. The following modified version of this macro shows only the command bars of type msoBarTypePopup, as shown in Figure 15-11. SHide PivotChart Field Buttons 3956 gt W tateifl . All Lei-els FatsEs I PofNfiE1 The code to display the popups is shown here Dim ctl As CommandBarControl Dim cbr As CommandBar Dim iRow As Integer If Not...
Writing to Text Files Using Print
Print enables you to write text files in any format you just have to do a bit more work. To see the effect of using Print instead of Write, change the Write statement in the WriteFile sub as follows Print iFNumber, dDate, sCustomer, sProduct, dPrice The output looks like that in Figure 11-3. If you want to read data in this format, you can read each line of the file using the Line Input statement. You then need to have code to parse out the data. Taking a hint from Write, you might want to use...
Chart Object and the Charts Collection Example
This example creates a 3D chart from a given range, formats it, and saves a picture of it as a .jpg image 'Set the data source and plot by columns .SetSourceData .Location Where xlLocationAsNewSheet 'Size and shape matches the window it's in .ChartTitle.Caption Main Chart .ApplyDataLabels Type xlDataLabelsShowNone 'Save a picture of the chart as a jpg image .Export c amp .Name amp .jpg, jpg, False
ColorFormat Properties
the color of the color mapped to Color the theme color scheme by using the RGB Long Set Get the red-green-blue value associated the color of the ColorFormat using an index value corresponding to the current color scheme a value that lightens or darkens the color of a specified shape. The values can be from -1 darkest to 1 lightest . Zero is neutral Type MsoColor Type Read-only. Returns whether the color is an
Embedded Charts
When you create a chart embedded as a ChartObject, it is a good idea to name the ChartObject so that it can be easily referenced in later code. When you select the chart, you will see its name to the left of the Formula bar at the top of the screen in the name box. You can select and change the name of the ChartObject in the name box and press Enter to update it. The embedded chart in Figure 8-4 was created, dragged to its new location, and had its name changed to MangoesChart. The name can...
CalculatedFields
You can create new fields in a PivotTable by performing calculations on existing fields. For example, you might want to calculate the weighted average price of each product. You could create a new field called AveragePrice and define it to be Revenue divided by NumberSold, as in the following code Sub CalculateAveragePrice Dim pvt As PivotTable 'Add new Worksheet and PivotTable Worksheets.Add TableDestination ActiveCell, 'Remove AveragePrice if it exists On Error Resume Next On Error GoTo 0...
Comment Object and the Comments Collection Example
This example removes the user name added by Excel at the start of the comment and formats the comment to make it more readable Dim oComment As Comment, i As Integer 'Loop through all the comments in the sheet For Each oComment In ActiveSheet.Comments 'Using the text of the comment With oComment.Shape.TextFrame.Characters 'Find and remove the user name inserted by Excel i InStr 1, .Text, amp vbLf If i gt 0 Then .Name Arial .Size 10 .Bold False End With End With 'Make the text frame auto-fit...
Series Object and the SeriesCollection Collection
The SeriesCollection collection holds the collection of series associated with a chart group. Each Series object contains a collection of points associated with a chart group in a chart. For example, a simple line chart contains a series Series of points brought in from the originating data. Because some charts can have many series plotted on the same chart, the SeriesCollection is used to hold that information. The parent of the SeriesCollection is the ChartGroup. The SeriesCollection object...
Object Variables
The variables you have seen so far have held data such as numbers and text. You can also create object variables to refer to objects such as worksheets and ranges. The Set statement is used to assign an object reference to an object variable. Object variables should also be declared and assigned a type as with normal variables. If you don't know the type, you can use the generic term Object as the type Dim objWorkbook As Object Set objWorkbook ThisWorkbook MsgBox objWorkbook.Name It is more...
The Val Function
This is the most commonly used function to convert from strings to numbers. It actually only converts a U.S.-formatted numerical string to a number. All the other string-to-number conversion functions try to convert the entire string to a number and raise an error if they can't. Val, however, works from left to right until it finds a character that it doesn't recognize as part of a number. Many characters typically found in numbers, such as dollar signs and commas, are enough to stop it from...
Watch Object Example
The following routine prompts the user for a range, then loops through each cell in the range and adds it to the Watch window. It then displays the Watch window Set rngWatches Application.InputBox _ Please select a cell or cell range to watch, 'View the watch window based on their answer Application.CommandBars Watch Window .Visible
Range Object and the Ranges Collection Object
The Range object is one of the more versatile objects in Excel. A range can be a single cell, a column, a row, a contiguous block of cells, or a non-contiguous range of cells. The main parent of a Range object is the Worksheet object. However, most of the objects in the Excel object model use the Range object. The Range property of the Worksheet object can be used to choose a certain range of cells using the Cell1 and Cell2 parameters. New to Excel 2007, the Ranges object holds a collection of...
Prerequisites
If you intend to spend more than ten minutes investigating RibbonX, there are a few key downloads you'll need and web sites you'll need to know The official RibbonX site is at The Office 2007 Custom UI Editor is available from http openxmldeveloper.org articles CustomUIeditor.aspx. There are two invaluable files available on the MSDN web site. The first contains a list of the names of all Excel's built-in tabs, groups, and controls the second is an Excel add-in that adds a gallery of all the...
AddinInstall Event
Two special events are triggered when you install and uninstall an Add-in. The following code, in the ThisWorkbook module, shows how to display a user form when the Add-in is installed The user form displays the information shown in Figure 17-7 for the user. You have just installed the Company Data Lisi Use the con trols under the Add-Ins tab of the Ribbon to show the data ferm. When the data fbrm is dosed, you can sort the data ltstr print it or select a Department. After selecting a...
Range Properties
Set Get whether text in a cell is automatically indented if the text alignment in a cell is set to equally distribute Read-only. Parameters RowAbsolute, ColumnAbsolute, ReferenceStyle As XlReferenceStyle, External , RelativeTo . Returns the address of the current range as a string in the macro's language. The type of address reference, absolute, A1 reference style, R1C1 reference style is specified by the parameters Read-only. Parameters RowAbsolute, ColumnAbsolute, ReferenceStyle As...
The Open Method
This method opens a connection to the data source, and has the following syntax connection.Open ConnectionString, UserID, Password, Options The ConnectionString argument serves the same purpose as the ConnectionString property discussed in the previous section. ADO allows you to set this property in advance or pass it in at the time you open the connection. The UserID and Password arguments can be passed separately from the connection string if you wish. The Options argument is particularly...
The Properties Window
The Properties window shows you the properties that can be changed at design time for the currently active object in the Project Explorer window. For example, if you click Sheet1 in the Project Explorer, the Sheet1 properties are displayed in the Properties window, as shown in Figure 1-7. The ScrollArea property has been set to A1 D10, to restrict users to that area of the worksheet. You can get to the help screen associated with any property very easily. Just select the property, such as the...
Creating a UDF
Unlike manual operations, UDFs cannot be recorded you have to write them from scratch using a standard module in the VBE. If necessary, you can insert a standard module by right-clicking in the Project Explorer window and choosing Insert O Module. A simple example of a UDF is shown here Fahrenheit Centigrade 9 5 32 End Function Here, a function called Fahrenheit is created that converts degrees Centigrade to degrees Fahrenheit. In the worksheet, you could have column A containing degrees...
Summary Cul
In RibbonX, Microsoft has done an extremely good job of exposing the capabilities of the Office 2007 Ribbon for developers to use. You can easily create custom tabs, groups, menus, buttons, toggle buttons, drop-downs, galleries, checkboxes, and dynamic menus to add your application's features to the Ribbon. You have a comprehensive and cohesive set of attributes that can be applied to all control types, and nearly every attribute has an equivalent getAttribute callback. Using those controls and...
Hooking BuiltIn Controls
Whereas most of RibbonX concentrates on customizing the visual appearance of the Ribbon, the commands and command elements provide a mechanism for overriding any built-in menu, to modify its enabled state or to intercept any button clicks. To achieve that, you can include the following XML to, say, override the Print button so you can set up some formatting lt command idMso FilePrint onAction rxPrint_onAction gt lt commands gt lt customUI gt The command element can only have an enabled...
Summary Mss
As you have seen, the AutoFilter and Advanced Filter features can be combined with VBA code to provide flexible ways for users to extract information from data lists. By combining these features with ActiveX controls, such as combo boxes and command buttons, you can make them readily accessible to all levels of users. You can use the macro recorder to get an indication of the required methods and adapt the recorded code to accept input from the ActiveX controls. Tables provide a way to...
Visible Property
You can hide items by setting their Visible property to False. Say you are working with the grouped dates from the last exercise, and you want to see only Jan 2006 and Jan 2007, as shown in Figure 7-12. Sub CompareMonths Dim pvt As PivotTable Dim pvi As Pivotltem Dim sMonth As String 'Specify month to be visible sMonth Jan Set pvt ActiveSheet.PivotTables l 'Hide all years except 2006 amp 2007 For Each pvi In If pvi.Name lt gt 2006 And pvi.Name lt gt 2007 Then 'Make sure specified month is...
The VBProject Object
This object is the container for all the programming aspects of a workbook, including UserForms, standard modules, class modules, and the code behind each worksheet and the workbook itself. Each VBProject corresponds to one of the top-level items in the Project Explorer. A specific VBProject object can be located either by iterating through the VBE's VBProjects collection or through the VBProject property of a workbook. To find the VBProject that corresponds to the workbook Bookl.xlsm, the...
ChartObject Methods
Name Returns Parameters Description Activate Variant Makes the embedded chart the active chart Activate Variant Makes the embedded chart the active chart Brings the embedded chart to the front of all the other objects on the sheet. Changes Copies the Chart object into the clipboard as a picture. The Appearance parameter can be used to specify whether the picture is copied as it looks on the screen or when printed. The Format parameter can specify the type of picture that will be put into the...
Validation Properties
Read-only. Returns how the user will be alerted if the range includes invalid data. Uses the XlDVAlertStyle constants Set Get the error message to show for data validation Set Get what the title is for the error data validation dialog box Read-only. Returns the value, cell reference, or formula used for data validation Read-only. Returns the second part of the value, cell reference, or formula used for data validation. The Operator property must be xlBetween or xlNotBetween Set Get whether a...
Validation Methods
Adds data validation to the parent range. The validation type Type parameter must be specified. The type of validation alert AlertStyle can be specified with the XlDVAlertStyle constants. The Operator parameter uses the XlFormatCondition Operator to pick the type of operator to use. The Formulai and Formula2 parameters pick the data validation formula Deletes the Validation method for the range AlertStyle , Operator , Formulai , Formula2 Modifies the properties associated with the Validation....
RibbonX and VBA
As well as having the ability to create custom tabs and groups containing built-in controls, Microsoft has provided the ability to add many types of custom controls to the Ribbon, and to hook their actions and most of their attributes to VBA procedures and functions. This is done using a mechanism known as a callback. A callback means simply that, as part of a RibbonX definition, you provide the name of a procedure to run when the control is clicked, changed, and so on. It's exactly the same as...
SmartTag Properties
Returns a URL to save along with the corre Name String Read-only. Returns the name of the SmartTag Returns the properties of the SmartTag Range Range Read-only. Returns the range to which the specified Smart Returns the type of action for the selected XML String Read-only. Returns a sample of the XML that would be
Gridlines Methods
'Set the target chart and set up the gridline object Set oChart ActiveSheet.ChartObjects Chart 1 .Chart Set oGridline oChart.Axes xlValue .MajorGridlines True .Border.Weight xlMedium .Border.LineStyle xlContinuous .Border.ColorIndex 3 End With
Advanced Filter
A powerful way to filter data from a list is to use Advanced Filter. You can filter the list in place, like AutoFilter, or you can extract it to a different location. The extract location can be in the same worksheet, in another worksheet in the same workbook, or in another open workbook. In the following example, the data for NSW and VIC has been extracted for the first quarter of 2007. The data has been copied from the workbook containing the data list to a new workbook. The source data can...
VBE Properties
Returns or sets the CodePane currently being edited by the user. Typically used to identify which object is being worked on, or to force the user to work with a specific code pane. Returns or sets the VBProject selected in the Project Explorer window. If the Project Explorer is showing a VBComponent selected, this property returns the VBProject containing the component. Read-only. Returns the active Window, which may be a code pane, designer, or one of the VBIDE windows such as Project...
CommandBarControl Object and the CommandBarControls Collection Object
The CommandBarControl object represents a generic control on a CommandBar. A control usually consists of a CommandBarButton, a CommandBarComboBox, or a CommandBarPopup. When using one of these controls, you can work with them directly using their own object reference. Doing so will yield all of the properties and methods specific to that control. Use the Control object when you are unsure which type of CommandBar object you are working with, or when using controls other than the three mentioned...
PivotFormula Properties
Set Get the formula associated with the table. Use the Al-style reference notation Set Get the order that the formulas in the parent collection will be processed Set Get the formulas with standard U.S. formatting Set Get the formula associated with the table
Windows Collection Properties and Methods
Read-only. Returns a Window object from the Windows collection based on a given Index parameter specifying an index number or a name Set Get whether the contents of multiple windows can be simultaneously scrolled when documents are being compared side by side Arrange Method used to arrange the windows on the screen. Parameters ArrangeStyle As XlArrangeStyle, ActiveWorkbook , SynchHorizontal , SynchVertical used to end Side-by-Side mode. Returns a Boolean value indicating whether the operation...
Charts Collection Properties and Methods
The Charts collection has a few properties and methods besides the typical collection attributes. These are listed in the following table. Read-only. Returns a collection holding all the horizontal page breaks associated with the Charts collection Set Get whether the charts in the collection are visible. Also, you can set this to xlVeryHidden to prevent a user from making the charts in the collection visible Read-only. Returns a collection holding all the vertical page breaks associated with...
Shape Methods
Applies formatting that has been copied using the PickUp method Appearance As XLPicture Appearance , Format As XlCopyPicture Format Copies the range into the clipboard as a picture. The Appearance parameter can be used to specify whether the picture is copied as it looks on the screen or when printed. The Format parameter can specify the type of picture that will be put into the clipboard Cuts the shape and places it in the clipboard Duplicates the shape returning the new shape Flips the shape...
CommandBarComboBox Methods
Name Returns Parameters Description Adds a list item to the specified CommandBarComboBox. The combo box control must be a custom control and must be a drop-down list box or a combo box. This method will fail if it's applied to an edit box or a built-in ComboBox control. Removes all list items from a CommandBarComboBox drop-down list box or combo box and clears the text box edit box or combo box . This method will fail if it's applied to a built-in command bar control. Copies a...
The RangeAdvancedFilter Method
The AdvancedFilter method does play by the rules, but in a way that may be undesirable. The criteria used for filtering are entered on the worksheet in the criteria range. In a similar way to AutoFilter, the criteria string includes an operator and a value. Note that when using the equals operator, AdvancedFilter correctly matches by value and hence differs from AutoFilter in this respect. Because this is entirely within the Excel domain, the string must be formatted according to the Windows...
Using Names
One of the most useful features in Excel is the ability to create names. You can create a name by selecting the Formulas tab on the Ribbon and clicking the Name Manager button to display the Name Manager dialog box, shown in Figure 5-1. If the name refers to a range, you can create it by selecting the range, typing the name into the Name box at the left side of the Formula bar, and pressing Enter. However, in Excel, names can refer to more than just ranges. Mary ,, gt 2,3, t 3,4,5 rt, 5.5 5,6,7...
Connecting to an OLAP Data Source
The process of connecting to an OLAP data source is similar to that of any other external data source. In the Data tab, select the From Other Data Sources option and choose From Analysis Services, as demonstrated in Figure 23-1. Create a connection to a SQL Senrertable. Import data into Excel as a Table or PIvotTable report. From Analysis Services Create a connection to a SQL Server Analyse Servkas cube. Import data into Excel as a Table 01 Pi otfable report From JiML Data Import Open or map a...
Ranges of Inactive Worksheets
As with the Range property, you can apply the Cells property to a worksheet that is not currently active If you want to refer to a block of cells on an inactive worksheet using the Cells property, the same precautions apply as with the Range property. You must make sure you qualify the Cells property fully. If Sheet2 is active, and you want to refer to the range A1 E10 on Sheet1, the following code will fail because Cells 1,1 and Cells 10,5 are properties of the active worksheet Cells 10,5...
PivotCaches
A PivotCache is a buffer, or holding area, where data is stored and accessed as required from a data source. It acts as a communication channel between the data source and the PivotTable. In Excel 2007, you can create a PivotCache using the Create method of the PivotCaches collection, as seen in the recorded code. You have extensive control over what data you draw from the source when you create a PivotCache. Particularly in conjunction with ADO ActiveX Data Objects , which is demonstrated at...
The CursorLocation Property
This property allows you to specify whether the server-side cursor engine or the client-side cursor engine manages the records in the recordset. A cursor is the underlying object that manages the data in the recordset. Certain operations require the cursor engine to be on one side or the other. This is explored in more detail in the examples section. This property must be set before the recordset is opened. If you do not specify a cursor location, the default is server-side. You can set this...
PivotItems
Each PivotField object has a Pivotltems collection associated with it. You can access the Pivotltems using the Pivotltems method of the PivotField object. It is a bit peculiar that this is a method and not a property, and is in contrast to the Hiddenltems property and Visibleltems property of the PivotField object that return subsets of the Pivotltems collection. The Pivotltems collection contains the unique values in a field. For example, the Product field in the source data has four unique...
Application Methods
Activates an application specified by XlMSApplication. Opens the application if it is not open. Acts in a similar manner as the GetObject function in VBA Adds the array of strings specified by ListArray to Excel's custom lists. The ListArray may also be a cell range Calculates all the formulas in all open workbooks that have changed since the last calculation. Only applicable if using manual calculation Calculates all the formulas in all open workbooks. Forces recalculation of every formula in...
PivotTables Collection
You can use another method to create a PivotTable from a PivotCache, using the Add method of the PivotTables collection. If you have already created a PivotCache in your workbook and you want to create a second PivotTable, you can use the following code Dim pvc As PivotCache Dim pvt As PivotTable Set pvc ActiveWorkbook.PivotCaches l 'Add new PivotTable to PivotTables collection There is no particular advantage to using this method compared with the CreatePivotTable method. It's just another...
Error Properties
Get Set whether error checking is enabled for a range Read-only. Returns whether all the validation criteria are met Sub CheckForEmptyCellReference ' Place a formula in cell A1. Range A1 .Formula B1 C1 'If Cell B1 is empty MsgBox One or more of the referenced cells are empty. End If End Sub