Convert A List Into An Array

By converting a list of common values to an array, you can access the individual values quickly using one variable. You can convert a list of values to an array using a variety of different methods. You assign values to arrays by referencing the index values of each element. Arrays use index values to identify each of their elements. For example, if an array has 10 elements with a lower bounds of 1, the third element in the array has an index value of 3. In order to assign a value to an array...

Working With The Excel Object Model

You can capture the results of a button pressed on a displayed dialog box by assigning the Show property statement to a variable. The value of this variable is True if the user clicks OK, and False if the user clicks Cancel in the dialog box. Excel has over 240 different dialog boxes that display throughout its application. You can display any of these dialog boxes using the appropriate constant. The following table lists a few of the most commonly used Excel dialog boxes The Delete dialog box,...

Defining Ranges

Besides determining the number of rows and columns within a range you may also need to know the exact row or column where the range begins. You can accomplish this by using either the Row property for rows or the Column property for columns. You can determine the number of the first row in a range with the following code This code assigns the integer value representing the first row in the specified range to the FirstRowNum variable. You can also determine the first column in the range using...

Macro Storage

The Store macro in option on the Macro dialog box instructs Excel where to store the macros you record. Excel provides three different storage locations for your macros the current workbook, a new workbook, or the Personal Macro Workbook. You can store a recorded macro to your current workbook, commonly referred to as the active workbook, by selecting the This Workbook option. Use this option if you plan to share the workbook with other users. Storing the macros in the active workbook makes...

Using The Visual Basic Editor

The number of windows you can open within the Visual Basic Editor depends on the resolution of your monitor. The higher your monitor resolution, the more viewing space you have available. When writing code for your macro, you typically need to have only the Properties window, the Project Explorer window, and the corresponding code module window open. If you have a large high-resolution monitor, you may want to consider sizing the Visual Basic Editor and Excel windows so that you can see both...

Working With Worksheets

The FileFormat parameter accepts any of the XlFileFormat constant values, as outlined in Appendix A. The list of available file formats is rather extensive. Typically, you save the worksheet in another workbook by specifying the xlWorkbookNormal constant. This constant creates a new workbook based upon the default workbook format for the current version of Excel. If you need to save the workbook in a format to be used by an earlier version of Excel, you need to specify the appropriate format...

Modules

The Project Explorer lists a node for each module within the project. Modules contain general procedures, either functions or subroutines. Excel creates a new module for a project each time you add a new macro to the corresponding workbook. You can add other modules within the Visual Basic Editor, as outlined later in this chapter. Not all modules contain macros that are visible within Excel. You can create hidden procedures that are called by other functions and subroutines. The Properties...

Vba Programming Basics

Excel Vba Programming

VBA provides over 700 built-in constants, which you can insert into your code at any point without declaring them. The Excel VBA object model adds another 1,266, all of which begin with either xl or vb. You can use these constants anywhere, and you do not need to know their actual values in order to use them. Two of the most commonly used VBA constants deal with inserting carriage returns, vbCrLf, and tab characters, vbTab, in your output. Although each of these constants have a numeric...

Print A Sheet

You can create a procedure to print the contents of a sheet using the PrintOut method. Not only do you have the ability to specify what to print, you can also specify the number of copies to print or the method to print the contents of a sheet to a file. The PrintOut method has several different properties available for specifying how Excel prints the sheet From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, and PrToFileName. You use the From and To parameters to indicate the range...

Excel Macros

Although Microsoft did not originate the concept of macros and spreadsheets, they have definitely built upon it. The combination of the Macro Recorder and VBA makes macro creation a powerful feature of Excel. The Macro Recorder provides a great method for creating a macro without writing VBA code directly. The Macro Recorder holds true to its name. Just like a tape recorder, when you turn it on, it records all the events that occur within Excel. Excel takes the recorded events and creates the...

Hide A Macro

You can hide macros so that they do not appear on the Macro dialog box in Excel. If you create workbooks that you intend to share with other users, you may find that you want to hide specific macros within your workbook. This can help to ensure that an unknowing user does not inadvertently delete the macro from your workbook. Because Excel cannot execute a hidden macro from the Macro dialog box, the only method of execution for a hidden macro involves assigning a toolbar button or menu option....

Working With Cells

Excel remembers the values specified for the LookIn, LookAt, SearchOrder, and MatchByte parameters. If you run a search again without these parameter values, Excel uses the settings from the previous Find or Replace method. These values are also modified if you run a Find or Replace from within Excel. To avoid running searches that have unexpected values set, you should set these values each time you run the method. You can continue a search and find the next match using the FindNext method. In...

Execute A Procedure At A Specific Time

You can create a procedure that executes at a specific time by capturing the OnTime event. For example, you can set a reminder message that pops up at a specific time while editing a workbook. Unlike most other events, the OnTime event is not associated with a specific object. You, therefore, must access this event using the OnTime method that is associated with the Application object. There are four different parameters you use with the OnTime method, with only the first two being required...

Working With Other Workbooks And Files

Instead of saving an individual workbook, you can save the entire workspace. You can use workspaces, which have a .xlw extension, if you have multiple workbooks that you need to open simultaneously. If you save them as a workspace, you simply need to open that workspace and all workbooks in the workspace. To save a workspace, you use the SaveWorkspace method from the Application object. The Filename parameter, the required and only parameter, which you must specify for this method, contains the...

Adding Control Statements

You place labels within the code of a procedure to mark the subprocedure code. Other than signalling to the GoTo statement the location of the code to run, Excel ignores the label. Labeling the code does not change how it executes. Code within a loop or conditional statement executes only when a condition is met. Labeled code executes when the GoTo statement jumps to it, or when control passes to that area of the code. If you have multiple areas of labeled code you may not want it all to...

Format A Date Expression

You can custom format an expression using a specific date or time with the FormatDateTime function. Doing so returns a Variant data type value with the specified formatting. See Chapter 3 for more information on data types. The FormatDateTime function uses two different arguments, of which only the first argument is required Date, and NamedFormat. The Date argument identifies the date expression that you want to format and accepts cell references, variable references, string expressions, or...

Add Comments To A Cell

You can add comments to any cell in a worksheet using the AddComment method with the Range object. Comments are great methods for adding extra information about the value in a cell, such as how it was calculated, who provided the information, and so on. Using the AddComment method is basically the same as clicking InsertOComment within Excel. The biggest difference is that by default when you create a comment in Excel, the name of the person creating the comment is always added to the top of...

Custom Paste Values In Cells

You can customize how values paste into a worksheet from the Windows Clipboard by using the PasteSpecial method. You can use the PasteSpecial method with values that have been added to the Windows Clipboard using the Cut or Copy methods, or even values placed there directly from Excel. With the PasteSpecial method, you can customize how the cell contents are pasted into the new range by only pasting the cell formats or even by adding the cell values to the contents of the cells from where you...

Set Properties For A Project

You can set the properties, such as the project name and the lock status of a project, for each project that you view in the Visual Basic Editor. When you lock a project, the project is password protected so that only people who know the password can view and modify the contents of the project. You set both the project name and password information in the Project Properties dialog box. Excel considers each open workbook a project when you access the Visual Basic Editor. By default, the Visual...

Run A Procedure Before Losing A Workbook

You can create a procedure that runs automatically before a particular workbook closes in Excel. Because this type of procedure only executes once as the workbook closes, it works well for removing custom menus and toolbars loaded when the workbook opened, closing other workbooks, recalculating, or even automatically saving the workbook. The procedure executes when the workbook closes by catching the BeforeClose event that the closing workbook triggers. To produce a procedure that executes when...

Assign A Digital Signature To A Macro

You can assign a digital signature to any of your macros. You attach signatures to code in a macro, or file, to signify that the code is valid and that no one has modified it since you applied the signature. You can create two types of digital signatures certified digital signatures and personal digital signatures. You acquire certified digital signatures from commercial agencies, such as VeriSign, Inc. The signatures of choice when you distribute your code to other users, commercial agencies...

Dialog Object

The Dialog object references each of the built-in dialog boxes available in Excel. Excel stores these dialog boxes in the Dialogs collection. You can use the constant value associated with each Excel dialog box object to view the dialog. You can view individual dialog boxes by using the Show method. The names for each of the dialog boxes begin with xlDialog followed by a unique value that references the appropriate dialog box. For example, xlSaveAs references the Save As dialog box in Excel....

Using Excel Worksheet Functions

You can add almost all of the Excel worksheet functions to your VBA code. VBA provides a very limited number of built-in functions. By using the various functions available within Excel, you can add functionality that is not available with the existing VBA functions. For example, Excel provides several different financial functions that you can use within your macros. To place an Excel worksheet function in your VBA subroutine or function, you use the WorksheetFunction property along with the...

Automating Procedures With Excel Events

Vba Scripts

Understanding Excel Events Run a Procedure as a Workbook Opens Run a Procedure Before Closing a Run a Procedure Before Saving a Workbook Run a Procedure When Excel Creates a Workbook Monitor a Range of Cells for Changes Execute a Procedure at a Specific Time Execute a Procedure When You Press Keys Run a Procedure When Right-Clicking a VBA and Excel Object Model Quick What's on the Using the E-Version of the Hungry Minds, Inc. End-User License Excel Programming Your visual blueprint for creating...

An Introduction To

Ou use the Visual Basic for Applications (VBA) programming language to create all macros within Excel. Although most people use VBA for macro development, VBA is actually much more powerful than just a macro language. A member of the Visual Basic (VB) family, VBA sits between Visual Basic and VBScript as far as overall functionality. Amazingly enough, VBA actually provides a large portion of the language elements that VB uses, including forms, controls, objects, modules, and dataaccess...

Object List

Lists objects associated with the selected project. Lists objects associated with the selected project. Lists the procedures associated with the selected object. 0- IU Microsoft Excel Objects -a Modules Modulel S -4 VBAProject OfficeEKpen B 4a VBAProject PERSONAL. lj' I Microsoft Excel Objects B Q Modules Modulel recorded 6 10 2001 by Jinje ltd S hoc tout Cccl c Displays properties for the currently selected object. 0- IU Microsoft Excel Objects -a Modules Modulel S -4 VBAProject OfficeEKpen B...

Excel Programming

Your visual blueprint for creating interactive spreadsheets At maranGraphics, we believe in producing great computer books one book at a time. maranGraphics has been producing high-technology products for over 25 years, which enables us to offer the computer book community a unique communication process. Our computer books use an integrated communication process, which is very different from the approach used in other computer books. Each spread is, in essence, a flow chart the text and screen...

Create A Custom Toolbar

You can design a VBA procedure to create new toolbars within Excel where you can place links to the custom macros you create. You create a new toolbar by adding a new CommandBar object to the CommandBars object collection. Excel comes with approximately 30 different built-in toolbars, to which you can add controls. By creating new toolbars to house your custom toolbar options, you do not affect the layout of the standard toolbars. of True for the MenuBar parameter. If you only want the toolbar...

Add Controls To A

You can add controls to a toolbar that correspond to VBA macro code or any other Excel commands that you want to execute when selecting that toolbar control. You can add controls to any toolbar available within Excel. You can add existing Excel controls to the toolbar, or you can add new controls. When you add a new control to a toolbar, you can specify the icon image to represent the control along with the tool tip text, which displays when you drag the cursor across the control. You add a new...

Open A Workbook

Can create a procedure to open a workbook in Excel using the Open method of the Workbooks collection. Each time you open another workbook, Excel adds that workbook to the Workbooks collection. Similar to using the Open command on the File menu, opening another workbook using the Open method makes the workbook active. Sixteen different parameters determine how Excel opens a workbook. Of these parameters, Excel requires only FileName. In addition, you only need to use the FileName, ReadOnly,...

Return A Portion Of A String

Instead of an entire string, you can use the built-in functions available in VBA to return only a portion of a string. These functions work well when you only want a smaller portion of a string. You can use three different functions to return a portion of a string. The Left function returns the specified number of characters starting at the left side, or beginning, of the string. The Right function returns the specified number of characters starting at the right side, or end of the string. Each...

Working With Commandbars

By using toolbars and shortcut menus, you can provide quick access to commonly used commands. Excel has more than 50 different built-in toolbars and about 60 different shortcut menus, all of which are part of the CommandBars collection. This collection also contains any new custom toolbars and menus you create. Due to their central location, you can easily make modifications to existing menus and toolbars, or add new ones as needed. UNDERSTANDING THE COMMANDBARS COLLECTION The CommandBars...

Copy And Paste Ranges Of Cells

You can copy and paste cell ranges within a worksheet using the Copy method. The Copy method is essentially the same as the Copy and Paste commands within Excel, except that in Excel you are required to use two commands that is, you first copy the desired range and move to the appropriate location, and then select the Paste command. The Copy method associated with the Range object provides the ability to copy the values from a specific range and paste the values either in the Windows Clipboard...

Bz

Excel enables you to add custom menus to house links to VBA macros or other commonly used Excel commands. You can design a VBA procedure to create new menus that display within Excel. Typically you place most menus on the active menu bar. The active menu bar in Excel is the first object in the CommandBars collection. All menus you add become CommandBarControls on the active menu bar. You can create a new Excel menu with the Add method associated with the CommandBarControls collection. Although...

Vba And Excel Object Model Quick Reference

EXCEL OBJECT MODEL CONSTANTS CONTINUED MSDOS comma-separated values format. MS Windows comma-separated values format. Text file based on current operating system. Excel 2.0 format - Far East version. Text file created for a printer .prn . EXCEL OBJECT MODEL CONSTANTS CONTINUED Lotus 3.x and Lotus 1-2-3 for Windows format.

Protect A Worksheet

You can use the Protect method to password protect a worksheet so that other users cannot modify it. You can allow certain types of modifications, such as inserting rows, by specifying the appropriate parameter value for the Protect method. The Protect method has several different optional parameters that enable you to customize the type of protection that you assign to the workbook. Most of these parameters accept only True or False to indicate whether that type of protection is active for the...

Working With Charts

Chart Basics Create a Chart Sheet Embed a Chart within a Worksheet Apply Chart Wizard Settings to a Chart Add a New Data Series to a Chart Format Chart Text Create Charts with Multiple Chart Types Determine Variations in a Series of Data Add a Data Table to the Chart Customize the Chart Axis .256 .258 .260 .262 .264 .266 .268 .270 .272 .274

Delete A Macro From A Workbook

Ou can remove macros from any workbook to reduce your list of available macros. Similar to cleaning a closet, you want to eliminate the stuff you no longer need. When you delete a macro, Excel removes the actual macro without affecting any changes previously applied to the workbook with that macro. For example, if you use the macro to sum a series of cells, the sum remains the same when you delete the macro. Excel immediately applies the macro changes to the worksheet and then no longer relies...

Move A Sheet

You can rearrange sheets within a workbook using the Move method with the Sheets object. When you move a sheet, you indicate the new location by specifying the name of the sheet that you want to place before or after the current sheet. The Move method has two optional parameters, Before and After. Although both parameters are optional, you can only use one of them. Use the Before parameter to specify the sheet in front of which you want to move the current sheet. Use the After parameter to...

Set Macro Security

Due to the increasing problem with computer viruses, specifically macro viruses, by default, Excel disables all macros in worksheets that you open, except those with a signature from a trusted source. You can have Excel open all macros regardless of source, or prompt you before opening unsigned macros, by modifying the macro security level. Digital signatures, which a creator uses to verify a macro's safety, remain attached to a macro or other file so long as no one modifies the macro or file....

Run A Procedure Before Saving A Workbook

You can create a procedure that runs automatically before Excel saves a particular workbook. By creating this type of procedure, you can customize the method you use to save the workbook. For example, you may always want to display the Save As dialog box whenever the user selects the Save or SaveAs option in Excel. This procedure executes whenever you select the Save or the SaveAs options within Excel for the corresponding workbook. To create a procedure that executes before saving a workbook,...

Rename A Macro

You can very easily rename a macro that you created either with the Macro Recorder or in the Visual Basic Editor. Doing so, however, does require accessing the actual macro code within the Visual Basic Editor. When you create a macro in Excel using the Macro Recorder, Excel automatically writes the code for the macro in Visual Basic for Applications VBA . When you create a macro using the Macro Recorder, the only way that you can make changes to the macro code, or the macro name, is by...

Add A New Data Series To A Chart

After you create a chart, you can redefine the range of data Excel uses to display values on the chart by adding a new data series. A data series consists of a group of data values, which Excel displays on the chart. For example, if you have a bar chart showing the monthly sales in Dallas for each month the year, you can add another data series which contained the sales in Miami for the year. To define a new data series to add to the existing range of data, you create a new Series object and...

Embed A Chart Within A Worksheet

You can use VBA to embed a new chart to a worksheet in the existing workbook. When you embed a chart, Excel creates a new Chart object, which contains all the options that correspond to the chart. Each Chart object contains several objects that represent the settings for the chart, such as the ChartTitle object, which contains the chart title, its font and border properties, and other associated attributes. See the section Chart Basics for more information about the various child objects for...

Resize A Range

You can change the size of a range using the Resize property. Typically you resize a range because it does not contain the desired number of cells. When you resize a range, you change the number of rows and columns in a range. You can change the size by specifying either more or fewer rows or columns. The Resize property has two optional parameters of which you need to use at least one. If you do not use either parameter, Excel returns the original range. The first parameter, RowSize, indicates...

Close A Workbook

You can close a particular workbook from your macro using the Close method and including a reference to the Workbook object that contains the workbook you want to close. The Workbooks collection contains all of the currently open workbooks as individual workbook objects. The Workbooks collection adds the Workbook objects sequentially in the order you opened them. You reference a workbook with an index value, the name of the workbook, the ActiveWorkbook property, or the ThisWorkbook property....

Save A Workbook

Ou can save the currently selected Excel workbook using either the Save or Save As methods of the Workbook object. Excel has a different workbook object for each workbook you open. You can reference a specific workbook object by name, if you know the name. For example, the code Workbooks Sample, xls .Save saves the Sample.xls workbook. If you do not know the name of the workbook you want to save, you make the workbook the active workbook in Excel, and use the ActiveWorkbook property to save the...

An Introduction To The Excel Object Model

Designed around the ability to access and manipulate objects, VBA has access to an Object Model in each Microsoft Office product, including Excel, that enables you to interact with each application. Using the Object Model, you can access everything from the entire application to an individual cell in a worksheet. Objects represent the individual pieces of each application. Every object has specific properties and methods associated with it. You use properties and methods to capture events and...

Delete A File

VBA provides the ability to delete a workbook, or any other file using the Kill statement. You can use this statement to delete any file, as long as the user has permission to delete it. The following code illustrates the use of the Kill statement Kill pathname . The Kill statement requires one argument, the pathname. The pathname argument is a string referencing the files that you want to delete. To assure that Excel locates the files, the pathname argument must include not only the filename...

Run A Procedure When Excel Creates A Workbook

You can use the NewWorkbook application event to create a procedure that executes whenever Excel opens a new workbook. When you use an application event, you capture the events that the application in this case, Excel creates. The NewWorkbook event triggers whenever Excel creates a new workbook. Because the event comes from the application and not an individual object such as a workbook, or chart, you may find the process a little more complex than capturing other object events. When working...

Capture Input From A Custom Dialog

You typically use dialog boxes in Excel to gather input from the user. The input you capture from a user can be anything from determining which button was pressed to actual values typed by a user. You can capture the user input from the dialog box so you can return the appropriate responses by using the UserForm events. For example, when the user clicks an OK CommandButton control you use a CommandButton_Click subroutine to indicate what steps to perform. Excel considers every user interaction...

Logical Operators

Logical operators evaluate expressions and return a logical value of True or False. For example, you can use a logical operator to compare two comparison expressions. Negates the value of the expression. If the expression is True the operator causes it to be false, or vice versa. Performs a logical conjunction of two expressions. If they are both True, the result is True. If either of the expressions is False the result is False. If either expression is Null the result is Null. Performs a...

Format Chart Text

Excel Vba Chart Formatieren

As with all elements of a chart, you can customize the text that displays on the chart by changing the font attributes. When Excel adds text to a chart either as the chart title, axis labels, or even data labels, it applies default formatting to the specified text. For example, typically the text for a chart title is formatted using the default font, normally Arial, and bold with a font size of 12 point. You can customize the text of the ChartTitle object, and all other objects on the chart...

Jyn

UNDERSTANDING EXCEL EVENTS amp - - An event occurs within Excel whenever you perform any type of action. For example, if you click a particular cell, a Click event occurs. You can use these events to trigger the execution of particular procedures by creating event-handling procedures. Event-handling procedures are exactly what the name describes, procedures that execute when a particular event occurs. You can monitor five different types of events within Excel workbook, worksheet, chart,...

Save Workbook In Format Specified By User

You can request the name, location, and format for saving a workbook file from the user of your macro with the GetSaveAsFilename method. Using this method displays the Save As dialog box into which the user entered information for saving the file. The dialog box does not save the workbook file instead, Excel returns the user specified information to the variable assigned to the statement. To save the file, you use the SaveAs method. See the section Save a Workbook for more information. The...

Call A Custom Dialog Box From A Procedure

You can call and display any custom dialog boxes that are part of the same project as your procedure. You use custom dialog boxes to gather user input. For example, you can use the dialog box to request the values you need from the user to perform the appropriate calculations within a worksheet. To display a custom dialog box, you use the Show method of the UserForm object. The Show method instructs Excel to display the specified UserForm. The Show method has only one optional parameter, as...

Create A Shortcut

Msobarpopup Access 2007 Form

You can create a shortcut menu that displays when a user performs a specific action that contains commands related to VBA macro and Excel commands. A shortcut menu is a pop-up menu that displays when you right-click a particular location within Excel. You can create new shortcut menus or modify existing Excel shortcut menus, and you perform all shortcut menu creation and modification within the Visual Basic Editor. A shortcut menu is similar to a toolbar in that both are actually CommandBar...

Update A Recorded Macro

You can update macro code at any time by adding or removing VBA code. Of course, after you record a macro, you can record over the top of it to replace it, but you cannot modify it directly within Excel. The only method you can use to actually modify the macro code is to change the corresponding subroutine within the Visual Basic Editor. If you do not know how to read and write VBA code required for the step you want to add to the macro, this can become quite an undertaking. Typically,...

Create Custom Userform Controls

Excel 2000 Vba Userform

You can customize the Toolbox window to suit your needs. The Toolbox that displays when you select a UserForm within the Visual Basic Editor contains all of the standard controls you can add to the UserForm. These controls display on a single tabbed page called Controls. You can change the icon that a control uses as well as the tip text that displays when you drag you cursor across the icon. You can also create new controls to add to the Toolbox. add an OK button to all of your UserForms, you...

Userform Basics

You can use dialog boxes to request specific information from users by providing them an interface with your VBA code. Every Microsoft Windows application utilizes dialog boxes to gather information from the user, and Excel is no exception. As an example, you PARTS OF THE VISUAL BASIC EDITOR TOOLBOX frequently interact with the Open dialog box in Excel to select a file to open. VBA provides two standard dialog boxes MsgBox and InputBox. See Chapter 7 for more information on working with these...

Conditionally Call A Subroutine

You can conditionally move out of any location in a subroutine to run another subroutine or function. You can accomplish this by using a conditional VBA statement, such as an If Then statement, and combining it with a procedure call statement. When you combine the Call statement with a conditional statement, the other procedure is only called if the condition is met. When you use this type of structure, the If Then statement checks the specified condition. If the value of the condition is true,...

Open A File Requested By The User

Instead of specifying the file to open in your code, you can retrieve the name of the file by prompting the user with an Open dialog box. To display an Open dialog box from an Excel macro, you use the GetOpenFilename method. When you use this method, the file selected in the Open dialog box does not open when the user clicks OK. The dialog box passes the name of the file selected back to the variable that receives the statement assignment. If you want to open the selected file, you need to use...

Assign A Macro To A Menu

You can assign a macro to any existing Excel menu. If you do not want to use existing menus, you can even create a new menu. By assigning a macro to a menu, you make the macro as accessible as any menu option. Assigning macros to menus eliminates the need to remember the shortcut key required to launch the macro. When you add a macro to a menu, it remains on the menu for all workbooks that you open in Excel. For that reason, you should assign a macro that exists in your Personal Macro Workbook...

Format A Numeric Expression

You can custom format a numeric expression so that it displays with specific formatting by using either the FormatNumber, FormatCurrency, or FormatPercentage functions. These functions all take a numeric value and return a formatted number based upon the values you specify for each argument. The FormatNumber function returns a formatted number, whereas the FormatCurrency function returns a number that begins with a currency symbol, and the FormatPercentage function returns a number followed by...

Copy A Range To Multiple Sheets

You can copy a range of cells and place it in the same location on multiple sheets with the FillAcrossSheets method. When you use this method, Excel copies the specified cells to each worksheet you specify. You can copy everything in the range of cells, just the values in the cells, or only the formatting. When you use this method, you call it by indicating the range of worksheets where Excel should copy the cells followed by the FillAcrossSheets method. The range of worksheets must exist...

Find Specific Cell Values

Ou can use the Find method to search for specific values within a range of cells. This method works essentially the same as the EditOFind command in Excel. The Find method has several different parameters of which only the What parameter is required. You must specify the string for which you want to search as the value of the What parameter. If you want to start searching from a specific cell, use the After parameter to reference the cell before where you want to start searching. If omitted,...

Apply Chart Wizard Settings To A Chart

When you create a new chart within Excel, the Chart Wizard appears to step you through the process and requires that you specify numerous properties such as the chart location and the chart data values. With VBA, you can use the ChartWizard method to quickly format a chart without the need to set each individual property. You use the ChartWizard method with a specific Chart object. This method includes eleven different optional parameters, which you can only use with this method and which...

Remove A Module

You can remove modules from the Visual Basic Editor rather quickly. As you work within the Visual Basic Editor, you may find that you have modules that you want to remove from a selected project. Typically, you delete modules that contain subroutines and functions that you no longer need for your project. When you remove a module that contains code for a macro used within Excel, remember that you can no longer access the macro. Also, if you remove a module that contains code referenced by a...

Macro Workbook

You can delete macros that you no longer use from the Personal Macro Workbook. The Personal Macro Workbook stores macros that you want to make available to all workbooks. Excel creates the Personal Macro Workbook when you store your first macro in it. After Excel creates the Personal Macro Workbook, the workbook opens as a hidden file whenever you run Excel. You can only tell that a hidden file exists by viewing the Macro dialog box, where Excel lists the macros used by the Personal Macro...

Insert A Range

Ou can insert a range of cells into a worksheet using the Insert method. When you insert a range of cells into a worksheet, Excel adjusts the values in the existing cells by moving them either down or to the right to allow you to insert into the specified location. For example, if you insert a new row of cells in row 3, Excel shifts the existing values in row 3 down to row 4. Excel shifts all remaining values within the worksheet down. If you add a new column, Excel shifts all existing values...

Create A Constant

Ou can create constants to refer a value, or a string that never changes. For example, given that a week always contains seven days, you can declare a constant with a value of 7 when you place that value in a procedure. Just as you do with variables, you declare constants with a specific data type. In fact, constants use the same data types that variables use. If you do not specify a data type for a constant, VBA treats the value as a variant. Because constants never change, the functionality...

Customize The Chart Axis

You customize each axis on your chart with the Axis object properties and methods. Most charts that you create have a default of two different axes, the category axis and the values axis. For example, if you look at a standard column chart, the category axis runs horizontally across the bottom of the chart while the values axis runs vertically on the left side of the chart. When dealing with 3-D charts, there is a third series axis. Each chart axis is a separate Axis object. The Axes collection...

Validate Input From A Dialog

Excel Vba Input Dialog Box

You must validate the values specified for controls on a dialog box before passing the values back to your procedure. When you validate the data values, you do so for two major reasons. First, you ensure that the user specifies a value for a control. If the user forgets to select a control value, you can remind them immediately. Second, and probably most importantly, you ensure that errors do not occur in your code because the wrong type of data passes to a procedure. Then statement, to check...

Convert A Column Of Text Into Multiple Columns

You can break a column of text into multiple columns using the TextToColumns method. For example, if you have a list that contains both the first and last names in one column, you use TextToColumns to break that list into two different columns. You use the TextToColumns with the Range object containing the columns to parse into multiple columns. This method provides several different optional parameters to specify how to separate the text, including Destination, DataType, TextQualifier,...

Add A New Module

You can create new code modules directly within the Visual Basic Editor. VBA uses modules to store variable declarations and all procedures, including functions and subroutines. Whenever you create a new macro using the Macro Recorder, Excel generates a new module within the corresponding project to house the new macro. Excel places the macro code in a subroutine with the same name as the macro. See Chapter 3 for more information on procedures, including functions and subroutines. You do not...

Run A Procedure When Rightclicking A Chart

Position Chart Cell Vba

You can create a procedure that runs automatically each time a user right-clicks on a particular chart with the mouse. To create this type of procedure, you need to capture the BeforeRightClick event associated with the appropriate Chart object. To create a procedure that executes when a user right-clicks a chart, you create a new procedure and add it to the object code module for the particular chart. In fact, all event-handling procedures that you create for monitoring chart events must...

Display A Builtin Dialog

You can display all of the dialog boxes available in Excel in your macros by using VBA. By displaying a particular dialog box, you can incorporate that Excel functionality directly into your procedure. The Excel Object Model contains a Dialog object for each of the Excel dialog boxes. These objects are part of the Dialogs collection. You can access each of the Excel dialog box objects by specifying the corresponding constant value. The constant value for each dialog box begins with xlDialog...

Understanding Excel Events

Excel associates UserForm events not only with the form but also with each of the controls that exist on the form. You need to place event-handling procedures related to a UserForm in the code module for the UserForm object. The following table lists the available UserForm events. Excel adds a run-time control to the UserForm. The user performs a drag-and-drop operation. The user releases the mouse button to paste the data from the drag-and-drop operation. The user clicks the mouse on a...

Execute A Procedure When You Press Keys

You can create a procedure that executes when you press a specific key or combination of keys. For example, you can change the built key combination of Ctrl S for saving a workbook to display your own custom pop-up dialog box. To do this, you capture the OnKey event. If you specify a key combination that Excel already uses, your new definition overrides the Excel combination. Unlike most other events, the OnKey event is not associated with a specific object. For this reason, you access this...

Record A Macro

You can use macros to automate a series of steps. The easiest method for creating a macro involves using the Macro Recorder option, which captures everything you do and saves it in a macro module with the name you specify. After you create the macro, you can run the macro again, modify it, or delete it. Because the Macro Recorder records every action you perform when you use it, consider planning your steps before creating the macro. Because each macro action takes time to record, when you plan...

Perform Mathematical Calculations

Ou can perform many types of mathematical calcuations within your macros. VBA provides several different operators for performing mathematical calculations within your procedures. Because Excel typically contains numeric values, you frequently use VBA operators to create Excel macros. VBA includes eight different arithmetic operators for performing calculations. These operators include addition , - subtraction , A exponential , multiplication , division , integer division , and MOD Modulo -...

Combine Multiple Ranges

You can use the Union method to create a muliple area range. A multiple area range contains more than one block of cells, which may not be connected. For example, you use the Union method to create a Range object containng the cells A1 through B5 and D1 through E5. Although these two groups of cells are separated within the worksheet, using the Union method you can create one range that references only those cells. When you use the Range property in conjunction with the Union method, you can...

Customizing Dialog Boxes Menus And Toolbars

EXISTING MENUS AND TOOLBARS CONTINUED An msoControlType constant value indicating the type of control to add. The five constant values include msoControlButton for a standard button, msoControlEdit for an edit box, msoControlComboBox for a combo box, msoControlDropdown for a drop-down list, and msoControlPopup for another pop-up list of controls or a menu pop-up. An integer value specifying the built-in control to add to the command bar. If you omit this parameter, or specify a value of 1,...

Compare Two Strings

You can compare two strings to see if they are alike using the built-in StrComp function in VBA. When you compare two strings, the StrComp function returns a value indicating whether the strings are the same. If the strings are different, the function returns a value that shows which string is larger. The StrComp function has three different arguments, of which the first two arguments are required string1, string2, and compare. The string1 and string2 arguments indicate the strings to compare....

Getting Started With Excel Macros

When you create a new macro, you have the option of assigning it to a keyboard shortcut by typing the shortcut in the Keyboard Shortcut box on the Record Macro dialog box. When you do this, the macro runs when you press the Ctrl key and the specified lowercase key simultaneously. If you specify an uppercase letter for the key, you can run the macro by pressing Ctrl Shift the specified key. Unfortunately, Excel does not stop you from creating shortcuts that override other predefined Excel...

Using The Object Browser

Liminating the need to remember required syntax, the Object Browser enables you to quickly search for an object, property, or method that matches a desired keyword. For example, to add a new worksheet when you do not remember the appropriate method, you can use the search option on the Object Browser to find all objects that deal with the Add method. The Object Browser refers to each object as a class and lists them within the Classes list box. The Object Browser lists all properties and...